Janady13/missionmatch / db/migrations/0069_ingest_manifest.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0069_ingest_manifest.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0069_ingest_manifest.sql
Original SHA-256	7f649275ec06beaaa4119aff141f007768330f4029d443c30a5d3873cb418bca
Git blob SHA	4be991c8d65d59088c1e77ceda174c5da2bd899b
Reading-copy SHA-256	7f649275ec06beaaa4119aff141f007768330f4029d443c30a5d3873cb418bca

Original source ↗

Recovered source transcript

Download complete source text ↓44 lines · 2.6 KB

-- +goose Up
-- ingest_manifest is the provenance ledger for every external source pulled into the platform: each sync
-- run and each ingested document records one row here (source, url, license, fetched_at, record_count,
-- content_hash), and every synced row and knowledge card traces back to a manifest row by id. Like the
-- sam_opportunities and opportunity_records catalogs it describes, the manifest is GLOBAL PUBLIC reference
-- data, identical for every org, so it carries NO organization_id and sits OUTSIDE Row Level Security. The
-- content_hash makes a re run idempotent and lets a consumer prove what was ingested without holding the
-- payload. The migrator writes during a sync; the app role also inserts a manifest row when it ingests a
-- document during a request, and reads for provenance display.

CREATE TABLE ingest_manifest (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    source        text NOT NULL,                       -- the external source, e.g. SAM.gov, USASpending, O*NET
    url           text NOT NULL DEFAULT '',            -- the fetched endpoint or document URL
    license       text NOT NULL DEFAULT '',            -- the license or terms the data was ingested under
    fetched_at    timestamptz NOT NULL DEFAULT now(),  -- when the source was pulled
    record_count  integer NOT NULL DEFAULT 0,          -- how many records this manifest covers
    content_hash  text NOT NULL DEFAULT '',            -- hash of the ingested payload, for idempotence and provenance
    created_at    timestamptz NOT NULL DEFAULT now()
);

-- Provenance lookups: the most recent pull per source, and idempotence checks by content hash.
CREATE INDEX idx_ingest_manifest_source_fetched ON ingest_manifest (source, fetched_at DESC);
CREATE INDEX idx_ingest_manifest_content_hash   ON ingest_manifest (content_hash);

-- Global public reference data, no RLS. The migrator owns the table and writes during syncs; the app role
-- reads for provenance and inserts a manifest row when it ingests a document during a request.
GRANT SELECT, INSERT ON ingest_manifest TO missionmatch_app;

-- +goose Down
-- The down refuses to destroy provenance rows the application authored, per the directive: a manifest is an
-- audit style trace, so a down that dropped a populated table would erase the lineage of live tenant rows.
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
  SELECT count(*) INTO n FROM ingest_manifest;
  IF n > 0 THEN
    RAISE EXCEPTION 'refusing to drop ingest_manifest: % provenance rows present', n;
  END IF;
END $$;
-- +goose StatementEnd
DROP TABLE IF EXISTS ingest_manifest;