Backend

Utility Scripts Reference

Relevant source files

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

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

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.
API Synchronization: It uses a helper casdoorFetch to create Organizations via Casdoor's HTTP API scripts/setup-casdoor.ts:131-138.
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

Code
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

Code
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

Last modified on July 13, 2026

Server Bootstrap and Middleware Pipeline README