> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ionworks.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 2026 07 20 protocols require project id design

# Protocols require project\_id

**Date:** 2026-07-20
**Status:** Approved (design)
**Builds on:** PR #1225 (moves the protocol simulator into projects) and PR #1206
(scopes materials/components to project — the pattern this mirrors).

## Problem

"Protocols" (stored as `experiment_templates`) and their concrete
parameterisations (`experiments`) are currently scoped only by
`organization_id`. There is no project scoping. "System protocols" (the built-in
Cycler Protocol / standard charge-discharge templates) are represented as rows
owned by the system organization `00000000-0000-0000-0000-000000000001` and
merged into every org's listing via a union in the list route plus an RLS
allowance.

Now that PR #1225 has moved the protocol simulator inside projects, protocols
should belong to a project — the same move materials made in PR #1206. The
end state: every protocol carries a `project_id`; "system protocols" are no
longer a user-facing shared library but a hidden internal **catalog** that is
copied into each project on creation, so each project starts with its own
editable set of the standard protocols.

## Goals

* `experiment_templates` and `experiments` each carry a `project_id`.
* New projects are auto-seeded with copies of the standard protocol set.
* Existing projects are backfilled with the same set.
* "System protocols" stop being a merged read-time library and become an
  internal copy source.
* Rolled out with the repo's expand-and-contract discipline: `project_id` is
  additive/nullable first, made `NOT NULL` only after the backfill has run in
  every environment.

## Non-goals

* Changing the protocol/UCP content of the standard protocols.
* Migrating `optimization_templates` (already has `project_id` + `access_level`).
* Any frontend work beyond what PR #1225 already delivered (the in-project
  simulator already passes a project context).

## Current state (as explored)

* **`experiment_templates`** (`baseline.sql:842`): `organization_id NOT NULL`,
  no `project_id`, no system flag. Dedup unique index
  `idx_experiment_templates_content_hash_org (content_hash, organization_id)`
  (`baseline.sql:3593`). `content_hash` is set by a BEFORE INSERT/UPDATE trigger
  (`trg_experiment_templates_set_content_hash`, `baseline.sql:3973`).
* **`experiments`** (`baseline.sql:2165`): `template_id NOT NULL`,
  `organization_id NOT NULL`, no `project_id`. Unique index
  `experiments_template_params_org_unique (template_id, parameters,
  organization_id)` (`baseline.sql:3441`).
* **System protocols** = rows with `organization_id =
  SYSTEM_ORGANIZATION_ID (00…001)`. Exposed via: RLS SELECT allowance
  (`baseline.sql:5872`), the list-route union of org + system templates
  (`routes/experiment_templates.py:117-126`), and the get-by-id service
  fallback to a service-client repo (`services/experiment_templates.py:16`,
  `104-149`).
* **Sibling precedent** for an explicit "system" concept:
  `optimization_templates` (`baseline.sql:2316`) and `jobs` (`baseline.sql:2201`)
  both use `project_id uuid` (nullable) + `access_level text` CHECK, with a
  CHECK that `access_level='project' ⇒ project_id NOT NULL`.
