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

---
title: DB — Entities
status: stable
owner: pavel
audience: [pavel, ai, engineer]
tldr: All schema entities owned by the db Supabase project — canvas tables, user_settings, leads, RPCs, and every applied amendment.
---
# DB — entities

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

All schema entities owned by `@rapoport-studio/db`'s Supabase project (`nifagnmgwoqkplegsicy`, eu-central-2). Each `## …` heading that represents an applied amendment is preserved verbatim from db.md.

---

## Project → spec → idea hierarchy (applied 2026-05-22 from change `rearchitect-spec-document-model`, RAP-1170)

**Wave 1 of RAP-1170** dropped the 12 `canvas_*` tables and recreated the schema as a clean three-level hierarchy: `project → spec → idea → { messages, files }`. Every section below that documents the legacy `canvas_*` tables is **deprecated as of 2026-05-22** — the tables no longer exist on prod. The legacy sections are retained as a record of what was there; consumers must read this section for the current schema.

**Migration:** `supabase/migrations/20260522120000_rearchitect_spec_document_model.sql` (Wave 1, applied to prod 2026-05-22 via Supabase MCP).
**Follow-up:** `supabase/migrations/20260523000000_rap_1170_get_user_displays_idea_scoped.sql` recreates the `get_user_displays(p_idea_id uuid, p_user_ids uuid[])` RPC against the new schema. The first parameter was renamed from `p_canvas_id` to `p_idea_id`.

### New tables

| Table | Purpose | Key columns |
|---|---|---|
| **`specs`** | One per project. Holds the spec-level metadata that used to live on `canvas_sessions.mode` etc. | `id`, `project_id` FK, `title`, `created_at`, `updated_at` |
| **`ideas`** | The unit of change. Replaces `canvas_sessions`. Self-FK `parent_id` for the epic + child pattern (Wave 3d). | `id`, `spec_id` FK, `parent_id` FK self-ref, `author_user_id`, `slug`, `title`, `status` `idea_status` enum, `source` `idea_source` enum, lifecycle stage columns, `cost_cents`, `cost_cap_cents`, `github_branch`, `github_repo_full_name`, `last_pr_url`, … (32 columns total — see types.ts) |
| **`messages`** | Chat messages under an idea. Replaces `canvas_messages`. Same shape (`author_id`, `role`, `kind`, `content`, `sequence`, `cancellation`). | `id`, `idea_id` FK, `author_id` FK auth.users, `role`, `kind`, `content`, `sequence`, `created_at` |
| **`files`** | Curated files under an idea. Replaces `canvas_files`. New `status` `file_status` enum (`proposed | accepted | applied | rejected`) for the Wave 3b file-lifecycle FSM. | `id`, `idea_id` FK, `path`, `content`, `status` `file_status` enum, `base_sha`, `base_content`, `source_idea_id`, `source_path`, `last_emitted_by_message_id` |
| **`idea_attachments`** | Replaces `canvas_attachments`. Idea-scoped AI-file attachment join. | `idea_id` FK, `ai_file_id` FK |
| **`idea_instructions`** | Pinned per-idea instructions. Replaces `canvas_project_instructions`. | `idea_id` FK, `content`, `kind`, `pinned`, `author_user_id` |
| **`idea_proposals`** | Wave-3 agent proposal staging. New surface. | `idea_id` FK, `path`, `content`, `proposed_by_message_id` |
| **`idea_stage_events`** | Lifecycle stage transition audit. Replaces `canvas_stage_events`. | `idea_id` FK, `prev_stage`, `next_stage`, `prev_percent`, `next_percent`, `triggered_by_user_id`, `triggered_by_action`, `reason` |
| **`idea_tool_uses`** | Tool-use turn audit. Replaces `canvas_tool_uses`. | `idea_id` FK, `tool_use_id`, `name`, `start_ts`, `complete_ts`, `args`, `result`, `cost_cents` |
| **`idea_daily_costs`** | Daily cost rollup per idea. Replaces `canvas_daily_costs`. | `idea_id` FK, `day` date, `cents_used` |
| **`spec_invitees`** | Spec-level access table. Replaces `canvas_invitees`. Access moved one level up so invitations are per-spec, not per-idea. | `id`, `spec_id` FK, `invitee_email`, `invitee_user_id` FK auth.users, `inviter_email`, `invited_by`, `role`, `persona`, `invited_at`, `accepted_at`, `revoked_at`, `expires_at` |

### Enums

- **`idea_status`** — `captured | discussing | drafted | applied | epic | rejected` (Wave 3a lifecycle FSM)
- **`idea_source`** — `brief | chat | agent | manual` (provenance of the idea)
- **`file_status`** — `proposed | accepted | applied | rejected` (Wave 3b curatorial lifecycle)

### Column renames on retained tables

- `inbox.canvas_id_if_accepted` → `idea_id_if_accepted`
- `channels.bound_canvas_id` → `bound_idea_id`
- `ai_files.canvas_id` → `idea_id`

### Authorization helpers (functions)

- `is_platform_owner()` — unchanged
- `has_project_role(project_id uuid, allowed_roles text[])` — checks `project_members` for the calling user
- `has_spec_role(spec_id uuid, allowed_roles text[])` — checks via spec → project membership
- `has_idea_role(idea_id uuid, allowed_roles text[])` — checks via idea → spec → project membership
- `has_idea_invitee_role(idea_id uuid, allowed_roles text[])` — checks via idea → spec → `spec_invitees` direct grant

### What this replaces

The legacy schema (12 `canvas_*` tables rooted at `canvas_sessions`) is documented in the sections below for archival reference. Engineers writing new code should ignore those sections and read this one + `packages/db/src/types.ts` for canonical column shapes.

### RAP-845 specialist applications (applied 2026-05-23)

`supabase/migrations/20260523080000_rap_845_specialist_applications_and_specialists.sql` added the `specialist_applications` + `specialists` tables that had been spec'd in entities.md but never migrated. They unblocked the studio prod deploy (the studio code referenced the table; tsc was failing for a week). Shape matches the existing `§ public.specialist_applications` / `§ public.specialists` sections further below.

---

## Canvas access tables (applied 2026-05-02 from change `add-canvas-invitations`)

> **DEPRECATED 2026-05-22 (RAP-1170 Wave 1).** Tables dropped. See § "Project → spec → idea hierarchy" above for the replacement. The section below is preserved for archival reference only.

**`canvas_invitees`** — new table tracking per-canvas invitation state.

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` (PK) | gen_random_uuid() |
| `canvas_id` | `uuid` (FK `canvas_sessions.id` ON DELETE CASCADE) | the canvas the invitation is scoped to |
| `invitee_email` | `text` | denormalized for matching before invitee has signed up |
| `invitee_user_id` | `uuid` (FK `auth.users.id` ON DELETE SET NULL) | NULL until accepted; preserved post-acceptance |
| `inviter_email` | `text` | denormalized for audit (the owner's email at invite time) |
| `invited_by` | `uuid` (FK `auth.users.id`) | the owner who created the invitation |
| `role` | `text` | CHECK (`role IN ('collaborator')`) — `client` role lands in change `add-client-role` |
| `invited_at` | `timestamptz` | default `now()` |
| `accepted_at` | `timestamptz` | NULL until first successful login of invitee email |
| `revoked_at` | `timestamptz` | NULL unless owner revoked |

`UNIQUE (canvas_id, invitee_email)` prevents duplicate invitations for the same email-canvas pair. Indexes on `invitee_user_id` (proxy hot path) and `canvas_id` (owner's invite list). RLS: owner reads/writes own (matched via `canvas_sessions.user_id`); invitee reads own (`invitee_user_id = auth.uid()`).

**`canvas_messages.author_id`** — new nullable `uuid` column, FK to `auth.users(id)` ON DELETE SET NULL.

| Role | Author_id semantics |
|---|---|
| `role='user'` (going forward) | NOT NULL in practice — enforced at the application layer in `apps/studio/src/app/(auth-walled)/(canvas)/spec/[id]/actions.ts` `runTurn` inserts as of migration `20260502052231` |
| `role='user'` (legacy rows) | NULL preserved for messages authored before the migration; rendered as "Unknown" in shared canvases to keep attribution honest |
| `role='assistant'` | NULL by design — the UI maps NULL+assistant to "Muse" |

Resolution at render time: a `participantsById` map is built from the canvas owner (`canvas_sessions.user_id` → `auth.users`) plus accepted invitees (`canvas_invitees.invitee_user_id WHERE accepted_at IS NOT NULL AND revoked_at IS NULL`). Lookup by `author_id` returns the display name or "Unknown" / "Muse". `auth.users` is not exposed via PostgREST, so the display fields (email, full_name, avatar_url) come from the `public.get_user_displays` RPC — see § Public RPCs.

**`auth.users.raw_app_meta_data.platform_owner`** — bootstrap-only flag. Value `'true'` denotes a platform owner (currently `pavel.rapoport@gmail.com`, `hello@rapoport.studio`); absence or any other value denotes non-owner. Read from the JWT `app_metadata` claim in middleware — no DB roundtrip on the hot path. No UI in v1; managed via SQL only.

---

## Role enum extended 2026-05-02 (change `add-client-role`)

`canvas_invitees.role` CHECK constraint extended from `'collaborator'` only to `('collaborator', 'client')`. The `client` role grants chat-only access:

- `canvas_messages` RLS unchanged — clients read and write chat
- `canvas_sessions` RLS unchanged — clients see canvas in sidebar
- **`canvas_files` RLS** — invitee branch carries `AND ci.role <> 'client'` (originally tightened on the predecessor `canvas_artifacts` table; the policy was preserved verbatim by RAP-44's table swap), hard-walling clients from proposal/design/tasks/mirror/research files
- `canvas_invitees` RLS unchanged

Default value for new rows remains `'collaborator'`. Existing rows preserved.

---

## Persona on canvas_invitees (added 2026-05-03 from change `add-muse-research-tools-with-personas`)

`canvas_invitees.persona` is `TEXT NOT NULL DEFAULT 'domain'` with a CHECK enum `('architect','domain','tech','business','ops')`. Soft-routing axis independent of role. Default backfilled on existing rows.

`canvas_sessions.tool_cost_cents` is `INT NOT NULL DEFAULT 0` with `CHECK (>= 0)`. Per-canvas Muse tool-cost accumulator. Budget cap is 50 (= $0.50). Updated post tool execution in `executeToolWithGuards`. **Updated 2026-05-04 (column renamed `cost_cents`, cap raised to 500 = $5) — see § canvas_sessions.cost_cents column below.**

---

## Public RPCs (added 2026-05-03 from change `replace-listusers-with-rpc`)

Two SECURITY DEFINER functions in the `public` schema replace direct `admin.auth.admin.listUsers` calls (which silently truncated at 200 users/page). Both lock `search_path = public, auth, pg_temp`, are revoked from `public` and `anon`, and granted `EXECUTE` only to `authenticated`. Authz failures raise SQLSTATE `42501`.

| RPC | Args | Returns | Authz |
|---|---|---|---|
| `get_user_displays` | `p_canvas_id uuid, p_user_ids uuid[]` | `(id, email, display_name, avatar_url, default_persona, is_platform_owner)[]` | Caller must be the canvas owner OR an accepted invitee on `p_canvas_id`. Result rows are additionally filtered to the canvas owner ∪ accepted-invitees set (defense in depth). |
| `get_user_id_by_email` | `p_email text` | `uuid` or `NULL` | `auth.uid()` must be non-null (any authenticated user). Deterministic — `ORDER BY u.created_at ASC LIMIT 1` to handle OAuth duplicate-email rows. |

Caller pattern: both are invoked through the user-bound Supabase server client (`getServerSupabaseClient()`), not the service-role admin client — `auth.uid()` must be populated for the authz check to pass. Function definitions live in `supabase/migrations/20260503180944_rap_9_add_user_lookup_rpcs.sql`.

---

## Project slug binding for Mirror (applied 2026-05-03 from change `wire-muse-runtime-in-studio`)

Adds `canvas_sessions.project_slug` — nullable `TEXT` column — binding a canvas to a Mirror project (`openspec/mirror/<slug>/perception.md`) and gating the `write_mirror_field` Muse tool at runtime.

### Schema delta

- New column: `canvas_sessions.project_slug TEXT NULL`.
- New CHECK constraint `canvas_sessions_project_slug_format_check`: `CHECK (project_slug IS NULL OR project_slug ~ '^[a-z][a-z0-9-]{0,63}$')`. Allowlist regex tighter than the existing `canvas_sessions.slug` CHECK (which allows leading digits and unbounded length); the two columns are conceptually different — internal canvas slug vs Mirror project slug.
- New partial index `canvas_sessions_project_slug_idx ON (project_slug) WHERE project_slug IS NOT NULL`. Minimal cost on a small table; future-proof for per-slug queries.
- Migration: `20260503135012_add_project_slug_to_canvas_sessions.sql`. Wrapped `BEGIN; ... COMMIT;` per the precedent set by `add_canvas_invitees_and_seed_owner`. Existing rows stay `NULL`; no backfill.
- Types regenerated via Supabase MCP `generate_typescript_types`.

### Cross-slug RLS isolation (defense-in-depth audit pass)

Cross-slug isolation property — *a `client` invited to canvas A (project_slug='foo') cannot SELECT canvas B (project_slug='bar')* — already holds via the existing per-canvas invitation model. `canvas_invitees` rows are keyed by `canvas_id`; the invitee branch in every canvas-table RLS policy joins on `ci.canvas_id = canvas_sessions.id`. A client without an invitation row matching canvas B cannot SELECT it, regardless of slug.

**No RLS DDL is required for v1.** v2 settings-panel slug edits would invalidate the property (a client invited when slug was 'foo' could subsequently see the canvas with slug='bar'), and the v2 change would need to add an `AND ci.project_slug_at_invite = canvas_sessions.project_slug` style guard. Tracked as a follow-up in `studio-workspace/spec.md § Mirror runtime in canvas`.

SQL smoke verified end-to-end with a synthetic client JWT during migration apply: invited to canvas A, the synthetic client could SELECT canvas A but returned zero rows for canvas B (by id, by slug filter, and via JOIN canvas_messages).

### Studio-side enforcement (defense in depth)

Three additional layers re-validate slug shape outside the DB:

1. **Studio runtime** — `apps/studio/src/lib/project-slug.ts isValidProjectSlug` re-validates after canvas state load, before threading into `ToolUseLoopContext.toolContext.projectSlug`. On mismatch, drops to `undefined` and logs.
2. **Server action** — `createCanvasAction` re-validates form input before INSERT, returning a clean error to the dialog form rather than relying on the CHECK trap.
3. **Per-tool fallback** — `packages/muse/src/mirror/path.ts isInvalidSlug` (looser regex `^[a-z0-9][a-z0-9-]*$`) is the last belt+suspenders check inside `write_mirror_field.execute()` for Mirror-fs path safety.

---

## canvas_sessions.cost_cents column (applied 2026-05-04 from change `credit-token-cost-to-canvas-budget`)

Renames `canvas_sessions.tool_cost_cents` to `canvas_sessions.cost_cents` to reflect the broadened meaning — one column, both tool execution cost and per-turn assistant token cost. Budget cap raised 50 → 500 cents ($5/canvas). **Subsequently raised 500 → 5000 cents ($50/canvas) by RAP-957 (applied 2026-05-16 — see § canvas_sessions.cost_cents cap raised to $50 below).**

### Schema delta

- Column renamed in place: `canvas_sessions.tool_cost_cents` → `canvas_sessions.cost_cents`. Type unchanged: `INT NOT NULL DEFAULT 0`. CHECK constraint preserved (renamed to `canvas_sessions_cost_cents_check`, expression `cost_cents >= 0`).
- `COMMENT ON COLUMN canvas_sessions.cost_cents` updated to reference the $5 cap and the broadened meaning (tool + token cost). **Cap subsequently raised to $50 (5000¢) — see § canvas_sessions.cost_cents cap raised to $50 below.**
- Migration: `supabase/migrations/20260504001407_rap_37_rename_tool_cost_to_cost_canvas_sessions.sql`.
- Types regenerated — zero `tool_cost_cents` references in `packages/db/src/types.ts`.

### Why one column

Tool cost and assistant token cost share the same operational meaning — money spent on this canvas. Splitting them across two columns leaks an implementation detail (which subsystem charged the cents) into the schema. The pre-RAP-37 split existed only because architect-mode cost was already tracked elsewhere via forge; the canvas runtime never had a separate token accumulator.

---

## Inbox table RLS (applied 2026-05-04 from change `add-client-onboarding`)

The `inbox` table (created in Phase 1/2/3, migration `20260503000002_rap_14_inbox_rls.sql`) carries the following access model:

- **INSERT** — service role only. `/api/studio/leads` (in `apps/studio`) uses `createAdminClient()` to bypass RLS on lead creation. No authenticated user rows on write.
- **SELECT / UPDATE** — restricted to `platform_owner`. Policy expression: `auth.jwt() -> 'app_metadata' ->> 'platform_owner' = 'true'`. Only Pavel can read or update inbox rows from the studio app.

### Inbox status enum

`inbox.status` column: `TEXT NOT NULL DEFAULT 'new'`. Allowed values enforced by a CHECK constraint: `new`, `reviewing`, `accepted_pending_invite`, `accepted`, `declined`. The `accepted_pending_invite` value represents the M6 half-success intermediate state — canvas created but invite delivery failed. It is observable in the studio inbox and retryable via the Retry invite button.

### IP anonymization (applied 2026-05-26 from change `implement-inbox-ip-90day-anonymization`)

`inbox.ip_address` stores the submitter's IP for the first 90 days. After 90 days, the host bits are zeroed per GDPR Article 5(1)(e) — IPv4 to `/24`, IPv6 to `/48` (standard log-retention anonymization masks; preserves geolocation/ISP-prefix-level signal without retaining the data subject's identifier). The studio inbox detail view renders the IP with an inline "anonymized after 90 days" note.

**Mechanism:** SQL function `public.anonymize_old_inbox_ips()` (`SECURITY DEFINER`, idempotent via `masklen` guard on already-anonymized rows). Scheduled by `pg_cron` job `inbox-ip-anonymize-90d` daily at 04:15 UTC (staggered 15min after the existing `skill_specialist_counts` refresh). The `pg_cron` schedule statement in the migration is guarded by extension availability — when `pg_cron` is not installed (current state at apply time on this Supabase project), the function is still callable manually via `SELECT public.anonymize_old_inbox_ips();` and the schedule registers automatically the next time the migration runs against an instance with `pg_cron` enabled.

**Migration:** `supabase/migrations/20260525220000_implement_inbox_ip_90day_anonymization.sql`.

**Source-of-truth:** [`openspec/archive/2026-05-26-implement-inbox-ip-90day-anonymization/`](../../archive/2026-05-26-implement-inbox-ip-90day-anonymization/) — design decisions D-IIA-1 (anonymize in place, not delete), D-IIA-2 (`/24` IPv4, `/48` IPv6), D-IIA-3 (pg_cron not Worker cron). Originating compliance contract: [`openspec/archive/2026-05-13-validate-personal-data-security/`](../../archive/2026-05-13-validate-personal-data-security/).

---

## Knowledge system tables (applied 2026-05-04 from change `add-muse-knowledge-system`)

### canvas_project_instructions

Owner/collaborator-authored instructions that persist across turns for a canvas.

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid DEFAULT gen_random_uuid()` | PK |
| `canvas_id` | `uuid NOT NULL` | FK → `canvas_sessions(id)` |
| `author_user_id` | `uuid NOT NULL` | FK → `auth.users(id)` |
| `content` | `text NOT NULL` | Instruction body |
| `kind` | `text NOT NULL` | CHECK: `vocabulary`, `preference`, `constraint`, `context` |
| `pinned` | `boolean NOT NULL DEFAULT false` | Owner-pinned instructions sort first + reach all collaborators |
| `created_at` | `timestamptz NOT NULL DEFAULT now()` | |
| `updated_at` | `timestamptz NOT NULL DEFAULT now()` | |

