# Schema Snapshot and Diff System

# Schema Snapshot and Diff System

<details>
<summary>Relevant source files</summary>

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

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

</details>



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"
```mermaid
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"
```mermaid
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]()

---