# Customers Data Access Layer

# Customers Data Access Layer

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

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

- [src/domain/entities/iclonable_entity.ts](src/domain/entities/iclonable_entity.ts)
- [src/modules/customers/audit-transformer.ts](src/modules/customers/audit-transformer.ts)
- [src/modules/customers/customer_entity.ts](src/modules/customers/customer_entity.ts)
- [src/modules/customers/customers_dal.ts](src/modules/customers/customers_dal.ts)
- [src/modules/customers/customers_repo.ts](src/modules/customers/customers_repo.ts)
- [src/modules/customers/dto.ts](src/modules/customers/dto.ts)

</details>



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

---