canopy-security Data Model

audit_events

The system-wide audit log. One row per domain event received from the wildcard (#) RabbitMQ subscriber. Carries event_id (the original event’s UUID), event_type (RabbitMQ routing key), source_service, action, resource_type, optional resource_id, optional user_id / user_role, optional ip_address, full metadata JSONB, and an explicit event_timestamp separate from received_at. The 20260402000001 migration added previous_hash + event_hash columns extending the SHA-256 tamper-evidence chain across the audit log. The 20260601000010 migration (Plan-1 worker-intake MR5a) added a nullable household_id UUID populated by the canonical extractor (event_parsing::parse_event) for new rows — existing rows stay NULL per ADR-016 forward-only — feeding the worker-portal Audit section’s household filter via a partial index. Per ADR-014, inserts are serialised by pg_advisory_xact_lock(1); the chain breach detector emits fti.audit_chain.breach_detected and the auditor endpoint returns 503 until investigation completes. The 20260621000000 migration (T2-5 #686, ADR-014 Amendment 1) added a per-row hash_version SMALLINT selector so legacy v1 rows (which hashed only previous_hash · event_id · event_type · canonical_timestamp) stayed byte-stable alongside the hardened v2 formula; the 20260624130000 migration (T2-6 #687, ADR-014 Amendment 3) dropped hash_version — pre-1.0 there were no v1 rows, so the chain collapsed to its single (former-v2) formula. That formula hashes the RFC 8785 (JCS) canonical bytes of a typed input struct covering previous_hash, event_id, event_type, the canonical timestamp, the actor (user_id/user_role), action, resource_type, resource_id, source_service, household_id, and a content-hash of metadata (which carries the worker fact-history before/after + author) — making the change-history tamper-evident. The metadata is normalized through Postgres (SELECT $1::jsonb) before hashing so insert and verify hash identical bytes. Since T2-6 MR9 (#687, ADR-036 §5/§7, ADR-014 Amendment 4) the fact-history before/after PII value leaves arrive sealed (a SealedValue envelope under canopy-persons' per-fact DEK — canopy-security never holds the DEK, ADR-001), so metadata holds ciphertext; the formula hashes the ct unchanged, and a fact redaction (which tombstones that DEK in canopy-persons, never rewriting this row) leaves metadata + event_hash byte-identical — the chain stays valid while the value becomes unopenable (tamper-evidence preserved over redacted values, no security-side shred or fan-out). The change-history read renders (sealed) for those leaves (worker value display re-sourced from the system-of-record per #920). event_id/previous_hash/event_hash ordering uses created_at, id (UUID v7 tie-break). The 20261128000000 migration (#1519, ADR-044 epic &78 MR-5) added a nullable programs TEXT[] — the row’s program-scope assertion for worker-portal visibility: NULL = no assertion (invisible to any program-scoped reader — fail-closed), '{}' = asserted program-neutral (visible to all scoped readers; protects the Pub-1075 ssn.accessed trail and other cross-program streams), non-empty = the named storage slugs (visible on scope overlap). Populated at ingest by event_parsing::derive_programs (publisher envelope assertion → routing-key first/last-segment derivation → curated neutral families → NULL); existing rows stay NULL per ADR-016 forward-only (see Security Operations › Audit Program-Scope Posture). Like household_id and dedup_key, programs is OUTSIDE the frozen v1 chain-hash input — the migration header records the tamper-evidence reasoning; the dormant chain-v2 substrate deliberately does NOT carry the column yet (the #1279 cutover rules on its v2 treatment).

audit_events_archive

Long-term archive of aged-out audit rows. Originally created via LIKE audit_events INCLUDING ALL; the 20260409000000 migration explicitly re-adds previous_hash / event_hash after the LIKE was outpaced by the 20260402000001 hash-chain addition (without that fix, INSERT INTO audit_events_archive SELECT * FROM audit_events failed with "more expressions than target columns"). The 20260601000010 migration likewise adds household_id UUID to the archive twin so aged-out rows keep the column (no partial index on the NULL-heavy archive). The 20260621000000 migration (T2-5) added hash_version SMALLINT to the twin and the 20260624130000 migration (T2-6) dropped it from the twin — both applied to audit_events and its archive in lock-step so the positional INSERT … SELECT * archive move. The 20261128000000 migration adds programs TEXT[] to the twin in lock-step (#1519) so the mover’s explicit-column move and the export union keep working; no GIN index on the archive twin (the scoped read paths hitting the archive are the low-volume export union and by-id lookups). Per ADR-014, the hash chain extends across the audit_events ↔ audit_events_archive boundary so archived rows remain verifiable.

breach_alerts

Detection-rule firings. One row per breach detected by the background detector. Carries rule_name, severity ∈ {low, medium, high, critical} (CHECK from 20260402), human-readable description, optional user_id and source_service for the principal/origin, evidence JSONB (matched events, counts, thresholds), status ∈ {open, in_progress, resolved, false_positive} (CHECK from 20260402), and the resolved_by / resolved_at workflow pair.

nist_control_mappings

NIST SP 800-53 control coverage table, seeded by 20260326000001. One row per control with control_id, control_name, control_family, the event_types[] array that satisfies the control, a human-readable description, and implementation_status (implemented / planned / etc.). Initial seed covers AU-2, AU-3, AU-6, AU-9, AU-11, AC-2, AC-6, AC-7, SI-4, IR-4, IR-5. Unique partial index on control_id from 20260402 prevents duplicate seedings.

detection_rules

Configurable breach detection rules (IRS Pub 1075 §9), seeded by 20260326000002 and REPAIRED by 20261102000000 (#1302 — the shipped detector was double-inert: dispatch handled only rule_type = 'event_count' while seeds carried free vocabulary types, and the evaluator’s rule-name string map matched no seeded name). One row per rule with rule_name (UNIQUE), rule_type (event_count is the sole evaluator), sliding-window (threshold, window_minutes), severity (CHECK ∈ {low, medium, high, critical} from 20260402), enabled/active flags, optional notify_webhook, and the #1302 TYPED predicate columns — event_type_pattern / action_pattern / resource_type_pattern / source_service_pattern, NULL = wildcard, AND-combined, ILIKE-contains — so a rule is self-describing runtime config (thresholds/predicates tunable via UPDATE, no redeploy). The evaluator counts across BOTH audit stores (audit_events ∪ audit_events_v2; under chain-v2 new events land only in the v2 substrate). Pattern semantics: ILIKE containment ('export' also matches 'exported'; %/ in a pattern act as ILIKE metacharacters — patterns are trusted config, bound as parameters, never interpolated). Seeds after #1302: Failed Authentication (5/10min, high, action auth_failed), Bulk Data Access (100/5min, medium, action export), Privilege Escalation (1/60min, critical, action role_changed), FTI Access Volume (100/10min, high, ssn/accessed — the live ssn.accessed Pub-1075 stream), Reporting Extract Volume (50/10min, medium, source canopy-reporting — LIVE end-to-end since #1404: canopy-reporting’s route-table middleware stages reporting.report.generated / reporting.report.accessed / reporting.extract.exported envelopes on every successful generate/read/export, parsed by named arms to (generate/read/export, reporting*); the export action deliberately also counts toward Bulk Data Access’s containment pattern). After Hours Access is disabled by the #1302 migration: it never had time-of-day semantics and its natural pattern would substring-alias accessed; the real after-hours evaluator is #1405.

fti_chain_verifications

Per-FTI-service hash-chain verification result ledger per ADR-014 §7-8. One row per verify run for canopy-tanf or canopy-medicaid. Carries service, verified_at, rows_verified, broken flag, optional broken_at_row_id + broken_reason for the breach point, and duration_ms. This table is NOT itself part of the hash chain — it’s the verification side-channel that validated the FTI tables in the program services. Legacy v1 ledger on its way out: no writers since #1245 (the full-walk loop was removed) and no reader beyond the breach bit since #1206 MR-3 (legacy_fti_breach_latched — a latched broken = true row forces the unified GET /v1/security/chain/status?family=fti&service=… to breached with reason legacy_breach_latched; the legacy fti/chain-status endpoint and its FtiChainVerification wire DTO are deleted). The table itself drops at the #1279 cutover (the #1245 "a breach is never silently swallowed" safety invariant retires with it).

event_outbox (ADR-018 + ADR-039)

Per-service transactional outbox (ADR-018), schema single-sourced in crates/canopy-mq/outbox-migrations/ and generated into this service (ADR-039). Columns, indexes, hold semantics, and the migration inventory are documented ONCE in the cross-cutting description — see the data-models index.

event_inbox (#433)

Per-service consumer inbox (#433 / ADR-018 amendment). Subscriber writes a row before invoking the handler; PK on event_id (the envelope’s UUID v7) makes RabbitMQ redelivery idempotent. Carries (event_id, routing_key, payload, enqueued_at, processed_at, attempts, last_error). Janitor (canopy-mq::InboxDrainer) sweeps processed rows older than 7 days.

audit_archive_runs (#1208)

The durable archive-run record — the unit of async archival work AND the accountability record. UUIDv7 PK; state ∈ {queued, running, done, error}; requested_by (admin:{sub} / scheduler); the frozen config snapshot (archive_after_days CHECK 1..=36500, chunk_size CHECK 100..=20000, max_chunks_per_pass CHECK 1..=1000); the token-fenced lease (lease_owner/lease_token uuidv7/lease_expires_at/heartbeat_at — expiry-only takeover, a fenced heartbeat matches zero rows); attempts; the per-chunk committed progress (chunks_committed, rows_archived — durable + pollable mid-run); more (pass ended on a full chunk); error_code CHECK in the closed set {duplicate_overlap, upgrade_state_unrepaired, statement_timeout, db_error, crashed} + error_detail; started_at/finished_at; state-consistency CHECKs (running ⇔ leased, terminal ⇔ finished, error ⇔ error_code, queued ⇒ pristine). ONE active run total via the partial unique index audit_archive_runs_active_uq on true WHERE state IN ('queued','running') (queued counts — no depth-N queue); audit_archive_runs_queued_idx + audit_archive_runs_reclaim_idx partial indexes serve claim and expired-lease reclaim. Deliberately NOT append-only-guarded — operational state, not chained data.

audit_archive_schedule (#1208)

The transactional due-state singleton for scheduled archival (Skip semantics). One row (singleton BOOLEAN PK CHECK), next_due_at, last_claimed_at; seeded due-now. The due-claim is UPDATE … SET next_due_at = now() + interval WHERE next_due_at ⇐ now() RETURNING — one winner, no burst catch-up (a week of downtime = ONE claim); a more = true run pulls next_due_at forward via LEAST(next_due_at, now() + catchup) so backlogs drain boundedly. Shape-compatible with #1211’s future shared fence.

chain_instances / chain_topology / chain_epochs / chain_sources / chain_heads

The C1/C3/C4 registry: immutable instance history, the active family pointer (composite FK to epochs), fenced epoch states (installing|active|closing|anchored|closed, one-open partial unique), the trusted source→instance binding the append function validates its baked literal against, and pre-created per-shard heads with the C7 watermark columns.

audit_events_v2 / audit_events_archive_v2

The audit family’s strict-from-row-one event store: BYTEA(32) hash CHECKs, RFC 8785-bounded chain_seq, five-tuple position UNIQUEs on live AND archive, composite FK → heads (structural shard validity). Hashed business columns are DERIVED from canonical_event_payload by chain_append_rows_audit; ip_address rides the pinned unhashed ingress.

chain_anchors / chain_anchor_heads

The C5 anchor store (all families): canonical manifest bytes + hash, structural monotonic sequencing via the chain_anchor_append CAS, one-way notarization_state transitions (confirmed immutable).

chain_verification_checkpoints / chain_verification_runs / chain_incidents

C6 verification state: lease/fence CAS checkpoints, INSERT-only run records, and incident latch/resolve with SPLIT grants (resolution only via canopy_chain_incident_admin). Reshaped by the #1205 MR-1 hardening migration — see the chain-v2 verification hardening section below for the as-built shapes (token-confidential leases, the family loop kind, cycle stamps, the stored detecting loop, the closed 22-kind vocabulary).

chain_append_staging

One row per accepted-but-unchained audit event: event_id (UUIDv7 PK — the payload’s hashed id and the shard-routing id, equality CHECK-enforced), the built closed-set canonical_event_payload, the build-time SHA-256 payload_digest (the divergent-replay discriminator), the pinned unhashed ingress, the router-stamped placement trio (chain_instance_id/chain_epoch/shard_id — a CHECK makes partial stamps unrepresentable), and the park quarantine (parked_at/park_reason paired by CHECK, attempts). Three partial indexes: the drainer’s exact-stamp claim, the router’s unrouted claim (each matching its query’s ORDER BY), and the parked-ops index.

chain_staging_dequeue(uuid[]) / chain_staging_park(uuid, text)

The ONLY delete path and the ONLY park path (both SECURITY DEFINER, EXECUTE to canopy_security_app). Dequeue is called inside the drainer’s append transaction so append + head advance + dequeue commit atomically (the exactly-once handoff). Park is ONE-WAY (WHERE parked_at IS NULL — it cannot touch an existing quarantine). The app role’s own grants are SELECT + INSERT + UPDATE on the three routing-stamp columns ONLY: payload, digest, unhashed, staged_at, event_id, AND the park columns are all immutable to the runtime by column-grant omission — so the runtime can quarantine a row but structurally cannot clear the quarantine.

audit_events_v2_event_id_uq

The PERMANENT replay identity (Amendment 7): a UNIQUE expression index on the live chain table’s payload event_id. Staging rows are dequeued and inbox dedup rows are reaped after 7 days, so this index is what stops a very-late redelivery from double-chaining — the drainer probes it before locking (equal digest → silent dequeue; different → park fail-closed) and it backstops as a 23505 during the append. Pruned WITH the data: once v2 rows age into audit_events_archive_v2 the live index no longer covers them (replay horizon days, archive horizon years). v2 archival rides #1303/#1304 — #1208 archives only the v1 audit_events table.

chain_verification_checkpoints (reshape)

Gains lease_token UUID (v7-CHECKed), cycle_started_at, cycle_completed_at; loop_kind CHECK widens to ('tail','scrub','family'). The FAMILY row — (instance, family, epoch, shard 0, 'family'), PK-distinct from shard 0’s tail/scrub rows — carries NO cursor/target (CHECK-enforced): it holds the family lease, the ONE trusted_manifest_ref, and the census-cadence stamp. A CHECK matrix pins hash lengths (32 bytes), non-negative seq/fence, the target pair, target ≥ cursor, and the lease trio (owner ⇔ expires_at ⇔ token). Token confidentiality (X1): the verify role’s raw SELECT is REVOKED; it reads chain_checkpoints_verify_v (everything EXCEPT lease_token) — the only way to hold a token is to have minted it via chain_checkpoint_acquire.

chain_verification_runs (reshape)

Gains shard_id SMALLINT (NULL = family-scoped), mode CHECK ('scheduled','manual'), a bare job_id UUID (FK + CHECK land with the MR-2 jobs table), outcome/finish-pair/finish-order/rows CHECKs, and the partial index chain_verification_runs_latest_idx (WHERE mode = 'scheduled') for per-scope latest-run status reads. Direct INSERT revoked from the verify role — runs are recorded only via chain_run_record under the FAMILY lease token.

chain_incidents (reshape)

Gains detected_loop_kind (stored at latch, never inferred; 'family' ⇔ shard_id IS NULL), the closed 22-kind vocabulary as a CHECK (hash_mismatch, linkage_break, noncontiguous_seq, duplicate_seq, formula_version, payload_set_violation, derived_column_mismatch, routing_mismatch, source_mismatch, id_mismatch, genesis_mismatch, terminal_mismatch, head_regression, rows_beyond_head, missing_head, unexpected_head, missing_shard_rows, target_hash_mismatch, manifest_divergence, manifest_metadata_mismatch, boundary_unavailable, malformed_row), a bounded-evidence CHECK (JSONB object ≤ 16 KiB), and the NULLS NOT DISTINCT partial unique chain_incidents_latched_uq (race-free dedup incl. family-scoped NULL epoch/shard). Evidence split (X3): the verify role reads only the evidence-free chain_incidents_verify_v; evidence + resolution text are readable only by canopy_chain_incident_admin.

Guarded fns (old signatures DROPPED, never overloaded)

chain_checkpoint_acquire — the only row-creating, fence-raising path; bounded duration (1..=600 s), expiry computed in-fn, expiry-only takeover, required init cursor on first tail/scrub acquire, scrub cycle-start as a cursor-CAS. chain_checkpoint_advance — existing-row-only, exact-token-bound (expiry never read), cursor-monotonic; PROVES scrub cycle completion (target equality) and the manifest ref (confirmed anchor, same identity) relationally. chain_run_record — family-token-fenced run INSERT. chain_incident_latch — scope-lease-validated, idempotent under race. chain_incident_resolve — actor := session_user; requires a manual ok run of the STORED detected loop, scope-covering, newer than detection (execute: canopy_chain_incident_admin only). chain_anchor_transition_emit / _confirm — the anchor role/arm split: append + emitter edges move to canopy_chain_anchor_emitter (new NOLOGIN role); the verifier keeps submitted→confirmed only.

audit_events_archive_v2_event_id_uq

Archive-side attestation index (LIKE copies no indexes): unique expression index on the archive twin’s payload event_id, so GET /v1/security/chain/attest position resolution stays indexed across archive ∪ live.

chain_verify_jobs

The durable manual-verify job store (X4). Columns: v7-CHECKed id; the target (chain_family CHECK ('audit','fti'), fti_source CHECK ('canopy-tanf','canopy-medicaid'), paired by CHECK — fti ⇔ source present); requested_loop CHECK ('tail','scrub','family-full'); optional incident_id FK → chain_incidents (revalidation jobs); requested_by/requested_at; state CHECK ('queued','running','done','error'); the durable work definition written ONCE at first claim (chain_instance_id, chain_epoch, captured_targets JSONB — object, ≤ 64 KiB, all-or-none by CHECK; a reclaim resumes the SAME target vector, never re-captures weaker targets); the claim (claim_owner, DB-minted v7 claim_token, claim_expires_at, heartbeat_at — all four NULL together by CHECK); attempts; run_id FK → chain_verification_runs; error_code CHECK ('coverage_incomplete','verifier_error','integrity_rejected','crashed'); finished_at. The full state matrix is CHECK-enforced (running ⇔ claimed; terminal ⇔ finished; done ⇒ run; error ⇔ error code; queued ⇒ pristine). Indexes: partial queued (by requested_at), reclaim (running, by claim_expires_at), reap (terminal, by finished_at), and the NULLS NOT DISTINCT partial unique chain_verify_jobs_active_target_uq on (chain_family, fti_source) WHERE state IN ('queued','running') — ONE active job per target, the 409 verification_in_progress source.

chain_verification_status_v

Coverage + freshness for the _app status assembly: the checkpoint rows WITHOUT lease_token, lease_owner, or lease expiry — tokens never reach _app (X1), and status derivation reads stamps, never clocks.

chain_verification_runs_v

Latest-run inputs: WHERE mode = 'scheduled' ONLY (manual outcomes never feed status, X4; the predicate matches chain_verification_runs_latest_idx exactly) and the raw error diagnostic text stays out of _app.

chain_incidents_app_v

Evidence-free incidents for status + the row banner: position, kind, detected loop, state — never evidence, never resolution text (those remain canopy_chain_incident_admin-only).

chain_anchor_trusted_v

The trusted manifest: joins the FAMILY checkpoint rows' trusted_manifest_ref to chain_anchors — the join guarantees a newly confirmed but never-verifier-checked anchor authorizes nothing (X5: the family row is the ONE source). manifest_bytes is exposed for the attestation decode (manifests are publishable by design, ADR-014).

chain_verify_jobs_app_v

Token-free job polling: everything GET /v1/security/chain/verify-jobs/{id} serves, NONE of the claim fields — granting the raw table would leak claim_token to every replica (the X1 class, symmetric); the only way to hold a claim token is to have minted it via chain_job_claim.

Runs mode ⇔ job constraint

ALTER TABLE chain_verification_runs: CHECK (mode = 'manual') = (job_id IS NOT NULL) + the job_id FK → chain_verify_jobs (deferred from MR-1, which introduced the bare column before this table existed). A manual run structurally requires its job; a scheduled run structurally refuses one.

Guarded job fns (SECURITY DEFINER, owner-transferred, PUBLIC revoked; no raw DML grants to anyone)

chain_job_enqueue(family, source, loop, requested_by, incident, max_queued) → (job_id, created) — idempotent on the active target (existing job → its id + false → 409); queue cap RAISEs (→ 503); a revalidation job must reference a same-family incident and cover its detected loop [execute: canopy_security_app — the API handler’s arm]. chain_job_claim(family, source, worker, claim_secs) → SETOF jobs — target-scoped, FOR UPDATE SKIP LOCKED, oldest queued or expired-claim reclaim; mints the claim token (claimant-only). chain_job_capture(id, token, instance, epoch, targets) — writes the work definition only when unset (first capture wins; a reclaimer resumes). chain_job_heartbeat(id, token, claim_secs) — token-validated claim extension. chain_job_finalize(id, token, state, run, error_code) — token-validated terminal transition, nulls the claim; committed in the SAME transaction as chain_run_record. chain_job_reap(older_than_days) → bigint — terminal-only, 7-day floor in-fn; rows referencing a still-latched incident are exempt [claim/capture/heartbeat/finalize/reap execute: canopy_chain_verify]. Plus the SAME-SIGNATURE chain_run_record replace (explicitly not an overload): a manual run now requires its RUNNING, family-matched job.