Storage
PostgreSQL 18 holds everything except files. Study documents move between the browser and S3 over short-lived presigned links (the API never proxies a document's bytes; it records its name, size, content type and SHA-256), and export files are written to S3 and streamed to the person who asks, with a checksum. The audit ledger's daily anchors are written to S3 under Object Lock.
The schema is plain SQL: 73 migrations, one concern for each file, applied by npm run db:migrate as the owner role (The database). There is no ORM-generated schema, and no data is seeded: a migrated database holds no organization, no person and no study.
The shape of the data
The core of the 72-table schema. Identity tables anchor the organization; study tables hold the design as versions and frozen snapshots; clinical tables hold captured data, where every value ever entered is its own row; the signature and audit ledgers are append-only. Click a table to see its key columns and relationships.
72 tables, in five groups:
| Group | Tables |
|---|---|
| Identity and access (16) | organizations, platform_users, org_memberships, user_role_assignments, user_sessions, otp_challenges, rbac_roles, rbac_role_revisions, access_exceptions, org_access_revisions, study_memberships, site_memberships, site_delegations, invitations, invitation_tokens, organization_onboarding |
| Study and design (21) | studies, study_state_requests, study_versions, config_snapshots, amendments, draft_revisions, draft_revision_entries, version_signoffs, uat_cycles, uat_cycle_issues, uat_cycle_corrections, study_forms, form_version_snapshots, study_visits, form_visit_mappings, study_rules, sdv_plan_items, sites, library_forms, ai_form_generations, library_form_generations |
| Clinical data (21) | study_subjects, subject_visit_instances, subject_contacts, subject_reconsents, subject_version_transitions, form_records, item_values, event_records, event_follow_ups, event_reviews, event_reconciliations, queries, query_thread_entries, signatures, signature_supersessions, source_verifications, protocol_deviations, documents, document_versions, export_jobs, id_sequences |
| Audit (6) | audit_events (partitioned by month), audit_outbox, audit_chain_heads, audit_chain_checkpoints, audit_chain_anchors, audit_chain_breaks |
| Operations (8) | mail_outbox, mail_events, notifications, backup_runs, summary_subject, summary_site, summary_dirty, schema_migrations |
Three design choices are worth calling out:
- A design is a document; data is rows. A form's design is a JSON document the builder authors, frozen into immutable snapshots (
form_version_snapshots, and the whole design intoconfig_snapshotswith a content hash) when a version is submitted. A captured value is a row initem_values, one row for each value ever entered: a change is a new row that supersedes the old one, never an update (ADR 0015).form_recordsis only the header of a form's data. - The audit trail, not the value, is the change history, and the value chain is its own evidence: every value that was ever saved is still there.
- Dashboards read summary tables,
summary_subjectandsummary_site, that the worker recomputes from the rows that changed (summary_dirty) and re-checks against the raw tables; a disagreement is rebuilt and alerted (Architecture).
Tenant isolation
Tenancy is pooled, with PostgreSQL row-level security, not a schema or a database for each tenant (ADR 0002).
- 66 of the 72 tables carry an organization (
org_id) and a row-level security policy keyed tocurrent_org(), a setting that the application binds from the authenticated session on every connection checkout and clears on release. Nothing in the application filters by organization in place of a policy: a filter the developer must remember is not a control. - It fails closed. When the setting is unset the policy matches zero rows, so a request without tenant context sees nothing, never everything.
- The six tables without it are deliberate:
organizations(the tenant root, read by id to sign in),otp_challenges(read before a tenant is known),org_access_revisions(a counter),schema_migrations, and the operator'sbackup_runsandaudit_chain_breaks. None holds clinical data. The reads that must happen before sign-in go through narrowSECURITY DEFINERfunctions (ADR 0011). - A write that matches no visible row is an error, not a silent no-op.
- The runtime role is not the owner. A table owner bypasses row-level security (it is not forced, because migrations run as the owner), so the application connects as a different role for which the policies always apply, and production refuses to start if the two roles are the same, or if the runtime role is a superuser, owns anything, or can update or delete a ledger (The database).
The workspace wall
Independently of the organization, a study's execution data carries a workspace, uat or prod, in an environment column. 21 tables have a second policy, tenant_env_isolation, that matches only rows of the workspace the session has entered, which the API binds into app.current_env from the session's access profile (ADR 0013, ADR 0019). They are the participants, visits, form records and values, events, queries, signatures, verifications, deviations, documents, exports, sites and the two summary tables. A session that has entered no workspace sees none of it. The design itself (studies, versions, forms, visits, mapping, rules) belongs to no workspace.
Inside UAT there is a second narrowing: the execution rows that carry a UAT cycle (the participants, visits, values, queries and the rest of a cycle's test data) are visible only while that cycle is open, or from the read-only view of that closed cycle. A narrow, greppable setting (app.env_unrestricted) exists for reads that legitimately span both workspaces; each use is expected to justify itself.
The wall is therefore a property of the database, not of a screen: test data cannot be read in a Live session, and Live data cannot be read in a UAT session, whatever the application asks.
Identifier generation
Human-facing identifiers (the participant's screening number and subject number, the randomization number, in UAT with a UAT marker) are issued from id_sequences rather than computed from a count, so simultaneous enrollments cannot collide.
Integrity mechanisms
| Mechanism | Where |
|---|---|
| Hash chain | The audit ledger: each event hashes itself and its predecessor in its own chain, verified on a schedule and anchored daily (Architecture) |
| Append-only evidence | The runtime role can only read and insert, and a trigger refuses UPDATE, DELETE and TRUNCATE, on 12 tables, among them audit_events, item_values, signatures, signature_supersessions, source_verifications, query_thread_entries, form_version_snapshots, document_versions and rbac_role_revisions |
| A boot contract | ledger-contract.ts states, for 45 tables, what the runtime role may do (append-only, owner-written or guarded) and which guard triggers must exist and fire in every session; the server refuses to start in production when the catalog disagrees, and a table with a guard that is not on the list is reported |
| Content fingerprint | Signatures seal a SHA-256 of exactly what was signed; a change supersedes the signature visibly |
| Snapshot hash | The compiled design of a version, kept for ever, with the hash every sign-off and design export carries |
| Checksums | Every document version; every export file |
| Separation of duties | Query closing, source verification, version sign-off, delegation signing and the lock are refused by the database as well as the application |
| Partial unique indexes | One in-flight and one Live version for a study; one role for a person on a study |
What is not automated
Backups, restore verification, retention and archival are operational: the application ships the tooling and an alert that watches it, and the hosting must install and operate it (Running it). Nothing in the application deletes clinical data, and nothing in it can guarantee the data is still there after a disk failure.
Related
Architecture · The database · Environments and versioning · Security and hosting · Architecture decisions