canopy-web Data Model

On this page

Cross-link: canopy-web API Reference · Source: migrations/

Tables

Table Purpose

sessions

tower-sessions PostgreSQL store (ADR-009 — never MemoryStore). One row per active server-side session, keyed by the opaque session id; data is the serialized session blob (BYTEA) and expiry_date bounds the 30-minute TTL. Schema is fixed by tower-sessions-sqlx-store. The applicant BFF (canopy-portal) does not share this tower-sessions Postgres schema — per ADR-026 its sessions live in Redis (opaque tokens), so canopy-portal has no sessions table at all. Garbage-collected by the store’s own expiry sweep, not by a domain janitor.

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.

composition_documents (epic &51, ADR-022)

Override storage for the worker-portal composition runtime (ADR-021). One row per (jurisdiction_id, layer, scope_key, surface); patch_ops is the override body — an RFC 6902 JSON Patch op list for case_detail / sign_in, or a user_delta_v1 envelope for the three dashboard surfaces (ADR-024). The three DB-backed override layers (user delta, role override, jurisdiction_live) share this one table per ADR-022; the loader fetches all layers in one indexed query and replays the patch lists against the jurisdiction TOML baseline in jurisdiction_live → role → user order. created_by is the actor’s UUID. created_at / updated_at default to clock_timestamp(). Two Postgres ENUM types back the constrained columns: composition_layer (user / role / jurisdiction_live) and composition_surface (worker_dashboard / supervisor_dashboard / analyst_dashboard / case_detail / sign_in).

composition_documents_archive (epic &51, ADR-022)

Explicit-archive history for promoted/superseded composition overrides. Created via LIKE composition_documents INCLUDING DEFAULTS INCLUDING IDENTITY (columns + defaults only) plus archived_at (default clock_timestamp()) and archived_by (UUID). It deliberately does not copy the UNIQUE (jurisdiction_id, layer, scope_key, surface) constraint or the lookup index (INCLUDING ALL is avoided) — inheriting the uniqueness would forbid archiving the same composition tuple more than once over a jurisdiction’s lifetime, conflicting with the 1-year override-layer audit retention (ADR-022 §Schema note).

Relationships

canopy-web’s tables are independent infrastructure tables — there are no intra-database foreign keys between them. composition_documents_archive mirrors composition_documents by LIKE derivation (not by FK).

Diagram

Cross-service FKs (ADR-001 boundary)

Per ADR-001, canopy-web holds no Postgres-level foreign keys to other services, and in fact holds no cross-table FKs at all — every table here is standalone BFF infrastructure. The UUID columns that reference identities elsewhere are application-level references only and are never DB-enforced: composition_documents.jurisdiction_id (jurisdiction config), composition_documents.created_by / composition_documents_archive.archived_by (Keycloak subject UUIDs — workers are not canopy-persons rows). The sessions table holds opaque session keys, not person/household IDs. canopy-web depends on the other services purely over HTTP (it is a BFF), so no cross-DB relationship exists at the schema layer.

Retention

canopy-web does not hold FTI or PHI; no Pub 1075 / HIPAA retention floor applies at this layer. The data it does hold is BFF infrastructure:

  • sessions — transient; rows are deleted on logout or by the tower-sessions expiry sweep (30-minute TTL per ADR-009). No regulatory floor.

  • event_outbox — transient publish buffer; published rows are swept by the 7-day outbox janitor. No regulatory floor (the durable system-of-record is the consuming service plus canopy-security’s audit log).

  • composition_documents / composition_documents_archive — every override write emits a JWS-signed AuditEvent per ADR-014, and the override-layer audit events carry a uniform 1-year retention per ADR-022. Archiving is explicit (Studio promote-merge), not migration-driven destructive change (ADR-016 forward-only).

Indexes

  • sessions_expiry_idx — sessions (expiry_date); supports the tower-sessions expiry sweep.

  • event_outbox_* — the three generated partial outbox indexes (drainer hot path, lease-aware claim, ADR-039 held-skip); documented once in the data-models index

  • composition_documents_lookup_idx — composition_documents (jurisdiction_id, surface, layer, scope_key); the loader’s single-query fetch of all override layers for a surface.

  • composition_documents_archive_lookup_idx — composition_documents_archive (jurisdiction_id, surface, archived_at DESC); most-recent-first history lookup per jurisdiction/surface.

  • UNIQUE (jurisdiction_id, layer, scope_key, surface) on composition_documents — enforces one live override row per layer tuple (deliberately not inherited by the archive table).

Migration files

  • 20260401000000_create_sessions_table.sql — tower-sessions PostgreSQL store (ADR-009); canopy-web only. canopy-portal does not share this schema — its sessions live in Redis per ADR-026.

  • 20260508000000_create_event_outbox.sql + 20260518004851_event_outbox_lease_columns.sql + 20260713000000_event_outbox_hold.sql — the generated ADR-039 single-sourced outbox migrations (cargo xtask outbox-migrations --write); documented once in the data-models index

  • 20260522002115_create_composition_documents.sql — epic &51 / ADR-022 composition override storage: composition_layer + composition_surface ENUMs, composition_documents + composition_documents_archive tables, lookup indexes (closes #489).

All migrations are forward-only per ADR-016.

Edit this page · default