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

---
title: DB — Conventions
status: stable
owner: pavel
audience: [pavel, ai, engineer]
tldr: Conventions for the db package — browser singleton vs admin factory, file naming, barrel-only index, and no-business-logic rule.
---
# DB — conventions

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

## Browser vs. admin separation

`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 — a client-bundle import of such a module is a build-time failure, not merely a code-review concern. The `@rapoport-studio/db` package itself stays marker-free: it ships an inert factory that is environment-agnostic until called, so the marker is enforced at the app edge where instantiation happens (see `narrative.md § Open questions`).

## Singleton for browser, factory for admin

The asymmetry is deliberate (see `narrative.md § Architecture`). Singleton for browser matches Supabase's single-client-per-page pattern; factory for admin keeps service-role clients out of long-lived shared module scope and makes the import name (`createAdminClient`) an obvious code-review signal in client-side bundles.

## File naming

Single file per concern (`client.ts`, `admin.ts`, `types.ts`, `index.ts`). Kebab-case where multi-word — none today. No subdirectories under `src/` except `__tests__/`.

## Barrel-only `index.ts`

`src/index.ts` does nothing but re-export. Three lines, no logic, no side effects. Single subpath export `.` in `package.json:5-7` means consumers always import from `@rapoport-studio/db` (no `@rapoport-studio/db/client` form).

## No business logic, no query helpers

The package exposes raw Supabase clients and the `Database` type. There are no convenience helpers (e.g., `getUser(id)`, `listProjects()`), no zod validators alongside the types, no transaction wrappers. Consumers write queries directly. Whether to add a query layer is an open question (see `narrative.md § Open questions`).

---

## RLS adversarial-audit convention (applied 2026-05-13 from change `validate-personal-data-security`)

Any PR that touches `supabase/migrations/**` containing RLS policy changes MUST run the adversarial-audit harness and attach a summary to the PR description.

**Convention:**

1. Run `tools/security/rls-redteam.sh` against a preview Supabase instance.
2. Paste the `PASS/FAIL` summary table into the PR description under `### RLS audit`.
3. Zero critical/major findings required to merge; minor findings may be accepted with one-line rationale.
4. The CI workflow (`.github/workflows/rls-audit.yml`) enforces this automatically once the workflow is wired (tracked in `archive/2026-05-13-validate-personal-data-security/` Phase 3 follow-up). Until then, the convention is manual.

**Source artefacts:** `tools/security/rls-redteam.sh` and `tools/security/scenarios/` (15 initial scenarios; additional scenarios added after LINDDUN review). Full run findings documented in `_methodology/security/canvas-rls-adversarial-audit-2026.md`.

---

## Project-membership-first RLS pattern (applied 2026-05-15 from change `redesign-canvas-permission-model`)

Any new table that gates access by canvas or project membership MUST follow the three-branch RLS chain below. Existing tables were rewritten to this pattern in migrations `20260515130000_rls_chain_through_project_members.sql` through `20260515130002_canvas_excluded_members_policies.sql`.

**Branch order (mandatory):**

1. **`platform_owner` short-circuit** — `(((auth.jwt() -> 'app_metadata') ->> 'platform_owner'))::boolean IS TRUE`. No DB roundtrip. Must always be Branch 1.
2. **`project_members` EXISTS** — check `project_members` for an active, non-expired row with the allowed role set. Include `NOT EXISTS canvas_excluded_members` subquery when the table is canvas-scoped.
3. **`canvas_invitees` EXISTS** — preserved legacy path; evaluated only when Branches 1 and 2 fail. May be removed for new tables that have no legacy-migration obligation.

**Role list discipline:**

- `USING` (read) and `WITH CHECK` (write) role lists differ — writes always have a stricter list than reads.
- `client` role is never allowed write access to any table except `canvas_messages`.
- `external_collaborator` cannot read `openspec/changes/**`, `openspec/current/**`, or `_methodology/**` paths in `canvas_files` — enforced via a separate policy on the Mirror-path split.
- `viewer` cannot write to any table.

**New tables:** copy the three-branch shape from `db/entities.md § Project members + role hierarchy § RLS pattern — three-branch policy`. Adapt `<roles allowed>` per the operation matrix in the same section.

**`project_members` self-policies:** separate four policies (SELECT/INSERT/UPDATE/DELETE) follow the P0.1 Strict rule — co-owner cannot promote anyone to `owner` or `co_owner` (enforced via `WITH CHECK`).

## Extension guidance

### Adding a new query pattern

1. Decide: does the helper belong in `db` (generic, schema-driven) or in `auth` / an app (caller-specific)? Default is the latter — keep `db` thin.
2. If it belongs in `db`: add a new module (e.g., `src/queries.ts`) and re-export from `src/index.ts`. Do **not** put it inside `client.ts` or `admin.ts` — those files are for client construction only.
3. Add tests in `src/__tests__/`.
4. Update `modules.md`'s module map.
