Data Patterns

Database responsibilities

PostgreSQL enforces data integrity; application services own business processes. Tables must use foreign keys, uniqueness constraints, nullability, and check constraints so invalid data cannot be inserted by bypassing the application.

Business tables should normally have:

• an auto-generated integer id used for relationships and auditing. This is generally the primary key;

• a stable, human-readable text code used as the public business key;

• the standard creation, update, and deletion audit fields.

Relationship, reference, and other implementation tables may use a different key shape when the data model requires it. For example the country table uses the ISO country code as the primary key.

Every business table should have an audit trigger that invokes the shared audit_trigger_fn function.

Package schema and seed SQL belongs in the package's install/ directory and is listed in installation order by voyzu.package.ts. SQL file names do not control execution order.

Data transfer objects (DTOs)

A Data Transfer Object (DTO) is the definitive definition of data as it is described within the application. DTOs define data exchanged through the application and API boundaries.

DTOs describe the shape of a data object as it applies to a given data operation. There is no attempt, for example, to define a single "stock object"; rather, there are multiple definitions depending on the data operation.

// packages/@acme/warehousing/modules/types/stock.ts
import type { AuditMetadataDto } from "@voyzu/types/modules/core";

// a code value must be supplied
export interface StockItemCreateRequestDto {
  code: string;
  name: string;
  supplierCode?: string;
  reorderLevel?: number;
}

// code cannot be supplied as code is invalid in a patch request
export interface StockItemPatchRequestDto {
  name?: string;
  supplierCode?: string;
  reorderLevel?: number;
}

// audit properties are returned in a GET response
// but not present in data create DTOs
export interface StockItemGetResponseDto {
  id: number;
  code: string;
  name: string;
  supplierCode: string | null;
  reorderLevel: number | null;
  audit: AuditMetadataDto;
}

The create DTO includes code because the stable business code is assigned when the entity is created. The patch DTO deliberately omits code because a business code cannot be changed.

Create and patch DTOs must not accept audit details. Audit timestamps, actors, users, and mutation identifiers are generated by the system rather than supplied by an API caller. The response DTO includes the resulting system-generated audit information through AuditMetadataDto.

TypeScript interfaces do not validate runtime values. A handler must validate untrusted input before calling a service, and a service must enforce business rules independently of the transport.

Database row types are internal persistence shapes. Map them to DTOs rather than returning rows directly.

Data Repository

A Data Repository in Voyzu is code that controls read and write data access. Repositories own SQL and row mapping. All database access must be via a Data Repository. The general pattern is that a module service file calls a data repository file, and all interactions go through the service module.

Within the Data Repository use getDb from @voyzu/capability/db for normal application queries:

// packages/@acme/warehousing/modules/stock/server/db/stock.repo.ts
import { getDb } from "@voyzu/capability/db";

export async function getStock(code: string) {
  const result = await getDb().query(
    "select id, code, name from stock where code = $1",
    [code],
  );
  return result.rows[0] ?? null;
}

The platform owns the shared connection pool. Application request code must not call pool.end(). A standalone process that deliberately creates and owns its process lifetime may close the pool when it exits.

Transactions

Use withTransaction when a business operation changes multiple rows or must share one audit mutation:

// packages/@acme/warehousing/modules/stock/server/lib/stock.service.ts
import { withTransaction } from "@voyzu/capability/db";

await withTransaction(async (client) => {
  await stockRepo.update(client, code, patch);
  await stockMovementRepo.create(client, movement);
});

Pass the transaction client through the service and repository layers. Do not open nested independent transactions for work that must commit atomically.

Naming and SQL safety

Use snake_case for PostgreSQL identifiers and keep terminology consistent between tables, DTOs, services, and APIs. Always bind values as query parameters. Never concatenate untrusted values into SQL.

Dynamic identifiers, such as a permitted sort column, must be selected from an explicit allow-list before being included in a statement.

See also

• Validation layers

• Auditing patterns

• API patterns