**RLS**: owner reads/writes all; collaborator reads own + owner-pinned + writes own; client reads own pinned only.

Migration: `supabase/migrations/<ts>_add_canvas_project_instructions.sql`.

### studio_prompt_templates

Platform-owner-authored reusable prompt templates with variable substitution.

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid DEFAULT gen_random_uuid()` | PK |
| `slug` | `text UNIQUE NOT NULL` | URL-safe identifier |
| `name` | `text NOT NULL` | Display name |
| `description` | `text NOT NULL DEFAULT ''` | Short description |
| `content` | `text NOT NULL` | Template body; `{variable_name}` placeholders |
| `variables` | `jsonb NOT NULL DEFAULT '[]'` | String array of unique variable names extracted from content |
| `created_by` | `uuid NOT NULL` | FK → `auth.users(id)` |
| `last_used_at` | `timestamptz` | Updated on apply |
| `usage_count` | `integer NOT NULL DEFAULT 0` | Incremented on apply |
| `created_at` | `timestamptz NOT NULL DEFAULT now()` | |
| `updated_at` | `timestamptz NOT NULL DEFAULT now()` | |

**RLS**: `platform_owner` reads/writes all; authenticated reads (for apply); clients excluded.

Migration: `supabase/migrations/<ts>_add_studio_prompt_templates.sql`.

### canvas_messages.kind (extended column)

`kind` column added to `canvas_messages`: `text NOT NULL DEFAULT 'message' CHECK (kind IN ('message', 'instruction'))`. Messages sent via `/instruct <text>` are persisted with `kind='instruction'`; all others default to `'message'`. Indexed on `(canvas_id, kind)` for the recent-instructions query.

`template_id uuid NULL REFERENCES studio_prompt_templates(id)` also added to `canvas_messages` to track which template produced a given user message.

Migration: `supabase/migrations/<ts>_extend_canvas_messages_kind.sql`.

---

## Projects table (applied 2026-05-04 from change `add-projects-table`)

Introduces the first-class `projects` entity. A Project is the container for one or more Canvases — a single client engagement, internal platform deliverable, or external collaboration. Bindings to GitHub repositories, Linear teams/projects, and Mirror perception layers attach to a Project as **optional capabilities**, not preconditions. A Project may be active and authoring Mirror perception or OpenSpec docs without any binding ever being attached.

This change is the foundation; subsequent narrow changes (`add-project-bind-github`, `add-project-bind-linear`, `add-project-home`, etc., sequenced in `openspec/ROADMAP.md`) layer integrations and UI on top.

### Schema

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` PK | `gen_random_uuid()` |
| `slug` | `text NOT NULL UNIQUE` | CHECK regex `^[a-z][a-z0-9-]{0,63}$` — same shape as `canvas_sessions.project_slug` |
| `name` | `text NOT NULL` | CHECK `length(name) BETWEEN 1 AND 200` |
| `owner_id` | `uuid NOT NULL` | FK → `auth.users(id) ON DELETE RESTRICT` |
| `status` | `text NOT NULL DEFAULT 'active'` | CHECK in `('active', 'archived')` |
| `archived_at` | `timestamptz NULL` | Set when archiving |
| `created_at` | `timestamptz NOT NULL DEFAULT now()` | |
| `updated_at` | `timestamptz NOT NULL DEFAULT now()` | |

Indexes: `projects_owner_idx ON (owner_id)`; `projects_status_active_idx ON (status) WHERE status = 'active'`.

### Lifecycle semantics

`status = 'active'` is the default for new projects. `status = 'archived'` is the soft-delete state; `archived_at` is set at the same time. v1 supports archive only — hard-delete is deferred until a real need surfaces (it would require reassigning canvases and Mirror file cleanup). Slugs are immutable post-creation, mirroring the canvas slug pattern; rename is tracked as a future v1.1+ design question.

### Integrations are always optional

External-service bindings live in `project_connections` (see `## Project Connections` below) and are nullable / absent throughout the project lifecycle. The studio's vision requires that an idea-stage project exist as a workspace **before** a GitHub repository or Linear tracker is appropriate. New connection kinds add **capabilities** to bind, never **requirements**. Application code must not assume any specific connection exists.

### Binding writers — replaced 2026-05-08 by `add-project-connections`, columns dropped 2026-05-09 by `drop-deprecated-projects-binding-columns` (RAP-175)

The original `add-project-bindings` writers (RAP-55 + RAP-56) wrote to four bespoke columns on `projects` (`github_repo`, `github_repo_url`, `linear_team_id`, `linear_project_id`). 2026-05-08 superseded the surface with `project_connections`; 2026-05-09 dropped the four columns physically. See `## Project Connections` below for the live schema and write surface.

### RLS

Enabled. Single permissive `ALL` policy mirroring the chain-through-canvas pattern used by sibling tables, but with the chain inverted (Project is the parent):

