Data Model: Security & Compliance

On this page

Overview

The security service is CRAIG’s compliance, audit, and platform-integrity backbone (port 8007, database craig_security). Its tables back the audit trail (45 CFR § 95.621), the security-review and NIST SP 800-53 control registries, data archiving/retention (2 CFR § 200.334), the administrative-unit registry, breach detection (rules + alerts), partner + worker identity (ADR-017 / ADR-018 / ADR-026), and the outbox/inbox/dead-letter durability + idempotency machinery (ADR-022). This page catalogs all 19 tables (#1173 originally counted 17; the epic &77 event_inbox_collisions
request_claims and epic &78 schema_compat_floor arrived since, and the orphaned idempotency_responses was dropped by &77 BF; source of truth: services/craig-security/migrations/). The schema carries exactly four foreign keys — review_evidence.review_id → security_reviews.id, security_alerts.rule_id → detection_rules.id, partner_api_keys.partner_id → partners.id, and partner_signer_keys.partner_id → partners.id; the durability tables' cross-table links are logical, by envelope_id.

The endpoint catalog for this service is API: Security & Compliance; its state machines are in State Machines; its events are in ADR-003: RabbitMQ Topology (the wildcard # audit subscriber).

Tables

Table Purpose

audit_log

Append-only audit trail of all data access + mutation (45 CFR § 95.621); the wildcard # subscriber writes here, plus actor (on-behalf-of) columns.

security_reviews

Biennial / incident / ad-hoc security reviews (45 CFR § 95.621(f)) — findings + remediation plans; soft-deletable.

nist_controls

NIST SP 800-53 control mapping + implementation-status tracking; soft-deletable, partial-unique on control_id while active.

archive_records

Data-archiving + purge tracking (2 CFR § 200.334) — retention windows + purge eligibility per source service/table.

admin_unit_registry

Registry of administrative units (states / counties / districts) with jurisdiction + FIPS mappings; partial-unique on (name, jurisdiction) while active. version BIGINT is the ADR-062 §G3 If-Match CAS counter (the update SET clause overwrites every field).

detection_rules

Breach-detection rule registry (six pattern types over audit_log); soft-deletable (active = false), scanned by the 60 s scheduler + POST /v1/security/detection/run. version BIGINT is the ADR-062 §G3 If-Match CAS counter (a stale replay cannot re-enable a disabled rule).

security_alerts

Alerts fired by breached detection rules — FK to detection_rules; at most ONE unacknowledged alert per rule (uq_security_alerts_standing_rule, #1172).

dead_letter_audit

Forensic capture of RabbitMQ DLX-dead-lettered messages for causal-chain reconstruction
threshold alerting (ADR-022 Step 4).

event_outbox

Transactional outbox for this service’s own staged events (ADR-022), published-only 30-day retention (ADR-058).

event_inbox

Idempotent inbox for subscription handlers — the ADR-062 single-transaction attempt machine (claim + effects + stamp atomic; supersedes ADR-022 §D3.2’s three-statement shape), 31-day retention (ADR-058).

request_claims

ADR-062 §B convert-class idempotency claims (claim-first inside the domain transaction); pruned hourly on the fleet-wide CRAIGREQUEST_CLAIMSWINDOW_DAYS horizon.

schema_compat_floor

ADR-063 rollback floor singleton — the minimum binary version the schema still serves; destructive contract migrations bump it in the same file. Written only by the migration gate; the verify-only boot SELECTs it.

event_inbox_collisions

Identity-collision ledger (ADR-062 §A-4): reused envelope ids with divergent (source, type, payload_hash) triples, recorded without touching the canonical claim.

partners

Partner organization registry (hospital / school / LE / other) for edge identity (ADR-017); active → suspended → deleted soft-delete; per-partner rate limit. version BIGINT is the ADR-062 §G3 body-version CAS counter (suspension cycles are ABA-vulnerable).

partner_api_keys

SHA-256-hashed partner API credentials (FK → partners) — prefix display, revocation, 1-year expires_at rotation (F-009); hot-path /partners/verify lookup by hash.

partner_signer_keys

Per-PERSON ES256 signer keys for mandated-reporter detached JWS (ADR-018; FK → partners); pending → approved → revoked, globally-unique JWS kid lookup.

worker_identities

IdP-neutral worker identity cache (ADR-026) — lazy-populated from JWT claims by the auth-after middleware; trigram autocomplete for BFF reassignment.

review_evidence

Uploaded evidence objects attached to security reviews (FK → security_reviews) — object-store pointers, never file bytes.

major_changes

CCWIS major-change monitoring (staffing / org / system / policy) with ACF-reporting flags; soft-deletable.

Key columns

Source of truth: services/craig-security/migrations/. Every PK is id UUID DEFAULT uuidv7() unless noted (ADR-002).

audit_log

Column Type Notes

timestamp

TIMESTAMPTZ

NOT NULL, DEFAULT now()

user_id / user_role

TEXT

acting worker identity (display)

service

TEXT

originating service

action

TEXT

e.g. read / create / update / delete

resource_type

TEXT

domain resource class

resource_id

UUID

nullable

details

JSONB

nullable structured context

ip_address

TEXT

nullable

success

BOOLEAN

NOT NULL, DEFAULT true

actor_service

TEXT

NOT NULL, DEFAULT 'unknown' — on-behalf-of service (ADR-028)

actor_user_sub

UUID

nullable — on-behalf-of subject

security_reviews

Column Type Notes

review_type

TEXT

NOT NULL — the closed ReviewType vocabulary (biennial / incident / ad_hoc / access_review), API-enforced at create with the typed 400 INVALID_ENUM_VALUE (#1288)

scheduled_date

DATE

NOT NULL

completed_date

DATE

nullable

reviewer

TEXT

NOT NULL

findings / remediation_plan

JSONB

nullable

status

TEXT

NOT NULL, DEFAULT 'scheduled'

active / deleted_at

BOOLEAN / TIMESTAMPTZ

soft-delete (active partial index)

nist_controls

Column Type Notes

control_id

TEXT

NOT NULL, partial-UNIQUE while active

control_family / control_name

TEXT

NOT NULL

implementation_status

TEXT

NOT NULL, DEFAULT 'planned'

implementation_notes

TEXT

nullable

evidence_key

TEXT

nullable (object-store pointer)

last_assessed

DATE

nullable

assessed_by / assessed_by_name

TEXT

nullable (sub + denormalized display name)

active / deleted_at

BOOLEAN / TIMESTAMPTZ

soft-delete

archive_records

#1129 (ADR-058): the durable LOCAL LEDGER of the archive-then-prune engine — one row per archive batch, written inside the batch’s prune transaction (D8). Also holds craig-rules' fleet BOOKKEEPING copies (validated from rules.evaluations_archived, D16) — those are outside security’s purge authority (rules' own archive_ledger drives their lifecycle).

Column Type Notes

source_service / source_table

TEXT

NOT NULL — what was archived

record_count

INTEGER

NOT NULL

archived_at

TIMESTAMPTZ

NOT NULL, DEFAULT now()

retention_until

DATE

NULLABLE since #1129: NULL = awaiting the DFCS records-retention schedule (⁂ #1073); purge refuses NULL/future/held at EVERY destructive statement (D14)

purge_eligible

BOOLEAN

NOT NULL, DEFAULT false — purge-time audit fact ONLY (set true at purge; the D14 predicate is the purge authority). #1145 retired the list filter, the web badge, and the idx_archive_purge partial index built on it

purged_at / purged_by

TIMESTAMPTZ / TEXT

the D15-attributed purge stamp (partial index WHERE purged_at IS NULL)

archived_by / archived_by_name

TEXT

actor sub or system:retention; display name stays NULL at engine inserts

legal_hold

BOOLEAN

NOT NULL, DEFAULT false — never purged while true (D14)

data_key / manifest_key

TEXT

full object keys (NULL only on pre-engine legacy rows)

data_sha256

TEXT

sha256 of the data object’s exact bytes

store_endpoint / store_bucket

TEXT

the D9 store identity at archive time

first_record_id / last_record_id

UUID

the batch’s [min, max] row-id range (D17 candidate lookup; min/max never inverts)

first_record_at / last_record_at

TIMESTAMPTZ

oldest/newest archived row by the age column

hot_window_days

INTEGER

the window in force when the batch was cut

admin_unit_registry

Column Type Notes

name

TEXT

NOT NULL

unit_type

TEXT

NOT NULL (state / county / district)

fips_code

TEXT

nullable

state_fips

TEXT

NOT NULL

jurisdiction

TEXT

NOT NULL — partial-UNIQUE (name, jurisdiction) while active

active

BOOLEAN

NOT NULL, DEFAULT true

created_at

TIMESTAMPTZ

NOT NULL, DEFAULT now()

detection_rules

Column Type Notes

rule_name

TEXT

NOT NULL, UNIQUE

description

TEXT

NOT NULL

rule_type

TEXT

NOT NULL, CHECK — failed_auth / bulk_access / after_hours_access / privilege_escalation / data_export / account_lockout

threshold / window_minutes

INTEGER

NOT NULL — fire when the window count ≥ threshold

severity

TEXT

NOT NULL, CHECK (info / warning / critical)

enabled

BOOLEAN

NOT NULL, DEFAULT true — scan-participation switch

notify_webhook

TEXT

nullable — critical-severity push target

active

BOOLEAN

NOT NULL, DEFAULT true — soft-delete flag

security_alerts

rule_id → detection_rules.id is one of the schema’s four foreign keys (see Overview). #1172: uq_security_alerts_standing_rule (partial UNIQUE on rule_id WHERE NOT acknowledged) DB-enforces the #1156 one-standing-alert-per-rule invariant — the scan’s insert uses it as an explicit ON CONFLICT arbiter, so overlapping scans produce exactly one alert. Legacy NULL-rule_id rows are outside the constraint (partial-index NULLs are distinct).

Column Type Notes

rule_id

UUID

FK → detection_rules.id; nullable (legacy rows only — the store requires it since #1172)

rule_name / severity / description

TEXT

NOT NULL (severity CHECK: info / warning / critical)

details

JSONB

nullable — window count context

acknowledged

BOOLEAN

NOT NULL, DEFAULT false — partial-unique key domain

acknowledged_by

TEXT

nullable — operator sub, or the attributed migration:standing-alert-dedupe (#1172) maintenance actor

acknowledged_at

TIMESTAMPTZ

nullable

created_at

TIMESTAMPTZ

NOT NULL, DEFAULT now()

dead_letter_audit

Column Type Notes

envelope_id

UUID

NOT NULL — correlates to event_outbox.id / event_inbox.envelope_id

source_service / event_type

TEXT

NOT NULL

original_queue

TEXT

NOT NULL

envelope

JSONB

NOT NULL — the full dead-lettered message

dlq_received_at

TIMESTAMPTZ

NOT NULL, DEFAULT now()

occurrence_token

TEXT

NULLABLE (#1181): per-occurrence idempotency token (dlxcap:/xdeath: grammar); PARTIAL UNIQUE WHERE occurrence_token IS NOT NULL is the redelivery-dedup arbiter — NULL = tokenless/historical, recorded undeduped

occurred_at

TIMESTAMPTZ

NULLABLE (#1181): when the occurrence happened (when derivable); the #1156 threshold window keys on LEAST(COALESCE(occurred_at, dlq_received_at), dlq_received_at) — clamped occurrence time, never receipt alone

event_inbox

PK is envelope_id UUID (the dedup key from the envelope), not a generated id.

Column Type Notes

envelope_id

UUID

PK — dedup key

source_service / event_type

TEXT

NOT NULL

received_at

TIMESTAMPTZ

NOT NULL, DEFAULT now()

processed_at

TIMESTAMPTZ

nullable — set when the handler returns Ok

error_count

INT

NOT NULL, DEFAULT 0

last_error

TEXT

nullable

failed_at

TIMESTAMPTZ

nullable — permanently failed (DLX-surfaced only after a CONFIRMED surface, ADR-062)

attempt_started_at

TIMESTAMPTZ

nullable — in-flight marker

payload_hash

TEXT

nullable — hex SHA-256 of the payload, the third leg of the ADR-062 identity triple (#1053); NULL = legacy pre-migration claim (backfilled at the next success stamp; NOT NULL ratchet #1273)

event_inbox_collisions

Present in ALL 8 stateful services (like event_inbox; documented here per the transport-table catalog convention). A reused envelope id whose (source_service, event_type, payload_hash) triple diverges from the canonical event_inbox claim is recorded here and nacked without requeue (ADR-062 §A-4, #1053); the canonical row is never mutated. Any row is an alert — the report-only inbox_identity_collisions_present watchdog fires on presence.

Column Type Notes

envelope_id

UUID

part of the composite PK — the reused id

observed_source / observed_type

TEXT

NOT NULL — the collider’s identity

observed_payload_hash

TEXT

NOT NULL — part of the composite PK with the two above

first_seen

TIMESTAMPTZ

NOT NULL, DEFAULT now() — the deterministic occurred_at of the collision’s DLX surface

occurrences

INT

NOT NULL, DEFAULT 1 — repeat observations of the same collider bump this

partners

Column Type Notes

name

TEXT

NOT NULL

kind

TEXT

NOT NULL, CHECK (hospital / school / le / other)

status

TEXT

NOT NULL, DEFAULT 'active', CHECK (active / suspended / deleted) — suspended blocks /partners/verify but preserves the row for audit/reinstatement

contact_email / contact_phone

TEXT

nullable

rate_limit_rpm

INTEGER

NOT NULL, DEFAULT 60, CHECK > 0 — mirrors intake’s per-partner default

created_by

UUID

NOT NULL

partner_api_keys

Plaintext keys are never stored — the raw key is returned exactly once at issue time. SHA-256 (not argon2/bcrypt) is deliberate: keys are 256-bit high-entropy random tokens, not passwords, and the hash must support the indexed hot-path /partners/verify lookup (partial index on unrevoked key_hash).

Column Type Notes

partner_id

UUID

FK → partners.id, ON DELETE RESTRICT

key_hash

TEXT

NOT NULL, UNIQUE — SHA-256 hex of the raw key

key_prefix

TEXT

NOT NULL — first 8 chars, safe to display for revocation UX

label

TEXT

nullable

expires_at

TIMESTAMPTZ

NOT NULL, DEFAULT now() + 1 year (F-009 rotation) — expired keys fail verify even if unrevoked

revoked_at / last_used_at

TIMESTAMPTZ

nullable

created_by

UUID

NOT NULL

partner_signer_keys

ADR-018: one partner carries many signer keys, ONE PER PERSON — mandatory-reporting statute identifies the individual as the accountable reporting party. Scoped to the partner (not the api-key) so credential rotation never orphans signer keys.

Column Type Notes

partner_id

UUID

FK → partners.id, ON DELETE RESTRICT

user_identifier

TEXT

NOT NULL — the person (email, employee id, …)

display_name

TEXT

NOT NULL

public_key_jwk

JSONB

NOT NULL (RFC 7517)

algorithm

TEXT

NOT NULL, DEFAULT + CHECK 'ES256'

key_id

TEXT

NOT NULL, UNIQUE — the JWS header kid; intake’s verify path looks up by kid (partial index WHERE approved AND unrevoked)

status

TEXT

NOT NULL, DEFAULT 'pending', CHECK (pending / approved / revoked) — admin review gates approval

approved_by / approved_at

UUID / TIMESTAMPTZ

nullable approval stamp

revoked_by / revoked_at

UUID / TIMESTAMPTZ

nullable revocation stamp

expires_at / last_used_at

TIMESTAMPTZ

nullable

created_by

UUID

NOT NULL

worker_identities

PK is sub UUID (the IdP subject), not a generated id. Lazy-upserted from JWT claims by the auth-after middleware on every authenticated craig-security request (ADR-026).

Column Type Notes

sub

UUID

PK — IdP subject

preferred_username

TEXT

NOT NULL — deliberately non-unique (sub is the identity key; username uniqueness is the IdP’s contract, not CRAIG’s invariant); trigram-indexed

display_name

TEXT

NOT NULL — trigram-indexed for GET /v1/security/workers?search=

email

TEXT

nullable

attrs

JSONB

NOT NULL, DEFAULT '{}' — custom IdP claims (assigned_units / region / …) for per-jurisdiction authz expressions, no schema change needed

first_seen_at / last_seen_at

TIMESTAMPTZ

NOT NULL, DEFAULT now()

review_evidence

Column Type Notes

review_id

UUID

NOT NULL, FK → security_reviews.id

file_name / content_type

TEXT

NOT NULL

object_key

TEXT

NOT NULL — object-store pointer (bytes live in the store, never here)

uploaded_by

TEXT

NOT NULL

major_changes

Column Type Notes

change_type

TEXT

NOT NULL, CHECK (worker_assignment / supervisor_change / org_restructure / system_upgrade / policy_change)

description / affected_scope

TEXT

NOT NULL

effective_date

DATE

NOT NULL

reported_to_acf

BOOLEAN

NOT NULL, DEFAULT false

reported_date

DATE

nullable

reported_by / reported_by_name

TEXT

NOT NULL / nullable (sub + denormalized display name)

active / deleted_at

BOOLEAN / TIMESTAMPTZ

soft-delete

Related

  • API: Security & Compliance — the endpoint catalog.

  • ADR-022 — the outbox/inbox/DLQ durability model event_inbox + dead_letter_audit implement.

  • ADR-028 — the on-behalf-of identity the actor_* audit columns capture.

Edit this page · latest