Skip to content
TrialionDocs
ImplementedFor DBAs, engineers, validation reviewers, security review

The database

Last updated Oct 21, 2026

Trialion keeps its records in PostgreSQL 18. The schema is built by SQL files, one concern for each, applied in order. There are two databases: the data plane's (everything an organization holds) and the control plane's (the list of organizations, platform administrators, feature switches and the platform ledger, with no clinical data).

The two database roles

Two roles work each database:

Role (example name)SettingDoes
Owner (cdms_super; the control plane's cdms_platform_super)DATABASE_MIGRATION_URLRuns npm run db:migrate and owns every object. The server never connects as it
Runtime (cdms_app; cdms_platform_app)DATABASE_URLWhat the API and the worker connect as. Granted, object by object, exactly what the application uses

Why two: the ledgers (the audit events, signatures, item values, the version and UAT records) are guarded by triggers, and a trigger does not bind a role that owns the table or is a superuser: that role could switch the guard off and rewrite history. Row-level security does not bind the owner either. A runtime role that is neither cannot do either. In production and staging the server refuses to start unless both URLs are set and name different roles, and then checks the connection it actually has: not a superuser, not the owner, no UPDATE, DELETE or TRUNCATE on a ledger, row-level security in force. The names are yours; the migration runner reads the runtime role's name from the user DATABASE_URL names and hands it to the migrations as a session setting, so no migration writes a role name.

Locally, psql -U postgres -f db/bootstrap.sql creates both roles and the cdms_dev and cdms_test databases. On RDS, as the master user and once: create the two roles, create the database owned by the owner role, and grant the runtime role CONNECT (Running it).

Migrations

The schema was re-authored before launch as one migration per table, so a database built by the earlier history cannot be migrated forward: npm run db:migrate says so and stops, instead of failing on "relation already exists". Nothing in such a database is worth keeping before launch, so it is reset (below).

  • One concern for each file, named NNNN_<slug>.sql and numbered from 0001: the runtime role and its grant helpers (0001), the shared functions (0002 to 0004), then one table (or a tightly related pair) with what belongs to it: indexes, triggers, row-level security policies, comments, the functions about it and what the runtime role may do with it. The gates that span several tables (the version lifecycle, the person lookups, the summaries) come after the tables they touch. There are 73.
  • Each file has a -- migrate:up and a -- migrate:down block, both run in one transaction. SQL only: no programmatic schema. A merged file is not edited: the schema changes by a new file.
  • The runtime role gets nothing by default. A new table is invisible to the application until its own migration calls cdms_grant_runtime('<table>', '<verbs>') with the verbs the application's statements use and no more (a ledger gets SELECT, INSERT; TRUNCATE can never be granted), and a function the application calls is opened with cdms_grant_runtime_execute. The tests hold every table and function to the list they are expected to have, so a new one is a decision made on purpose.
  • Nothing is seeded. A migrated database holds no organization, no person and no study: organizations arrive through the control plane's provisioning call, and a development database is filled by npm run db:seed-dev.
  • db/schema.sql is the schema the migrations make, dumped by npm run db:dump and committed with the migration; a governance test fails npm run verify when a migration is added without refreshing it (it compares the migration versions the file records, so a reviewer diffing the file sees what a release changes). It leaves out the audit ledger's monthly partitions (made as time passes) and every privilege (they belong to the environment's roles).

Conventions

  • Tables are plural and snake_case; primary keys are id uuid (gen_random_uuid()); timestamps are timestamptz; a table with updated_at binds the set_updated_at() trigger.
  • An organization-owned table carries org_id and a row-level security policy keyed to current_org(); the workspace (uat or prod) is a column where the data is split by it (Data and tenancy).
  • Soft delete is the exception: aggregates use a status column. The application deletes rows only from otp_challenges and notifications; the runtime role holds DELETE nowhere else.
  • Append-only tables are guarded by triggers (enabled in every session, including a replica-mode one) and, for the runtime role, by privileges. src/infrastructure/database/ledger-contract.ts is the contract the boot check enforces, for 45 tables.
  • The audit ledger is partitioned by month (audit_events by recorded_at), the partitions made ahead by the worker; the runtime role holds nothing on a partition.

Commands

npm run db:migrate           # apply pending migrations (as the owner)
npm run db:migrate:status    # list applied and pending
npm run db:migrate:down      # roll back the most recent migration
npm run db:new -- <slug>     # scaffold the next migration
npm run db:dump              # refresh db/schema.sql
npm run db:reset -- cdms_dev # development only: erase a local database and build it again
npm run db:seed-dev          # development only: an organization, an administrator, a role set
npm run db:rebuild-summaries # rebuild the dashboards' summary tables from the raw rows

Resetting a database

A local database: npm run db:reset -- cdms_dev. The name you type must equal the database the URL names, the host must be this machine, and neither the name nor NODE_ENV may say production or staging. It works as the owner, needs no superuser, and leaves the roles and the database alone. If AUDIT_ANCHOR_STORE=local, delete the anchor directory too: the anchors of the erased ledger would otherwise be held against the new one.

A deployed database (staging, or a database that predates the re-authored history): drop and recreate it as the master user, or empty the schema (DROP SCHEMA public CASCADE, recreate it owned by the owner role) and let the deployment's migration build from zero. The audit anchor store belongs to the ledger it anchors: a new ledger needs its own AUDIT_ANCHOR_INSTANCE_ID, because anchors written under S3 Object Lock cannot be deleted before their retention ends.

The control plane is reset the same way, and its first administrator is made again with npm run admin:bootstrap (Platform console: signing in and platform administrators).

Layout

db/
├── bootstrap.sql   local only: the two roles and the dev and test databases
├── migrations/     SQL, one concern per file, committed
└── schema.sql      the schema the migrations make, dumped and committed

Compliance Q&A

Compliance Q&ABuiltPart 11 11.10(e)Part 11 11.10(c)Annex 11 9Annex 11 17ALCOA+ enduringALCOA+ original
Can the application, or a person using its credentials, alter or delete the audit trail?

No. The runtime role can read and insert and nothing more on the ledgers, triggers refuse UPDATE, DELETE and TRUNCATE in every session, and the server refuses to start in production unless the role it is running as is neither a superuser nor an owner.

Limits: an administrator who owns the database, or who has access to the database host, is outside what the application can protect; that is the hosting's control (Security and hosting), together with the daily write-once anchors that make events cut off the end of a chain findable.

Evidence: schema triggers (UPDATE/DELETE/TRUNCATE refused, ENABLE ALWAYS)runtime role privilegesassert-least-privilege at boot (fatal in production)ledger-contract.tsthe tests that hold each table to its expected privileges

Data and tenancy · Running it · Architecture decisions · Configuration

PreviousAINext Running it