Target DDL to Runtime Projection

1. The problem: declared schema is not observed schema

A target DDL or migration artifact represents intent. Release evidence needs a separate observation of runtime state. A file can be correct while a constraint is missing, an index differs, an object is invisible to the observing role, or an environment-owned object exists only at runtime. This lesson never treats a green migration status as proof of the deployed schema.

2. Keep two evidence objects separate

DDL Runtime Projection Reconciliation Record v1 keeps Target Definition and Runtime Observation as separate fields. Target state comes from target.sql or a migration artifact; runtime state comes from an observed projection. A row closes only when its source, scope, and timestamp are explicit. Inference or policy choice must not be written as observation.

3. Establish object identity before diffing

Identify objects by stable identity such as schema, object type, and object name before comparing definitions. A raw text diff can reflect ordering, whitespace, quoting, or dump formatting. The goal is semantic comparison of tables, columns, constraints, indexes, and views rather than checksum equality between files.

4. Start with a portable information_schema projection

PostgreSQL documents information_schema as a standards-oriented set of views for database metadata [PG-INFO-SCHEMA]. It is a useful portable observation surface, but it does not expose every PostgreSQL-specific detail and row visibility can depend on the current role. A missing row is therefore not automatically proof that the object is absent.

5. Add PostgreSQL-specific projection when needed

When PostgreSQL-specific detail is required, system catalogs and information functions can reconstruct definitions. For constraints, separate existence/type metadata from reconstructed PostgreSQL-specific definition text [PG-TABLE-CONSTRAINTS][PG-SYSINFO]. Reconstructed text is not the original source DDL.

6. A schema-only dump is one projection, not complete truth

pg_dump --schema-only emits object definitions without table data or statistics [PG-PGDUMP]. It is useful as an observed schema projection, but it does not prove application correctness, data integrity, restore success, or runtime traffic behavior. In this lesson the dump is only one observation surface.

7. Semantic equivalence is not textual equality

Two definitions may differ textually while remaining semantically equivalent. Clause order or formatting can change without changing the meaning of a constraint or index. Record semantic_match from object-level facts rather than from raw string equality.

8. What counts as an Expected Difference

Some differences are explicitly allowed and explainable, such as an extension-owned object or a documented environment-specific projection. Such a row is EXPECTED_DIFFERENCE, not drift. The reason and authority must be present in the evidence; “it looks normal” is insufficient.

9. What counts as Drift

Drift is a meaningful divergence between target and runtime for an expected object when no authority permits it. A missing foreign key, an unauthorized changed default, or a materially different index definition can be drift. Drift should lead to a bounded repair and rerun rather than unrelated broad changes.

10. Preserve Unknown

When the observing role cannot see an object, the snapshot is incomplete, or the source is insufficient, do not guess ABSENT or MATCH. The correct state is UNKNOWN. The key boundary is that lack of evidence is not evidence of absence.

11. Negative probe: raw diff can mislead

One fixture changes only formatting and ordering in a schema dump. The raw diff is noisy while normalized object comparison remains equivalent. If a diagnostic system calls this drift, its comparison model is too weak. This probe forces semantic reconciliation.

12. Visibility and privilege boundaries

In the restricted-visibility fixture, a target object is not visible in information_schema and the synthetic role lacks enough catalog evidence to close the question. The result remains UNKNOWN_VISIBILITY. This lesson does not authorize privilege escalation or production access; it records the observation limit.

13. Build the record object by object

For each object capture Target Definition, Runtime Observation, Observation Source, Visibility Scope, Semantic Match, Expected Difference, Drift, Unknown, Evidence Timestamp, and Next Action. Release evidence is built only from rows whose sources and classifications are explicit.

14. Repair and rerun proportionally

For real drift, define the smallest relevant repair and rerun the same observation surface. For unknowns, do not invent a code fix; address the evidence gap. For expected differences, record their authority and keep any release conclusion bounded to that authority.

15. Transfer: reconcile the second environment independently

The transfer fixture changes schema name, visibility, a generated default, and one index definition. Do not copy classifications from the first environment. Re-establish object identity, observation source, and semantic relation. Independent defense of expected, drift, and unknown states demonstrates transfer.