- **Owner** (`auth.uid() = owner_id`) — reads/writes own.
- **Collaborator invitee on a canvas in the project** — read-only (project layer).
- **Client invitee** — explicitly excluded from the EXISTS branch (`AND ci.role = 'collaborator'`). Clients participate in canvas chat but do not see Project-layer aggregates.
- **`WITH CHECK (auth.uid() = owner_id)`** — only owners INSERT/UPDATE.
- `platform_owner` JWT shortcut not added (the existing canvas policies don't use it for cross-owner reads either; `inbox` is the lone exception).
- `revoked_at` not filtered. Matches the existing canvas RLS pattern. If we tighten the convention later, we tighten it everywhere as a separate change.

RLS smoke verified against the live project on 2026-05-04: owner reads / stranger blocked / collaborator reads / client blocked / non-owner INSERT rejected with SQLSTATE 42501 ("new row violates row-level security policy for table projects").

### canvas_sessions delta

`canvas_sessions.project_id uuid NULL REFERENCES projects(id) ON DELETE RESTRICT`. Partial index `canvas_sessions_project_id_idx ON (project_id) WHERE project_id IS NOT NULL`. `ON DELETE RESTRICT` is deliberate — a project archive should not cascade-delete canvases. Hard-delete of a project requires explicitly null'ing the FK on its canvases first (or moving them to another project).

`canvas_sessions.project_slug` is **kept** as a denormalised cache of `projects.slug` for file-system path uses (Mirror's `openspec/mirror/<slug>/perception.md`). Application-level invariant: when both columns are present, they must match. Slugs are immutable, which makes the cache trivially consistent (set once at canvas creation, never updated). The existing CHECK regex on `project_slug` stays unchanged.

### Backfill

The migration backfilled 2 `projects` rows from existing distinct `(project_slug, owner)` pairs in `canvas_sessions` (`i-avocat` owned by Pavel, `test` owned by hello@). All slug-bound canvases (3 rows: 2 i-avocat, 1 test) had `project_id` populated to match. Orphan canvases (`project_slug IS NULL`, 8 rows) keep `project_id IS NULL` — no fabricated bindings. A pre-flight assertion in the migration aborts if any `project_slug` value spans multiple owners; verified zero conflicts at apply time.

Migration: `supabase/migrations/20260504175547_add_projects_table.sql`.

### Studio self-hosting backfill (applied 2026-05-09 from change `migrate-studio-into-platform`, RAP-169)

Post-`reset-and-squash-database` (2026-05-05) the studio itself was missing as a `projects` row. The onboarding migration `20260509100000_register_studio_project.sql` lands the row idempotently:

- Resolves `owner_id` via `SELECT id FROM auth.users WHERE email = 'pavel.rapoport@gmail.com'` (raises if the row is missing — the `bootstrap_platform_owner` trigger seeds `platform_owner=true` on first magic-link login).
- Inserts `projects(slug='studio', name='Rapoport Studio', status='active')` with legacy bind columns populated for parity (`github_repo`, `github_repo_url`, `linear_team_id`).
- Inserts the two `project_connections` rows (kind=`github` + kind=`linear`) the registry-driven Connections card needs to light up. The `linear` config carries `teamId='c48bbbb1-9eba-477d-87d2-d082c506d885'` (Linear API team UUID, captured 2026-05-09); `teamKey: 'RAP'` is added at runtime by the first verify pass.

`ON CONFLICT (slug) DO UPDATE` and `ON CONFLICT (project_id, kind) DO UPDATE` keep the migration safely re-runnable. No new tables, no new columns — the change is row-level data only. Orphan-canvas re-parenting was scoped out at apply time (Wave 0.4 audit found the four `project_id IS NULL` canvases were short-lived UI typing-tests with no studio corpus authored in them).

Migration: `supabase/migrations/20260509100000_register_studio_project.sql`.

---

## canvas_files table + canvas_sessions mode columns (applied 2026-05-05 from change `complete-canvas-file-lifecycle`)

Replaces the versioned, three-name-only `canvas_artifacts` table with a path-keyed `canvas_files` table that handles arbitrary OpenSpec / Mirror / research file paths via UPSERT on `(canvas_id, path)`. Adds a `mode` lifecycle to `canvas_sessions` so the canvas surface can distinguish authoring states (`draft` → `project` → `legacy_promoted`). The DB schema landed via RAP-44; the runtime rewire (Tracks A–E, RAP-70/71/72/73/74) followed once the columns existed; RAP-75 (Track F) dropped the legacy `canvas_artifacts` table and its scaffolding (the `saved_at` column, the partial-pending unique index, the role <> 'client' RLS tightening) on `2026-05-05`.

### canvas_files

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` PK | `gen_random_uuid()` |
| `canvas_id` | `uuid NOT NULL` | FK → `canvas_sessions(id) ON DELETE CASCADE` |
| `path` | `text NOT NULL` | CHECK `length(path) BETWEEN 1 AND 512`; CHECK `path !~ '\.\.|^/|^[a-zA-Z]:/'` (no traversal, no absolute paths, no Windows drive letters) |
| `content` | `text NOT NULL` | CHECK `length(content) <= 102400` (100 KiB cap; OpenSpec markdown comfortably fits) |
| `is_pending` | `boolean NOT NULL DEFAULT true` | Toggled `false` by `saveArtifactAction` (draft mode) or `saveFileAction` (project mode, RAP-73) |
| `base_sha` | `text NULL` | GitHub blob SHA the canonical anchor was captured from. NULL until conversion to project mode. |
| `base_content` | `text NULL` | Snapshot of `main` content at conversion time, used for the per-file diff view. NULL until conversion. CHECK `(base_sha IS NULL) = (base_content IS NULL)` keeps both fields consistent. |
| `last_emitted_by_message_id` | `uuid NULL` | FK → `canvas_messages(id) ON DELETE SET NULL`; the assistant message that most recently UPSERTed this row. Drives the chat-history `<ArtifactDiffBlock>` bucket. |
| `source_canvas_id` | `uuid NULL` | FK → `canvas_sessions(id) ON DELETE SET NULL`. Set by `copyFileAction` (RAP-73) to the source canvas. Surfaces as a `copy` lineage badge in the file tree. |
| `source_path` | `text NULL` | Source path companion to `source_canvas_id`. Together they answer "where did this row come from?". |
| `created_at` | `timestamptz NOT NULL DEFAULT now()` | |
| `updated_at` | `timestamptz NOT NULL DEFAULT now()` | Stamped on every UPSERT. |

`UNIQUE (canvas_id, path)` enforces one row per file per canvas — the basis of UPSERT-on-emit. Indexes: `canvas_files_canvas_idx ON (canvas_id)` for the per-canvas tree query; partial `canvas_files_pending_idx ON (canvas_id) WHERE is_pending = true` for the pending-count badge in the canvas header strip.

**RLS** mirrors the now-dropped `canvas_artifacts` policy verbatim — owner reads/writes; collaborator invitee reads/writes; client invitee excluded (`AND ci.role <> 'client'` on the invitee branch); anon excluded. Both `USING` and `WITH CHECK` carry the role filter so a client cannot write rows it cannot read.

Migration: `supabase/migrations/20260504214100_rap_44_add_canvas_files_table.sql`.

### canvas_sessions mode + GitHub anchor columns

| Column | Type | Notes |
|---|---|---|
| `mode` | `text NOT NULL DEFAULT 'draft'` | CHECK in `('draft', 'project', 'legacy_promoted')`. Orthogonal to `status`. New canvases start `draft`; conversion via `convertToProjectAction` (RAP-73) flips to `project`. The seven canvases that pre-dated the lifecycle were backfilled to `legacy_promoted` and remain frozen / read-only. |
| `github_branch` | `text NULL` | Set by `convertToProjectAction` to the GitHub branch the canvas was committed to (e.g. `canvas/<slug>`). Drives the canvas-header-strip mode pill suffix `Project (canvas/<slug>)`. |
| `github_repo_full_name` | `text NULL` | Resolved repo at conversion time (`owner/repo`). Persisted alongside the branch so subsequent saves don't have to re-resolve. |
| `converted_to_project_at` | `timestamptz NULL` | Audit timestamp set by `convertToProjectAction`. |
| `base_sha_at_conversion` | `text NULL` | The branch's tip SHA at the moment of conversion — useful for diagnostics if a canvas's history needs to be reconstructed. |
| `last_pr_url` | `text NULL` | Set by `openPullRequestAction` (RAP-73). Toggles the `[Open PR]` button to `[View PR]` linking to GitHub. Idempotent on re-open thanks to the action's `alreadyOpen` handling. |

Migration: `supabase/migrations/20260504214200_rap_44_add_canvas_sessions_mode_columns.sql`.

### canvas_artifacts → canvas_files backfill, then drop

Pre-existing `canvas_artifacts` rows were collapsed to one row per `(canvas_id, file)` pair (the latest version's content) and inserted into `canvas_files` with `is_pending=true` and the source row's `last_emitted_by_message_id` preserved. **Pre-conversion in-canvas version history was intentionally dropped** — the new model uses GitHub commits for history once a canvas converts to project mode, and pre-conversion iteration history was rarely consulted. A `pg_dump` of `canvas_artifacts` was taken before each migration; the per-migration backups (`canvas_artifacts_pre_rap44_*.json`, `canvas_artifacts_pre_rap75_*.sql`) were superseded and deleted by the RAP-76 reset (see § Database reset and squash below). The full pre-reset state — including all `canvas_artifacts` history reconstructible from the historical migrations — lives in `tools/backups/full_schema_pre_reset_20260505171931.sql` (DDL) and `tools/backups/full_data_pre_reset_20260505171931.json` (data snapshot).

The `legacy_promoted` short-circuit in `apps/studio/.../spec/[id]/loaders.ts toArtifactRow` papers over the backfill choice — every migrated row landed `is_pending=true`, but those seven canvases must still render as saved (they're frozen). The synthesizer forces `savedAt = updated_at` whenever `mode === 'legacy_promoted'`.

Migrations:
- `supabase/migrations/20260504214300_rap_44_migrate_canvas_artifacts_to_canvas_files.sql` — backfill latest-version-only.
- `supabase/migrations/20260505163010_rap_75_drop_canvas_artifacts.sql` — `DROP TABLE canvas_artifacts CASCADE`. Cascade also dropped the `canvas_artifacts_one_pending` partial unique index, the `saved_at` column scaffolding, and the canvas_artifacts RLS policies. Earlier paragraphs in this file that refer to `canvas_artifacts` (the role-extension RLS tightening, the diff-and-save scaffolding) describe historical state — the table no longer exists.

> **Migration filenames above were archived under `supabase/migrations/_archive_pre_squash/` by RAP-76 (see § Database reset and squash below).** They remain in git as a historical reference; the live database is rebuilt from the single squash baseline `supabase/migrations/20260505173723_reset_and_squash.sql`. Do not run the archived migrations.

---

## Database reset and squash (applied 2026-05-05 from change `reset-and-squash-database`)

Live Supabase project `nifagnmgwoqkplegsicy` was reset and rebuilt from a single squashed baseline migration replacing all 26 prior migrations (`canvas_architect_mode` through `rap_75_drop_canvas_artifacts`). New baseline lives at `supabase/migrations/20260505173723_reset_and_squash.sql`; the 26 historical files are archived under `supabase/migrations/_archive_pre_squash/` for reference only — see [`supabase/migrations/_archive_pre_squash/README.md`](../../supabase/migrations/_archive_pre_squash/README.md). The squash represents the same post-RAP-75 schema state byte-for-byte: regenerated `packages/db/src/types.ts` produced a zero-diff against the pre-reset file. No app code changed; the reset is a history-collapse, not a feature change.

### What was destroyed

- **All `public.*` data wiped** — 12 canvases, 185 messages, 14 canvas_files rows, 7 canvas_invitees, 15 canvas_tool_uses, 2 projects. Empty: inbox, canvas_project_instructions, studio_prompt_templates.
- **All `auth.users` wiped** (7 rows). Pre-existing Supabase Auth sessions on `app.rapoport.studio` are invalidated; clients re-login via magic-link to recreate their `auth.users` row from scratch.
- **`supabase_migrations.schema_migrations` cleared and reseeded with the single baseline** — `list_migrations` returns one row (version `20260505174438`).
- Stale per-table backups `tools/backups/canvas_artifacts_pre_rap44_*.json` and `canvas_artifacts_pre_rap75_*.sql` deleted; the pre-reset full snapshot `tools/backups/full_{schema,data}_pre_reset_20260505171931.{sql,json}` supersedes them.

### What was preserved

- Same Supabase project, same URL, same anon/service-role keys. Cloudflare Workers secrets unchanged; no `wrangler secret put` reapplied.
- Same nine tables, same 15 RLS policies, same two RPCs (`get_user_displays`, `get_user_id_by_email`), same three `set_updated_at` BEFORE-UPDATE triggers wired to `extensions.moddatetime`.
- Same FK shape, same CHECK constraints, same indexes (verified by zero-diff `packages/db/src/types.ts` regen).

### `bootstrap_platform_owner_trigger` — new in this baseline

The squash adds **one new piece of behaviour** that didn't exist pre-RAP-76: a `BEFORE INSERT ON auth.users FOR EACH ROW` trigger that stamps `raw_app_meta_data.platform_owner = true` for whitelisted emails. Replaces the historical one-shot `UPDATE auth.users SET raw_app_meta_data ||= '{"platform_owner": true}' WHERE email IN (...)` seed pattern from `20260502094137_add_canvas_invitees_and_seed_owner.sql`, which silently no-op'd on a fresh database (zero rows in `auth.users` until the first magic-link signup, so the seed updated nothing).

```sql
CREATE OR REPLACE FUNCTION public.bootstrap_platform_owner()
RETURNS TRIGGER LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_temp AS $$
BEGIN
  IF NEW.email IN ('hello@rapoport.studio', 'pavel.rapoport@gmail.com') THEN
    NEW.raw_app_meta_data := COALESCE(NEW.raw_app_meta_data, '{}'::jsonb)
                              || '{"platform_owner": true}'::jsonb;
  END IF;
  RETURN NEW;
END;
$$;

CREATE TRIGGER bootstrap_platform_owner_trigger
  BEFORE INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.bootstrap_platform_owner();
```

**Why `BEFORE INSERT`, not `AFTER`.** Supabase signs the access-token JWT at session creation, reading `raw_app_meta_data` from the just-inserted `auth.users` row. `BEFORE INSERT` mutates `NEW.raw_app_meta_data` in place, so the JWT signed for the very first request already carries `app_metadata.platform_owner = true` and `packages/auth/src/proxy.ts` short-circuits without a DB roundtrip from the first hit. An `AFTER INSERT` UPDATE trigger would create a window where the first request flows through the slow-path invitee lookup before the next session refresh picks up the flag.

**Whitelist hardcoded in the DB function.** The two emails (`hello@rapoport.studio`, `pavel.rapoport@gmail.com`) are baked into `bootstrap_platform_owner()`. Adding a third platform owner is a follow-up Linear issue — either a new migration that extends the `IN (...)` list or, more durably, a small UI change behind `inviteUserByEmail` + manual UPDATE that preserves the audit trail. Out of scope for RAP-76 by design (see `openspec/archive/2026-05-05-reset-and-squash-database/proposal.md` § Out of scope).

**Trigger smoke-tested synthetically before live deploy** via a TEMP TABLE clone of `auth.users` with the same trigger attached: `hello@rapoport.studio` got `platform_owner: true`, an arbitrary non-whitelisted email got no `platform_owner` key. Live verification (real magic-link login → `/token-check` JWT inspection) is a manual checkpoint at the head of `apps/studio` after the reset.

### Recovery path

If the reset proves catastrophic:

1. **Schema rollback**: `tools/backups/full_schema_pre_reset_20260505171931.sql` is the concatenated text of all 26 historical migrations in apply order. Drop the squash, drop the `_archive_pre_squash` prefix from the historical files, and re-apply via `apply_migration` per file.
2. **Data rollback**: `tools/backups/full_data_pre_reset_20260505171931.json` carries every row from every public table plus a snapshot of `auth.users` (including the platform_owner flag on the two original admin rows). Replay via `INSERT ... SELECT FROM jsonb_array_elements(...)` per table; the order matters because of FK chains — projects → canvas_sessions → canvas_messages → canvas_files / canvas_tool_uses / canvas_project_instructions / canvas_invitees / inbox.
3. **JWT rollback** is impossible — every active Supabase Auth session is invalidated by the `auth.users` truncate. Affected users must re-authenticate via magic-link regardless of whether the schema/data is rolled back.

### Migration discipline going forward

New migrations land as ordinary `supabase/migrations/<ts>_<name>.sql` files; they are NOT prefixed with `_` or placed in `_archive_pre_squash/`. Supabase MCP picks up everything in `supabase/migrations/*.sql` matching the `<14-digit-timestamp>_<name>.sql` shape. The single baseline + per-feature additive migrations is the working model from this point on.

---

## User settings (applied 2026-05-05 from change `add-user-settings`)

First-class per-user preferences surface. Adds `public.user_settings` (one row per user, JSONB blob) with RLS owner-read/write-own. Unblocks layout state persistence, default Muse persona, default GitHub repo, and a placeholder locale override slot that downstream consumers can read once the studio adopts i18n.

### Schema

| Column | Type | Notes |
|---|---|---|
| `user_id` | `uuid` PK | FK → `auth.users(id) ON DELETE CASCADE` |
| `settings` | `jsonb` NOT NULL DEFAULT `'{}'::jsonb` | shape enforced in TypeScript via zod, not DDL — schema evolves without migrations |
| `created_at` | `timestamptz` | default `now()` |
| `updated_at` | `timestamptz` | maintained by `extensions.moddatetime` BEFORE-UPDATE trigger |

`UNIQUE (user_id)` is implicit in the PK. No additional indexes needed for a 1:1 join shape.

### RLS

Single permissive policy `"user_settings: owner read/write own"` (`FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id)`). `service_role` bypasses RLS by Supabase convention so admin/automation tooling stays unblocked.

### TypeScript schema (zod)

The shape lives in [`apps/studio/src/lib/user-settings.ts`](../../apps/studio/src/lib/user-settings.ts). Six namespaces, each nested with `.default({})` so an empty `settings` JSONB parses to a fully-defaulted object:

```
layout.canvasChatWidth        : 'compact' | 'normal' | 'wide'      → 'normal'
layout.fileTreeWidth          : int [200..400]                     → 256
layout.instructionsPanelDefault : 'open' | 'closed'                → 'closed'
appearance.theme              : 'light' | 'dark' | 'system'        → 'system'
appearance.locale             : 'auto' | 'en' | 'ru' | 'ro'        → 'auto'
appearance.reducedMotion      : boolean                            → false
workflow.defaultPersona       : 'architect' | 'domain' | 'tech' | 'business' | 'ops' → 'architect'
workflow.defaultGithubRepo    : string | null                      → null
workflow.recentCanvases       : Array<{id, slug, lastVisited}> max 10 → []
workflow.pinnedCanvases       : string[] max 20                    → []
notifications.email.invitation: boolean                            → true
notifications.telegram.taskMention : boolean                       → true
onboarding.welcomeTourSeen    : boolean                            → false
onboarding.lastSeenChangelogAt: string | null                      → null
integration.githubUsername    : string | null                      → null
integration.linearUserId      : string | null                      → null
integration.telegramUserId    : number | null                      → null
```

Unknown root-level keys are stripped on parse (`z.strip()`) so a schema-evolved old row with legacy keys doesn't fail validation.

### Server-side helpers

- `loadUserSettings()` — server-only fetch with safe fallback to `DEFAULT_USER_SETTINGS` for unauthenticated callers, missing rows, and rows whose JSONB blob fails zod validation. Logs the validation failure but never crashes the page render.
- `updateUserSettingsAction(prev, patch)` — server action at [`apps/studio/src/app/actions/user-settings.ts`](../../apps/studio/src/app/actions/user-settings.ts). Loads current row, deep-merges patch (arrays replaced, not concatenated), re-validates the merged result via zod, upserts. Returns `unauthorized` / `invalid_patch` / `db_error` / `{ ok: true, settings }`. Reducer signature so it backs both `useActionState` forms and direct fire-and-forget calls.

### Consumers

| Surface | Reads | Writes | Notes |
|---|---|---|---|
| Canvas header strip toggle | `layout.canvasChatWidth` (server-rendered initial) | Optimistic UI + fire-and-forget `updateUserSettingsAction` on click | RAP-77 Phase 4 |
| Canvas chat-column max-width | `layout.canvasChatWidth` → CSS variable `--chat-max-w` | (read-only consumer) | `compact: 36rem` / `normal: 48rem` / `wide: 100%` |
| `/settings` Workspace preferences card | All four user-facing fields (persona / chat width / repo / locale) | Form submit → `updateWorkspacePreferencesAction` → `updateUserSettingsAction` | RAP-77 Phase 5 |
| `get_user_displays` RPC | `workflow.defaultPersona` (preferred), falls back to `auth.users.raw_app_meta_data.default_persona` | (read-only) | RAP-77 Phase 6 — see § `default_persona` migration below |

### `default_persona` migration

`get_user_displays` historically read `default_persona` from `auth.users.raw_app_meta_data->>'default_persona'` only. That slot has no UI writer and could only be set via service-role SQL. RAP-77 Phase 6 updates the RPC to read from `public.user_settings.settings->'workflow'->>'defaultPersona'` first, falling back to the legacy slot for users without a `user_settings` row yet.

```sql
COALESCE(
  NULLIF(us.settings->'workflow'->>'defaultPersona', ''),
  NULLIF(u.raw_app_meta_data->>'default_persona', '')
) AS default_persona
```

Backfill was a no-op on apply day — zero `auth.users` rows had the legacy slot populated. The fallback exists for forward compatibility; the legacy slot can be retired in a future cleanup once we're confident no out-of-band writer touches it. Migration: `supabase/migrations/20260505202304_get_user_displays_read_user_settings.sql`.

### Storage choice (rejected alternatives)

Single new table picked over two alternatives, see `openspec/archive/2026-05-05-add-user-settings/design.md § 3` for the full analysis:

- **Extend `auth.users.user_metadata`** rejected — JWT bloat, OAuth merge risk, no audit timestamps, no SQL-level RLS surface.
- **Hybrid `profiles` + `user_settings`** rejected — over-engineering for v1. Display fields (full_name, avatar_url) stay Supabase-managed in `user_metadata`; preferences land in `user_settings`. Revisit when an editable display name override becomes a real user-facing need.

### Locale override (Phase 7) — deferred

The `appearance.locale` field is captured in the schema and writeable from `/settings`, but the studio app currently has no `next-intl` infrastructure (only `apps/web` does). The consumer that overrides `Accept-Language` ships in a separate change once studio adopts i18n. Until then the saved value is dormant — round-trips through the form and the DB but doesn't change rendered text.

### `anthropic_api_key` is not migrated

`auth.users.user_metadata.anthropic_api_key` (the per-user override of the studio's bot key, used inside Muse turn route) stays where it is. It's a special secret-shaped slot updated by `updateApiKeyAction` and consumed at runtime; folding it into `user_settings.settings.integration.*` would expose it to a broader read surface than necessary. Tracked as a deliberate non-migration in `openspec/archive/2026-05-05-add-user-settings/design.md § 8`.

---

## Community member tier (applied 2026-05-06 from change `add-studio-self-serve-signup`)

Adds two `public` tables and two `auth.users` triggers backing the `community_member` access tier introduced in `auth.md § Community member tier`. Migration: `supabase/migrations/20260506080000_add_community_member_tier.sql`.

### `public.community_members`

Profile row materialized whenever a user gets the `app_metadata.community_member: true` flag. The flag is the proxy hot-path source of truth; this table carries everything else.

| Column | Type | Notes |
|---|---|---|
| `user_id` | `uuid` (PK, FK `auth.users.id` ON DELETE CASCADE) | One row per community member |
| `joined_at` | `timestamptz` | default `now()` — set when the row is materialized |
| `discipline` | `text` | nullable; populated when the user submits a `certified_applications` row |
| `display_opt_in` | `boolean` | default `false` — forward-compat for public `/network` listing; **no UI surfaces it in v1** |

RLS:

- `SELECT` — community member can read own row (`auth.uid() = user_id`); `platform_owner` can read all.
- `UPDATE` — community member can update own row (currently no writeable column; reserved for `display_opt_in` flip when the future profile-display surface ships).
- `INSERT` / `DELETE` — service role only. INSERT is driven by the `sync_community_member_row` trigger; DELETE cascades from `auth.users`.

### `public.certified_applications`

Stores apply-to-certified submissions — the form on `/community/apply`. Captured but not auto-reviewed in v1 (review happens out-of-band; an admin surface is a follow-up change — see `studio-workspace/spec.md § Community member surface § Out of scope`).

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` (PK) | default `gen_random_uuid()` |
| `user_id` | `uuid` (FK `auth.users.id` ON DELETE CASCADE) | the applicant; one user can hold multiple applications over time |
| `discipline` | `text` | CHECK constraint enum: `design / engineering / product / research / other` (refinable in a follow-up change) |
| `portfolio_urls` | `text[]` | length-bounded (1..N) |
| `spec_sample_url` | `text` | nullable |
| `pitch` | `text` | nullable, length-bounded |
| `nda_accepted_at` | `timestamptz` | required (server-stamped at insert) |
| `coc_accepted_at` | `timestamptz` | required (server-stamped at insert) |
| `submitted_at` | `timestamptz` | default `now()` |
| `reviewed_at` | `timestamptz` | nullable |
| `reviewed_by` | `uuid` (FK `auth.users.id`) | nullable |
| `outcome` | `text` | nullable, CHECK enum: `accepted / declined / shadow` |
| `notes` | `text` | nullable, internal review notes |

RLS:

- `SELECT` — applicant reads own rows (`auth.uid() = user_id`); `platform_owner` reads all.
- `INSERT` — applicant inserts own row, gated on `app_metadata.community_member = true`. Cannot mutate after submission.
- `UPDATE` — `platform_owner` only, on `outcome` / `reviewed_at` / `reviewed_by` / `notes`. Applicants cannot edit submitted applications.
- Service role unrestricted (bypasses RLS).

### Triggers on `auth.users`

Two `SECURITY DEFINER` triggers with `search_path` locked and `REVOKE ALL ... FROM PUBLIC` on each function:

1. **`bootstrap_community_member`** — `BEFORE INSERT` on `auth.users`. Reads `NEW.raw_user_meta_data ->> 'signup_intent'`; if `'community'`, mutates `NEW.raw_app_meta_data` to add `community_member: true`. Mirrors `bootstrap_platform_owner`. Critical property: the mutation happens *before* commit, so the JWT signed at session creation already carries the flag.
2. **`sync_community_member_row`** — `AFTER INSERT` and `AFTER UPDATE OF raw_app_meta_data` on `auth.users`. If the `community_member` flag is present, `INSERT INTO public.community_members (user_id) ON CONFLICT DO NOTHING`. Materializes the profile row on signup or on later service-role admin promotion.

Both triggers also forward `joined_at` (default `now()`) implicitly via the table default. No application-side write is permitted — drift between the JWT flag and the DB row is structurally impossible modulo manual admin DB writes.

### Why the hybrid (flag + table) shape

Mirrors the existing access primitives:

- `platform_owner` lives in `app_metadata` only (no DB row needed — internal staff is small, no profile data).
- `canvas_invitees` lives in DB only (per-canvas access is finer-grained than a single boolean; there's no flag mirror).
- `community_member` lives in **both** — flag for hot-path access decisions in the proxy, row for profile + application data, kept in sync by triggers.

Future tiers should reuse this pattern: the flag if hot-path access is involved; the row if there's profile or audit data; both if both.

---

## Forge tables (applied 2026-05-07 from change `add-forge-ui-task-detail`)

Four new tables under `public` back the Forge UI surface (`forge/entities.md § UI surface`). All four enable RLS — `platform_owner` is the only role that can read or write in v1. Foreign-key chains lock the hierarchy: every step / finding / output cascades from a single `forge_runs` row.

### `forge_runs`

One row per execution of `forge build` / `forge audit` / etc.

- **PK** `id` `uuid`
- **Identifier** `linear_issue_key` `text NOT NULL` — `'RAP-78'`-shaped; not FK to Linear (Linear is external).
- **Lifecycle** `command` (`'build' | 'audit' | 'spec' | 'review'`), `status` (`'queued' | 'running' | 'awaiting_input' | 'done' | 'failed' | 'cancelled'`), `base_branch`, `base_sha`, `head_branch`, `head_sha`, `worktree_path`.
- **Cost** `model` (`'opus' | 'sonnet'`), `cost_usd numeric(10,4)`, `cost_cap_usd numeric(10,4) default 5.00`, `auto_advance boolean default true`.
- **Audit** `created_by` (FK `auth.users.id`), `started_at`, `completed_at`, `cancelled_at`, `failed_at`, `failed_reason`.
- Indexes: `(linear_issue_key, created_at desc)`; partial `(status)` filtered to active states (`queued`, `running`, `awaiting_input`).

### `forge_steps`

One row per phase per run. Phases are fixed (`plan` / `implement` / `review` / `pr`), enforced by `unique (run_id, phase)`.

- **PK** `id` `uuid`
- **FK** `run_id → forge_runs.id ON DELETE CASCADE`
- **Phase** `phase` (CHECK in 4 values), `ordinal smallint` (1..4), `status` (CHECK in `pending / running / awaiting_input / done / failed / skipped`).
- **Payload** `payload jsonb` (phase-specific — e.g. `payload.plan_md`), `tool_calls jsonb` (array of `{ name, input, output, status, duration_ms, cost_usd }`).
- **Cost** `cost_usd`, `input_tokens`, `output_tokens`.
- Index: `(run_id, ordinal)`.

### `forge_findings`

One row per review/audit finding, surfaced in Phase 3 UI.

- **PK** `id` `uuid`
- **FKs** `run_id → forge_runs.id ON DELETE CASCADE`, `step_id → forge_steps.id ON DELETE CASCADE`
- **Source** `agent` `text NOT NULL` — slug identifying the Forge agent that emitted this finding. Free-text: no CHECK constraint; the UI renders whatever string is stored verbatim. Known producers listed below.

  > `agent` is free-text (`text NOT NULL`, no CHECK constraint). Any string a Forge agent emits is persisted verbatim and rendered as-is by the UI. The tables below enumerate **known producers**, not an enforced enum. Adding a new agent slug requires no migration — update this list when the slug ships.

  **v1 producers** (code-review audit — `forge review`):

  | Slug | Role |
  |---|---|
  | `security` | Security vulnerability review |
  | `a11y` | Accessibility review |
  | `design-system` | Design-system consistency review |
  | `architect` | Architecture review |
  | `qa` | Quality assurance review |
  | `spec-fidelity` | Spec-fidelity review |

  **`openspec-*` producers** (`forge audit openspec-change <slug>`):

  | Slug | Kind | Role |
  |---|---|---|
  | `openspec-principal-architect` | obligatory | Principal architecture concerns (weight 1.3×) |
  | `openspec-product-expert` | obligatory | Product fit and requirements (weight 1.0×) |
  | `openspec-security-privacy` | obligatory | Security and privacy (weight 1.3×) |
  | `openspec-sre-reliability` | obligatory | Reliability, observability, ops (weight 1.0×) |
  | `openspec-data-architect` | obligatory | Data model and schema (weight 1.0×) |
  | `openspec-qa-test` | obligatory | Testability and QA (weight 1.0×) |
  | `openspec-spec-editor` | obligatory | Spec-writing quality (weight 0.7×) |
  | `openspec-adversarial-reviewer` | obligatory | Adversarial steel-manning (weight 1.5×) |
  | `openspec-counsel-compliance` | opt-in | Legal/compliance review (weight 0.8×) |
  | `openspec-ux-frontend` | opt-in | UX and frontend review (weight 0.8×) |
- **Severity** `severity` (`'critical' | 'major' | 'minor' | 'info'`).
- **Location** `file_path text`, `line_number integer`.
- **Body** `body text NOT NULL` (rendered through `<Markdown>`).
- **State** `state` (`'open' | 'acknowledged' | 'addressing' | 'addressed' | 'suppressed'`), `state_changed_at`.
- Indexes: `(step_id, severity)`; partial `(run_id, severity)` filtered to `state = 'open'`.

### `forge_outputs`

Append-only artifact log: PR URLs, report paths, diffs, files, log refs.

- **PK** `id` `uuid`
- **FK** `run_id → forge_runs.id ON DELETE CASCADE`
- **Kind** `kind` (`'pr' | 'report' | 'diff' | 'file' | 'log'`).
- **Pointer** `url text` (PR URL, etc.), `metadata jsonb` (kind-specific — e.g. for `'pr'`: `{ number }`).
- Index: `(run_id, created_at desc)`.

### RLS

All four tables enable RLS with a single permissive policy chain:

```sql
-- forge_runs: direct platform_owner check from JWT
create policy "forge_runs: platform_owner all"
  on public.forge_runs
  for all to authenticated
  using   ((((auth.jwt() -> 'app_metadata') ->> 'platform_owner'))::boolean is true)
  with check ((((auth.jwt() -> 'app_metadata') ->> 'platform_owner'))::boolean is true);

-- forge_steps / forge_findings / forge_outputs: chain through run_id EXISTS check
create policy "<table>: via run platform_owner"
  on public.<table>
  for all to authenticated
  using (exists (
    select 1 from public.forge_runs r
    where r.id = <table>.run_id
      and (((auth.jwt() -> 'app_metadata') ->> 'platform_owner'))::boolean is true
  ))
  with check (...);
```

Multi-user policies land in `add-forge-multi-user` if/when needed.

### Cross-references

- Persisted entity catalog — `entities/persisted.md § Forge entities`.
- UI surface that drives writes — `forge/entities.md § UI surface`, `studio-workspace/spec.md § Task page`.

---

## Canvas lifecycle stage columns (applied 2026-05-08 from change `add-canvas-stage-primitive`)

`canvas_sessions` carries a deterministic stage and aggregate completeness percent, plus a sign-off audit pair for the terminal `build_ready` stage. A new `canvas_stage_events` table holds the append-only audit feed and powers Supabase Realtime subscriptions for the future progress view.

### `canvas_sessions` — added columns

```sql
alter table canvas_sessions
  add column stage              text         not null default 'intake'
    check (stage in ('intake','discovery','concept','design','tasks','build_ready')),
  add column stage_percent      integer      not null default 0
    check (stage_percent between 0 and 100),
  add column stage_overrides    jsonb        not null default '{}'::jsonb,
  add column signoff_at         timestamptz,
  add column signoff_by_user_id uuid         references auth.users(id) on delete set null,
  add constraint canvas_sessions_signoff_paired_check
    check ((signoff_at is null) = (signoff_by_user_id is null));
```

- `stage` — current lifecycle position. Six-value enum mirroring `studio-workspace/spec.md § Canvas lifecycle stages § 6 stages`. Computed by `recomputeStageAction`; orthogonal to `status` and `mode`.
- `stage_percent` — aggregate completeness across all stages (sum of weights of passing checks per `openspec/_methodology/lifecycle.yaml`). The `stage` column is the unique stage whose `[start, end)` range contains this percent.
- `stage_overrides` — append-only JSONB audit of manual stage overrides (`{ who, when, why, prev_stage, next_stage }` entries). Empty object when the canvas has only ever transitioned via deterministic recompute. Phase 6 sign-off flow writes here on force-promote.
- `signoff_at` / `signoff_by_user_id` — set together when stage 5 (`build_ready`) is acknowledged. CHECK constraint enforces both NULL or both NOT NULL.

Backfill is deterministic: existing rows keep the `('intake', 0)` defaults until `tools/backfall-canvas-stages.ts` walks the table or the next mutation hits an auto-recompute hook. Safe to re-run.

### `canvas_stage_events` — new table

Append-only audit feed of stage / percent transitions. One row per `recomputeStageAction` call where `stage` OR `stage_percent` changed since the prior persisted value.

```sql
create table canvas_stage_events (
  id                    uuid          primary key default gen_random_uuid(),
  canvas_id             uuid          not null references canvas_sessions(id) on delete cascade,
  prev_stage            text          not null
    check (prev_stage in ('intake','discovery','concept','design','tasks','build_ready')),
  next_stage            text          not null
    check (next_stage in ('intake','discovery','concept','design','tasks','build_ready')),
  prev_percent          integer       not null check (prev_percent between 0 and 100),
  next_percent          integer       not null check (next_percent between 0 and 100),
  triggered_by_user_id  uuid          references auth.users(id) on delete set null,
  triggered_by_action   text          not null
    check (length(triggered_by_action) between 1 and 64),
  reason                text
    check (reason is null or length(reason) between 1 and 1000),
  created_at            timestamptz   not null default now()
);

create index canvas_stage_events_canvas_id_created_at_desc_idx
  on canvas_stage_events(canvas_id, created_at desc);
```

- `triggered_by_action` — short string identifying the upstream caller. Wave 1 vocabulary: `send_message | save_artifact | save_file | convert_to_project | add_project_instruction | mirror_write | backfill | manual_override`. Free-text rather than enum so new auto-recompute hooks can ship without a migration.
- `reason` — optional rationale, populated when `triggered_by_action = 'manual_override'`. NULL for deterministic recomputes.

Regression-allowed: `next_stage < prev_stage` is a valid event (e.g. deleting a vocabulary entry drops the canvas from 32% → 28%). Phase 2 progress view surfaces regressions as warnings.

### RLS

`canvas_stage_events` enables RLS and chains through `canvas_sessions` for viewer authorization — same scope as `canvas_sessions` itself: owner OR accepted invitee.

```sql
alter table canvas_stage_events enable row level security;

create policy "canvas_stage_events: owner or accepted invitee via canvas"
  on canvas_stage_events
  for all to authenticated
  using (
    exists (
      select 1 from canvas_sessions cs
      where cs.id = canvas_stage_events.canvas_id
        and (
          cs.user_id = auth.uid()
          or exists (
            select 1 from canvas_invitees ci
            where ci.canvas_id = cs.id
              and ci.invitee_user_id = auth.uid()
              and ci.accepted_at is not null
          )
        )
    )
  )
  with check (
    exists (
      select 1 from canvas_sessions cs
      where cs.id = canvas_stage_events.canvas_id
        and cs.user_id = auth.uid()
    )
  );
```

`WITH CHECK` is owner-only intentionally: only the canvas owner (or service role bypassing RLS) writes events. Invitees see events via `USING` but never insert directly — the recompute action runs server-side as the canvas owner's session.

### Cross-references

- Computation engine + auto-recompute hooks — `studio-workspace/spec.md § Canvas lifecycle stages`.
- Authoritative lifecycle config — `openspec/_methodology/lifecycle.yaml`.
- Migration — `supabase/migrations/20260508040831_add_canvas_stage.sql`.

---

## Project Connections (applied 2026-05-08 from change `add-project-connections`)

Replaces the bespoke `projects.{github_repo,github_repo_url,linear_team_id,linear_project_id}` columns (added by `add-projects-table`, dropped 2026-05-09 by `drop-deprecated-projects-binding-columns`) with a single registry-driven `project_connections` table. Each row binds a project to one external service (`kind` ∈ `('github','linear')` in v1) with kind-specific config + cached metadata. Adding a new external service in v1.x = one TS handler in `packages/connections/src/kinds/<slug>.tsx` + one slug in the DB CHECK + one folder in `openspec/connections/<slug>/`.

### Schema

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` PK | `gen_random_uuid()` |
| `project_id` | `uuid NOT NULL` | FK → `projects(id) ON DELETE CASCADE` |
| `kind` | `text NOT NULL` | CHECK in `('github','linear')` — extends as new kinds register |
| `config` | `jsonb NOT NULL` | Kind-specific canonical config. GitHub: `{ repo, repoUrl }`. Linear: `{ teamId, teamKey }`. Always carries the resolved shape (Linear key→UUID resolution lands here, not on disk verbatim from user input). |
| `metadata` | `jsonb NULL` | Kind-specific live projection (description / language / member count etc.). Populated by `loadMetadata` on save / verify; nullable when verify gracefully fails. Render path tolerates null. |
| `status` | `text NOT NULL DEFAULT 'active'` | CHECK in `('active','disabled')`. Disabled rows hide from UI but stay queryable for history. |
| `last_verified_at` | `timestamptz NULL` | Stamp from `verify` success. Phase 9 smoke uses this for "stale" detection. |
| `metadata_fetched_at` | `timestamptz NULL` | Stamp from `loadMetadata` success — separate from verify so a `null` metadata path (graceful fail) doesn't reset the verify timestamp. |
| `created_at` | `timestamptz NOT NULL DEFAULT now()` | |
| `updated_at` | `timestamptz NOT NULL DEFAULT now()` | |

Unique index: `project_connections_project_kind_idx ON (project_id, kind)` — one row per (project, kind). The save action upserts on this constraint.

### RLS

Enabled. Single permissive `ALL` policy chained through `projects.owner_id`:

```sql
create policy project_connections_owner_or_collaborator
  on project_connections for all
  using (
    exists (
      select 1 from projects p
      where p.id = project_connections.project_id
        and (
          auth.uid() = p.owner_id
          or exists (
            select 1
              from canvas_sessions cs
              join canvas_invitees ci on ci.canvas_id = cs.id
             where cs.project_id = p.id
               and ci.invitee_user_id = auth.uid()
               and ci.accepted_at is not null
               and ci.role = 'collaborator'
          )
        )
    )
  )
  with check (
    exists (
      select 1 from projects p
      where p.id = project_connections.project_id
        and auth.uid() = p.owner_id
    )
  );
```

`USING` mirrors the projects-table read pattern (owner + collaborator-via-canvas; client invitees excluded). `WITH CHECK` is owner-only — only the project owner inserts / updates / deletes connection rows. Collaborators see them on the project page but cannot mutate.

### Write surface

`apps/studio/src/app/actions/project-connections.ts` (RAP-139, Phase 5). Four actions, all kind-agnostic — they look up `PROJECT_CONNECTION_KINDS[kind]` and dispatch:

- `listConnectionsForProject(projectId)` — RLS-gated read.
- `saveConnectionAction(projectId, kind, rawInput)` — `parseInput` → `requireOwner` → `loadCredential` → `verify` → `loadMetadata` → upsert. Stores `verified.normalizedConfig ?? parsed.config` so Linear's key→UUID resolution lands in the DB.
- `removeConnectionAction(projectId, kind)` — DELETE with `count: 'exact'`.
- `verifyConnectionAction(projectId, kind)` — re-verify + refresh `last_verified_at` / `metadata_fetched_at`.

Action bodies have **zero per-kind branches**. Errors mapped exhaustively: `kind_unknown`, `parse_failed`, `credential_unavailable`, `auth_required`, `ownership_required`, `db_error`, `not_found`, plus all `VerifyError` reasons (`auth_failed` / `no_access` / `rate_limited` / `network_error` / `invalid_config`).

Credentials route through `packages/connections/src/server-bridge/load-credential.ts`'s `EnvReader` adapter — the package never imports `@opennextjs/cloudflare` directly; the action wraps `lib/forge/identity.ts`'s readers (`readGitHubBotToken` / `readLinearBotApiKey`).

### Read paths beyond the action

Four subsystems read `project_connections.config` directly:

- `apps/studio/src/app/(auth-walled)/(canvas)/spec/[id]/file-lifecycle-actions.ts` — `resolveRepoFullName` queries `(project_id, kind=github, status=active)` and reads `config.repo` for canvas → repo conversion.
- `apps/studio/src/lib/forge/run-pr.ts` — `resolveTargetRepo` queries the same shape for forge PR target resolution.
- `apps/studio/src/app/(auth-walled)/(default)/projects/loaders.ts` — `loadConnectionKindsForProjects(supabase, ids[])` batches a single `(project_id, kind)` SELECT for the `/projects` list page's connection-presence meta-line.
- `apps/studio/src/lib/control/engagement-loader.ts` — `loadEngagementOverview`'s helper `loadBindingsByProjectIds` batches `(project_id, kind ∈ {github,linear}, status=active)` and reads `config.repo` / `config.teamId` for the control-room dashboard's per-engagement pills.

### Backfill

The migration's `INSERT ... SELECT ... ON CONFLICT (project_id, kind) DO NOTHING` statements were no-ops at apply time (zero rows in `projects` had non-null `github_repo` / `linear_team_id`). The clauses stay in place so re-running the migration on a populated database (rollforward scenario) lands the rows correctly.

### Migration

`supabase/migrations/20260507111500_add_project_connections.sql` (per `add-project-connections/tasks.md § Phase 1.2`).

### Catalog cross-reference

Per-kind specs live at:

- `openspec/connections/github/spec.md`
- `openspec/connections/linear/spec.md`

These mirror the in-code Builder Form (`packages/connections/src/types.ts → ProjectConnectionKind`) on the human-readable side.

### Cross-references

- Ports + design: `openspec/archive/<date>-add-project-connections/`
- Originating change being superseded: `openspec/archive/2026-05-05-add-project-bindings/`
- Sister change (user OAuth / actor-bound credentials): RAP-112
- Forge transport boundary (D-PC-13): `forge/entities.md § GitHub API integration` + `§ Linear API integration`

---

## `canvas_sessions.wizard_state` column (applied 2026-05-08 from change `add-canvas-discovery-wizard`)

Reserved at the 2026-05-05 reset/squash migration; populated by the discovery wizard panel that ships in `add-canvas-discovery-wizard` (Track B of MVP v1).

```sql
wizard_state jsonb NOT NULL DEFAULT '{}'::jsonb
```

### Shape

Authoritative schema lives in `packages/canvas/src/wizard/schema.ts` as `WizardStateSchema` (`.strict()`). v1 unversioned shape:

```jsonc
{
  "problem_statement": "string (≤ 500 chars; default '')",
  "proposed_solution": "string (≤ 500 chars; default '')",
  "target_audience":   "string (≤ 200 chars; default '')",
  "industry":          "enum (11 values incl. 'unspecified')",
  "timeline":          "enum (5 ranges incl. 'unspecified')",
  "budget":            "enum (5 ranges incl. 'unspecified')"
}
```

A row never touched by the wizard parses cleanly into all-default values via the `parseWizardState` helper. The schema rejects unknown top-level keys; future shape changes ship via a follow-up migration that owns the rewrite.

### Writers

`apps/studio/src/app/(auth-walled)/(canvas)/spec/[id]/wizard-actions.ts` — three server actions:

- `saveWizardField(canvasId, field, value)` — partial update via read-merge-write loop. Per-field Zod-validated.
- `clearWizardField(canvasId, field)` — resets to Zod default.
- `loadWizardState(canvasId)` — read helper; never returns null.

RLS reuses the existing `canvas_sessions` owner + accepted-invitee policies; the wizard does not introduce new policies. Concurrent same-field saves are last-write-wins; concurrent different-field saves don't clobber each other in v1's single-Pavel scenario.

### Readers

- The studio `<DiscoveryWizard>` panel — renders the form + handles autosave.
- The lifecycle stage engine — `evalWizardFieldMinChars` reads two stage-1 (`discovery`) signals (`problem_statement` ≥ 40 chars + `target_audience` ≥ 5 chars) per `lifecycle.yaml § stages.discovery`.
- The Muse turn route — `tryLoadWizardPreamble` filters via `filledWizardFields` from `@rapoport-studio/canvas/wizard` and threads the result through `runMuseTurnStream → buildSystemPrompt` so every Muse turn carries a `# Discovery wizard state` preamble.

### Out of scope

- **Versioned shape** — v1 unversioned. Premature versioning is its own form of debt; future change owns the migration.
- **Multi-user real-time collaboration** — last-write-wins per field is acceptable in single-user v1.
- **Auto-fill from chat history** — manual entry only.

### Cross-references

- Wizard surface contract — `studio-workspace/spec.md § Discovery wizard`.
- Muse system-prompt block — `muse.md § Discovery wizard preamble`.
- Lifecycle integration — `openspec/_methodology/lifecycle.yaml § stages.discovery` + `studio-workspace/spec.md § Canvas lifecycle stages`.
- Schema source — `packages/canvas/src/wizard/schema.ts`.

---

## TG canvas surface schema (applied 2026-05-09 from change `add-tg-canvas-surface`)

Added by `add-tg-canvas-surface` (RAP-121). Capability: `telegram.md`.

### New tables

#### canvas_daily_costs (D-TCS-7, R2 reconciliation)

Per-canvas-per-day cost ledger. Coexists with `canvas_sessions.cost_cents`
(cumulative-lifetime, untouched).

| Column                | Type        | Notes                                |
|-----------------------|-------------|--------------------------------------|
| canvas_id             | uuid        | FK canvas_sessions(id) ON DELETE CASCADE |
| date                  | date        | UTC                                  |
| cents_spent           | integer     | Main token cost                      |
| classification_cents  | integer     | Haiku classification turn cost (separate audit lane) |
| tokens_in             | bigint      |                                      |
| tokens_out            | bigint      |                                      |
| audio_seconds         | integer     | Cumulative voice processed via Whisper |

PK: `(canvas_id, date)`.

Atomic dual-write via Postgres function `record_canvas_cost(canvas_id, delta_cents, usage)`:
updates both `canvas_sessions.cost_cents` (lifetime) and `canvas_daily_costs.cents_spent` (daily)
in one transaction.

#### canvas_proposals (D-TCS-4, D-TCS-8)

Per-canvas propose-mode mutation queue. Worker writes; owner resolves; service role executes.

| Column                       | Type                  | Notes                |
|------------------------------|-----------------------|----------------------|
| id                           | uuid PK               |                      |
| canvas_id                    | uuid                  | FK canvas_sessions   |
| kind                         | proposal_kind enum    | mirror_write \| github_open_pr \| ... \| architect_emit |
| payload                      | jsonb                 | exact tool input the LLM intended |
| source_tool_name             | text                  | for audit            |
| proposed_by_tg_user_id       | bigint                | speaker who triggered the turn |
| proposed_at                  | timestamptz           |                      |
| resolved_at                  | timestamptz nullable  |                      |
| resolution                   | proposal_resolution   | pending \| approved \| rejected \| expired \| executed \| failed |
| resolved_by_studio_user_id   | uuid nullable         | FK auth.users        |
| execution_result             | jsonb nullable        | linear issue id, PR number, etc. on approve+execute |

RLS: owner + invitees read; only owner resolves.

#### tg_user_bindings (D-TCS-6)

| Column           | Type       | Notes                              |
|------------------|------------|------------------------------------|
| tg_user_id       | bigint PK  | Telegram numeric user_id           |
| studio_user_id   | uuid       | FK auth.users(id) ON DELETE CASCADE |
| bound_at         | timestamptz |                                   |
| bound_via_token  | uuid nullable | FK tg_invite_tokens(token)      |

#### tg_invite_tokens (D-TCS-6)

| Column              | Type           | Notes                            |
|---------------------|----------------|----------------------------------|
| token               | uuid PK        | jti claim of JWT                 |
| canvas_id           | uuid           | FK canvas_sessions ON DELETE CASCADE |
| created_by_user_id  | uuid           | FK auth.users                    |
| expires_at          | timestamptz    | typically now() + 24h            |
| used_at             | timestamptz nullable |                            |
| used_by_tg_user_id  | bigint nullable |                                 |

Token shape: HS256 JWT signed with `JWT_INVITE_SIGNING_KEY` from Infisical.
Payload: `{ jti, canvas_id, studio_user_id, exp, iat, iss: "rapoport-studio", aud: "tg-canvas-bind" }`.

### Modified tables

#### channels — `bound_canvas_id`

```sql
ALTER TABLE channels
  ADD COLUMN bound_canvas_id uuid REFERENCES canvas_sessions(id);

CREATE UNIQUE INDEX channels_bound_canvas_unique
  ON channels(bound_canvas_id) WHERE bound_canvas_id IS NOT NULL;
```

One canvas = one TG group (channel). Existing channels schema (RAP-95) reused.

#### canvas_sessions — `daily_cap_cents`

```sql
ALTER TABLE canvas_sessions
  ADD COLUMN daily_cap_cents integer NOT NULL DEFAULT 300;
```

R5: $3 default sourced from Pavel handoff prompt 2026-05-07. Owner overrides per canvas via UI (P5).

#### projects — `tg_surface_enabled`

```sql
ALTER TABLE projects
  ADD COLUMN tg_surface_enabled boolean NOT NULL DEFAULT false;
```

D-TCS-11 + G1: stored on `projects` table since `engagements` table doesn't exist. Worker
reads `canvas_sessions.project_id → projects.tg_surface_enabled` at webhook entry. Methodology
charter (`rapoport-studio-engagement.md § 8 Appendix B → D11`) records the same value
as human-readable opt-in.

### Garbage-collection

Daily Postgres job deletes `tg_message_id` dedup rows >30 days old (per
`telegram.md § Privacy boundaries` security threat model). Prevents unbounded growth from
chatty canvas groups while keeping replay-protection window honest.

---

## Linear OAuth connections (applied 2026-05-10 from change `add-linear-oauth-connection`)

Per-user Linear OAuth binding lives in `public.linear_oauth_connections`:

| Column | Type | Notes |
|---|---|---|
| `user_id` | `uuid PK FK auth.users` | One connection per user |
| `linear_user_id`, `linear_user_name`, `linear_user_email`, `linear_workspace_name` | `text` | From Linear `viewer` query at callback time |
| `access_token_ciphertext`, `access_token_iv` | `bytea` | AES-256-GCM, key in `LINEAR_OAUTH_ENCRYPTION_KEY` (Cloudflare secret) |
| `scope` | `text` | `read,write,issues:create,comments:create` |
| `created_at`, `updated_at` | `timestamptz` | `set_updated_at()` trigger |

RLS: a user reads/deletes their own row only; service-role client (callback handler) can upsert any row. No cross-user reads — verified by synthetic Phase 4 test in `archive/2026-05-10-add-linear-oauth-connection/design.md § 5`.

Encryption-at-rest pattern: `apps/studio/src/lib/oauth-token-crypto.ts` reads the key via `getCloudflareContext()`, memoises the `CryptoKey`, exposes `encryptToken()` / `decryptToken()`. Auth-tag tampering throws on decrypt.

Authoritative source: `archive/2026-05-10-add-linear-oauth-connection/design.md §§ 3–4`.

---

## connection_events — audit log (applied 2026-05-13 from change `add-connection-audit-and-cleanup`)

Append-only audit log for all lifecycle events on `project_connections` rows. Closes gap G-07. Migration: `supabase/migrations/20260513000001_create_connection_events.sql`.

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` (PK) | `gen_random_uuid()` |
| `connection_id` | `uuid` nullable | Soft FK to `project_connections.id` — **no** `ON DELETE CASCADE`. History survives after the connection row is deleted. |
| `connection_kind` | `text` NOT NULL | Denormalised kind slug (e.g. `linear`, `github`). |
| `project_id` | `uuid` NOT NULL | FK `projects.id` `ON DELETE RESTRICT`. Hard anchor so history cannot outlive the project. |
| `actor_id` | `uuid` nullable | FK `auth.users.id`. NULL for cron jobs and system-initiated events. |
| `action` | `text` NOT NULL | CHECK in: `created`, `verified`, `metadata_loaded`, `token_refreshed`, `health_check_passed`, `health_check_failed`, `used`, `reconnected`, `disconnected`, `cleanup_succeeded`, `cleanup_failed`. |
| `payload` | `jsonb` NOT NULL | Structured context. Shape is action-specific. PII (token IDs, repo names) acceptable in v1. |
| `created_at` | `timestamptz` NOT NULL | `default now()` |

Indexes: `(project_id, created_at DESC)`, `(connection_id, created_at DESC)`, `(action, created_at DESC)`.

RLS: project owner reads own events (SELECT only); service-role client writes (no INSERT policy for authenticated users). No DELETE policy — append-only. Retention policy: keep forever in v1; revisit at scale.

Writers: `recordConnectionEvent()` in `apps/studio/src/lib/connection-audit.ts` — uses service-role admin client, never throws.

Authoritative source: `archive/<date>-add-connection-audit-and-cleanup/design.md §§ 1–2`.

---

## Mirror audience tagging — content-level, not RLS-level (applied 2026-05-13 from change `add-document-zone-permissions`)

Audience tags on Mirror content (`audience: [...]` fields on `canvas_files` YAML frontmatter items, `section_audiences` map on body sections) operate **above** the RLS layer, not as a replacement for it.

- **RLS** controls whether a `canvas_files` row is readable at all for a given `auth.uid()`. A `client`-role invitee is already hard-walled from all `canvas_files` rows by the `canvas_invitees.role <> 'client'` branch in the `canvas_files` SELECT policy. This is unchanged.
- **Audience tags** control which sub-content within an already-readable file is surfaced to a given viewer role. They apply at the application layer (loader projection via `loadMirrorForRole`) and at render time (Claude API input projection via `loadMirrorForRender`). They have no effect on the DB query itself.

The implication: if a `collaborator` can read a Mirror file via RLS, audience tags further filter which fields within that file the collaborator sees (e.g. sections tagged `['internal']` are hidden from collaborator projection). An item tagged `['owner', 'co_owner']` never appears in a collaborator's `MirrorView` even though the collaborator can read the row.

This is intentional: audience tags express **editorial sensitivity** (what's appropriate to show whom), while RLS expresses **access control** (who can open the file at all). Both layers must be satisfied for content to reach a viewer. Authoritative source: `archive/2026-05-13-add-document-zone-permissions/design.md § 3.3`.

---

## Specialist tables (applied 2026-05-13 from change `add-specialist-onboarding`)

Two new tables implement the specialist intake + identity model. DB migrations are ⚠️ **operator-blocked** (pending Supabase project access); TypeScript type stubs are live in `packages/db/src/types.ts`.

### `public.specialist_applications`

Stores intake form submissions. Storage option D-SO-2 option a — separate table, clean separation from client `inbox`.

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` PK | `gen_random_uuid()` |
| `email` | `text` NOT NULL | Magic-link target; applicant identity |
| `full_name` | `text` NOT NULL | Display name |
| `disciplines` | `text[]` NOT NULL | Multi-select from open list per `specialist-network.md § 1` |
| `years_in_discipline` | `int` NOT NULL | Self-reported |
| `domains_of_interest` | `text[]` NOT NULL | Industries, technologies, problem spaces |
| `current_role_or_context` | `text` NOT NULL | One-paragraph free text |
| `why_studio` | `text` NOT NULL | Most important review field; three-paragraph free text |
| `worked_with_specs_before` | `text` NOT NULL | CHECK in: `daily`, `sometimes`, `rarely`, `never` |
| `comfort_with_ai_tools` | `text` NOT NULL | CHECK in: `daily`, `sometimes`, `rarely`, `never` |
| `availability_window` | `text` NOT NULL | CHECK in: `actively-looking`, `open-to-conversations`, `curious-only` |
| `language_preferences` | `text[]` NOT NULL | `en` / `ru` / `ro` / `he` / other |
| `portfolio_or_evidence_url` | `text` nullable | Personal site, GitHub, published work |
| `referred_by` | `text` nullable | Pre-filled from `?ref=` querystring |
| `additional_notes` | `text` nullable | Free text |
| `status` | `text` NOT NULL DEFAULT `'new'` | State machine: `new → reviewing → shadowing / declined / proceed_to_nda → nda_pending → nda_signed / nda_declined → training_in_progress → certified_reviewer / certified_author → active` |
| `reviewed_at` | `timestamptz` nullable | Stamped on first status transition by reviewer |
| `reviewed_by` | `uuid` nullable | FK `auth.users.id` |
| `decision_at` | `timestamptz` nullable | Stamped when a terminal state is reached |
| `created_at` | `timestamptz` NOT NULL DEFAULT `now()` | |
| `updated_at` | `timestamptz` NOT NULL | `set_updated_at()` trigger |

RLS: applicant reads/writes own row; `platform_owner` reads all rows; no public read.

### `public.specialists`

Identity extension for onboarded specialists. A `community_member` gains a row here after `nda_signed` → training → certification.

| Column | Type | Notes |
|---|---|---|
| `id` | `uuid` PK | FK `auth.users.id` |
| `application_id` | `uuid` nullable | FK `specialist_applications.id`; nullable for backfilled records |
| `nda_status` | `text` NOT NULL | CHECK in: `none`, `pending`, `signed`, `expired`, `revoked` |
| `nda_signed_at` | `timestamptz` nullable | |
| `nda_template_version` | `text` nullable | |
| `certification_level` | `text` NOT NULL DEFAULT `'apprentice'` | CHECK in: `apprentice`, `reviewer`, `author`, `senior` |
| `certified_at` | `timestamptz` nullable | |
| `disciplines` | `text[]` NOT NULL | Editable by specialist |
| `domains_of_interest` | `text[]` NOT NULL | Editable by specialist |
| `availability` | `text` NOT NULL | CHECK in: `actively-looking`, `open`, `curious-only`, `inactive` |
| `prompt_augmentations_uri` | `text` nullable | Relative path under `packages/muse/specialists/<slug>/` |
| `language_preferences` | `text[]` NOT NULL | |
| `created_at` | `timestamptz` NOT NULL DEFAULT `now()` | |
| `updated_at` | `timestamptz` NOT NULL | `set_updated_at()` trigger |

RLS: specialist reads/writes own row; `platform_owner` reads all rows; no public read.

---

## Project members + role hierarchy (applied 2026-05-15 from change `redesign-canvas-permission-model`)

Project-level membership replaces per-canvas invitations as the primary canvas-access mechanism. Two new tables (`project_members`, `canvas_excluded_members`) plus extensions to `canvas_invitees` ship with Phase 1–3 migrations.

### `public.project_members`

| Column | Type | Notes |
|---|---|---|
| `project_id` | `uuid` NOT NULL FK `projects.id` ON DELETE CASCADE | |
| `user_id` | `uuid` NOT NULL FK `auth.users.id` ON DELETE CASCADE | |
| `role` | `text` NOT NULL | CHECK in `('owner','co_owner','collaborator','external_collaborator','viewer','client')` |
| `invited_by` | `uuid` nullable FK `auth.users.id` ON DELETE SET NULL | NULL for seeded owner rows |
| `invited_at` | `timestamptz` NOT NULL DEFAULT `now()` | |
| `accepted_at` | `timestamptz` nullable | NULL until magic-link accepted; stamped by dispatch helper |
| `expires_at` | `timestamptz` nullable | NULL means never. Reminder surfaces 7-day warning on members page |
| `revoked_at` | `timestamptz` nullable | Soft-delete; rows never hard-deleted (audit trail) |
| `created_at` | `timestamptz` NOT NULL DEFAULT `now()` | |
| `updated_at` | `timestamptz` NOT NULL | `set_updated_at()` BEFORE-UPDATE trigger via `extensions.moddatetime` |

**PRIMARY KEY** `(project_id, user_id)` — one row per user per project. Repeat invites UPSERT.

**Constraints:**
- `project_members_role_check` — `role IN ('owner','co_owner','collaborator','external_collaborator','viewer','client')`
- `project_members_revoked_paired_check` — `(revoked_at IS NULL) OR (revoked_at >= invited_at)`

**Indexes:** `project_members_user_id_idx` on `(user_id)`; `project_members_project_id_role_idx` on `(project_id, role)`; `project_members_active_idx` on `(project_id, user_id) WHERE accepted_at IS NOT NULL AND revoked_at IS NULL` (partial, covers proxy hot-path lookup).

**Seeded rows:** owner row at project creation time carries `invited_by = NULL`, `accepted_at = invited_at`, `expires_at = NULL`. Agent uses `role='owner'`.

**Migration:** `supabase/migrations/20260515120000_add_project_members.sql` — applied to live project `nifagnmgwoqkplegsicy` on 2026-05-15.

### `public.canvas_excluded_members`

Per-canvas exclusion overrides for project members. Conditional on P0.2 outcome — P0.2 resolved Option A (default-cascade with exclusion), so this table is created.

| Column | Type | Notes |
|---|---|---|
| `canvas_id` | `uuid` NOT NULL FK `canvas_sessions.id` ON DELETE CASCADE | |
| `user_id` | `uuid` NOT NULL FK `auth.users.id` ON DELETE CASCADE | |
| `excluded_by` | `uuid` NOT NULL FK `auth.users.id` ON DELETE SET NULL | |
| `excluded_at` | `timestamptz` NOT NULL DEFAULT `now()` | |
| `reason` | `text` nullable | CHECK `length BETWEEN 1 AND 500` when non-null |

**PRIMARY KEY** `(canvas_id, user_id)`. Index `canvas_excluded_members_user_id_idx` on `(user_id)`.

**RLS:** `owner` and `co_owner` may INSERT/DELETE exclusion rows for their project's canvases; excluded user may SELECT their own row.

**Migration:** `supabase/migrations/20260515120001_add_canvas_excluded_members.sql` — applied on 2026-05-15.

### `canvas_invitees` extensions

`canvas_invitees.role` CHECK extended from `('collaborator','client')` to the full six-role enum matching `project_members.role`. New `expires_at timestamptz` column added (NULL means never).

**Migration:** `supabase/migrations/20260515120002_extend_canvas_invitees_role.sql` — applied on 2026-05-15.

### Role × operation matrix (definitive)

Enforced at: DB RLS (source of truth), server action permission check (`requireProjectRole`), Studio UI gate, `canUser(role, action, entity)` helper at `packages/db/src/permission/check.ts`.

| Operation | platform_owner | owner | co_owner | collaborator | external_collaborator | viewer | client |
|---|---|---|---|---|---|---|---|
| Read project | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| Archive project | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ |
| Transfer ownership | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ |
| Manage `project_connections` (CRUD) | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ |
| Invite `co_owner` | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ | ✗ |
| Invite `collaborator` / `viewer` / `client` | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ |
| Invite `external_collaborator` | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ |
| Revoke any membership | ✓ | ✓ | ✓ (except owner) | ✗ | ✗ | ✗ | ✗ |
| Add / remove `canvas_excluded_members` | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ |
| Read OpenSpec files (`openspec/changes/**`, `openspec/current/**`, `_methodology/**`) | ✓ | ✓ | ✓ | ✓ | ✗ (RLS hard-wall) | ✓ | ✗ |
| Write OpenSpec files | ✓ | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ |
| Read Mirror (`openspec/mirror/**`) | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
| Write Mirror (`write_mirror_field`) | ✓ | ✓ | ✓ | ✓ | ✓ (P0.3 default A) | ✗ | ✗ |
| Send chat message | ✓ | ✓ | ✓ | ✓ | ✓ | ✗ | ✓ |
| Sign-off on stage 5 (`build_ready`) | ✓ | ✓ | ✓ | ✗ | ✗ | ✗ | ✗ |

`viewer` is read-everywhere-write-nowhere. `external_collaborator` is `collaborator` minus OpenSpec read minus invitation rights. `client` is chat + Mirror only and stays per-canvas-invited (not project-cascading).

### RLS pattern — three-branch policy

All canvas-scoped tables (`canvas_sessions`, `canvas_messages`, `canvas_files`, `canvas_project_instructions`, `canvas_proposals`, `canvas_stage_events`, `canvas_daily_costs`) share this shape:

```sql
USING (
  -- Branch 1: platform_owner short-circuit (no DB roundtrip on hot path)
  (((auth.jwt() -> 'app_metadata') ->> 'platform_owner'))::boolean IS TRUE
  OR
  -- Branch 2: project member (new primary path)
  EXISTS (
    SELECT 1
    FROM public.project_members pm
    JOIN public.canvas_sessions cs ON cs.project_id = pm.project_id
    WHERE cs.id = <table>.canvas_id
      AND pm.user_id    = auth.uid()
      AND pm.accepted_at IS NOT NULL
      AND pm.revoked_at  IS NULL
      AND (pm.expires_at IS NULL OR pm.expires_at > now())
      AND pm.role IN (<roles allowed>)
      AND NOT EXISTS (
        SELECT 1 FROM public.canvas_excluded_members cem
        WHERE cem.canvas_id = cs.id AND cem.user_id = auth.uid()
      )
  )
  OR
  -- Branch 3: direct canvas invite (preserved legacy path)
  EXISTS (
    SELECT 1 FROM public.canvas_invitees ci
    WHERE ci.canvas_id       = <table>.canvas_id
      AND ci.invitee_user_id = auth.uid()
      AND ci.accepted_at IS NOT NULL
      AND ci.revoked_at  IS NULL
      AND (ci.expires_at IS NULL OR ci.expires_at > now())
      AND ci.role IN (<roles allowed>)
  )
)
```

`canvas_files` has two separate policies — one for `openspec/mirror/%` paths (all six roles read, four roles write) and one for `openspec/changes/%`, `openspec/current/%`, `_methodology/%` paths (excludes `external_collaborator` from read).

Migrations: `20260515130000_rls_chain_through_project_members.sql`, `20260515130001_project_members_self_policies.sql`, `20260515130002_canvas_excluded_members_policies.sql` — all applied on 2026-05-15.

### Backfill (Phase 3)

`supabase/migrations/20260515140000_backfill_project_members.sql` — applied on 2026-05-15. Seeded owner rows for each project row and promoted project-bound `canvas_invitees` rows to `project_members`. Result: 2 owner rows + 1 promoted invitee = 3 `project_members` rows total. Two orphan-canvas invitees (no `project_id`) remain in `canvas_invitees` for Branch 3 RLS.

### Cutover criterion

**Resolved 2026-05-22 by RAP-1170 Wave 1.** The cutover became moot rather than satisfied: `supabase/migrations/20260522120000_rearchitect_spec_document_model.sql` dropped the entire `canvas_*` table family, including `canvas_invitees`. Branch 3 RLS no longer references a real table — the post-RAP-1170 schema uses `spec_invitees` as the per-spec access table (see § Project → spec → idea hierarchy row for `spec_invitees`). The two orphan-canvas-invitee rows that previously kept Branch 3 alive were dropped with the table; no project access was lost because they were orphans by definition (no `project_id`, no `project_members` peer).

The legacy preamble at the top of this file (line 18) declares the whole legacy-`canvas_*` section deprecated 2026-05-22; this paragraph extends that ruling to the specific Branch 3 RLS cutover claim. The original criterion — "when all canvas_sessions older than 7 days with `project_id IS NOT NULL` have at least one matching `project_members` row" — is retained below for archival reference only; do not treat it as actionable.

The ESLint warn rule `no-direct-canvas-invitees-write` (at `tools/eslint-rules/no-direct-canvas-invitees-write.js`) can be retired in a follow-up cleanup PR — the table it warns about no longer exists, so its findings are now structurally false-positive. Tracked as `Q-RLS-B3-1`.

> **Original criterion (archival):** `canvas_invitees` Branch 3 RLS may be removed when all `canvas_sessions` older than 7 days with `project_id IS NOT NULL` have at least one matching `project_members` row for every previously-accepted invitee.

Authoritative source: `openspec/archive/2026-05-13-add-specialist-onboarding/design.md §§ 4.2–5.2` (original criterion); `openspec/archive/2026-05-23-rearchitect-spec-document-model/` (resolution).

---

## canvas_sessions.cost_cents cap raised to $50 (applied 2026-05-16 from change `raise-canvas-cost-cap`)

Budget cap on `canvas_sessions.cost_cents` raised 500 → 5000 cents ($5 → $50/canvas). No schema migration — the column type, CHECK constraint (`cost_cents >= 0`), and RLS policies are unchanged. The cap is purely a runtime constant enforced by `CANVAS_BUDGET_CENTS` in `packages/muse/src/orchestration/tool_use_loop.ts` and a mirrored literal in `apps/studio/src/app/(auth-walled)/(canvas)/spec/[id]/turn/route.ts`.

Rationale: the $5 cap was tripping on ordinary repo-intake sessions (RAP-957). The cap is a safety rail, not a pricing gate; $50 allows a full productive session.

Source: `openspec/archive/2026-05-16-raise-canvas-cost-cap/`.

---

## Orchestra journal + agent status tables (applied 2026-05-10; surfaced 2026-05-20 from change `add-maestro-runtime-mvp`)

Two tables backing the agent orchestra's audit substrate. Schemas were applied 2026-05-10 via separate changes (RAP-185 / RAP-186) but the entities catalogue did not yet list them; `add-maestro-runtime-mvp` consumes both and documents them here as part of the v1.0 archive amendment.

### `orchestra_journal`

Append-only canonical journal for routing decisions, handoff records, and audit observations across the orchestra. Backs Maestro's `agents/maestro.md § Access scope > DB tables` declarations.

```sql
CREATE TABLE public.orchestra_journal (
  id                     uuid          PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at             timestamptz   NOT NULL DEFAULT now(),
  originating_agent_id   text          NOT NULL,
  session_id             text          NOT NULL,
  sequence               integer       NOT NULL,
  event_kind             text          NOT NULL,
  payload                jsonb         NOT NULL,
  content_hash           text          NOT NULL,
  corrects_content_hash  text,

  CONSTRAINT orchestra_journal_session_sequence_unique UNIQUE (session_id, sequence),
  CONSTRAINT orchestra_journal_sequence_positive_check CHECK (sequence > 0)
);

CREATE INDEX orchestra_journal_agent_time_idx
  ON public.orchestra_journal (originating_agent_id, created_at DESC);
CREATE INDEX orchestra_journal_session_seq_idx
  ON public.orchestra_journal (session_id, sequence);

ALTER TABLE public.orchestra_journal ENABLE ROW LEVEL SECURITY;

CREATE POLICY "orchestra_journal: platform_owner read"
  ON public.orchestra_journal FOR SELECT TO authenticated
  USING ((((auth.jwt() -> 'app_metadata'::text) ->> 'platform_owner'::text))::boolean IS TRUE);
```

**Design choices (locked by RAP-185 design):**

- **`event_kind` is free text, not enum.** New agents can ship event kinds without migrations. The spec is the contract — see `agents/maestro.md § Access scope` and the per-agent capability specs (Forge emits `handoff_ack` + `build_complete`; Architect emits `spec_promoted`; Maestro emits `routing_plan`).
- **`payload jsonb` carries event-specific fields.** Stable keys per `event_kind` are documented in `openspec/archive/2026-05-20-add-maestro-runtime-mvp/design.md § 2 Data shapes > Payload-key convention`.
- **`(session_id, sequence)` UNIQUE.** Per-session monotonic ordering. Sessions are per-process — Maestro and Forge use distinct `session_id` values; cross-correlation rides on `payload.from_maestro_session_id`.
- **`corrects_content_hash`** is the append-only correction pointer. The original row is preserved; readers reconcile by walking corrections.
- **RLS: platform_owner SELECT only.** Writes go through the service role (Maestro Muse mode runtime, Forge `safeEmitOrchestraJournal`, studio server actions). No INSERT / UPDATE / DELETE policy for `authenticated`.

Source: `openspec/archive/2026-05-10-add-orchestra-journal-table/`. Migration: `supabase/migrations/20260510030411_add_orchestra_journal.sql`.

Active writers (as of 2026-05-20):

| Writer | Event kinds | Path |
|---|---|---|
| Maestro mode | `routing_plan` | `packages/muse/src/modes/maestro/db_journal.ts` (delegates to cli-core helper) |
| Forge build orchestrator | `handoff_ack`, `build_complete` | `packages/forge/src/core/forge-events.ts:safeEmitOrchestraJournal` |
| Architect canvas turn | `spec_promoted` | `apps/studio/src/app/(auth-walled)/(canvas)/spec/[id]/turn/route.ts` |

All three writers route through `insertJournalEntry` at `packages/cli-core/src/orchestra/journal-client.ts` (the single hash-canonicalising point — same payload → same `content_hash` regardless of writer).

### `agent_status`

One row per agent. Maestro's read source for routing context — current work item, declared scope, last completion, last heartbeat.

```sql
CREATE TABLE public.agent_status (
  agent_id            text         PRIMARY KEY,
  current_work_item   text,
  current_session_id  text,
  declared_scope      text         NOT NULL,
  last_completion_at  timestamptz,
  last_heartbeat_at   timestamptz  NOT NULL DEFAULT now(),
  updated_at          timestamptz  NOT NULL DEFAULT now()
);

ALTER TABLE public.agent_status ENABLE ROW LEVEL SECURITY;
-- platform_owner SELECT + service_role ALL (writes via admin client).
```

**Design choices (locked by RAP-186 design):**

- **`agent_id` text PK, not enum.** Phase B agents (Atlas, Echo, Pulse) activate by row insert, not schema migration.
- **`declared_scope` text without CHECK constraint.** Each agent declares its own posture vocabulary; Phase A seed is `idle` for all known agents. Maestro's `route()` validator at `packages/muse/src/modes/maestro/route.ts:57` only cares about `current_work_item` (null = idle) for the `agent_claimed` invariant.
- **`last_heartbeat_at` drives staleness detection** — Maestro's deferred drift-audit pass surfaces "agents quiet for >N min" from this column.

Source: `openspec/archive/2026-05-10-add-agent-status-table/`. Migration: `supabase/migrations/20260510000001_create_agent_status.sql`.

Active readers (as of 2026-05-20):

- Maestro mode (`packages/muse/src/modes/maestro/headless.ts:defaultReadSnapshot`) — reads all rows on every `maestro route` invocation; passes the snapshot to the pure `route()` validator.

No active writers on v1.0 (Phase A bootstrap seeds rows manually; Phase B agents take over their own rows when they activate). The "one-time seed" procedure is documented in the smoke runbook at `packages/forge/scripts/journal-smoke.ts` JSDoc and PR [#1542](https://github.com/rapoport-studio/rapoport.studio/pull/1542) review notes.

## agent_run_events (applied 2026-05-23 from change `add-agent-event-relay-mvp`, RAP-1155)

Append-only structured event log for agent runs across all CLIs (`forge`, `muse`, `maestro`). Mirrors the `idea_stage_events` append-only pattern (and the original `canvas_stage_events` before the RAP-1170 rename): one row per event, no `UPDATE`/`DELETE` RLS policy. Generic across CLIs via the `actor_cli` discriminator — one table serves all three, no per-CLI tables.

### Schema

| Column | Type | Notes |
|---|---|---|
| `event_id` | `uuid` PK | UUIDv7, client-minted; encodes emit-time millisecond so the envelope's `ts` field is not stored separately |
| `run_id` | `text` not null | 1–200 chars; the CLI-side run identifier |
| `kind` | `text` not null | 1–64 chars; AG-UI-aligned event kind (`run_started`, `run_finished`, `model_call`, `spec_drafted`, `pr_opened`, …) |
| `payload` | `jsonb` not null default `'{}'` | Per-kind shape; validated client-side via `packages/cli-core/src/events/schema.ts` |
| `actor_cli` | `text` not null | `check in ('forge','muse','maestro')` |
| `linear_id` | `text` nullable | `check null or ~ '^RAP-\d+$'` |
| `created_at` | `timestamptz` not null default `now()` | DB-insert time (distinct from envelope emit-time) |

### Indexes

- `agent_run_events_run_id_created_at_idx` — for run-scoped event reads in created order
- `agent_run_events_linear_id_created_at_desc_idx` — for RAP-issue-scoped reverse-chronological reads

### RLS

- Row-level security enabled.
- One SELECT policy: `agent_run_events: platform owner read` — `authenticated` JWT with `app_metadata.platform_owner = true` only.
- No INSERT/UPDATE/DELETE policies → append-only to every non-service caller. Service role bypasses RLS and is the sole writer.

### Realtime broadcast

`AFTER INSERT` trigger `agent_run_events_broadcast` invokes `realtime.broadcast_changes('run:' || new.run_id, …)`, so subscribers can stream events per `run_id` via Supabase Realtime channel `run:<run_id>`. UI consumers are out of scope for the MVP.

### Wave status (at archive 2026-05-23)

| Wave | Deliverable | Status |
|---|---|---|
| 1 | `packages/cli-core/src/events/` — envelope + per-kind schemas | ✅ shipped via PR #1585 |
| 2 | `muse research` instrumentation | ✅ shipped via PR #1585 |
| 3 | `agent_run_events` migration + DB types | ✅ shipped via PR #1587 |
| 4 | `POST /api/runs/ingest` (apps/studio) — service-role write, bearer-token auth | **Deferred.** Forge could not author the endpoint reliably (B-1/B-2 class failures on two attempts). The endpoint has no production caller in this MVP — per `design.md § 1`: *"In this change the CLI stops at the local jsonl log."* When a real caller materialises, file a successor change `add-agent-event-ingest-endpoint`. |

Source: `openspec/archive/2026-05-23-add-agent-event-relay-mvp/`. Migration: `supabase/migrations/20260521010000_add_agent_run_events.sql`.


## Forge build-state columns on ideas (applied 2026-05-24 from change complete-idea-to-forge-loop)

**RAP-1204 / RAP-1206 (Phase 2 + Phase 4a of complete-idea-to-forge-loop).** Two migrations add four columns to `ideas` that wire the dispatch → read-back loop:

```sql
ALTER TABLE public.ideas
  ADD COLUMN forge_state_url       text NULL,
  ADD COLUMN forge_last_checkpoint text NULL,
  ADD COLUMN forge_outcome         text NULL;

ALTER TABLE public.ideas
  ADD COLUMN rap_id                text NULL;
ALTER TABLE public.ideas
  ADD CONSTRAINT ideas_rap_id_format_check
  CHECK (rap_id IS NULL OR rap_id ~ '^RAP-[0-9]+$');
CREATE UNIQUE INDEX ideas_rap_id_unique_idx
  ON public.ideas (rap_id) WHERE rap_id IS NOT NULL;
```

| Column | Owner | Meaning |
|---|---|---|
| `forge_state_url` | `forge-on-change-merge.yml` workflow (dispatch writer) | URL of the most recent `forge-build.yml` run for the idea. Null until first dispatch. |
| `forge_last_checkpoint` | `poll-build-state.ts` server action | Latest Forge Builder-mode checkpoint name while a run is in-flight. One of `plan-ack`, `spec-acceptance`, `post-migrations`, `code-review`, `push`, `pr-creation`. |
| `forge_outcome` | `poll-build-state.ts` server action | Terminal `BuildOutcome` once the run finishes. One of `completed-with-pr`, `completed-no-pr`, `aborted-no-changes`, `aborted-at-checkpoint`, `failed-commit`, `failed-push`, `failed-pr`, `failed-builder`, `cancelled`. |
| `rap_id` | `muse draft` CLI + studio surfaces | Canonical Linear identifier (`RAP-<digits>`). Partial UNIQUE — multiple NULL rows allowed; at most one row per RAP-id when non-null. |

**Mutex contract** (D-IFL-12, enforced in `packages/db/src/forge-build-state.ts` `normalizeForgeBuildStatePatch`): when `forge_outcome` is non-null, `forge_last_checkpoint` is null. Both null together is the valid "no run dispatched yet" state. The contract is NOT a SQL CHECK — both enum spaces are owned by the Forge build pipeline and may grow; SQL CHECKs would create a tight coupling.

Migrations: `supabase/migrations/20260524122825_complete_idea_to_forge_loop.sql` (forge_*) + `supabase/migrations/20260524143330_rap_1206_add_rap_id_to_ideas.sql` (rap_id). RLS unchanged — additive nullable columns inherit the existing `has_idea_role` policy on `ideas`.

Source: `openspec/archive/2026-05-24-complete-idea-to-forge-loop/`.

## Marketing funnel events + inbox.source_page (applied 2026-05-25 from change `add-marketing-funnel-instrumentation`, RAP-1214)

Server-side measurement loop for the marketing funnel. Adds one new table and one column.

### `public.marketing_funnel_events`

Append-only event log written by the `/api/funnel` server route. Service-role only — anon + authenticated have all permissions revoked.

```sql
CREATE TABLE public.marketing_funnel_events (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  occurred_at  timestamptz NOT NULL DEFAULT now(),
  event_kind   text NOT NULL,
  session_id   text,                              -- per-tab sessionStorage UUID (no PII)
  source_page  text,                              -- URL path of the emit-point
  locale       text,
  metadata     jsonb DEFAULT '{}'::jsonb
);
CREATE INDEX marketing_funnel_events_occurred_at_idx
  ON public.marketing_funnel_events (occurred_at);
CREATE INDEX marketing_funnel_events_kind_occurred_at_idx
  ON public.marketing_funnel_events (event_kind, occurred_at);
ALTER TABLE public.marketing_funnel_events ENABLE ROW LEVEL SECURITY;
REVOKE ALL ON public.marketing_funnel_events FROM anon, authenticated;
```

Allowed `event_kind` values (whitelist enforced at the `/api/funnel` route, not at the DB layer): `intake_step_reached`, `lead_submitted`, `openspec_archive_downloaded`, `decision_router_selected`, `discovery_token_issued`, `discovery_paid`.

`session_id` is a per-tab UUID stored in browser `sessionStorage` (key `rs-funnel-session`) — not a cookie, no cross-session tracking, cleared on tab close. Cookieless-by-default posture preserved.

### `public.inbox.source_page`

New nullable column on the existing `inbox` table (the marketing intake-form sink). Captures the URL path the visitor was on when they submitted the intake (e.g. `/services/discovery`).

```sql
ALTER TABLE public.inbox
  ADD COLUMN source_page text;
```

Populated through the data path: `apps/web` `<IntakeForm />` reads `window.location.pathname` at submit → POSTs to `/api/intake` → forwarded to `apps/studio /api/studio/leads` → inserted into `inbox.source_page`.

Migration: `supabase/migrations/20260524190000_rap_1214_marketing_funnel_events.sql`. Source: `openspec/archive/2026-05-25-add-marketing-funnel-instrumentation/`.

## Discovery checkout tokens (applied 2026-05-25 from change `wire-stripe-checkout-on-discovery`, RAP-1213)

Stripe Checkout token table for the Discovery engagement payment flow. One UUID token = one checkout opportunity for one lead.

```sql
CREATE TABLE public.discovery_checkout_tokens (
  token                      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  lead_id                    uuid NOT NULL REFERENCES public.inbox(id),
  price_id                   text NOT NULL,
  issued_at                  timestamptz NOT NULL DEFAULT now(),
  expires_at                 timestamptz NOT NULL,
  redeemed_at                timestamptz,
  stripe_checkout_session_id text UNIQUE,
  amount_cents               integer,
  created_by                 uuid REFERENCES auth.users(id)
);
CREATE INDEX ON public.discovery_checkout_tokens (lead_id);
CREATE INDEX ON public.discovery_checkout_tokens (expires_at)
  WHERE redeemed_at IS NULL;
ALTER TABLE public.discovery_checkout_tokens ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Platform owners manage discovery tokens"
  ON public.discovery_checkout_tokens FOR ALL
  USING (public.is_platform_owner())
  WITH CHECK (public.is_platform_owner());
```

RLS: `platform_owner` (`public.is_platform_owner()`) only. Service-role bypasses RLS for the webhook handler and admin API routes.

### `public.inbox.discovery_paid_at`

New nullable column on `inbox`. Set to `now()` by the Stripe webhook handler on `checkout.session.completed`.

```sql
ALTER TABLE public.inbox ADD COLUMN discovery_paid_at timestamptz;
```

Migration: `supabase/migrations/20260525120000_discovery_checkout_tokens.sql`.


## `project_openspec_snapshots` + ingest columns on `projects` (applied 2026-05-25 from change `lift-spec-block-and-ingest-linked-repo-openspec`)

**RAP-1223.** Per-project cache of the linked GitHub repo's `openspec/current/` tree. Populated by `syncProjectOpenspec(projectId)` (see `current/studio.md § OpenSpec ingest for client projects`); consumed by `<ProjectSnapshotSpecBlock>`.

### `public.project_openspec_snapshots`

```sql
CREATE TABLE public.project_openspec_snapshots (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id uuid NOT NULL REFERENCES public.projects(id) ON DELETE CASCADE,
  path       text NOT NULL,        -- 'current/auth.md', 'current/agents/scope.md'
  content    text NOT NULL,
  sha        text NOT NULL,        -- GitHub blob SHA at fetch time
  fetched_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (project_id, path)
);

CREATE INDEX ON public.project_openspec_snapshots (project_id);
CREATE INDEX ON public.project_openspec_snapshots (fetched_at);
ALTER TABLE public.project_openspec_snapshots ENABLE ROW LEVEL SECURITY;

CREATE POLICY "project_openspec_snapshots: read own or member"
  ON public.project_openspec_snapshots FOR SELECT TO authenticated
  USING (
    EXISTS (
      SELECT 1 FROM public.projects p
      WHERE p.id = project_openspec_snapshots.project_id
        AND (p.owner_id = auth.uid() OR public.is_project_member(p.id))
    )
  );
```

Writes are restricted to the service role (performed by `syncProjectOpenspec` via `getAdminClient`). No authenticated INSERT / UPDATE / DELETE. Migration: `supabase/migrations/20260525210000_add_project_openspec_snapshots.sql`.

### `public.projects` — six ingest columns

```sql
ALTER TABLE public.projects
  ADD COLUMN openspec_ingest_enabled         boolean NOT NULL DEFAULT false,
  ADD COLUMN openspec_ingest_repo            text,        -- 'owner/repo' slug, nullable when disabled
  ADD COLUMN openspec_ingest_branch          text NOT NULL DEFAULT 'main',
  ADD COLUMN openspec_ingest_last_synced_at  timestamptz,
  ADD COLUMN openspec_ingest_last_error      text,
  ADD COLUMN openspec_ingest_last_error_at   timestamptz;
```

Opt-in is explicit (D-SPEC-INGEST-1): no project auto-discovers an `openspec/` folder. Disabling ingest pauses the sync; row removal from `project_openspec_snapshots` is explicit via `clearProjectOpenspecSnapshots(projectId)`. Migration: `supabase/migrations/20260525210001_add_projects_openspec_ingest_columns.sql`.

Source: `openspec/archive/2026-05-25-lift-spec-block-and-ingest-linked-repo-openspec/`.

## `ideas.title` + `specs.title` write contract (applied 2026-05-26 from change `add-title-to-ideas-and-specs`, RAP-1226)

Both `ideas` and `specs` now carry a `title text` column used as the display name in the studio's header + sidebar surfaces.

```sql
alter table public.ideas add column title text;  -- nullable; null falls back to ideas.slug in the UI

-- One-shot backfill: existing specs inherit the project's name.
update public.specs s
   set title = p.name
  from public.projects p
 where s.project_id = p.id
   and s.title is null;
```

Write paths:

- `specs.title` — defaults to `projects.name` on insert (the spec-create code path reads the project's name first and writes both `project_id` and `title` in the same transaction). Editable via the `updateSpecTitle(specId, title)` server action at `apps/studio/src/app/actions/spec-title.ts` (owner-only via `requireOwner()`, trimmed, 140-char cap, non-empty; `revalidateTag(\`spec-${specId}\`)` on success).
- `ideas.title` — populated by Muse Architect alongside `update_proposal` (verb-led title derived from the proposal's H1 or first sentence, ≤ 140 chars). Editable via `updateIdeaTitle(ideaId, title)` at `apps/studio/src/app/actions/idea-title.ts` (same gate + validation pattern; `revalidateTag(\`idea-${ideaId}\`)`).

Slug stability is invariant (D-IT-2): renaming a title does NOT change `ideas.slug` or `ideas.github_branch`. Migration: `supabase/migrations/20260525230000_rap_1226_add_ideas_title_and_backfill_specs_title.sql`. Source: [`openspec/archive/2026-05-26-add-title-to-ideas-and-specs/`](../../archive/2026-05-26-add-title-to-ideas-and-specs/).
