Skip to main content

How to Write Schema-Safe Backend Code

One deployment of flex-v3-backend serves every team, each in its own PostgreSQL schema. The single most dangerous bug class in this codebase is a query that runs against the wrong schema — it doesn't error, it leaks one team's data to another.

Use this when: writing or reviewing any backend code that touches the database.

How the schema travels (V3)​

  1. Every request carries an X-Team-Schema header.
  2. team-schema.guard.ts validates it against a whitelist (isValidSchema) — this is the SQL-injection barrier, never bypass it — and stores the schema in CLS (continuation-local storage: per-request state that follows the request through async calls, so every layer below can read the schema without it being passed around by hand).
  3. Repositories read the schema from context automatically. You should almost never handle the schema string yourself.

(History, for when you read V1/V2 code: V1/V2 passed teamSchema in every request body — that's why everything was a POST; Pass V3 used the route path; Flex V3 settled on the header.)

The rules​

  • Use query, not queryGlobal. The schema-aware query runs against the request's team schema. queryGlobal is hardcoded to the global schema — it exists for genuinely global data only. If you reach for queryGlobal to "make something work", stop.
  • global is never a tenant. No fan data lives there.
  • @SkipTeamSchema() is an exception, not a convenience. It exempts a route from the guard (health checks, internal endpoints). A fan-facing route with this decorator is a bug.
  • Caches must be schema-keyed. Every cache key that holds team data includes the teamSchema (see the card service for the pattern). A cache key without it serves one team's data to all of them.
  • Cross-team testing: the same user can exist in two schemas with completely separate data (cards, orders, credit). Never assume userId alone identifies a person globally.

Who else writes these tables​

cortex-backend (the admin plane) writes the same tbl_* tables in the same schemas — schema selection there comes from the admin's selected workspace. A schema-handling change in either repo affects Flex fans, Premium fans, and admins at once; check all three before shipping.

Backend layering (controller → service → repository)​

The backend follows a strict three-layer flow, and reviews enforce it:

  • Controller — HTTP only: validate the request (DTOs), call one service, shape the response. No business logic, no DB access.
  • Service — business logic: orchestrates, applies rules, calls repositories. Cross-module access is service-to-service — inject another module's service, never its repository.
  • Repository — data access only. This is where the raw SQL lives (via the schema-aware query); repositories hold no business logic.

Keep the dependency arrow pointing one way — controller → service → repository. A controller touching the DB, or a service reaching into another module's repository, is a smell.

Sandbox​

flex_v3_sandbox is the shared sandbox schema (or spin up your own tm_sandbox_<name>_flex). SS1–SS15 are the Archtics sandbox event codes used inside those schemas — not schemas themselves. See Environments & Sandboxes.