Repository Public
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