# Repository Pattern


The DAL `Repository` class is the primary data access interface. It maps
TypeScript entity classes to PostgreSQL tables and provides type-safe CRUD,
filtering, pagination, bulk operations, soft delete, and audit trail — all
without writing raw SQL.

## Defining an entity

Entities are plain TypeScript classes decorated with metadata. Every
property is a SQL column by default (snake_case). Decorators are only
needed for overrides.

```typescript
import {
  Entity, Column, Key, Unique,
  AuditableField, DeletableField,
} from "@primebrick/dal-pg";

@Entity("organizations")
export class OrganizationEntity {
  @Key() id!: bigint;
  @Unique() uuid!: UUID;
  @Column() idp_code!: string;
  @Column({ nullable: true }) display_name?: string | null;

  @AuditableField(AuditableFieldType.CREATED_AT) created_at!: Date;
  @AuditableField(AuditableFieldType.CREATED_BY) created_by!: string;
  @AuditableField(AuditableFieldType.UPDATED_AT) updated_at!: Date;
  @AuditableField(AuditableFieldType.UPDATED_BY) updated_by!: string;
  @AuditableField(AuditableFieldType.VERSION) version!: number;

  @DeletableField(DeletableFieldType.DELETED_AT) @Column({ nullable: true }) deleted_at?: Date | null;
  @DeletableField(DeletableFieldType.DELETED_BY) @Column({ nullable: true }) deleted_by?: string | null;
}
```

### Decorator reference

| Decorator | Purpose |
|-----------|---------|
| `@Entity(table, schema?)` | Maps class to a table |
| `@Key()` | Marks the primary key column |
| `@Unique()` | Creates a unique index |
| `@Column({ pgType, nullable })` | Overrides column type or nullability |
| `@IsNotColumn()` | Excludes a property from persistence |
| `@AuditableField({ type })` | Marks an audit field (`createdAt`, `createdBy`, `updatedAt`, `updatedBy`, `version`) |
| `@DeletableField({ type })` | Marks a soft-delete field (`deletedAt`, `deletedBy`) |
| `@SynchronizableField({ type })` | Marks a sync tracking field (`lastSyncedAt`) |
| `@CloneField()` | Marks the clone source field (`cloned_from`) |
| `@AuditTrail()` | Marks entity as having an audit trail table |
| `@AuditTrailEntity({ changedByColumn })` | Marks class as being an audit trail entity |

## Creating a Repository

The `Dal` gateway manages the connection pool. Use `getDal()` to access it:

```typescript
import { getDal } from "@primebrick/dal-pg";

const dal = getDal({
  connectionString: process.env.DATABASE_URL!,
  schemas: ["public"],
  max: 10,
  statementTimeoutMs: 30000,
  applicationName: "my-app",
});

// Repository is sealed — consumers use the Dal proxies directly:
// dal.add / dal.find / dal.findAll / dal.update / dal.delete / …
```

## CRUD operations

### Create

```typescript
const org = await dal.add(OrganizationEntity, {
  uuid: randomUUID(),
  idp_code: "ACME",
  display_name: "Acme Inc.",
}, {
  actor: userUuid,       // sets created_by, updated_by
  audit: auditPort,      // writes audit log (optional)
});
```

### Read by UUID

```typescript
const org = await dal.findByUUID(OrganizationEntity, uuid);
if (!org) throw new NotFoundError();
```

### Read by ID

```typescript
const org = await dal.findById(OrganizationEntity, BigInt(123));
```

### Find single record with filters

```typescript
import { Filter, field } from "@primebrick/dal-pg";

const org = await dal.find(OrganizationEntity, null, {
  filters: [
    Filter.fieldValue(field(OrganizationEntity, "idp_code"), "=", "ACME"),
  ],
  throwIfNotFound: true,
});
```

### Update

```typescript
await dal.update(OrganizationEntity,
  { uuid, display_name: "New Name" },
  { actor: userUuid, audit: auditPort },
);
```

### Soft delete

```typescript
await dal.delete(OrganizationEntity,
  { uuid },
  { actor: userUuid, audit: auditPort },
);
```

### Restore

```typescript
await dal.restore(OrganizationEntity,
  { uuid },
  { actor: userUuid, audit: auditPort },
);
```

### Hard delete

```typescript
await dal.hardDelete(OrganizationEntity, { uuid });
```

## Filtering

The `Filter` DSL supports field-value comparisons, field-field comparisons,
raw filters, and grouped AND/OR logic.

### Operators

| Operator | Description |
|----------|-------------|
| `=`, `!=`, `<>` | Equality |
| `<`, `<=`, `>`, `>=` | Comparison |
| `ILIKE`, `LIKE` | Pattern matching |
| `IN`, `NOT IN` | Set membership |
| `BETWEEN` | Range |
| `IS`, `IS NOT` | Null checks |

### Examples

```typescript
// Field-value
Filter.fieldValue(field(Entity, "name"), "=", "Alice")

// Field-field
Filter.fieldField(field(Entity, "created_at"), ">", field(Entity, "updated_at"))

// Raw
Filter.raw("name", "ILIKE", "%alice%")

// Grouped (OR)
Filter.group([
  Filter.fieldValue(field(Entity, "name"), "ILIKE", "%alice%"),
  Filter.fieldValue(field(Entity, "email"), "ILIKE", "%example%"),
], "OR")
```

