Identity database recovery

Source of truth: docs/runbooks/identity-database-recovery.md (synced at build time — edit the original, not this copy). This runbook covers the independent, fixed-realm veltro_identity PostgreSQL 16 database introduced by issue #235. It is the suite’s identity authority. It does not back up, restore, or modify VectorFlow.

For full-suite disaster recovery (all product databases, OpenSearch, Redis, named volumes, sealed identity keyrings), use backup-restore.md (veltro-backup/v1). This runbook remains the identity-only recovery path and the recovery-evidence contract the suite backup reuses for schema-regression refuse rules.

Invariants

• Database: veltro_identity; schemas: veltro and veltro_migrations.
• Realm: exactly one row whose literal ID is default.
• Migration owner: veltro_identity_owner; runtime: veltro_identity_runtime; recovery evidence: veltro_identity_recovery.
• Migrations run once through identity-init before the runtime starts. Recovery is repair-forward.
• Expected head: 038_identity_sigmatic_bootstrap_admin.
• Expected normalized migration SHA-256: 22c0f33fe737c9d10e9f82fef82771a0ae4fc3a386ab6ef1d61f9d1ecb27e48a.
• Expected schema fingerprint: 1a82d8bc3b8dfc3adb1913f3ef43fb576996baec5cf0cf5fbe8849c7daf3ecbf.
• Every live function body and constraint definition in the identity schemas that names chad must also name sigmatic. The PostgreSQL harness checks the persisted catalogs after upgrade, fresh migration and restore, including historical helpers.
• Veltro-native admin/member authority is independent of product grants; the database rejects removal, disablement, or demotion of the final active administrator, including concurrent attempts.
• Personal access token plaintext is never stored. OIDC and SCIM secrets use the veltro-setting:v1 envelope and the mounted Veltro keyring contract.
• Recovery is repair-forward. There is no automatic or destructive down migration.

The machine-readable source of truth is conformance/identity-persistence-v1.json.

Create a checkpoint

1. Establish the source and destination write-fence state for the coordinate being captured. This issue does not authorize destination-only writes, reverse synchronization, cutover, contract, or any point of no return.

2. Using the recovery credential, require veltro.recovery_evidence() to report the expected database, PostgreSQL major, authenticated recovery role, singleton realm, migration head/checksum, schema fingerprint, exact privileges, and row counts. The role can append/read recovery evidence but cannot read identity application tables or migration ledgers directly.

3. Prepare a mode-0600 checkpoint JSON document containing:

   • application version and commit;
   • PostgreSQL major;
   • migration head and every migration checksum;
   • schema fingerprint;
   • realm literal (default);
   • VectorFlow source repository, commit, migration head, watermark, and snapshot digest;
   • destination authority generation and deterministic state digest;
   • row counts returned by veltro.recovery_evidence();
   • encryption key identifiers needed by encrypted TOTP and identity-setting values;
   • explicit source/destination fence state, pre-cutover phase, and rollback mode.

4. Append the validated checkpoint using the recovery role. Append one monotonic reconciliation watermark per covered domain; each watermark must prove identical source/destination PK-set and effective-permission digests. Checkpoints and watermarks are immutable even to the owner:

   pnpm --filter @veltrosecurity/identity recovery:checkpoint \
     append-checkpoint recovery-checkpoint.json

5. Never put passwords, DSNs containing passwords, TOTP plaintext, backup-code plaintext, encryption keys, or other key material in the checkpoint.

6. Produce a plain-SQL dump with the owner credential, then create the external manifest. The manifest binds the canonical checkpoint digest to the dump SHA-256 without requiring the dump to contain its own digest (an entertainingly impossible circle):

   pg_dump "$VELTRO_IDENTITY_MIGRATION_DATABASE_URL" \
     --format=plain --no-owner --file=veltro_identity.sql
   pnpm --filter @veltrosecurity/identity recovery:checkpoint \
     manifest recovery-checkpoint.json veltro_identity.sql recovery-manifest.json

Store the dump, checkpoint, and veltro-identity-recovery-manifest/v1 artifact independently from product databases. Redis rate-limit/failure-window state is explicitly excluded: it is transient protection state, not PostgreSQL recovery state, and this issue adds no Redis dependency or backup procedure.

Restore into an empty database

1. Provision the owner, runtime, importer, and recovery login roles before restoring because dump grants name all four. Their credentials must be physically distinct. Every role is NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT NOREPLICATION NOBYPASSRLS; runtime, importer, and recovery roles have no memberships.

2. Create an empty PostgreSQL 16 database owned by veltro_identity_owner. Revoke PUBLIC access, including CREATE and TEMPORARY; grant only the required CONNECT privileges.

3. Rebuild the manifest from the retained checkpoint JSON and dump, then compare it byte-for-byte with the retained manifest. This verifies both the checkpoint digest and dump SHA-256.

4. Restore as the owner:

   psql "$VELTRO_IDENTITY_MIGRATION_DATABASE_URL" \
     -X --set=ON_ERROR_STOP=1 --file=veltro_identity.sql

5. Do not run migrations to rewrite a restored ledger. First prove that the restored ledger already matches the recorded source checksums.

6. With the runtime credential, call veltro.persistence_evidence(); with the recovery credential, call veltro.recovery_evidence(). For a temporary restore database, database-name fields intentionally differ; every other head, checksum, fingerprint, realm, role, and privilege field must match. Rename or promote only through the operator-controlled database procedure.

7. Compare every table’s deterministic row digest with the checkpoint, including users.realm_role, personal_access_tokens, identity_settings, mcp_policy, immutable audit events, recovery checkpoints, and reconciliation watermarks. A schema/head match plus one surviving user is insufficient. Exercise foreign keys, fixed-realm checks, product/role and MCP-scope checks, PAT revocation/expiry, final-admin concurrency, monotonic TOTP state and reconciliation chains, disabled-user state, and both append-only evidence boundaries.

8. In an authorized recovery environment that has access to the encryption service, prove encrypted values are decryptable by the recorded key IDs. Do not copy key material into the checkpoint or this runbook.

9. Enable writes only after every check succeeds.

apps/identity/db/verify-postgres.sh is the disposable PostgreSQL evidence harness used by CI. It performs fresh migration, replay, checksum-tamper rejection, runtime/importer/recovery privilege negatives, typed checkpoint insertion, monotonic watermark and append-only trigger proofs, concurrent final-admin protection, PAT/settings/policy proofs, manifest-bound plain-SQL dump, exact authority/recovery-data restore comparison, and an adversarial child-table omission rejection.

Failure and repair-forward policy

• A checksum, ledger, schema fingerprint, realm, role, privilege, constraint, row-count, or decryptability mismatch fails closed. Keep writes disabled.
• Never edit the migration ledger to make verification green. Diagnose source/dump provenance and create a new forward migration for an approved schema repair.
• Never silently merge, rename, or delete a non-default realm. Such state is unsupported and requires an explicit export or later migration contract.
• Before destination-only authoritative writes exist, discard the shadow restore and retry from a known checkpoint; there is no point of no return.
• After destination-only authoritative writes begin, an image downgrade is not a lossless rollback. Freeze writes and reconcile explicitly, or repair forward with a reviewed migration.