# Database Management

# Database Management

<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/diff-entities-vs-database.json](db-meta/diff-entities-vs-database.json)
- [db-meta/snapshot-database.json](db-meta/snapshot-database.json)
- [db-meta/snapshot-entities.json](db-meta/snapshot-entities.json)
- [scripts/database-patch-apply.ts](scripts/database-patch-apply.ts)
- [scripts/database-patch-compare.ts](scripts/database-patch-compare.ts)

</details>



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.

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

---

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

---

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

---