Decision: How a Repository Reaches the Database

:::info Mirrored record — Version 1.0 · Original source: rarit-saas-kernel This page is a faithful reproduction of docs/Database-Guide/db-access-pattern-decision.md from the rarit-saas-kernel repository, mirrored into this collection per Actions ACT-004. This is a snapshot as of the version noted below, not a live sync — if rarit-saas-kernel's copy is amended, this page needs a manual update and a new version number, the same way signed-records/ treats any other accepted document. Policy §5, §6 and Annex A.10 cite this decision directly.

The original also references two sibling documents from the same docs/Database-Guide/ collection (architecture-database-ownership-and-migrations.md and WORK_PLAN.md) that are not mirrored here — those references are left as plain text below, exactly as written in the source. :::

Status: Accepted 2026-08-26. Merged into feat/db-integration. Story: DBI-00 (SASK-178) Applies to: all five engines, and every vertical that consumes them.

---

Why we need this

Every *.repository.ts keeps data in a Map today, except iam/src/repositories/lockout.repository.ts, which runs raw SQL with an in-memory fallback. We are about to rewrite all of them.

Five people will do that work, four engines at the same time. Without one agreed pattern we get four different answers to the same question, and we find that out at merge time. This already happened once: PR #61 had to invent its own database handle because there was nothing to copy.

This document is the thing to copy.

---

The decision

A repository gets its handle from getDb(), exported by platform-core. The handle lives in an execution scope opened by the service, using AsyncLocalStorage from Node.

Three parts, nothing else.

1. One place opens the connection. The vertical app calls initDatabase(connectionString) once at boot. No engine reads DATABASE_URL.

2. The service opens a scope. One use-case goes inside withTenantScope(tenantId, work). That takes one pooled client, runs BEGIN, sets statement_timeout and app.tenant_id for that transaction only, runs the work, then COMMIT or ROLLBACK. Services live in src/v1/services/.

3. The repository asks for the handle. It calls getDb(). It takes no database parameter and imports no framework. Repositories live in src/repositories/.

// service
export async function grantOverride(input: GrantOverrideInput) {
  const saved = await withTenantScope(input.tenantId, async () => {
    const row = await repo.insertOverride(input);
    await logAction({
      /* ... */
    }); // joins the same transaction
    return row;
  });
  invalidateEntitlementCache(input.tenantId, input.entitlementKey); // after commit
  return saved;
}

// repository: same signature, only the body changes
export async function getActiveSubscription(tenantId: string) {
  const [row] = await getDb()
    .select({ id: tenantSubscriptions.id, planId: tenantSubscriptions.planId })
    .from(tenantSubscriptions)
    .where(
      and(
        eq(tenantSubscriptions.tenantId, tenantId),
        eq(tenantSubscriptions.status, "active"),
      ),
    )
    .limit(1);
  return row ?? null;
}

Two properties that matter:

• A scope inside a scope joins. Same tenant, same transaction. A different tenant inside an open scope throws. This is how the Audit engine writes inside the caller's transaction without any transaction object crossing a package boundary.
• getDb() throws when no scope is open. No fallback, no lazy scope. A forgotten scope fails in a test, not in production.

The module factory takes an accessor, not a handle

const entitlements = createEntitlementsModule({ getDb, logger, config });

Two layers, not two options. The factory answers a boot-time question: which database does this module use. The execution context answers a request-time question: which transaction is open right now. getDb() is the mechanism a repository uses to reach the second one. It does not replace factory injection.

We pass getDb rather than db so there is one source for the handle. A pool passed at boot does not know the tenant and is not a transaction, so it cannot carry app.tenant_id and RLS would never engage.

Repository method signatures stay the same either way, so a later move to createRepository(db) would change only the constructor.

See section 6 of architecture-database-ownership-and-migrations.md for the full layering.

---

Options we rejected

fastify.decorate("db", drizzle(pool)). This is the common Fastify pattern and one engine already does it. Three problems: a repository would need the Fastify instance to reach the handle, which CLAUDE.md forbids ("<engine>.service.ts NEVER imports Fastify"); a decorated handle is a pool, not a transaction, so a mutation and its audit row cannot be written together; and it carries no app.tenant_id, so RLS does nothing.

Passing a transaction as an explicit first parameter, getPlan(tx, id). Clearer, and it is what the working instructions show today. Rejected for two reasons. It changes the signature of every repository function and every call site in five packages. And the Audit engine's write must join the caller's transaction, which would mean passing Drizzle's transaction type across package boundaries. Drizzle's type would become published API for all five engines, and a Drizzle upgrade would become a breaking change everywhere at once.

Keeping withTenant() as it is. It opens its own transaction on every call, so a mutation and its audit row land in two transactions. That is the thing we are trying to fix.

---

What we accept

AsyncLocalStorage is implicit state. That is the real cost of this choice and we are taking it knowingly. The mitigation is that it fails loudly: getDb() throws when no scope is open, so a forgotten scope surfaces in the first test rather than returning a wrong value in production.

getDb() also returns a handle to the whole database, not just to one engine's tables. Nothing technical stops engine A reading engine B's tables. That is not new: the raw pool exported from platform-core today does the same and bypasses RLS as well. The boundary stays a review rule for now. A lint rule limiting each repository to its own *.tables.ts is a reasonable follow-up.

---

The notifications plugin

packages/notifications/src/db/drizzle.db.plugin.ts:16 decorates a Drizzle instance as db.

It is removed. No query runs through it today, so nothing depends on it and the change costs nothing. Covered by DBI-12.

There is a second reason. CLAUDE.md says table definitions live at src/<engine>.tables.ts, directly under src/, with no db/ wrapper folder, and it names this engine's db/ folder as the one to flatten. Removing the plugin and flattening the folder is the same work.

Owner sign-off: Ziad Ashraf. Given.

---

Rules that come with this decision

1. Repository function signatures do not change. Same name, same parameters. Only the body changes. If you believe a signature must change, stop and raise it.
2. Keep the in-memory implementation. Export it as createInMemory<Engine>Repository(). Unit tests and example apps run without Postgres.
3. No package reads DATABASE_URL. Only the vertical app does.
4. The service opens the scope. The repository never does.
5. platform-core is frozen once db-scope.ts merges into feat/db-integration. Any change after that is announced and everyone rebases the same day.

---

Ownership

Each engine team owns its own rewrite. Nobody opens a file inside another engine's package.

One story has one owner, and one branch. Two people never share a branch.

Work	Package	Owner
DBI-01 execution scope	platform-core	Muhammad Al-Shurman
DBI-03 fastify and request	platform-core	Ziad Ashraf
DBI-05 RLS convention	platform-core	Fatima
DBI-06 reference implementation	audit	Fatima
DBI-07, DBI-08, DBI-09	entitlements	Muhammad Al-Shurman
DBI-10, DBI-11	iam	Amal Mousa
DBI-12	notifications	Ziad Ashraf
DBI-13	payment	Mahmoud Magdy
DBI-15 migration journal	platform-core	Muhammad Al-Shurman

The owners above are proposed, not confirmed. Each person confirms their own.

Three stories in Phase 1 touch platform-core, and they are owned by three different people. Only index.ts is shared, so DBI-01 merges first and the other two rebase on it. Full detail: docs/Database-Guide/WORK_PLAN.md.

---

Order

platform-core first, because every engine imports from it. Then audit, because a mutation plus its audit row in one transaction is the property this pattern exists to give us, and audit is the smallest place to prove it. Then the remaining four in parallel.

Once audit merges, it is the reference. Open that file before asking on the group.

Full process, ownership and definition of done: docs/Database-Guide/DB_INTEGRATION.md.