Database

Source: src/db/ — schema.ts, index.ts, rls.sql, roles.sql.

The database layer is the keystone of multi-tenancy. Postgres role separation + row-level security (RLS) means a query that forgets its WHERE tenant_id = ? clause still cannot leak rows across tenants.

Tables

Roles

Two roles, principle of least privilege:

The split is enforced in two places:

• src/db/index.ts — runtime pool always connects as mcp_app.
• drizzle.config.ts — migrations connect as mcp_admin.

RLS policies

src/db/rls.sql enables RLS on every tenant-scoped table (metric_cache, platform_credentials, sync_log, tenant_deks) and creates a single tenant_isolation policy per table that compares tenant_id against current_setting('app.current_tenant_id').

Setting the tenant context is the responsibility of the request lifecycle. After auth resolves a tenantId, the next DB statement on that connection must run:

SELECT set_config('app.current_tenant_id', '<uuid>', true);

The true makes the setting transaction-local — it auto-clears at end of transaction.

In Phase 2 this pattern is used in three places:

• cache.service.ts — readOrFetch opens a tx and sets context for every cache read/write.
• credentials.service.ts — ensureDek and destroyDek each open their own short tx.
• adapters/google/auth.ts — exchangeAuthCode and ensureValidToken open short txs around platform_credentials reads and writes; the network call to Google happens between transactions, never inside one.

The stub Phase 1 MCP tools don’t query tenant-scoped tables and don’t need context; audit_log writes don’t need it (no RLS on that table).

Migrations

# Generate a new migration after editing schema.ts:
npm run db:generate
 
# Apply pending migrations (uses mcp_admin):
npm run db:migrate

Drizzle Kit does NOT auto-generate down-migrations. The official rollback path is to restore from a pg_dump taken immediately before the migration runs — see docs/phase-1-foundation.md §B6 for the deploy-time procedure.

Why audit_log is not under RLS

A multi-tenant SaaS often wants to give a customer their own audit log. We deliberately do NOT do that here: security review (and SOC 2 evidence collection) needs unrestricted cross-tenant visibility. The trade-off is that any future “show me my audit log” customer-facing feature must come from a separate, RLS-scoped derived table — not from audit_log directly.

Why api_keys is not under RLS

api_keys is the table the auth middleware reads to establish the tenant context — it cannot itself be tenant-scoped without creating a chicken-and-egg dependency. An RLS policy of tenant_id = current_setting('app.current_tenant_id')::uuid evaluates to NULL when no tenant is set, hides every row, and the lookup-by-key_hash returns nothing. The middleware then 401s every valid request. (The first 2026-05-07 Ubuntu smoke-test hit exactly this — see docs/phase-1-foundation.md §14 #4.)

Access control on api_keys is structural, not policy-based:

• mcp_app can SELECT/INSERT/UPDATE the table, scoped only by the application’s WHERE clauses.
• The key_hash is HMAC-SHA256 with a server-only secret — an attacker with read-only DB access still cannot mint a working key from a stored hash.
• A future “list my keys” customer endpoint must explicitly filter by tenant_id in the application; there is no DB-level safety net.

SSL posture

App and Postgres share a host through Phase 1–5: traffic never leaves loopback. src/db/index.ts sets ssl: false. The snake-oil cert that Postgres ships with cannot satisfy rejectUnauthorized: true against Node’s bundled Mozilla CA bundle (the one earlier draft of this code tried — see docs/phase-1-foundation.md §14 #5), and SSL on 127.0.0.1 adds no security regardless. Revisit only if Postgres ever moves off-host.

TIMESTAMP type parser override

src/db/index.ts registers a pg type parser for OID 1114 (timestamp without time zone) that interprets stored values as UTC:

pg.types.setTypeParser(pg.types.builtins.TIMESTAMP, (val) =>
  val === null ? null : new Date(val + 'Z'),
);

Why: node-postgres’ default parser treats timestamp without time zone as a JS Date in the HOST’s local timezone, which silently corrupts UNIX-millis math on any machine not running TZ=UTC. Every timestamp we write is the Date.toISOString() UTC literal, so reading it back as UTC is the correct contract.

The override applies to every pg-driver connection in the process — Drizzle’s pool, the test pools, anything that imports pg after this module loads. timestamptz (OID 1184) is unaffected; it already round-trips correctly.

Pin TZ=UTC on the systemd unit in production so the server clock and the stored timestamps align even without the override. See setup-ubuntu.md Step 10.

Running locally

See the project README.md for the dev bootstrap. The short version:

docker compose up -d postgres
bash scripts/dev-secrets.sh
npm run db:migrate
psql ... -v app_password="$APP_PW" -f src/db/roles.sql
psql ... -f src/db/rls.sql

Tests

tests/integration/rls.test.ts creates two tenants, inserts a row owned by tenant B, switches the connection’s tenant context to A, and asserts that A sees zero rows. If this test ever fails, do not ship.

tests/integration/audit.test.ts verifies mcp_app cannot UPDATE or DELETE audit_log.

tests/integration/credentials.test.ts covers tenant_deks RLS isolation in addition to the envelope-encryption contract.