Janady13/missionmatch / db/migrations/0082_provenance_by_source.sql

1-- +goose Up 2-- PROVENANCE FOR opportunity_documents IS BOUND FROM THE SOURCE, not from the table. 3-- 4-- Migration 0081 bound provenance per TABLE, which was right for five of its six tables and wrong for this 5-- one. opportunity_documents holds BOTH a customer's own uploaded package AND published federal attachments 6-- pulled from sam.gov, and those are not the same kind of data: 7-- 8-- an uploaded PWS is the customer's private work product and must never leave the local plane 9-- a sam.gov attachment is a published US Government document, already public, and the external 10-- normalization lane exists precisely for text like it 11-- 12-- Binding every row to tenant_upload made the public ones unusable by the lane built for them. Binding every 13-- row to public_cache would be catastrophic. So the binding derives from source_type, which is the column 14-- that already states where the row came from. 15-- 16-- THE PROPERTY THAT MUST SURVIVE, and this is the whole point of provenance at origin: A CALLER STILL 17-- CANNOT DECLARE PROVENANCE. It is computed, never accepted. What a caller can now influence is 18-- source_type, which is a different and much more visible lie: it is a documented column with a CHECK 19-- constraint, it appears in every audit entry, and the binding sites test enumerates the code paths allowed 20-- to set the public values. Fabricating provenance therefore still requires touching a named location, it is 21-- simply a different named location than before. 22-- 23-- THE HONEST RESIDUAL RISK, written down rather than discovered later: application code that inserted a 24-- tenant document with source_type 'sam_attachment' would produce a public_cache row. Nothing in the 25-- database can distinguish that from a real attachment. The mitigation is that exactly one binary writes 26-- that source_type, cmd/sam-attachment-sync, and the test asserts it. 27 28-- +goose StatementBegin 29CREATE OR REPLACE FUNCTION provenance_from_document_source() RETURNS trigger AS $$ 30DECLARE 31 born text; 32BEGIN 33 -- The mapping is EXHAUSTIVE and its default is the restrictive one. A source_type nobody has thought 34 -- about yet becomes tenant_upload, which fails closed: the worst outcome is that a public document is 35 -- treated as private and cannot be sent to an external model, which is an inconvenience. The reverse 36 -- default would be a disclosure. 37 born := CASE 38 WHEN NEW.source_type IN ('sam_attachment', 'url_import') THEN 'public_cache' 39 ELSE 'tenant_upload' 40 END; 41 42 IF TG_OP = 'INSERT' THEN 43 NEW.provenance := born; 44 RETURN NEW; 45 END IF; 46 47 -- Provenance remains IMMUTABLE, and so does the source it is derived from. Allowing source_type to be 48 -- updated would reintroduce exactly the hole this design closes: a row could be inserted as an upload 49 -- and later relabelled an attachment, laundering a customer's document into the public class. 50 IF NEW.provenance IS DISTINCT FROM OLD.provenance THEN 51 RAISE EXCEPTION 'provenance is immutable: % cannot become % on table %', 52 OLD.provenance, NEW.provenance, TG_TABLE_NAME; 53 END IF; 54 IF NEW.source_type IS DISTINCT FROM OLD.source_type THEN 55 RAISE EXCEPTION 'source_type is immutable on %: % cannot become %, because provenance derives from it', 56 TG_TABLE_NAME, OLD.source_type, NEW.source_type; 57 END IF; 58 RETURN NEW; 59END; 60$$ LANGUAGE plpgsql; 61-- +goose StatementEnd 62 63-- The trigger name is the one 0081 ACTUALLY created, read from pg_trigger. Guessing it wrong left the old 64-- trigger attached, and the reclassify UPDATE below was then refused by it with "provenance is immutable", 65-- which rolled the whole migration back. A correct rollback, and a wasted attempt that a two second query 66-- would have avoided. 67DROP TRIGGER IF EXISTS trg_opportunity_documents_provenance ON opportunity_documents; 68DROP TRIGGER IF EXISTS trg_provenance_opportunity_documents ON opportunity_documents; 69-- THE ORDER BELOW IS LOAD BEARING. The one time reclassification runs while NO trigger is attached, 70-- because the trigger's whole job is to refuse a provenance change, and the reclassify IS a provenance 71-- change. Creating the trigger first made the migration refuse itself, which it did correctly and which 72-- cost a second rolled back attempt. Trigger goes on AFTER the data is correct, and from that moment the 73-- column can never move again. 74 75-- The CHECK from 0081 pinned this table to a single value. Widen it to the two the mapping can produce, and 76-- no further: the column still cannot hold curated_card or tenant_record. 77ALTER TABLE opportunity_documents DROP CONSTRAINT IF EXISTS ck_opportunity_documents_provenance; 78ALTER TABLE opportunity_documents ADD CONSTRAINT ck_opportunity_documents_provenance 79 CHECK (provenance IN ('tenant_upload', 'public_cache')); 80 81-- Existing rows are RECLASSIFIED to what the mapping says they should always have been. This is a one time 82-- correction of rows written before the mapping existed, not a licence to update provenance later; the 83-- trigger above refuses any subsequent change. 84UPDATE opportunity_documents SET provenance = 'public_cache' 85 WHERE source_type IN ('sam_attachment', 'url_import') AND provenance <> 'public_cache'; 86 87CREATE TRIGGER trg_opportunity_documents_provenance 88 BEFORE INSERT OR UPDATE ON opportunity_documents 89 FOR EACH ROW EXECUTE FUNCTION provenance_from_document_source(); 90 91-- CHUNKS INHERIT FROM THEIR DOCUMENT, for the same reason. A chunk of a published federal attachment is 92-- public; a chunk of a customer's PWS is not. 0081 bound every chunk to tenant_upload, which would have 93-- made extracted public text unusable by the lane that exists for it. 94-- +goose StatementBegin 95CREATE OR REPLACE FUNCTION provenance_from_parent_document() RETURNS trigger AS $$ 96DECLARE 97 parent text; 98BEGIN 99 IF TG_OP = 'INSERT' THEN 100 SELECT provenance INTO parent FROM opportunity_documents WHERE id = NEW.document_id; 101 -- A chunk whose parent cannot be found fails CLOSED rather than defaulting to public. 102 NEW.provenance := COALESCE(parent, 'tenant_upload'); 103 RETURN NEW; 104 END IF; 105 IF NEW.provenance IS DISTINCT FROM OLD.provenance THEN 106 RAISE EXCEPTION 'provenance is immutable: % cannot become % on table %', 107 OLD.provenance, NEW.provenance, TG_TABLE_NAME; 108 END IF; 109 RETURN NEW; 110END; 111$$ LANGUAGE plpgsql; 112-- +goose StatementEnd 113 114DROP TRIGGER IF EXISTS trg_opportunity_document_chunks_provenance ON opportunity_document_chunks; 115DROP TRIGGER IF EXISTS trg_provenance_opportunity_document_chunks ON opportunity_document_chunks; 116 117ALTER TABLE opportunity_document_chunks DROP CONSTRAINT IF EXISTS ck_opportunity_document_chunks_provenance; 118ALTER TABLE opportunity_document_chunks ADD CONSTRAINT ck_opportunity_document_chunks_provenance 119 CHECK (provenance IN ('tenant_upload', 'public_cache')); 120 121UPDATE opportunity_document_chunks c SET provenance = d.provenance 122 FROM opportunity_documents d 123 WHERE d.id = c.document_id AND c.provenance <> d.provenance; 124 125CREATE TRIGGER trg_opportunity_document_chunks_provenance 126 BEFORE INSERT OR UPDATE ON opportunity_document_chunks 127 FOR EACH ROW EXECUTE FUNCTION provenance_from_parent_document(); 128 129-- +goose Down 130DROP TRIGGER IF EXISTS trg_opportunity_document_chunks_provenance ON opportunity_document_chunks; 131DROP TRIGGER IF EXISTS trg_opportunity_documents_provenance ON opportunity_documents; 132DROP FUNCTION IF EXISTS provenance_from_parent_document(); 133DROP FUNCTION IF EXISTS provenance_from_document_source(); 134