Copy page

Backend

AuditService and Delta Calculator

AuditService and Delta Calculator

Relevant source files

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

• src/db/build-entity-snapshot.ts
• src/db/database-patch-to-sql.ts
• src/db/repository/repository.ts
• src/db/schema-types.ts
• src/lib/audit/audit-service.ts
• src/lib/audit/audit-types.ts
• src/lib/audit/delta-calculator.ts

The Audit System provides a comprehensive mechanism for tracking state changes across the application's domain entities. It utilizes a dedicated AuditService to persist change logs and a delta-calculator utility to determine the precise differences between entity states.

AuditService

The AuditService is responsible for persisting audit records to the database. It is typically instantiated with a PostgreSQL connection pool and invoked by the Repository or specialized Data Access Layers (DALs).

Implementation Details

The service targets tables following a specific naming convention: {original_table_name}_audit src/lib/audit/audit-service.ts:19-19. It executes a standard INSERT statement into the public schema src/lib/audit/audit-service.ts:22-25.

Key Function: writeAudit<T> src/lib/audit/audit-service.ts:9-18

• Parameters:
  • entityClass: The class definition of the entity being audited.
  • entityId: The numeric primary key of the record.
  • entityUuid: The unique identifier of the record.
  • action: A member of the AuditAction enum.
  • delta: A JSON object representing the changes.
  • version: The version number of the record at the time of the change.

Audit Actions

The system tracks lifecycle events via the AuditAction enum:

Action	Description
INSERT	Creation of a new record src/lib/audit/audit-types.ts:2-2
UPDATE	Modification of an existing record src/lib/audit/audit-types.ts:3-3
SOFT_DELETE	Marking a record as deleted without removing the row src/lib/audit/audit-types.ts:4-4
HARD_DELETE	Physical removal of a row from the database src/lib/audit/audit-types.ts:5-5
RESTORE	Reversing a soft-delete operation src/lib/audit/audit-types.ts:6-6

Sources: src/lib/audit/audit-service.ts:1-38, src/lib/audit/audit-types.ts:1-8

---

Delta Calculator

The Delta Calculator is a utility that compares two versions of an entity and produces a structured "delta" object. This object captures exactly what changed, providing both the previous and current values for every modified field.

Delta Format

The calculator produces a Record<string, { old: unknown; new: unknown }> src/lib/audit/delta-calculator.ts:1-2.

• Key: The property name of the entity.
• Value: An object containing the old value and the new value.

Comparison Logic

The calculator uses JSON.stringify to perform a deep comparison of field values src/lib/audit/delta-calculator.ts:4-4. If the stringified versions differ, the field is included in the delta.

Forced Fields

In some scenarios, certain fields must be present in the audit log even if they haven't changed (e.g., updated_by or version fields for traceability). The calculateDeltaWithForcedFields function ensures these fields are included by explicitly checking a forceFields array src/lib/audit/delta-calculator.ts:16-28.

Sources: src/lib/audit/delta-calculator.ts:1-29

---

Data Flow: Repository to Audit

The Repository class orchestrates the interaction between data persistence and auditing. When an entity is marked as auditable (via @AuditTrail or isAuditable meta), the repository automatically calculates the delta and triggers the AuditService.

Logic Flow Diagram

The following diagram illustrates how a write operation in Repository triggers the audit lifecycle.

"Repository Audit Flow"

Code
graph TD
    subgraph "Repository.ts"
        A["insertMany() / update()"] --> B{"isAuditable?"}
        B -- "Yes" --> C["calculateDelta()"]
        C --> D["auditService.writeAudit()"]
    end

    subgraph "AuditService.ts"
        D --> E["getTableName() + '_audit'"]
        E --> F["INSERT INTO ..._audit"]
    end

    subgraph "PostgreSQL"
        F --> G[("Table_audit")]
    end

Sources: src/db/repository/repository.ts:91-113, src/lib/audit/audit-service.ts:19-35

---

Database Schema and Naming Conventions

Audit tables are generated automatically by the migration system if an entity is marked as auditable src/db/database-patch-to-sql.ts:103-104.

Table Structure

Each audit table contains the following standard columns:

Column	Type	Description
id	bigint	Primary key (Identity) src/db/database-patch-to-sql.ts:108-108
entity_id	bigint	Foreign ID to the source table src/db/database-patch-to-sql.ts:109-109
entity_uuid	uuid	UUID of the source record src/db/database-patch-to-sql.ts:110-110
action	text	The AuditAction performed src/db/database-patch-to-sql.ts:111-111
changed_at	timestamptz	Timestamp of the change src/db/database-patch-to-sql.ts:112-112
changed_by	text	User ID or 'system' src/db/database-patch-to-sql.ts:113-113
version	integer	Record version for optimistic locking src/db/database-patch-to-sql.ts:114-114
delta	jsonb	The change payload src/db/database-patch-to-sql.ts:115-115

Partitioning and Performance

To handle high volumes of audit data, the system utilizes pg_partman for native monthly partitioning based on the changed_at column src/db/database-patch-to-sql.ts:121-121. Additionally, indexes are created on entity_uuid and action to facilitate fast lookups in the UI src/db/database-patch-to-sql.ts:125-126.

Mapping Code to Database

This diagram maps the SchemaTableMeta configuration to the generated SQL artifacts.

"Entity Metadata to SQL Mapping"

Code
classDiagram
    class SchemaTableMeta {
        +String name
        +Boolean isAuditable
    }
    class AuditTableSQL {
        +String table_name_audit
        +JSONB delta
        +TIMESTAMPTZ changed_at
    }
    SchemaTableMeta --> AuditTableSQL : "buildSqlPatchFromMetaDiff()"
    note for AuditTableSQL "Generated in src/db/database-patch-to-sql.ts"

Sources: src/db/database-patch-to-sql.ts:103-128, src/db/schema-types.ts:34-42, src/db/build-entity-snapshot.ts:28-35

---