Copy page

Backend

Database Management

Database Management

Relevant source files

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

• .githooks/post-merge
• db-meta/diff-entities-vs-database.json
• db-meta/snapshot-database.json
• db-meta/snapshot-entities.json
• scripts/database-patch-apply.ts
• scripts/database-patch-compare.ts

The Primebrick v3 backend utilizes a custom, automated database evolution system that bridges the gap between TypeScript @Entity definitions and the physical PostgreSQL schema. Instead of traditional manually-written migration files, the system uses a Snapshot and Diff approach to generate SQL patches based on the detected delta between the application's intent and the current database state.

System Architecture

The database management lifecycle is divided into two main phases: Generation (development-time) and Application (deployment/sync-time).

Database Management Flow

The following diagram illustrates how code entities are transformed into database changes and tracked via the patch registry.

Code
graph TD
    subgraph "Code Entity Space"
        A["ENTITY_REGISTRY"] -- "buildEntitySnapshot()" --> B["snapshot-entities.json"]
    end

    subgraph "Natural Language & Intent"
        B -- "compareSnapshots()" --> C["diff-entities-vs-database.json"]
        D["PostgreSQL Instance"] -- "buildDatabaseSnapshot()" --> E["snapshot-database.json"]
        E -- "compareSnapshots()" --> C
    end

    subgraph "Migration & Persistence"
        C -- "buildSqlPatchFromMetaDiff()" --> F["UTC_slug.sql Patch"]
        F -- "db:migrate (database-patch-apply.ts)" --> G["primebrick_database_patch"]
        G -- "Records SHA256" --> D
    end

    style A stroke-width:2px
    style D stroke-width:2px
    style G stroke-width:2px

Sources: scripts/database-patch-compare.ts:61-82, scripts/database-patch-apply.ts:124-133, src/db/database-patch-registry.ts:1-10

---

Schema Snapshot and Diff System

The core of the migration logic lies in the db-meta/ directory, which stores the state of both the code and the database in JSON format.

1. snapshot-entities.json: A canonical representation of all classes in the ENTITY_REGISTRY decorated with @Entity db-meta/snapshot-entities.json:1-10.
2. snapshot-database.json: A structural dump of the actual PostgreSQL tables, columns, and types db-meta/snapshot-database.json:1-10.
3. diff-entities-vs-database.json: The result of compareSnapshots(), categorizing differences into typeMismatches, nullabilityMismatches, onlyInEntityTables, and likelyRenames db-meta/diff-entities-vs-database.json:1-15.

The command pnpm run db:meta:compare (driven by scripts/database-patch-compare.ts) performs this comparison and generates a timestamped SQL file in db-meta/patches/ if changes are detected scripts/database-patch-compare.ts:75-82.

For details, see Schema Snapshot and Diff System.

---

Migration Patch Registry and Application

The application of changes is handled by scripts/database-patch-apply.ts (invoked via pnpm run db:migrate). This script ensures that migrations are applied exactly once and remain immutable.

Patch Idempotency and Integrity

The system tracks applied patches in a specialized table: public.primebrick_database_patch scripts/database-patch-apply.ts:23-24.

Feature	Implementation
Ordering	Patches are applied in alphabetical order based on their UTC timestamp prefix scripts/database-patch-apply.ts:61-68.
Idempotency	The content_sha256 of the SQL body is checked. If the same content exists under a different filename, it is registered without re-execution scripts/database-patch-apply.ts:107-122.
Immutability	If a patch_id exists in the registry but the file content has changed, the process fails to prevent corrupted migration histories scripts/database-patch-apply.ts:101-105.
Automation	A post-merge git hook automatically runs db:migrate after a git pull to keep the local environment in sync .githooks/post-merge:24-25.

For details, see Migration Patch Registry and Application.

---

Key Components Reference

Component	Code Entity / Path	Role
Entity Registry	ENTITY_REGISTRY	The source of truth for desired database state src/domain/entities/registry.ts.
Registry Table	primebrick_database_patch	Tracks patch_id and content_sha256 src/db/database-patch-registry.ts.
Compare Script	scripts/database-patch-compare.ts	Generates snapshots and new .sql patches scripts/database-patch-compare.ts:1-14.
Apply Script	scripts/database-patch-apply.ts	Executes pending patches against the database scripts/database-patch-apply.ts:1-16.
Post-Merge Hook	.githooks/post-merge	Triggers migrations automatically on git updates .githooks/post-merge:1-25.

Sources: scripts/database-patch-compare.ts:1-14, scripts/database-patch-apply.ts:1-16, .githooks/post-merge:1-25, src/db/database-patch-registry.ts:23-24

---