<!-- source: openspec/current/db/spec.md -->

---
title: DB — Spec
status: stable
owner: pavel
audience: [pavel, ai, engineer]
tldr: Engineering contract for the db package — package location, browser/admin separation, RLS contract, dependencies, and known gaps.
---
# DB — spec

> Part of openspec/current/db/ — split from db.md on 2026-05-12

## Where db lives

- Source: `packages/db/`
- Package name: `@rapoport-studio/db`
- Version: `0.0.0`, `private: true`
- Entry surface: subpath `.` only — `./src/index.ts` (no main, no per-module subpath exports beyond root)
- Build: none. Consumers compile `.ts` directly via Next.js / vitest / app build.
- Scripts: `lint` (eslint src), `typecheck` (tsc --noEmit), `test` (vitest run).
- Test runtime: vitest with `environment: "node"`.
- Tracked source files: 4 (`src/index.ts`, `src/client.ts`, `src/admin.ts`, `src/types.ts`) plus 1 test file (`src/__tests__/smoke.test.ts`).

---

## Browser/admin client separation contract

`client.ts` exports a **module-load singleton** with the public anon key. `admin.ts` exports a **factory** (`createAdminClient()`) that takes no arguments and creates a fresh client per call with the service role key. Browser code may freely import from `@rapoport-studio/db`; admin code is server-only. As of change `wire-spec-enforcement-gates` (RAP-1167, applied 2026-05-22), the app-edge modules that instantiate and re-export the service-role client (e.g. `apps/studio/src/lib/supabase-admin.ts`) carry the `server-only` npm import marker — `server-only` is now the enforced marker, so a client-bundle import of such a module fails the build rather than merely failing code review. The `@rapoport-studio/db` package itself stays marker-free; the marker is enforced at the app edge where the client is instantiated (see `narrative.md § Open questions`).

## RLS contract

Browser clients rely on Supabase RLS for every read and write. The admin client (`createAdminClient()`) bypasses RLS — callers are responsible for correct authorization logic. Importing `createAdminClient` in a client-side bundle is a code-review failure.

See `narrative.md § RLS philosophy` for the three RLS policy patterns used across the schema. Full per-table RLS definitions live in `entities.md`.

---

## Validation

`packages/db` does not validate environment variables — it consumes already-typed accessors from `@rapoport-studio/config`. Behavior on missing or invalid config:

- **`db` (browser singleton)**: at module load, `createClient<Database>(supabase.url, supabase.anonKey)` is called with whatever the config accessor returns. Per `config.md`, both come through as raw `process.env[...]` reads via the lazy `env` Proxy with no runtime validation; if either is `undefined`, `@supabase/supabase-js` throws a descriptive error from `createClient` (typically "Invalid URL"). The package adds no pre-validation.
- **`createAdminClient()`**: explicitly checks `!supabase.serviceRoleKey` and throws `Error("SUPABASE_SERVICE_ROLE_KEY is required for admin client")` (`src/admin.ts:6-8`) before reaching `createClient`. This is the only runtime guard in the package.

### Test coverage

`packages/db/src/__tests__/smoke.test.ts` — 4 tests, vitest, `environment: "node"`:

| Test (file:line) | Asserts |
|---|---|
| `smoke.test.ts:21` | `db` singleton is exported and defined |
| `smoke.test.ts:26` | `createClient` is called with `config.supabase.url` and `config.supabase.anonKey` |
| `smoke.test.ts:37` | `createAdminClient()` throws when `serviceRoleKey` is unset |
| `smoke.test.ts:44` | `createAdminClient()` returns a client when `serviceRoleKey` is set |

Both `@rapoport-studio/config` and `@supabase/supabase-js` are mocked at module scope; `vi.resetModules()` runs `beforeEach`; the second admin test uses `vi.doMock()` to swap in a non-empty service role key for that specific case.

No tests of `types.ts` shape, no integration tests against a real Supabase instance, no tests of behavior when `supabase.url` is undefined at module load.

