Copy page

Backend

Auditable Joins and Display Name Resolution

Auditable Joins and Display Name Resolution

Relevant source files

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

• src/db/repository/README.md
• src/db/repository/audit-join-helper.ts
• src/db/repository/auditable-joins.ts
• src/db/repository/auditable-types.ts

The Primebrick backend utilizes a standardized system for resolving user UUIDs stored in audit fields (created_by, updated_by, deleted_by, and changed_by) into human-readable display names. This is achieved through a combination of DSL join helpers, SQL projection utilities, and a robust regex-based guardrail pattern to handle non-UUID values like "system".

Overview and Data Flow

Entities that implement IAuditableEntity store the UUID of the performing user in specific metadata columns src/db/repository/README.md:5-5. To display these names in the UI, the system joins the entity table with the public.user_profiles table src/db/repository/README.md:5-5.

Join Logic and UUID Guardrail

A critical feature of the join logic is the UUID Regex Guardrail. Because audit fields can sometimes contain non-UUID strings (e.g., "system" for automated migrations or background tasks), a direct join on a UUID column would cause PostgreSQL to throw a type conversion error.

The system uses the regex ^[0-9a-fA-F-]{36}$ to validate the field content before attempting the join src/db/repository/README.md:66-82.

Code Entity to SQL Mapping

The following diagram illustrates how TypeScript functions translate into specific SQL Join and Select structures.

Natural Language to Code Entity Space: Join Resolution

Code
graph TD
    subgraph "TypeScript Space"
        A["buildAuditableJoins()"]
        B["getAuditUserJoinSql()"]
        C["WithAuditableDisplayNames<T>"]
    end

    subgraph "SQL / Database Space"
        D["LEFT JOIN public.user_profiles"]
        E["REGEX: ~ '^[0-9a-fA-F-]{36}$'"]
        F["AS created_by_name"]
        G["AS updated_by_name"]
    end

    A --> D
    A --> E
    B --> D
    B --> E
    C -.-> F
    C -.-> G

Sources: src/db/repository/auditable-joins.ts:15-36, src/db/repository/audit-join-helper.ts:13-19, src/db/repository/auditable-types.ts:23-27

---

Auditable Join Helpers

The repository layer provides helpers to automatically construct the necessary Join expressions for the Query DSL.

buildAuditableJoins

This function generates an array of three LEFT JOIN expressions for created_by, updated_by, and deleted_by src/db/repository/auditable-joins.ts:15-36.

• Alias Mapping:
  • created_by joins as alias creator src/db/repository/auditable-joins.ts:21-21.
  • updated_by joins as alias updater src/db/repository/auditable-joins.ts:27-27.
  • deleted_by joins as alias deleter src/db/repository/auditable-joins.ts:33-33.

buildAuditableJoinsSelective

Allows including only specific joins to reduce query overhead src/db/repository/auditable-joins.ts:46-53. This is useful for high-performance list views where only the creator's name is required.

includeAuditableJoins Option

The Repository.findByPage method supports a boolean flag includeAuditableJoins in its options. When set to true, the query builder automatically applies these joins without requiring manual DSL configuration src/db/repository/README.md:102-116.

Sources: src/db/repository/auditable-joins.ts:1-91, src/db/repository/README.md:100-116

---

Audit Trail Queries

Audit tables (e.g., customers_audit) differ from main entity tables because they use a single changed_by field instead of three separate audit columns src/db/repository/README.md:143-145.

Helper Functions

Function	Purpose	SQL Output Snippet
getAuditUserJoinSql	Joins user_profiles to the audit alias.	LEFT JOIN public.user_profiles creator ON audit.changed_by ~ ...
getAuditSelectWithDisplayName	Selects standard audit fields + display name.	creator.display_name as changed_by_display_name
getAuditSelectWithIdpCode	Selects audit fields + IDP code (no name).	creator.idp_code as changed_by_idp_code

Sources: src/db/repository/audit-join-helper.ts:1-58

---

Type Safety with WithAuditableDisplayNames

To ensure TypeScript recognizes the dynamically joined display name fields, the WithAuditableDisplayNames<T> utility type is used to extend base entity row types.

Code
export type WithAuditableDisplayNames<T> = T & {
  created_by_name?: string;
  updated_by_name?: string;
  deleted_by_name?: string;
};

src/db/repository/auditable-types.ts:23-27

The query builder automatically maps the join aliases (creator, updater, deleter) to these specific field names (created_by_name, etc.) during result set processing src/db/repository/README.md:128-134.

Sources: src/db/repository/auditable-types.ts:1-62, src/db/repository/README.md:49-62

---

Implementation Summary Diagram

The following diagram demonstrates the lifecycle of a query from the Repository call to the SQL execution involving auditable joins.

Query Execution Flow

Code
sequenceDiagram
    participant App as Service/Module
    participant Repo as Repository
    participant QB as QueryBuilder
    participant DB as PostgreSQL

    App->>Repo: findByPage(Entity, { includeAuditableJoins: true })
    Repo->>QB: buildSelect(options)
    QB->>QB: buildAuditableJoins(Entity)
    Note over QB: Applies Regex Guardrail Pattern
    QB->>DB: SELECT ..., creator.display_name AS created_by_name ...
    DB-->>QB: Raw Rows
    QB-->>Repo: Typed Entities (WithAuditableDisplayNames)
    Repo-->>App: Result Page

Sources: src/db/repository/README.md:100-116, src/db/repository/auditable-joins.ts:15-36, src/db/repository/auditable-types.ts:23-27

---