P3D — Birth B3-P Policy Storage DDL — Agent Prompt (DRAFT rev2)

Date: 2026-05-12 Rev: 2 (execution log, advisory lock, compiled preview, rollback, constraint naming) Self-contained.

Effort: low-medium.

---

Mission

Add 6 coverage policy columns + CHECK constraints to collection_registry. DDL only, no data mutation.

---

Scope constants

target_table_family = collection_registry (verify exists)
db_schema = DISCOVER
column_names are design constants (not runtime hardcode):
  coverage_status, coverage_scope_status, coverage_exemption_reason,
  coverage_review_owner, coverage_decided_at, coverage_decided_by
constraint_naming = chk_<table>_<column>

---

Hard boundaries

DDL: ALTER TABLE ADD COLUMN + ADD CONSTRAINT only. No DROP. No data mutation. No function/trigger. No other tables.

---

Phase 0 — Environment + DB discovery (self-contained inline)

---

Phase 1 — Table existence + current schema introspection

Verify collection_registry exists. Introspect ALL current columns + constraints.

For each proposed column:

ABSENT → will ADD
EXISTS + compatible type (varchar/text for text cols, timestamptz for timestamp) → ALREADY_EXISTS_OK
EXISTS + incompatible type → BLOCKED_EXISTING_CONFLICT

For each proposed CHECK constraint:

ABSENT → will ADD
EXISTS + same allowed values → ALREADY_EXISTS_OK
EXISTS + different allowed values → BLOCKED_EXISTING_CONFLICT

If ANY BLOCKED → report all conflicts, STOP. Do not partial-execute.

---

Phase 2 — Execution log

RUN_STARTED_AT=$(date -u +"%Y-%m-%dT%H:%M:%SZ")
LOG_FILE=<discovered_or_convention_log_path>/p3d-birth-b3p-ddl-$(date -u +%Y%m%dT%H%M%SZ).log

---

Phase 3 — Compiled SQL preview

Before execution, log ALL compiled DDL statements:

For each column to add:
  ALTER TABLE <resolved_schema>.<resolved_table> ADD COLUMN <column_name> <type>;

For each CHECK constraint to add:
  ALTER TABLE <resolved_schema>.<resolved_table>
    ADD CONSTRAINT chk_<resolved_table>_<column_name>
    CHECK (<column_name> IN ('value1','value2',...));

compiled_sql_logged=true before execution begins.

---

Phase 4 — DDL transaction

BEGIN;

-- Advisory lock
SELECT pg_try_advisory_xact_lock(hashtext('p3d_birth_b3p_ddl_policy_storage'));
-- false → ROLLBACK, LOCK_BUSY

-- Add columns (only those marked ABSENT in Phase 1)
-- Add CHECK constraints (only those marked ABSENT)

-- Post-DDL introspection: verify each added column exists with correct type
-- Verify each added constraint exists with correct check_clause

-- ALL verified → COMMIT
-- ANY verification fail → ROLLBACK

COMMIT;

---

Phase 5 — Post-commit verification

Full introspection of collection_registry: columns + constraints. Compare against expected state.

---

Phase 6 — KB report + VPS log

Upload report to KB. Persist log to VPS.

Rollback (if needed post-commit):

-- For each column added:
ALTER TABLE <resolved_schema>.<resolved_table> DROP COLUMN IF EXISTS <column_name>;
-- For each constraint added:
ALTER TABLE <resolved_schema>.<resolved_table> DROP CONSTRAINT IF EXISTS <constraint_name>;

Post-rollback verification: introspect again, confirm columns/constraints absent.

---

Final response

b3p_ddl_status=PASS|PARTIAL|BLOCKED|LOCK_BUSY
columns_added=<N>
columns_already_existed=<N>
columns_blocked_conflict=<N>
constraints_added=<N>
constraints_already_existed=<N>
constraints_blocked_conflict=<N>
compiled_sql_logged=true|false
execution_log_path=<path>
no_data_mutation=true
post_ddl_verified=true|false

---

B3-P DDL | rev2 | Production safety | 2026-05-12