Skip to main content

​
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:

​
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).
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.
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.