* **Materials precedent** (PR #1206): migration
  `20260715000001_add_project_id_to_materials.sql` (nullable `project_id`,
  composite FK `NOT VALID`, project-aware `NULLS NOT DISTINCT` unique index) +
  ops task `0018_scope_materials_to_project.py` (`on_demand` claim-or-copy
  backfill) + a deferred `NOT NULL` contract migration.
* **Seed hook**: `ProjectService.create_project_with_admin`
  (`services/project_service.py:48`) creates the project and assigns the
  Project Admin role; this is where protocol seeding is added.

## Design decisions (agreed)

1. **System protocols → auto-copied into every project.** Not a read-only global
   library, not copy-on-first-use. Each project gets its own editable copies.
2. **Catalog is the source of truth.** Keep the existing system-org rows as a
   hidden internal catalog (`access_level='system'`), never project-scoped,
   never user-visible in project reads. Copy from it on project create + backfill.
3. **Scope both tables.** `experiment_templates` AND `experiments` get
   `project_id`.
4. **`access_level` column** (like `optimization_templates`/`jobs`), not the
   materials `NULLS NOT DISTINCT` org-global hack — self-documenting and the
   CHECK enforces the invariant.
5. **Seed synchronously** inside `create_project_with_admin` (a handful of
   INSERTs; matches how role assignment already runs there), not via the task
   queue.
6. **Two PRs**: expand now, contract in \~1 month, mirroring materials.

## Section 1 — Schema (expand phase, PR 1)

Migration `~20260728174630_add_project_id_to_protocols.sql`. All DDL idempotent
and additive.

**`experiment_templates`:**

* `ADD COLUMN IF NOT EXISTS project_id uuid` (nullable in expand).
* `ADD COLUMN IF NOT EXISTS access_level text NOT NULL DEFAULT 'system'` with
  `CHECK (access_level IN ('system','project'))`.
  **The default is `'system'`, not `'project'` — this is load-bearing.** The
  existing rows this migration runs against are the system-org catalog with
  `project_id NULL`. Defaulting to `'project'` would label them `project` while
  their `project_id` is still NULL, which violates the invariant CHECK below and
  **fails the migration on any DB that already has those rows**. Defaulting to
  `'system'` labels them correctly on add; the seed/backfill path then inserts
  `access_level='project'` rows explicitly. (New project-scoped writes always set
  `'project'` explicitly, so the column default is only ever hit by the catalog.)
* Invariant CHECK (mirrors `jobs_access_level_project_id_check` at
  `baseline.sql:2205`, matching its parenthesisation exactly):
  ```
  CHECK (
    (access_level = 'project' AND project_id IS NOT NULL)
    OR (access_level = 'system' AND project_id IS NULL)
  )
  ```
  Note this is **stronger** than the `jobs`/`optimization_templates` mirror: it
  also forbids a `system` row from carrying a `project_id`, so the catalog
  invariant ("system ⇒ project\_id NULL") is actually enforced by the constraint,
  not just by convention. The dedup index's single-system-slot reasoning (below)
  depends on this.
* Composite FK `(project_id, organization_id) → projects(id, organization_id)
  ON DELETE CASCADE`, added `NOT VALID` (existing rows are NULL/system, exempt;
  VALIDATE in contract phase). CASCADE: a project's protocols die with it.
  `NOT VALID` only skips validating *existing* rows — the cascade fires on new
  DML immediately, which is why every dependent row must end up in the same
  project as the protocol it references (see the board re-pointing below);
  otherwise deleting one project reaches sideways into another's data.
* Replace the dedup index with a project-aware one:
  `DROP` the org-wide unique index, `CREATE UNIQUE INDEX … ON
  (content_hash, organization_id, project_id) NULLS NOT DISTINCT`. Each project
  holds its own copy; the single system slot (`project_id NULL`) stays deduped;
  the fixed column list stays targetable by PostgREST `on_conflict` upsert.
* `CREATE INDEX IF NOT EXISTS idx_experiment_templates_project_id`.

**Canonical find-or-create must become project-aware (blocker if missed).**
Changing the dedup grain to include `project_id` breaks the existing canonical
lookup, which keys on `(content_hash, organization_id)` only:

* The SQL function `experiment_templates_find_by_content`
  (`baseline.sql:900-909`) filters on `content_hash = … AND organization_id = …`
  with `LIMIT 1` and no `ORDER BY`. After the backfill, every project's copy of a
  catalog protocol has the **same `content_hash` and `organization_id`**, so this
  returns an arbitrary project's row.
* `find_or_create_canonical_template`
  (`repositories/experiment_templates.py:148-230`) then may `update()` that row's
  `name`/`created_by` (lines 187-192) — a **cross-project mutation** — and the
  23505 race re-lookup (lines 219-221) has the same defect.

Fix, in the same PR as the index change — **move the lookup into app code rather
than re-signature the RPC.** Re-signaturing (adding `in_project_id`, filtering
`project_id IS NOT DISTINCT FROM`) works, but migrations run before the app
rolls out, so between the two the deployed backend calls a signature that no
longer exists. Instead:

* Leave `experiment_templates_find_by_content` untouched and unused; the contract
  migration drops it.
* `find_or_create_canonical_template` calls the unchanged 2-arg
  `generate_experiment_template_content_hash` RPC — the same function the insert
  trigger uses, so app-side and stored hashes cannot disagree — then filters on
  `(content_hash, organization_id, project_id)` itself. The `project_id is None`
  branch must use `IS NULL`, not equality, to keep matching the catalog slot.
* Thread `project_id` through `find_or_create_canonical_template` (accept it as a
  param, narrow the lookup with it, and include it in the `create()` payload with
  `access_level='project'`).

General rule this illustrates: a DB function whose signature the app depends on
cannot change in the same release as its callers. Prefer app-side logic over an
RPC when the shape is likely to move.

**`experiments`:**

* `ADD COLUMN IF NOT EXISTS project_id uuid` (nullable in expand).
* Composite FK `(project_id, organization_id) → projects(id, organization_id)
  ON DELETE CASCADE`, `NOT VALID`.
* Replace `experiments_template_params_org_unique` with project-aware
  `(template_id, parameters, organization_id, project_id) NULLS NOT DISTINCT`.
* `CREATE INDEX IF NOT EXISTS idx_experiments_project_id`.
* No `access_level` — experiments are never "system"; each inherits its
  template's project.

**Experiments upsert must be updated with the index (blocker if missed).**
`ExperimentRepository.create_experiment`
(`repositories/experiments.py:86-89`) hardcodes
`on_conflict="template_id,parameters,organization_id"`. PostgREST requires the
`on_conflict` column list to match an existing unique constraint; once the index
gains `project_id`, the 3-column arbiter no longer matches any unique index and
the upsert errors. In the same PR:

* Change the arbiter to
  `on_conflict="template_id,parameters,organization_id,project_id"`.
* Add `project_id` to the `create_experiment` payload (inherited from the parent
  template) and to the `find_by_template_and_parameters` fallback filter
  (`repositories/experiments.py:36-42`).

**Existing system-org rows** become the catalog. With the column defaulting to
`'system'` (see Section 1), the add already labels them correctly, so no data
UPDATE is strictly required by the current data. The backfill ops task still
defensively re-stamps `access_level='system' WHERE organization_id = 00…001`
(idempotent, cheap) to cover any environment that acquired a stray non-system
label. Any data write stays in the ops task, not the migration, per the
supabase-migrations rule. Catalog rows keep `organization_id = 00…001`,
`project_id NULL`.

## Section 2 — Seed-on-create + backfill (PR 1)

**Shared helper** `copy_catalog_protocols_into_project(service_client, org_id,
project_id, created_by=None)`:

* Reads catalog rows (`access_level='system'`).
* Inserts a project-scoped copy of each: same `name`, `protocol_config`,
  `parameters_schema`, `time_series_spec`, `metrics_spec`, `plot_options`,
  `description`, `source_protocol`; `organization_id = org_id`,
  `project_id = project_id`, `access_level='project'`, fresh `id`. `content_hash`
  is recomputed by the existing trigger.
* **Idempotent**: find-or-insert per catalog row — skip if a same-`content_hash`
  row already exists for `(org, project)` (guaranteed by the project-aware unique
  index; use `on_conflict` no-op or an explicit existence check).
* Uses the **service client** because catalog rows live in the system org and a
  user client can't read them (mirrors the service-repo usage already in the
  template service).

