Copy page

Backend

Repository and Query DSL

Repository and Query DSL

Relevant source files

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

• src/db/pool.ts
• src/db/repository/README.md
• src/db/repository/audit-join-helper.ts
• src/db/repository/auditable-joins.ts
• src/db/repository/auditable-types.ts
• src/db/repository/dsl.ts
• src/db/repository/query-builder.ts
• src/db/repository/repository.ts
• src/db/repository/types.ts
• src/lib/audit/delta-calculator.ts

The Primebrick backend utilizes a generic Repository pattern combined with a type-safe Domain Specific Language (DSL) for SQL query construction. This system abstracts raw PostgreSQL interactions into a structured, entity-aware API that handles filtering, sorting, joining, and automatic audit logging.

Repository Class

The Repository class src/db/repository/repository.ts:27 is the primary interface for data access. It accepts a PostgreSQL Queryable (Pool or Client) and an optional AuditService. It translates high-level DSL expressions into optimized SQL via the query-builder.

Key Methods

• insertMany: Performs bulk inserts using a single INSERT ... VALUES statement src/db/repository/repository.ts:45-114. It automatically skips identity columns and triggers audit logging for auditable entities.
• findById: Fetches a single record by its primary key src/db/repository/repository.ts:116-140.
• findByPage: Provides structured pagination, returning both the entity list and a total record count src/db/repository/repository.ts:177-209.
• update: Handles updates with optimistic concurrency control. It checks the version column to ensure no mid-air collisions occur src/db/repository/repository.ts:241-322.

Type-Safe Query DSL

The DSL allows developers to construct queries using TypeScript objects instead of raw strings, ensuring that field names correspond to actual entity properties.

DSL Components

Component	Function	Description
field	field(Entity, "prop")	References a specific property on an entity src/db/repository/dsl.ts:28-33.
Filter	Filter.fieldValue(...)	Defines WHERE clauses (supports =, ILIKE, IN, BETWEEN, etc.) src/db/repository/dsl.ts:63-86.
Sort	Sort.by(...)	Defines ORDER BY clauses src/db/repository/dsl.ts:89-93.
Join	Join.on(...)	Defines JOIN logic between two entities src/db/repository/dsl.ts:102-111.
Project	Project.field(...)	Defines specific columns to return in the SELECT clause src/db/repository/dsl.ts:117-124.

Sources: src/db/repository/repository.ts:27-28, src/db/repository/dsl.ts:3-21, src/db/repository/dsl.ts:35-61

Query Execution Flow

The following diagram illustrates how a DSL request is transformed into a database result.

DSL to SQL Execution Flow

Code
graph TD
    subgraph "Code Space"
        A["DAL Method"] --> B["DSL Expressions (Filter, Sort, Join)"]
        B --> C["Repository.findAll / findByPage"]
    end

    subgraph "Query Engine"
        C --> D["buildSelectQuery (query-builder.ts)"]
        D --> E["renderFilterExpr"]
        D --> F["renderJoins"]
        D --> G["renderProjection"]
    end

    subgraph "Database Space"
        E & F & G --> H["pg.Pool.query()"]
        H --> I["PostgreSQL Table"]
    end
    
    I --> J["Result Rows"]
    J --> K["Entity Mapping"]

Sources: src/db/repository/query-builder.ts:126-155, src/db/repository/query-builder.ts:156-187, src/db/repository/repository.ts:160-175

Auditable Joins and Display Names

Entities implementing IAuditableEntity store user UUIDs in fields like created_by and updated_by. To resolve these to human-readable names, the system uses "Auditable Joins".

• Guardrail Pattern: Joins to user_profiles use a regex check ~ '^[0-9a-fA-F-]{36}$' to ensure the field contains a valid UUID before attempting a cast, preventing errors when fields contain strings like "system" src/db/repository/auditable-joins.ts:9-11.
• Automatic Projection: The query-builder automatically maps join aliases (creator, updater, deleter) to virtual fields like created_by_name src/db/repository/query-builder.ts:85-102.
• Utility Types: WithAuditableDisplayNames<T> is used to type the resulting rows including these virtual name fields src/db/repository/auditable-types.ts:23-27.

Sources: src/db/repository/auditable-joins.ts:15-36, src/db/repository/README.md:64-83, src/db/repository/audit-join-helper.ts:13-19

Optimistic Concurrency and Audit Logging

The repository enforces data integrity and traceability during updates through the version column and a delta calculator.

Update and Audit Sequence

Code
sequenceDiagram
    participant R as Repository
    participant DC as Delta Calculator
    participant DB as PostgreSQL
    participant AS as Audit Service

    R->>DB: SELECT current record (for delta)
    DB-->>R: Old Entity Data
    R->>DC: calculateDelta(old, new)
    DC-->>R: { field: { old: val, new: val } }
    
    R->>DB: UPDATE ... SET version = version + 1 WHERE id = $1 AND version = $2
    
    alt Version Mismatch
        DB-->>R: rowCount == 0
        R-->>R: Throw OptimisticConcurrencyException
    else Success
        DB-->>R: rowCount == 1
        R->>AS: writeAudit(entity, delta, action)
    end

Sources: src/db/repository/repository.ts:241-322, src/lib/audit/delta-calculator.ts:1-9, src/db/repository/repository.ts:91-113

Delta Calculator

The calculateDelta function compares two objects and returns only the changed fields src/lib/audit/delta-calculator.ts:1-9.

• calculateDeltaWithForcedFields: Allows specific fields (like updated_by) to be included in the audit log even if their value hasn't changed, ensuring context is preserved src/lib/audit/delta-calculator.ts:16-28.

Soft Deletes and Pagination

• Soft Delete: The query-builder automatically appends WHERE deleted_at IS NULL unless the deletedRecords option is set to INCLUDED or ONLY src/db/repository/query-builder.ts:188-198.
• Pagination: findByPage executes two queries: one for the total count and one for the limited result set, returning a PaginatedEntity<T> object src/db/repository/repository.ts:177-209.

Sources: src/db/repository/types.ts:1-21, src/db/repository/query-builder.ts:45-48

---