Provenance & Data Trust
Updated
IntermediateWhat you'll learn
~25 min- Add a non-nullable data_source column (provenance: where a value came from) with the values manual, seed, sync, sample and ai_drafted
- Set provenance in server code only, never from the request body
- Render an ai_drafted value with a visible label so it is never shown as observed fact
- Prove that no row lacks a source and that an ai_drafted row renders with its label
Every value has an origin
When someone asks how a record got into the system, the answer should be a column, not an investigation. You will add that column to the vehicle-fleet feature, give it five values, and write tests so the answer stays true. The fifth value, ai_drafted, is new: it marks a value an AI produced that a human has not yet confirmed. Lesson 27 builds a feature on it.
Each problem below has a pattern, and your platform lesson says where it lands.
| Problem | The pattern | Where your platform lesson lands it | You decide |
|---|---|---|---|
| Where a record came from | One data_source column on every operational table; an audit table is the alternative if you need change-level provenance | The migration runs against your managed SQL Server: 34 · 35 · 36 | Whether you need row-level or change-level provenance |
| Who may set the source | Server code only; a signed ingestion job per source is the alternative | Nothing platform-specific | Which writers are trusted to say sync |
| Fabricated demo data | None in production tables; synthetic data only in a clearly isolated demo environment | Nothing platform-specific | Which environments may hold sample rows |
| AI-produced values | ai_drafted, always labelled, never counted as observed; a separate suggestions table is the alternative | Nothing platform-specific | Whether a draft lives beside the record or apart from it |
| Value | Meaning | Example |
|---|---|---|
manual | A person entered it through the application | An operator adds a vehicle |
seed | Loaded during setup or migration as reference data | Department codes, status values |
sync | Pulled from another system by an integration | Vehicle records from a fleet system |
sample | Demonstration or test data | Records in a development environment |
ai_drafted | Produced by a model, not yet confirmed by a human | A suggested category for a record |
Plan the change with your agent
Open your workspace agent at the project root, switch to Plan mode (Shift+Tab, /plan, or the desktop app’s mode picker), and ask:
Read our vehicle-fleet migration, route handlers, and schemas, and plan row-level data provenance so the server records where every row came from and labels unconfirmed AI drafts. Ask anything unclear.What to check in the plan before you approve
- The migration enforces
data_source NVARCHAR(20) NOT NULLonvehicle_fleetwith a namedCHECKconstraint allowing'manual','seed','sync','sample', and'ai_drafted', and drops anyDEFAULTconstraint so callers cannot forget the column. - Server code sets
data_source('manual'inPOST,'sync'in sync jobs,'ai_drafted'for model outputs); the Zod create and update schemas reject request bodies that includedata_source. ProvenanceBadgerenders nothing extra formanual,seed, orsync, renders a neutral"Sample"chip forsample, and renders"AI draft, not confirmed"with a matchingaria-labelforai_drafted.- Aggregates exclude
sampleandai_draftedrows, CSV exports includedata_source, and no demo rows are seeded into operational tables.
If the first plan leaves a default value on data_source or lets clients pass data_source in the request body, nudge it before approving:
Drop the default constraint on data_source so any insert that omits it fails loudly, and reject data_source in request bodies.What you get
- Typed in the terminal:
claude - Mode:Plan mode
- You type in the session: Read our vehicle-fleet migration, route handlers, and schemas, and plan row-level data provenance so the server records where every row came from and labels unconfirmed AI drafts. Ask anything unclear.
- The tool replies: Proposed plan: 1. Migration: add data_source NVARCHAR(20), backfill existing rows, set NOT NULL, and add CK_vehicle_fleet_data_source for manual, seed, sync, sample, ai_drafted. 2. Server & types: POST writes 'manual'; Zod schemas omit data_source; VehicleFleetRow exposes readonly data_source. 3. UI & reporting: ProvenanceBadge labels sample and ai_drafted rows; KPI aggregates exclude sample and ai_drafted; CSV export includes data_source. Should we keep Lesson 6's DEFAULT 'manual' constraint on data_source or drop it?
- You type in the session: Drop the default constraint on data_source so any insert that omits it fails loudly, and reject data_source in request bodies. Build it.
- The tool replies: Updated: - migrations: data_source NVARCHAR(20) NOT NULL, a nullable add, a documented backfill to manual, then NOT NULL and constraint CK_vehicle_fleet_data_source allowing manual, seed, sync, sample, ai_drafted. The lesson 6 default is dropped. - api/vehicle-fleet/route.ts: POST sets 'manual'; the create and update schemas are strict, so a body containing data_source is rejected with the contract error. - types: data_source readonly on VehicleFleetRow. - ProvenanceBadge: sample shows a neutral chip; ai_drafted shows the value plus 'AI draft, not confirmed', and the cell's aria-label repeats that. - Aggregates filter to rows whose source is manual, seed or sync. - CSV export includes the data_source column. I seeded nothing.
- You type in the session: A teammate's import script inserts rows without data_source. What happens?
- The tool replies: The insert fails with a NOT NULL violation, because the column has no default. That is intended: an import has to say what it is. If I had defaulted to 'manual', their script would have succeeded and recorded machine-imported rows as typed by a person, which is the false claim this column exists to prevent.
- You type in the session: Can a user send data_source in the POST body to make their row look like a sync?
- The tool replies: No. The schema is strict, so the body is rejected before the handler runs, and the handler would ignore it anyway because it passes the literal 'manual'. I added a test that posts data_source: 'sync' and expects a 400.
- You type in the session: Add the tests: no row lacks a source, and an ai_drafted row shows its label.
- The tool replies: Created two. A database check that counts rows whose data_source is NULL or outside the allowed set (zero is the pass; the constraint makes it structural, the test guards a future migration that drops it). And a render test that renders a row with ai_drafted and asserts the output contains 'AI draft, not confirmed' and does not contain the plain observed style. I removed the label in the component, watched the render test fail, and restored it.
Illustrative session; exact output varies by tool and agency configuration.
The rules
Every operational table gets the column
Lookup tables and junction tables included. Here is the reference migration your agent writes:
-- 1. nullable first, so existing rows do not block the changeALTER TABLE vehicle_fleet ADD data_source NVARCHAR(20) NULL;
-- 2. backfill with a documented rule (here: rows that predate the column were typed by a person)UPDATE vehicle_fleet SET data_source = 'manual' WHERE data_source IS NULL;
-- 3. tighten, and constrain the valuesALTER TABLE vehicle_fleet ALTER COLUMN data_source NVARCHAR(20) NOT NULL;ALTER TABLE vehicle_fleet ADD CONSTRAINT CK_vehicle_fleet_data_source CHECK (data_source IN ('manual', 'seed', 'sync', 'sample', 'ai_drafted'));
-- 4. lesson 6's scaffold defaulted new rows to 'manual'; drop that default so an insert must say what it isALTER TABLE vehicle_fleet DROP CONSTRAINT DF_vehicle_fleet_data_source;The backfill rule is the part to write down: it is a claim about every old row. On a table that already holds rows, ADD ... NOT NULL with no default fails, which is why the column goes in nullable first. After step 4, the lesson 7 insert that omits data_source fails until it passes the literal 'manual', as the handler below does.
Server code sets it
A route handler or job sets the value; a request body never does (reference handler snippet shown):
// POST handler: the literal comes from code, not the requestconst result = await withPool((pool) => pool.request() .input("vehicle_id", sql.NVarChar, parsed.data.vehicle_id) .input("dataSource", sql.NVarChar, "manual") .input("createdBy", sql.NVarChar, session.sub) // the sign-in subject, not a display name .query(`INSERT INTO vehicle_fleet (vehicle_id, data_source, created_by) VALUES (@vehicle_id, @dataSource, @createdBy)`));Never fabricate operational data
The instinct from product work is to seed realistic rows so the screen looks alive. In an operational table that is a liability: someone screenshots it, exports it, and quotes “the 50 vehicles in the system” in a meeting. Seed data belongs in reference tables (seed) and in non-production environments (sample).
If deleting a record would have a real-world consequence, it is operational and must not be invented. If deleting it only changes configuration, it is reference data.
Empty state is correct
A new table with zero rows is not broken. Design the empty state to guide: “No vehicles registered yet”, with an Add button. Test your page with zero, one and many rows before you consider adding sample data.
When someone asks for realistic rows before a demo, use a development environment where sample rows are labelled, or demo the empty state and create a real record live. The director sees the whole workflow instead of a pre-filled table.
The AI-drafted value
A model can produce a plausible category, a summary, or a suggested field value. A plausible value presented in the same style as a recorded one gets read as recorded. Three rules keep it honest:
- It carries
ai_draftedfrom the moment it is written. Only server code writes it, and only for a value a model produced. - It is always labelled where it is shown. The label is text, not just color: “AI draft, not confirmed”. A screen reader hears it too.
- It never counts as observed. Aggregates, exports used as records, and downstream systems either exclude it or carry the column so the reader can.
The label component is small:
REFERENCE CODE src/components/ProvenanceBadge.tsx: visible chip and screen-reader label for sample and ai_drafted values open reference hide reference Reference pattern for your workspace agent; open to inspect or compare this file. You do not type or paste this by hand.
import Chip from "@mui/material/Chip";
export type DataSource = "manual" | "seed" | "sync" | "sample" | "ai_drafted";
export function ProvenanceCell({ value, source }: { value: string; source: DataSource }) { if (source === "ai_drafted") { return ( <span aria-label={`${value}, AI draft, not confirmed`}> <em>{value}</em> <Chip size="small" variant="outlined" label="AI draft, not confirmed" /> </span> ); } if (source === "sample") { return <span>{value} <Chip size="small" label="Sample" /></span>; } return <span>{value}</span>;}A human confirming a draft is a write like any other: a route that checks the session and role, changes the source, and records who confirmed it. Lesson 27 designs that step.
Some teams never let a draft share a table with records. A separate suggestions table makes mixing impossible, at the cost of a join and a copy step when a human accepts one. If your agency has an authoritative-records rule, choose this and keep the contract test: no draft appears in an observed-data view.
Prove it
Run these in your normal test command. Both have a negative case you should see fail once. First, the reference SQL verification query:
-- Zero is the pass. Run in CI against the migrated schema.SELECT COUNT(*) AS bad_rowsFROM vehicle_fleetWHERE data_source IS NULL OR data_source NOT IN ('manual', 'seed', 'sync', 'sample', 'ai_drafted');And here is the reference component test your agent writes in src/components/ProvenanceBadge.test.tsx:
import { it, expect } from "vitest";import { renderToStaticMarkup } from "react-dom/server";import { ProvenanceCell } from "./ProvenanceBadge";
it("an ai_drafted value renders with its label", () => { const html = renderToStaticMarkup(<ProvenanceCell value="Cargo van" source="ai_drafted" />); expect(html).toContain("AI draft, not confirmed");});
it("an observed value carries no draft label", () => { const html = renderToStaticMarkup(<ProvenanceCell value="Cargo van" source="manual" />); expect(html).not.toContain("AI draft");});Planted faults: insert a row with NULL provenance in a scratch database (the constraint should refuse; if the query ever returns a count above zero, a migration dropped it), and delete the label from the component (the first test must fail). Add a third test for your production configuration: a query that counts sample rows in production must return zero.
A KPI card shows 95% utilization, but operations says half the fleet is idle. In the technical view, 30 of 60 'active' rows have data_source = 'sample'. What is the fix?
A model suggests a category for a record and your code stores it. Which handling satisfies the contract?
Key takeaway
Provenance is a column set by server code, enforced by a constraint, and shown to the reader wherever a value might otherwise pass for fact. Lane 5 (lessons 20 to 28) puts it to work on a feature where a model proposes and a person decides.
- LEADERSHIP DECISION
- Which tables need row-level provenance and who approves the backfill rule for existing rows.
- PRACTITIONER ACTION
- Run the migration on a copy holding rows, then run the NULL-count check and the render test.
- SUCCESS MEASURE
- Audit finding avoided: no figure shown as observed that rests on sample or AI-drafted rows.
Legend: LEADERSHIP DECISION = what a leader decides · PRACTITIONER ACTION = what the engineer does next · SUCCESS MEASURE = how you know, in calendar time, assessor-hours or cost, contract or revenue risk, or audit finding avoided