Janady13/missionmatch / db/migrations/0055_placement_integrity.sql

Source identity

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

Original source ↗

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/0055_placement_integrity.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0055_placement_integrity.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓176 lines · 9.1 KB

-- +goose Up
-- Ties offers and placements to the pipeline so revenue records and stage records cannot diverge, and
-- closes four integrity gaps in 0048. Every one was reproduced against a live database first.
--
-- A. Two ACCEPTED offers could exist on one application. uq_offers_one_live caps only the 'extended'
--    status, so accepting twice was permitted, leaving two contradictory sets of agreed rates with
--    nothing to say which one is the revenue.
--
-- B. A placement could carry an offer belonging to a DIFFERENT application. Reproduced: a placement on
--    application b1 referencing an offer extended to b2, so the rates, the candidate and the money came
--    from a different person. Same defect shape as the cross client hiring manager fixed in 0052.
--
-- C. A placement could exist while its application sat at 'sourced'. The pipeline and the placement
--    record described different realities, and nothing reconciled them.
--
-- D. A requisition with one opening could carry two live placements, because the seat cap added in 0054
--    counts applications at stage 'placed' and nothing required a placement to move the stage.
--
-- C and D are the same defect seen from two sides, and both are fixed by one rule: a live placement
-- exists if and only if its application is at stage 'placed'. With that biconditional held by the
-- database, the seat cap already enforced on stage in 0054 covers placements transitively, and there is
-- exactly one answer to "is this person placed" rather than two that can disagree.

-- ---------------------------------------------------------------------------
-- A. At most one accepted offer per application.
--
-- Separate partial index rather than widening uq_offers_one_live, because an application legitimately
-- holds one live offer AND, after a rescind and re extend, several historical ones. Only 'accepted' is
-- capped here; declined, rescinded and expired may repeat freely.
CREATE UNIQUE INDEX uq_offers_one_accepted
    ON offers (organization_id, application_id) WHERE status = 'accepted';

-- An extended offer must carry terms. An offer with no numbers is a draft, and letting one be recorded
-- as extended means a margin calculation later reads NULL as zero and reports a placement that earns
-- nothing. Mirrors placements_pricing_chk so an offer and the placement made from it agree in shape.
ALTER TABLE offers ADD CONSTRAINT offers_pricing_chk CHECK (
    status <> 'extended'
    OR (employment_type = 'direct_hire' AND (salary IS NOT NULL OR fee_pct IS NOT NULL))
    OR (employment_type <> 'direct_hire' AND bill_rate IS NOT NULL AND pay_rate IS NOT NULL));

-- Selling below cost is a data entry error on an offer for the same reason it is on a placement.
ALTER TABLE offers ADD CONSTRAINT offers_margin_chk CHECK (
    bill_rate IS NULL OR pay_rate IS NULL OR bill_rate >= pay_rate);

-- ---------------------------------------------------------------------------
-- B. A placement's offer must belong to the same application.
--
-- Needs a unique key on the referenced side carrying application_id for the composite key to point at.
ALTER TABLE offers ADD CONSTRAINT uq_offers_application_id UNIQUE (organization_id, application_id, id);

ALTER TABLE placements DROP CONSTRAINT placements_organization_id_offer_id_fkey;

-- The column list on SET NULL is required, not decoration. A bare SET NULL on a composite key nulls
-- every referencing column including organization_id, which is NOT NULL, so deleting an offer would
-- abort and the offer would become undeletable. Migration 0053 repaired twenty three constraints that
-- made exactly that mistake, including this one; it is written correctly here.
ALTER TABLE placements ADD CONSTRAINT placements_offer_fkey
    FOREIGN KEY (organization_id, application_id, offer_id)
    REFERENCES offers (organization_id, application_id, id)
    ON DELETE SET NULL (offer_id);

-- ---------------------------------------------------------------------------
-- C and D. A live placement exists if and only if the application is at stage 'placed'.
--
-- Enforced by trigger rather than by a constraint because the rule spans two tables, and in both
-- directions: creating or reviving a placement requires the stage, and leaving the stage requires no
-- live placement. A CHECK cannot see another row, and a foreign key cannot express a conditional.
--
-- The direction that refuses leaving 'placed' is deliberate. The alternative, silently ending the
-- placement, would invent an end date nobody chose and destroy the distinction between an assignment
-- that completed and one that fell off inside the guarantee, which is the difference between revenue
-- earned and revenue clawed back.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_placement_requires_placed_stage() RETURNS trigger AS $$
DECLARE
    cur_stage text;
