QTI authoring asset pipeline: media for the graphic/media interaction kinds

Status: accepted — amended 2026-06-15 (upload mechanism: base64-through-the-API → resumable TUS direct-to-Storage + server-side finalize; see Amendment).

Builds on ADR-0021 (native QTI item authoring), ADR-0009 (imported-standard boundary), ADR-0024 (decision 4: server-authoritative asset dimensions), and the existing Supabase Storage usage for external packages (package-storage-adapter.ts, supabase-package-storage-adapter.ts, content-runtime’s storage reader). It unblocks the remaining authorable interaction kinds.

The tension (named, not hand-waved)

ADR-0021 §5 native authoring now covers eleven interaction kinds end-to-end (choice, inline-choice, order, text-entry, extended-text, slider, upload, associate, match, hottext, gap-match). The eight that remain are exactly the ones that reference a media file the teacher must supply:

• image-backed (7): hotspotInteraction, selectPointInteraction, graphicAssociateInteraction, graphicGapMatchInteraction, graphicOrderInteraction, positionObjectStage, and drawingInteraction (background image);
• media-backed (1): mediaInteraction (audio/video).

The engine delivers and scores all of these, and emergent can already import them — but it has no way for a teacher to upload an image/audio/video in the native authoring flow, and the QTI those kinds emit must carry a resolvable data/src href to that media. So the gap is an asset pipeline: upload → store → reference from the item → serve to the learner → (later) package on export.

This is pre-launch ([[feedback_no_legacy_pre_launch]]); nothing forces a compatibility compromise. emergent already runs Supabase Storage for external packages, so the mechanism exists — this ADR is mostly about reusing it for authored-item assets, plus the access + reference model.

Decision

1. Assets live in Supabase Storage; content-addressed + offering-scoped metadata

A dedicated bucket (item-assets) holds authored-item media. Objects are content-addressed (sha256 of the bytes) and scoped by offering. A new assessment_item_asset row records (id, offering_id, sha256, mime_type, byte_size, width, height, storage_key, original_filename, created_by_person_id, …) — the metadata + authz anchor. Content-addressing dedups identical uploads (unique (offering_id, sha256)); offering-scoping bounds access and cleanup.

width/height are the intrinsic pixel dimensions, read server-side and are the authoritative coordinate basis for the graphic interaction kinds (the authored shape coordinates live in this pixel space), so they are server-derived, never client-supplied (ADR-0024 decision 4). They are nullable: every image carries real dimensions (an image we cannot measure is rejected); audio/video are null (no image coordinate basis).

(Schema change → migration → DB reset is Anton’s step; [[feedback_never_drop_database]], [[feedback_never_edit_generated_migrations]].)

2. Upload surface — resumable (TUS) direct-to-Storage + server finalize

(Amended 2026-06-15; supersedes the original base64/multipart-through-the-API upload.)

The teacher’s browser uploads the bytes directly to Supabase Storage over the TUS resumable protocol (Uppy @uppy/core + @uppy/tus), to /storage/v1/upload/resumable, authorized by the teacher’s own session JWT — the pattern already proven in production on this exact Supabase Helm chart. Uploads are chunked (6 MiB) and resumable, so 100 MiB video survives a flaky connection, and the bytes never inflate (+33% base64) or buffer in the API.

• Object key is {offeringId}/{uuid}.{ext} — an opaque per-upload key, not the content hash, because the client cannot be trusted to compute the content address.
• Upload authorization = RLS policies on storage.objects (the documented Supabase extension point — adding policies, not altering the table): INSERT + SELECT gated by bucket_id = 'item-assets' AND public.can_manage_offering_assets((storage.foldername(name))[1]::uuid); UPDATE denied (objects are immutable); DELETE restricted to managers. The predicate reuses the app_metadata.authorization.grants[] claim that every public table policy already uses, via a SECURITY DEFINER helper in the public schema (can_manage_offering_assets(uuid)), which joins public.offering to resolve the org-scoped roles. No function, table, or ALTER ever lands in a Supabase-owned schema (see the hard infra rule below).
• Finalize — POST /workflow/qti-item-assets (JSON metadata only: { offeringId, objectName, contentType, originalFilename }; tiny body), the same grant gate as the authoring route. It reads the uploaded object back from Storage and computes the authoritative sha256 (a streaming hash — bounded memory even at 100 MiB) and, for images, the intrinsic width/height from the header bytes (ADR-0024 decision 4 holds even though the API never received the upload). It then dedups against the unique (offering_id, sha256) index — on a hit, deletes the redundant just-uploaded object and returns the existing asset — otherwise inserts the row. Returns { assetId, url, mimeType, width, height }. Idempotent on content.

