# Repository and Query DSL

# Repository and Query DSL

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

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

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

</details>



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**
```mermaid
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**
```mermaid
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]()

---