Skip to content

ADR 0031: Company-Scoped PostgreSQL Backup and Atomic Restore

Context

ADR 0030 requires a supported backup to restore one company's database records, managed evidence, and key envelope together without changing current state after failed validation. BooksPlus currently stores more than one company in a shared PostgreSQL schema. Many records carry company_id directly, some child records inherit company ownership through a parent, and some security and tax-rule records may be shared.

A whole-database pg_dump restore would replace other companies and therefore cannot implement the company-scoped backup and restore contract.

Decision

BooksPlus uses a versioned logical company snapshot, not a whole-database restore. The canonical Company Backup Snapshot contract assigns every persisted relation to exactly one of:

  • company-owned rows selected directly by company identity;
  • company-owned child rows selected through a declared parent relationship;
  • company-scoped security configuration captured for protected review rather than automatic activation;
  • immutable shared definitions referenced by identity and reconciled without overwriting existing definitions; or
  • installation state excluded from company backup.

The catalog is a versioned implementation contract. It may define explicit row or logical-component projections where one physical relation contains both company and installation state or more than one required component. No row or payload may be included twice. Every schema migration must classify each added, removed, renamed, or ownership-changing relation before the migration can be used by the supported backup path. An uncataloged relation blocks backup creation and restore.

Security definitions and assignments

Company-scoped actor-role assignments are captured security configuration, but restore never activates a captured assignment merely because it was present in the backup. Before replacement, an authorized administrator reviews the current and captured assignments through the distinct protected role_permission.change action. Only explicitly approved assignments are applied in the replacement transaction. Existing assignments remain current unless that review explicitly replaces or removes them. Recovery into an absent company activates no captured assignment by default.

Actors, roles, permissions, and role-permission bundles are shared reference definitions. The backup embeds every shared definition referenced by the company snapshot or a captured assignment.

Restore never overwrites a shared definition. An existing shared identity is reused only when its stable meaning is compatible with the embedded definition; incompatible UUID, actor type or mapped external identity, role name, protected-action name, or role-permission set blocks restore.

Missing roles, permissions, and role-permission bundles may be inserted from their exact embedded definitions when needed by explicitly approved assignments. A missing actor is restored with its stable actor id, historical display identity, and actor type, but is disabled and has no operating-system or application-login mapping. Rebinding and re-enabling the actor is a later protected user-management action. Existing actor status and login mapping are not rolled back by company restore.

Immutable shared rules

Every referenced InformationReturnRuleSet is embedded with the company snapshot. Restore reuses an existing key and version only on an exact content match, inserts a missing immutable version, and blocks conflicting reuse. Later rule versions never substitute for the version captured by the backup.

Company identity and restore modes

A backup retains the stable company UUID. The supported modes are:

  • replacement of an existing company having that exact UUID; and
  • recovery into a managed installation where that UUID is absent, creating the company with the same UUID.

Restore never remaps, clones, or merges company identity. A different existing company cannot be selected as the restore target. Recovery into an absent target does not activate restored user access automatically.

Consistency, quiescing, and atomicity

Capture uses one PostgreSQL repeatable-read snapshot for company-owned relational state, company key-envelope state, and managed-evidence state. Logical components may share the same physical PostgreSQL transaction but remain separately manifested.

Replacement requires exclusive BooksPlus maintenance mode. New company-scoped writes and background work are stopped, active application work is drained, and the restore holds the supported company-scoped exclusive database lock through validation and replacement. Every ordinary company mutation must participate in the corresponding shared lock protocol.

Restore loads the candidate into an isolated staging boundary and validates format, manifest, catalog version, schema version, company identity, shared references, relational integrity, managed evidence, and the company key envelope before current state can change. The supplied recovery secret must both open the backup and authenticate the captured company key envelope. Replacement of company-owned state occurs in one database transaction. Failure before or during replacement leaves pre-restore replaceable company state unchanged. The permitted value-free failed-restore audit event may be appended.

Audit history

Company-scoped security audit events are append-only merge state, not replaceable company state. Restore:

  • imports missing backup audit events by immutable event id;
  • treats an exact existing event as already present;
  • blocks conflicting reuse of an event id;
  • retains current events created after the backup; and
  • appends the protected restore result.

Company-null installation audit events are not part of a company backup. Historical events may continue to reference business objects removed by a restore because they remain evidence of the intervening activity.

Schema compatibility

The initial supported restore requires an exact application-schema version and exact company-snapshot catalog version. A newer backup is rejected. An older backup is rejected unless a later accepted, explicit, and tested forward conversion loads and validates it entirely in staging before replacement. Ordinary application migrations are not implicit backup conversion rules.

Managed evidence

Managed W-9 evidence remains a distinct manifested backup component even when its encrypted bytes are physically stored in PostgreSQL. Relational artifact metadata and the managed-evidence component use protected-payload and storage identities to reconstitute one exact artifact without omitting or counting the ciphertext twice. Sensitive artifact plaintext never enters the snapshot.

The company key envelope is also a distinct required component and is not duplicated as ordinary relational payload.

Consequences

  • Backup and restore remain company-scoped in a shared database.
  • A restore can recover one company without replacing other companies or installation migration state.
  • Shared security and tax-rule meaning cannot be silently overwritten.
  • Recovery on a new installation preserves historical actor identity without granting restored login access.
  • Append-only audit evidence survives rollback to an older company snapshot.
  • Exact schema matching is intentionally conservative for the launch path.
  • Production PostgreSQL capture and replacement must implement and verify the snapshot catalog before the protected-data retention gate can be removed.
  • 0002-postgresql-primary-company-data-store.md
  • 0027-managed-local-postgresql-launch-deployment.md
  • 0030-sensitive-company-data-key-recovery-and-backup-protection.md
  • ../../contracts/company.contract.md
  • ../../contracts/security/backup-security-contract.md
  • ../../contracts/security/company-backup-snapshot.contract.md