Janady13/missionmatch / db/migrations/0102_teaming_and_vms_intake.sql

1-- +goose Up 2 3-- ============================================================================ 4-- 1. TEAMING PARTNERS, TRACKED FROM CAPTURE RATHER THAN FROM AWARD 5-- ============================================================================ 6-- 7-- THE GAP IS MARKET WIDE AND IT IS NAMED. Subcontractor tooling forces a choice between managing teaming 8-- arrangements DURING capture and tracking compliance AFTER award: the ERPs that GovCon firms already run, 9-- Unanet and Deltek Costpoint, handle post award subcontractor financials well and do not track teaming 10-- partners during capture at all. Partner data ends up scattered across a capture CRM, a proposal tool and a 11-- post award ERP, and the question "who agreed to team with us on this pursuit, and did they sign" has no 12-- single home. 13-- 14-- missionMATCH sits exactly on that seam. It already tracks an opportunity through capture 15-- (tracked_opportunities) and into a proposal (proposals), which is the span the partner relationship 16-- actually lives across, and it had nothing to record a partner in. proposal_team_members is INTERNAL staff: 17-- capture manager, writer, reviewer. It is not this, and using it for this would have conflated the people 18-- who write the bid with the companies bidding alongside you. 19-- 20-- IT ATTACHES TO THE CAPTURE RECORD, NOT THE PROPOSAL, and that is the whole point. Teaming is agreed while 21-- the pursuit is being shaped, months before a proposal row exists. Hanging it off the proposal would mean 22-- the partner could only be recorded after the moment the decision was made. 23CREATE TABLE teaming_partners ( 24 organization_id uuid NOT NULL REFERENCES organizations(id), 25 id uuid NOT NULL DEFAULT gen_random_uuid(), 26 opportunity_id uuid NOT NULL, 27 company_name text NOT NULL, 28 -- Which way the relationship runs on THIS pursuit. The same company can be a prime on one and a sub on 29 -- the next, which is why it is a property of the pairing rather than of the company. 30 partner_role text NOT NULL DEFAULT 'subcontractor' 31 CHECK (partner_role IN ('prime', 'subcontractor', 'joint_venture')), 32 -- The lifecycle a capture manager actually tracks. agreement_signed is the one that matters at a bid 33 -- review, because an unsigned partner is a plan rather than a commitment. 34 status text NOT NULL DEFAULT 'identified' 35 CHECK (status IN ('identified', 'in_discussion', 'agreement_signed', 'declined')), 36 -- The identifiers a federal teaming agreement is written against. Free text and optional: a partner is 37 -- often identified by name weeks before anybody looks up their UEI. 38 uei text NOT NULL DEFAULT '', 39 cage_code text NOT NULL DEFAULT '', 40 -- The set asides the partner carries, which is frequently the REASON for teaming: a small business 41 -- partner is how a large prime reaches a set aside vehicle. Free text array so a new category does not 42 -- need a migration. 43 set_asides text[] NOT NULL DEFAULT '{}', 44 scope_summary text NOT NULL DEFAULT '', 45 agreement_signed_on date, 46 created_by uuid, 47 created_at timestamptz NOT NULL DEFAULT now(), 48 updated_at timestamptz NOT NULL DEFAULT now(), 49 PRIMARY KEY (id), 50 CONSTRAINT uq_teaming_org_id UNIQUE (organization_id, id), 51 CONSTRAINT uq_teaming_partner_per_pursuit UNIQUE (organization_id, opportunity_id, company_name), 52 FOREIGN KEY (organization_id, opportunity_id) REFERENCES tracked_opportunities (organization_id, id) ON DELETE CASCADE, 53 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL (created_by), 54 CONSTRAINT ck_teaming_named CHECK (btrim(company_name) <> ''), 55 -- A SIGNED STATUS MUST CARRY THE DATE IT WAS SIGNED. Without this the status is an assertion nobody can 56 -- date, and at a bid review "signed" with no date is the same as unsigned. 57 CONSTRAINT ck_teaming_signed_dated CHECK ( 58 status <> 'agreement_signed' OR agreement_signed_on IS NOT NULL) 59); 60CREATE INDEX idx_teaming_org_opp ON teaming_partners (organization_id, opportunity_id, status); 61ALTER TABLE teaming_partners ENABLE ROW LEVEL SECURITY; 62ALTER TABLE teaming_partners FORCE ROW LEVEL SECURITY; 63CREATE POLICY teaming_partners_isolation ON teaming_partners 64 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 65 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 66GRANT SELECT, INSERT, UPDATE, DELETE ON teaming_partners TO missionmatch_app; 67 68-- ============================================================================ 69-- 2. KEY PERSONNEL COMMITMENTS 70-- ============================================================================ 71-- 72-- requirements.key_personnel has existed since the schema was written and nothing hangs off it. A key 73-- personnel position is one the solicitation names and evaluates: the program manager, the lead architect. 74-- The evaluation is of a PERSON, not a labour category, so the bid has to name somebody and be able to show 75-- they will actually turn up. 76-- 77-- THE RISK THIS MODELS IS THE ONE THAT LOSES BIDS. A named key person who walks between proposal submission 78-- and award takes their evaluation score with them, and the firm either substitutes and takes a hit or 79-- withdraws. What holds them is a letter of intent or a contingent offer, both of which EXPIRE, and neither 80-- of which this product could record. A capture manager reviewing a bid needed to know which named people 81-- were actually committed and which were assumed, and there was nowhere to look. 82-- 83-- A COMMITMENT CAN COME FROM A PARTNER. On a teamed bid the prime frequently proposes a person the partner 84-- employs, and that person has no candidate record here and should not: they are not on this firm's bench. 85-- So either candidate_id or teaming_partner_id carries the answer, and the CHECK below requires exactly one 86-- source of the person rather than allowing a row that names nobody. 87CREATE TABLE key_personnel_commitments ( 88 organization_id uuid NOT NULL REFERENCES organizations(id), 89 id uuid NOT NULL DEFAULT gen_random_uuid(), 90 requirement_id uuid NOT NULL, 91 -- One of these two identifies the person. Our own candidate, or a partner's employee named by the partner. 92 candidate_id uuid, 93 teaming_partner_id uuid, 94 -- Only used when the partner names somebody we hold no record for, which is the normal case. 95 partner_person_name text NOT NULL DEFAULT '', 96 role_title text NOT NULL DEFAULT '', 97 commitment_type text NOT NULL DEFAULT 'letter_of_intent' 98 CHECK (commitment_type IN ('letter_of_intent', 'contingent_offer', 'employee', 99 'partner_provided')), 100 status text NOT NULL DEFAULT 'proposed' 101 CHECK (status IN ('proposed', 'committed', 'withdrawn', 'superseded')), 102 signed_on date, 103 -- WHEN THE COMMITMENT LAPSES. A letter of intent signed for a bid that slips six months is not a 104 -- commitment any more, and the whole reason this column exists is so a review can say which ones have 105 -- gone stale rather than assuming they all still hold. 106 expires_on date, 107 notes text NOT NULL DEFAULT '', 108 created_by uuid, 109 created_at timestamptz NOT NULL DEFAULT now(), 110 updated_at timestamptz NOT NULL DEFAULT now(), 111 PRIMARY KEY (id), 112 CONSTRAINT uq_kpc_org_id UNIQUE (organization_id, id), 113 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE, 114 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE SET NULL (candidate_id), 115 FOREIGN KEY (organization_id, teaming_partner_id) REFERENCES teaming_partners (organization_id, id) ON DELETE SET NULL (teaming_partner_id), 116 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL (created_by), 117 -- EXACTLY ONE SOURCE OF THE PERSON. Our candidate, or a partner plus a name. A row naming neither is a 118 -- commitment to nobody, and a row naming both is two people in one commitment. 119 CONSTRAINT ck_kpc_names_somebody CHECK ( 120 (candidate_id IS NOT NULL AND teaming_partner_id IS NULL) 121 OR (candidate_id IS NULL AND teaming_partner_id IS NOT NULL AND btrim(partner_person_name) <> '') 122 ), 123 -- A committed status must carry the date it was signed, for the same reason a signed teaming agreement 124 -- must: an undated commitment cannot be aged and cannot be defended. 125 CONSTRAINT ck_kpc_committed_dated CHECK (status <> 'committed' OR signed_on IS NOT NULL), 126 CONSTRAINT ck_kpc_expiry_after_signing CHECK ( 127 expires_on IS NULL OR signed_on IS NULL OR expires_on >= signed_on) 128); 129CREATE INDEX idx_kpc_org_req ON key_personnel_commitments (organization_id, requirement_id, status); 130-- The index the staleness review needs: which commitments lapse soon. 131CREATE INDEX idx_kpc_expiring ON key_personnel_commitments (organization_id, expires_on) 132 WHERE expires_on IS NOT NULL AND status IN ('proposed', 'committed'); 133ALTER TABLE key_personnel_commitments ENABLE ROW LEVEL SECURITY; 134ALTER TABLE key_personnel_commitments FORCE ROW LEVEL SECURITY; 135CREATE POLICY kpc_isolation ON key_personnel_commitments 136 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 137 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 138GRANT SELECT, INSERT, UPDATE, DELETE ON key_personnel_commitments TO missionmatch_app; 139 140-- ============================================================================ 141-- 3. VMS AND MSP INTAKE 142-- ============================================================================ 143-- 144-- A LARGE SHARE OF GOVCON STAFFING WORK ARRIVES THROUGH A VENDOR MANAGEMENT SYSTEM and this product had no 145-- idea. Fieldglass, Beeline and Coupa distribute one requisition to a preferred vendor list, and most of 146-- those lists run five to ten agencies competing on the same role. A requisition that arrived that way is a 147-- fundamentally different piece of work from one a client phoned in: it has a hard submission deadline, a 148-- submission cap, and known competition, and a desk that treats the two the same loses the first kind. 149-- 150-- THIS IS THE INBOUND MODEL, NOT A CONNECTOR. A live Fieldglass or Beeline integration needs per tenant 151-- credentials this build does not have, and pretending otherwise would be the fake integration the directive 152-- forbids. What is here is the shape a connector would later populate, and it is useful on its own the day 153-- it ships: a recruiter typing a req in from a VMS portal can record where it came from and when it closes, 154-- and the deadline becomes visible instead of living in somebody's inbox. 155ALTER TABLE requisitions ADD COLUMN intake_source text NOT NULL DEFAULT 'direct' 156 CHECK (intake_source IN ('direct', 'vms', 'msp', 'referral', 'recompete')); 157-- The VMS this came through, and its own identifier for the req, so a submission can be reconciled against 158-- the portal without anybody searching by title. 159ALTER TABLE requisitions ADD COLUMN vms_name text NOT NULL DEFAULT ''; 160ALTER TABLE requisitions ADD COLUMN vms_requisition_ref text NOT NULL DEFAULT ''; 161-- THE DEADLINE, which is the single most operationally important field on a VMS req and the one most often 162-- missed. A timestamp rather than a date because these close at a stated time of day. 163ALTER TABLE requisitions ADD COLUMN submission_deadline timestamptz; 164-- How many agencies are competing, and how many candidates this firm may submit. Both are stated on the 165-- portal and both change how a desk prioritises: three submissions against nine competitors is a different 166-- calculation from unlimited submissions against one. 167ALTER TABLE requisitions ADD COLUMN competing_vendors int; 168ALTER TABLE requisitions ADD COLUMN max_submissions int; 169 170ALTER TABLE requisitions ADD CONSTRAINT ck_requisitions_vms_counts CHECK ( 171 (competing_vendors IS NULL OR competing_vendors >= 0) 172 AND (max_submissions IS NULL OR max_submissions >= 0) 173); 174-- A VMS sourced requisition must say WHICH vms. Without the name the source is a label that answers nothing, 175-- and reconciling a submission against the wrong portal is worse than not recording it. 176ALTER TABLE requisitions ADD CONSTRAINT ck_requisitions_vms_named CHECK ( 177 intake_source <> 'vms' OR btrim(vms_name) <> ''); 178 179-- The index the deadline surface needs: open requisitions closing soon, which is the queue a desk works from 180-- on a VMS heavy account. Partial on open, because a closed req's deadline is history. 181CREATE INDEX idx_requisitions_deadline ON requisitions (organization_id, submission_deadline) 182 WHERE submission_deadline IS NOT NULL AND deleted_at IS NULL AND status = 'open'; 183 184-- ============================================================================ 185-- 4. STAGING GRANT PARITY 186-- ============================================================================ 187-- 188-- EVERY MIGRATION THAT CREATES A TABLE MUST CALL THIS AND THIS ONE DID NOT. The GRANT statements above name 189-- missionmatch_app, which is the PRODUCTION application role. Staging's role is missionmatch_staging_app, so 190-- without the mirror the two new tables exist on staging and the application cannot touch them: every write 191-- answered "permission denied for table key_personnel_commitments", which the handler had no rule for and 192-- reported as a 500. 193-- 194-- The convention has been in place since 0086 and every table creating migration since has carried this line. 195-- It was found by walking the feature as a capture manager, which is the only thing that would have: the Go 196-- suite was green, the migration applied cleanly, and the schema was correct. Nothing but using it fails. 197SELECT mm_mirror_staging_role_privileges(); 198 199-- +goose Down 200DROP INDEX IF EXISTS idx_requisitions_deadline; 201ALTER TABLE requisitions DROP CONSTRAINT IF EXISTS ck_requisitions_vms_named; 202ALTER TABLE requisitions DROP CONSTRAINT IF EXISTS ck_requisitions_vms_counts; 203ALTER TABLE requisitions DROP COLUMN IF EXISTS max_submissions; 204ALTER TABLE requisitions DROP COLUMN IF EXISTS competing_vendors; 205ALTER TABLE requisitions DROP COLUMN IF EXISTS submission_deadline; 206ALTER TABLE requisitions DROP COLUMN IF EXISTS vms_requisition_ref; 207ALTER TABLE requisitions DROP COLUMN IF EXISTS vms_name; 208ALTER TABLE requisitions DROP COLUMN IF EXISTS intake_source; 209DROP TABLE IF EXISTS key_personnel_commitments; 210DROP TABLE IF EXISTS teaming_partners; 211