# Utility Scripts Reference

# Utility Scripts Reference

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

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

- [scripts/apply-audit-patch.ts](scripts/apply-audit-patch.ts)
- [scripts/apply-emailsender-schema-patch.ts](scripts/apply-emailsender-schema-patch.ts)
- [scripts/apply-service-registry-patch.ts](scripts/apply-service-registry-patch.ts)
- [scripts/clean-drift-patches.ts](scripts/clean-drift-patches.ts)
- [scripts/fix-drift-patch.ts](scripts/fix-drift-patch.ts)
- [scripts/fix-missing-audit-records.ts](scripts/fix-missing-audit-records.ts)
- [scripts/fix-patch-registry.ts](scripts/fix-patch-registry.ts)
- [scripts/kill-port-3001.mjs](scripts/kill-port-3001.mjs)
- [scripts/remove-patch-from-registry.ts](scripts/remove-patch-from-registry.ts)
- [scripts/seed-customers.ts](scripts/seed-customers.ts)
- [scripts/setup-casdoor.ts](scripts/setup-casdoor.ts)
- [scripts/truncate-customers.ts](scripts/truncate-customers.ts)
- [scripts/update-role-mappings-timestamps.ts](scripts/update-role-mappings-timestamps.ts)
- [src/db/build-database-snapshot.ts](src/db/build-database-snapshot.ts)
- [src/db/database-patch-naming.ts](src/db/database-patch-naming.ts)
- [src/db/entity-ts-to-pg.ts](src/db/entity-ts-to-pg.ts)

</details>



This page provides a technical reference for the maintenance and utility scripts located in the `scripts/` directory. These tools are used for database management, identity provider (Casdoor) configuration, environment stabilization, and data seeding.

## 1. Database Patch Management Utilities

These scripts interact with the `public.primebrick_database_patch` registry to manage the lifecycle of SQL migrations.

### Registry Integrity and Fixes
When patch files are modified after application, the SHA256 hash in the registry becomes invalid. `fix-patch-registry.ts` reconciles the filesystem state with the database.

*   **Implementation**: It iterates through a hardcoded list of core patches, calculates the `sha256Hex` of the local file, and updates the `content_sha256` column in the database if a mismatch is detected [scripts/fix-patch-registry.ts:15-54]().
*   **Drift Cleanup**: Scripts like `clean-drift-patches.ts` and `fix-drift-patch.ts` are used to purge temporary or "drift" patches (often generated during development) from the registry to ensure a clean state for production deployments [scripts/clean-drift-patches.ts:4-14]().

### Manual Patch Application
Specific scripts exist to apply complex patches that require manual intervention or specific extensions (like `pg_partman`).

*   **Audit Table Partitioning**: `apply-audit-patch.ts` manages the setup of `pg_partman`. It ensures the `partman` schema exists, creates the extension, and applies the `customers_audit` partitioning logic [scripts/apply-audit-patch.ts:43-78]().
*   **Service Registry & Email**: Scripts like `apply-service-registry-patch.ts` read SQL files from `db-meta/patches/`, execute them, and manually insert the record into the registry to maintain idempotency [scripts/apply-service-registry-patch.ts:6-25]().

**Sources**: [scripts/fix-patch-registry.ts](), [scripts/clean-drift-patches.ts](), [scripts/apply-audit-patch.ts](), [scripts/apply-service-registry-patch.ts]()

---

## 2. Casdoor & Identity Maintenance

The `setup-casdoor.ts` script is the primary bootstrap utility for the identity provider. It performs a "Dual-Write" initialization: configuring the Casdoor PostgreSQL database directly and then synchronizing those entities into the Primebrick database.

### Implementation Logic
1.  **Direct SQL Fixes**: It connects to the `casdoor` database to update the `application` table, enabling `grant_types` (password, authorization_code, client_credentials) and setting up the `primebrick-api` client credentials [scripts/setup-casdoor.ts:43-88]().
2.  **API Synchronization**: It uses a helper `casdoorFetch` to create Organizations via Casdoor's HTTP API [scripts/setup-casdoor.ts:131-138]().
3.  **Local Mirroring**: It inserts the corresponding organization record into `public.organizations` in the Primebrick database to ensure the local DAL can resolve IDP references [scripts/setup-casdoor.ts:141-160]().

