​

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 0023ispre_deployrather thanon_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 0023re-points everyexperiments, simulation_boardsandplanned_measurementsrow off the catalog first.experimentsandsimulation_boardscascade — on prod this would otherwise destroy 135 of 166 simulation boards — whileplanned_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