Copy page

DAL Library

Audit and Soft-Delete Subsystems

Audit and Soft-Delete Subsystems

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 @primebrick/dal-pg framework provides a comprehensive, cross-cutting subsystem for tracking entity lifecycles through automated auditing and soft-deletion. These features are integrated directly into the Repository write operations, ensuring that every change to an auditable entity is recorded without requiring manual boilerplate in the business logic.

The system is designed around two primary pillars:

1. Lifecycle Stamping: Automatically managing created_at, updated_at, deleted_at, and the corresponding actor UUIDs (created_by, etc.).
2. Audit Trail Generation: Capturing granular "deltas" (field-level changes) and dispatching them via a non-blocking port to external storage.

Subsystem Architecture

The following diagram illustrates how the Audit and Soft-Delete systems intercept repository operations to perform actor stamping and delta calculation.

Repository Write Lifecycle with Audit

Code
graph TD
    subgraph "Natural Language Space"
        A["Application Code"]
        B["Audit Log Storage"]
        C["User Entity Table"]
    end

    subgraph "Code Entity Space"
        A -->|"calls add/update/delete"| Rep["Repository (src/repository/repository.ts)"]
        Rep -->|"calculates diff"| Delta["calculateDelta (src/audit/audit-port.ts)"]
        Rep -->|"stamps actor"| Meta["EntityMetadata (src/meta/entity-meta.ts)"]
        Rep -.->|"fire-and-forget"| Port["AuditPort.record (src/audit/audit-port.ts)"]
        Port -.-> B
        
        Rep -->|"buildAuditableJoins"| AJ["auditable-joins.ts"]
        AJ -->|"LEFT JOIN"| C
    end

    [src/repository/repository.ts:1-20]()
    [src/audit/audit-port.ts:1-50]()
    [src/audit/auditable-joins.ts:23-47]()

Sources: src/repository/repository.ts:1-20, src/audit/audit-port.ts:1-50, src/audit/auditable-joins.ts:23-47

---

Audit Port and Delta Tracking

The audit system operates on a Port-based architecture, allowing the DAL to remain agnostic of how audit logs are actually stored (e.g., a separate SQL table, a NoSQL stream, or an external microservice).

Key features include:

• AuditAction Enum: Categorizes changes as CREATE, UPDATE, DELETE, or RESTORE src/audit/audit-port.ts:20-25.
• Delta Calculation: The calculateDelta utility compares the "old" state of a record against the "new" state to produce a minimal JSON object containing only changed fields src/audit/audit-port.ts:95-120.
• Non-Blocking Execution: Audit records are dispatched as "fire-and-forget" promises. The DAL uses a LoggerPort to swallow and log any failures in the audit write, ensuring that the primary database transaction is not rolled back if the audit log fails src/audit/audit-port.ts:50-65.

For details on implementation, interfaces, and actor stamping, see Audit Port and Delta Tracking.

---

Auditable Joins and Display Names

While the database stores actor information as UUIDs (e.g., created_by), user interfaces typically require human-readable names. The DAL provides utilities to resolve these UUIDs into display names using efficient SQL joins.

Entity to Display Name Mapping

Code
classDiagram
    class "IAuditableEntity" {
        +UUID created_by
        +UUID updated_by
        +UUID deleted_by
    }
    class "WithAuditableDisplayNames" {
        +String created_by_name
        +String updated_by_name
        +String deleted_by_name
    }
    class "AuditableJoins" {
        +buildAuditableJoins()
        +buildAuditableJoinsSelective()
    }
    
    IAuditableEntity <|-- WithAuditableDisplayNames : "Type Extension"
    AuditableJoins ..> WithAuditableDisplayNames : "Populates"

Sources: src/audit/auditable-types.ts:10-14, src/audit/auditable-joins.ts:23-47

• buildAuditableJoins: A helper that generates LEFT JOIN expressions to link the target entity with a "User" entity (e.g., UserProfileEntity) src/audit/auditable-joins.ts:23-26.
• UUID Guardrails: The joins include logic to handle cases where actor fields might contain non-UUID strings (like "system"), preventing SQL casting errors src/audit/auditable-joins.ts:16-18.
• Utility Types: TypeScript interfaces like WithAuditableDisplayNames<T> ensure type safety when working with the results of these joined queries src/audit/auditable-types.ts:10-14.

For details on configuring joins and using the selective join API, see Auditable Joins and Display Names.

---

Soft-Delete Logic

The DAL implements soft-deletion by default for entities decorated with @DeletableField src/meta/entity-meta.ts:150-160.

• Visibility: Read operations (find, findAll) automatically filter out records where deleted_at is NOT NULL, unless the deletedRecords: 'INCLUDED' or 'ONLY' option is explicitly passed src/repository/repository.ts:150-165.
• Restore: A specialized restore() method is provided to nullify the deletion metadata and record a RESTORE action in the audit trail src/repository/repository.ts:450-465.

Sources: src/meta/entity-meta.ts:150-160, src/repository/repository.ts:150-165, src/repository/repository.ts:450-465

---