Hard infra rule: never DDL a schema we don’t own

emergent migrations may only:

• INSERT/UPDATE data in Supabase-owned schemas (e.g. the storage.buckets rows), and
• CREATE POLICY ON storage.objects (the sanctioned access-control extension point).

They must never emit structural DDL on storage / auth / vault / realtime — no ALTER TABLE, no ENABLE ROW LEVEL SECURITY (storage-api already enables it on storage.objects), no CREATE FUNCTION/TABLE/TRIGGER in those schemas. Our regenerate-from-scratch discipline (DROP SCHEMA public CASCADE; [[feedback_never_edit_generated_migrations]]) plus storage-api upgrades make such DDL dangerous. All emergent-owned logic (the can_manage_offering_assets helper) lives in public.

3. Items reference assets by a resolvable URL (not embedded bytes)

The authored item carries the asset as an absolute URL computed from a configured base (mirroring the CASE / OneRoster / credential base-URL convention — computed, not stored in the document): the graphic interactions’ <object data="…"> / image href and mediaInteraction’s media object point at it. The normalized document stays pure QTI; nothing about emergent storage leaks into it beyond the URL.

4. Delivery access = an authenticated, offering-scoped serving endpoint

GET /workflow/qti-item-assets/:assetId checks the caller is entitled to the asset’s offering (enrolled learner or teacher) and 302-redirects to a short-lived signed Storage URL so the browser/<img>/media element fetches the bytes directly. The endpoint is the authz gate; assets stay private to the offering. Moving learner reads to a direct RLS-gated GET (a storage.objects SELECT policy keyed on offering_membership) was considered and deferred — signed-URL serving is the blessed private-serving pattern and is CDN/cache-friendly; revisit only if asset-GET volume justifies it.

5. Content-Package export integration (forward reference to ADR-0021 §3)

When an item/test is exported as a QTI Content Package, asset URLs are rewritten to package-relative resource paths and the bytes are included as <resource> files in the manifest. Authoring stores URLs; export packages them. No change needed here now — recorded so the export lane honours it.

6. Lifecycle

Assets are offering-scoped and content-addressed (dedup). Objects are immutable (a changed image is new bytes → new hash → new row; the published item keeps resolving the old content). Orphan collection (an asset no item references) is deferred — assets persist with their offering; a sweep can be added later.

7. Scope + sequencing

• v1 — images (image/png, image/jpeg, image/gif, image/webp, image/svg+xml): unblocks the 7 image-backed kinds. drawingInteraction also supports a blank canvas (no asset) as a no-upload sub-case.
• v2 — media (audio/mpeg|ogg|wav, video/mp4|webm allow-list): unblocks mediaInteraction.

Bucket file_size_limit is 100 MiB (104857600) to accommodate video; the allow-list + limit are mirrored by ASSET_MIME_EXTENSIONS / MAX_ASSET_BYTES in the asset service. Each interaction kind is a per-kind authoring slice (draft + builder + UI) with an asset picker that calls §2, and the graphic kinds additionally use the coordinate/shape editor (its own design).

Consequences

