rfc-app/docs/superpowers/specs/2026-06-03-m3-backend-design.md

# M3-backend — §22 multi-project: registry mirror + data spine + APIs

> Design spec for the backend half of §22 slice **M3** ("Registry mirror +
> routing + runtime branding"). The roadmap bundles M3 as one slice; this
> session splits it at the natural backend/frontend seam. **M3-backend** (this
> doc) ships the data spine, the registry mirror, the two runtime-config APIs,
> and the entry-state/review semantics. **M3-frontend** (a separate spec) ships
> `/p/<project>/` routing, the 308 redirects, the `VITE_APP_NAME`→runtime-config
> cut, the per-project theme overlay, the deployment directory at `/`, and the
> project switcher — all consuming the APIs defined here.
>
> Section references `§22.x` point at `docs/design/multi-project-spec.md` (the
> draft §22 + slicing plan). The SPEC.md §22 merge itself lands in M7.

## Status

- **Date:** 2026-06-03
- **Slice:** §22 M3 (backend half). M1 + M2 landed and merged to `main`.
- **Version impact:** minor bump, breaking (pre-1.0) — see §8.

## Goal

After M3-backend, the framework learns its projects from a git **registry**
(not from `META_REPO`), the `projects` cache table and all slug-bearing tables
are keyed by `(project_id, …)` so a second project can exist without collision,
the default project's identity is re-stamped to its real slug while no `/p/`
URL is yet public, and the runtime exposes deployment + project config over two
new endpoints. The entry-state/review semantics (`initial_state`, `unreviewed`)
ship complete even though OHM (a `document`/`super-draft` project) does not yet
exercise them.

Non-goals (M3-frontend, later): `/p/<project>/` routing, 308 redirects off
`/rfc/<slug>`, runtime branding in the UI, theme application, the deployment
directory, the project switcher, the catalog's unreviewed-filter **UI**.

## Decisions taken in brainstorming

1. **Scope split** — backend spine first; frontend surfaces are a separate
   spec/session.
2. **Review machinery** — build the full `initial_state` / `unreviewed`
   plumbing now (parse + columns + mirror + landing logic + mark-reviewed +
   catalog filter query side), per the spec's M3 bundle, even though no live
   project exercises it yet.
3. **Config cut** — hard cut. `REGISTRY_REPO` required (loud fail if unset),
   `META_REPO` removed. Upgrade-steps document the manual registry creation.
4. **Re-stamp** — rewrite `project_id` everywhere: rename `projects.id`
   `default` → the config slug and rewrite every child row, folded into the
   same create-copy-drop-rename rebuild that adds the `project_id` FK. One
   identifier; DB and URL agree.
5. **Mirror structure** — a self-contained `app/registry.py` module (not folded
   into `cache.py`), driven by the existing webhook dispatcher + the existing
   `Reconciler.sweep()`.
6. **Two execution plans (found during planning).** The 12-table PK rebuild is
   not self-contained: folding `project_id` into keys + FK forces every
   `ON CONFLICT` upsert target to gain `project_id` (~10 sites across 6 modules)
   and — because the rebuilt tables can no longer default `project_id` to a live
   value once the default is re-stamped — forces **every RFC/branch writer** to
   be threaded to supply the real `project_id`. That activation is larger and
   riskier than the rest of M3-backend combined, and it is only required *before
   a second project can collide* (i.e. right before M4). So M3-backend is split
   into two plans at that seam:
   - **Plan A (ships first):** registry mirror + the two APIs + `initial_state`/
     `unreviewed` semantics. Migration `027` is **additive only** (no rebuilds).
     Operates entirely on the `default`-id project — no re-stamp, no rebuild, no
     writer threading. `cached_rfcs` keeps its `slug` PK, so no upsert breakage.
   - **Plan B (before M4 / before public `/p/` URLs):** the 12-table PK rebuild
     (migration `028`), `project_id` threading through every writer, the
     `default`→slug **re-stamp** (which rides here because it is only correct
     once the rebuild's column-default fix + threading land), and two-project
     isolation tests.

   The §1 sections below describe the **full** backend (both plans); §1c (the
   rebuilds) and §1d (the re-stamp) are **Plan B**. Everything else is Plan A.

---

## 1. Migration `027_projects_activate.sql`

Runs while `default` is still the sole project and no `/p/` URL exists — the
safe window for an identity rewrite. One transaction (SQLite DDL is
transactional): the deployment either fully advances to `027` or stays on `026`.

`DEFAULT_PROJECT_ID` is read at migration time, so it **must be set before the
upgrade deploy** (documented in §8). The slug it names is referred to below as
`<slug>`; absent the env var, `<slug>` stays `default`.

### 1a. Additive columns

- `projects`:
  - `type TEXT NOT NULL DEFAULT 'document' CHECK (type IN ('document','specification','bdd'))`
  - `initial_state TEXT NOT NULL DEFAULT 'super-draft' CHECK (initial_state IN ('super-draft','active'))`
- `cached_rfcs`:
  - `unreviewed INTEGER NOT NULL DEFAULT 0`
  - `reviewed_at TEXT`
  - `reviewed_by TEXT`

### 1b. New `deployment` singleton table

Holds deployment-level identity mirrored from the registry's `deployment:`
block (rather than overloading `projects`):

```sql
CREATE TABLE IF NOT EXISTS deployment (
    id          INTEGER PRIMARY KEY CHECK (id = 1),
    name        TEXT,
    tagline     TEXT,
    registry_sha TEXT,
    updated_at  TEXT NOT NULL DEFAULT (datetime('now'))
);
INSERT OR IGNORE INTO deployment (id) VALUES (1);
```

> **Re-stamp split (correctness, found during planning).** The migration
> runner executes pure-SQL files and **cannot read `DEFAULT_PROJECT_ID` from the
> environment** — the same constraint that forced M1's `seed_default_project`
> into Python. So the re-stamp is **not** in the `.sql` file. Migration `027`
> (pure SQL) does §1a–§1c with `project_id` copied **verbatim** (`default` stays
> `default`); the rebuilt FKs are declared `ON UPDATE CASCADE ON DELETE
> CASCADE`. The re-stamp (§1d) is a **Python startup step** in `app/projects.py`,
> run after migrations and before the registry mirror, that issues a single
> `UPDATE projects SET id = <slug>` (cascading to the 12 FK tables) plus a plain
> `UPDATE` of the 7 non-FK `project_id` tables. Idempotent: a no-op once no
> `default` row remains.

### 1c. PK / uniqueness rebuilds (the 12 deferred tables)

Per migration 026's deferred block, create-copy-drop-rename each table to fold
`project_id` into the key and add `project_id … REFERENCES projects(id) ON
UPDATE CASCADE ON DELETE CASCADE` (the `ON UPDATE CASCADE` is what lets the
§1d Python re-stamp move all child rows with one parent UPDATE):

| Table | Key change |
| --- | --- |
| `cached_rfcs` | PK `(slug)` → `(project_id, slug)` |
| `cached_branches` | UNIQUE `(rfc_slug, branch_name)` → `+project_id` |
| `branch_visibility` | UNIQUE `(rfc_slug, branch_name)` → `+project_id` |
| `branch_contribute_grants` | UNIQUE `(rfc_slug, branch_name, grantee_user_id)` → `+project_id` |
| `stars` | UNIQUE `(user_id, rfc_slug)` → `+project_id` |
| `watches` | UNIQUE `(user_id, rfc_slug)` → `+project_id` |
| `pr_seen` | UNIQUE `(user_id, rfc_slug, pr_number)` → `+project_id` |
| `branch_chat_seen` | UNIQUE `(user_id, rfc_slug, branch_name)` → `+project_id` |
| `funder_consents` | PK `(user_id, rfc_slug)` → `+project_id` |
| `rfc_collaborators` | UNIQUE INDEX `(rfc_slug, user_id)` → `+project_id` |
| `contribution_requests` | UNIQUE INDEX `(rfc_slug, requester_user_id) WHERE pending` → `+project_id` |
| `proposed_use_cases` | UNIQUE `(scope, pr_number)` → `+project_id` |

`cached_prs` is already globally unique (`repo` is the full `org/repo` string,
distinct per project) — **no rebuild**.

The copy step writes `<slug>` in place of `default` for `project_id`, so these
12 tables are re-stamped for free (§1d).

### 1d. Re-stamp `default` → `<slug>` (Python startup step)

In `app/projects.py`, run at startup after `db.init` and before
`refresh_registry`. When `DEFAULT_PROJECT_ID` is set and a `default` project row
still exists, in one `db.tx()`:

- `UPDATE projects SET id = <slug>, updated_at = datetime('now') WHERE id =
  'default'` — cascades `project_id` across the 12 FK tables via `ON UPDATE
  CASCADE`.
- For the 7 `project_id`-bearing tables with **no** FK (`threads`, `changes`,
  `notifications`, `actions`, `pr_resolution_branches`, `rfc_invitations`,
  `cached_prs`): `UPDATE <t> SET project_id = <slug> WHERE project_id =
  'default'`.

End state: a single `project_id` value DB-wide. Idempotent — a no-op once no
`default` row remains, so it is safe on every boot.

### 1e. Notes

- Migration `016` is absent from the on-disk sequence (`015 → 017`); the runner
  already tolerates the gap (the app runs today). **Do not renumber.** New file
  is `027`.
- `PRAGMA foreign_keys` is honored going forward; the FK lands on the 12 rebuilt
  tables. The other 7 keep app-layer integrity (matching today's posture for
  non-rebuilt tables).

---

## 2. Registry format + `app/registry.py`

### 2a. `projects.yaml` (root of `REGISTRY_REPO`)

```yaml
deployment:
  name: Open Human Model         # replaces VITE_APP_NAME (M3-frontend consumes)
  tagline: ...
projects:
  - id: ohm                      # url-stable slug, unique in the deployment
    name: Open Human Model
    type: document               # document | specification | bdd — immutable
    content_repo: ohm            # repo under the deployment's Gitea org
    visibility: public           # gated | public | unlisted
    initial_state: super-draft   # optional; defaults from type
    enabled_models: [claude, gemini]   # optional; falls back to ENABLED_MODELS
    theme: { accent: "#5b5bd6" }       # optional; M3-frontend consumes
```

### 2b. `refresh_registry(config, gitea) -> RegistryResult`

The config-side analogue of `cache.refresh_meta_repo`. Fetches `projects.yaml`
from `REGISTRY_REPO` at HEAD, parses, **validates**, and upserts:

- Each project → `projects` row: `id, name, type, content_repo, visibility,
  initial_state`, plus `config_json` (JSON blob for `theme`, `enabled_models`),
  plus `registry_sha` (commit SHA, provenance).
- The `deployment:` block → the `deployment` singleton (`name`, `tagline`,
  `registry_sha`).

Projects present in the table but absent from the registry are **not** deleted
in M3-backend (archival semantics are out of scope; a removed entry simply
stops being refreshed). This is noted as a known limitation; revisit if/when
project archival is specced.

### 2c. Validation (loud)

Per the framework's separation-of-concerns rule, malformed config fails
visibly rather than shipping wrong content silently:

- Each project requires `id`, `name`, `type`, `content_repo`.
- `type` ∈ {`document`,`specification`,`bdd`}; `visibility` ∈
  {`gated`,`public`,`unlisted`}.
- `initial_state` defaults from `type` when omitted: `document`/`specification`
  → `super-draft`, `bdd` → `active`. When present it must be a valid §2.4
  super-draft entry-state value.
- `id` values unique and slug-shaped (`^[a-z0-9][a-z0-9-]*$`).
- **`type` is immutable:** an incoming `type` differing from the stored row's is
  rejected (the entry is skipped, the rest proceed; logged loudly).
- **Default-id consistency:** the re-stamped default id (`DEFAULT_PROJECT_ID`,
  else `default`) MUST appear as an `id` in the registry, or the registry is
  inconsistent with config → surfaced loudly.

### 2d. Wiring (Option A)

- **Webhook** (`app/webhooks.py`): add a branch — if the pushed repo
  `full_name` matches `REGISTRY_REPO`, call `registry.refresh_registry(...)`.
  Same HMAC-verified `/api/webhooks/gitea` dispatcher; no new endpoint. Add
  `REGISTRY_REPO` to the set of repos the dispatcher recognizes.
- **Sweep** (`cache.Reconciler.sweep()`): add one `await
  registry.refresh_registry(...)` at the top of each pass, so the safety-net
  loop keeps `projects` in sync if a webhook is missed.
- **Startup** (`main.py` lifespan): run `refresh_registry` once after
  migrations. This **replaces** M1's `seed_default_project` (which is removed).

### 2e. Failure posture

- **Startup / first boot:** if `REGISTRY_REPO` is unset/unreachable, or
  `projects.yaml` is missing or fails validation, the app **fails loudly**
  (refuses to start) — there is no last-known-good to serve.
- **Running deployment:** a malformed `projects.yaml` pushed in a later PR is
  logged and **skipped**, leaving the last-good `projects` rows intact — a bad
  config PR must not take the deployment down. This mirrors how the corpus
  reconciler tolerates a bad content push today.

---

## 3. Config (`app/config.py`)

- `registry_repo`: **required** — construction fails loudly if unset (matching
  the other required vars). Add `registry_repo_full` → `{gitea_org}/{registry_repo}`.
- `meta_repo` and `meta_repo_full`: **removed**.
- `default_project_id`: optional; consumed by migration `027` (re-stamp) and by
  `refresh_registry` (the §2c consistency gate).
- `enabled_models`: unchanged — the deployment-level fallback for a project's
  optional `enabled_models`.
- `app/projects.py`: `seed_default_project` retired (superseded by the mirror).
  `DEFAULT_PROJECT_ID` constant and resolution helpers retained as needed.

---

## 4. APIs — `app/api_deployment.py`

A new sub-router mounted in `app/api.py`.

### `GET /api/deployment`

Returns `{ name, tagline, projects: [...] }`. The deployment `name`/`tagline`
come from the `deployment` singleton. The project list is filtered by caller
visibility (§22.5):

- `public` projects → visible to everyone (incl. anonymous).
- `gated` projects → only when the caller is a member (`visible_project_ids`
  from M2's resolver).
- `unlisted` projects → **omitted entirely** (reachable only by direct id).

Each item: `{ id, name, type, visibility }` — enough for the M3-frontend
directory + switcher. (Theme is fetched per project.)

### `GET /api/projects/:id`

Returns `{ id, name, tagline, type, visibility, initial_state, theme }`.
Guarded by `require_project_readable(user, id)` — 404 for a non-member of a
gated project, reusing the M2 resolver. `unlisted` is readable here by direct
id (it is hidden only from enumeration).

---

## 5. Entry-state & review semantics

### 5a. Frontmatter (`app/entry.py`)

Parse three new fields, lenient (default `unreviewed=false`, nulls):
`unreviewed: bool`, `reviewed_at`, `reviewed_by`. Add to the `Entry`
dataclass. These are git-truth (§2 frontmatter) so they survive a cache
rebuild, exactly like `state`.

### 5b. Cache mirror (`app/cache.py`)

`_upsert_cached_rfc` writes the three fields into `cached_rfcs`
(`unreviewed`, `reviewed_at`, `reviewed_by`).

### 5c. Entry-landing path

When a creating idea-PR merges (§2.4), resolve the project's `initial_state`:

- `super-draft` → today's behavior unchanged (propose → super-draft → graduate).
- `active` → land the entry `active` with `unreviewed = true`, skipping the §13
  graduate gate. The exact merge-handling site (in the PR-merge reconcile path)
  is located during implementation.

### 5d. Mark-reviewed

`POST /api/projects/:pid/rfcs/:slug/mark-reviewed`. Authority:
`is_project_superuser` (project_admin or deployment owner/admin) — the same tier
that graduates an entry. Effect: the bot writes `unreviewed: false` +
`reviewed_at`/`reviewed_by` into the entry frontmatter (a git commit, paralleling
graduate), which the mirror then reflects into `cached_rfcs`. Stamps provenance
paralleling `graduated_at`/`graduated_by`.

### 5e. Catalog filter (query side)

`GET /api/rfcs` (and the project-scoped form) gains an `unreviewed=true` query
param that filters on the cached column — the owner's worklist. The UI for it
is M3-frontend; only the query side ships here. `unreviewed` applies to
`active` entries only.

---

## 6. Testing

- **Migration `027`:** seed a `026`-shaped DB with rows under
  `project_id='default'`; run `027`; assert: new columns present; the 12 tables
  rebuilt with composite keys + FK; all 19 `project_id`-bearing tables
  re-stamped to `<slug>`; row counts preserved; FK integrity on; idempotent
  re-run is a no-op.
- **`registry.py`:** valid `projects.yaml` upserts all fields + `registry_sha`;
  each validation failure rejected (bad enum, missing field, dup id, `type`
  mutation, missing default id); `initial_state` type-default applied;
  startup-strict vs running-tolerant posture.
- **APIs:** `/api/deployment` visibility filtering across
  gated/public/unlisted × member/non-member/anonymous; `/api/projects/:id` 404
  gate for gated non-member, 200 for unlisted-by-id.
- **Review flow:** `initial_state: active` lands `unreviewed=true`;
  mark-reviewed authority (allow superuser, deny others) + frontmatter write +
  mirror reflection; catalog `unreviewed` filter returns the worklist.
- **Two-project isolation:** extend `test_multi_project_authz_vertical.py` with
  a genuine **second** registry project sharing a slug with the first, proving
  the PK rebuilds isolate them (the core point of M3's activation).

---

## 7. File-touch summary

**New**
- `backend/migrations/027_projects_activate.sql`
- `backend/app/registry.py`
- `backend/app/api_deployment.py`
- tests: `backend/tests/test_migration_027.py`, `test_registry.py`,
  `test_api_deployment.py`, `test_review_flow.py`; extend
  `test_multi_project_authz_vertical.py`

**Modified**
- `backend/app/config.py` (registry_repo required; meta_repo removed;
  default_project_id)
- `backend/app/webhooks.py` (registry-repo branch)
- `backend/app/cache.py` (`Reconciler.sweep` registry call;
  `_upsert_cached_rfc` review fields)
- `backend/app/entry.py` (frontmatter fields)
- `backend/app/api.py` (mount `api_deployment`; `unreviewed` filter on
  `/api/rfcs`)
- `backend/app/main.py` (startup `refresh_registry`; drop `seed_default_project`)
- `backend/app/projects.py` (retire `seed_default_project`)
- `backend/.env.example` (`REGISTRY_REPO`, `DEFAULT_PROJECT_ID`; remove
  `META_REPO`)

---

## 8. Versioning & upgrade

Minor bump, breaking (pre-1.0). `VERSION` + `frontend/package.json#version` move
together (§20). `CHANGELOG.md` gets a breaking entry with an **upgrade-steps**
block:

1. Create a registry repo under the deployment's Gitea org.
2. Author `projects.yaml`: a `deployment:` block (`name`, `tagline`) and one
   `projects:` entry for the existing corpus — `id: <slug>`, `name`, `type:
   document`, `content_repo: <old META_REPO value>`, `visibility: public`.
3. Set env: `REGISTRY_REPO=<registry repo name>`, `DEFAULT_PROJECT_ID=<slug>`
   (must equal the entry's `id`); remove `META_REPO`.
4. Deploy. Migration `027` runs the rebuilds + re-stamp; `refresh_registry`
   reconciles the registry into `projects`. Verify `/api/deployment` returns the
   project and `/api/health` is green.

The SPEC.md §22 merge stays in M7 per the slicing plan; this slice references the
draft at `docs/design/multi-project-spec.md`.

## Known limitations / deferred

- **Project archival/deletion** from the registry is not handled (a removed
  entry stops refreshing but its rows persist). Defer to a future archival spec.
- All routing, redirects, runtime branding, theme application, directory, and
  switcher are **M3-frontend**.