Cookbook

This section contains copy/paste-friendly recipes for the most common backend tasks. All recipes follow the architecture contract in _validation/ARCHITECTURE.md.

Add a new REST route

1. Add the DTO in <module>.dto.ts.
2. Add the controller method in <module>.controller.ts.
3. Add service orchestration in <module>.service.ts.
4. Add the repository method in <module>.repository.ts.
5. Add @RequirePermissions or @Public.
6. Cast DTO strings to branded DB types in the service.
7. Write pnpm db:slice:<module> to verify the live query.
8. Run pnpm db:verify before committing.

Example controller method

@Post('clients/:id/notes')
@RequirePermissions('CLIENT.MODIFIER')
async ajouterNote(
  @Param('id') id: string,
  @Body() dto: AjouterNoteDto,
  @CurrentUser() user: AuthUser,
) {
  return this.service.ajouterNote(id as BigIntStr, dto, user.id);
}

Example service write

async ajouterNote(
  idClient: BigIntStr,
  dto: AjouterNoteDto,
  idUtilisateur: BigIntStr,
) {
  return this.scope.audit(idUtilisateur, async (a) => {
    return this.repo.ajouterNote(a, {
      id_client: idClient,
      contenu: dto.contenu,
      date_creation: new Date().toISOString() as TimestampStr,
    });
  });
}

Add a repository list query

async listerNotes(
  a: TxAccess,
  filtres: ListerNotesFiltres = {},
) {
  let q = a.acc.selectFrom('client.note').selectAll();
  q = applyEquality(q, filtres, { id_client: 'id_client' });
  q = applySearch(q, filtres.recherche, ['contenu']);
  q = applySort(q, filtres.sort, filtres.order, ['date_creation'], 'date_creation');
  q = applyPagination(q, filtres);
  return q.orderBy('date_creation', 'desc').execute();
}

Use TxAccess safely

Always pass a as the first argument. Never create TxAccess by hand.

// good
async majClient(a: TxAccess, id: BigIntStr, patch: Partial<client_clientUpdate>) { ... }

// bad
const a = new DbAccess(kysely); // breaks transaction threading

Regenerate generated.ts

cd sim-database
make migrate
cd ../sim-backend
pnpm db:gen
git diff src/db/generated.ts

The diff should match the schema delta exactly. If it does not, commit the generated diff with the migration.

Cast network values to branded DB types

const insert: client_clientInsert = {
  nom: dto.nom,
  prenoms: dto.prenoms ?? null,
  tel_principal: dto.tel_principal,
  date_naissance: (dto.date_naissance ?? null) as DateStr | null,
  date_enregistrement: new Date().toISOString() as TimestampStr,
};

Common patterns:

Wire type	DB type	Cast
id string	bigint	id as BigIntStr
money string	NUMERIC(12,2)	montant as Money
quantity string	NUMERIC(10,2)	quantite as DecimalQuantity
date string	date	date as DateStr
ISO timestamp	timestamp	new Date().toISOString() as TimestampStr

Handle PostgreSQL errors

Let the global filter translate SQLSTATE to HTTP. Do not catch and re-throw generic errors unless the domain requires it.

Common mappings:

• 23505 unique violation → 409 CONFLICT
• 23503 foreign key violation → 422 FK_VIOLATION
• 23502 / 23514 check / not-null → 400 DATA_ERROR
• 22P02 invalid text → 400 DATA_ERROR

Test a repository with a live-DB slice

Create src/<module>/<module>.e2e.ts:

describe = 'mon-module';

async function main() {
  const mod = await Test.createTestingModule({
    imports: [DatabaseModule, MonModuleModule],
  }).compile();

  const app = mod.createNestApplication();
  configureApp(app);
  await app.init();

  const repo = app.get(MonRepository);
  const scope = app.get(AccessScope);

  try {
    await scope.audit(ACTEUR_ID, async (a) => {
      const created = await repo.creer(a, { ... });
      ok('created', created != null);

      const found = await repo.findById(a, created.id);
      ok('found', found != null);

      throw new Error('rollback-marker');
    });
  } catch (e: any) {
    if (e.message !== 'rollback-marker') throw e;
  }

  const outside = await repo.findById(scope.root, id);
  ok('rolled back', outside == null);

  await app.close();
}

main();

Run it with pnpm db:slice:mon-module.

Anti-patterns to avoid

• Do not add a repository base class.
• Do not cache DB results in Redis.
• Do not write to views; DbAccess enforces TableKeys.
• Do not use sql.raw for user input.
• Do not trust DTO enum unions at runtime; use hand-written @IsIn arrays.
• Do not construct TxAccess outside AccessScope.
• Do not add auto-migrations; schema evolution belongs to sim-database.