> ## 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 13 raw data storage design # Raw Data Storage — Phase 1 Design **Date:** 2026-07-13 **Branch:** `feat/raw-data-storage` **Status:** Design approved; pending spec review + implementation plan ## 1. Purpose & scope A new **org + project-scoped raw-data subsystem** for capturing uploaded files *as-is* (provenance), designed generically enough to also serve as a general org/project file store. The first consumer is measurement ingestion: raw files are the original sources that later get processed into `cell_measurements`. Provenance is **many-to-many** — one raw file can feed many measurements, and one measurement can be built from many raw files. ### In scope (phase 1) * **Backend:** CRUD for raw-data records (upload, list by project, get, download-url, update, delete) — org + project scoped. * **Backend:** a new dedicated `raw-data` storage bucket. * **Backend:** a `measurement_raw_data_sources` join table (many-to-many). * **Backend:** bulk attach + single detach + provenance-read endpoints linking measurements ↔ raw-data records. * **SDK (`ionworks-api`):** a `RawDataClient` sub-client for upload / list / get / download-url / update / delete, plus measurement-centric attach/detach and both-direction provenance reads. * **SDK skills:** update `discover-api` and `upload-data` via the `ionworks-dev-sync` skill. ### Explicitly out of scope * Automatic raw → measurement **processing/conversion** (the ingestion pipeline that turns a raw cycler file into `cell_measurements`). Phase 1 stores the raw file and lets a caller *manually attach* an already-created measurement. * Any **web frontend UI**. * **Project-scoped RLS** (see §2 decision) — projects are a grouping/filter dimension this phase, not a database-enforced trust boundary. ## 2. Data model ### New table `public.raw_data` Org + project scoped, mirroring `material_property_datasets`. | Column | Type | Notes | | ----------------- | ------------------------------------ | ------------------------------------------------------------------------------------- | | `id` | uuid PK | `gen_random_uuid()` | | `organization_id` | uuid NOT NULL | FK → `organizations`; RLS gate | | `project_id` | uuid NOT NULL | FK → `projects`; composite `(project_id, organization_id)` FK per existing convention | | `name` | text NOT NULL | human label | | `filename` | text NOT NULL | original uploaded filename | | `content_type` | text | MIME, nullable | | `size_bytes` | bigint | nullable | | `storage_path` | text NOT NULL | `{org_id}/{id}/{filename}` | | `metadata` | jsonb NOT NULL DEFAULT `'{}'` | free-form | | `created_by` | uuid | FK → `users` | | `created_at` | timestamptz NOT NULL DEFAULT `now()` | | | `updated_at` | timestamptz NOT NULL DEFAULT `now()` | `moddatetime` trigger | Indexes: `(organization_id)`, `(project_id)`, `(created_at DESC)`. **RLS:** 4 policies (SELECT/INSERT/UPDATE/DELETE) reusing an existing permission set gated on `has_permission_in_org(organization_id, '')`. The permission verb reuses the `cell_measurement:*` set (same choice analyses and material datasets made); confirm exact verb during implementation. ### New join table `public.measurement_raw_data_sources` Many-to-many provenance link. | Column | Type | Notes | | --------------------- | ------------------------------------ | ------------------------------------------- | | `id` | uuid PK | `gen_random_uuid()` | | `cell_measurement_id` | uuid NOT NULL | FK → `cell_measurements`, ON DELETE CASCADE | | `raw_data_id` | uuid NOT NULL | FK → `raw_data`, ON DELETE CASCADE | | `created_at` | timestamptz NOT NULL DEFAULT `now()` | | | — | | UNIQUE `(cell_measurement_id, raw_data_id)` | Cascade on both FKs means deleting a measurement or a raw-data record cleans up its links automatically. RLS gates via the parent org (through `raw_data.organization_id`); confirm the exact policy join during implementation. ### New storage bucket `raw-data` Created via migration (mirroring `20260504100001_create_material_property_datasets_bucket.sql`), plus 4 `storage.objects` policies gated on `has_permission_in_org(resolve_org_id_from_path_token((storage.foldername(name))[1]), '')`. **Path convention:** `{org_id}/{raw_data_id}/{filename}` — org is always path segment 1, per every existing bucket. Project is **not** in the path. ### Design decision — org-only RLS, project is a filter dimension (confirmed) RLS (table + storage) gates on `organization_id` only. `project_id` is a required grouping/filter column, **not** a security boundary — any org member with the permission can read raw-data across all projects in the org. This matches `material_property_datasets` exactly (it carries `project_id` but no policy references it). Org isolation is DB-enforced; project isolation is application-layer (list endpoints filter by `project_id`). If projects later become a trust boundary, tightening to project-scoped RLS is a clean additive extension (the codebase already has `user_project_memberships` / `has_permission_in_project`), with the caveat that storage-object RLS cannot see `project_id` from the org-only path — so files would need a path change or a different enforcement point. Deferred. ## 3. Backend layers Mirrors the analyses trio (`analysis.py` / `analysis_bucket.py` / `analysis_service.py` / `routes/analysis.py`), the cleanest recent precedent. ### DB repository — `backend/src/repositories/raw_data.py` * `RawDataRepository(BaseRepository[RawData])`, table `"raw_data"`. * `list_by_project(project_id, organization_id, limit=100, offset=0)` with `.select("*", count="exact")` and inclusive `.range(offset, offset+limit-1)` per the repositories pagination rule; returns a paginated response with `items`, `count`, `total`. * Join-table access (either on this repo or a small `MeasurementRawDataSourcesRepository`): * `attach(cell_measurement_id, raw_data_ids)` — bulk insert with `ON CONFLICT (cell_measurement_id, raw_data_id) DO NOTHING`. * `detach(cell_measurement_id, raw_data_id)`. * `list_sources_for_measurement(cell_measurement_id, ...)` (paginated). * `list_measurements_for_raw_data(raw_data_id, ...)` (paginated). * FastAPI dep `get_raw_data_repository`. ### Storage repository — `backend/src/repositories/raw_data_bucket.py` * `BUCKET_NAME = "raw-data"`. * `build_path(org_id, raw_data_id, filename)` → `{org}/{id}/{filename}`. * `upload(...)` via `async_upload_file_to_storage` (`utils/storage_utils.py`). * `create_signed_download_url(path, expires=300)`. * `delete_paths([...])` (batched). ### Service — `backend/src/services/raw_data_service.py` Orchestrates the DB repo, storage repo, and (for attach validation) the cell measurements repo. Raises domain exceptions from `src/exceptions.py`, never `HTTPException`. **Ordering invariants (same as analyses):** * **Create:** insert DB row → upload file → on upload failure, delete the row (rollback) and raise. The DB row is the commit point; no orphaned file, no row without a file. * **Delete (raw-data record):** delete DB row → best-effort file delete. The file is a single object at a known path (`{org}/{id}/{filename}`), so there is no nested-folder orphaning risk like the analyses sweep guards against — a plain path delete suffices. Cascade removes the record's join rows. * **Delete (cell measurement) — key many-to-many invariant:** deleting a measurement cascades away its `measurement_raw_data_sources` rows but MUST NOT delete any raw-data record or its file. A raw file may still be attached to other measurements and is an independent, org/project-owned asset. Detaching or deleting a measurement never touches `raw_data` or the `raw-data` bucket. **Attach:** validate the measurement exists and is in-org; validate every `raw_data_id` exists and is in-org (else `BadRequestError` naming the offending id); bulk-insert join rows idempotently (dupes skipped). Return the measurement's resulting source list. ### Routes — `backend/src/routes/raw_data.py` (`prefix="/raw_data"`) + measurement sub-routes Org from `Depends(get_current_organization_id)`; never in the path. All updates use `PATCH`. Thin handlers; domain exceptions propagate to the global handler. Raw-data CRUD: * `POST ""` — multipart `UploadFile` + `Form` fields (`project_id`, `name`, `metadata` JSON) → create record + upload. The service derives `filename`, `content_type`, and `size_bytes` server-side from the FastAPI `UploadFile` (`upload.filename`, `upload.content_type`, and the read byte length) and writes them to the row — the client does not supply them. * `GET ""` — paginated list; **required** `project_id` query param; `limit` `Query(ge=1, le=100)`. * `GET /{id}` — get one. * `GET /{id}/download-url` — `{"url": ...}` signed URL. * `PATCH /{id}` — partial update of `name` / `metadata`. * `DELETE /{id}` — delete record + file. * `GET /{id}/cell_measurements` — reverse provenance read (paginated). Provenance attach/detach (measurement-centric, defined in `routes/cell_measurements.py` for discoverability — the SDK in §4 mirrors this by putting the methods on `CellMeasurementClient`): * `POST /cell_measurements/{measurement_id}/raw_data` — body `{"raw_data_ids": [uuid, ...]}`. Bulk attach; idempotent (already-linked ids skipped). Returns the measurement's source list. * `DELETE /cell_measurements/{measurement_id}/raw_data/{raw_data_id}` — detach one pair. * `GET /cell_measurements/{measurement_id}/raw_data` — list a measurement's raw-data sources (paginated). ## 4. SDK (`ionworks-api`) New sub-client `ionworks/raw_data.py` → `RawDataClient(client)`, attached as `client.raw_data` in `client.py` (alongside the existing sub-client block). New `RawData` pydantic model in `models.py` (`ConfigDict(extra="allow")`, minimal required fields: `id`, `project_id`, `name`, `filename`), exported from `ionworks/__init__.py`. ### `RawDataClient` methods * `upload(project_id, file, name=None, metadata=None) -> RawData` — accepts a path / `os.PathLike` / open file-like, resolves to a binary handle, builds `data={"project_id", "name", "metadata"(JSON)}` + `files={"file": (filename, handle, content_type)}`, calls `self.client.upload_multipart("/raw_data", data=..., files=...)`, returns `RawData(**response)`. Template: `ModelClient.upload_custom` (`custom_model.py:229`). * `list(project_id, limit=..., offset=...) -> PaginatedList[RawData]` — via `_build_endpoint` / `_parse_list_response`. * `get(raw_data_id) -> RawData`. * `download_url(raw_data_id) -> str` — GET `/raw_data/{id}/download-url`, returns `response["url"]`. * `update(raw_data_id, name=None, metadata=None) -> RawData` — PATCH. * `delete(raw_data_id) -> None`. * `list_measurements(raw_data_id) -> PaginatedList[CellMeasurement]` — reverse provenance read. ### Measurement-centric attach/detach (on `CellMeasurementClient`) Co-located with the measurement client, where the relationship reads naturally. * `attach_raw_data(cell_measurement_id, raw_data_ids: list[str]) -> None` — POST the bulk body `{"raw_data_ids": [...]}`. Accepts a list; a single id is `[id]`. Idempotent (server skips dupes). * `detach_raw_data(cell_measurement_id, raw_data_id) -> None` — DELETE one pair. * `list_raw_data(cell_measurement_id) -> PaginatedList[RawData]`. ### SDK skills After the client lands, run the `ionworks-dev-sync` skill (`packages/ionworks-api/.claude/skills/ionworks-dev-sync/`) to update: * `packages/skills/skills/discover-api/SKILL.md` — add the `client.raw_data` sub-client row + the raw-data ↔ measurement hierarchy. * `packages/skills/skills/upload-data/SKILL.md` — add the raw-data upload flow and the measurement attach/detach flow. * The dev-sync SKILL.md's own file list gets a raw-data mention. ## 5. Error handling Follows `.claude/rules/fastapi-backend.md`: * Services raise domain exceptions (`NotFoundError`, `BadRequestError`, `ConflictError`), never `HTTPException` or bare `ValueError`. * Missing raw-data / measurement → `NotFoundError(resource_type, id)`. * An attach body referencing an id in another org or nonexistent → `BadRequestError` naming the offending id. * Upload failures after row insert → roll back the row, raise `AppError` / `ExternalServiceError` as appropriate. * SDK surfaces the standardized `{error_code, message, detail}` body as `IonworksError` (`errors.py`). Bulk attach is idempotent server-side (2xx, `ON CONFLICT DO NOTHING` — dupes are silently skipped, never a 409), so the SDK does not need to handle a 409 on attach; any 409 handling would be belt-and-suspenders, not an expected path. ## 6. Testing * **Repositories:** pagination shape (`count`/`total`), `list_by_project` filtering, join-table idempotent insert + cascade. * **Service:** create rollback-on-upload-failure; delete removes row + sweeps file; attach validates cross-org ids and skips dupes. * **Routes:** multipart create; required `project_id` on list; bulk attach body; detach; provenance reads both directions. Run via `just test-backend`. * **Search/RLS:** if applicable, an integration test that org A cannot read org B's raw-data rows/files (`just test-search`, real local Supabase). * **SDK:** mocked-HTTP tests (`packages/ionworks-api/tests/test_raw_data.py`) following `tests/test_measurement_types.py` — assert endpoint + payload for upload (`upload_multipart` kwargs), list, attach (bulk body), detach. ## 7. Migrations & ops * All DDL (table, join table, indexes, RLS) goes in `supabase/migrations/` — additive, idempotent (`IF NOT EXISTS`, `DROP POLICY IF EXISTS` before `CREATE POLICY`), per `.claude/rules/supabase-migrations.md`. * **Bucket creation is the one sanctioned INSERT.** Creating the `raw-data` bucket is `INSERT INTO storage.buckets (...) ON CONFLICT (id) DO NOTHING`, exactly as `20260504100001_create_material_property_datasets_bucket.sql` does. This is technically DML, but it is *not* a data backfill of a domain table — it is the only supported way to declare a bucket, it is idempotent, and it is the accepted precedent. The migrations rule's DML ban targets domain-table backfills, which this is not. * No backfill needed (new tables). If any later becomes necessary it goes in an ops task, not a migration. ## 8. Open items for implementation * Confirm the exact permission verb reused for RLS (`cell_measurement:*` vs a new `raw_data:*` set). * Confirm the join-table RLS policy join expression (via `raw_data` → `organization_id`).