Janady13/missionmatch / db/migrations/0002_matching.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0002_matching.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	39df94f92b2a33f852481fe1954dabed841524bb
Branch	w0-aedt-compliance
Path or archive member	db/migrations/0002_matching.sql
Original SHA-256	d776ab5d4ddc203cbc6a9568869016b11e7e037f443eb035e0e3f2a452a087b1
Git blob SHA	a1e3314e1cd724f89ab12e302d13fa9809eb11a4
Reading-copy SHA-256	d776ab5d4ddc203cbc6a9568869016b11e7e037f443eb035e0e3f2a452a087b1
Original source ↗
Recovered source transcript

Download complete source text ↓281 lines · 15.8 KB
-- +goose Up
-- Matching domain: skills ontology (global), opportunities, candidates, requirements, slates.
--
-- Tenant isolation is defense-in-depth: RLS on organization_id (ENABLE+FORCE, USING+WITH CHECK) PLUS
-- COMPOSITE foreign keys that carry organization_id, so a child row can NEVER reference a parent in
-- another tenant — FK validation bypasses RLS, so WITH CHECK alone is necessary-but-not-sufficient
-- [review RLS-1/INT-4]. Every tenant parent has UNIQUE (organization_id, id) as the composite-FK target.
-- Clearance/poly/status/band are text + CHECK mirroring the engine enums' DB codes (engine Code()).

-- ---------------------------------------------------------------------------
-- skills — GLOBAL ontology reference (shared across tenants). No RLS. Partial
-- HNSW so the index matches the "WHERE embedding IS NOT NULL" query predicate.
-- ---------------------------------------------------------------------------
CREATE TABLE skills (
    id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    name        text NOT NULL UNIQUE,
    category    text,
    embedding   vector(384),     -- MVP self-hosted MiniLM-class dim; config-pinned (STACK_DECISIONS.md §3/§6)
    created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX idx_skills_embedding_hnsw ON skills
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64)
    WHERE embedding IS NOT NULL;
GRANT SELECT, INSERT, UPDATE ON skills TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- opportunities — a proposal/solicitation that groups requirements, so
-- /proposal-readiness aggregates across a proposal, not a single LCAT [review COMPL-2].
-- ---------------------------------------------------------------------------
CREATE TABLE opportunities (
    id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id      uuid NOT NULL REFERENCES organizations(id),
    name                 text NOT NULL,
    solicitation_number  text,
    customer_agency      text,
    due_date             date,
    status               text NOT NULL DEFAULT 'open'
                           CHECK (status IN ('open','submitted','won','lost','no_bid')),
    created_at           timestamptz NOT NULL DEFAULT now(),
    updated_at           timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_opportunities_org_id UNIQUE (organization_id, id)
);
CREATE INDEX idx_opportunities_org ON opportunities (organization_id, created_at DESC);
ALTER TABLE opportunities ENABLE ROW LEVEL SECURITY;
ALTER TABLE opportunities FORCE  ROW LEVEL SECURITY;
CREATE POLICY opportunities_tenant_isolation ON opportunities
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- ---------------------------------------------------------------------------
-- candidates — tenant-scoped. NO demographic columns (AEDT). clearance_status is
-- a stored flag; clearance_expires_at is the source of truth for /clearance-status
-- and as-of-date active/expired scoring [review COMPL-1]. Partial GIN/HNSW match
-- the soft-delete query predicate [review PGV-6].
-- ---------------------------------------------------------------------------
CREATE TABLE candidates (
    id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id       uuid NOT NULL REFERENCES organizations(id),
    full_name             text NOT NULL,
    email                 text,
    headline              text,
    clearance_level       text NOT NULL DEFAULT 'unknown'
                            CHECK (clearance_level IN ('unknown','none','confidential','secret','top_secret','ts_sci')),
    clearance_poly        text NOT NULL DEFAULT 'none'
                            CHECK (clearance_poly IN ('none','ci','full_scope')),
    clearance_status      text NOT NULL DEFAULT 'unknown'
                            CHECK (clearance_status IN ('unknown','active','expired','inactive')),
    clearance_granted_at  date,
    clearance_expires_at  date,
    reinvestigation_due_at date,
    years_experience      int  NOT NULL DEFAULT 0 CHECK (years_experience >= 0),
    location              text,
    remote                boolean NOT NULL DEFAULT false,
    relocatable           boolean NOT NULL DEFAULT false,
    available             boolean NOT NULL DEFAULT false,
    resume_embedding      vector(384),
    search_tsv            tsvector GENERATED ALWAYS AS (
                              to_tsvector('english',
                                  coalesce(full_name,'') || ' ' || coalesce(headline,'') || ' ' || coalesce(location,''))
                          ) STORED,
    created_at            timestamptz NOT NULL DEFAULT now(),
    updated_at            timestamptz NOT NULL DEFAULT now(),
    deleted_at            timestamptz,
    CONSTRAINT uq_candidates_org_id UNIQUE (organization_id, id)
);
CREATE INDEX idx_candidates_org ON candidates (organization_id, created_at DESC);
CREATE INDEX idx_candidates_tsv ON candidates USING gin (search_tsv) WHERE deleted_at IS NULL;
CREATE INDEX idx_candidates_emb_hnsw ON candidates USING hnsw (resume_embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64)
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL;
CREATE INDEX idx_candidates_clearance_exp ON candidates (organization_id, clearance_expires_at)
    WHERE deleted_at IS NULL;
