Janady13/missionmatch / db/migrations/0022_skill_alias_relationship.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0022_skill_alias_relationship.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0022_skill_alias_relationship.sql
Original SHA-256	83ebcc0b4be76f8bafeeb6dcdf8c16802f7c7239d49d84dff0d08ff0bcc7243c
Git blob SHA	12ab3a886814af1c138b4b4478a349e1c6f2c9dd
Reading-copy SHA-256	83ebcc0b4be76f8bafeeb6dcdf8c16802f7c7239d49d84dff0d08ff0bcc7243c

Original source ↗

Every recorded occurrence

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

Recovered source transcript

Download complete source text ↓56 lines · 3.4 KB

-- +goose Up
-- WS2 deterministic alias and relationship layer. Two GLOBAL reference tables, like skills they carry no
-- organization_id and sit outside RLS, app role SELECT only, migrator writes. They FK to the global skills
-- table. The engine consults these (curated, ontology backed) before the semantic matcher: an alias is an
-- exact equivalence, a relationship is a curated partial credit. Weights, bands, and the 0.62 threshold are
-- unchanged; this registers ModelVersion v1.2.0 for the new curated match capability.

ALTER TABLE skills ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT '';
ALTER TABLE skills ADD COLUMN IF NOT EXISTS soc_code text NOT NULL DEFAULT '';

CREATE TABLE skill_aliases (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    skill_id    uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    alias_text  text NOT NULL,
    alias_type  text NOT NULL CHECK (alias_type IN ('acronym','synonym','vendor_term','legacy')),
    source      text NOT NULL DEFAULT '',
    confidence  numeric(3,2) NOT NULL DEFAULT 1.0 CHECK (confidence >= 0 AND confidence <= 1),
    verified_at timestamptz,
    created_at  timestamptz NOT NULL DEFAULT now()
);
-- A functional unique on lower(alias_text) must be a separate index, Postgres does not allow an
-- expression in an inline UNIQUE constraint. The seed relies on ON CONFLICT (lower(alias_text)).
CREATE UNIQUE INDEX skill_aliases_alias_lower_key ON skill_aliases (lower(alias_text));
CREATE INDEX idx_skill_aliases_skill ON skill_aliases (skill_id);

CREATE TABLE skill_relationships (
    id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    from_skill_id uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    to_skill_id   uuid NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    relation      text NOT NULL CHECK (relation IN ('requires','substitutes_for','broader_than','related_to')),
    strength      numeric(3,2) NOT NULL DEFAULT 1.0 CHECK (strength >= 0 AND strength <= 1),
    source        text NOT NULL DEFAULT '',
    verified_at   timestamptz,
    created_at    timestamptz NOT NULL DEFAULT now(),
    UNIQUE (from_skill_id, to_skill_id, relation)
);
CREATE INDEX idx_skill_rel_from ON skill_relationships (from_skill_id);
CREATE INDEX idx_skill_rel_to ON skill_relationships (to_skill_id);

GRANT SELECT ON skill_aliases TO missionmatch_app;
GRANT SELECT ON skill_relationships TO missionmatch_app;

INSERT INTO aedt_model_versions (version, weights, band_cuts, notes) VALUES (
    'v1.2.0',
    '{"clearance":0.25,"skills":0.25,"certifications":0.15,"customer_experience":0.15,"experience":0.10,"location_availability":0.10}',
    '{"green":90,"yellow":75}',
    'Adds the curated deterministic alias and relationship match layer (skill_aliases and skill_relationships). An alias resolves a curated ontology backed equivalence as an exact match (similarity 1.0); a substitutes_for or broader_than relationship contributes its curated strength as a partial match, gated at or above the 0.62 semantic threshold. The six component weights, the green and yellow band cuts, and the 0.62 threshold are unchanged from v1.1.0. Clearance remains a self reported indicator, never an input proxy for a protected attribute.'
) ON CONFLICT (version) DO NOTHING;

-- +goose Down
DELETE FROM aedt_model_versions WHERE version = 'v1.2.0';
DROP TABLE IF EXISTS skill_relationships;
DROP TABLE IF EXISTS skill_aliases;
ALTER TABLE skills DROP COLUMN IF EXISTS soc_code;
ALTER TABLE skills DROP COLUMN IF EXISTS source;