Role split

The audit trail and the signatures table are append-only because the application connects as a role that can only read and insert into them, and the triggers that refuse changes are defence in depth on top of that. The split is a hard prerequisite of migration 0002_console: the release script refuses the migrate Job while the api or worker DATABASE_URL still names the owner role. This page is the operator view; the reasoning is in ADR 0014.

The controls described here are what a reviewer can inspect; they are not a compliance claim. Status of the phase-2 release: designed until the R1 gates pass; the local development recipe below works today.

Roles

Role	Used by	May
owner role (`app` locally; the cluster’s master or `trovensa_owner` when deployed)	the migrate Job only	own the schema, run Alembic, create and grant
`trovensa_app`	the API and the worker	`SELECT`, `INSERT`, `UPDATE`, `DELETE` on every table except the append-only ones; `SELECT`, `INSERT` only on `audit_events` and `signatures` (and `audit_chain` from R2); `USAGE` on sequences incl. `audit_entry_seq`; no `TRIGGER`, `REFERENCES`, `TRUNCATE` on the append-only tables; not a superuser

Migration 0002 wraps its GRANT and REVOKE statements in a check for the role’s existence, so make check and the migration tests round-trip on a database without the role; ALTER DEFAULT PRIVILEGES covers tables created later.

Local development


cd api
make db-up          # vector-enabled image on 5434; docker/db-init/01-roles.sql creates trovensa_app and trovensa_owner on first start
make migrate        # alembic upgrade head with the owner DSN (MIGRATE_DATABASE_URL); 0002 applies the grants
make db-roles       # re-applies the role split to an existing volume (idempotent)
make dsn-check      # asserts the application DSN role is not a superuser and cannot update/delete/truncate audit or signature rows
make test-records   # role split, audit envelope, signatures, run tokens, worker lanes

The API and the worker run with DATABASE_URL pointing at trovensa_app; only make migrate uses the owner DSN.

Deployed order

Create the application role and its secret. Terraform creates the secret shell trovensa/<env>/app-db-app; the Kustomize Job trovensa-db-roles runs with the owner DSN and is idempotent: create trovensa_app when missing, apply the grants, set default privileges. Checkpoint: SELECT rolsuper FROM pg_roles WHERE rolname = 'trovensa_app' is false.
Point the api and worker at the application role. The runtime secret’s DATABASE_URL names trovensa_app; the migrate Job keeps its own secret with the owner DSN (trovensa-api-migrate-secrets). Roll out; readiness stays green because 0001 tables are readable by the new role.
Run the 0002 migrate Job. scripts/release.sh --api refuses this step while either DSN names the owner role (refuse 0002 while DSN is owner role). The Job installs the trigger functions, the sequence, the envelope columns and the grants, and backfills actor_kind, occurred_at, entry_seq and row_hash on existing rows; alembic check is clean afterwards.
Verify. As trovensa_app: INSERT into audit_events succeeds and the row comes back with entry_seq, row_hash and created_at set regardless of what was supplied; UPDATE, DELETE and TRUNCATE on audit_events and signatures are refused with permission denied; ALTER TABLE audit_events DISABLE TRIGGER is refused. As the owner: UPDATE or DELETE on the same tables raises the append-only trigger; DELETE FROM users for a referenced user fails on the foreign key. Deleting a referenced organisation is blocked the same way.
Record. The deployed tags and the Job outcomes go into the deployment record; the claims register rows for 50 tables and the role split are re-pinned.

Rollback

Before step 3: revert the runtime secret to the previous DSN and roll out; nothing in the schema changed.
After step 3: alembic downgrade 0001 through the migrate Job restores the previous schema (the envelope columns and new tables are dropped; the sequence and trigger functions are removed); the api and worker keep working on 0001 with either role. Audit rows written under 0002 lose their envelope columns on downgrade, so a downgrade is a last resort and is recorded as such.

Checks that gate a release

Check	Command	Expected
DSN role	`make dsn-check`	application role is not a superuser and cannot update, delete or truncate audit or signature rows
Migration round trip	`make test-migrations`	`0001` -> `0002` -> `0001` -> `0002`, backfills verified, `alembic check` clean on both database images
Role split tests	`make test-records`	`trovensa_app` refused on the append-only tables; owner hits the trigger; FK delete refused; app role cannot disable a trigger
Release script	`make release-check`	the dry run prints the role Job before the migrate Job and refuses `0002` on an owner DSN
Hash parity	`make test-records`	SQL `trovensa_jcs` and the Python twin agree byte for byte on the sample rows

Source: api/alembic/versions/0002_console.py, api/docker/db-init/01-roles.sql, api/docker-compose.yml, api/Makefile, api/app/db/role_check.py, api/app/services/audit.py, api/tests/test_role_split.py, api/tests/test_migrations.py, infra/k8s/base/SECRETS.md, PRODUCT-CONTRACT.md sections 10.4 and 10.7, PRODUCT-PLAN.md Part 4 sections 5.1 and 8, decisions/0014-alcoa-envelope-and-append-only-audit.md