## Pagination

```typescript
const result = await dal.findByPage(
  OrganizationEntity,
  page,           // 1-based
  pageSize,       // records per page
  null,           // fields (null = all)
  {
    filters: [Filter.fieldValue(field(OrganizationEntity, "deleted_at"), "IS", null)],
    sorting: [Sort.by(field(OrganizationEntity, "created_at"), "DESC")],
    deletedRecords: "EXCLUDED",  // "EXCLUDED" | "ONLY" | "INCLUDED"
  },
);

// result.entities — array of records
// result.total_records — total count (bigint)
```

### Soft delete filtering

| `deletedRecords` | Behavior |
|------------------|----------|
| `"EXCLUDED"` (default) | Only non-deleted records |
| `"ONLY"` | Only soft-deleted records |
| `"INCLUDED"` | All records (deleted and non-deleted) |

## Sorting

```typescript
import { Sort, field } from "@primebrick/dal-pg";

const sorting = [
  Sort.by(field(OrganizationEntity, "created_at"), "DESC"),
  Sort.by(field(OrganizationEntity, "display_name"), "ASC"),
];
```

## Joins

```typescript
import { Join, field, buildAuditableJoins } from "@primebrick/dal-pg";

// Manual join
const joins = [
  Join.on(
    field(UserProfileEntity, "uuid"),
    field(OrganizationEntity, "created_by"),
    "LEFT",
    { castLeftTo: "text", castRightTo: "text", alias: "creator" },
  ),
];

// Helper: join all auditable fields (created_by, updated_by, deleted_by)
const joins = buildAuditableJoins(OrganizationEntity, UserProfileEntity);
```

## Transactions

Use `dal.transaction()` to run multiple operations in a single transaction —
the callback receives a tx-scoped `Repository` (never the raw client):

```typescript
await dal.transaction(async (txRepo) => {
  await txRepo.add(OrganizationEntity, orgData, { actor: userUuid });
  await txRepo.add(MembershipEntity, memberData, { actor: userUuid });

  // Commit happens automatically on success.
  // Any throw rolls back the transaction.
});
```

### Timeout override

```typescript
await dal.transaction(async (txRepo) => {
  // Long-running operation
}, { timeoutMs: 60000 });  // SET LOCAL statement_timeout for this tx
```

## Bulk operations

Bulk operations use a TEMP TABLE strategy for performance:

```typescript
// Bulk insert
await dal.addMany(Entity, rows, {
  actor: userUuid,
  batchSize: 500,
  timeoutMs: 120000,
});

// Bulk upsert
await dal.upsertMany(Entity, rows, {
  conflictColumns: ["uuid"],
  batchSize: 500,
});

// Bulk update
await dal.updateMany(Entity, updates, {
  matchColumn: "uuid",
  batchSize: 500,
});
```

## Audit trail

When `@AuditTrail()` is applied to an entity and an `AuditPort` is provided
in write options, the Repository automatically computes field-level deltas
and writes them to the audit table.

```typescript
import { calculateDelta } from "@primebrick/dal-pg";

// The Repository calls audit.writeAudit() automatically on add/update/delete.
// You only need to provide the AuditPort:
await dal.update(OrganizationEntity,
  { uuid, display_name: "New Name" },
  { actor: userUuid, audit: auditPort },
);

// Manual delta calculation (for custom audit logic):
const delta = calculateDelta(oldRecord, newRecord);
const deltaWithForced = calculateDeltaWithForcedFields(oldRecord, newRecord, ["updated_at"]);
```

### AuditLogEntity

Audit tables share a common shape, mapped by `AuditLogEntity`:

| Column | Type | Description |
|--------|------|-------------|
| `id` | `bigint` | PK |
| `entity_id` | `bigint` | FK to the audited entity |
| `entity_uuid` | `uuid` | UUID of the audited entity |
| `action` | `text` | `INSERT`, `UPDATE`, `SOFT_DELETE`, `HARD_DELETE`, `RESTORE` |
| `changed_at` | `timestamptz` | When the change happened |
| `changed_by` | `text` | Who made the change |
| `version` | `integer` | Entity version at change time |
| `delta` | `jsonb` | Field-level diff: `{ field: { old, new } }` |

## Clone

```typescript
const cloned = await dal.clone(
  OrganizationEntity,
  sourceUuid,
  { actor: userUuid },
);
```

Clone behavior:
- Copies all fields from the source record
- Generates a new UUID (excludes PK and `@Unique` columns)
- Sets `@CloneField` column to the source UUID
- Resets audit fields (created_at/updated_at = now, version = 1)
- Resets soft-delete fields (deleted_at/deleted_by = null)
- No audit log written

## Type parsers

The `Dal` gateway registers custom pg type parsers automatically:

| PG type | JS type | Notes |
|---------|---------|-------|
| `INT8` | `bigint` | Native BigInt (not string) |
| `NUMERIC` | `string` | Lossless always — `Decimal` entity columns hydrate via the column contract |
| `DATE` | `string` | `"YYYY-MM-DD"` — no timezone shift (`ISODate`) |

These are registered once per process (idempotent).