Skip to content

database.md

Latest commit

 

History

History
203 lines (138 loc) · 11.8 KB

database.md

File metadata and controls

203 lines (138 loc) · 11.8 KB
Raw

Database and the three-role pool

Why this exists

A ForgeKit app is multi-tenant: many organizations share the same tables, and every row carries an org_id. The hard rule is that one organization must never see another's rows.

You could try to enforce that in application code, by adding WHERE org_id = :currentOrg to every query. The trouble is that a single forgotten WHERE leaks another tenant's data. ForgeKit instead pushes the guarantee down into Postgres, so the database refuses to hand back the wrong rows even when the application code has a bug.

The mechanism is three separate Postgres logins, called roles, each with a different level of power.

The three roles

Think of them by who connects as each one and what it is allowed to see.

forgekit_app — normal user traffic. When a signed-in user loads their dashboard, the request connects to Postgres as forgekit_app. This role is deliberately weak: it is not a superuser and does not have BYPASSRLS, so Row-Level Security (RLS) policies apply to it. Those policies scope every query to the current organization automatically: a handler that forgets its WHERE org_id = ... still gets back only the current org's rows, because Postgres itself filters them. The reference table example_records carries this policy, and the isolation tests prove it; every real tenant table adopts the same pattern. This is the role that turns tenant isolation from a coding convention into a database guarantee.

forgekit_operator — admin and background jobs. Some work legitimately spans every tenant: an admin console that lists all organizations, or a nightly job that cleans up expired sessions across the whole system. That work connects as forgekit_operator, which has BYPASSRLS, so the per-org policies do not apply and it can read across tenants on purpose. It is used only for these cross-tenant surfaces, never for ordinary user requests.

forgekit_owner — schema changes. This role owns the tables and runs migrations (creating tables, adding columns). The migration tooling uses it; it is not used while serving traffic.

How it fits together

The role picks what a connection may see; the org scope picks which org's rows.

flowchart TD
  code["Feature code decides the operation"]
  code --> choice{"Tenant-scoped or cross-tenant?"}

  choice -->|"tenant request"| appPool["App pool<br/>role forgekit_app<br/>NOSUPERUSER, RLS applies"]
  choice -->|"admin or background job"| opPool["Operator pool<br/>role forgekit_operator<br/>BYPASSRLS"]

  orgCtx["Server-derived org id<br/>membership, API key, or webhook<br/>never user input"]
  orgCtx --> scope

  appPool --> scope["withOrgScope binds app.current_org_id<br/>inside a transaction"]
  scope --> policy["tenant_isolation policy checks<br/>org_id equals app.current_org_id"]
  policy --> onlyOrg["Reads and writes rows for that one org"]

  opPool --> allOrgs["Reads and writes rows for every org<br/>policy bypassed"]
Loading

Connection URLs

Each role logs into Postgres with its own username, carried in a connection URL:

postgres://forgekit_app:secret@localhost:5432/forgekit
          └──────────┘ the username is the role

In development you set a single DATABASE_URL (as the owner), and the code works out the app and operator logins by swapping the username. That is what "resolving" the URLs means:

resolveDatabaseUrls({
  DATABASE_URL: "postgres://forgekit_owner:secret@localhost:5432/forgekit",
});
// {
//   app:      "postgres://forgekit_app:secret@localhost:5432/forgekit",
//   operator: "postgres://forgekit_operator:secret@localhost:5432/forgekit",
// }

deriveRoleUrl is the single-swap building block behind that:

deriveRoleUrl("postgres://forgekit_owner:secret@localhost:5432/forgekit", "forgekit_app");
// "postgres://forgekit_app:secret@localhost:5432/forgekit"

In production you do not rely on the swap: you set APP_DATABASE_URL and OPERATOR_DATABASE_URL explicitly, each with its own password, and those always win over derivation. If no DATABASE_URL is set at all, both resolve to null, so a fresh clone boots with no database on in-memory fallbacks.

Client handle

createDb(url) returns three things: the Drizzle query interface (db), the underlying pg connection pool (a set of reusable connections to Postgres), and a close function that drains the pool.

const { db, close } = createDb(appUrl);
// ... run queries through db ...
await close();

Building the handle does not connect to Postgres. The pool opens a real connection only when the first query runs, not when the handle is created (this is what "lazily" means). That is why constructing it is safe even with no database running, and why application code should build a handle only once a real database URL has been resolved.

Request scoping and RLS context

RLS policies need to know which organization is active for the current request. ForgeKit carries that value in the Postgres setting app.current_org_id.

withOrgScope(appDb, orgId, fn) opens a transaction on the app-role pool, binds the org id inside that transaction, and runs fn with the transaction as the active scoped database handle:

await withOrgScope(appDb, orgId, async () => {
  const db = getScopedDb(appDb);
  return db.select().from(projects);
});

The binding is transaction-local. Postgres reverts it on commit or rollback, so the value never leaks across pooled connections.

The code uses set_config('app.current_org_id', orgId, true) instead of SET LOCAL. SET LOCAL cannot take a bind parameter, which would force string-concatenating the org id into SQL. set_config takes the value as a parameter. The org id is always server-derived, such as from a membership row, API key, or signed webhook, never user input.

withScopedDb and getScopedDb propagate the active handle through Node's AsyncLocalStorage, so service code resolves the transaction at query time. The fallback passed to getScopedDb must be the app-role pool. If a query runs with no scope active, it should fail closed under RLS instead of reading across tenants.

Both outcomes of that resolution — the bound transaction or the app-pool fallback — pass through RLS on the app role, which is what makes a forgotten scope fail closed rather than leak:

flowchart TD
  q["Service code at query time<br/>db = getScopedDb(appDb)"]
  q --> store{"AsyncLocalStorage:<br/>is a scope active?"}
  store -->|"inside withOrgScope"| tx["Transaction handle<br/>app.current_org_id is bound"]
  store -->|"no scope active"| fb["App-pool fallback<br/>app.current_org_id is unset"]
  tx --> onlyOrg["RLS returns that org's rows"]
  fb --> zero["RLS returns zero rows<br/>fails closed, never another org"]
Loading

An unset app.current_org_id makes current_setting('app.current_org_id', true) return null, so the tenant_isolation comparison matches no row. The fallback is deliberately the app pool and never the operator pool: routing an unscoped query to the operator role would bypass RLS and read across tenants, the exact failure this design prevents.

Tenant isolation policy

Every tenant table follows the same pattern: it carries org_id text not null and a tenant_isolation policy. The USING clause governs reads, updates, and deletes. The WITH CHECK clause governs inserts and updates. Both require the row's org_id to equal current_setting('app.current_org_id', true).

FORCE ROW LEVEL SECURITY keeps the policy active even for the table owner. With no org bound, current_setting('app.current_org_id', true) returns null, so the comparison matches no row. An unscoped app query returns zero rows rather than another org's data.

example_records is the reference table that carries this pattern, and the isolation tests exercise the scoped app role, refused cross-org writes, operator access, and the unscoped fail-closed case.

CREATE TABLE example_records (
  id text PRIMARY KEY,
  org_id text NOT NULL,
  body text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX example_records_org_id_idx ON example_records (org_id);

ALTER TABLE example_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE example_records FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON example_records
  USING (org_id = current_setting('app.current_org_id', true))
  WITH CHECK (org_id = current_setting('app.current_org_id', true));

GRANT SELECT, INSERT, UPDATE, DELETE ON example_records TO forgekit_app, forgekit_operator;

IDs

uuidv7() generates the text primary key for every row:

uuidv7(); // e.g. "018f9a4c-7b2e-7c3d-8e4f-1a2b3c4d5e6f"

A UUIDv7 puts a timestamp at the front of the id, so newly created ids sort by creation time and land near the end of a B-tree index instead of scattering across it. That keeps index inserts fast while still giving globally unique ids the application can generate on its own, with no database round trip.

Table definitions and the migrations that create them live with the features that own them.

Local database and migrations

Start the development database with Docker Compose:

docker compose up -d db

The local service listens on host port 5433 because port 5432 may already be used by another Postgres. It uses trust auth so the derived per-role development logins can connect without passwords. This is for development only and must never be used in production.

Use this URL for the Compose database:

DATABASE_URL=postgres://postgres@localhost:5433/forgekit

The @forgekit/db package owns the migration lifecycle scripts:

pnpm --filter @forgekit/db run db:generate
pnpm --filter @forgekit/db run db:migrate
pnpm --filter @forgekit/db run db:reset
pnpm --filter @forgekit/db run db:test-reset

db:generate asks drizzle-kit to create migration files from schema changes. db:migrate applies pending migrations using DATABASE_URL. db:reset reads DATABASE_URL, connects to the postgres maintenance database, drops and recreates the target database, then runs migrations and the seed step. db:test-reset uses the same reset script for whichever test database is named by DATABASE_URL.

The first migration creates forgekit_owner, forgekit_app, and forgekit_operator with CREATE ROLE. It is idempotent because Postgres roles are cluster-global and survive a database drop.

The role integration tests are gated. They are skipped unless DATABASE_URL is set, so a fresh clone can run pnpm test without a local database.

References