Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql

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	1675031db74316e100782d063e9eda1a597fe3a1
Branch	workstream-a-opportunity-intelligence
Path or archive member	db/migrations/0005_sam_opportunities.sql
Original SHA-256	a8757f9b409057052f10b4f24a44a70ce0e2da2d786313794b592a0ffb60b1ed
Git blob SHA	0b44771e33f338bb35e32a325031e68a865fae7f
Reading-copy SHA-256	a8757f9b409057052f10b4f24a44a70ce0e2da2d786313794b592a0ffb60b1ed

Original source ↗

Every recorded occurrence

These 7 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql	workstream-a-opportunity-intelligence	1675031db74316e100782d063e9eda1a597fe3a1	Open original
Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql	workstream-d-command-center	255e7d1d420abf5e277d0cd8c9b14f6f72b6acb9	Open original
Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql	workstream-e-commercial-stubs	4437da660539284a400ee380b9b427a300c369ee	Open original
Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql	workstream-b-sam-contract-awards	d5101cceb98e70000cebb31d3e7ac73d747d3a6d	Open original
Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql	workstream-d-command-center	255e7d1d420abf5e277d0cd8c9b14f6f72b6acb9	Open original
Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql	workstream-a-opportunity-intelligence	1675031db74316e100782d063e9eda1a597fe3a1	Open original
Janady13/missionmatch / db/migrations/0005_sam_opportunities.sql	workstream-e-commercial-stubs	4437da660539284a400ee380b9b427a300c369ee	Open original

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;