# Schema Snapshot & Migration Pipeline

# Schema Snapshot & Migration Pipeline

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

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

- [emailsender/db-meta/diff-entities-vs-database.json](emailsender/db-meta/diff-entities-vs-database.json)
- [emailsender/db-meta/patches/20260528114750_create_emailsender_email_config_emailsender_email_templates.sql](emailsender/db-meta/patches/20260528114750_create_emailsender_email_config_emailsender_email_templates.sql)
- [emailsender/db-meta/snapshot-database.json](emailsender/db-meta/snapshot-database.json)
- [emailsender/db-meta/snapshot-entities.json](emailsender/db-meta/snapshot-entities.json)
- [emailsender/scripts/apply-patch.ts](emailsender/scripts/apply-patch.ts)
- [emailsender/scripts/build-database-snapshot.ts](emailsender/scripts/build-database-snapshot.ts)
- [emailsender/scripts/build-entity-snapshot.ts](emailsender/scripts/build-entity-snapshot.ts)
- [emailsender/scripts/compare-snapshots.ts](emailsender/scripts/compare-snapshots.ts)
- [emailsender/scripts/generate-patch.ts](emailsender/scripts/generate-patch.ts)
- [emailsender/src/db/build-database-snapshot.ts](emailsender/src/db/build-database-snapshot.ts)
- [emailsender/src/db/build-entity-snapshot.ts](emailsender/src/db/build-entity-snapshot.ts)
- [emailsender/src/db/database-patch-naming.ts](emailsender/src/db/database-patch-naming.ts)
- [emailsender/src/db/database-patch-to-sql.ts](emailsender/src/db/database-patch-to-sql.ts)
- [emailsender/src/db/entity-ts-to-pg.ts](emailsender/src/db/entity-ts-to-pg.ts)
- [emailsender/src/db/schema-rename-heuristics.ts](emailsender/src/db/schema-rename-heuristics.ts)
- [emailsender/src/db/schema-snapshot.ts](emailsender/src/db/schema-snapshot.ts)
- [emailsender/src/db/schema-type-normalize.ts](emailsender/src/db/schema-type-normalize.ts)

</details>



The Primebrick v3 Microservices architecture employs a custom, metadata-driven migration pipeline. Instead of manual migration files, the system generates SQL patches by comparing the state of TypeScript entities (the "Source of Truth") against the live PostgreSQL database schema.

## Pipeline Overview

The migration workflow follows a unidirectional flow from TypeScript decorators to SQL execution. It is designed to be idempotent and provides a registry to track applied patches.

### Data Flow Diagram

```mermaid
graph TD
    subgraph "Entity Space"
        [ENTITY_REGISTRY] -- "Reflect Metadata" --> [buildEntitySnapshot]
        [DecoratorMetadata] -- "Type Mapping" --> [buildEntitySnapshot]
    end

    subgraph "Database Space"
        [PostgreSQL] -- "Introspection" --> [buildDatabaseSnapshot]
    end

    subgraph "Comparison & Generation"
        [buildEntitySnapshot] -- "snapshot-entities.json" --> [compareSnapshots]
        [buildDatabaseSnapshot] -- "snapshot-database.json" --> [compareSnapshots]
        [compareSnapshots] -- "diff-entities-vs-database.json" --> [buildSqlPatchFromMetaDiff]
    end

    subgraph "Persistence"
        [buildSqlPatchFromMetaDiff] -- "SQL + SHA256" --> [PatchFile]
        [PatchFile] -- "apply-patch.ts" --> [PostgreSQL]
        [apply-patch.ts] -- "Audit" --> [primebrick_database_patch]
    end
```
**Sources:** `emailsender/scripts/generate-patch.ts:46-84` [], `emailsender/src/db/schema-snapshot.ts:1-13` []

---

## Snapshot Building

The system uses two distinct introspection methods to create standardized `SchemaSnapshot` objects.

### 1. Entity Snapshotting
The `buildEntitySnapshot` function iterates through the `ENTITY_REGISTRY` [emailsender/src/db/build-entity-snapshot.ts:7-7](). It uses `getEntityPersistenceMeta` [emailsender/src/db/build-entity-snapshot.ts:8-8]() to extract metadata defined via decorators (e.g., `@Column`, `@Key`). 
- **Type Mapping:** TypeScript types are converted to PostgreSQL types using `pgType` or `inferredPgType` [emailsender/src/db/build-entity-snapshot.ts:13-13]().
- **Output:** A JSON representation of the intended schema, including primary keys, nullability, and default SQL expressions [emailsender/db-meta/snapshot-entities.json:5-160]().

