Customer prompt provenance

For functional acceptance steps, see the PR2 QA runbook.

Prompt provenance records which editable fields came from an author edit, a prefill, or a verified reuse. It stores hashes and bounded field metadata, while prompt text remains in existing media storage. This layer adds no paid prompt analysis, model dispatch, analysis budget, or consumer of analysis results.

Brisket freezes a submission identity and edit stamps for a logical generation request. Sirloin validates them against the resolved workflow and unmerged inputs. Only accepted media becomes a parent. Retries reuse the submission identity; conflicting payloads cannot replace it. Capture failure does not fail an otherwise successful generation.

Four-table schema

apps/sirloin/internal/app/migrate/schema/184_prompt_provenance.sql creates these relations after media migration 178 and main migrations 179–183 (funnels, Primer payment type, asset-root hardening, and billing obligation support). The provenance migration uses plain SQL without custom functions or triggers. It never creates the removed capture-gap, deletion-receipt, or analysis tables.

erDiagram
  PROMPT_SUBMISSIONS ||--o{ PROMPT_PARENTS : "user_id + submission_id FK, CASCADE"
  PROMPT_OWNERS ||..o{ PROMPT_SUBMISSIONS : "owner guard, application link"
  MEDIA ||..o| PROMPT_PARENTS : "media_id, application link"
  CHARACTERS ||..o{ PROMPT_SUBMISSIONS : "character_id, application link"
  CHARACTERS ||..o| PROMPT_CHARACTER_FENCES : "character_id, retained fence"
  PROMPT_OWNERS { text user_id PK boolean capture_enabled timestamptz deleted_at bigint check_generation timestamptz check_due_at text check_outcome }
  PROMPT_SUBMISSIONS { text user_id PK uuid submission_id PK uuid character_id text contract_fingerprint bytea payload_fingerprint jsonb fields text status }
  PROMPT_PARENTS { text user_id PK,FK uuid submission_id PK,FK uuid media_id PK,UK }
  PROMPT_CHARACTER_FENCES { uuid character_id PK text user_id timestamptz last_deleted_at }
  MEDIA { uuid id PK timestamptz prompt_birth_at boolean prompt_birth_trusted boolean prompt_capture_invalidated }
  CHARACTERS { uuid id PK timestamptz deleted_at }

Only the submission-to-parent relationship above is a SQL FK. Dotted edges show application checks, not database constraints. Owner and character fences outlive deleted entities. Erased submissions retain their owner-scoped request identity and scrub their content metadata. Their character identity can become a zero-UUID tombstone, so a character FK would conflict with that lifecycle.

Accepted-parent writes lock and validate live owner-scoped media. The media deletion transaction removes parent references and marks a submission unavailable after its final parent disappears. Soft deletion also sets the retained media’s prompt_capture_invalidated marker. Clearing deleted_at during maintenance does not clear this marker, so delayed acceptance cannot recreate its provenance. Surviving siblings and genuinely new media remain eligible. A raw maintenance delete bypasses that lifecycle; it must not replace the application operation. Private reads independently recheck the live media and character.

The submission PK supports owner-scoped lookups and parent FK cleanup. The unique parent media_id supports deletion and source lookup. The owner due index supports bounded reconciliation. There is no paid-analysis queue index in this layer.

Privacy and replay boundaries

Storage proves exact values against current source fingerprints before granting one-hop reuse. It withholds private reads without a fresh owner check. Source mutation, erased requests, missing parents, owner deletion, and character fences prevent stale reuse.

Media birth uses the database clock. Existing rows receive an untrusted migration boundary with a stable default; new inserts receive a trusted clock_timestamp() birth. Historical rows cannot prove that they postdate a character deletion. A restored character alone cannot revive pre-deletion provenance.

Owner deletion stops enrollment, writes its durable tombstone, and scrubs provenance in one transaction. Repeated deletion of the same owner remains idempotent. Character deletion retains its fence, including after a restore. Lock ordering and the archived concurrency regressions remain part of this contract.

Two agreed capabilities are removed: durable capture-gap counts, and detection of a reused deletion event ID across different owners. Issuer and event IDs remain bounded RPC inputs; they are not persisted receipts. An accepted generation without valid capture may therefore have no provenance row.

Service access and rollout

Clerk deletion events pass Svix signature verification before Brisket calls DeletePromptOwnerProvenance. A dedicated server caller sends the existing PROMPT_DELETION_HANDOFF_TOKEN as authorization: Bearer .... Sirloin fails closed when its matching SIRLOIN_PROMPT_DELETION_TOKEN is absent or mismatched. The same method-scoped credential protects GetProvenPromptFields; the Brisket procedure derives the requested owner from its authenticated session. Generic customer-facing RPC calls receive no deletion credential.

Brisket returns success to Clerk only after the deletion RPC succeeds. Unavailable configuration, RPC failure, or database failure preserves Clerk’s retry. There is no success response that skips deletion.

Pre-merge gate: the authenticated RPC must already be live in production before PR2 merges. This local implementation does not satisfy that gate. Release the server capability through an approved prerequisite delivery before switching the Brisket webhook. Keep the existing handler active until the server is verified. Do not move prompt deletion back into the media-only PR1 or deploy automatically.

Before deploying, verify that the existing Sirloin application database role can use the tagging schema and perform the provenance reads and writes required by capture and deletion. The migration does not install a separate runtime grants script.

The provenance migration 184 defines the final schema directly, including the media invalidation marker and no redundant parent fingerprint. Existing previews that recorded provenance as migration 181 require verified schema and migration-ledger reconciliation before deploying a target-184 Sirloin binary. Preserve their existing rows, original provenance ledger identity and timestamp, and deletion fences; do not reset or replay provenance DDL.

For a preview already recorded as 181:prompt_provenance.sql, the repair must move that row to 184 and install main’s 181 asset-root constraint, 182 billing enum labels, and 183 obligation index with matching ledger entries. Migration 182 must commit before the 183 index can use the new enum labels. Guard the exact target and existing schema, confirm zero NULL asset roots before hardening, and verify unchanged prompt lifecycle data and tombstones. The migrator skips every number at or below its highest applied number, so renumbering the ledger alone would silently skip main’s migrations. The target-bound repair requires a separate reviewed rollout; starting new target-184 code before it completes can try to replay provenance DDL.

An older Brain preview may also retain the generation_tagging_source_revision_positive CHECK from its already-applied source-revision migration. Current fresh installs keep tagging_source_revision NOT NULL with default 1 but do not add that CHECK. The retained preview constraint is compatible with normal writers, which start at 1 and increment; it does not need to be dropped for this rollout.

Capture defaults off. Owner reconciliation defaults to zero work. Before any bounded enrollment, configure the owner sweep and establish current owner verification. Re-enrollment must reject existing deletion tombstones. A database restore also needs the dedicated provenance reset procedure before private feature traffic resumes.

Paid analysis remains preserved separately for PR3. Adding it later requires its own deletion dispatch fence, cost accounting, qualification, and identified consumer.