# Bulk Operations

# Bulk Operations

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

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

- [src/repository/repository.ts](src/repository/repository.ts)
- [test/benchmark/bulk-benchmark.test.ts](test/benchmark/bulk-benchmark.test.ts)
- [test/repository-bulk.test.ts](test/repository-bulk.test.ts)

</details>



The Primebrick DAL provides specialized methods for high-volume data modifications. These methods are designed to bypass the overhead of single-row processing while maintaining data integrity, audit compliance, and PostgreSQL parameter limits.

## Overview of Bulk Methods

Bulk operations in the `Repository` class are optimized for different use cases, ranging from simple batched inserts to complex temporary table-based updates. All bulk methods accept `BulkOptions` to control execution behavior.

### BulkOptions
| Option | Description |
| :--- | :--- |
| `batchSize` | Manual override for the number of rows per batch. |
| `timeoutMs` | Statement timeout for the operation. |
| `actor` | (Required for auditable entities) The user/system performing the operation. |

**Sources:** [src/types/types.ts:34-37](), [src/repository/repository.ts:148-150]()

---

## 1. addMany (Batched INSERT)

The `addMany` method performs high-speed inserts by grouping records into multi-row `INSERT` statements. 

### Implementation Details
- **Auto-Batching:** To avoid the PostgreSQL limit of 65,535 parameters per query, `addMany` calculates an optimal batch size using `autoBatchSize(columnCount)`. This ensures that `rowCount * columnCount` never exceeds the limit [src/repository/repository.ts:142-146](), [src/repository/repository.ts:476-485]().
- **Audit Stamping:** Every row is automatically stamped with `created_by`, `updated_by`, and `version = 1` if the entity is auditable [src/repository/repository.ts:494-510]().
- **Result Hydration:** It uses `RETURNING *` to return the fully hydrated entities, including database-generated IDs and defaults [src/repository/repository.ts:517-522]().

**Sources:** [src/repository/repository.ts:466-531]()

---

## 2. upsertMany (Atomic Merge)

`upsertMany` handles "insert or update" logic at scale using the PostgreSQL `INSERT ... ON CONFLICT` syntax.

### Key Features
- **Conflict Handling:** Requires a `conflictTarget` (usually the primary key or a unique column) to determine when to update instead of insert [src/repository/repository.ts:544-550]().
- **Audit Awareness:** If a conflict occurs, the system increments the `version` and updates the `updated_by` field, while preserving the original `created_by` value [src/repository/repository.ts:577-585]().
- **Data Flow:** Like `addMany`, it uses the `autoBatchSize` logic to prevent parameter overflow [src/repository/repository.ts:557-560]().

**Sources:** [src/repository/repository.ts:533-617]()

---

## 3. updateMany (Temp Table Strategy)

Updating thousands of rows with different values for each row is inefficient using standard `UPDATE` statements. The DAL employs a **Temporary Table Strategy** to maximize performance.

### The Update Workflow
1. **Create Temp Table:** A temporary table is created with the same structure as the target table but with `ON COMMIT DROP` to ensure cleanup [src/repository/repository.ts:684-692]().
2. **Bulk Insert:** All update data is inserted into this temporary table using the `addMany` logic [src/repository/repository.ts:696-699]().
3. **Single Join Update:** A single `UPDATE ... FROM` statement is executed, joining the target table with the temporary table on the `matchBy` column [src/repository/repository.ts:701-724]().

### Diagram: UpdateMany Strategy
```mermaid
sequenceDiagram
    participant R as Repository
    participant DB as PostgreSQL
    R->>DB: CREATE TEMP TABLE "tmp_..." ON COMMIT DROP
    Note over R,DB: Batched Insert into Temp Table
    loop for each batch
        R->>DB: INSERT INTO "tmp_..." VALUES (...)
    end
    Note over R,DB: Single Join Update
    R->>DB: UPDATE target_table SET ... FROM "tmp_..." WHERE target.id = "tmp_...".id
    DB-->>R: RETURNING *
```
**Sources:** [src/repository/repository.ts:654-740]()

---

## 4. deleteMany (Soft-Delete)

`deleteMany` performs a bulk soft-delete by updating the `deleted_at` and `deleted_by` columns for a set of records.

### Implementation
- **Array-based WHERE:** Instead of multiple queries, it uses the `ANY($1::uuid[])` operator to update all matching rows in a single statement [src/repository/repository.ts:641-645]().
- **Audit Stamping:** It records the `actor` in the `deleted_by` column and sets the current timestamp in `deleted_at` [src/repository/repository.ts:634-639]().

**Sources:** [src/repository/repository.ts:619-652]()

---

## Technical Architecture

### Data Flow: Natural Language to Code Entities

The following diagram maps the high-level bulk operations to the internal functions and PostgreSQL mechanics used in the `Repository` class.

```mermaid
graph TD
    subgraph "Natural Language Space"
        B1["'Insert 10,000 rows'"]
        B2["'Update multiple rows with different data'"]
        B3["'Delete a list of IDs'"]
    end

    subgraph "Code Entity Space (Repository)"
        R_AM["Repository.addMany()"]
        R_UM["Repository.updateMany()"]
        R_DM["Repository.deleteMany()"]
        ABS["autoBatchSize()"]
        TT["TEMP TABLE Strategy"]
        ANY["ANY() Array Operator"]
    end

    subgraph "Database Space (PostgreSQL)"
        PG_INS["INSERT INTO ... VALUES (...), (...)"]
        PG_UP_FROM["UPDATE ... FROM temp_table"]
        PG_UP_ANY["UPDATE ... WHERE id = ANY(...)"]
    end

    B1 --> R_AM
    B2 --> R_UM
    B3 --> R_DM

    R_AM --> ABS
    ABS --> PG_INS

    R_UM --> TT
    TT --> PG_UP_FROM

    R_DM --> ANY
    ANY --> PG_UP_ANY
```
**Sources:** [src/repository/repository.ts:142-146](), [src/repository/repository.ts:466-470](), [src/repository/repository.ts:619-623](), [src/repository/repository.ts:654-658]()

### Performance Benchmarks
The bulk system is tested against various scales (100 to 1,000,000 rows). The `updateMany` strategy is specifically optimized to maintain high throughput even as row counts increase.

| Operation | 1,000 Rows | 10,000 Rows | 1,000,000 Rows (Opt-in) |
| :--- | :--- | :--- | :--- |
| `addMany` | ~50ms | ~300ms | ~25s |
| `updateMany` | ~80ms | ~500ms | ~45s |

**Sources:** [test/benchmark/bulk-benchmark.test.ts:97-101](), [test/benchmark/bulk-benchmark.test.ts:119-121]()

---