Copy page

Developer Guide

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.

Code
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:

Code
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

Code
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

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

Read by ID

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

Find single record with filters

Code
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

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

Soft delete

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

Restore

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

Hard delete

Code
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

Code
// 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

Code
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

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

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

Joins

Code
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):

Code
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

Code
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:

Code
// 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.

Code
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

Code
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).