Database access

The backend does not use an ORM. It uses Kysely with a generated Database type that mirrors the PostgreSQL catalog. The database boundary lives in src/db/ and is enforced by DbAccess, AccessScope, and TxAccess.

Pool and type parsing

src/db/pool.ts creates a single pg.Pool and registers type parsers so date, time, and timestamp without time zone stay as strings:

import pg from 'pg';

pg.types.setTypeParser(1082, (v) => v); // date
pg.types.setTypeParser(1083, (v) => v); // time
pg.types.setTypeParser(1114, (v) => v); // timestamp without time zone

These string values are branded as DateStr, TimeStr, and TimestampStr in src/db/generated.ts.

DatabaseModule

DatabaseModule is @Global() and exports four primitives:

@Global()
@Module({
  providers: [
    {
      provide: Kysely,
      useFactory: () =>
        new Kysely<Database>({
          dialect: new PostgresDialect({ pool }),
        }),
    },
    DbAccess,
    {
      provide: DB_FUNCTIONS,
      useFactory: (db: Kysely<Database>) => makeFunctions(db),
      inject: [Kysely],
    },
    AccessScope,
  ],
  exports: [Kysely, DbAccess, DB_FUNCTIONS, AccessScope],
})
export class DatabaseModule {}

DbAccess (frozen CRUD)

DbAccess restricts mutations to real tables only:

@Injectable()
export class DbAccess {
  selectFrom<K extends keyof Database>(table: K) { /* tables + views */ }
  insertInto<K extends TableKeys>(table: K) { /* tables only */ }
  updateTable<K extends TableKeys>(table: K) { /* tables only */ }
  deleteFrom<K extends TableKeys>(table: K) { /* tables only */ }
}

TableKeys = Exclude<keyof Database, ViewKeys>, so a view cannot be accidentally inserted or deleted through the type system.

TxAccess

TxAccess binds a DbAccess and typed SQL functions to one query executor (root or transaction). This prevents writes inside a transaction from accidentally using a different pool connection.

export interface TxAccess {
  acc: DbAccess;
  fn: DbFunctions;
}

AccessScope

AccessScope is the injectable API for repositories:

@Injectable()
export class AccessScope {
  readonly root: TxAccess;

  audit<T>(
    idUtilisateur: bigint | string | null,
    work: (a: TxAccess, tx: Kysely<Database>) => Promise<T>,
  ): Promise<T> { /* ... */ }
}

• Use this.scope.root for reads.
• Use this.scope.audit(user.id, async (a) => …) for writes.

Branded type casts

Network DTOs stay string-valued. The service layer casts to branded DB types at the repository boundary:

date_naissance: (dto.date_naissance ?? null) as DateStr | null;
montant_loyer: dto.montant_loyer as Money;
id_logement: dto.id_logement as BigIntStr;
date_enregistrement: new Date().toISOString() as TimestampStr;

This keeps the transport layer free of brands while the repository receives compile-time-safe insert/update types.

List query helpers

src/db/list-query.ts provides explicit helpers that repositories call directly:

Helper	Purpose
applyPagination	limit (max 200) and offset
applySearch	ilike over a whitelist of columns
applySort	whitelisted sort + order with a default column
applyDateRange	du/au on a date/timestamp column
applyEquality	exact equality filters for whitelisted fields

A typical list repository method looks like:

async listerClients(a: TxAccess, filtres: ListerClientsFiltres) {
  let q = a.acc.selectFrom('client.client').selectAll();
  q = applyEquality(q, filtres, {
    type_client: 'type_client',
    code: 'code',
    nom: 'nom',
    // ...
  });
  q = applySearch(q, filtres.recherche, SEARCH_COLUMNS);
  q = applySort(q, filtres.sort, filtres.order, SORT_COLUMNS, 'nom');
  q = applyPagination(q, filtres);
  return q.execute();
}

DbFunctions

Callable PostgreSQL functions are typed and exposed through TxAccess.fn:

await a.fn.residence.encaisserLoyer({
  p_id_echeance: id,
  p_montant: montant,
  p_id_moyen: idMoyen,
  p_id_utilisateur: user.id,
});

Wrapped functions include:

• audit.masquer_donnees
• market.reesolde_stock
• residence.encaisser_loyer, residence.encaisser_loyer_lot, residence.generer_echeances, residence.reviser_loyer
• rh.recalculer_paie

PATCH guard

assertNonEmptyPatch turns an empty UPDATE ... SET into a clear 400 BadRequestException:

export function assertNonEmptyPatch(patch: Record<string, unknown>): void {
  if (Object.keys(patch).length === 0) {
    throw new BadRequestException('Aucun champ à mettre à jour (corps vide ou content-type incorrect).');
  }
}

Rules of the boundary

1. Always read through this.scope.root.
2. Always write through this.scope.audit(user.id, async (a) => …).
3. Pass a: TxAccess as the first argument to repository write methods.
4. Cast DTO strings to branded types in the service, not the controller.
5. Use sql.ref for identifiers; bind values with sql${value}`.
6. Do not write to ViewKeys; DbAccess prevents it at compile time.
7. Regenerate generated.ts after every migration and run pnpm db:verify.