[Product Epic] Complete governed forward-engineering apply, rollback, and recovery

[seonghobae]

Buyer-visible problem

pg-erd-cloud can export DDL and PR #834 has built a substantial execution-neutral foundation for structured migration plans, dry-run attempts, cancellation, leases, live preflight, and audit evidence. It deliberately does not yet register a production consumer, provision an isolated sandbox, grant live apply authority, prove process recovery, or offer an end-to-end buyer workflow.

That claim boundary is correct, but the product remains incomplete for teams that expect a database design tool to take a reviewed change from proposed model to safely executed migration. A commercial product must provide a small, auditable vertical path rather than an indefinitely growing all-in-one branch.

Product outcome

Deliver a protected, versioned workflow:

base snapshot
→ proposed target schema
→ deterministic migration plan
→ risk/precondition review
→ isolated dry run
→ live read-only preflight
→ human approval
→ bounded apply
→ convergence capture
→ success or recovery action
→ immutable evidence bundle

Required decomposition

Do not continue accumulating unrelated behavior in one PR. Reconstruct or stack bounded PRs from current protected main, each with exact-head evidence:

1. Plan authority and compiler

   • immutable source/target snapshot IDs and hashes;
   • deterministic typed operations and dependency order;
   • dialect/version capability matrix;
   • reversible, conditionally reversible, and irreversible classification;
   • fixed resource limits and no free-form SQL authority.

2. Sandbox runtime

   • isolated ephemeral PostgreSQL 14–18 database/container;
   • no access to production credentials or customer network;
   • CPU, memory, storage, wall-clock, statement, and output bounds;
   • complete cleanup and orphan reaper;
   • sandbox snapshot and convergence report.

3. Stored-target live preflight provider

   • exact project/connection/base snapshot/attempt lease binding;
   • post-connect revalidation;
   • read-only catalog capture and precondition checks;
   • DNS/SSRF/TLS and least-privilege enforcement;
   • secret-safe errors and cancellation.

4. Approval and authorization

   • deployer role plus maker-checker approval for high-risk plans;
   • exact plan digest, target fingerprint, environment, expiry, and scope;
   • approval invalidation on any plan/target/state change;
   • accessible review UI that explains risk and the buyer's next action.

5. Apply worker

   • production startup/consumer registration;
   • one active attempt per run with fenced leases and heartbeats;
   • statement-level timeouts and cancellation checkpoints;
   • transaction boundary declared per operation class;
   • retry only where idempotency is proved;
   • no generic replay of partially committed DDL.

6. Convergence and recovery

   • recapture target state through the same guarded connection;
   • compare actual target with planned target;
   • distinguish success, partial success, divergent state, and unknown state;
   • generate recovery guidance from known committed operations;
   • integrate approved customer backup/PITR evidence when available;
   • never claim automatic rollback for irreversible or non-transactional DDL.

7. Operations and evidence

   • durable event/outbox/inbox model;
   • OpenTelemetry traces and metrics without DSN/schema-value leakage;
   • incident and cancellation runbooks;
   • downloadable signed execution evidence and machine-readable provenance;
   • service restart, worker crash, lease loss, queue duplication, and provider timeout recovery.

Mandatory safety invariants

• dry_run=false remains default-deny until the final apply capability is explicitly enabled by deployment policy.
• A legacy free-form SQL route cannot silently become structured apply authority.
• The worker never accepts plaintext DSNs, arbitrary connection overrides, or plan SQL from queue payloads.
• Every external identifier is re-resolved and authorized at execution time.
• The target is sticky to the approved provider/connection lineage.
• No status is marked successful before post-apply convergence evidence is committed transactionally with the outbox event.
• PII/schema metadata remains usable for authorized work; protection is encryption, access control, purpose limitation, and audit rather than blanket masking.

Realistic acceptance tests

• PostgreSQL 14–18: additive column/index/FK, rename, type conversion, partition operation, extension-owned index access method, and quoted multilingual identifiers;
• lock contention, statement timeout, deadlock, connection loss, worker SIGKILL, lease expiry, duplicate signal, and service restart;
• plan changed after approval, target changed after approval, snapshot missing, cross-project target, revoked credential, and DNS rebind;
• transactional DDL rollback, non-transactional/irreversible operation requiring explicit manual recovery;
• recovery from partial commit without replaying already committed steps;
• convergence mismatch and operator-visible next action;
• full production statement/branch coverage and public API docstrings;
• real sandbox and guarded-route integration, not only mocks.

Completion evidence

• feat: establish forward engineering plan authority #834's useful commits are decomposed or integrated without stale aggregate baggage;
• PRD/TRD/ADRs/UML/ERD/threat model/runbook and docs/product-technical-gap-baseline.md agree with executable behavior;
• clean install and upgrade migration rehearsal;
• exact-current-head required checks, zero unresolved valid review findings, and qualifying independent approval;
• version/CHANGELOG/release notes clearly state supported operation classes and remaining non-goals.

References — APA 7th

PostgreSQL Global Development Group. (2026). PostgreSQL 18 documentation: Data definition. https://www.postgresql.org/docs/18/ddl.html

National Institute of Standards and Technology. (2022). Secure software development framework (SSDF) version 1.1 (NIST Special Publication 800-218). https://doi.org/10.6028/NIST.SP.800-218

SLSA Community. (2025). Supply-chain levels for software artifacts specification, version 1.2. https://slsa.dev/spec/v1.2/