> ## 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 18 material property source tracking design

# Material property & analysis source tracking — design

**Date:** 2026-07-18
**Status:** approved (pending spec review)

## Problem

Material property datasets and analyses are often *computed* from a pipeline
(orchestrated or simple) or from another analysis, but the platform records no
provenance. A user looking at a conductivity-vs-temperature dataset cannot tell
whether it was fitted from a pipeline run, derived from an analysis, or uploaded
by hand. We want to record, per dataset and per analysis, **where it came from**,
and link back to that source where one exists.

## Scope

* Add source tracking to two tables: `material_property_datasets` and `analyses`.
* Backend: migration, Pydantic models, repositories, services (with source
  existence validation).
* Frontend: display source on both detail views; allow editing source on the
  material property dataset (analyses have no create/edit form today, so their
  source is read-only in the UI and set only via the backend/API).

Out of scope: backfilling source for existing rows (none is knowable);
automatically populating source when a pipeline writes a dataset (a follow-up —
this PR adds the columns and the manual/API path).

## Data model

> **Design revision (superseded the original polymorphic model).** This feature
> was first built with a polymorphic `source_type` (text) + `source_id` (uuid,
> no FK) pair, with existence validated in the service layer against the RLS
> user client. It was then switched to **three optional, mutually-exclusive real
> foreign keys** before the migration was ever deployed. Real FKs give DB-level
> referential integrity and `ON DELETE SET NULL` semantics, and remove the need
> for a service-layer existence pre-check (the DB rejects a bad id directly). The
> sections below describe the shipped FK model; the polymorphic rationale is
> retained at the end of this section as historical context.

Four new **nullable** columns added to **both** `material_property_datasets` and
`analyses`:

| Column                      | Type                                                    | Meaning                                                                                                   |
| --------------------------- | ------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- |
| `source_pipeline_id`        | `uuid` FK → `pipelines(id)` `ON DELETE SET NULL`        | The dataset/analysis was computed from this pipeline run.                                                 |
| `source_simple_pipeline_id` | `uuid` FK → `simple_pipelines(id)` `ON DELETE SET NULL` | Computed from this simple pipeline.                                                                       |
| `source_analysis_id`        | `uuid` FK → `analyses(id)` `ON DELETE SET NULL`         | Computed from another analysis.                                                                           |
| `source_label`              | `text`                                                  | Free-text human note (e.g. "fitted from HPPC, Jan 2026 batch"); used when the source has no linkable row. |

### Why three exclusive real FKs

A record has **one** origin, so the three FKs are mutually exclusive: a single
`CHECK` on each table enforces that at most one is non-null. Real FKs give what
the polymorphic pair could not — DB-level existence + org-scope enforcement and
`ON DELETE SET NULL`, so deleting a source pipeline/analysis simply nulls the
breadcrumb rather than leaving a dangling id. There is no separate provenance
join table (the field is read inline, per-row, by primary key).

The `manual` marker from the original design is **dropped**: with FKs, "all three
null" already means "no linked source", and a hand-entered provenance note lives
in `source_label` alone. `source_label` is unconstrained free text — allowed even
when every FK is null (a bare human note with no structured source is valid).

```
-- single-source CHECK on each table:
( (source_pipeline_id IS NOT NULL)::int
+ (source_simple_pipeline_id IS NOT NULL)::int
+ (source_analysis_id IS NOT NULL)::int ) <= 1
```

The analysis self-cycle rule (an analysis may not cite itself) is enforced in
the service layer, since a CHECK cannot cleanly compare against the row's own
generated id across all write paths.

### Migration

* One additive migration `supabase/migrations/20260718000001_add_source_tracking.sql`
  (revised in place from the polymorphic version — never deployed, so no
  expand-and-contract needed).
* `ALTER TABLE ... ADD COLUMN IF NOT EXISTS` for all four columns on both tables.
* FK and CHECK constraints added idempotently via the
  `DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN NULL; END $$;` form.
