02 — Workstream A: birth_admission_permit Table Rehearsal

Result: PROVEN. Additive, reversible, fully self-contained. Created and tested entirely inside BEGIN … ROLLBACK as a temp table (ON COMMIT DROP), then proven absent after rollback.

Rehearsed schema

CREATE TABLE birth_admission_permit (
  permit_id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  collection_name     varchar NOT NULL,
  entity_code         varchar NOT NULL,
  requested_by_dot    varchar,
  requested_by_actor  varchar NOT NULL,
  requested_at        timestamptz NOT NULL DEFAULT now(),
  expires_at          timestamptz NOT NULL,
  status              varchar NOT NULL DEFAULT 'REQUESTED',
  idempotency_key     varchar NOT NULL,
  policy_snapshot_ref varchar,
  consumed_at         timestamptz,
  finalized_birth_id  integer,
  failure_reason      text,
  CONSTRAINT chk_permit_status CHECK (status IN
    ('REQUESTED','RESERVED','CONSUMED','FINALIZED','FAILED','EXPIRED','CANCELLED'))
);
CREATE UNIQUE INDEX uq_permit_idem ON birth_admission_permit(idempotency_key);
CREATE UNIQUE INDEX uq_permit_active
  ON birth_admission_permit(collection_name, entity_code)
  WHERE status IN ('REQUESTED','RESERVED');   -- one active permit per object

Notes:

• gen_random_uuid() is PG16 core (no extension needed).
• No raw secret/token/log value is stored. policy_snapshot_ref and requested_by_dot/actor are references/identifiers only.
• The partial unique index is the key invariant: at most one active (REQUESTED/RESERVED) permit per (collection_name, entity_code); terminal-state permits do not block re-request, so failed/expired permits remain visible and scannable without locking the object out.

Status model

REQUESTED → RESERVED → CONSUMED → FINALIZED (happy path) with terminal branches FAILED, EXPIRED, CANCELLED. The gate (Workstream D) moves an active permit to CONSUMED on a successful insert; the deferred finalize (Workstream E) moves CONSUMED → FINALIZED at the commit boundary.

Test battery & live results

#	Test	Expected	Result
A.1	Insert valid dot_tools permit	accepted	PASS (status REQUESTED)
A.2	Insert with status='BOGUS'	rejected by CHECK	PASS — check_violation
A.3	Second active permit for same (dot_tools, DOT-TEST-001)	rejected by partial unique	PASS — unique_violation
A.4	Reuse idempotency_key='idem-001'	rejected by uq_permit_idem	PASS — unique_violation
A.5	Permit with expires_at in the past	is_valid_now = false	PASS (validity = active-status AND not-expired)
A.6	After first permit → CONSUMED, insert new active permit for same code	accepted (terminal no longer blocks)	PASS (1 CONSUMED + 1 REQUESTED coexist)

Validity predicate (for the gate)

A permit is valid for consumption iff:

status IN ('REQUESTED','RESERVED')
  AND expires_at > now()
  AND collection_name = <target collection>
  AND entity_code     = <target code>

A CONSUMED/FINALIZED permit can never be reused (it is no longer active, so the gate's lookup returns nothing → block).

Rollback proof

After ROLLBACK: to_regclass('public.birth_admission_permit') → NULL (table absent); permit-related temp functions count → 0. No production object created.

Decision

PERMIT_TABLE = PROVEN / READY. Additive (no change to existing rows), reversible (single DROP TABLE rollback), self-contained, enforces every required invariant at the storage layer. Apply requires owner DDL approval only (doc 10).