BEGIN
    IF NEW.status NOT IN ('pending_start','active') THEN
        RETURN NEW; -- an ended placement carries no claim about the current stage
    END IF;
    SELECT stage INTO cur_stage FROM applications
     WHERE organization_id = NEW.organization_id AND id = NEW.application_id
       FOR UPDATE;
    IF cur_stage IS DISTINCT FROM 'placed' THEN
        RAISE EXCEPTION
            'a live placement requires the application to be at stage placed, it is at %', cur_stage
            USING ERRCODE = '23514';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

CREATE TRIGGER placements_require_placed_stage
    BEFORE INSERT OR UPDATE ON placements
    FOR EACH ROW EXECUTE FUNCTION rm_placement_requires_placed_stage();

-- The other direction. Moving an application off 'placed' while a live placement stands would leave a
-- placement claiming a stage the application no longer holds.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_stage_respects_live_placement() RETURNS trigger AS $$
DECLARE
    live int;
BEGIN
    IF OLD.stage = 'placed' AND NEW.stage <> 'placed' THEN
        SELECT count(*) INTO live FROM placements
         WHERE organization_id = OLD.organization_id
           AND application_id  = OLD.id
           AND status IN ('pending_start','active');
        IF live > 0 THEN
            RAISE EXCEPTION
                'this candidate has a live placement: end it as completed, terminated or fell_off first'
                USING ERRCODE = '23514';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

-- Fires after the stage guard from 0054, which is alphabetically earlier, so an illegal direct write is
-- still refused first and this only ever sees a legitimate event driven change.
CREATE TRIGGER applications_stage_respects_placement
    BEFORE UPDATE ON applications
    FOR EACH ROW EXECUTE FUNCTION rm_stage_respects_live_placement();

-- ---------------------------------------------------------------------------
-- Offer lifecycle. 0048 constrains which statuses need a responded_at but not which may follow which,
-- so an accepted offer could be moved back to extended and a declined one accepted after the fact.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION rm_offer_transition() RETURNS trigger AS $$
BEGIN
    IF OLD.status = NEW.status THEN
        RETURN NEW;
    END IF;
    -- extended is the only starting point. accepted may still be rescinded, because a client pulling a
    -- role after acceptance is real and the record has to be able to say so. declined and expired are
    -- terminal: re offering is a NEW offer, so the history keeps both.
    IF NOT (
        (OLD.status = 'extended' AND NEW.status IN ('accepted','declined','rescinded','expired'))
        OR (OLD.status = 'accepted' AND NEW.status = 'rescinded')
    ) THEN
        RAISE EXCEPTION 'an offer cannot move from % to %', OLD.status, NEW.status
            USING ERRCODE = '23514';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

CREATE TRIGGER offers_transition_guard
    BEFORE UPDATE ON offers
    FOR EACH ROW EXECUTE FUNCTION rm_offer_transition();

-- Reads the placement and offer screens make: a requisition's live placements, and assignments ending
-- soon so a recruiter can redeploy before the bench goes idle.
CREATE INDEX idx_placements_ending ON placements (organization_id, end_date)
    WHERE status IN ('pending_start','active');

-- +goose Down
DROP INDEX IF EXISTS idx_placements_ending;
DROP TRIGGER IF EXISTS offers_transition_guard ON offers;
DROP FUNCTION IF EXISTS rm_offer_transition();
DROP TRIGGER IF EXISTS applications_stage_respects_placement ON applications;
DROP FUNCTION IF EXISTS rm_stage_respects_live_placement();
DROP TRIGGER IF EXISTS placements_require_placed_stage ON placements;
DROP FUNCTION IF EXISTS rm_placement_requires_placed_stage();
ALTER TABLE placements DROP CONSTRAINT IF EXISTS placements_offer_fkey;
ALTER TABLE placements ADD CONSTRAINT placements_organization_id_offer_id_fkey
    FOREIGN KEY (organization_id, offer_id) REFERENCES offers (organization_id, id)
    ON DELETE SET NULL (offer_id);
ALTER TABLE offers DROP CONSTRAINT IF EXISTS uq_offers_application_id;
ALTER TABLE offers DROP CONSTRAINT IF EXISTS offers_margin_chk;
ALTER TABLE offers DROP CONSTRAINT IF EXISTS offers_pricing_chk;
DROP INDEX IF EXISTS uq_offers_one_accepted;