Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0060_solicitation_extractions.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0060_solicitation_extractions.sql |
| Original SHA-256 | 3ecd3120fc46a9c547c9737e9b34c1466994eb266084568e8edcfed9f3c894f2 |
| Git blob SHA | bdcd24876decfaaee4ab60d4f963b54fa36e3ff4 |
| Reading-copy SHA-256 | 3ecd3120fc46a9c547c9737e9b34c1466994eb266084568e8edcfed9f3c894f2 |
Every recorded occurrence
These 2 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/0060_solicitation_extractions.sql | feature/opportunity-to-outcome | 720d47fae476b0d38477ae4401697d372b1ea2bc | Open original |
| Janady13/missionmatch / db/migrations/0060_solicitation_extractions.sql | feature/aioe-upgrade | 2178e7298d0e5d9af83afa12402fd587f0ba6ed8 | Open original |
Recovered source transcript
Download complete source text ↓81 lines · 4.9 KB
-- +goose Up
-- Solicitation extractions, brought under migration control.
--
-- This table already exists in the production database on M4 holding 2164 rows, but no migration in
-- either set creates it. It was applied to production by hand as raw DDL by the growth engine work,
-- which extracts labor categories, clearances, and skills out of SAM.gov solicitations. RUNBOOK_AIOE
-- recorded the debt and said to renumber the uncommitted copy to 0045 before it entered the repo; 0045
-- was taken in the meantime, so the number in that instruction is stale and this migration is 0060.
--
-- Leaving it uncommitted was not cosmetic. Under the database per tenant model a tenant database is
-- built by replaying the migration set, so a tenant provisioned anywhere other than the original M4
-- machine would come up WITHOUT this table and the growth engine would fail against it, silently and
-- only for the new tenant. Capturing it here is what makes a provisioned tenant reproducible.
--
-- The definition below is transcribed from the live table so the two match exactly, verified against
-- pg_dump of production rather than reconstructed from memory.
--
-- There is deliberately no organization_id and no row level security here, which is the one apparent
-- departure from the tenancy rule. It is correct: the parent table opportunity_records is likewise
-- global and unsecured because SAM.gov solicitations are PUBLIC federal data that is identical for
-- every tenant, not tenant owned records. An extraction is a derived fact about a public solicitation,
-- so it inherits the parent's scope. Adding RLS here would imply a per tenant ownership that does not
-- exist. If opportunity_records ever becomes tenant scoped, this table must change with it.
CREATE TABLE IF NOT EXISTS solicitation_extractions (
id uuid NOT NULL DEFAULT gen_random_uuid(),
opportunity_record_id uuid NOT NULL,
labor_category text,
required_clearance text,
required_skills jsonb NOT NULL DEFAULT '[]'::jsonb,
required_certifications jsonb NOT NULL DEFAULT '[]'::jsonb,
required_years integer,
confidence numeric,
model_version text NOT NULL,
source_signal text,
extract_status text NOT NULL DEFAULT 'ok'::text,
extracted_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT solicitation_extractions_pkey PRIMARY KEY (id),
-- One extraction per solicitation. This is what makes the extractor idempotent: a re run upserts on
-- this key rather than accumulating a second opinion about the same solicitation.
CONSTRAINT solicitation_extractions_uniq_record UNIQUE (opportunity_record_id),
-- CASCADE, because an extraction is meaningless once the solicitation it describes is gone.
CONSTRAINT solicitation_extractions_opportunity_record_id_fkey
FOREIGN KEY (opportunity_record_id) REFERENCES opportunity_records (id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_solext_clearance ON solicitation_extractions (required_clearance);
CREATE INDEX IF NOT EXISTS idx_solext_extracted_at ON solicitation_extractions (extracted_at);
CREATE INDEX IF NOT EXISTS idx_solext_labor ON solicitation_extractions (labor_category);
-- Read only for the application and compliance roles, matching production exactly. The extractor runs
-- as the migrator role, so no write grant is issued to the application: the API reads extractions and
-- never authors them.
GRANT SELECT ON solicitation_extractions TO missionmatch_app;
GRANT SELECT ON solicitation_extractions TO missionmatch_compliance;
-- +goose Down
-- This down is deliberately asymmetric with the up, and refuses rather than dropping when the table
-- holds rows. On the production database this migration CREATES NOTHING: the table and its 2164 rows
-- were already there, applied by hand, and this migration only brings them under version control. A
-- plain DROP would therefore destroy data that the corresponding up never authored, which is the one
-- case where the usual symmetry of a migration pair is wrong. On a fresh database, where the up really
-- did create the table and it is still empty, the drop proceeds and up, down, up round trips cleanly.
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
IF to_regclass('public.solicitation_extractions') IS NULL THEN
RETURN;
END IF;
EXECUTE 'SELECT count(*) FROM solicitation_extractions' INTO n;
IF n > 0 THEN
RAISE EXCEPTION
'refusing to drop solicitation_extractions: % rows present. This migration did not create '
'that data, it only placed an existing hand applied table under version control. Export or '
'truncate deliberately first if the drop is really intended.', n;
END IF;
DROP TABLE solicitation_extractions;
END
$$;
-- +goose StatementEnd