ALTER TABLE candidates ENABLE ROW LEVEL SECURITY;
ALTER TABLE candidates FORCE  ROW LEVEL SECURITY;
CREATE POLICY candidates_tenant_isolation ON candidates
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- candidate child tables: denormalized org + COMPOSITE FK to the parent (org,id) ----------------
CREATE TABLE candidate_skills (
    organization_id uuid NOT NULL,
    candidate_id    uuid NOT NULL,
    skill_id        uuid NOT NULL REFERENCES skills(id),
    PRIMARY KEY (candidate_id, skill_id),
    FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_cand_skills_org ON candidate_skills (organization_id, candidate_id);
ALTER TABLE candidate_skills ENABLE ROW LEVEL SECURITY;
ALTER TABLE candidate_skills FORCE  ROW LEVEL SECURITY;
CREATE POLICY cand_skills_isolation ON candidate_skills
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

CREATE TABLE candidate_certifications (
    organization_id uuid NOT NULL,
    candidate_id    uuid NOT NULL,
    name            text NOT NULL,
    PRIMARY KEY (candidate_id, name),
    FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_cand_certs_org ON candidate_certifications (organization_id, candidate_id);
ALTER TABLE candidate_certifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE candidate_certifications FORCE  ROW LEVEL SECURITY;
CREATE POLICY cand_certs_isolation ON candidate_certifications
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

CREATE TABLE candidate_customer_experience (
    organization_id uuid NOT NULL,
    candidate_id    uuid NOT NULL,
    agency          text NOT NULL,
    PRIMARY KEY (candidate_id, agency),
    FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_cand_custexp_org ON candidate_customer_experience (organization_id, candidate_id);
ALTER TABLE candidate_customer_experience ENABLE ROW LEVEL SECURITY;
ALTER TABLE candidate_customer_experience FORCE  ROW LEVEL SECURITY;
CREATE POLICY cand_custexp_isolation ON candidate_customer_experience
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- ---------------------------------------------------------------------------
-- requirements — tenant-scoped; optionally grouped under an opportunity.
-- ---------------------------------------------------------------------------
CREATE TABLE requirements (
    id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id   uuid NOT NULL REFERENCES organizations(id),
    opportunity_id    uuid,
    title             text NOT NULL,
    customer_agency   text,
    required_level    text NOT NULL DEFAULT 'none'
                        CHECK (required_level IN ('unknown','none','confidential','secret','top_secret','ts_sci')),
    required_poly     text NOT NULL DEFAULT 'none'
                        CHECK (required_poly IN ('none','ci','full_scope')),
    required_years    int  NOT NULL DEFAULT 0 CHECK (required_years >= 0),
    work_location     text,
    remote_allowed    boolean NOT NULL DEFAULT false,
    key_personnel     boolean NOT NULL DEFAULT false,
    created_at        timestamptz NOT NULL DEFAULT now(),
    updated_at        timestamptz NOT NULL DEFAULT now(),
    deleted_at        timestamptz,
    CONSTRAINT uq_requirements_org_id UNIQUE (organization_id, id),
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL
);
CREATE INDEX idx_requirements_org ON requirements (organization_id, created_at DESC);
CREATE INDEX idx_requirements_opp ON requirements (organization_id, opportunity_id);
ALTER TABLE requirements ENABLE ROW LEVEL SECURITY;
ALTER TABLE requirements FORCE  ROW LEVEL SECURITY;
CREATE POLICY requirements_tenant_isolation ON requirements
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

CREATE TABLE required_skills (
    organization_id uuid NOT NULL,
    requirement_id  uuid NOT NULL,
    skill_id        uuid NOT NULL REFERENCES skills(id),
    mandatory       boolean NOT NULL DEFAULT false,
    PRIMARY KEY (requirement_id, skill_id),
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_req_skills_org ON required_skills (organization_id, requirement_id);
ALTER TABLE required_skills ENABLE ROW LEVEL SECURITY;
ALTER TABLE required_skills FORCE  ROW LEVEL SECURITY;
CREATE POLICY req_skills_isolation ON required_skills
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

CREATE TABLE required_certifications (
    organization_id uuid NOT NULL,
    requirement_id  uuid NOT NULL,
    name            text NOT NULL,
    PRIMARY KEY (requirement_id, name),
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_req_certs_org ON required_certifications (organization_id, requirement_id);
ALTER TABLE required_certifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE required_certifications FORCE  ROW LEVEL SECURITY;
CREATE POLICY req_certs_isolation ON required_certifications
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

-- ---------------------------------------------------------------------------
-- slates + slate_members. slate_members freezes the explainable match result as
-- the AEDT decision record. Scores are numeric(7,4) so a perfect 100.0000 fits
-- (numeric(6,4) maxed at 99.9999 and overflowed) with CHECK 0..100 [review SQLC-1].
-- ---------------------------------------------------------------------------
CREATE TABLE slates (
    id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id  uuid NOT NULL REFERENCES organizations(id),
    requirement_id   uuid NOT NULL,
    name             text NOT NULL,
    status           text NOT NULL DEFAULT 'draft'
                       CHECK (status IN ('draft','in_review','finalized')),
    created_by       uuid,
    created_at       timestamptz NOT NULL DEFAULT now(),
    updated_at       timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT uq_slates_org_id UNIQUE (organization_id, id),
    FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id),
    FOREIGN KEY (organization_id, created_by)     REFERENCES users (organization_id, id)
);
CREATE INDEX idx_slates_org ON slates (organization_id, created_at DESC);
CREATE INDEX idx_slates_req ON slates (organization_id, requirement_id);
ALTER TABLE slates ENABLE ROW LEVEL SECURITY;
ALTER TABLE slates FORCE  ROW LEVEL SECURITY;
CREATE POLICY slates_tenant_isolation ON slates
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

CREATE TABLE slate_members (
    organization_id   uuid NOT NULL,
    slate_id          uuid NOT NULL,
    candidate_id      uuid NOT NULL,
    total_score       numeric(7,4) NOT NULL CHECK (total_score      BETWEEN 0 AND 100),
    band              text NOT NULL CHECK (band IN ('green','yellow','red')),
    clearance_score   numeric(7,4) NOT NULL CHECK (clearance_score  BETWEEN 0 AND 100),
    skills_score      numeric(7,4) NOT NULL CHECK (skills_score     BETWEEN 0 AND 100),
    certs_score       numeric(7,4) NOT NULL CHECK (certs_score      BETWEEN 0 AND 100),
    customer_score    numeric(7,4) NOT NULL CHECK (customer_score   BETWEEN 0 AND 100),
    experience_score  numeric(7,4) NOT NULL CHECK (experience_score BETWEEN 0 AND 100),
    location_score    numeric(7,4) NOT NULL CHECK (location_score   BETWEEN 0 AND 100),
    explanation       text  NOT NULL,
    trace             jsonb NOT NULL,
    gap_summary       jsonb NOT NULL DEFAULT '[]',
    decision          text  NOT NULL DEFAULT 'pending'
                        CHECK (decision IN ('pending','approved','rejected')),  -- human-in-the-loop; never auto-reject
    decided_by        uuid,
    decided_at        timestamptz,
    rank              int,
    created_at        timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (slate_id, candidate_id),
    FOREIGN KEY (organization_id, slate_id)     REFERENCES slates (organization_id, id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id),
    FOREIGN KEY (organization_id, decided_by)   REFERENCES users (organization_id, id)
);
CREATE INDEX idx_slate_members_org ON slate_members (organization_id, slate_id);
ALTER TABLE slate_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE slate_members FORCE  ROW LEVEL SECURITY;
CREATE POLICY slate_members_isolation ON slate_members
    USING      (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
    WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);

GRANT SELECT, INSERT, UPDATE, DELETE ON
    opportunities,
    candidates, candidate_skills, candidate_certifications, candidate_customer_experience,
    requirements, required_skills, required_certifications,
    slates, slate_members
    TO missionmatch_app;

-- +goose Down
DROP TABLE IF EXISTS slate_members;
DROP TABLE IF EXISTS slates;
DROP TABLE IF EXISTS required_certifications;
DROP TABLE IF EXISTS required_skills;
DROP TABLE IF EXISTS requirements;
DROP TABLE IF EXISTS candidate_customer_experience;
DROP TABLE IF EXISTS candidate_certifications;
DROP TABLE IF EXISTS candidate_skills;
DROP TABLE IF EXISTS candidates;
DROP TABLE IF EXISTS opportunities;
DROP TABLE IF EXISTS skills;