**On project creation** — `create_project_with_admin`:

* After the project + Project Admin role are created, call the helper
  synchronously with the service client.
* A failure surfaces as `AppError`, consistent with the method's existing
  role-assignment failure handling.

**Backfill ops task** `0023_seed_project_protocols.py`, `RUN_ON="on_demand"`,
batch 500:

* Stamp `access_level='system'` on the existing system-org catalog rows (the one
  data step the migration can't do).
* **Adopt each org's legacy unscoped protocols** (real `organization_id`,
  `project_id` NULL — protocols were org-wide before this change; prod has \~500
  across 25 orgs). Insert-only seeding is *not* enough: these rows are invisible
  to the project-scoped list and would block the contract `SET NOT NULL`. For
  each row, copy it into all the org's projects except the oldest, then claim the
  row itself for the oldest project. Claiming in place (rather than copy-then-
  delete) keeps its id, so `experiments` / `simulations` references survive; the
  copies preserve the org-wide visibility users had before. Copy-before-claim is
  what makes an interrupted run replay-safe — the row stays unscoped, and so
  re-selected, until its copies exist.
* **Then** seed the catalog into every project — after adoption, so a legacy
  protocol whose content matches a catalog protocol claims that project's slot
  first and the catalog upsert skips it as a duplicate instead of colliding.
