Copy page

Backend

Migration Patch Registry and Application

Migration Patch Registry and Application

Relevant source files

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

• .githooks/post-merge
• db-meta/patches/00000000000000_init_database.sql
• scripts/database-patch-apply.ts
• scripts/database-patch-compare.ts
• scripts/fix-patch-registry.ts
• scripts/update-role-mappings-timestamps.ts
• src/db/build-entity-snapshot.ts
• src/db/database-patch-registry.ts
• src/db/database-patch-to-sql.ts
• src/db/schema-types.ts
• src/lib/audit/audit-service.ts
• src/modules/auth/role-mapping-repo.ts
• src/modules/auth/role_mapping_entity.ts

The Primebrick backend utilizes a custom, idempotent migration system that bridges the gap between TypeScript @Entity definitions and the PostgreSQL physical schema. Unlike traditional migration frameworks that rely on manual version numbers, this system uses structural diffing and a SHA256-based registry to ensure consistency across environments.

Overview and Purpose

The migration workflow is designed to be safe, repeatable, and automated. It consists of two primary phases:

1. Generation: Comparing the ENTITY_REGISTRY against the current database state to produce a timestamped SQL patch.
2. Application: Executing pending patches and recording their success in a dedicated registry table.

Key Components

• db:migrate: The command used to apply pending patches scripts/database-patch-apply.ts:1-16.
• primebrick_database_patches: A registry table that tracks applied patches via their unique ID and content hash src/db/database-patch-registry.ts:6-14.
• Idempotency: The system checks if a patch's content_sha256 is already recorded before execution, preventing duplicate runs of identical SQL scripts/database-patch-apply.ts:107-122.

---

The Patch Registry Table

The primebrick_database_patches table (aliased as PATCH_REGISTRY_FQNAME) is the single source of truth for the migration state of a target database.

Column	Type	Description
patch_id	text (PK)	The filename of the patch (e.g., 20260529150000_init.sql).
content_sha256	text	A SHA256 hex hash of the entire SQL file content.
applied_at	timestamptz	The server time when the patch was successfully committed.

Sources: src/db/database-patch-registry.ts:4-14

Data Flow: Patch Registration

When database-patch-apply.ts runs, it ensures the registry table exists by executing PATCH_REGISTRY_DDL scripts/database-patch-apply.ts:74.

Code
graph TD
    A["Start db:migrate"] --> B["Ensure public.primebrick_database_patches exists"]
    B --> C["Read db-meta/patches/*.sql"]
    C --> D["Sort by filename (Timestamp)"]
    D --> E{Check patch_id in Registry}
    E -- "Exists (Same SHA)" --> F["Skip (Already Applied)"]
    E -- "Exists (Different SHA)" --> G["FAIL: Immutable Patch Changed"]
    E -- "Missing" --> H{Check SHA in Registry}
    H -- "SHA Found under other ID" --> I["Register ID only (Skip SQL)"]
    H -- "SHA Not Found" --> J["BEGIN; Execute SQL; Insert Registry; COMMIT"]

Sources: scripts/database-patch-apply.ts:84-139, src/db/database-patch-registry.ts:17-28

---

SQL Patch Generation

Patches are generated by database-patch-compare.ts. This script compares a snapshot of the current @Entity models (Entity Intent) against a snapshot of the live database (Target State) scripts/database-patch-compare.ts:61-76.

Automatic Audit Table Generation

If an entity is decorated with @AuditTrail(), the generator automatically appends DDL for the corresponding _audit table, including partitioning via pg_partman src/db/database-patch-to-sql.ts:103-128.

Heuristic Rename Handling

The system detects potential renames (column or table) but flags them for manual review if they are ambiguous. Ambiguous renames are generated as commented-out SQL in the patch file src/db/database-patch-to-sql.ts:151-165.

Patch Metadata Footer

Every generated .sql file includes a metadata footer used for manual registry updates or debugging:

Code
-- === database patch registry (repeatable runs) ===
-- patch_id: 20260529150000_init
-- content_sha256: <hash>

Sources: scripts/database-patch-compare.ts:113-131, src/db/database-patch-to-sql.ts:48-52

---

Automation and Hooks

Git Post-Merge Hook

To ensure developers' local databases stay in sync with the repository, a post-merge hook is provided. It automatically runs pnpm run db:migrate after a git pull or git merge, unless PB_SKIP_POST_MERGE_DB_MIGRATE is set to 1 .githooks/post-merge:7-25.

Patch Registry Maintenance

In cases where a patch file was modified after application (e.g., fixing a comment that changed the SHA256), the fix-patch-registry.ts script can be used to synchronize the database registry with the local file system's current hashes scripts/fix-patch-registry.ts:14-57.

---

Technical Entity Mapping

The following diagram maps the logical migration entities to their implementation classes and files.

Code
classDiagram
    class EntityRegistry {
        <<ENTITY_REGISTRY>>
        src/domain/entities/registry.ts
    }
    class SnapshotBuilder {
        buildEntitySnapshot()
        buildDatabaseSnapshot()
        src/db/build-entity-snapshot.ts
    }
    class DiffEngine {
        compareSnapshots()
        src/db/schema-snapshot.ts
    }
    class SqlGenerator {
        buildSqlPatchFromMetaDiff()
        src/db/database-patch-to-sql.ts
    }
    class Applier {
        database-patch-apply.ts
        scripts/database-patch-apply.ts
    }

    EntityRegistry --> SnapshotBuilder : Inputs
    SnapshotBuilder --> DiffEngine : Snapshots
    DiffEngine --> SqlGenerator : Diff
    SqlGenerator --> Applier : SQL Files
    Applier --> "primebrick_database_patches" : Updates

Sources: scripts/database-patch-compare.ts:19-26, src/db/database-patch-registry.ts:4-14, src/db/database-patch-to-sql.ts:48-49

Registry Logic Summary

• Idempotency Strategy: Uses content_sha256 to detect if the same logical change was applied under a different filename scripts/database-patch-apply.ts:107-122.
• Transaction Safety: Each patch is executed within a single SQL transaction (BEGIN/COMMIT) scripts/database-patch-apply.ts:126-132.
• Sorting: Patches are applied in lexicographical order, which corresponds to the UTC timestamp prefix (YYYYMMDDHHMMSS) scripts/database-patch-apply.ts:61-69.

Sources: scripts/database-patch-apply.ts:1-16, src/db/database-patch-naming.ts:21-24

---