SPB Git

Repository Public

0001_init.sql 24.8 KB 467 lines sql

1-- MacLustr Command Center — schéma initial (PostgreSQL 17). Modèle de données neuf.2-- Conventions : uuid v7 générés par l'application, timestamptz en UTC, jamais de secret en clair3-- (seuls des hachés SHA-256 de jetons sont stockés).45create extension if not exists citext;67-- ───────────────────────── Identité ─────────────────────────8create type user_role as enum ('owner','admin','operator','viewer');910create table users (11  id               uuid primary key,12  handle           citext not null unique check (handle ~ '^[a-z0-9][a-z0-9._-]{1,31}$'),13  display_name     text   not null check (length(display_name) between 1 and 80),14  role             user_role not null,15  locale           text   not null default 'fr' check (locale in ('fr','en')),16  webauthn_handle  bytea  not null unique,          -- user.id WebAuthn : 32 octets aléatoires, jamais dérivé17  created_at       timestamptz not null default now(),18  disabled_at      timestamptz19);2021create table passkeys (22  id               uuid primary key,23  user_id          uuid not null references users(id),24  credential_id    bytea not null unique,25  public_key       bytea not null,                  -- COSE26  aaguid           bytea,27  sign_count       bigint not null default 0,28  transports       text[] not null default '{}',29  backup_eligible  boolean not null default false,30  backup_state     boolean not null default false,31  attestation_fmt  text not null default 'none',32  label            text not null default '',33  created_at       timestamptz not null default now(),34  last_used_at     timestamptz,35  revoked_at       timestamptz36);37create index on passkeys(user_id) where revoked_at is null;3839create type challenge_kind as enum ('registration','authentication','stepup');40create table webauthn_challenges (41  id            uuid primary key,42  kind          challenge_kind not null,43  user_id       uuid references users(id),44  session_id    uuid,                               -- step-up et ajout de passkey : lié à la session45  invitation_tx uuid,                               -- enregistrement sur invitation46  data          jsonb not null,                     -- SessionData de la cérémonie47  created_at    timestamptz not null default now(),48  expires_at    timestamptz not null,49  consumed_at   timestamptz                         -- usage unique : UPDATE … WHERE consumed_at IS NULL50);51create index on webauthn_challenges(expires_at);5253create type device_kind as enum ('web','macos','ios');54create table devices (55  id            uuid primary key,56  user_id       uuid not null references users(id),57  kind          device_kind not null,58  label         text not null default '',59  model         text not null default '',60  os_version    text not null default '',61  app_version   text not null default '',62  push_token    text,                               -- jeton APNs (non secret, mais jamais journalisé)63  push_env      text check (push_env in ('development','production')),64  created_at    timestamptz not null default now(),65  last_seen_at  timestamptz,66  revoked_at    timestamptz67);68create index on devices(user_id);6970create type session_kind as enum ('cookie','bearer');71create table sessions (72  id                  uuid primary key,73  user_id             uuid not null references users(id),74  device_id           uuid not null references devices(id),75  kind                session_kind not null,76  token_hash          bytea not null unique,        -- cookie de session, ou jeton d'accès courant (bearer)77  access_expires_at   timestamptz,                  -- bearer seulement78  csrf_hash           bytea,                        -- cookie seulement79  passkey_id          uuid references passkeys(id),80  created_at          timestamptz not null default now(),81  last_seen_at        timestamptz not null default now(),82  authenticated_at    timestamptz not null default now(),  -- dernière cérémonie passkey (step-up)83  idle_expires_at     timestamptz not null,84  absolute_expires_at timestamptz not null,85  revoked_at          timestamptz,86  revoked_reason      text,87  ip                  inet,88  user_agent          text89);90create index on sessions(user_id) where revoked_at is null;91create index on sessions(device_id);9293create table refresh_tokens (94  id           uuid primary key,95  session_id   uuid not null references sessions(id),96  token_hash   bytea not null unique,97  issued_at    timestamptz not null default now(),98  expires_at   timestamptz not null,99  used_at      timestamptz,                         -- rotation : un jeton déjà utilisé qui revient = rejeu → session révoquée100  replaced_by  uuid references refresh_tokens(id)101);102create index on refresh_tokens(session_id);103104create table invitations (105  id             uuid primary key,106  token_hash     bytea not null unique,             -- SHA-256 d'un jeton de 32 octets aléatoires107  role           user_role not null,108  handle         citext,                            -- identité proposée (nouveau compte)109  display_name   text,110  target_user_id uuid references users(id),         -- invitation de liaison à un compte existant111  note           text not null default '',112  created_by     uuid references users(id),         -- null = amorçage hors bande (CLI)113  created_at     timestamptz not null default now(),114  expires_at     timestamptz not null,115  consumed_at    timestamptz,116  revoked_at     timestamptz,117  failed_attempts int not null default 0,118  completed_at   timestamptz,119  resulting_user_id uuid references users(id)120);121122create table invitation_transactions (123  id            uuid primary key,124  invitation_id uuid not null unique references invitations(id),125  tx_hash       bytea not null unique,              -- transaction courte échangée contre le jeton126  created_at    timestamptz not null default now(),127  expires_at    timestamptz not null,128  completed_at  timestamptz,129  ip            inet130);131132create type grant_effect as enum ('allow','deny');133create table policy_grants (134  id            uuid primary key,135  subject_kind  text not null check (subject_kind in ('user','role')),136  subject       text not null,137  permission    text not null,138  resource_kind text,                               -- null = toutes ressources139  resource_id   text,140  effect        grant_effect not null,141  created_by    uuid references users(id),142  created_at    timestamptz not null default now(),143  unique (subject_kind, subject, permission, resource_kind, resource_id)144);145146-- ───────────────────────── Audit (append-only, chaîné) ─────────────────────────147create type audit_outcome as enum ('success','denied','failure');148create table audit_log (149  seq            bigserial primary key,150  ts             timestamptz not null default now(),151  actor_kind     text not null check (actor_kind in ('user','agent','system','cli')),152  actor_id       uuid,153  actor_label    text not null default '',154  device_id      uuid,155  session_id     uuid,156  ip             inet,157  action         text not null,158  target_kind    text,159  target_id      text,160  params         jsonb not null default '{}',       -- paramètres non sensibles uniquement (liste blanche par action)161  outcome        audit_outcome not null,162  detail         text not null default '',163  correlation_id uuid not null,164  prev_hash      bytea not null,165  hash           bytea not null166);167create index on audit_log(ts desc);168create index on audit_log(actor_id, ts desc);169create index on audit_log(action, ts desc);170create index on audit_log(target_kind, target_id, ts desc);171create index on audit_log(correlation_id);172173create function audit_log_immutable() returns trigger language plpgsql as $$174begin raise exception 'audit_log is append-only'; end $$;175create trigger audit_no_update before update or delete on audit_log176  for each row execute function audit_log_immutable();177create trigger audit_no_truncate before truncate on audit_log178  for each statement execute function audit_log_immutable();179180-- ───────────────────────── Inventaire ─────────────────────────181create type node_kind as enum ('mac','linux','nas');182create table nodes (183  id             uuid primary key,184  hardware_uuid  text unique,                       -- IOPlatformUUID / machine-id ; null tant qu'aucun agent n'a parlé185  alias          citext not null unique,186  kind           node_kind not null default 'mac',187  hostname       text not null default '',188  site           text not null default 'default',189  role           text not null default 'worker',190  environment    text not null default 'production',191  tags           text[] not null default '{}',192  model          text, chip text, arch text,193  cpu_cores      int, cpu_levels jsonb, gpu_cores int,194  mem_bytes      bigint, storage_bytes bigint,195  os_name        text, os_version text, os_build text,196  primary_ip     inet,197  registered_via text not null check (registered_via in ('manual','agent')),198  maintenance    boolean not null default false,199  maintenance_reason text not null default '',200  inventory      jsonb not null default '{}',       -- interfaces, volumes, détails matériels (dernier inventaire)201  inventory_at   timestamptz,202  created_at     timestamptz not null default now(),203  last_seen_at   timestamptz,204  retired_at     timestamptz205);206207create table agents (208  id               uuid primary key,209  node_id          uuid not null references nodes(id),210  cert_serial      text not null unique,211  cert_fingerprint bytea not null,212  cert_not_after   timestamptz not null,213  version          text not null default '',214  os               text not null default '',215  capabilities     text[] not null default '{}',    -- types de commandes que l'agent accepte localement216  enrolled_at      timestamptz not null default now(),217  last_seen_at     timestamptz,218  last_seq         bigint not null default 0,219  overhead         jsonb not null default '{}',     -- CPU/RSS de l'agent lui-même220  revoked_at       timestamptz,221  revoked_reason   text222);223create unique index agents_one_active_per_node on agents(node_id) where revoked_at is null;224225create table agent_enrollment_tokens (226  id          uuid primary key,227  token_hash  bytea not null unique,228  node_alias  citext not null,229  node_kind   node_kind not null default 'mac',230  created_by  uuid references users(id),231  created_at  timestamptz not null default now(),232  expires_at  timestamptz not null,233  consumed_at timestamptz,234  revoked_at  timestamptz235);236237-- ───────────────────────── Télémétrie ─────────────────────────238create table metric_catalog (239  id          smallint primary key,240  name        text not null unique,241  unit        text not null,242  kind        text not null check (kind in ('gauge','counter_rate')),243  interval_s  int  not null,244  labeled     boolean not null default false,245  source      text not null,246  description text not null247);248249-- quality : 0 = mesuré ; les états unavailable/unsupported/permission_denied ne produisent PAS d'échantillon,250-- ils sont déclarés dans node_state.availability.251create table samples_raw (252  node_id   uuid not null,253  metric_id smallint not null,254  label     text not null default '',255  ts        timestamptz not null,256  value     double precision not null,257  primary key (node_id, metric_id, label, ts)258) partition by range (ts);259260create table samples_1m (261  node_id uuid not null, metric_id smallint not null, label text not null default '',262  ts timestamptz not null, avg double precision not null, min double precision not null,263  max double precision not null, n int not null,264  primary key (node_id, metric_id, label, ts)265) partition by range (ts);266267create table samples_1h (268  node_id uuid not null, metric_id smallint not null, label text not null default '',269  ts timestamptz not null, avg double precision not null, min double precision not null,270  max double precision not null, n int not null,271  primary key (node_id, metric_id, label, ts)272) partition by range (ts);273274create table rollup_state (tier text primary key, watermark timestamptz not null);275276create table node_state (                           -- dernier instantané par nœud (lecture rapide)277  node_id      uuid primary key references nodes(id),278  ts           timestamptz not null,                -- horodatage côté agent (UTC)279  received_at  timestamptz not null,280  values       jsonb not null,                      -- { "cpu.usage": 12.3, … }281  availability jsonb not null default '{}',         -- { "gpu.usage": "unsupported", "thermal.temp": "permission_denied" }282  processes    jsonb,                               -- top N processus283  processes_at timestamptz,284  schema_version int not null default 1285);286287create type service_manager as enum ('pm2','launchd','systemd','docker');288create table services (289  id          uuid primary key,290  node_id     uuid not null references nodes(id),291  manager     service_manager not null,292  name        text not null,293  state       text not null,                        -- online|stopped|errored|unknown (normalisé) ; brut dans meta294  pid         int, restarts int, mem_bytes bigint, cpu_pct real, uptime_s bigint,295  version     text, ports int[] not null default '{}',296  app         text,                                 -- application logique (registre de déploiement), si connue297  public_url  text, health jsonb,298  managed     boolean not null default false,       -- actions autorisées (liste blanche serveur + agent)299  meta        jsonb not null default '{}',300  first_seen_at timestamptz not null default now(),301  last_seen_at  timestamptz not null,302  gone_at     timestamptz,303  unique (node_id, manager, name)304);305306create table inference_servers (307  id         uuid primary key,308  node_id    uuid not null references nodes(id),309  kind       text not null,                         -- mlx_lm | ollama | llama.cpp …310  port       int not null,311  model      text, quantization text,312  pid        int, rss_bytes bigint, uptime_s bigint, disk_bytes bigint,313  health     text not null default 'unknown',       -- ok|failing|unknown314  health_at  timestamptz, health_latency_ms real,315  listed_models jsonb,316  meta       jsonb not null default '{}',317  last_seen_at timestamptz not null, gone_at timestamptz,318  unique (node_id, port)319);320321create table volumes (322  id uuid primary key, node_id uuid not null references nodes(id),323  mount text not null, device text, fs text, kind text not null default 'internal', -- internal|external|network324  total_bytes bigint, used_bytes bigint, smart_status text, remote text,325  last_seen_at timestamptz not null, gone_at timestamptz,326  unique (node_id, mount)327);328329create table nas_targets (                          -- NAS connus (enregistrement manuel), état observé par les agents330  id uuid primary key, name text not null unique, host inet not null, share text not null default '',331  protocol text not null default 'smb', note text not null default '',332  state text not null default 'unknown', state_at timestamptz, observed_by uuid references nodes(id),333  latency_ms real, mounted_on int not null default 0, total_bytes bigint, used_bytes bigint334);335336create table backups (                              -- sauvegardes observées (jamais déclarées « ok » sans preuve)337  id uuid primary key, name text not null unique, node_id uuid references nodes(id),338  kind text not null, last_run_at timestamptz, last_success_at timestamptz, last_status text not null default 'unknown',339  size_bytes bigint, detail text not null default '', observed_at timestamptz340);341342-- ───────────────────────── Alertes, incidents ─────────────────────────343create type severity as enum ('info','warning','critical');344create table alert_rules (345  id uuid primary key, name text not null unique, description text not null default '',346  enabled boolean not null default true, builtin boolean not null default false,347  kind text not null check (kind in ('threshold','absence','service_state')),348  metric text, label_match text,                    -- threshold349  op text check (op in ('>','>=','<','<=')), threshold double precision, clear_threshold double precision,350  for_seconds int not null default 300, clear_seconds int not null default 120,351  severity severity not null default 'warning',352  scope jsonb not null default '{"all":true}',      -- {"all":true} | {"node_ids":[…]} | {"tags":[…]}353  notify boolean not null default true,354  created_by uuid references users(id), created_at timestamptz not null default now(), updated_at timestamptz not null default now()355);356357create type alert_state as enum ('pending','firing','acknowledged','resolved');358create table incidents (359  id uuid primary key, title text not null, severity severity not null,360  state text not null default 'open' check (state in ('open','mitigated','resolved')),361  summary text not null default '', owner_id uuid references users(id),362  opened_at timestamptz not null default now(), resolved_at timestamptz, created_by uuid references users(id)363);364create table alerts (365  id uuid primary key, rule_id uuid not null references alert_rules(id), node_id uuid references nodes(id),366  label text not null default '', fingerprint text not null, state alert_state not null, severity severity not null,367  summary text not null, last_value double precision,368  started_at timestamptz not null, fired_at timestamptz, acknowledged_at timestamptz, acknowledged_by uuid references users(id),369  resolved_at timestamptz, clear_since timestamptz, last_eval_at timestamptz not null,370  flap_count int not null default 0, suppressed_by text,           -- silence|maintenance : pas de notification371  incident_id uuid references incidents(id)372);373create unique index alerts_open_fingerprint on alerts(fingerprint) where state <> 'resolved';374create index on alerts(state, severity);375create table timeline_events (                      -- chronologie commune alertes / incidents376  id uuid primary key, alert_id uuid references alerts(id), incident_id uuid references incidents(id),377  ts timestamptz not null default now(), kind text not null, actor_id uuid references users(id), body text not null default ''378);379create index on timeline_events(alert_id, ts); create index on timeline_events(incident_id, ts);380381-- ───────────────────────── Entretien : calendrier + notes ─────────────────────────382create table maintenance_windows (383  id uuid primary key, title text not null check (length(title) between 1 and 160), description text not null default '',384  category text not null default 'planned' check (category in ('planned','emergency','update','hardware','inspection')),385  starts_at timestamptz not null, ends_at timestamptz not null check (ends_at > starts_at),386  timezone text not null default 'America/Toronto',387  recurrence text not null default 'none' check (recurrence in ('none','daily','weekly','monthly')),388  recurrence_until timestamptz,389  scope jsonb not null default '{"node_ids":[]}',390  set_maintenance_mode boolean not null default true, silence_alerts boolean not null default true,391  status text not null default 'scheduled' check (status in ('scheduled','in_progress','completed','cancelled')),392  activated_occurrence timestamptz,                 -- occurrence en cours appliquée par l'ordonnanceur393  created_by uuid references users(id), created_at timestamptz not null default now(), updated_at timestamptz not null default now()394);395create index on maintenance_windows(starts_at, ends_at);396create table maintenance_notes (397  id uuid primary key, window_id uuid references maintenance_windows(id) on delete cascade,398  node_id uuid references nodes(id), occurrence timestamptz,399  author_id uuid not null references users(id), body text not null check (length(body) between 1 and 20000),400  pinned boolean not null default false,401  created_at timestamptz not null default now(), edited_at timestamptz, deleted_at timestamptz,402  check (window_id is not null or node_id is not null)403);404create index on maintenance_notes(window_id, created_at); create index on maintenance_notes(node_id, created_at);405406create table silences (407  id uuid primary key, reason text not null, scope jsonb not null, rule_id uuid references alert_rules(id),408  starts_at timestamptz not null, ends_at timestamptz not null, created_by uuid references users(id),409  maintenance_window_id uuid references maintenance_windows(id) on delete cascade, created_at timestamptz not null default now()410);411412-- ───────────────────────── Actions, commandes, jobs ─────────────────────────413create type action_state as enum ('pending_approval','queued','dispatched','running','succeeded','failed','cancelled','timed_out','rejected','expired');414create table actions (415  id uuid primary key, type text not null, criticality text not null check (criticality in ('low','medium','high','critical')),416  node_id uuid references nodes(id), target_kind text not null, target_id text not null,417  params jsonb not null default '{}', state action_state not null,418  requested_by uuid references users(id), requested_via text not null default 'api', justification text not null default '',419  idempotency_key text not null, request_hash bytea not null,420  approval_required boolean not null default false, approved_by uuid references users(id), approved_at timestamptz, rejection_reason text,421  created_at timestamptz not null default now(), queued_at timestamptz, started_at timestamptz, finished_at timestamptz,422  deadline_at timestamptz, timeout_s int not null, cancel_requested_at timestamptz,423  result jsonb, error_code text, error_message text, correlation_id uuid not null, job_id uuid,424  unique (requested_by, idempotency_key)425);426create index on actions(state, created_at); create index on actions(node_id, created_at desc);427create table action_locks (target_key text primary key, action_id uuid not null references actions(id), acquired_at timestamptz not null default now());428create table action_events (429  id bigserial primary key, action_id uuid not null references actions(id), ts timestamptz not null default now(),430  kind text not null, stream text, data text not null default ''     -- sortie expurgée (secrets masqués par l'agent ET le serveur)431);432create index on action_events(action_id, id);433434create table agent_commands (435  id uuid primary key, action_id uuid not null references actions(id), agent_id uuid not null references agents(id),436  envelope bytea not null, signature bytea not null, key_id text not null,437  issued_at timestamptz not null default now(), expires_at timestamptz not null,438  delivered_at timestamptz, acked_at timestamptz, finished_at timestamptz,439  state text not null default 'pending' check (state in ('pending','delivered','acked','finished','expired','cancelled'))440);441create index on agent_commands(agent_id, state);442443create type job_state as enum ('queued','running','succeeded','failed','cancelled','timed_out');444create table jobs (445  id uuid primary key, template text not null, owner_id uuid references users(id), params jsonb not null default '{}',446  state job_state not null default 'queued', node_id uuid references nodes(id), placement jsonb not null default '{}',447  progress real, attempt int not null default 0, max_attempts int not null default 1, dedupe_key text unique,448  schedule_id uuid, depends_on uuid[] not null default '{}',449  created_at timestamptz not null default now(), started_at timestamptz, finished_at timestamptz,450  next_attempt_at timestamptz, result jsonb, error text, usage jsonb451);452create index on jobs(state, created_at);453create table job_schedules (454  id uuid primary key, name text not null unique, template text not null, params jsonb not null default '{}',455  placement jsonb not null default '{}', every_seconds int not null check (every_seconds >= 60), enabled boolean not null default true,456  next_run_at timestamptz not null, last_run_at timestamptz, created_by uuid references users(id), created_at timestamptz not null default now()457);458459-- ───────────────────────── Flux d'événements, réglages ─────────────────────────460create table events_outbox (461  seq bigserial primary key, ts timestamptz not null default now(), topic text not null, payload jsonb not null462);463create index on events_outbox(ts);464create table user_dashboards (user_id uuid not null references users(id), name text not null default 'default', layout jsonb not null, updated_at timestamptz not null default now(), primary key (user_id, name));465create table settings (key text primary key, value jsonb not null, updated_at timestamptz not null default now(), updated_by uuid references users(id));466create table rate_limits (bucket text primary key, tokens real not null, updated_at timestamptz not null);467
Files main
Menu

Theme