* **No index** on the FK columns. There is no planned reverse lookup ("find all
  datasets derived from pipeline X") in this PR; source is read forward, per-row,
  by primary key. Omission is intentional.
* **DDL only** — no data backfill (per the supabase-migrations rule).

### Historical: why the original design was polymorphic

The first cut used `source_type` + `source_id` to avoid "three FK columns"
(considered a smell) and to scale to future source types without a migration. It
accepted no DB referential integrity, recovering only a consistency CHECK, and
validated existence in the service layer via the RLS user client. This was
reversed in favor of real FKs (above) for DB-enforced integrity and simpler
service code.

## Backend

### Pydantic (`backend/src/pydantic_models/`)

* `cells.py` — add `source_type` / `source_id` / `source_label` to
  `MaterialPropertyDatasetBase`, the read model, and `UpdateMaterialPropertyDataset`.
* `analysis.py` — add the same three to `AnalysisBase`, `Analysis`,
  `CreateAnalysis`, `UpdateAnalysis`.
* Introduce a `SourceType` `StrEnum` (`pipeline`/`simple_pipeline`/`analysis`/
  `manual`). There is **no** existing shared enum module spanning cells +
  analysis, so this is a **new** small module (e.g.
  `backend/src/pydantic_models/sources.py`) imported by both `cells.py` and
  `analysis.py` — not a reuse of something that doesn't exist.
* A model validator on each mirrors the DB CHECK: `source_id` required iff
  `source_type` is FK-bearing; forbidden for `manual`/`None`. Keeps API errors
  actionable before they hit the DB.
* **Public SDK parity**: the `Analysis` model is mirrored in
  `packages/ionworks-api/ionworks/models.py` (which already carries an
  `AnalysisType` StrEnum sibling of `KNOWN_ANALYSIS_TYPES`). If the source fields
  are exposed through the public API models, add the three fields there and a
  sibling `SourceType` enum, keeping the public package in lockstep (per the
  schema/pipeline parity rule). Material property datasets: mirror only if they
  are part of the public SDK surface — confirm during implementation.

### Repositories

* `material_property_datasets.py` (`create_property`) and `analysis.py`
  (`create_analysis`) thread the three fields through the insert dict (additive —
  they already build dicts from the model).
* No new list-method behavior; pagination rules unaffected.

### Services (source existence validation)

> **Superseded by the FK model.** With real FKs the service no longer does an
> RLS existence pre-check: the DB rejects a dangling id with a foreign-key
> violation (SQLSTATE 23503), which a `translate_source_fk_violation()` context
> manager wrapping the create/update converts into a `NotFoundError`. The
> service only enforces the *shape* (at most one FK set, canonical UUID) and the
> analysis self-cycle rule. The original RLS-pre-check design is retained below
> for context.

Both create/update paths validate that a referenced source **exists and is
visible to the caller** before persisting. Note: `pipelines` has **no
`organization_id` column** (its RLS is project-scoped via
`has_permission_via_project`), while `simple_pipelines` and `analyses` do carry
`organization_id`. So validation cannot be a uniform explicit org filter.

Validation contract: **"the source row is visible to the requesting user."**
This is enforced by doing the lookup through the **user (RLS) supabase client**
that these repositories already use (e.g. `analysis.py` uses
`get_supabase_user_client`). Under RLS, a `get_by_id(source_id)` that returns
`None` means "does not exist OR the caller has no permission" — exactly the
guarantee we want, and it works for all three source types without needing an
`organization_id` column on `pipelines`.

* `material_property_dataset_service.py` (`create`, `update`) and
  `analysis_service.py` (`create`, `update`): when `source_type` is
  `pipeline` / `simple_pipeline` / `analysis`, fetch `source_id` via the
  corresponding repo's existing by-id method **using the RLS user client**.
  * Returns `None` (missing or not permitted) → `NotFoundError(<source_type>, source_id)`.
  * `manual` → no lookup.
* The existing by-id methods suffice — `PipelineRepository.get_by_id`,
  `SimplePipelineRepository.get_by_id`, `AnalysisRepository.get_analysis` all
  fetch by id and rely on RLS for scoping. **No new org-filtered repo methods are
  required**; the earlier idea of passing `org_id` into these lookups is dropped
  because it is neither available (pipelines) nor necessary (RLS covers it).
* A small private helper `_validate_source(source_type, source_id)` per service
  (thin, no cross-service coupling) dispatches to the right repo. The relevant
  source repositories (`PipelineRepository`, `SimplePipelineRepository`,
  `AnalysisRepository`) are injected where needed, each constructed with the same
  user client the service already holds.
  * **RLS correctness (implementation note):** `PipelineRepository` and
    `SimplePipelineRepository` have no user-client factory of their own — the
    implementer MUST build them from a user (RLS) client the service already
    holds (e.g. `self.<existing_repo>.supabase`), never a service client.
    Instantiating them with a service client would bypass RLS and defeat the
    "visible to the caller" guarantee this validation depends on.

### Routes

No signature change — request bodies widen via the updated Pydantic models
(PATCH partial-update semantics already in place). Domain exceptions propagate
to the global handler.

## Frontend

### Material property datasets (createAsyncThunk slice)

* `redux/types/material-property-dataset.ts` — add the three fields to
  `MaterialPropertyDataset`.
* `redux/slices/model/material-property-datasets-slice.ts` — widen
  `updateMaterialPropertyDataset` / `updateProperty(patch)` to carry
  `source_type` / `source_id` / `source_label`.
* `sections/materials/material-detail-container.tsx` — add a **Source** column to
  the datasets grid rendering `source_label` (fallback: a humanized
  `source_type`) as a `RouterLink` to the source when `source_type` +
  `source_id` resolve to a known route (`paths.dashboard.pipelines.detail`,
  `paths.dashboard.<...>.analysisDetail`); plain text otherwise. Simple
  pipelines have no dedicated detail route today — render as a labeled chip with
  no link.
* `sections/materials/material-property-dataset-edit-dialog.tsx` — add source
  fields to the Zod schema, a `source_type` select + `source_label` text input +
  a `source_id` input (entity picker is a follow-up; a plain uuid field is
  acceptable for v1, disabled/cleared when type is `manual`), and include them in
  the patch diff.
* The upload dialog (`material-property-dataset-upload-dialog.tsx`) may optionally
  accept a source on manual creation; v1 can default new uploads to
  `source_type = 'manual'` and leave richer selection to the edit dialog.

### Analyses (RTK Query api) — read-only

* `redux/api/analyses-api.ts` — add the three fields to the `Analysis` interface.
* `sections/cell/analysis-details-container.tsx` — surface source alongside the
  existing "View source measurement" provenance block (lines \~264–329): render a
  **Source** row with the `source_type` chip, `source_label`, and a `RouterLink`
  when the source resolves to a route. No edit UI (no analysis form exists).
* `components/cell/measurement-analyses-card.tsx` — optional: show a small
  `source_type` chip if present; low priority.

### Link resolution rule (shared)

A small helper maps `(source_type, source_id, project_id)` → optional href:
`pipeline` → `paths.dashboard.pipelines.detail(project_id, source_id)`;
`analysis` → analysis detail path; `simple_pipeline` / `manual` / unresolved →
no link. Render defensively — if the target 404s the label still shows.

## Testing

* **Migration**: applies cleanly and is idempotent (re-run no-ops); CHECK rejects
  inconsistent rows.
* **Backend service tests**:
  * valid `pipeline` / `simple_pipeline` / `analysis` source resolves and persists;
  * non-existent `source_id` → `NotFoundError`;
  * source not visible to the caller under RLS (repo `get_by_id` returns `None`)
    → `NotFoundError` (mock the repo to return `None`);
  * `manual` + non-null `source_id` → validation error (Pydantic);
  * FK-bearing type + null `source_id` → validation error.
* **Repository round-trip**: create with source fields, read them back on both
  tables.
* **Frontend unit**: dataset slice `updateProperty` sends the source fields
  (`makeTestStore` + `mockAxios`); link-resolution helper returns the right href
  per `source_type` and `null` for `manual`/unknown.
* **Frontend E2E** (if a materials E2E path exists): edit a dataset's source,
  assert it displays with a working link.

## Rollout

Single additive PR. Columns are nullable and default null, so existing rows and
existing create calls are unaffected. Auto-population of source from pipeline
writes is a deliberate follow-up.