Janady13/missionmatch / db/migrations/0021_ontology.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0021_ontology.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0021_ontology.sql
Original SHA-256	8aa04a6b32dfd1e7ce1203fc5ed33277db9f6fcca9f3ef5f88a9acc4e1505de3
Git blob SHA	125d91fd75b10cf4ad2d1e09b2cf023b23b9bc31
Reading-copy SHA-256	8aa04a6b32dfd1e7ce1203fc5ed33277db9f6fcca9f3ef5f88a9acc4e1505de3

Original source ↗

Every recorded occurrence

These 20 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/0021_ontology.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	collision-deconfliction	4ee5a09972608ed89f8377c6665542d5f450f0a6	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	ws6-retrieval	6c56e29231ec05735927fa265c97d517499dfeaf	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	m5-price-to-win	41000ad30fff4129fdaf5ec84e15ba14c27d4890	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	ws1-ontology-sync	b596f449a32a1016fbd403c36505cc00a1ac9ee6	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	master	1e9249840a4302ec82e123b9c9cb6f75badea3e4	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	ws8-teaching-loop	c823f0fb6bb2aba0f39001eb6de96f4000d9321f	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	mag-substrate	b82c7100a492dec67dfd1eda6b3f53def859be35	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	ws3-clearance	4b8e7ce594c068621867f9c9deed0ec64ae6c553	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	phase2-eval-fairness	d361af254701975fe239927656ababb56374655e	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	time-to-fill-survival	b66e1e505863d4f17741a4b9ede73509e45bd6c6	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	graphrag-foundation	b0ddeb9f633fd138d09309d1a17b7d13bc3a67f7	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	ws9-bench-ingestion	f585a9ef32fa554663ce1fba9a414a54b6e49cf0	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	ws2-alias-relationship	e4dada4601c6eb101830f9d223a9655dfa120616	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	presentation-readiness	eb574acaa29d71ce4d0f852572e87c275c79fdb7	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	vendor-intelligence	fe9796a19c261951d3f2565206c0c1b5ac294465	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	ws7-federal-signal	6a9f6ce9cfb13345ffb47770d4960e7c2d9c17c2	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	phase2-connectors	e038d4c687aec16f8bd9bc4d907eea14d91f8704	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original
Janady13/missionmatch / db/migrations/0021_ontology.sql	capture-readiness	bae6af7cb2997e819174a9e9cfff5e16067a1975	Open original

Recovered source transcript

Download complete source text ↓71 lines · 3.9 KB

-- +goose Up
-- WS1 ontology staging and reference tables (AI Grounding Program). Like skills, sam_opportunities,
-- and opportunity_records these are GLOBAL PUBLIC reference data, NOT tenant data: the federal
-- vocabulary is identical for every org, so these tables carry NO organization_id and sit OUTSIDE Row
-- Level Security. The runtime app role gets SELECT only; cmd/ontology-sync writes as the migrator role.
-- ontology_terms is the raw pulled vocabulary with provenance; ontology_crosswalk links codes across
-- frameworks (NICE to DCWF, SOC to skill); mission_spine is the hand curated IC vocabulary, seeded here.

CREATE TABLE ontology_terms (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    source_name      text NOT NULL,
    source_record_id text NOT NULL,
    term_kind        text NOT NULL CHECK (term_kind IN ('skill','labor_category','work_role','ksa','psc_code','mission_area')),
    term             text NOT NULL DEFAULT '',
    code             text NOT NULL DEFAULT '',
    category         text NOT NULL DEFAULT '',
    description      text NOT NULL DEFAULT '',
    raw_object_key   text NOT NULL DEFAULT '',
    raw_sha256       text NOT NULL DEFAULT '',
    fetched_at       timestamptz NOT NULL,
    synced_at        timestamptz NOT NULL DEFAULT now(),
    UNIQUE (source_name, source_record_id)
);
CREATE INDEX idx_ontology_terms_kind ON ontology_terms (term_kind);
CREATE INDEX idx_ontology_terms_code ON ontology_terms (code);

CREATE TABLE ontology_crosswalk (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    from_system text NOT NULL,
    from_code   text NOT NULL,
    to_system   text NOT NULL,
    to_code     text NOT NULL,
    relation    text NOT NULL DEFAULT 'maps_to',
    source_name text NOT NULL DEFAULT '',
    synced_at   timestamptz NOT NULL DEFAULT now(),
    UNIQUE (from_system, from_code, to_system, to_code)
);

CREATE TABLE mission_spine (
    id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    version            text NOT NULL,
    term               text NOT NULL,
    canonical_definition text NOT NULL DEFAULT '',
    authority          text NOT NULL DEFAULT '',
    source_doc         text NOT NULL DEFAULT '',
    source_url         text NOT NULL DEFAULT '',
    effective_date     date,
    UNIQUE (version, term)
);

INSERT INTO mission_spine (version, term, canonical_definition, authority, source_doc) VALUES
 ('v1','SIGINT','Intelligence derived from signals, communications, electronic, and foreign instrumentation.','ODNI','Intelligence disciplines'),
 ('v1','GEOINT','Intelligence from the exploitation and analysis of imagery and geospatial information.','ODNI','Intelligence disciplines'),
 ('v1','HUMINT','Intelligence collected from human sources.','ODNI','Intelligence disciplines'),
 ('v1','MASINT','Measurement and signature intelligence.','ODNI','Intelligence disciplines'),
 ('v1','OSINT','Intelligence produced from publicly available information.','ODNI','IC OSINT Strategy'),
 ('v1','Satellite C2','Command and control of satellite systems.','JP 3-14','Space Operations'),
 ('v1','Cross Domain Solution','A controlled interface providing manual or automated access between security domains.','CNSSI 4009','National Information Assurance Glossary'),
 ('v1','COMSEC','Communications security: measures to deny unauthorized access to telecommunications.','CNSSI 4009','National Information Assurance Glossary'),
 ('v1','TEMPEST','Protection against compromising emanations.','CNSSI 4009','National Information Assurance Glossary'),
 ('v1','Electronic Warfare','Military action using electromagnetic and directed energy.','JP 3-13.1','Electronic Warfare');

GRANT SELECT ON ontology_terms TO missionmatch_app;
GRANT SELECT ON ontology_crosswalk TO missionmatch_app;
GRANT SELECT ON mission_spine TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS mission_spine;
DROP TABLE IF EXISTS ontology_crosswalk;
DROP TABLE IF EXISTS ontology_terms;