### Audit Backfilling
Because Casdoor initialization happens outside the standard application lifecycle, initial records may lack audit trails. `fix-missing-audit-records.ts` identifies the `ACME` organization and `admin` user and manually inserts `INSERT` actions into `organizations_audit` and `user_profiles_audit` [scripts/fix-missing-audit-records.ts:14-111]().

### Data Flow: Casdoor Setup
Title: Casdoor Initialization Data Flow
```mermaid
graph TD
    subgraph "Filesystem"
        ENV[".env / Config"]
    end

    subgraph "Script: setup-casdoor.ts"
        MAIN["main()"]
        SQL["Direct SQL (pg.Pool)"]
        HTTP["casdoorFetch (API)"]
    end

    subgraph "Database: casdoor"
        APP_TBL["Table: application"]
        ORG_TBL["Table: organization"]
    end

    subgraph "Database: primebrick"
        PB_ORG["Table: organizations"]
        PB_AUDIT["Table: organizations_audit"]
    end

    ENV --> MAIN
    MAIN --> SQL
    MAIN --> HTTP
    
    SQL -- "Update Client ID/Secret" --> APP_TBL
    HTTP -- "Create ACME Org" --> ORG_TBL
    MAIN -- "Mirror Org Data" --> PB_ORG
    
    subgraph "Script: fix-missing-audit-records.ts"
        FIX["Backfill Audit"]
    end
    
    PB_ORG --> FIX
    FIX --> PB_AUDIT
```
**Sources**: [scripts/setup-casdoor.ts](), [scripts/fix-missing-audit-records.ts]()

---

## 3. Data Seeding and Truncation

These scripts provide rapid environment resets for testing the Customers module.

| Script | Action | Implementation Detail |
| :--- | :--- | :--- |
| `seed-customers.ts` | Populates `public.customers` | Uses `CustomersDal` or direct SQL to insert mock data for performance testing. |
| `truncate-customers.ts` | Clears `public.customers` | Executes `TRUNCATE TABLE public.customers CASCADE;` to remove all data and related audit logs [scripts/truncate-customers.ts:46-48](). |
| `update-role-mappings-timestamps.ts` | Fixes system metadata | Updates `created_at` and `version` for default role mappings [scripts/update-role-mappings-timestamps.ts:6-14](). |

**Sources**: [scripts/truncate-customers.ts](), [scripts/update-role-mappings-timestamps.ts]()

---

## 4. Port and Environment Helpers

### Port Management
`kill-port-3001.mjs` is a Windows-specific utility (using `netstat` and `taskkill`) to clear the default backend port if a previous process crashed without releasing the socket.

*   **Logic**: It parses `netstat -ano` output to find PIDs associated with `:3001` and executes `taskkill /F /PID` on them [scripts/kill-port-3001.mjs:9-43]().

### Environment Loading
Most scripts include a `tryLoadDatabaseUrlFromEnvFile()` helper. This ensures that even when run via `tsx` or `node` outside of the main `npm start` pipeline, the script can locate the `DATABASE_URL` by manually parsing the `.env` file in the project root [scripts/truncate-customers.ts:13-34]().

### Code-to-Entity Mapping
Title: Script to Database Entity Association
```mermaid
classDiagram
    class setup_casdoor_ts {
        +toSnakeCaseLower()
        +main()
    }
    class fix_patch_registry_ts {
        +fixRegistry()
    }
    class truncate_customers_ts {
        +tryLoadDatabaseUrlFromEnvFile()
    }

    setup_casdoor_ts ..> organizations : "Inserts/Updates"
    setup_casdoor_ts ..> application : "Casdoor DB"
    fix_patch_registry_ts ..> primebrick_database_patch : "Updates SHA256"
    truncate_customers_ts ..> customers : "TRUNCATE CASCADE"
    
    class organizations {
        uuid: UUID
        idp_code: String
    }
    class primebrick_database_patch {
        patch_id: String
        content_sha256: String
    }
    class customers {
        id: Serial
        first_name: String
    }
```
**Sources**: [scripts/setup-casdoor.ts](), [scripts/fix-patch-registry.ts](), [scripts/truncate-customers.ts](), [scripts/kill-port-3001.mjs]()

---