Copy page

Backend

Schema Snapshot and Diff System

Schema Snapshot and Diff System

Relevant source files

The following files were used as context for generating this wiki page:

• db-meta/diff-entities-vs-database.json
• db-meta/snapshot-database.json
• db-meta/snapshot-entities.json
• scripts/seed-customers.ts
• src/db/build-database-snapshot.ts
• src/db/database-patch-naming.ts
• src/db/entity-ts-to-pg.ts
• src/db/schema-rename-heuristics.ts
• src/db/schema-snapshot.ts
• src/db/schema-type-normalize.ts

The Schema Snapshot and Diff System is a specialized infrastructure designed to detect structural "drift" between the TypeScript source of truth (the @Entity definitions) and the actual state of the PostgreSQL database. It facilitates the automated generation of migration patches by categorizing differences into additions, removals, type mismatches, and potential column renames.

System Architecture and Data Flow

The system operates by generating two JSON "snapshots" and then executing a comparison algorithm to produce a detailed diff.

Data Flow Diagram

The following diagram illustrates how entity metadata and database catalog information are processed into snapshots and finally compared.

"Schema Comparison Lifecycle"

Code
graph TD
    subgraph "Entity Space"
        A["@Entity Classes"] -- "reflect-metadata" --> B["ENTITY_REGISTRY"]
        B -- "inferPgTypeFromEntityColumn()" --> C["snapshot-entities.json"]
    end

    subgraph "Database Space"
        D["PostgreSQL Catalog"] -- "buildDatabaseSnapshot()" --> E["snapshot-database.json"]
    end

    C & E -- "compareSnapshots()" --> F["diff-entities-vs-database.json"]
    
    subgraph "Output Space"
        F -- "patchSlugFromDiff()" --> G["SQL Migration Patch"]
        F -- "RENAME_HEURISTIC_AGENT_INSTRUCTION" --> H["User Review Required"]
    end

Sources: src/db/schema-snapshot.ts:62-161, src/db/build-database-snapshot.ts:46-142, src/db/database-patch-naming.ts:23-44

Snapshot Components

1. Entity Snapshot (snapshot-entities.json)

This file represents the "intended" state of the database. It is generated by iterating through the ENTITY_REGISTRY.

• Type Inference: The system uses inferPgTypeFromEntityColumn to map TypeScript types (e.g., Number, String, Date) to PostgreSQL base types (e.g., bigint, text, timestamptz).
• Heuristics: If emitDecoratorMetadata is unavailable, the system uses naming heuristics (e.g., columns ending in _at default to timestamptz).

2. Database Snapshot (snapshot-database.json)

This file represents the "actual" state. It is generated via the buildDatabaseSnapshot function which queries pg_catalog tables.

• Introspection: It queries pg_attribute, pg_type, and pg_class to extract OIDs, type categories, and nullability.
• Primary Keys: It uses loadPrimaryKeyColumns to identify PK constraints which are not part of the standard column metadata.

Sources: src/db/entity-ts-to-pg.ts:140-146, src/db/build-database-snapshot.ts:17-39, db-meta/snapshot-database.json:1-10

Comparison and Diffing Logic

The compareSnapshots function in src/db/schema-snapshot.ts performs a deep comparison between the two JSON files.

Diff Categories

The resulting diff-entities-vs-database.json categorizes changes into:

Category	Description
onlyInEntityTables	Tables defined in code but missing from the database.
onlyInDatabaseTables	Tables existing in the database but removed from code.
typeMismatches	Columns where the comparable PG type differs (e.g., varchar vs text).
nullabilityMismatches	Columns where isNullable differs between entity and DB.
likelyRenames	Columns detected as renames rather than a drop/add pair.

Sources: src/db/schema-snapshot.ts:20-57, db-meta/diff-entities-vs-database.json:1-25

Type Normalization

To avoid "noisy" diffs caused by PostgreSQL aliases (e.g., bool vs boolean), the system uses comparablePgType. This function maps various aliases into a stable string for equality checks.

Sources: src/db/schema-type-normalize.ts:20-63

Heuristic Rename Review Process

One of the most complex parts of the system is the findLikelyRenames logic in src/db/schema-rename-heuristics.ts. Instead of treating a missing column and a new column as two separate operations, the system attempts to pair them as a RENAME COLUMN operation.

Match Tiers

Renames are evaluated using a "ladder" of strict-to-loose rules:

1. Tier 0: Compatible type AND same ordinalPosition.
2. Tier 1: Compatible type AND adjacent ordinalPosition (delta 1).
3. Tier 2: Compatible type AND ordinalPosition delta of 2.
4. Tier 5: Compatible type when ordinal metadata is unavailable.

Ambiguity and User Review

If the heuristic detects multiple valid candidates for a single rename (e.g., two new text columns where one old text column was removed), it marks userReviewRequired: true.

"Heuristic Rename Logic"

Code
graph TD
    A["rawOnlyInEntity"] --> B["classifyRenamePair()"]
    C["rawOnlyInDb"] --> B
    B -- "Tier 0-5 Match" --> D["Candidate Pair"]
    D -- "Multiple Candidates?" --> E{"Ambiguity?"}
    E -- "Yes" --> F["userReviewRequired = true"]
    E -- "No" --> G["likelyRenames List"]
    F -- "RENAME_HEURISTIC_AGENT_INSTRUCTION" --> H["Stop & Ask User"]

Sources: src/db/schema-rename-heuristics.ts:44-51, src/db/schema-rename-heuristics.ts:86-133, src/db/schema-snapshot.ts:153-158

Key Functions and Classes

compareSnapshots

src/db/schema-snapshot.ts:62-161 The entry point for generating a diff. It iterates through all tables and columns, invoking the rename heuristics and type normalization logic.

inferPgTypeFromEntityColumn

src/db/entity-ts-to-pg.ts:140-146 Resolves the SQL type name for an entity column. It prioritizes explicit @Column({ pgType }) hints, then falls back to design:type metadata, and finally uses naming heuristics.

buildDatabaseSnapshot

src/db/build-database-snapshot.ts:46-142 Connects to the database via a pg.Pool and executes catalog queries to build a SchemaSnapshot object representing the current physical schema.

patchSlugFromDiff

src/db/database-patch-naming.ts:23-44 Analyzes a SchemaMetaDiffV2 object to generate a human-readable slug for the migration filename (e.g., addcols_customers or rename_user_profiles).

Sources: src/db/schema-snapshot.ts:62-161, src/db/entity-ts-to-pg.ts:140-146, src/db/build-database-snapshot.ts:46-142, src/db/database-patch-naming.ts:23-44

---