V1 control metadata and transaction boundaries

Status: this document describes the earlier PostgreSQL planning prototype. The implemented runtime now has its own SQLite/PostgreSQL schemas and adapter; see ecosystem slice. Do not apply this prototype as the runtime migration. The database check could not execute in this sandbox: initdb fails creating a shared memory segment. Run sh scripts/check-postgres.sh on a host permitting PostgreSQL shared memory before accepting this migration.

Ownership

burst/migrations/001_control.sql bootstraps one shared control database for the initial single-authority network model. burst coordinates migration order; component services own operations on their respective tables. This does not merge their repositories or release versions. Separate databases would require an explicit distributed transaction/outbox design and are deferred.

Tables cover networks, spaces, principals, OIDC mappings, memberships, enrollments, grants, contract versions/activations, mutation outcomes and audit. Group memberships, issuance job execution, object manifests, placements, tombstones and application state still need migrations. IDs are opaque nonempty strings for now; API-level typed ID grammar remains to be specified.

Composite foreign keys prevent a grant or contract activation from referring to a space/version in another network. Principal identity is global; access is scope-bound. The database does not validate OIDC tokens, signatures, certificate profiles, delegation, principal kinds in references or policy decisions. Those remain service checks.

Required atomic operations

1. Enrollment: authorize operator approval; lock the matching digest/network/node record; check approval and expiry using authoritative time; conditionally set consumption and a stable issuance job ID; insert the issuance job and audit event in the same transaction. The current migration only reserves the job ID; the job table/worker and issuer reconciliation are pending. Call the external issuer after commit with that stable ID, never inside a transaction assumed to cover an external CA. Recovery must reconcile an already-issued certificate instead of issuing blindly again.
2. Mutations: bind keys to network, space, authenticated principal and operation. Hash the validated request. A duplicate with the same hash returns its recorded outcome; a different hash conflicts. Persist effect, outcome and audit in one transaction. Unique-key races require retry/re-read. Retention must exceed the documented client retry window; no TTL is chosen yet.
3. Contract publication: resolve the signer key and current publication grant, verify signature/source/runtime, validate and simulate policy, then insert an immutable version and audit. Activation requires an additional current administrative check; increase activation_revision even when selecting an old source version.
4. Commit-time enforcement: lock/read the active activation revision and current grants/membership in the transaction making the mutation visible. Compare to the evaluated revision. A changed revision requires reevaluation before commit. Publication and revocation paths must participate in a consistent locking/isolation strategy. CheckRevision alone is not a transaction or a full authorization check.
5. Revocation: update authoritative state/revision and append audit plus a durable invalidation/outbox event atomically. No outbox or event consumer exists yet. Reads requiring current authority fail when it cannot be reached.

Use PostgreSQL transactions and retry serialization failures as a whole operation rather than building a transaction engine. PostgreSQL documents isolation and serialization failures.

Runtime database permissions

Migration owners and service roles must be separate. The current migration grants no service access. Before connecting services, provision narrow table privileges and tenant enforcement (scoped queries plus tested RLS where appropriate). Runtime roles must not own tables, bypass RLS, truncate immutable tables, disable triggers, or update/delete audit/contract versions. Triggers protect ordinary updates/deletes; they do not protect against the owner or superuser. Never treat the current migration as a production access-control configuration.

Schema checks

sh scripts/check-postgres.sh creates a disposable Unix-socket-only cluster and removes it after checks. It does not connect to existing databases. Scenarios assert cross-tenant foreign keys, issuer/subject uniqueness, approval-before-consumption, replay rejection, immutable contract versions and append-only audit updates/deletes. Concurrent SQL enrollment consumption, crash recovery, RLS, schema upgrades, backup/restore and production database versions remain untested.