Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 39df94f92b2a33f852481fe1954dabed841524bb |
| Branch | w0-aedt-compliance |
| Path or archive member | db/migrations/0005_sam_opportunities.sql |
| Original SHA-256 | 93e084c3617264301235deb468054d6e3d263fbb1a48f046c75942df5dcc2f1c |
| Git blob SHA | ed3881a7bb010130636ab1a743a524d9a8fc157a |
| Reading-copy SHA-256 | 93e084c3617264301235deb468054d6e3d263fbb1a48f046c75942df5dcc2f1c |
Recovered source transcript
Download complete source text ↓53 lines · 3.5 KB
-- +goose Up
-- SAM.gov Contract Opportunities cache (screenMATCH requirement feed).
--
-- This is a GLOBAL PUBLIC reference catalog, NOT tenant data: federal opportunities published on
-- SAM.gov are public and identical for every org, keyed by SAM's own notice_id. It therefore has NO
-- organization_id and is deliberately OUTSIDE Row-Level Security (like audit_logs) — every tenant
-- reads the same cached catalog. The runtime app role gets SELECT only; the daily sync writes as the
-- migrator role (see cmd/sam-sync). The frontend never calls SAM live — it always reads this store,
-- so the demo is fast and survives SAM downtime or quota exhaustion.
CREATE TABLE sam_opportunities (
notice_id text PRIMARY KEY, -- SAM noticeId (globally unique; upsert key)
title text NOT NULL DEFAULT '',
solicitation_number text NOT NULL DEFAULT '',
department text NOT NULL DEFAULT '', -- first segment of fullParentPathName
full_parent_path text NOT NULL DEFAULT '', -- full dotted agency hierarchy
sub_agency text NOT NULL DEFAULT '', -- normalized (AIR FORCE / SPACE FORCE / 2nd segment)
naics_code text NOT NULL DEFAULT '',
psc_code text NOT NULL DEFAULT '', -- classificationCode (PSC)
set_aside_code text NOT NULL DEFAULT '', -- typeOfSetAside (e.g. 8A)
set_aside text NOT NULL DEFAULT '', -- typeOfSetAsideDescription
notice_type text NOT NULL DEFAULT '', -- type
base_type text NOT NULL DEFAULT '', -- baseType
posted_date date, -- nullable
response_deadline timestamptz, -- nullable (carries a time)
active boolean NOT NULL DEFAULT true, -- SAM "active" Yes/No -> bool
pop_city text NOT NULL DEFAULT '', -- placeOfPerformance.city.name
pop_state text NOT NULL DEFAULT '', -- placeOfPerformance.state.code
pop_zip text NOT NULL DEFAULT '',
ui_link text NOT NULL DEFAULT '', -- public sam.gov notice link
description_url text NOT NULL DEFAULT '', -- description (a URL; append api_key to fetch text)
resource_links jsonb NOT NULL DEFAULT '[]', -- attachment download URLs
poc jsonb NOT NULL DEFAULT '[]', -- pointOfContact array
raw jsonb NOT NULL, -- full SAM JSON for this notice
source text NOT NULL DEFAULT 'SAM.gov',
synced_at timestamptz NOT NULL DEFAULT now()
);
-- In-memory/SQL filter columns (the sync pulls by NAICS only; the app filters 8(a)/agency/state here).
CREATE INDEX idx_sam_opps_naics ON sam_opportunities (naics_code);
CREATE INDEX idx_sam_opps_state ON sam_opportunities (pop_state);
CREATE INDEX idx_sam_opps_setaside ON sam_opportunities (set_aside_code);
CREATE INDEX idx_sam_opps_department ON sam_opportunities (department);
CREATE INDEX idx_sam_opps_subagency ON sam_opportunities (sub_agency);
CREATE INDEX idx_sam_opps_active_posted ON sam_opportunities (active, posted_date DESC);
-- Runtime app role: read-only (NOBYPASSRLS; no RLS on this table so it sees the whole public catalog).
GRANT SELECT ON sam_opportunities TO missionmatch_app;
-- The migrator owns the table and runs the sync writes; compliance never needs it.
-- +goose Down
DROP TABLE IF EXISTS sam_opportunities;