# Auditable Joins and Display Name Resolution

# Auditable Joins and Display Name Resolution

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

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

- [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)

</details>



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

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

---