# Database Migrations

# Database Migrations

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

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

- [src/migrations/__tests__/apply-patches.test.ts](src/migrations/__tests__/apply-patches.test.ts)
- [src/migrations/apply-patches.ts](src/migrations/apply-patches.ts)
- [src/migrations/patch-naming.ts](src/migrations/patch-naming.ts)
- [src/migrations/patch-registry.ts](src/migrations/patch-registry.ts)
- [src/ports/database-port.ts](src/ports/database-port.ts)

</details>



The migrations module provides a robust, idempotent mechanism for applying SQL patches to a database. It is designed to be database-agnostic by relying on the `DatabasePort` interface, allowing consuming microservices to use any SQL driver while maintaining a consistent migration strategy.

## Patch Registry and DDL

Before any patches are processed, the system ensures the existence of a registry table. This table tracks which patches have been applied and records a SHA-256 hash of their contents to guarantee immutability.

The registry is defined in `src/migrations/patch-registry.ts` and consists of:
- **Table Name**: `public.primebrick_database_patches` [src/migrations/patch-registry.ts:3-3]()
- **Primary Key**: `patch_id` (the filename without extension) [src/migrations/patch-registry.ts:6-6]()
- **Content Tracking**: `content_sha256` (SHA-256 hex digest of the file body) [src/migrations/patch-registry.ts:7-7]()
- **Metadata**: `applied_at` timestamp [src/migrations/patch-registry.ts:8-8]()
- **Index**: An index on `content_sha256` to speed up duplicate detection [src/migrations/patch-registry.ts:10-11]()

### Data Flow: Initialization
The `applyPatches` function always executes the `PATCH_REGISTRY_DDL` statement as its first action to ensure the environment is ready [src/migrations/apply-patches.ts:31-31]().

```mermaid
graph TD
    subgraph "Natural Language Space"
        A["Ensure Registry Table Exists"]
        B["Read Patch Files"]
    end

    subgraph "Code Entity Space"
        A1["applyPatches()"]
        A2["PATCH_REGISTRY_DDL"]
        A3["DatabasePort.query()"]
        
        A1 -->|calls| A3
        A2 -.->|SQL Content| A3
    end
    
    A --> A1
    B --> A1
```
Sources: [src/migrations/apply-patches.ts:30-31](), [src/migrations/patch-registry.ts:5-12]()

## Patch Naming and Identification

The SDK enforces specific naming conventions to ensure patches are applied in a predictable, chronological order.

| Function | Purpose | Implementation Detail |
| :--- | :--- | :--- |
| `utcTimestampForFilename` | Generates a sortable timestamp | Formats current date as `YYYYMMDDHHMMSS` [src/migrations/patch-naming.ts:3-9]() |
| `slugifyPatchSegment` | Sanitizes descriptions | Replaces non-alphanumeric chars with `_`, limits to 72 chars [src/migrations/patch-naming.ts:11-18]() |
| `patchIdFromFilename` | Derives the DB ID | Removes the `.sql` extension from the filename [src/migrations/patch-naming.ts:20-22]() |
| `sha256Hex` | Calculates file integrity | Uses `node:crypto` to create a hex digest of the SQL body [src/migrations/patch-naming.ts:24-26]() |

Sources: [src/migrations/patch-naming.ts:1-27]()

## The Four-Case Patch Strategy

The `applyPatches` function implements a specific logic flow for every `.sql` file found in the target directory [src/migrations/apply-patches.ts:15-21](). Files are sorted alphabetically by filename before processing [src/migrations/apply-patches.ts:35-35]().

### 1. Skip (Already Applied)
If the `patch_id` exists in the registry and the `content_sha256` matches the current file, the patch is skipped [src/migrations/apply-patches.ts:60-63]().

### 2. Reject (SHA Mismatch)
If the `patch_id` exists but the `content_sha256` is different, the system throws an error. This prevents "floating" migrations where a previously applied file is modified [src/migrations/apply-patches.ts:65-67]().

### 3. Register-Only (Duplicate Content)
If the `patch_id` is new, but the `content_sha256` already exists under a different ID, the system inserts the new `patch_id` into the registry without re-executing the SQL. This handles cases where the same schema change is introduced under a new filename [src/migrations/apply-patches.ts:74-83]().

### 4. Apply (New Patch)
If neither the ID nor the SHA exists, the system:
1. Starts a transaction (`BEGIN`) [src/migrations/apply-patches.ts:87-87]().
2. Executes the raw SQL content [src/migrations/apply-patches.ts:88-88]().
3. Inserts the registry row [src/migrations/apply-patches.ts:89-92]().
4. Commits the transaction (`COMMIT`) [src/migrations/apply-patches.ts:93-93]().

If any step fails, it performs a `ROLLBACK` [src/migrations/apply-patches.ts:96-96]().

### Strategy Logic Diagram

```mermaid
flowchart TD
    START["applyPatches(dir, db)"] --> READ["Read & Sort *.sql files"]
    READ --> LOOP["For each filename"]
    LOOP --> CALC["Calculate SHA-256 & patch_id"]
    CALC --> CHECK_ID{"patch_id exists?"}
    
    CHECK_ID -- "Yes" --> CHECK_SHA{"SHA matches?"}
    CHECK_SHA -- "Yes" --> SKIP["Skip (Case 1)"]
    CHECK_SHA -- "No" --> FAIL["Throw Error (Case 2)"]
    
    CHECK_ID -- "No" --> CHECK_SHA_GLOBAL{"SHA exists globally?"}
    CHECK_SHA_GLOBAL -- "Yes" --> REG_ONLY["Register ID only (Case 3)"]
    CHECK_SHA_GLOBAL -- "No" --> APPLY["BEGIN -> EXEC SQL -> INSERT -> COMMIT (Case 4)"]
    
    APPLY --> NEXT["Next file"]
    SKIP --> NEXT
    REG_ONLY --> NEXT
    NEXT --> LOOP
```
Sources: [src/migrations/apply-patches.ts:47-99]()

## Database Port Integration

The migration runner does not depend on a specific database driver like `pg`. Instead, it consumes the `DatabasePort` interface [src/ports/database-port.ts:11-17]().

### DatabasePort Interface
```typescript
export interface DatabasePort {
  query<T = unknown>(text: string, params?: unknown[]): Promise<{ rows: T[] }>;
}
```
The `applyPatches` function uses this port to:
- Run the registry DDL [src/migrations/apply-patches.ts:31-31]().
- Check for existing patches [src/migrations/apply-patches.ts:53-56]().
- Manage transactions (`BEGIN`, `COMMIT`, `ROLLBACK`) [src/migrations/apply-patches.ts:87-96]().

Sources: [src/ports/database-port.ts:1-17](), [src/migrations/apply-patches.ts:23-27]()

---