Data model

How data is shaped, owned, and accessed across the platform.

The rules

1. One Aurora cluster, many schemas. Every service owns a Postgres schema (identity.*, clinical.*, payments.*, etc.).
2. No cross-schema reads or writes. A service accesses other services’ data via HTTP, never via SQL across the schema boundary.
3. Every table has brand_id NOT NULL. Enforced by the migration-brand-id-required convention check.
4. Every state-changing service has an audit_log table. Enforced by audit-table-required.
5. Foreign keys exist within a schema, not across schemas. Cross-service relationships are by ID + the platform’s HTTP boundary.
6. The ledger (accounting.transactions) is append-only. Postgres triggers reject UPDATE and DELETE.

Schemas and their owners

Cross-service references

When a row in service A needs to point at something owned by service B:

• Store the foreign ID as a plain string (user_id, customer_profile_id, affiliate_id).
• Don’t add a foreign key constraint across schemas.
• When you need the related entity, call B’s HTTP API.

Example: clinical.biomarkers.user_id holds a string. To get the user’s name, services/clinical calls services/identity via the SDK.

Migrations

Drizzle Kit per service. Numbered SQL files in services/<svc>/migrations/. Forward-only. Expand–contract pattern for any destructive change. See Zero-downtime migrations.

Audit log

Every service owns its audit table. Append-only (no UPDATE or DELETE grants). Schema:

CREATE TABLE <service>.audit_log (
  id              UUID PRIMARY KEY,
  occurred_at     TIMESTAMPTZ NOT NULL,
  actor_type      TEXT NOT NULL,
  actor_id        TEXT NOT NULL,
  actor_brand_id  TEXT,
  event_type      TEXT NOT NULL,
  entity_type     TEXT,
  entity_id       TEXT,
  brand_id        TEXT NOT NULL,
  request_id      TEXT,
  details         JSONB
);

See Audit and PHI.

Related

• System overview
• Zero-downtime migrations
• Brands and multi-tenancy
• Audit and PHI
• Service architecture