---

## Dependencies

### Runtime

| Package | Version | Used by |
|---|---|---|
| `@supabase/supabase-js` | `^2` | `client.ts`, `admin.ts` (`createClient`) |

### Internal

| Package | Range | Used by |
|---|---|---|
| `@rapoport-studio/config` | `workspace:*` | `client.ts`, `admin.ts` (the `supabase` accessor) |

### Dev

| Package | Version | Used by |
|---|---|---|
| `@types/node` | `^20` | type checking |
| `typescript` | `^5` | `tsc --noEmit` |
| `vitest` | `^4` | `__tests__/smoke.test.ts` |

No peer dependencies declared.

---

## Boundaries

Enforced by `eslint.config.mjs:87-97`. `db` may import only `@rapoport-studio/config`.

- DB **MAY** import from `@rapoport-studio/config`
- DB **MAY NOT** import from `@rapoport-studio/{ui,i18n,auth,forge,canvas,muse}` or any app
- DB **MAY NOT** contain business logic, route handlers, or app-specific query helpers
- Verified consumers: `packages/auth` (type-only). Apps reach db transitively via auth — zero direct app imports.

---

## Known gaps

- **No local `supabase/config.toml`.** Migrations live at `supabase/migrations/` at repo root, but the Supabase CLI is not initialized locally — there is no `config.toml`. Typegen and `apply_migration` are routed through the Supabase MCP server today. If a future workflow needs `supabase db diff` / `supabase db pull` against a local Postgres, the CLI initialization is a separate change.

---

## Atlas-ratified table renames (applied 2026-05-23 from change `ratify-entities-atlas`)

The `canvas_*` table family was renamed under RAP-1170 (the rearchitect-spec-document-model epic) and ratified by Atlas D-ATLAS-5 (canvas vocabulary retired). The new table set is the canonical persistence schema for the studio's domain entities per Atlas D-ATLAS-6 (Spec sphere = project / idea / spec / capability / change / inquiry).

### Active tables (per `packages/db/src/types.ts`)

| Was (canvas_*) | Now | Notes |
|---|---|---|
| `canvas_sessions` | `ideas` **+** `specs` | The canvas table was split per the D-ATLAS-5 split between "work session" (Tier-1 `idea`) and "deliverable document set" (Tier-1 `spec`). `ideas.spec_id` is the FK from idea → spec. |
| `canvas_messages` | `messages` | FK `idea_id`. |
| `canvas_files` | `idea_attachments` | Note: `idea_attachments` no longer stores `path` + `content` inline — those live on the separate `ai_files` table (`idea_attachments.ai_file_id` FK). |
| `canvas_invitees` | `spec_invitees` | Permission model was redesigned at the spec level, not per-idea, hence the column rename `canvas_id → spec_id`. |
| `canvas_excluded_members` | `spec_excluded_members` | Same redesign — exclusion lists are per-spec. |
| `canvas_project_instructions` | `idea_instructions` | FK `idea_id`. |
| `canvas_tool_uses` | `idea_tool_uses` | FK `idea_id`. |

### Retired tables — no successor

None. Every `canvas_*` table renamed; none were dropped without replacement. The retired NAMES are the canvas-prefixed ones; the rows live on under the new identifiers.

### Atlas guard

`pnpm atlas:verify` D-ATLAS-5 enforces the rename at the source-tree level: a `grep-forbidden` guard fails CI on any `.from("canvas_*"` call across `apps/` and `packages/`. Allowlist: `openspec/archive/`, `supabase/migrations/` (historical), `apps/studio/src/generated/` (openspec bundle text), and `packages/db/src/__tests__/rls-permission-model.test.ts` (suite-skipped pending rewrite per RAP-1200).

Cross-ref: `openspec/archive/2026-05-23-ratify-entities-atlas/`. Capability: `openspec/current/entities.md` § Persisted entities. Per-table catalog: `openspec/entities/persisted.md`.
