Architecture

The SIM backend is the NestJS REST API for GLOBAL SIM GROUP. It exposes one versioned HTTP surface (/api/v1) for all business modules while keeping the database access layer deliberately small, typed, and generated from PostgreSQL.

Core idea

A typed PostgreSQL access boundary generated from PostgreSQL itself.

The source chain is:

Flyway migrations → PostgreSQL → pg_catalog → dbgen → src/db/generated.ts

src/db/generated.ts is committed because a migration PR ships its generated diff, so reviewers can see exactly what TypeScript believes changed.

Stack

Layer	Choice	Notes
Runtime	Node.js 22 LTS, TypeScript 5.7	package.json
Framework	NestJS 11 on Express 5	package.json
API Versioning	URI /api/v1/<module>	src/app.setup.ts
Database access	Kysely 0.29 + pg	typed query builder
Database	PostgreSQL 18	migrations live in sim-database
Auth	JWT access + refresh rotation, RBAC permissions	src/auth
Redis	token revocation, distributed throttling, BullMQ job queues	src/redis
Object storage	MinIO / S3 (@aws-sdk/client-s3)	src/s3, src/uploads
Logs	pino (nestjs-pino)	src/main.ts, src/app.module.ts
Metrics	prom-client	/metrics
API docs	Swagger (/docs)	src/app.setup.ts
PDF / print	Puppeteer 25.8.2	src/pdf, Dockerfile
Task queue	BullMQ 6	src/jobs
Validation	class-validator / class-transformer	global ValidationPipe

Layered stack

Nest Controller
       │
       ▼
Domain Service
       │
       ▼
Repository
       │
       ▼
DbAccess  (frozen CRUD surface)
       │
       ▼
Kysely  →  pg  →  PostgreSQL

There is no EntityManager, UnitOfWork, IdentityMap or ORM-style entity graph.

Anti-feature list

The architecture contract explicitly avoids:

• relations / lazy-loading entity graphs
• identity map / change tracking
• generic query DSL beyond basic CRUD
• automatic joins
• caching at the DB result layer
• automatic migrations
• database vendor abstraction
• “repository magic” / auto-generated repositories

What it keeps:

• DB-derived types and typed views
• safe CRUD with compile-time protection
• explicit transactions
• PostgreSQL SQL and trigger-owned columns
• schema-aware code generation
• drift detection (pnpm db:check)

Module layout

Every module lives in src/<module>/ with a canonical shape:

src/<module>/
  <module>.module.ts      # Nest module wiring
  <module>.controller.ts  # routing + Swagger + auth decorators
  <module>.service.ts     # orchestration, DTO → brand casts
  <module>.repository.ts  # domain SQL
  <module>.dto.ts         # transport classes + class-validator
  <module>.e2e.ts         # live-DB slice (rollback transaction)

There is no repository base class. Each repository is explicit and domain-shaped.

Transaction threading

Reads use scope.root. Writes use scope.audit(idUtilisateur, (a, tx) => …).

src/db/access-scope.ts

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

  audit<T>(
    idUtilisateur: bigint | string | null,
    work: (a: TxAccess, tx: Kysely<Database>) => Promise<T>,
  ): Promise<T> {
    return withAudit(this.db, idUtilisateur, (tx) => work(txAccess(tx), tx));
  }
}

withAudit sets the PostgreSQL GUC sim.id_utilisateur inside a transaction:

src/db/audit.ts

export async function withAudit<T>(
  db: Kysely<Database>,
  idUtilisateur: bigint | string | null,
  work: (tx: Kysely<Database>) => Promise<T>,
): Promise<T> {
  return db.transaction().execute(async (tx) => {
    if (idUtilisateur != null) {
      await sql`SELECT set_config('sim.id_utilisateur', ${String(idUtilisateur)}, true)`.execute(tx);
    }
    return work(tx);
  });
}

This makes the actor id available to audit.journal_audit triggers without leaking across connections.

CRUD governance

DbAccess provides a frozen surface:

• findById / findOne / findMany / exists / count
• insert / insertMany
• update / delete

findMany accepts only an equality map + orderBy + limit + offset. Anything beyond becomes a one-off sql tag or a domain repository method.

insertInto, updateTable, and deleteFrom are typed against TableKeys, not keyof Database. Views are read-only at compile time.

Generated types

src/db/generated.ts exposes:

Type	Meaning	Example
BigIntStr	bigint as exact string	id as BigIntStr
Money	NUMERIC(12,2) FCFA amounts	montant as Money
DecimalQuantity	NUMERIC(10,2) quantities	qty as DecimalQuantity
DateStr	naive date as string	d('2026-10-31')
TimestampStr	naive timestamp as string	new Date().toISOString() as TimestampStr
TimeStr	naive time as string	—
Json	JSON / JSONB	unknown

Database maps schema.table names to table and view interfaces. ViewKeys lists read-only views; TableKeys excludes them.

Escape hatches

• Values are always bound (sql${value}`).
• Identifiers use sql.ref.
• sql.raw is generator-only (dbgen/dbgen.mjs).

Trigger-owned columns

dbgen statically analyzes trigger bodies and marks affected columns as ins: never / upd: never. If a trigger is too opaque, it requires an explicit declaration in dbgen/dbgen.metadata.json.

db:check invariant

The committed generated.ts must match the migrated PostgreSQL catalog:

fresh PostgreSQL → Flyway migrate → pnpm db:gen → git diff --exit-code → tsc --noEmit → tests

pnpm db:verify is the full enforcement of this invariant. Always run it after a schema change.