• emergent gains an item-assets bucket + assessment_item_asset table, a finalize endpoint, an authenticated serving endpoint, storage.objects RLS policies + a public can_manage_offering_assets helper, and (later) export packaging of assets.
• New client dependency: @uppy/core + @uppy/tus in teacher-portal (upload UI, progress, retry). The browser uploads with its Supabase session token; our RLS policies authorize it.
• No legacy: the base64/multipart-through-the-API upload path is removed outright (pre-launch).
• The finalize readback is an internal cluster hop at authoring time (server → Storage); for 100 MiB media the hash is computed by streaming, not buffering. Acceptable for an authoring-time, low-frequency operation; it is the price of keeping sha256 (and image dims) server-authoritative under a client-driven upload.
• A schema migration is required (Anton resets the dev DB); the storage RLS policies + bucket size/MIME live in the storage_buckets custom migration (a migration-sql builder, so migration-drift.test.ts covers them).
• The graphic interactions additionally need a coordinate/shape authoring UX — its own design, now built (ADR-0021 §5 follow-up).
• Until built, the 8 kinds stay importable, so platform coverage remains the engine’s full palette.

Amendment — 2026-06-15: resumable (TUS) upload + server finalize

Why the upload mechanism changed. The original decision 2 pushed bytes base64-encoded inside a JSON body through the API (mirroring the external-package importer). For mediaInteraction (v2) that is the wrong shape: base64 inflates payloads +33%, the whole body buffers in the functions pod, and there is no resumability for large video. Four facts settled the redesign:

1. Everything transits Istio anyway (even dev, even the storage path), and the gateway imposes no request-body cap — so “take bytes off Istio” was never the lever. Istio needs no change for either a through-the-API or a direct-to-Storage upload.
2. The Supabase Helm chart’s preflight shim is already TUS-ready (it enumerates Tus-Resumable, X-Upsert, Upload-Offset, Upload-Length, Upload-Metadata, Upload-Concat and allows PATCH/HEAD on /storage/v1/), and direct resumable upload is already proven in production on this chart. So TUS direct-to-Storage works with zero gateway changes.
3. Authorization is the documented storage.objects RLS model — adding policies (allowed), not structural DDL (forbidden; see the hard infra rule). The policy predicate reuses the existing grant claim, so it is consistent with every public table policy and needs only a public helper.
4. Server-authoritative dims + content hash (decision 1 / ADR-0024 decision 4) are preserved without the API ever receiving the bytes, by a finalize readback.

Net: a simpler, uniform path for all asset kinds (images + media), no base64, no in-API buffering, resumable to 100 MiB, with authz in our policies/code and no DDL on any Supabase-owned schema.

Decisions for Anton

Decisions 1–5 below were accepted on 2026-06-15; the asset-pipeline foundation, the graphic coordinate/shape editor, and the v2 media kind have since been built on the QTI authoring branches. Decisions 6–9 are the 2026-06-15 amendment (upload mechanism), accepted by Anton on 2026-06-16 and built on qti-item-assets (validate:full 12/12, test:integration 80/0 against a real storage-api):

1. Storage = a Supabase item-assets bucket, content-addressed + offering-scoped. (accepted)
2. Reference model = absolute URL resolved by an authenticated, offering-scoped serving endpoint (signed-URL 302 as the fetch mechanism), not embedded bytes / not world-readable. (accepted)
3. New assessment_item_asset table (metadata + authz anchor; + server-authoritative width/height per ADR-0024) → migration + DB reset. (accepted)
4. Sequencing: images first (v1, 7 kinds, + blank-canvas drawing), media second (v2). (accepted)
5. The graphic kinds’ coordinate/shape editor is a separate design. (accepted; built)
6. Upload = resumable TUS direct-to-Storage (Uppy), user-JWT-authorized, {offeringId}/{uuid}.{ext}, replacing the base64-through-the-API path (removed, no legacy). (amendment — accepted)
7. Authz = CREATE POLICY ON storage.objects only (INSERT/SELECT via the grant claim through a public SECURITY DEFINER helper; UPDATE denied; DELETE managers-only) + the hard rule: no structural DDL on Supabase-owned schemas. (amendment — accepted)
8. Server finalize does the readback → authoritative sha256 (streaming hash, uniform for images + media) + image dims + dedup + row insert. (amendment — accepted; the alternative — trusting a client-computed sha for media only — was rejected for integrity.)
9. Bucket ceiling = 100 MiB. (amendment — accepted)