* **Then** backfill `experiments.project_id` from each experiment's template.
* **Then** re-point every table that carries its own `project_id` *and*
  references a protocol, at the copy inside its own project. There are two:
  `simulation_boards.template_id` and `planned_measurements.protocol_id`. A row
  pointing at another project's protocol (its org's oldest, where the formerly
  org-wide protocol was homed) or at the catalog breaks project deletion, and the
  two FKs break it in opposite directions:

  * `simulation_boards.template_id` is `ON DELETE CASCADE` — deleting the other
    project silently deletes boards belonging to untouched projects. Prod: 40 of
    166 boards sit outside the home project, 135 point at the catalog.
  * `planned_measurements.protocol_id` is `ON DELETE RESTRICT` **and**
    `NOT NULL` — the delete is refused instead, so deleting a project fails
    outright with an FK error and the contract phase cannot delete the catalog
    rows at all. Prod exposure is currently zero (all 34 rows already sit in the
    home project), but that is a property of today's data, not an invariant.

  This requires the expand migration to lift `enforce_template_id_immutability`,
  the BEFORE-UPDATE trigger that raises on any `simulation_boards.template_id`
  change; the guard predates project scoping and is what makes these references
  unfixable. It is **replaced, not dropped**: `simulation_boards` is granted to
  `authenticated` and the UPDATE policy checks project permission but not
  columns, so the trigger was the only thing preventing a direct PostgREST
  `PATCH` from re-pointing a board at another project's protocol — re-creating
  the very cross-project reference this task erases. The replacement,
  `enforce_template_id_same_project`, changes the rule from "never re-point" to
  "may only reference a protocol in the same project" — strictly stronger, and
  satisfied by this task's re-pointing, so it never has to come off. It also
  permits references to unscoped (`project_id IS NULL`) catalog/legacy rows,
  which is what lets it be installed during expand while the backfill is still
  in flight; the contract phase's `NOT NULL` removes that branch's reach.
  `planned_measurements` has no such guard (only a `moddatetime` trigger, so
  re-pointing does bump `updated_at`).
* An experiment's project comes from **where it is used** — `experiments` ←
  `simulations` ← `study_simulation_mappings` → `studies.project_id` — not from
  its template. Inheriting the template's project would drop every legacy
  experiment into its org's oldest project, and since the study view matches
  boards to simulations by exact `template_id`, a board re-pointed to its own
  project would then never match its simulations and would render empty. The
  experiment is re-pointed at the chosen project's protocol copy too. Falls back
  to the template's project, then the org's oldest, when no study names it.
* Orgs with zero projects can't be adopted; log them as warnings. The contract
  migration must not run until every environment reports zero.
* Idempotent; `on_demand` so it's run/reviewed per environment.
* No unit test (per the no-ops-task-tests preference).

## Section 3 — Read/write path (PR 1) and contract (PR 2)

**Reads (expand — dual-source, backward compatible):**