### 2. Database Snapshotting
The `buildDatabaseSnapshot` function connects to the live database using a connection pool [emailsender/src/db/build-database-snapshot.ts:51-51]().
- **Introspection:** It queries `pg_catalog.pg_attribute`, `pg_type`, and `pg_class` rather than `information_schema` to support advanced types like PostGIS or custom UDTs [emailsender/src/db/build-database-snapshot.ts:43-45]().
- **Primary Key Detection:** It specifically identifies primary keys by joining `pg_index` and `pg_attribute` [emailsender/src/db/build-database-snapshot.ts:23-33]().

**Sources:** `emailsender/src/db/build-entity-snapshot.ts:5-39` [], `emailsender/src/db/build-database-snapshot.ts:46-142` []

---

## Comparison & Diffing

The `compareSnapshots` function identifies discrepancies between the two snapshots.

| Diff Category | Description |
| :--- | :--- |
| `onlyInEntityTables` | Tables defined in code but missing in the DB. |
| `onlyInDatabaseTables` | Tables in the DB not defined in the code (ignored for safety). |
| `onlyInEntityColumns` | New columns to be added to existing tables. |
| `typeMismatches` | Columns where the DB type differs from the Entity definition. |
| `likelyRenames` | Heuristic-based detection of potential column renames. |

### Rename Heuristics
If a column is removed and a new one is added simultaneously, the system flags a `renameHeuristicUserReviewRequired` [emailsender/scripts/generate-patch.ts:86-90](). This prevents accidental data loss during structural changes.

**Sources:** `emailsender/src/db/schema-snapshot.ts:1-15` [], `emailsender/db-meta/diff-entities-vs-database.json:1-10` []

---

## Patch Generation & Application

### SQL Generation
The `buildSqlPatchFromMetaDiff` function converts the diff into valid PostgreSQL DDL [emailsender/scripts/generate-patch.ts:62-62]().
- **Naming Convention:** Patches are named using a UTC timestamp and a slugified description of the change (e.g., `20260528114750_create_emailsender_email_config.sql`) [emailsender/src/db/database-patch-naming.ts:46-48]().
- **SHA256 Integrity:** Each patch includes a footer with a SHA256 hash of its contents to ensure the file has not been tampered with before application [emailsender/src/db/database-patch-naming.ts:54-56]().

### Patch Application Logic
The `apply-patch.ts` script performs the following:
1. **Execution:** Reads the SQL file and executes it against the pool [emailsender/scripts/apply-patch.ts:10-13]().
2. **Registration:** Inserts the `patch_id` and `content_sha256` into the `public.primebrick_database_patch` table [emailsender/scripts/apply-patch.ts:25-28](). This prevents the same patch from being applied multiple times.

**Sources:** `emailsender/scripts/generate-patch.ts:69-84` [], `emailsender/scripts/apply-patch.ts:6-37` [], `emailsender/db-meta/patches/20260528114750_create_emailsender_email_config_emailsender_email_templates.sql:46-53` []

---

## Key Automation Scripts

The following scripts in `emailsender/scripts/` manage the pipeline:

| Script | Purpose |
| :--- | :--- |
| `build-entity-snapshot.ts` | Generates `snapshot-entities.json` from TypeScript files. |
| `build-database-snapshot.ts` | Generates `snapshot-database.json` by introspecting the live DB. |
| `compare-snapshots.ts` | Produces `diff-entities-vs-database.json`. |
| `generate-patch.ts` | Orchestrates the full snapshot-compare-generate flow. |
| `apply-patch.ts` | Executes a specific SQL patch and updates the registry. |

### Code-to-System Mapping

```mermaid
graph LR
    subgraph "Scripts"
        [gen_patch["generate-patch.ts"]]
        [app_patch["apply-patch.ts"]]
    end

    subgraph "Core Utilities"
        [meta_diff["src/db/schema-snapshot.ts"]]
        [sql_gen["src/db/database-patch-to-sql.ts"]]
        [db_pool["src/db/pool.ts"]]
    end

    gen_patch -- "calls" --> meta_diff
    gen_patch -- "calls" --> sql_gen
    app_patch -- "uses" --> db_pool
    
    subgraph "Output Files"
        [sql_file[".sql Patch File"]]
        [diff_json["diff-entities-vs-database.json"]]
    end

    sql_gen -- "writes" --> sql_file
    meta_diff -- "writes" --> diff_json
```

**Sources:** `emailsender/scripts/build-entity-snapshot.ts:1-8` [], `emailsender/scripts/build-database-snapshot.ts:1-20` [], `emailsender/scripts/compare-snapshots.ts:1-16` [], `emailsender/scripts/generate-patch.ts:1-94` [], `emailsender/scripts/apply-patch.ts:1-42` []

---