Copy page

DAL Library

Auditable Joins and Display Names

Auditable Joins and Display Names

Relevant source files

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

• src/audit/auditable-joins.ts
• src/audit/auditable-types.ts

The Auditable Joins subsystem provides a standardized mechanism for resolving actor UUIDs (stored in created_by, updated_by, and deleted_by columns) into human-readable display names during read operations. By utilizing the DAL's Join DSL, these helpers automate the LEFT JOIN logic required to link an auditable entity with a user-profile entity without hardcoding specific table names, maintaining the DAL's role as a leaf dependency.

Implementation Overview

The system is centered around two primary utility functions that generate Join expressions compatible with the Repository.find and Repository.findAll methods.

buildAuditableJoins()

This function generates a standard set of three LEFT JOIN expressions for the creator, updater, and deleter src/audit/auditable-joins.ts:23-27. It requires both the target auditable entity class and the user entity class (which must contain uuid and display_name columns) src/audit/auditable-joins.ts:24-25.

buildAuditableJoinsSelective()

For performance-sensitive queries where only specific audit information is required (e.g., a "Created By" column in a list view), this function allows developers to toggle specific joins src/audit/auditable-joins.ts:58-65. It defaults to including all three joins if no options are provided src/audit/auditable-joins.ts:68.

Data Flow and Join Logic

When these helpers are invoked, they produce Join.on expressions that target the user entity table using specific aliases: creator, updater, and deleter src/audit/auditable-joins.ts:32-44.

Natural Language to Code Entity Space: Join Resolution

Natural Language Concept	Code Entity / Symbol	Implementation Detail
Target Entity	entity: EntityClass	The auditable table (e.g., OrderEntity) src/audit/auditable-joins.ts:24
User Table	userEntity: EntityClass	The source of names (e.g., UserProfileEntity) src/audit/auditable-joins.ts:25
Creator Alias	"creator"	Alias used for the created_by join src/audit/auditable-joins.ts:32
Updater Alias	"updater"	Alias used for the updated_by join src/audit/auditable-joins.ts:38
Deleter Alias	"deleter"	Alias used for the deleted_by join src/audit/auditable-joins.ts:44

System Flow: Auditable Join Generation

Code
graph TD
    subgraph "Input Space"
        A["EntityClass (Auditable)"]
        B["EntityClass (User/Profile)"]
        C["SelectiveOptions (Optional)"]
    end

    subgraph "Logic: buildAuditableJoinsSelective"
        D{"includeCreator?"}
        E{"includeUpdater?"}
        F{"includeDeleter?"}
    end

    subgraph "Output: Join Expressions"
        G["Join.on(user.uuid, entity.created_by, 'LEFT', {alias: 'creator'})"]
        H["Join.on(user.uuid, entity.updated_by, 'LEFT', {alias: 'updater'})"]
        I["Join.on(user.uuid, entity.deleted_by, 'LEFT', {alias: 'deleter'})"]
    end

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

    D -- "true" --> G
    E -- "true" --> H
    F -- "true" --> I

Sources:

• src/audit/auditable-joins.ts:23-47
• src/audit/auditable-joins.ts:58-104
• src/query/dsl.ts:10-10

Type Safety and Display Names

To handle the result sets of queries using these joins, the DAL provides TypeScript utility types. These types extend the base entity row type with optional display name strings. These fields are typically populated by the database projection (e.g., creator.display_name AS created_by_name).

Type Name	Purpose	Added Fields
WithAuditableDisplayNames<T>	Full audit visibility	created_by_name, updated_by_name, deleted_by_name src/audit/auditable-types.ts:10-14
WithCreatorDisplayName<T>	Creation tracking only	created_by_name src/audit/auditable-types.ts:17-19
WithUpdaterDisplayName<T>	Update tracking only	updated_by_name src/audit/auditable-types.ts:22-24

Sources:

• src/audit/auditable-types.ts:1-25

The UUID Regex Guardrail Pattern

A critical feature of these joins is the handling of non-UUID values. In the Primebrick ecosystem, audit fields may occasionally contain system identifiers (e.g., "system") instead of a standard UUID.

To prevent PostgreSQL from throwing errors when attempting to join a UUID column (user_profile.uuid) against a text column containing non-UUID strings, the buildAuditableJoins helpers utilize explicit casting:

• castRightTo: "text"
• castLeftTo: "text"

This ensures that the comparison is performed as a string match, safely returning NULL for the join if the ID does not exist in the user table, rather than crashing the query src/audit/auditable-joins.ts:32,38,44.

Entity Mapping Diagram

Code
classDiagram
    class IAuditableEntity {
        +UUID created_by
        +UUID updated_by
        +UUID deleted_by
    }
    class UserEntity {
        +UUID uuid
        +String display_name
    }
    class ResolvedRow {
        +String created_by_name
        +String updated_by_name
        +String deleted_by_name
    }

    IAuditableEntity "1" -- "0..1" UserEntity : creator (LEFT JOIN)
    IAuditableEntity "1" -- "0..1" UserEntity : updater (LEFT JOIN)
    IAuditableEntity "1" -- "0..1" UserEntity : deleter (LEFT JOIN)
    ResolvedRow ..|> IAuditableEntity : extends

Sources:

• src/audit/auditable-joins.ts:12-17
• src/audit/auditable-joins.ts:32-44
• src/audit/auditable-types.ts:10-14

---