* When a `project_id` is in scope (the in-project protocol list, post-1225),
  return that project's rows only (`access_level='project'`,
  `project_id=<project>`). No system-org union — each project has its own copies.
* The legacy org-wide union (org rows + system-org rows) stays only for callers
  not yet passing a project, so nothing breaks mid-rollout. `HIDDEN_TEMPLATE_NAMES`
  filter unaffected.
* `ExperimentTemplateRepository` / `ExperimentRepository` gain `project_id`
  filtering.
* Add `project_id` and `access_level` to `LIST_DEFAULT_COLUMNS`
  (`repositories/experiment_templates.py:18-21`) so list views can distinguish
  project-scoped rows without an `include=` round-trip.

**Writes:**

* `create_experiment_template` and `POST /protocols/parse-to-template` require a
  `project_id` and stamp `access_level='project'`.
* `create_experiment` stamps the parent template's `project_id`.
* `project_id` is resolved from the request body/path (never a URL org, per the
  fastapi-backend rule). Missing `project_id` on a project-scoped create →
  `BadRequestError` with an actionable message.

**Frontend / caller assumption.** PR #1225 (merged) moved the protocol simulator
inside projects, so its create/parse calls run in a project context. This design
makes `project_id` **required** on those endpoints. Before PR 1 merges, audit
every caller of `POST /experiment_templates`, `POST /protocols/parse-to-template`,
and experiment creation to confirm a `project_id` is supplied; any straggler that
doesn't will now get a `BadRequestError`. If a legitimate projectless caller
remains, keep it on the legacy org-wide path for the expand window rather than
forcing a project.

**Access model: protocols are project-private (intended end state), but RLS
enforcement is deliberately deferred.** Decision confirmed with the product
owner: the target model is that a protocol belongs to a project and only project
**members** should read it — the same model `optimization_templates` enforces
(project reads gated on `has_permission_via_project`). This matters because
project membership is **strictly narrower than org membership**:
`create_project_with_admin` grants a `user_project_role` only to the project
creator (`project_service.py`), so an org member is not automatically a member of
every project in that org.

However, the DB-layer RLS for that model is **not built in this effort** (neither
PR 1 nor PR 2). During the expand/contract window, project narrowing is enforced
in the **app/repository layer** (the project filter on reads), while RLS stays
org-permission-only (`has_permission_in_org`, `baseline.sql:5783/5803/5857`).

Consequences to be explicit about:

* This does **not reduce** today's protection: protocols are currently org-wide,
  so any org member can already read all of them. The app-layer project filter
  is a *new* narrowing, just not a DB-enforced one yet.
* Until the deferred RLS lands, the project-privacy guarantee is app-layer only —
  a caller that reaches `experiment_templates`/`experiments` with a user JWT but
  *without* the app's project filter (a raw PostgREST query, a future endpoint
  that forgets the filter) could read another project's protocols. This is an
  accepted, temporary gap, not the end state.
* The system-org SELECT allowance (`baseline.sql:5872`) stays through expand
  (harmless — the app no longer unions the catalog into project views) and is
  removed in the contract phase with the catalog rows.

**Deferred follow-up (tracked, not "out of scope"):** a later PR adds
project-membership RLS to `experiment_templates` and `experiments` —
`access_level='project'` reads/writes gated on `has_permission_via_project`,
mirroring `optimization_templates` (`baseline.sql:5865`). This closes the
app-layer-only gap above. It is deliberately sequenced **after** the expand +
contract migrations so the RLS predicates apply to a fully-backfilled,
`NOT NULL`-`project_id` table. Called out here so it is not lost.

**Contract phase (PR 2, \~1 month later, gated on the backfill running
everywhere):**

* `VALIDATE` the composite FKs.
* `SET NOT NULL` on `experiment_templates.project_id` and
  `experiments.project_id`.
