[Product Gap] Add snapshot promotion, bitemporal lineage, retention, and recovery workflows

[seonghobae]

Buyer-visible problem

pg-erd-cloud already creates immutable schema snapshots and computes diffs, but a timestamped list is not a complete lifecycle. Buyers still cannot answer, with product-owned evidence:

• Which snapshot was the approved baseline for a release or environment?
• Which target database and schema scope did it represent?
• Who promoted, superseded, or rejected it, and why?
• Which later views, exports, migration plans, annotations, and connector artifacts were derived from it?
• What is retained, archived, legally held, or eligible for deletion?
• How do we recover a known-good diagram/model without pretending that metadata rollback automatically rolls back a live customer database?

Product contract

Create a first-class, immutable snapshot lineage and promotion model. Preserve the existing schema_snapshot / schema_snapshot_data boundary while adding normalized lifecycle facts.

Time model

Store separate timestamps/intervals for:

• captured_at: when target metadata was observed;
• available_at: when the completed snapshot became usable;
• valid_from / valid_to: when a promoted baseline is declared applicable;
• recorded_at / superseded_at: when pg-erd-cloud learned or changed the declaration;
• knowledge_cutoff: the latest evidence allowed in an audit/export.

A later-captured snapshot may describe an earlier database state, so capture time must not be silently treated as valid time.

Lineage and lifecycle

Add normalized objects such as:

snapshot_lineage
snapshot_derivation
snapshot_promotion
promotion_environment
retention_policy
retention_disposition
legal_hold_record
recovery_checkpoint
recovery_action
audit_event_record

Required behavior:

1. Each snapshot has a canonical content hash, source dialect/version, connection reference, schema scope, capture tool version, and immutable source evidence.
2. Parent/child derivations distinguish captured_from, imported_from, normalized_from, compared_with, exported_from, and planned_from rather than using one generic edge.
3. Promotion uses optimistic concurrency and preserves history; changing the approved baseline closes the prior effective interval instead of rewriting it.
4. Promotion supports at least development, staging, and production environment codes without hardcoding customer-specific environments.
5. Retention and legal-hold decisions are policy records, not background hard deletes without evidence.
6. “Recover snapshot/view” restores product metadata and a selected diagram state only. Live database recovery requires a separately approved migration/backup workflow and cannot be implied by this feature.
7. Every export and migration plan can embed the exact snapshot, promotion, tool, commit, and policy references needed for reproducibility.
8. Provide accessible history, compare, promote, supersede, archive, and recover interactions with exact-value tables and non-color-only state cues.
9. Publish optional PROV-JSON-LD/JSON-LD projection while keeping the normalized relational model authoritative.

Required tests

• concurrent promotion of two snapshots;
• out-of-order capture and availability times;
• same content captured twice from the same and different connections;
• supersession, rejection, re-promotion, retention expiry, and legal hold;
• knowledge-cutoff queries that cannot see future evidence;
• deletion request with derived migration/export artifacts;
• snapshot/view recovery without live-target SQL execution;
• deterministic lineage export and tamper detection;
• cross-project/tenant lineage injection rejection;
• real PostgreSQL migration clean-install/upgrade/downgrade rehearsal;
• production statement/branch coverage 100% and public API docstrings 100%.

Acceptance evidence

• PRD/TRD/UML/ERD and ADR defining time semantics, lineage types, retention, and recovery claim boundaries;
• API and database migrations with 3NF, multi-word snake_case objects;
• frontend workflow aligned with Figma and Storybook states;
• OpenTelemetry audit/latency metrics without customer schema leakage;
• exact-current-head CI, security, accessibility, migration, and independent-review evidence;
• docs/product-technical-gap-baseline.md and CHANGELOG.md updated.

References — APA 7th

Snodgrass, R. T. (1999). Developing time-oriented database applications in SQL. Morgan Kaufmann.

World Wide Web Consortium. (2013). PROV-O: The PROV ontology. https://www.w3.org/TR/prov-o/

International Organization for Standardization, International Electrotechnical Commission, & Institute of Electrical and Electronics Engineers. (2022). Software, systems and enterprise—Architecture description (ISO/IEC/IEEE 42010:2022). https://www.iso.org/standard/74393.html