Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0074_opportunity_records_embedding.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0074_opportunity_records_embedding.sql |
| Original SHA-256 | 92e645159550643b30a8becab609b4f5e7de264f255c49152204ea76d466981f |
| Git blob SHA | aa525e5a5807615e8654f5374d90e86f5eed9e69 |
| Reading-copy SHA-256 | 92e645159550643b30a8becab609b4f5e7de264f255c49152204ea76d466981f |
Recovered source transcript
Download complete source text ↓52 lines · 3.3 KB
-- +goose Up
-- Teach the AI the federal opportunity landscape. The platform already ingests SAM.gov notices, contract
-- awards, and forecasts into opportunity_records, but nothing ever embedded them, so the inference layer
-- held the opportunities as rows it could aggregate and never as meaning it could retrieve. This adds the
-- same two retrieval arms the candidate corpus already has, so an opportunity can be found by meaning and
-- by words and the two fused with reciprocal rank fusion.
--
-- opportunity_records is GLOBAL PUBLIC reference data with no organization_id and no row level security,
-- exactly like the skills ontology, so this follows the skills pattern rather than the candidates pattern:
-- a vector(384) column pinned to the same MiniLM class dimension the rest of the platform uses, and a
-- PARTIAL HNSW index whose predicate matches the query predicate. The dimension is the one the boot vector
-- gate verifies, so an opportunity vector and a candidate vector are always comparable.
--
-- search_tsv is a GENERATED column, so the full text arm can never drift from the row: an ingest that
-- updates a title updates the index in the same write, with no backfill job to forget. The embedding is
-- necessarily a separate backfill (it requires a model call), which is why it is nullable and why every
-- read filters on IS NOT NULL.
ALTER TABLE opportunity_records
ADD COLUMN embedding vector(384);
ALTER TABLE opportunity_records
ADD COLUMN search_tsv tsvector GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(agency, '') || ' ' || coalesce(sub_agency, '')), 'B') ||
setweight(to_tsvector('english', coalesce(naics, '') || ' ' || coalesce(psc, '') || ' ' ||
coalesce(set_aside, '') || ' ' || coalesce(contract_vehicle, '')), 'C') ||
setweight(to_tsvector('english', coalesce(incumbent_name, '') || ' ' || coalesce(pop_state, '') || ' ' ||
coalesce(pop_city, '')), 'D')
) STORED;
-- Partial HNSW so the index matches the "WHERE embedding IS NOT NULL" query predicate, the same shape and
-- build parameters as the skills and candidates indexes.
CREATE INDEX idx_opp_records_embedding_hnsw ON opportunity_records
USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64)
WHERE embedding IS NOT NULL;
CREATE INDEX idx_opp_records_search_tsv ON opportunity_records USING gin (search_tsv);
-- The app role reads the catalog (it already holds SELECT) and the backfill writes vectors as the migrator,
-- matching how skills embeddings are written. UPDATE is granted so an in application embed path can refresh
-- a vector without the migrator, the same grant skills carries.
GRANT UPDATE ON opportunity_records TO missionmatch_app;
-- +goose Down
-- Dropping the columns loses only derived data (a vector and a generated index), never an ingested record,
-- so this down is safe to run and does not need the refusal guard the row owning tables carry.
DROP INDEX IF EXISTS idx_opp_records_search_tsv;
DROP INDEX IF EXISTS idx_opp_records_embedding_hnsw;
ALTER TABLE opportunity_records DROP COLUMN IF EXISTS search_tsv;
ALTER TABLE opportunity_records DROP COLUMN IF EXISTS embedding;