Janady13/missionmatch / db/migrations/0104_corpus_documents.sql

1-- +goose Up 2 3-- THE CORPUS DOCUMENT LAYER: missionMATCH's OWN KNOWLEDGE OF FEDERAL OPPORTUNITIES. 4-- 5-- THIS IS PRODUCT KNOWLEDGE, NOT CUSTOMER DATA, and that is the whole reason these tables exist rather than 6-- reusing the ones already here. opportunity_documents and opportunity_document_chunks carry RLS and an 7-- organization_id because they were built for the tenant path: a customer tracks a pursuit and pulls that 8-- package into THEIR space. Harvesting the federal corpus into those tables would file the government's 9-- public solicitations inside one tenant's boundary, which is both wrong and unusable, because the next 10-- tenant would have to download the same documents again to know the same public facts. 11-- 12-- missionMATCH should already be an expert on every government opportunity before any customer arrives. 13-- Each tenant then brings their own data and their own scopes on top of that. So these tables sit beside 14-- opportunity_records and ingest_manifest in the SHARED layer, with no organization_id and no RLS, exactly 15-- as the notice corpus does. 16-- 17-- WHY DOCUMENTS AT ALL, WHEN THE NOTICE TEXT IS ALREADY EXTRACTED. Because for staffing the notice text is 18-- usually a pointer and not an answer. A notice says "provide services in accordance with the attached 19-- PWS". The labor category matrix, the clearance required per position, the education and experience 20-- minimums, the certifications: all of that is in the attachment. The 2163 notice level extractions already 21-- in this database prove the extraction works, and they also show its ceiling, because a notice that 22-- delegates to its attachment yields a labor category and little else. 23-- 24-- THE BINARY IS NOT RETAINED. Measured against sam.gov, an average notice carries 2.5 files and 2.3 MB. 25-- Across the active federal landscape that is roughly 69 GB of PDF, and keeping it would buy nothing: SAM 26-- is a permanent public store with stable URLs, so the durable copy already exists and is not ours to 27-- duplicate. What is worth keeping is what was LEARNED. Every row here records the sha256 and the source 28-- url, so any original can be refetched byte for byte on demand, and the download itself is transient: 29-- fetch, extract, embed, discard, one file at a time. 30-- 31-- downloaded per notice 2.3 MB transient 32-- extracted prose ~80 KB three to five percent of a text PDF 33-- after lz4 ~25 KB 34-- ~20 chunks as halfvec ~15 KB 35-- retained per notice ~40 KB a 57x reduction 36-- 37-- That is the entire active federal opportunity landscape, searchable by meaning and citable to a page, in 38-- roughly a gigabyte. 39 40-- corpus_documents is one public federal document, described but not stored. 41CREATE TABLE corpus_documents ( 42 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 43 44 -- The notice this document belongs to, in the SHARED corpus. No tenant is involved. 45 opportunity_record_id uuid NOT NULL REFERENCES opportunity_records(id) ON DELETE CASCADE, 46 47 -- SAM's own identifiers, so a re fetch needs nothing but this row. 48 resource_id text NOT NULL, 49 file_name text NOT NULL DEFAULT '', 50 media_type text NOT NULL DEFAULT '', 51 size_bytes bigint, 52 53 -- THE TWO COLUMNS THAT MAKE DISCARDING THE BINARY SAFE. sha256 is of the bytes as fetched, so a refetch 54 -- can be proved identical to what was learned from; source_url is where to get them. Neither is 55 -- optional, because a document whose original cannot be recovered is a claim without evidence. 56 sha256 text NOT NULL, 57 source_url text NOT NULL, 58 59 -- What happened when this document was read. A failure is RECORDED rather than retried forever, so a 60 -- scanned drawing that will never yield prose stops costing a request on every pass. 61 extract_status text NOT NULL DEFAULT 'pending' 62 CHECK (extract_status IN ('pending','ok','no_prose','too_large','fetch_failed','parse_error','skipped')), 63 extract_error text NOT NULL DEFAULT '', 64 page_count integer, 65 -- The bytes actually downloaded to learn this, kept as a number so the cost of the corpus is a fact 66 -- rather than an estimate. The bytes themselves are gone. 67 fetched_bytes bigint, 68 fetched_at timestamptz, 69 70 created_at timestamptz NOT NULL DEFAULT now(), 71 updated_at timestamptz NOT NULL DEFAULT now(), 72 73 -- Provenance is born at origin, per migration 0081. A SAM attachment is a published federal document. 74 provenance text NOT NULL DEFAULT 'public_cache', 75 76 UNIQUE (opportunity_record_id, resource_id) 77); 78 79-- The harvester's working query: which notices still have documents to read. 80CREATE INDEX IF NOT EXISTS idx_corpus_documents_status 81 ON corpus_documents (extract_status, fetched_at NULLS FIRST); 82CREATE INDEX IF NOT EXISTS idx_corpus_documents_notice 83 ON corpus_documents (opportunity_record_id); 84 85-- corpus_chunks is the knowledge itself: prose, where it came from, and what it means. 86CREATE TABLE corpus_chunks ( 87 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 88 document_id uuid NOT NULL REFERENCES corpus_documents(id) ON DELETE CASCADE, 89 90 -- THE CITATION, which is not optional. A chunk that cannot say which page and section it came from is 91 -- an assertion, and this product does not make assertions. The tenant side enforces the same rule on 92 -- scope items through a database constraint; the corpus holds itself to it too. 93 page_number integer, 94 section_label text NOT NULL DEFAULT '', 95 96 -- lz4 rather than the cluster default pglz: this column is the bulk of the corpus, it is prose, and 97 -- lz4 both compresses it better and decompresses several times faster, which matters because every 98 -- retrieval reads it. 99 text_content text COMPRESSION lz4 NOT NULL, 100 content_hash text NOT NULL, 101 102 -- halfvec, NOT vector. Two bytes per dimension instead of four halves the largest fixed cost in the 103 -- corpus for a recall difference that does not survive measurement at this dimensionality. 384 matches 104 -- the embedding model already in service (bge-small-en-v1.5), so the corpus and the tenant bench speak 105 -- the same vector space and can be compared directly. 106 embedding halfvec(384), 107 108 extraction_method text NOT NULL DEFAULT '', 109 confidence real, 110 created_at timestamptz NOT NULL DEFAULT now(), 111 provenance text NOT NULL DEFAULT 'public_cache' 112); 113 114CREATE INDEX IF NOT EXISTS idx_corpus_chunks_document ON corpus_chunks (document_id); 115 116-- HNSW over halfvec with cosine distance, the same measure the rest of the platform retrieves by. Built 117-- now while the table is empty, which is far cheaper than building it over a million rows later. 118CREATE INDEX IF NOT EXISTS idx_corpus_chunks_embedding 119 ON corpus_chunks USING hnsw (embedding halfvec_cosine_ops); 120 121-- Full text as well as vector, because a solicitation is full of exact tokens that embeddings blur: a 122-- clearance level, a NAICS code, a labor category name, a certification acronym. Retrieval fuses both. 123CREATE INDEX IF NOT EXISTS idx_corpus_chunks_fts 124 ON corpus_chunks USING gin (to_tsvector('english', text_content)); 125 126-- NO ROW LEVEL SECURITY ON EITHER TABLE, and that is the point rather than an omission. This is the 127-- product's own knowledge of public federal documents. Every tenant reads the same corpus, nobody owns it, 128-- and there is nothing here that one customer could learn about another. 129-- 130-- The application role reads it and never writes it. Writes come from the harvester running as the 131-- migrator, the same shape opportunity_records already uses, so a compromised application session cannot 132-- rewrite what the product claims to know. 133GRANT SELECT ON corpus_documents TO missionmatch_app; 134GRANT SELECT ON corpus_chunks TO missionmatch_app; 135 136SELECT mm_mirror_staging_role_privileges(); 137 138-- +goose Down 139DROP INDEX IF EXISTS idx_corpus_chunks_fts; 140DROP INDEX IF EXISTS idx_corpus_chunks_embedding; 141DROP INDEX IF EXISTS idx_corpus_chunks_document; 142DROP TABLE IF EXISTS corpus_chunks; 143DROP INDEX IF EXISTS idx_corpus_documents_notice; 144DROP INDEX IF EXISTS idx_corpus_documents_status; 145DROP TABLE IF EXISTS corpus_documents; 146