Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0033_bid_outcomes.sql |
| Original SHA-256 | d063a29717183c7ec9ef4c3b0ab65d46246d1d8153653c49d1eeee93b2d65507 |
| Git blob SHA | fba7abb98434fbcc96c90e2b8ba4b1345413ceb6 |
| Reading-copy SHA-256 | d063a29717183c7ec9ef4c3b0ab65d46246d1d8153653c49d1eeee93b2d65507 |
Every recorded occurrence
These 12 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/0033_bid_outcomes.sql | feature/opportunity-to-outcome | 720d47fae476b0d38477ae4401697d372b1ea2bc | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | collision-deconfliction | 4ee5a09972608ed89f8377c6665542d5f450f0a6 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | m5-price-to-win | 41000ad30fff4129fdaf5ec84e15ba14c27d4890 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | master | 1e9249840a4302ec82e123b9c9cb6f75badea3e4 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | mag-substrate | b82c7100a492dec67dfd1eda6b3f53def859be35 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | time-to-fill-survival | b66e1e505863d4f17741a4b9ede73509e45bd6c6 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | graphrag-foundation | b0ddeb9f633fd138d09309d1a17b7d13bc3a67f7 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | presentation-readiness | eb574acaa29d71ce4d0f852572e87c275c79fdb7 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | vendor-intelligence | fe9796a19c261951d3f2565206c0c1b5ac294465 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | phase2-connectors | e038d4c687aec16f8bd9bc4d907eea14d91f8704 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | feature/aioe-upgrade | 2178e7298d0e5d9af83afa12402fd587f0ba6ed8 | Open original |
| Janady13/missionmatch / db/migrations/0033_bid_outcomes.sql | capture-readiness | bae6af7cb2997e819174a9e9cfff5e16067a1975 | Open original |
Recovered source transcript
Download complete source text ↓59 lines · 4.3 KB
-- +goose Up
-- M7 bid outcomes ledger. One append friendly row per bid: what the bid was for (an opportunity, a
-- requirement, or a slate, each nullable because not every bid links all three), when the proposal was
-- submitted, the win or loss result once the award decision lands, who won on a loss, and the debrief
-- notes. It ships DORMANT: the recording path is flag gated off by default (MM_BID_OUTCOMES_ENABLED) so
-- the table accrues real win and loss labels the day it lands while no learned model consumes it yet. The
-- single advisory read over it (bid_win_rate_by_segment in the closed AI Registry) returns a market base
-- rate, never a candidate score; the frozen matching engine stays the only scorer. This is data plumbing,
-- so there is NO model_version bump and NO engine change.
--
-- Tenant isolation is the platform standard, defense in depth: RLS on organization_id (ENABLE plus FORCE,
-- USING plus WITH CHECK) PLUS composite foreign keys that carry organization_id, so a child row can never
-- reference a parent in another tenant (FK validation bypasses RLS, so WITH CHECK alone is necessary but
-- not sufficient). Every reference target here already carries a composite UNIQUE (organization_id, id):
-- opportunities (uq_opportunities_org_id), requirements (uq_requirements_org_id), and slates
-- (uq_slates_org_id), all in 0002_matching.sql, so all three links are composite FKs ON DELETE SET NULL.
-- The app role records and updates an outcome and never deletes one, so the grant is SELECT, INSERT,
-- UPDATE with no DELETE.
CREATE TABLE bid_outcomes (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL REFERENCES organizations(id),
opportunity_id uuid, -- the opportunity the bid was for; null when not linked
requirement_id uuid, -- the requirement the bid was for; null when not linked
slate_id uuid, -- the slate the bid was staffed from; null when not linked
submitted_at timestamptz, -- when the proposal was submitted; null until submitted
result text NOT NULL DEFAULT 'pending'
CHECK (result IN ('win', 'loss', 'no_bid', 'pending')),
award_date date, -- when the award decision landed; null until known
awardee_uei text NOT NULL DEFAULT '', -- who won, recorded on a loss
notes text NOT NULL DEFAULT '', -- debrief notes
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
-- tenant composite key so this row can be a composite FK target if a later child ever needs one
CONSTRAINT uq_bid_outcomes_org_id UNIQUE (organization_id, id),
-- the linked opportunity is in the same org; a hard delete of the opportunity nulls the link rather
-- than orphaning or blocking the outcome record, so the win or loss label survives the parent delete
FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL,
-- the linked requirement is in the same org; a hard delete nulls the link, keeping the label
FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE SET NULL,
-- the linked slate is in the same org; a hard delete nulls the link, keeping the label
FOREIGN KEY (organization_id, slate_id) REFERENCES slates (organization_id, id) ON DELETE SET NULL
);
CREATE INDEX idx_bid_outcomes_org ON bid_outcomes (organization_id, created_at DESC);
CREATE INDEX idx_bid_outcomes_org_result ON bid_outcomes (organization_id, result);
ALTER TABLE bid_outcomes ENABLE ROW LEVEL SECURITY;
ALTER TABLE bid_outcomes FORCE ROW LEVEL SECURITY;
CREATE POLICY bid_outcomes_isolation ON bid_outcomes
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);
-- The app role records an outcome (INSERT), updates the result and award once known (UPDATE), and reads it
-- (SELECT for the advisory base rate). It never deletes an outcome: a win or loss label is a permanent fact.
GRANT SELECT, INSERT, UPDATE ON bid_outcomes TO missionmatch_app;
-- +goose Down
DROP TABLE IF EXISTS bid_outcomes;