# Key Design Decisions

# Key Design Decisions

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

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

- [.devin/skills/dal-usage/SKILL.md](.devin/skills/dal-usage/SKILL.md)
- [README.md](README.md)
- [docs/ai/dal-usage-guide.md](docs/ai/dal-usage-guide.md)
- [src/index.ts](src/index.ts)

</details>



This page documents the core architectural choices and philosophical foundations of the `@primebrick/dal-pg` library. These decisions prioritize performance, safety, and consistency across the Primebrick v3 ecosystem.

## Architectural Philosophy

The DAL is designed as a type-driven, metadata-heavy layer that bridges TypeScript classes to PostgreSQL tables without the overhead of a full ORM. It favors explicit configuration via decorators over convention-based magic, while enforcing strict anti-throttling measures to protect the database under high-concurrency workloads.

### 1. Snake_Case Everywhere
To eliminate the "transformation tax" common in Node.js applications, the DAL enforces `snake_case` for database columns, TypeScript entity properties, and JSON payloads.

*   **Implementation**: The `@Column` decorator assumes the property name matches the database column name unless explicitly overridden [src/meta/entity-meta.ts:333-338]().
*   **Data Flow**: Data fetched from the wire via `pg` is hydrated directly into entity instances. Since the property names are already in `snake_case`, no key-remapping logic is required during serialization to JSON [README.md:42-42]().

### 2. RETURNING * on All Writes
Every write operation (`add`, `update`, `upsert`, `delete`, `restore`) appends a `RETURNING *` clause to the SQL statement [README.md:43-43]().

*   **Benefit**: This ensures that the application always has the most up-to-date state of the record, including database-generated defaults (e.g., serial IDs), triggers, and audit timestamps, without requiring a secondary `SELECT` query [docs/ai/dal-usage-guide.md:231-235]().
*   **Hydration**: The resulting row is passed through `pgValueToJsValue` to ensure proper type coercion (e.g., converting `timestamptz` strings to JS `Date` objects) [src/meta/column-pg-io.ts:108-142]().

### 3. "Throw If Not Found" by Default
Finders (`findById`, `findByUUID`, `find`) default to throwing a `NotFoundError` if zero rows match the criteria [docs/ai/dal-usage-guide.md:143-144]().

*   **Rationale**: In most business logic, requesting a specific resource that does not exist is an exceptional state. This reduces "if (null)" checks in consumer code.
*   **Override**: Consumers can opt-out by passing `{ throwIfNotFound: false }` in the options, which causes the method to return `null` instead [docs/ai/dal-usage-guide.md:146-147]().

### 4. Soft-Delete Visibility (EXCLUDED by Default)
The DAL natively supports soft-deletion via the `@DeletableField` decorator [src/meta/entity-meta.ts:251-267]().

*   **Default Behavior**: All read operations automatically append a `WHERE deleted_at IS NULL` clause (or equivalent) unless specified otherwise [README.md:45-45]().
*   **Visibility Control**: The `deletedRecords` option allows three modes: `"EXCLUDED"` (default), `"ONLY"`, and `"INCLUDED"` [docs/ai/dal-usage-guide.md:155-155]().

**Sources:**
- [src/meta/entity-meta.ts:333-338]()
- [src/meta/column-pg-io.ts:108-142]()
- [docs/ai/dal-usage-guide.md:143-155]()
- [README.md:42-45]()

---

## High-Volume Write Strategy

For bulk operations, the DAL avoids the overhead of individual `INSERT` or `UPDATE` statements by using a temporary table strategy.

### TEMP TABLE Strategy for Bulk Ops
When performing `updateMany` or `upsertMany`, the DAL follows a specific sequence to ensure atomicity and performance [README.md:46-46]().

#### Data Flow: Bulk Update
1.  **Create**: A temporary table is created with `ON COMMIT DROP` to ensure it is cleaned up automatically after the transaction.
2.  **Load**: Data is inserted into the temporary table using batched `INSERT` statements (respecting the 65,535 parameter limit) [docs/ai/dal-usage-guide.md:315-320]().
3.  **Execute**: A single `UPDATE ... FROM` or `INSERT ... SELECT ON CONFLICT` statement is executed to merge the data from the temporary table into the target table.

