Database & Migrations

PostgreSQL is the only datastore of the API server: application data, the event outbox, workflow runs, delivery logs and the vault all live in one database per instance. This page explains how the schema evolves, how baseline data is seeded and what operators are responsible for.

Schema-first evolution

The data model is defined once, in a schema that also drives the API contracts and the ORM models (see Architecture). A change flows in one direction:

1. The schema changes and is reviewed.
2. The ORM models and API models are regenerated from it, so the server cannot drift from the contract.
3. A migration is generated from the difference between the previous and the new model and is shipped with the server release.

Migrations are ordered and reversible where the change allows it. Every server version knows which migration revision it expects; the database records which revision it is at.

Applying migrations on deploy

In a Kubernetes deployment the server pod runs sequenced init steps before the API process starts:

wait for the database → migrate → seed → register plugins → start API

Each step is idempotent. A pod restart on an already migrated and seeded database performs no changes and starts the API immediately. Because the steps are part of the pod, a rolling upgrade always migrates before the new version serves traffic. Operators who run migrations out-of-band can disable the init steps and run the same commands from the CLI.

When several replicas start at once, the migration step is protected by the database's own locking, so only one performs the change and the others observe the finished result.

Seed data

The seeder loads reference data that the application needs to function: roles and their scopes, status and type lookups (enrollment, device, questionnaire, delivery statuses and similar), the root folder and the initial administrator account. Seed sets are layered:

Layer	Contents
Default	The baseline every instance needs.
Environment-specific	Additions or overrides for a given environment (for example demo users or a preconfigured clinical source).

Entries are merged by identifier and upserted, so re-running the seeder on a live instance is safe and is exactly what happens on every upgrade: new lookup values and new role scopes arrive with the release without manual steps. A safe mode prompts (or, non-interactively, skips with a warning) before overwriting rows that were changed after the last seed.

Seed files may reference environment values for secrets such as the initial administrator password, so credentials never live in the seed data itself. After the first installation the operator should sign in with the initial administrator, create the organisation's own administrators and retire the initial account. See Deploying an Instance.

Data conventions

• Identifiers and audit fields. Every record carries an identifier, creation and update timestamps and the identity of the last editor.
• Soft delete. Most business records are soft-deleted: they receive a deletion timestamp, disappear from normal queries and can be restored (questionnaires, users) or purged later. List endpoints offer explicit parameters to include or select deleted records where that is permitted.
• JSON documents. Questionnaire definitions, submission answers, workflow graphs, plugin configuration and analytics definitions are stored as JSON documents, indexed where the application queries into them. This is what lets questionnaires and workflows evolve without schema migrations.
• Encrypted at rest. Vault secrets are encrypted before they are written; the database never holds them in clear. See Configuration & Vault.
• Retention. Event delivery tasks and webhook delivery records are pruned by background jobs after a retention period. Business data is never pruned automatically.

Operator responsibilities

Area	Responsibility
Backups	Regular logical or physical backups of the database, tested by restoring into a scratch instance. Back up the encryption key material together with the database; a backup without the key cannot decrypt vault entries.
Restore	Restore the database, then start the server; the init steps bring the schema and seed data to the release's level.
Upgrades	Upgrade one release at a time along the published sequence. Take a backup before upgrading; migrations are forward-only in production.
Downgrades	Restore the pre-upgrade backup rather than reversing migrations on a live instance.
Capacity	Monitor size and growth of the submission, event and delivery tables; plan storage for the retention you need.
Connections	The server uses a connection pool per process; size the database's connection limit for the number of replicas.

Development conveniences

The CLI offers commands to initialise, reset or wipe a development database, list tables and models, inspect a table and generate synthetic submissions for testing dashboards and workflows. These are meant for development and evaluation environments and are described in Command Line Interface.