# Migration Patch Registry and Application

# Migration Patch Registry and Application

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

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

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

</details>



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]().

```mermaid
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:
```sql
-- === 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.

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

---