### Audit-Aware Upserts
The `upsertMany` operation is designed to preserve the integrity of audit trails during conflicts [README.md:47-47]().
*   It preserves the original `created_at` and `created_by` values.
*   It updates the `updated_at`, `updated_by`, and increments the `version` field [docs/ai/dal-usage-guide.md:330-335]().

#### Entity Space to Code Entity Space: Bulk Writes
The following diagram maps the high-level bulk strategy to the internal functions that execute it.

| System Name | Code Entity | File Path |
| :--- | :--- | :--- |
| **Bulk Gateway** | `Repository.updateMany` | [src/repository/repository.ts]() |
| **Temp Table Logic** | `createTempTable` | [src/repository/repository.ts]() |
| **Batch Manager** | `autoBatchSize` | [src/repository/repository.ts]() |

**Diagram: Bulk Operation Lifecycle**
```mermaid
sequenceDiagram
    participant C as Consumer
    participant R as Repository
    participant DB as PostgreSQL
    
    C->>R: updateMany(Entity, data[])
    R->>DB: CREATE TEMP TABLE "tmp_..." ON COMMIT DROP
    Note over R,DB: Batching data to stay under 65k params
    R->>DB: INSERT INTO "tmp_..." VALUES (...)
    R->>DB: UPDATE "target" SET ... FROM "tmp_..."
    DB-->>R: RETURNING *
    R-->>C: Entity[]
```

**Sources:**
- [src/repository/repository.ts:1-500]()
- [docs/ai/dal-usage-guide.md:315-335]()
- [README.md:46-47]()

---

## Connection and Security

### Port-Based Audit
Audit logging is decoupled from the main database transaction via a "Port" pattern [src/index.ts:112-115]().

*   **Implementation**: The `AuditPort` interface allows consumers to provide their own implementation (e.g., logging to a separate audit database or a message bus) [src/types/types.ts:200-210]().
*   **Non-Blocking**: Audit writes are typically "fire-and-forget" from the perspective of the DAL, ensuring that audit failures do not roll back business transactions unless explicitly configured to do so [docs/ai/dal-usage-guide.md:435-440]().

### Anti-Throttling: statement_timeout
To prevent slow queries from starving the connection pool, the DAL enforces a mandatory `statement_timeout` [README.md:51-51]().

*   **Default**: 30 seconds [docs/ai/dal-usage-guide.md:53-53]().
*   **Mechanism**: The `Dal` gateway sets `SET statement_timeout = 30000` on every new connection via the `onConnect` hook [src/dal/dal.ts:80-95]().
*   **Overrides**: For long-running bulk operations, the `withClient` method allows a per-call override using `SET LOCAL statement_timeout`, which automatically resets when the client returns to the pool [src/dal/dal.ts:180-200]().

### No Schema Migrations
The DAL intentionally excludes schema migration tools [README.md:9-9]().
*   **Decision**: Schema management is considered a separate lifecycle concern (handled by tools like `db-migrate` or `liquibase`).
*   **Boundary**: The DAL provides `dal.getPool()` to allow external migration tools to share the connection configuration, but it does not execute DDL itself outside of `TEMP TABLE` creation [docs/ai/dal-usage-guide.md:120-123]().

**Diagram: Connection Initialization**
```mermaid
graph TD
    subgraph "Dal Gateway [src/dal/dal.ts]"
        G["getDal() Singleton"] --> P["pg.Pool"]
        P --> C["onConnect Hook"]
    end

    subgraph "Session Config"
        C --> S1["SET search_path"]
        C --> S2["SET statement_timeout"]
        C --> S3["SET application_name"]
    end

    subgraph "Type Parsers"
        G --> T1["INT8_OID -> BigInt"]
        G --> T2["NUMERIC_OID -> Number/String"]
    end
```

**Sources:**
- [src/dal/dal.ts:80-200]()
- [src/types/types.ts:200-210]()
- [docs/ai/dal-usage-guide.md:53-123]()
- [README.md:9-51]()

---