Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0103_capture_gate_decisions.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0103_capture_gate_decisions.sql |
| Original SHA-256 | f33fd6e0cff012c69731ea12e893a272596cf9e25c1f14ea66694c488ab78df3 |
| Git blob SHA | 72f2e3c15ca138a0901f4817a5dfbb257417e366 |
| Reading-copy SHA-256 | f33fd6e0cff012c69731ea12e893a272596cf9e25c1f14ea66694c488ab78df3 |
Recovered source transcript
Download complete source text ↓106 lines · 6.4 KB
-- +goose Up
-- THE CAPTURE GATE, WHICH TURNS A BID OR NO BID FROM A STATUS INTO A DECISION.
--
-- tracked_opportunities carries a status, and 'no_bid' is one of its values. That records the OUTCOME and
-- nothing else. It cannot answer the three questions anybody actually asks about a walked away pursuit six
-- months later: who decided, when, and against what.
--
-- THAT IS NOT A REPORTING GAP, IT IS A CAPTURE PROCESS GAP. A gate review is a structured decision point
-- producing a go or a no go with named accountability, and a firm that cannot reconstruct one has no way to
-- learn from the pursuits it declined. The pursuits a firm walks away from are the cheapest lessons it will
-- ever have, and this product was throwing every one of them away the moment somebody changed a dropdown.
--
-- THE ADVISORY NUMBER IS SNAPSHOTTED AT DECISION TIME, and that is the load bearing column here. The capture
-- readiness composition and the win probability model both move: coefficients get retrained, the market base
-- rate shifts, the bench changes underneath a pursuit. Recomputing the number in June and presenting it
-- beside a decision made in March is a rewrite of history dressed as a report. This is the same rule the
-- AEDT work follows for candidate evaluations, applied to the capture side: the number you decided ON is
-- kept, not the number the model would give you today.
--
-- NOTHING IS INFERRED FROM THE EXISTING STATUS. Pursuits already sitting in 'no_bid' get no backfilled
-- decision row, because inventing a decider and a date for a decision nobody recorded is exactly the kind of
-- fabricated provenance this system refuses everywhere else. Those pursuits report "no recorded gate
-- decision", which is the truth.
CREATE TABLE capture_gate_decisions (
organization_id uuid NOT NULL REFERENCES organizations(id),
id uuid NOT NULL DEFAULT gen_random_uuid(),
-- The pursuit this decision is about. tracked_opportunities is the TENANT capture layer, which is the
-- one a capture team works; the reference opportunities table is the shared corpus and is not what a
-- firm makes a bid decision on. Getting this wrong is a real trap in this schema and it has already
-- produced one defect in this build, so the composite foreign key names the table explicitly.
opportunity_id uuid NOT NULL,
-- WHICH GATE. Capture is not one decision, it is a series of them, and conflating them loses the shape
-- of the pipeline: qualifying out early is healthy, walking away after writing a proposal is expensive.
gate text NOT NULL
CHECK (gate IN ('qualify', 'pursue', 'bid')),
-- The decision itself. Two values on purpose. A gate review that can end in "maybe" is a meeting, not a
-- gate, and the pursuits that quietly sit at maybe are the ones that consume a capture team.
decision text NOT NULL
CHECK (decision IN ('go', 'no_go')),
-- WHO AND WHEN, the part the status could never carry. decided_by is nullable only so a decision
-- survives the deletion of a user account; it is always written by the service.
decided_by uuid,
decided_at timestamptz NOT NULL DEFAULT now(),
-- AGAINST WHAT. Free text, and required to be non empty, because a go or no go with no stated reason
-- teaches nobody anything and is the state this table exists to prevent.
rationale text NOT NULL
CHECK (length(btrim(rationale)) >= 1 AND length(btrim(rationale)) <= 4000),
-- The advisory numbers AS THEY STOOD when the decision was made. Both nullable: a firm may decide before
-- any model has anything to say, and a NULL here means "no advisory was available", which is different
-- from and must never be rendered as a zero.
readiness_score numeric(6,4),
readiness_band text
CHECK (readiness_band IS NULL OR readiness_band IN ('GO', 'REVIEW', 'NO_GO')),
win_probability numeric(6,4)
CHECK (win_probability IS NULL OR (win_probability >= 0 AND win_probability <= 1)),
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (organization_id, id),
FOREIGN KEY (organization_id, opportunity_id)
REFERENCES tracked_opportunities (organization_id, id) ON DELETE CASCADE
);
-- The review board reads the LATEST decision per pursuit and per gate, so the index leads with the tenant
-- and orders by recency inside the pursuit.
CREATE INDEX IF NOT EXISTS idx_capture_gate_decisions_pursuit
ON capture_gate_decisions (organization_id, opportunity_id, gate, decided_at DESC);
ALTER TABLE capture_gate_decisions ENABLE ROW LEVEL SECURITY;
ALTER TABLE capture_gate_decisions FORCE ROW LEVEL SECURITY;
CREATE POLICY capture_gate_decisions_tenant ON capture_gate_decisions
USING (organization_id = current_setting('app.current_org_id', true)::uuid)
WITH CHECK (organization_id = current_setting('app.current_org_id', true)::uuid);
-- THE APPLICATION ROLE NEEDS AN EXPLICIT GRANT, and this migration was written without one.
--
-- Creating a table grants nothing. The migrator owns it and the NOBYPASSRLS application role, which is what
-- actually serves every request, cannot read a row of it. The first exercise of this table under the app
-- role returned "permission denied for table capture_gate_decisions", which is the correct behaviour of a
-- database doing exactly what it was told.
--
-- This is the SAME defect 0102 shipped, one role over. That one forgot the staging mirror below and surfaced
-- to a user as a server error; this one forgot production and would have done the same. Two migrations in a
-- row, the same omission, which is the signal that it belongs in a checklist rather than in somebody's
-- memory: a migration that creates a table has TWO obligations after the CREATE, and neither is optional.
GRANT SELECT, INSERT, UPDATE, DELETE ON capture_gate_decisions TO missionmatch_app;
-- Every table creating migration since 0086 ends here. A new table is invisible to the staging application
-- role until the mirror runs, and 0102 forgot this and surfaced to a user as a server error rather than as a
-- permission problem, which is the least diagnosable shape that failure can take.
SELECT mm_mirror_staging_role_privileges();
-- +goose Down
DROP POLICY IF EXISTS capture_gate_decisions_tenant ON capture_gate_decisions;
DROP INDEX IF EXISTS idx_capture_gate_decisions_pursuit;
DROP TABLE IF EXISTS capture_gate_decisions;