Database Strategy

Built vs target-state

Built today: per-service logical Postgres databases (one Postgres process on dev-2 holds them), opaque *_locator cross-service references, and forward-only Goose migrations. Tenancy is enforced in application code against locator columns.

🚧 Target-state - not yet built: the Postgres RLS floor (FORCE ROW LEVEL SECURITY), the pii-vault / field-level encryption, and the Snowflake BI/CDC pipeline. These are design-stage (see Data Protection and Designs → Access Control); there is no RLS migration, column encryption, or warehouse in the running stack. Sections below are labelled accordingly.

Position

Olly treats persistent data as owned by exactly one service, partitioned across per-service Postgres logical databases that share a single Postgres process but never share tables. Cross-service reads happen only via APIs or Kafka projections keyed on locator IDs. The intended end-state adds tenancy enforced at the row level by Postgres RLS as a floor underneath an OPA-driven application policy, and sensitive attributes behind a dedicated pii-vault rather than in the originating service's columns - but today (see banner) tenancy is enforced in application code and PII is stored in plain columns.

Service-owned databases (per-service Postgres; cross-service refs only via locator IDs)

Each Go service (claims, eligibility, enrollment, billing, provider, notifications, policy-admin, triage, care, group-scheme, broker-api, consent, documents) owns one logical Postgres database with its own migration history. Within each database, tables are namespaced under a per-service schema created by the first migration (claims.claims, enrollment.policies, …). Today all services connect as the same superuser (olly); the intended per-service role that holds DML rights only on its own schema is 🚧 target-state, not yet enforced.

Cross-service references use opaque locator strings - policy_locator, party_locator, claim_locator, scheme_locator - never foreign keys to another service's tables. Consequences:

• A service can be rebuilt, migrated, or sharded without coordinating with consumers.
• Eventual consistency is the default; consumers needing a local view materialise it via Kafka projections (e.g. eligibility.member_coverage projects enrollment events, not a join).
• Cross-service aggregations are intended to run in a downstream BI layer rather than via federated SQL. 🚧 Target-state - not yet built: there is no warehouse today (no CDC, no Snowflake/BigQuery); the warehouse-vs-lakehouse choice is an open decision. Any reporting/actuarial query today runs against the transactional Postgres.

A single Postgres process holds every service's logical database; logical isolation (separate databases + schemas) is what the contract relies on, not physical separation. This is one Postgres container on dev-2, not a managed cluster.

Multi-tenant isolation

🚧 Target-state - not yet built

This whole section describes the designed two-layer isolation model. Neither layer is live today. There is no RLS in the schema (a grep for ROW LEVEL SECURITY across every migration returns zero), and no OPA sidecar is deployed. Today tenancy is enforced only in application code, which filters queries by the JWT's locator columns (party_locator / org_locator / scheme list). A missing WHERE in application code is therefore not caught by a database floor. The model below is the intended end-state; see Designs → Access Control.

The designed model enforces tenancy (member → org/scheme, broker, provider network) at two levels that compose as an AND - a layer can only ever remove access, never grant it:

1. Application PEP (OPA). Each service would consult an OPA sidecar for the authoritative row scope and field-level decisions, derived from JWT claims (party_locator, org_locator, scheme list, role). OPA owns context-dependent rules - purpose, consent, time, break-glass, field masking.
2. RLS floor. Every tenant-scoped table would carry the relevant tenancy column(s) and a FORCE ROW LEVEL SECURITY policy keyed on a per-transaction SET LOCAL GUC. The service sets the GUC at checkout from the pool; any query that forgets a WHERE still cannot return rows outside the tenant.

RLS is intentionally coarser than OPA: it captures what must survive arbitrary application bugs (tenant key, org/scheme ownership) and nothing else. In the target-state the application role is not the table owner and does not hold BYPASSRLS. Invariant: every OPA allow implies an RLS allow (π ⟹ ρ); a periodic canary asserts this. See ABAC for the full model.

PII custody

🚧 Target-state - not yet built. PII is plaintext today.

The pii-vault described below does not exist in the running stack (no pgcrypto/KMS/vault code; a grep for encrypt|kms|vault|aes|pgcrypto across policy-admin and consent returns zero). Party PII (name, email, phone, address, DOB) is stored as plaintext columns in policy_admin.parties, and List() even runs a plaintext ILIKE search on name/email. At-rest protection today relies only on disk/database-level encryption, not column or field-level application encryption. This matches Data Protection; the design below is aspirational.

In the designed end-state, PII at or above sensitivity pii (identity, clinical free-text, financial identifiers, geo/device) would not be stored in service tables as plaintext or as reversibly-encrypted column values. Service rows would hold three derivatives only:

• An opaque token (V(x)) - random per write, so duplicate values are uncorrelated at the DB layer.
• A lossy display surrogate (σ(x), e.g. "Bob S.") for list rendering without a vault round-trip.
• A keyed blind index (β(x) = HMAC_k(x)) for equality and prefix lookup.

Plaintext would be materialised only on the detail path via pii-vault.detok with a reason; the vault gates on OPA's field-level decision and appends a compliance-only audit row. At-rest the vault uses envelope encryption - per-record AES-256-GCM DEKs wrapped by a KEK held in OpenBao Transit, with Shamir k-of-n on unseal - so no single admin can bulk-extract plaintext. Detail in PII-at-Rest. List/search latency is independent of PII cardinality; only the detail view pays a vault round-trip.

Migrations and schema evolution (numbered Goose files; forward-only intent; multi-stage for breaking changes)

Each service carries a migrations/ directory of numbered Goose SQL files (with Up/Down blocks), tracked in a goose_db_version table inside that service's schema; once merged, files are never edited. The contract for any schema change:

• Forward-only. Down migrations exist for local dev but are not used in CI/CD. A bad migration is fixed by a new forward migration.
• Backward-compatible. New schema must work with the previous release. Breaking changes sequence as expand → migrate data → contract across at least two deploys.
• Online-safe. No long-blocking ALTERs on hot tables; new indexes are CONCURRENTLY; column drops are decoupled from code drops.
• Catalogued. Schema is ingested into OpenMetadata; ontology concept descriptions are written onto the physical columns so the catalog stays the canonical view of "what exists and what it means".

Backup & restore (snapshot cadence + retention + restore-drill cadence)

🚧 Target-state - not yet built

The dev stack runs a single Postgres container on dev-2 with no managed snapshot, WAL-archiving, or automated backup policy. The cadence below is the intended production posture (a managed Postgres such as Cloud SQL provides the snapshot/PITR primitives - see the Production topology); none of these drills run today.

• Snapshots. Managed Postgres snapshots daily, retained 30 days; WAL archiving enables point-in-time recovery within the window.
• Logical exports. pg_dump per service weekly, KMS-encrypted, stored off-cluster for 90 days. Used for cross-environment seeding and audit-window archive.
• Restore drills. Quarterly drill restores one randomly chosen service DB into an isolated namespace and verifies migrations replay, the service starts, and a sample read path returns expected rows. A failed drill blocks the next quarterly release.
• Vault store. The pii-vault ciphertext store backs up on the same cadence; the KEK is backed up via OpenBao's Shamir-share recovery procedure, never as a flat secret.

Performance posture (planned pooling + read replicas; slow-query alerting)

• Pooling. 🚧 Target-state - not yet built: PgBouncer in transaction-pooling mode is intended to front each service (pools sized to expected concurrent transactions, not peak request load). There is no PgBouncer in the running stack today; services connect to Postgres directly via their driver pools.
• Read replicas. Reporting and large list-scope queries will move to per-service read replicas once sustained load warrants it (planned, not yet provisioned). The pii-vault stays primary-only by design.
• Slow-query alerting. (Intended) pg_stat_statements exports to Prometheus; queries above per-service p99 budgets fire Grafana alerts; Tempo traces link the slow span back to the originating request. The Prometheus/Grafana/Tempo stack exists on dev-2, but the pg_stat_statements export and query-budget alerts are not yet wired.
• Index discipline. New indexes are justified in the migration commit and reviewed against query plans on a representative dataset before merge.

What we explicitly do NOT do

• No cross-service joins. A claim row is not joined to a policy row in SQL - claims calls the enrollment API or reads a local projection. No shared "common" schema.
• No shared mutable state. No service writes into another service's tables, even for "convenience" reads.
• No column-level PII (target-state). In the designed end-state, sensitive attributes never live as plaintext or as reversibly-encrypted columns and the vault is the only system-of-record for cls ≥ pii. Today PII is stored plaintext (see the PII custody banner above); this is a goal, not a live control.
• No reading another service's database directly. Operators do not run reporting queries against a service's primary; ad-hoc reads use the catalog and the service's read API. (The Kafka → warehouse BI load path is 🚧 target-state - there is no warehouse yet.)
• No reversible migrations in production. The intent is that rollbacks are forward migrations rather than Goose Down blocks (the Down blocks exist for local dev). There is no production deploy pipeline today; deploys are docker-compose on the dev VMs.
• No bypass of RLS (target-state). The designed model has no service role holding BYPASSRLS and the application role is not the table owner, with incident operators going through an audited break-glass path. RLS is not implemented today (see the Multi-tenant isolation banner), so this control is not yet in force.