# Auditable Joins and Display Names

# Auditable Joins and Display Names

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

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

- [src/audit/auditable-joins.ts](src/audit/auditable-joins.ts)
- [src/audit/auditable-types.ts](src/audit/auditable-types.ts)

</details>



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**

```mermaid
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**

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

---