Copy page

Backend

Customers Data Access Layer

Customers Data Access Layer

Relevant source files

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

• src/domain/entities/iclonable_entity.ts
• src/modules/customers/audit-transformer.ts
• src/modules/customers/customer_entity.ts
• src/modules/customers/customers_dal.ts
• src/modules/customers/customers_repo.ts
• src/modules/customers/dto.ts

The CustomersDal class serves as the specialized Data Access Layer for the CustomerEntity. It encapsulates complex PostgreSQL query construction, advanced filtering logic, and lifecycle operations such as soft-deletion, duplication, and audit trail retrieval. It leverages the generic Repository for base CRUD while providing custom implementations for streaming and search.

Search and Filter Pipeline

The DAL implements a multi-layered search strategy. It handles simple string searches across multiple fields and advanced structured filtering using a custom translation engine.

ILIKE Search Pattern

For free-text search, the DAL uses buildIlikeNeedleFromSearch to transform raw user input into PostgreSQL-compatible ILIKE patterns src/modules/customers/customers_dal.ts:19-54. This function escapes special characters like % and _ while converting * to % and ? to _ src/modules/customers/customers_dal.ts:34-39.

Advanced Filter Translation

The translateFilterConditions function maps API-level FilterCondition objects to the internal Repository DSL src/modules/customers/customers_dal.ts:63-140.

• Operator Support: It validates operators against a whitelist including =, !=, ILIKE, IN, BETWEEN, and IS src/modules/customers/customers_dal.ts:66-81.
• Field Validation: Only fields defined in CUSTOMER_FILTERABLE_KEYS are allowed src/modules/customers/customers_dal.ts:83-83.
• Complex Types: It handles array values for IN operators and object shapes {start, end} for BETWEEN operations src/modules/customers/customers_dal.ts:102-120.

Search Translation Logic

Code
graph TD
    A["Raw Search String"] --> B["buildIlikeNeedleFromSearch()"]
    B --> C{"Contains Wildcards?"}
    C -- "Yes" --> D["Map * to % and ? to _"]
    C -- "No" --> E["Escape %, _, #"]
    D --> F["Wrap in %needle%"]
    E --> F
    F --> G["Repository FilterExpr"]

Sources: src/modules/customers/customers_dal.ts:19-54, src/modules/customers/customers_dal.ts:63-140

Entity Lifecycle Operations

The DAL manages the specific lifecycle requirements of the CustomerEntity, including soft-deletion and record cloning.

Soft-Delete and Restore

The delete and restore methods do not perform physical deletions. Instead, they interact with fields decorated with @DeletableField src/modules/customers/customer_entity.ts:91-95.

• delete: Updates deleted_at and deleted_by src/modules/customers/customers_dal.ts:474-486.
• restore: Sets deleted_at and deleted_by to null src/modules/customers/customers_dal.ts:488-500.

Duplication (Cloning)

The duplicate method creates new records based on existing ones src/modules/customers/customers_dal.ts:502-555.

1. It fetches the source records by UUID.
2. It generates new uuid and code values.
3. It sets the cloned_from field to the source record's UUID src/modules/customers/customers_dal.ts:536-536.
4. It resets audit fields (created_at, version) before performing a bulk insert src/modules/customers/customers_dal.ts:526-535.

Cloning Data Flow

Code
sequenceDiagram
    participant API as "CustomersRouter"
    participant DAL as "CustomersDal"
    participant REPO as "Repository"
    participant DB as "PostgreSQL"

    API->>DAL: duplicate(uuids, actor)
    DAL->>REPO: findByUuids(CustomerEntity, uuids)
    REPO->>DB: SELECT * FROM customers
    DB-->>REPO: Source Rows
    DAL->>DAL: Generate New UUIDs & Codes
    DAL->>DAL: Set cloned_from = source.uuid
    DAL->>REPO: insertMany(newEntities)
    REPO->>DB: INSERT INTO customers
    DB-->>DAL: Success
    DAL-->>API: Cloned Entities

Sources: src/modules/customers/customers_dal.ts:502-555, src/modules/customers/customer_entity.ts:97-98

Streaming and Exports

To handle large datasets without memory exhaustion, CustomersDal provides a streaming interface for exports.

• streamAll: This method accepts CustomerListQuery parameters and returns a Readable stream of database rows src/modules/customers/customers_dal.ts:348-406.
• Cursor Management: It utilizes the Repository.stream method, which uses pg-query-stream to fetch rows sequentially src/modules/customers/customers_dal.ts:404-404.
• Data Transformation: Rows are transformed from the database shape to the CustomerDetailDto shape within the stream pipeline to ensure consistent formatting for XLSX/CSV templates src/modules/customers/customers_dal.ts:400-403.

Sources: src/modules/customers/customers_dal.ts:348-406, src/modules/customers/dto.ts:217-223

Audit Log Integration

The DAL provides a specialized query for the audit trail of a specific customer.

• getAuditTrail: Retrieves the history of changes for a record src/modules/customers/customers_dal.ts:557-613.
• Join Logic: It uses getAuditUserJoinSql and getAuditSelectWithDisplayName to join the audit table with the UserProfileEntity, resolving the changed_by ID to a human-readable display name src/modules/customers/customers_dal.ts:581-584.
• Transformation: The raw audit rows (containing JSON deltas) are passed through transformAuditEntry to generate localized descriptions of what changed (e.g., "Il campo Email è stato modificato da... a...") src/modules/customers/audit-transformer.ts:78-116.

Audit Query Entity Mapping

Code
classDiagram
    class CustomerEntity {
        +uuid: string
        +version: number
    }
    class CustomerAuditTable {
        +entity_uuid: string
        +action: string
        +delta: jsonb
        +changed_by: string
    }
    class UserProfileEntity {
        +idp_id: string
        +display_name: string
    }
    CustomerEntity "1" -- "n" CustomerAuditTable : "History"
    CustomerAuditTable "n" -- "1" UserProfileEntity : "Resolves changed_by"

Sources: src/modules/customers/customers_dal.ts:557-613, src/modules/customers/audit-transformer.ts:17-66

Seeding Logic

The seedIfEmpty method ensures the database has demo data for development environments src/modules/customers/customers_dal.ts:216-318.

• Check: It first calls repo.count(CustomerEntity) to avoid duplicate seeding src/modules/customers/customers_dal.ts:217-218.
• Generation: It uses hardcoded arrays of Italian names and companies to generate 137 unique customer records src/modules/customers/customers_dal.ts:220-305.
• Batch Insert: The generated entities are persisted using repo.insertMany src/modules/customers/customers_dal.ts:317-317.

Sources: src/modules/customers/customers_dal.ts:216-318, src/modules/customers/customers_repo.ts:112-205

---