Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0081_provenance_at_origin.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0081_provenance_at_origin.sql |
| Original SHA-256 | a2b471c0ca480acfc69ee94f93e94a5c2ef8007057f1273b9961fedc71385e9a |
| Git blob SHA | a49c29381971a1ea24f717f348e7ed398ec122f5 |
| Reading-copy SHA-256 | a2b471c0ca480acfc69ee94f93e94a5c2ef8007057f1273b9961fedc71385e9a |
Recovered source transcript
Download complete source text ↓117 lines · 7.8 KB
-- +goose Up
-- PROVENANCE BOUND AT ORIGIN, ratified 28 July 2026.
--
-- The Gate 2 fixtures proved that a purely content based egress gate cannot hold: a candidate row with the
-- identifiers stripped, and a customer's own performance work statement containing no personal data at all,
-- both crossed the external boundary. Provenance was introduced as the control, but in its first form the
-- CALLER declared it at call time. That is a control a mistake can defeat, because the only thing standing
-- between a tenant document and OpenAI was a constant written at one call site.
--
-- So provenance is now BORN WITH THE ROW and carried by it:
--
-- a row in the public SAM cache is born public_cache, because sam-sync is the only thing that writes it
-- a tenant uploaded document is born tenant_upload
-- an extraction chunk is born tenant_upload, because it IS the document's bytes
-- a scope item is born tenant_record
-- a candidate is born tenant_record
--
-- THE COLUMN IS NOT WRITABLE BY THE APPLICATION. A DEFAULT supplies it, a CHECK bounds it, and a trigger
-- refuses any INSERT or UPDATE that tries to set or change it to something else. Fabricating provenance
-- therefore requires editing THIS migration and shipping a new one, which is a named, reviewable location
-- rather than a constant somebody can type at a call site.
--
-- Why a trigger and not just a DEFAULT: a default only applies when the column is omitted. An INSERT that
-- names the column would otherwise let a writer choose, which is the exact hole being closed.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION provenance_is_born_not_chosen() RETURNS trigger AS $$
DECLARE
born text := TG_ARGV[0];
BEGIN
IF TG_OP = 'INSERT' THEN
-- Whatever the writer supplied, the row is born with the provenance its ORIGIN dictates.
NEW.provenance := born;
RETURN NEW;
END IF;
-- UPDATE: immutable. A row cannot be laundered from tenant private to public by an update.
IF NEW.provenance IS DISTINCT FROM OLD.provenance THEN
RAISE EXCEPTION 'provenance is immutable: % cannot become % on table %',
OLD.provenance, NEW.provenance, TG_TABLE_NAME;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd
-- sam_opportunities: the PUBLIC cache. The only table on this list whose rows may ever leave.
ALTER TABLE sam_opportunities ADD COLUMN IF NOT EXISTS provenance text NOT NULL DEFAULT 'public_cache';
ALTER TABLE sam_opportunities DROP CONSTRAINT IF EXISTS ck_sam_opportunities_provenance;
ALTER TABLE sam_opportunities ADD CONSTRAINT ck_sam_opportunities_provenance CHECK (provenance = 'public_cache');
DROP TRIGGER IF EXISTS trg_sam_opportunities_provenance ON sam_opportunities;
CREATE TRIGGER trg_sam_opportunities_provenance BEFORE INSERT OR UPDATE ON sam_opportunities
FOR EACH ROW EXECUTE FUNCTION provenance_is_born_not_chosen('public_cache');
-- opportunity_records: the other public catalog surface.
ALTER TABLE opportunity_records ADD COLUMN IF NOT EXISTS provenance text NOT NULL DEFAULT 'public_cache';
ALTER TABLE opportunity_records DROP CONSTRAINT IF EXISTS ck_opportunity_records_provenance;
ALTER TABLE opportunity_records ADD CONSTRAINT ck_opportunity_records_provenance CHECK (provenance = 'public_cache');
DROP TRIGGER IF EXISTS trg_opportunity_records_provenance ON opportunity_records;
CREATE TRIGGER trg_opportunity_records_provenance BEFORE INSERT OR UPDATE ON opportunity_records
FOR EACH ROW EXECUTE FUNCTION provenance_is_born_not_chosen('public_cache');
-- opportunity_documents: a tenant's own uploaded solicitation package. NEVER leaves.
ALTER TABLE opportunity_documents ADD COLUMN IF NOT EXISTS provenance text NOT NULL DEFAULT 'tenant_upload';
ALTER TABLE opportunity_documents DROP CONSTRAINT IF EXISTS ck_opportunity_documents_provenance;
ALTER TABLE opportunity_documents ADD CONSTRAINT ck_opportunity_documents_provenance CHECK (provenance = 'tenant_upload');
DROP TRIGGER IF EXISTS trg_opportunity_documents_provenance ON opportunity_documents;
CREATE TRIGGER trg_opportunity_documents_provenance BEFORE INSERT OR UPDATE ON opportunity_documents
FOR EACH ROW EXECUTE FUNCTION provenance_is_born_not_chosen('tenant_upload');
-- opportunity_document_chunks: literally the document's bytes, so it inherits the document's fate.
ALTER TABLE opportunity_document_chunks ADD COLUMN IF NOT EXISTS provenance text NOT NULL DEFAULT 'tenant_upload';
ALTER TABLE opportunity_document_chunks DROP CONSTRAINT IF EXISTS ck_opportunity_document_chunks_provenance;
ALTER TABLE opportunity_document_chunks ADD CONSTRAINT ck_opportunity_document_chunks_provenance CHECK (provenance = 'tenant_upload');
DROP TRIGGER IF EXISTS trg_opportunity_document_chunks_provenance ON opportunity_document_chunks;
CREATE TRIGGER trg_opportunity_document_chunks_provenance BEFORE INSERT OR UPDATE ON opportunity_document_chunks
FOR EACH ROW EXECUTE FUNCTION provenance_is_born_not_chosen('tenant_upload');
-- opportunity_scope_items: derived tenant work product.
ALTER TABLE opportunity_scope_items ADD COLUMN IF NOT EXISTS provenance text NOT NULL DEFAULT 'tenant_record';
ALTER TABLE opportunity_scope_items DROP CONSTRAINT IF EXISTS ck_opportunity_scope_items_provenance;
ALTER TABLE opportunity_scope_items ADD CONSTRAINT ck_opportunity_scope_items_provenance CHECK (provenance = 'tenant_record');
DROP TRIGGER IF EXISTS trg_opportunity_scope_items_provenance ON opportunity_scope_items;
CREATE TRIGGER trg_opportunity_scope_items_provenance BEFORE INSERT OR UPDATE ON opportunity_scope_items
FOR EACH ROW EXECUTE FUNCTION provenance_is_born_not_chosen('tenant_record');
-- candidates: the workforce. The row the whole boundary exists to protect.
ALTER TABLE candidates ADD COLUMN IF NOT EXISTS provenance text NOT NULL DEFAULT 'tenant_record';
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_provenance;
ALTER TABLE candidates ADD CONSTRAINT ck_candidates_provenance CHECK (provenance = 'tenant_record');
DROP TRIGGER IF EXISTS trg_candidates_provenance ON candidates;
CREATE TRIGGER trg_candidates_provenance BEFORE INSERT OR UPDATE ON candidates
FOR EACH ROW EXECUTE FUNCTION provenance_is_born_not_chosen('tenant_record');
-- The application role reads provenance; it has no path to write it, because the trigger overwrites any
-- value on insert and refuses any change on update. No grant change is needed and none is given.
-- +goose Down
DROP TRIGGER IF EXISTS trg_candidates_provenance ON candidates;
DROP TRIGGER IF EXISTS trg_opportunity_scope_items_provenance ON opportunity_scope_items;
DROP TRIGGER IF EXISTS trg_opportunity_document_chunks_provenance ON opportunity_document_chunks;
DROP TRIGGER IF EXISTS trg_opportunity_documents_provenance ON opportunity_documents;
DROP TRIGGER IF EXISTS trg_opportunity_records_provenance ON opportunity_records;
DROP TRIGGER IF EXISTS trg_sam_opportunities_provenance ON sam_opportunities;
ALTER TABLE candidates DROP CONSTRAINT IF EXISTS ck_candidates_provenance;
ALTER TABLE opportunity_scope_items DROP CONSTRAINT IF EXISTS ck_opportunity_scope_items_provenance;
ALTER TABLE opportunity_document_chunks DROP CONSTRAINT IF EXISTS ck_opportunity_document_chunks_provenance;
ALTER TABLE opportunity_documents DROP CONSTRAINT IF EXISTS ck_opportunity_documents_provenance;
ALTER TABLE opportunity_records DROP CONSTRAINT IF EXISTS ck_opportunity_records_provenance;
ALTER TABLE sam_opportunities DROP CONSTRAINT IF EXISTS ck_sam_opportunities_provenance;
ALTER TABLE candidates DROP COLUMN IF EXISTS provenance;
ALTER TABLE opportunity_scope_items DROP COLUMN IF EXISTS provenance;
ALTER TABLE opportunity_document_chunks DROP COLUMN IF EXISTS provenance;
ALTER TABLE opportunity_documents DROP COLUMN IF EXISTS provenance;
ALTER TABLE opportunity_records DROP COLUMN IF EXISTS provenance;
ALTER TABLE sam_opportunities DROP COLUMN IF EXISTS provenance;
DROP FUNCTION IF EXISTS provenance_is_born_not_chosen();