Backup, restore and retention¶

Everything Adriano knows that cannot be rebuilt from the sources is in its PostgreSQL database: the approval history, the content of every reviewed revision, and the candidates pushed by CI. Published records can be indexed again by a poll, but decisions cannot. Back the database up.

Backup¶

Back up on a schedule that matches how much decision history you can afford to lose; daily is a reasonable start. With the bundled db service:

docker compose exec db pg_dump -U adriano -d adriano \
  --format=custom --file=/tmp/adriano.dump
docker compose cp db:/tmp/adriano.dump ./adriano-$(date +%F).dump

Replace adriano with ADRIANO_DB_USER and ADRIANO_DB_NAME if you changed them. For a managed PostgreSQL, use your provider’s backups or run the same pg_dump against it. Keep backups away from the Docker host, and test a restore now and then.

The catalog file and .env are not in the database; keep them in your configuration management.

Restore¶

Stop everything that writes to the database first: Adriano and the scheduled poll. A write during the restore can corrupt it or be lost.

Stop Adriano and pause the poll schedule:
```
docker compose stop adriano
```

Restore the dump:

docker compose cp ./adriano-2026-10-19.dump db:/tmp/adriano.dump
docker compose exec db pg_restore -U adriano -d adriano \
  --clean --if-exists /tmp/adriano.dump

Bring the schema up to the version the running release expects (needed when the backup is older than the release; harmless otherwise):
```
docker compose run --rm --no-deps adriano adriano migrate
```
On Kubernetes, scale the Deployment to zero first and run adriano migrate as a one-off job with the same image.
Start Adriano and resume the poll schedule:
```
docker compose up -d
```
Run a poll, so published records catch up with the sources.

Never restore a backup taken by a newer release into an older one.

Retention¶

Kept forever

The approval history and the stored content of each revision are the audit trail. Adriano never deletes them, and you should not either.

May be pruned

The log of poll and push runs grows with every poll and push and holds no approval evidence. Adriano does not prune it. When it grows too large, delete old finished runs, for example everything older than 90 days:

DELETE FROM intake_run
WHERE finished_at IS NOT NULL
  AND finished_at < now() - interval '90 days';

Never delete runs that have not finished. Deleting a run also forgets its delivery_id, so a CI retry older than your retention would be accepted again as a new push.

Database roles and tamper resistance¶

The approval history table refuses changes and deletions, so application bugs and slips cannot rewrite it. That protection does not stop the table’s owner or a database superuser, who can switch it off.

The bundled stack runs the schema update and the application as the same owning user. That is fine for evaluation, but not tamper resistant. For production, use two users:

the owner, used only by the schema update (adriano migrate);
an application user for Adriano that owns nothing and may only add to the approval history.

Create the application user once, as the owner:

CREATE ROLE adriano_app LOGIN PASSWORD '<strong password>';
GRANT CONNECT ON DATABASE adriano TO adriano_app;
GRANT USAGE ON SCHEMA public TO adriano_app;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON source_registration, record, intake_run TO adriano_app;
GRANT SELECT, INSERT ON revision, decision_event TO adriano_app;

Then give the running container the application user, set ADRIANO_MIGRATE_ON_START to false and run adriano migrate with the owner’s ADRIANO_DATABASE_URL (on Kubernetes, give the migrate init container a Secret with the owner’s address; on Compose, see Docker Compose). Re-check the grants after every upgrade: a table added by a new release needs its own grant, and a missing one shows up as permission denied errors in the application log.

Even then, the database is not cryptographic evidence: a database administrator can still rewrite history, and the content fingerprints Adriano stores prove which text a decision refers to, not who wrote it or that it was ever published. If you need tamper-evident records, export the approval history regularly to write-once storage you control.