Applied AI-Accelerated Government Development

Provenance & Data Trust

Updated

Intermediate

What 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.

ProblemThe patternWhere your platform lesson lands itYou decide
Where a record came fromOne data_source column on every operational table; an audit table is the alternative if you need change-level provenanceThe migration runs against your managed SQL Server: 34 · 35 · 36Whether you need row-level or change-level provenance
Who may set the sourceServer code only; a signed ingestion job per source is the alternativeNothing platform-specificWhich writers are trusted to say sync
Fabricated demo dataNone in production tables; synthetic data only in a clearly isolated demo environmentNothing platform-specificWhich environments may hold sample rows
AI-produced valuesai_drafted, always labelled, never counted as observed; a separate suggestions table is the alternativeNothing platform-specificWhether a draft lives beside the record or apart from it
ValueMeaningExample
manualA person entered it through the applicationAn operator adds a vehicle
seedLoaded during setup or migration as reference dataDepartment codes, status values
syncPulled from another system by an integrationVehicle records from a fleet system
sampleDemonstration or test dataRecords in a development environment
ai_draftedProduced by a model, not yet confirmed by a humanA 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 NULL on vehicle_fleet with a named CHECK constraint allowing 'manual', 'seed', 'sync', 'sample', and 'ai_drafted', and drops any DEFAULT constraint so callers cannot forget the column.
  • Server code sets data_source ('manual' in POST, 'sync' in sync jobs, 'ai_drafted' for model outputs); the Zod create and update schemas reject request bodies that include data_source.
  • ProvenanceBadge renders nothing extra for manual, seed, or sync, renders a neutral "Sample" chip for sample, and renders "AI draft, not confirmed" with a matching aria-label for ai_drafted.
  • Aggregates exclude sample and ai_drafted rows, CSV exports include data_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

SESSION Provenance: plan it, build it, then try to break it
  1. Typed in the terminal: claude
  2. Mode:Plan mode
  3. 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.
  4. 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?
  5. 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.
  6. 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.
  7. You type in the session: A teammate's import script inserts rows without data_source. What happens?
  8. 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.
  9. You type in the session: Can a user send data_source in the POST body to make their row look like a sync?
  10. 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.
  11. You type in the session: Add the tests: no row lacks a source, and an ai_drafted row shows its label.
  12. 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 change
ALTER 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 values
ALTER 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 is
ALTER 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 request
const 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.

💬Demo the workflow, not the 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:

  1. It carries ai_drafted from the moment it is written. Only server code writes it, and only for a value a model produced.
  2. 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.
  3. 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.
src/components/ProvenanceBadge.tsx
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.

ℹThe alternative: keep drafts apart

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_rows
FROM vehicle_fleet
WHERE 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:

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.


KNOWLEDGE CHECK

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?

KNOWLEDGE CHECK

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

Search lessons