Janady13/missionmatch / db/migrations/0077_requirement_drafts.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0077_requirement_drafts.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0077_requirement_drafts.sql
Original SHA-256	dd51e0da8e9e3ab425a1e638d3ac3e79a5e74482dbb3fdbc180c3f9760bdf650
Git blob SHA	7cb2e578897de81a7651c6c73eaaf44c7c9e5ce8
Reading-copy SHA-256	dd51e0da8e9e3ab425a1e638d3ac3e79a5e74482dbb3fdbc180c3f9760bdf650
Original source ↗
Recovered source transcript

Download complete source text ↓115 lines · 6.9 KB
-- +goose Up
-- requirement_drafts IS THE SAFETY BOUNDARY. Everything the machine derives about who a solicitation needs
-- lands here, where the scoring engine cannot see it. A draft becomes a live requirement only when a named
-- human approves it, in one audited transaction that takes FOR UPDATE on the draft row. That single lock is
-- what makes racing approvals elect exactly one winner, matching the nine existing lock patterns.
--
-- The one way latch is enforced by the schema, not only by the service:
--
--   ck_drafts_approved_has_requirement  an approved draft MUST name the requirement it produced, and a draft
--                                       that is not approved must NOT name one. So "approved" can never be a
--                                       status a row merely claims; it is a status that carries its result.
--   ck_drafts_approved_has_reviewer     an approved or rejected draft MUST name the human who decided it and
--                                       when. An automated path cannot approve, because it has no user id to
--                                       write, which is the AEDT human in the loop requirement expressed as a
--                                       constraint rather than a convention.
--
-- Skills and certifications are stored as text arrays rather than as foreign keys to the skills ontology on
-- purpose: a draft is pre approval, and a solicitation routinely names a skill the ontology does not carry
-- yet. Forcing an ontology row at draft time would either lose the requirement or let extraction write the
-- GLOBAL ontology, which is superuser curation, not extraction. Resolution to skill ids happens at
-- promotion, where a human is present.

CREATE TABLE requirement_drafts (
    organization_id             uuid NOT NULL REFERENCES organizations(id),
    id                          uuid NOT NULL DEFAULT gen_random_uuid(),
    opportunity_id              uuid NOT NULL,
    source_scope_item_id        uuid,
    labor_category              text NOT NULL DEFAULT '',
    quantity                    integer NOT NULL DEFAULT 1 CHECK (quantity >= 0),
    clearance_level             text NOT NULL DEFAULT 'unknown'
                                  CHECK (clearance_level IN ('unknown','none','confidential','secret','top_secret','ts_sci')),
    polygraph                   text NOT NULL DEFAULT 'none'
                                  CHECK (polygraph IN ('none','ci','full_scope')),
    compartments                text[] NOT NULL DEFAULT '{}',
    required_skills             text[] NOT NULL DEFAULT '{}',
    mandatory_skill_flags       boolean[] NOT NULL DEFAULT '{}',
    required_certifications     text[] NOT NULL DEFAULT '{}',
    minimum_years_experience    integer NOT NULL DEFAULT 0 CHECK (minimum_years_experience >= 0),
    required_customer_experience text[] NOT NULL DEFAULT '{}',
    work_location               text NOT NULL DEFAULT '',
    remote_allowed              boolean NOT NULL DEFAULT false,
    travel_percent              integer NOT NULL DEFAULT 0 CHECK (travel_percent BETWEEN 0 AND 100),
    availability_date           date,
    employment_type             text NOT NULL DEFAULT '',
    pay_band_min                numeric(12,2),
    pay_band_max                numeric(12,2),
    bill_rate_min               numeric(12,2),
    bill_rate_max               numeric(12,2),
    derivation_method           text NOT NULL DEFAULT '',
    derivation_confidence       real NOT NULL DEFAULT 0,
    review_status               text NOT NULL DEFAULT 'draft'
                                  CHECK (review_status IN ('draft','in_review','approved','rejected')),
    reviewed_by_user_id         uuid,
    reviewed_at                 timestamptz,
    approved_requirement_id     uuid,
    created_at                  timestamptz NOT NULL DEFAULT now(),
    updated_at                  timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    CONSTRAINT uq_req_drafts_org_id UNIQUE (organization_id, id),
    -- The one way latch: approved means promoted, and only approved may be promoted.
    CONSTRAINT ck_drafts_approved_has_requirement CHECK (
        (review_status = 'approved' AND approved_requirement_id IS NOT NULL)
        OR (review_status <> 'approved' AND approved_requirement_id IS NULL)
    ),
    -- A decision names its decider. No automated path can satisfy this.
    CONSTRAINT ck_drafts_decided_has_reviewer CHECK (
        review_status NOT IN ('approved','rejected')
        OR (reviewed_by_user_id IS NOT NULL AND reviewed_at IS NOT NULL)
    ),
    -- A mandatory flag per required skill, or none at all. A mismatched pair would silently mark the wrong
    -- skill mandatory, which changes a band cap in the frozen engine after promotion.
    CONSTRAINT ck_drafts_skill_flags_align CHECK (
        cardinality(mandatory_skill_flags) = 0
        OR cardinality(mandatory_skill_flags) = cardinality(required_skills)
    ),
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES tracked_opportunities (organization_id, id),
    FOREIGN KEY (organization_id, source_scope_item_id) REFERENCES opportunity_scope_items (organization_id, id)
        ON DELETE SET NULL (source_scope_item_id),
    FOREIGN KEY (organization_id, approved_requirement_id) REFERENCES requirements (organization_id, id)
        ON DELETE SET NULL (approved_requirement_id),
    FOREIGN KEY (organization_id, reviewed_by_user_id) REFERENCES users (organization_id, id)
        ON DELETE SET NULL (reviewed_by_user_id)
);

-- The build requirements screen reads one opportunity's drafts by status.
CREATE INDEX idx_req_drafts_org_opp ON requirement_drafts (organization_id, opportunity_id, review_status);
-- One scope item promotes to one live requirement. The partial unique index is the second half of the
-- racing approval guarantee: even if two transactions somehow passed the FOR UPDATE, only one approved row
-- per source scope item can exist.
CREATE UNIQUE INDEX uq_req_drafts_one_approval_per_scope ON requirement_drafts (organization_id, source_scope_item_id)
    WHERE review_status = 'approved' AND source_scope_item_id IS NOT NULL;

ALTER TABLE requirement_drafts ENABLE ROW LEVEL SECURITY;
ALTER TABLE requirement_drafts FORCE  ROW LEVEL SECURITY;
CREATE POLICY req_drafts_isolation ON requirement_drafts
    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);

-- Drafts are created by derivation, edited by a reviewer, and decided by an approver. A rejected draft stays
-- as the record of what was refused, so there is no DELETE grant.
GRANT SELECT, INSERT, UPDATE ON requirement_drafts TO missionmatch_app;

-- +goose Down
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
  SELECT count(*) INTO n FROM requirement_drafts;
  IF n > 0 THEN
    RAISE EXCEPTION 'refusing to drop requirement_drafts: % draft rows present, including approval decisions', n;
  END IF;
END $$;
-- +goose StatementEnd
DROP TABLE IF EXISTS requirement_drafts;