Copy page

Microservices

Schema Snapshot & Migration Pipeline

Schema Snapshot & Migration Pipeline

Relevant source files

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

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

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

Code
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

Code
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 []

---