* Drop `experiments_template_params_org_unique`, the old 3-column unique index.
  It is deliberately **left in place** by the expand migration: the deployed
  backend's `create_experiment` / batch upsert use it as their `on_conflict`
  arbiter, and dropping it before that code ships 42P10s every write.

  Keeping it is only safe **once the backfill has run**. Before that, an existing
  experiment with `project_id` NULL and a new project-scoped insert for the same
  `(template_id, parameters, organization_id)` do not conflict on the 4-column
  arbiter (NULL is a distinct value), so `DO NOTHING` cannot suppress the insert
  and the 3-column index rejects it with a 23505 — reproduced against a live DB.
  That is why ops task 0023`is`pre\_deploy`rather than`on\_demand\`: it closes
  the window inside the deploy, with old code serving until it completes.
* Delete the `access_level='system'` catalog rows (ops task). Only safe because
  ops task 0023`re-points every`experiments`, `simulation\_boards`and`planned\_measurements`row off the catalog first.`experiments`and`simulation\_boards`cascade — on prod this would otherwise destroy 135 of 166
  simulation boards — while`planned\_measurements\` restricts, which would make
  the deletion fail outright. Re-verify all three are clear before running it.
* Replace the protocol-seeding source in `create_project_with_admin`. Deleting
  the catalog turns `copy_catalog_protocols_into_project` into a silent no-op
  (it returns 0 without raising), so new projects would be created with zero
  protocols. The expand phase logs a warning on a zero count to make that
  visible; the contract PR must remove or repoint the call.
* Tighten `enforce_template_id_same_project` by dropping its
  `t.project_id IS NULL` branch. That branch exists only so the guard can be
  installed during expand, while boards may still legitimately point at an
  unscoped catalog/legacy protocol mid-backfill. Once
  `experiment_templates.project_id` is `NOT NULL` and the catalog rows are gone,
  no protocol can have a NULL `project_id`, so the branch is unreachable — but
  leave nothing implicit: drop it so the invariant reads as
  "same project, always".
* Remove the system-org fallback code: `SYSTEM_ORGANIZATION_ID`,
  `get_service_experiment_template_repository`, and the list-route union
  (`routes/experiment_templates.py:117-126`).
* Drop the system-org SELECT allowance from the RLS policy
  (`baseline.sql:5872`) — the catalog rows are gone, so the
  `organization_id = 00…001` disjunct is dead.
* Drop `NULLS NOT DISTINCT` from the dedup indexes (no NULL slot left) → plain
  `(content_hash, organization_id, project_id)` and
  `(template_id, parameters, organization_id, project_id)`.
* Keep the `access_level` column (all `'project'`) for symmetry with
  `optimization_templates` — cheaper than dropping it.

## Testing

* **Migration**: applies cleanly on a DB with existing data; project-aware dedup
  index enforces per-project uniqueness and a single system slot.
* **`project_service` unit test**: creating a project seeds N catalog protocols
  into it (service-client mock); re-invoking the helper is a no-op (idempotency).
* **Repo/service tests**: a project-scoped list returns only that project's rows
  and **excludes** another project's copy (explicit exclusion assertion, per the
  repository / e2e rules); create stamps `project_id` + `access_level`; missing
  `project_id` → `BadRequestError`.
* **Ops task**: no test (per `feedback_no_ops_task_tests`).

## Rollout summary

| PR                   | Contents                                                                                                                                                                               | Gate                                                    |
| -------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------- |
| PR 1 (expand)        | Section 1 migration (incl. project-aware `find_by_content` RPC) + Section 2 seed/backfill ops task + Section 3 project-aware dual-source read/write (incl. experiments upsert arbiter) | —                                                       |
| PR 2 (contract)      | VALIDATE FKs, `SET NOT NULL`, delete catalog rows, remove system-org fallback + SELECT allowance, simplify dedup indexes                                                               | Backfill ops task run in every environment for ≥1 month |
| PR 3 (RLS, deferred) | Add `has_permission_via_project` RLS to `experiment_templates` + `experiments`, mirroring `optimization_templates` — closes the app-layer-only project-privacy gap                     | After contract (table is `NOT NULL`-`project_id`)       |