Janady13/missionmatch / db/migrations/0086_proposal_operations.sql

1-- +goose Up 2-- PHASE 3, PROPOSAL OPERATIONS. Directive Section 3.7, plus the recorded scope amendment of 30 July 2026 that 3-- adds pricing and recorded wage floors. 4-- 5-- TENANCY IS IN THE KEY, not merely in a column. Every table carries organization_id, every cross table 6-- reference is a COMPOSITE foreign key on (organization_id, target_id), and every index leads with 7-- organization_id. That is the Phase 2 pattern and it is what makes a mis scoped join impossible rather than 8-- merely unlikely: a row cannot reference a parent belonging to another tenant, because the key includes the 9-- tenant. 10-- 11-- POLICIES CARRY NO "TO" CLAUSE, DELIBERATELY. Of the 57 policies in this schema, 6 name a role and 51 do not, 12-- and the 6 are exactly the ones that broke Gate A tonight: a policy addressed only to missionmatch_app does 13-- not apply to missionmatch_staging_app, and with NO applicable policy RLS denies everything. An unaddressed 14-- policy applies to every role, which is both simpler and correct in every environment. Migration 0085 exists 15-- to repair that damage; these tables are written so it cannot happen to them. 16-- 17-- NO DELETE GRANTS ANYWHERE. Withdrawal, rejection, and supersession are STATUSES. A proposal that was 18-- withdrawn is a fact about the record, and a record that can be deleted cannot be audited. The one exception 19-- is proposal_team_members, where removing a person from a team is not a historical claim, and even there the 20-- grant is withheld and removal is a status, because the alternative is an audit trail with holes in it. 21 22-- ============================================================================ 23-- 1. proposals 24-- ============================================================================ 25CREATE TABLE proposals ( 26 organization_id uuid NOT NULL REFERENCES organizations(id), 27 id uuid NOT NULL DEFAULT gen_random_uuid(), 28 opportunity_id uuid NOT NULL, 29 title text NOT NULL DEFAULT '', 30 -- stage is where the proposal is in the capture lifecycle; status is whether it is live, so a proposal can 31 -- be at gold_team AND cancelled. Conflating them would lose the second fact. 32 stage text NOT NULL DEFAULT 'intake' 33 CHECK (stage IN ('intake','pink_team','red_team','gold_team','final_review', 34 'upload','submitted','awarded','lost','no_bid')), 35 status text NOT NULL DEFAULT 'active' 36 CHECK (status IN ('active','on_hold','cancelled','archived')), 37 proposal_owner_user_id uuid, 38 capture_owner_user_id uuid, 39 customer_deadline timestamptz, 40 internal_deadline timestamptz, 41 submitted_at timestamptz, 42 outcome_recorded_at timestamptz, 43 created_at timestamptz NOT NULL DEFAULT now(), 44 updated_at timestamptz NOT NULL DEFAULT now(), 45 PRIMARY KEY (id), 46 CONSTRAINT uq_proposals_org_id UNIQUE (organization_id, id), 47 FOREIGN KEY (organization_id, opportunity_id) REFERENCES tracked_opportunities (organization_id, id), 48 FOREIGN KEY (organization_id, proposal_owner_user_id) REFERENCES users (organization_id, id) 49 ON DELETE SET NULL (proposal_owner_user_id), 50 FOREIGN KEY (organization_id, capture_owner_user_id) REFERENCES users (organization_id, id) 51 ON DELETE SET NULL (capture_owner_user_id), 52 -- SUBMISSION IS A ONE WAY DOOR IN THE SCHEMA. submitted_at may only be set on a submitted or later stage, 53 -- so no code path can stamp a submission on a proposal still in review, whatever the API believes. 54 CONSTRAINT ck_proposals_submitted_stage CHECK ( 55 submitted_at IS NULL OR stage IN ('submitted','awarded','lost') 56 ), 57 -- An internal deadline after the customer deadline is a planning error worth refusing at the schema. 58 CONSTRAINT ck_proposals_deadline_order CHECK ( 59 internal_deadline IS NULL OR customer_deadline IS NULL OR internal_deadline <= customer_deadline 60 ) 61); 62CREATE INDEX idx_proposals_org_opp ON proposals (organization_id, opportunity_id, stage); 63CREATE INDEX idx_proposals_org_deadline ON proposals (organization_id, customer_deadline) 64 WHERE status = 'active'; 65ALTER TABLE proposals ENABLE ROW LEVEL SECURITY; 66ALTER TABLE proposals FORCE ROW LEVEL SECURITY; 67CREATE POLICY proposals_isolation ON proposals 68 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 69 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 70GRANT SELECT, INSERT, UPDATE ON proposals TO missionmatch_app; 71 72-- ============================================================================ 73-- 2 and 3. proposal_sections and proposal_section_versions 74-- ============================================================================ 75CREATE TABLE proposal_sections ( 76 organization_id uuid NOT NULL REFERENCES organizations(id), 77 id uuid NOT NULL DEFAULT gen_random_uuid(), 78 proposal_id uuid NOT NULL, 79 section_code text NOT NULL, 80 title text NOT NULL DEFAULT '', 81 owner_user_id uuid, 82 required boolean NOT NULL DEFAULT true, 83 page_or_word_limit text NOT NULL DEFAULT '', 84 due_at timestamptz, 85 draft_status text NOT NULL DEFAULT 'not_started' 86 CHECK (draft_status IN ('not_started','in_progress','drafted','final')), 87 review_status text NOT NULL DEFAULT 'not_reviewed' 88 CHECK (review_status IN ('not_reviewed','in_review','changes_requested','approved')), 89 approved_by_user_id uuid, 90 approved_at timestamptz, 91 created_at timestamptz NOT NULL DEFAULT now(), 92 updated_at timestamptz NOT NULL DEFAULT now(), 93 PRIMARY KEY (id), 94 CONSTRAINT uq_proposal_sections_org_id UNIQUE (organization_id, id), 95 CONSTRAINT uq_proposal_sections_code UNIQUE (organization_id, proposal_id, section_code), 96 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id), 97 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) 98 ON DELETE SET NULL (owner_user_id), 99 FOREIGN KEY (organization_id, approved_by_user_id) REFERENCES users (organization_id, id) 100 ON DELETE SET NULL (approved_by_user_id), 101 -- An approval must name who gave it. An approved section with no approver is the shape of an audit hole. 102 CONSTRAINT ck_proposal_sections_approval CHECK ( 103 review_status <> 'approved' OR (approved_by_user_id IS NOT NULL AND approved_at IS NOT NULL) 104 ) 105); 106CREATE INDEX idx_proposal_sections_org_prop ON proposal_sections (organization_id, proposal_id, draft_status); 107ALTER TABLE proposal_sections ENABLE ROW LEVEL SECURITY; 108ALTER TABLE proposal_sections FORCE ROW LEVEL SECURITY; 109CREATE POLICY proposal_sections_isolation ON proposal_sections 110 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 111 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 112GRANT SELECT, INSERT, UPDATE ON proposal_sections TO missionmatch_app; 113 114-- Versions are APPEND ONLY. There is no UPDATE grant, so a version cannot be edited after it exists: a new 115-- version is written instead. That is what makes "what did the red team actually review" answerable later. 116CREATE TABLE proposal_section_versions ( 117 organization_id uuid NOT NULL REFERENCES organizations(id), 118 id uuid NOT NULL DEFAULT gen_random_uuid(), 119 section_id uuid NOT NULL, 120 version_number integer NOT NULL, 121 content_ref text NOT NULL DEFAULT '', 122 content_sha256 text NOT NULL DEFAULT '', 123 authored_by_user_id uuid, 124 created_at timestamptz NOT NULL DEFAULT now(), 125 PRIMARY KEY (id), 126 CONSTRAINT uq_proposal_section_versions_org_id UNIQUE (organization_id, id), 127 CONSTRAINT uq_proposal_section_versions_num UNIQUE (organization_id, section_id, version_number), 128 CONSTRAINT ck_proposal_section_versions_num CHECK (version_number >= 1), 129 FOREIGN KEY (organization_id, section_id) REFERENCES proposal_sections (organization_id, id), 130 FOREIGN KEY (organization_id, authored_by_user_id) REFERENCES users (organization_id, id) 131 ON DELETE SET NULL (authored_by_user_id) 132); 133CREATE INDEX idx_proposal_section_versions_org_section 134 ON proposal_section_versions (organization_id, section_id, version_number DESC); 135ALTER TABLE proposal_section_versions ENABLE ROW LEVEL SECURITY; 136ALTER TABLE proposal_section_versions FORCE ROW LEVEL SECURITY; 137CREATE POLICY proposal_section_versions_isolation ON proposal_section_versions 138 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 139 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 140GRANT SELECT, INSERT ON proposal_section_versions TO missionmatch_app; 141 142-- ============================================================================ 143-- 4. proposal_compliance_items 144-- ============================================================================ 145CREATE TABLE proposal_compliance_items ( 146 organization_id uuid NOT NULL REFERENCES organizations(id), 147 id uuid NOT NULL DEFAULT gen_random_uuid(), 148 proposal_id uuid NOT NULL, 149 requirement_text text NOT NULL DEFAULT '', 150 source_document_id uuid, 151 source_page_number integer, 152 source_section_label text NOT NULL DEFAULT '', 153 response_location text NOT NULL DEFAULT '', 154 owner_user_id uuid, 155 due_at timestamptz, 156 required boolean NOT NULL DEFAULT true, 157 status text NOT NULL DEFAULT 'open' 158 CHECK (status IN ('open','in_progress','answered','verified','waived')), 159 evidence_ref text NOT NULL DEFAULT '', 160 reviewer_user_id uuid, 161 created_at timestamptz NOT NULL DEFAULT now(), 162 updated_at timestamptz NOT NULL DEFAULT now(), 163 PRIMARY KEY (id), 164 CONSTRAINT uq_proposal_compliance_org_id UNIQUE (organization_id, id), 165 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id), 166 FOREIGN KEY (organization_id, source_document_id) REFERENCES opportunity_documents (organization_id, id) 167 ON DELETE SET NULL (source_document_id), 168 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) 169 ON DELETE SET NULL (owner_user_id), 170 FOREIGN KEY (organization_id, reviewer_user_id) REFERENCES users (organization_id, id) 171 ON DELETE SET NULL (reviewer_user_id), 172 -- A WAIVER MUST BE ACCOUNTABLE. Waiving a required compliance item is the one status that removes a hard 173 -- blocker, so it may not be anonymous. 174 CONSTRAINT ck_proposal_compliance_waiver CHECK ( 175 status <> 'waived' OR reviewer_user_id IS NOT NULL 176 ) 177); 178CREATE INDEX idx_proposal_compliance_org_prop 179 ON proposal_compliance_items (organization_id, proposal_id, status, required); 180ALTER TABLE proposal_compliance_items ENABLE ROW LEVEL SECURITY; 181ALTER TABLE proposal_compliance_items FORCE ROW LEVEL SECURITY; 182CREATE POLICY proposal_compliance_isolation ON proposal_compliance_items 183 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 184 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 185GRANT SELECT, INSERT, UPDATE ON proposal_compliance_items TO missionmatch_app; 186 187-- ============================================================================ 188-- 5. proposal_milestones 189-- ============================================================================ 190CREATE TABLE proposal_milestones ( 191 organization_id uuid NOT NULL REFERENCES organizations(id), 192 id uuid NOT NULL DEFAULT gen_random_uuid(), 193 proposal_id uuid NOT NULL, 194 kind text NOT NULL 195 CHECK (kind IN ('question_deadline','pink_team','red_team','gold_team','final_review', 196 'upload','submission')), 197 due_at timestamptz, 198 completed_at timestamptz, 199 created_at timestamptz NOT NULL DEFAULT now(), 200 updated_at timestamptz NOT NULL DEFAULT now(), 201 PRIMARY KEY (id), 202 CONSTRAINT uq_proposal_milestones_org_id UNIQUE (organization_id, id), 203 CONSTRAINT uq_proposal_milestones_kind UNIQUE (organization_id, proposal_id, kind), 204 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id) 205); 206CREATE INDEX idx_proposal_milestones_org_prop ON proposal_milestones (organization_id, proposal_id, due_at); 207ALTER TABLE proposal_milestones ENABLE ROW LEVEL SECURITY; 208ALTER TABLE proposal_milestones FORCE ROW LEVEL SECURITY; 209CREATE POLICY proposal_milestones_isolation ON proposal_milestones 210 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 211 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 212GRANT SELECT, INSERT, UPDATE ON proposal_milestones TO missionmatch_app; 213 214-- ============================================================================ 215-- 6. proposal_risks 216-- ============================================================================ 217CREATE TABLE proposal_risks ( 218 organization_id uuid NOT NULL REFERENCES organizations(id), 219 id uuid NOT NULL DEFAULT gen_random_uuid(), 220 proposal_id uuid NOT NULL, 221 title text NOT NULL DEFAULT '', 222 likelihood text NOT NULL DEFAULT 'medium' CHECK (likelihood IN ('low','medium','high')), 223 impact text NOT NULL DEFAULT 'medium' CHECK (impact IN ('low','medium','high')), 224 owner_user_id uuid, 225 mitigation text NOT NULL DEFAULT '', 226 trigger_condition text NOT NULL DEFAULT '', 227 status text NOT NULL DEFAULT 'open' 228 CHECK (status IN ('open','mitigating','accepted','closed')), 229 related_scope_item_id uuid, 230 created_at timestamptz NOT NULL DEFAULT now(), 231 updated_at timestamptz NOT NULL DEFAULT now(), 232 PRIMARY KEY (id), 233 CONSTRAINT uq_proposal_risks_org_id UNIQUE (organization_id, id), 234 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id), 235 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) 236 ON DELETE SET NULL (owner_user_id), 237 FOREIGN KEY (organization_id, related_scope_item_id) 238 REFERENCES opportunity_scope_items (organization_id, id) 239 ON DELETE SET NULL (related_scope_item_id) 240); 241CREATE INDEX idx_proposal_risks_org_prop ON proposal_risks (organization_id, proposal_id, status); 242ALTER TABLE proposal_risks ENABLE ROW LEVEL SECURITY; 243ALTER TABLE proposal_risks FORCE ROW LEVEL SECURITY; 244CREATE POLICY proposal_risks_isolation ON proposal_risks 245 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 246 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 247GRANT SELECT, INSERT, UPDATE ON proposal_risks TO missionmatch_app; 248 249-- ============================================================================ 250-- 7. proposal_team_members 251-- ============================================================================ 252CREATE TABLE proposal_team_members ( 253 organization_id uuid NOT NULL REFERENCES organizations(id), 254 id uuid NOT NULL DEFAULT gen_random_uuid(), 255 proposal_id uuid NOT NULL, 256 user_id uuid NOT NULL, 257 team_role text NOT NULL DEFAULT 'contributor' 258 CHECK (team_role IN ('capture_manager','proposal_manager','writer','reviewer', 259 'pricer','contributor')), 260 status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','removed')), 261 created_at timestamptz NOT NULL DEFAULT now(), 262 updated_at timestamptz NOT NULL DEFAULT now(), 263 PRIMARY KEY (id), 264 CONSTRAINT uq_proposal_team_org_id UNIQUE (organization_id, id), 265 CONSTRAINT uq_proposal_team_member UNIQUE (organization_id, proposal_id, user_id, team_role), 266 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id), 267 FOREIGN KEY (organization_id, user_id) REFERENCES users (organization_id, id) 268); 269CREATE INDEX idx_proposal_team_org_prop ON proposal_team_members (organization_id, proposal_id, status); 270ALTER TABLE proposal_team_members ENABLE ROW LEVEL SECURITY; 271ALTER TABLE proposal_team_members FORCE ROW LEVEL SECURITY; 272CREATE POLICY proposal_team_isolation ON proposal_team_members 273 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 274 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 275GRANT SELECT, INSERT, UPDATE ON proposal_team_members TO missionmatch_app; 276 277-- ============================================================================ 278-- 8. proposal_staffing_links, AND THE PRICING FROM THE 30 JULY AMENDMENT 279-- ============================================================================ 280-- This is the table the eight way race runs against. approved_count is a DENORMALISED COUNTER maintained by 281-- the approve and withdraw transactions, and it exists because the readiness engine needs coverage without 282-- counting slate members on every calculation. 283-- 284-- A DENORMALISED COUNTER IS A CORRECTNESS RISK AND IS TREATED AS ONE. It carries a CHECK that it can never go 285-- negative and never exceed required_quantity, so a lost update or a double decrement fails LOUDLY at the 286-- database rather than quietly producing a coverage number that flatters the proposal. Gate 3 requires the 287-- count to survive an eight way race; this constraint is what turns a race defect into an error instead of a 288-- wrong answer. 289-- 290-- PRICING, per the amendment. pay_rate and bill_rate are the ends; burden_pct is what makes the middle real. 291-- MARGIN IS NOT STORED. It is computed by a pure function, because a stored margin drifts from its inputs and 292-- the gate requires the same inputs to produce a bit identical result. burden_pct being NULL is the state that 293-- clause A of the gate is about: it means the line is UNBURDENED and no margin may be reported for it at all. 294CREATE TABLE proposal_staffing_links ( 295 organization_id uuid NOT NULL REFERENCES organizations(id), 296 id uuid NOT NULL DEFAULT gen_random_uuid(), 297 proposal_id uuid NOT NULL, 298 requirement_id uuid NOT NULL, 299 slate_id uuid, 300 required_quantity integer NOT NULL DEFAULT 1, 301 approved_count integer NOT NULL DEFAULT 0, 302 -- Pricing. numeric, never float, mirroring the engine's decimal discipline into the schema. 303 labor_category text NOT NULL DEFAULT '', 304 pay_rate numeric(12,4), 305 burden_pct numeric(9,6), 306 bill_rate numeric(12,4), 307 wage_floor_id uuid, 308 rate_source text NOT NULL DEFAULT '', 309 rate_version text NOT NULL DEFAULT '', 310 priced_by_user_id uuid, 311 priced_at timestamptz, 312 created_at timestamptz NOT NULL DEFAULT now(), 313 updated_at timestamptz NOT NULL DEFAULT now(), 314 PRIMARY KEY (id), 315 CONSTRAINT uq_proposal_staffing_org_id UNIQUE (organization_id, id), 316 CONSTRAINT uq_proposal_staffing_link UNIQUE (organization_id, proposal_id, requirement_id), 317 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id), 318 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id), 319 FOREIGN KEY (organization_id, priced_by_user_id) REFERENCES users (organization_id, id) 320 ON DELETE SET NULL (priced_by_user_id), 321 CONSTRAINT ck_staffing_required_positive CHECK (required_quantity >= 0), 322 -- THE COUNTER INVARIANT, and the whole reason the race is provable rather than arguable. 323 CONSTRAINT ck_staffing_approved_range CHECK (approved_count >= 0 AND approved_count <= required_quantity), 324 CONSTRAINT ck_staffing_rates_nonneg CHECK ( 325 (pay_rate IS NULL OR pay_rate >= 0) AND (bill_rate IS NULL OR bill_rate >= 0) 326 AND (burden_pct IS NULL OR burden_pct >= 0) 327 ), 328 -- A PRICED LINE NAMES WHO PRICED IT AND WHEN. A bill rate with no attribution cannot be defended to a 329 -- customer or an auditor. 330 CONSTRAINT ck_staffing_priced_attribution CHECK ( 331 bill_rate IS NULL OR (priced_by_user_id IS NOT NULL AND priced_at IS NOT NULL) 332 ) 333); 334CREATE INDEX idx_proposal_staffing_org_prop ON proposal_staffing_links (organization_id, proposal_id); 335CREATE INDEX idx_proposal_staffing_org_req ON proposal_staffing_links (organization_id, requirement_id); 336ALTER TABLE proposal_staffing_links ENABLE ROW LEVEL SECURITY; 337ALTER TABLE proposal_staffing_links FORCE ROW LEVEL SECURITY; 338CREATE POLICY proposal_staffing_isolation ON proposal_staffing_links 339 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 340 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 341GRANT SELECT, INSERT, UPDATE ON proposal_staffing_links TO missionmatch_app; 342 343-- ============================================================================ 344-- 9. proposal_attachments 345-- ============================================================================ 346CREATE TABLE proposal_attachments ( 347 organization_id uuid NOT NULL REFERENCES organizations(id), 348 id uuid NOT NULL DEFAULT gen_random_uuid(), 349 proposal_id uuid NOT NULL, 350 attachment_kind text NOT NULL DEFAULT 'other' 351 CHECK (attachment_kind IN ('past_performance','resume','letter_of_commitment', 352 'pricing_workbook','representations','technical_volume','other')), 353 required boolean NOT NULL DEFAULT false, 354 file_name text NOT NULL DEFAULT '', 355 object_key text NOT NULL DEFAULT '', 356 content_sha256 text NOT NULL DEFAULT '', 357 byte_size bigint NOT NULL DEFAULT 0, 358 status text NOT NULL DEFAULT 'missing' 359 CHECK (status IN ('missing','uploaded','approved','superseded')), 360 uploaded_by_user_id uuid, 361 created_at timestamptz NOT NULL DEFAULT now(), 362 updated_at timestamptz NOT NULL DEFAULT now(), 363 PRIMARY KEY (id), 364 CONSTRAINT uq_proposal_attachments_org_id UNIQUE (organization_id, id), 365 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id), 366 FOREIGN KEY (organization_id, uploaded_by_user_id) REFERENCES users (organization_id, id) 367 ON DELETE SET NULL (uploaded_by_user_id), 368 -- An uploaded attachment has bytes behind it. A status of uploaded with no object key is a lie the 369 -- readiness engine would believe. 370 CONSTRAINT ck_proposal_attachments_bytes CHECK ( 371 status IN ('missing') OR (object_key <> '' AND content_sha256 <> '') 372 ) 373); 374CREATE INDEX idx_proposal_attachments_org_prop 375 ON proposal_attachments (organization_id, proposal_id, status, required); 376ALTER TABLE proposal_attachments ENABLE ROW LEVEL SECURITY; 377ALTER TABLE proposal_attachments FORCE ROW LEVEL SECURITY; 378CREATE POLICY proposal_attachments_isolation ON proposal_attachments 379 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 380 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 381GRANT SELECT, INSERT, UPDATE ON proposal_attachments TO missionmatch_app; 382 383-- ============================================================================ 384-- 10. proposal_readiness_snapshots. APPEND ONLY, no UPDATE grant. 385-- ============================================================================ 386-- One decimal column per component, per Section 7, rather than a jsonb blob, because a component score is a 387-- number the product reasons about and a blob is a number nobody can index or constrain. Weights live in the 388-- engine, not here: a snapshot records what was computed, never how. 389CREATE TABLE proposal_readiness_snapshots ( 390 organization_id uuid NOT NULL REFERENCES organizations(id), 391 id uuid NOT NULL DEFAULT gen_random_uuid(), 392 proposal_id uuid NOT NULL, 393 overall numeric(6,4) NOT NULL, 394 staffing_coverage numeric(6,4) NOT NULL, 395 compliance_matrix numeric(6,4) NOT NULL, 396 required_sections numeric(6,4) NOT NULL, 397 review_and_approval numeric(6,4) NOT NULL, 398 schedule_health numeric(6,4) NOT NULL, 399 risk_posture numeric(6,4) NOT NULL, 400 blockers jsonb NOT NULL DEFAULT '[]'::jsonb, 401 drivers jsonb NOT NULL DEFAULT '[]'::jsonb, 402 algorithm_version text NOT NULL, 403 stale boolean NOT NULL DEFAULT false, 404 calculated_at timestamptz NOT NULL DEFAULT now(), 405 PRIMARY KEY (id), 406 CONSTRAINT uq_proposal_readiness_org_id UNIQUE (organization_id, id), 407 FOREIGN KEY (organization_id, proposal_id) REFERENCES proposals (organization_id, id), 408 CONSTRAINT ck_readiness_bounds CHECK ( 409 overall BETWEEN 0 AND 1 AND staffing_coverage BETWEEN 0 AND 1 410 AND compliance_matrix BETWEEN 0 AND 1 AND required_sections BETWEEN 0 AND 1 411 AND review_and_approval BETWEEN 0 AND 1 AND schedule_health BETWEEN 0 AND 1 412 AND risk_posture BETWEEN 0 AND 1 413 ), 414 CONSTRAINT ck_readiness_algo_named CHECK (algorithm_version <> '') 415); 416CREATE INDEX idx_proposal_readiness_org_prop 417 ON proposal_readiness_snapshots (organization_id, proposal_id, calculated_at DESC); 418ALTER TABLE proposal_readiness_snapshots ENABLE ROW LEVEL SECURITY; 419ALTER TABLE proposal_readiness_snapshots FORCE ROW LEVEL SECURITY; 420CREATE POLICY proposal_readiness_isolation ON proposal_readiness_snapshots 421 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 422 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 423-- UPDATE is granted for exactly one column's worth of behaviour, the staleness flag a withdrawal sets. A 424-- snapshot's NUMBERS are never rewritten; only the flag saying they no longer describe the present. 425GRANT SELECT, INSERT ON proposal_readiness_snapshots TO missionmatch_app; 426GRANT UPDATE (stale) ON proposal_readiness_snapshots TO missionmatch_app; 427 428-- ============================================================================ 429-- 11. wage_determination_floors. THE RECORDED FLOOR, from the 30 July amendment. 430-- ============================================================================ 431-- ENTERED BY A HUMAN, NOT EXTRACTED. Finding one of the four that held Phase 2 open is that the SCA wage 432-- determination's text layer carries no usable character map, so its rate grid cannot be read from the 433-- document and OCR is deferred as candidate P2. A floor a named person recorded, with the determination number 434-- and revision beside it, is defensible. A floor guessed from an unreadable PDF would not be, and it would be 435-- worse than no floor at all because the submission blocker would be enforcing a fiction. 436CREATE TABLE wage_determination_floors ( 437 organization_id uuid NOT NULL REFERENCES organizations(id), 438 id uuid NOT NULL DEFAULT gen_random_uuid(), 439 wd_number text NOT NULL, 440 wd_revision text NOT NULL DEFAULT '', 441 labor_category text NOT NULL, 442 locality text NOT NULL DEFAULT '', 443 hourly_floor numeric(12,4) NOT NULL, 444 fringe_hourly numeric(12,4) NOT NULL DEFAULT 0, 445 effective_from date NOT NULL, 446 source_note text NOT NULL DEFAULT '', 447 recorded_by_user_id uuid NOT NULL, 448 created_at timestamptz NOT NULL DEFAULT now(), 449 PRIMARY KEY (id), 450 CONSTRAINT uq_wage_floors_org_id UNIQUE (organization_id, id), 451 CONSTRAINT uq_wage_floors_key UNIQUE (organization_id, wd_number, wd_revision, labor_category, locality, effective_from), 452 FOREIGN KEY (organization_id, recorded_by_user_id) REFERENCES users (organization_id, id), 453 CONSTRAINT ck_wage_floors_positive CHECK (hourly_floor > 0 AND fringe_hourly >= 0), 454 CONSTRAINT ck_wage_floors_named CHECK (wd_number <> '' AND labor_category <> '') 455); 456CREATE INDEX idx_wage_floors_org_lcat 457 ON wage_determination_floors (organization_id, labor_category, effective_from DESC); 458ALTER TABLE wage_determination_floors ENABLE ROW LEVEL SECURITY; 459ALTER TABLE wage_determination_floors FORCE ROW LEVEL SECURITY; 460CREATE POLICY wage_floors_isolation ON wage_determination_floors 461 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 462 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 463-- APPEND ONLY. A floor is a historical fact tied to a determination revision. Correcting one means recording 464-- the next revision, not editing the last, because a bill rate approved against an old floor was approved 465-- against that floor and the audit has to still show it. 466GRANT SELECT, INSERT ON wage_determination_floors TO missionmatch_app; 467 468ALTER TABLE proposal_staffing_links 469 ADD CONSTRAINT fk_staffing_wage_floor 470 FOREIGN KEY (organization_id, wage_floor_id) REFERENCES wage_determination_floors (organization_id, id) 471 ON DELETE SET NULL (wage_floor_id); 472 473-- ============================================================================ 474-- 12. THE SLATE APPROVAL LIFECYCLE, amendment 2.3, Phase 3 item 7. 475-- ============================================================================ 476-- slate_members already carries decision, decided_by, decided_at and decision_note, which is the RECRUITER's 477-- call on a candidate. The lifecycle below is a DIFFERENT fact: whether that member is approved onto a 478-- proposal. Conflating the two would lose the distinction between "we think this person fits" and "we have 479-- committed this person to a bid", and only the second one moves approved_count. 480ALTER TABLE slate_members 481 ADD COLUMN status text NOT NULL DEFAULT 'proposed' 482 CHECK (status IN ('proposed','approved','withdrawn')), 483 ADD COLUMN approved_by_user_id uuid, 484 ADD COLUMN approved_at timestamptz, 485 ADD COLUMN withdrawn_by_user_id uuid, 486 ADD COLUMN withdrawn_at timestamptz, 487 ADD COLUMN withdrawal_reason text NOT NULL DEFAULT ''; 488 489-- THE REASON IS REQUIRED ON EVERY WITHDRAWAL AND IT IS ENFORCED HERE, not only at the API. Gate 3 says a 490-- withdrawal without a reason is rejected by the API rather than just the form; this constraint means it is 491-- also rejected when the API is wrong. 492ALTER TABLE slate_members 493 ADD CONSTRAINT ck_slate_members_withdrawal CHECK ( 494 status <> 'withdrawn' 495 OR (withdrawal_reason <> '' AND withdrawn_by_user_id IS NOT NULL AND withdrawn_at IS NOT NULL) 496 ), 497 ADD CONSTRAINT ck_slate_members_approval CHECK ( 498 status <> 'approved' OR (approved_by_user_id IS NOT NULL AND approved_at IS NOT NULL) 499 ); 500 501CREATE INDEX idx_slate_members_org_status ON slate_members (organization_id, slate_id, status); 502GRANT UPDATE (status, approved_by_user_id, approved_at, withdrawn_by_user_id, withdrawn_at, withdrawal_reason) 503 ON slate_members TO missionmatch_app; 504 505-- ============================================================================ 506-- 13. STAGING ROLE PARITY, made reusable so tonight's Gate A failure cannot recur. 507-- ============================================================================ 508-- Migration 0085 repaired the staging app role by hand after Gate A found it missing seven column grants and 509-- absent from six policies. THE UNDERLYING CAUSE WAS NEVER FIXED: 51 migrations grant to missionmatch_app 510-- literally and none mentions the staging role, so every NEW grant repeats the gap. This migration adds 511-- grants, so without this it would repeat it right now. 512-- 513-- The function is created here and called at the end. Every future migration that adds a grant should call it 514-- as its last statement, which is one line and is cheaper than another Gate A failure. 515-- +goose StatementBegin 516CREATE OR REPLACE FUNCTION mm_mirror_staging_role_privileges() RETURNS void AS $$ 517DECLARE 518 staging_app CONSTANT text := 'missionmatch_staging_app'; 519 prod_app CONSTANT text := 'missionmatch_app'; 520 g record; 521BEGIN 522 -- Roles are CLUSTER wide while privileges are per database, so without this guard the block would grant the 523 -- staging role privileges on PRODUCTION tables. Inert today because the staging roles cannot connect to the 524 -- production database, and defence in depth is worth more than relying on that. 525 IF current_database() <> 'missionmatch_staging' THEN RETURN; END IF; 526 IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = staging_app) THEN RETURN; END IF; 527 528 FOR g IN 529 SELECT a.table_name, a.privilege_type FROM information_schema.role_table_grants a 530 WHERE a.grantee = prod_app AND a.table_schema = 'public' 531 AND NOT EXISTS (SELECT 1 FROM information_schema.role_table_grants b 532 WHERE b.grantee = staging_app AND b.table_schema='public' 533 AND b.table_name = a.table_name AND b.privilege_type = a.privilege_type) 534 LOOP 535 EXECUTE format('GRANT %s ON public.%I TO %I', g.privilege_type, g.table_name, staging_app); 536 END LOOP; 537 538 FOR g IN 539 SELECT a.table_name, a.column_name, a.privilege_type FROM information_schema.column_privileges a 540 WHERE a.grantee = prod_app AND a.table_schema = 'public' 541 AND NOT EXISTS (SELECT 1 FROM information_schema.column_privileges b 542 WHERE b.grantee = staging_app AND b.table_schema='public' 543 AND b.table_name = a.table_name AND b.column_name = a.column_name 544 AND b.privilege_type = a.privilege_type) 545 LOOP 546 EXECUTE format('GRANT %s (%I) ON public.%I TO %I', 547 g.privilege_type, g.column_name, g.table_name, staging_app); 548 END LOOP; 549 550 -- CONTAINMENT RE ASSERTED rather than trusted. Nothing above touches a database privilege; this proves it. 551 IF has_database_privilege(staging_app, 'missionmatch', 'CONNECT') THEN 552 RAISE EXCEPTION 'staging parity: % gained CONNECT on the production database', staging_app; 553 END IF; 554END $$ LANGUAGE plpgsql; 555-- +goose StatementEnd 556 557SELECT mm_mirror_staging_role_privileges(); 558 559-- +goose Down 560DROP TABLE IF EXISTS proposal_readiness_snapshots; 561DROP TABLE IF EXISTS proposal_attachments; 562ALTER TABLE proposal_staffing_links DROP CONSTRAINT IF EXISTS fk_staffing_wage_floor; 563DROP TABLE IF EXISTS wage_determination_floors; 564DROP TABLE IF EXISTS proposal_staffing_links; 565DROP TABLE IF EXISTS proposal_team_members; 566DROP TABLE IF EXISTS proposal_risks; 567DROP TABLE IF EXISTS proposal_milestones; 568DROP TABLE IF EXISTS proposal_compliance_items; 569DROP TABLE IF EXISTS proposal_section_versions; 570DROP TABLE IF EXISTS proposal_sections; 571DROP TABLE IF EXISTS proposals; 572ALTER TABLE slate_members 573 DROP CONSTRAINT IF EXISTS ck_slate_members_withdrawal, 574 DROP CONSTRAINT IF EXISTS ck_slate_members_approval; 575DROP INDEX IF EXISTS idx_slate_members_org_status; 576ALTER TABLE slate_members 577 DROP COLUMN IF EXISTS status, 578 DROP COLUMN IF EXISTS approved_by_user_id, 579 DROP COLUMN IF EXISTS approved_at, 580 DROP COLUMN IF EXISTS withdrawn_by_user_id, 581 DROP COLUMN IF EXISTS withdrawn_at, 582 DROP COLUMN IF EXISTS withdrawal_reason; 583DROP FUNCTION IF EXISTS mm_mirror_staging_role_privileges(); 584