Janady13/missionmatch / db/migrations/0076_opportunity_scope_items.sql

Source identity

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

Original source ↗

Recovered source transcript

Download complete source text ↓89 lines · 5.3 KB

-- +goose Up
-- opportunity_scope_items is what a solicitation ASKS FOR, broken into reviewable pieces, each one tied
-- back to the exact place it came from. It is the output of extraction and the input to human review, and
-- nothing here can reach the scoring engine: a scope item becomes a requirement only by passing through
-- requirement_drafts and a named human approval.
--
-- THE CITATION RULE IS ENFORCED BY THE DATABASE, not only by the extractor. Gate 2 requires that every
-- extracted scope item carries a source citation, and a rule that lives only in application code is a rule
-- that a future code path can forget. The CHECK below says: an item the machine produced (extracted, or a
-- later edit or merge of one) MUST carry either the document it came from or the hash of the quote it was
-- taken from. Only a human authored item (manual) may stand without a citation, because there the human IS
-- the source. Uncited model output therefore cannot be stored at all, which is a stronger guarantee than
-- rejecting it at the boundary.
--
-- Tenant data (the text is the tenant's solicitation package), so organization_id, forced RLS, a composite
-- index leading with organization_id, composite foreign keys, and UNIQUE (organization_id, id) as a
-- composite FK target for requirement_drafts.

CREATE TABLE opportunity_scope_items (
    organization_id       uuid NOT NULL REFERENCES organizations(id),
    id                    uuid NOT NULL DEFAULT gen_random_uuid(),
    opportunity_id        uuid NOT NULL,
    scope_type            text NOT NULL DEFAULT 'other'
                            CHECK (scope_type IN ('objective','task','deliverable','labor_category',
                              'staffing_quantity','skill','certification','clearance','customer_experience',
                              'location','travel','schedule','security_control','reporting','transition',
                              'key_personnel','pricing_instruction','evaluation_factor','compliance_clause',
                              'assumption','dependency','risk','other')),
    title                 text NOT NULL DEFAULT '',
    description           text NOT NULL DEFAULT '',
    -- Provenance. source_document_id points at the uploaded document a span came from; source_quote_hash
    -- fingerprints the exact quoted text, which is what lets a citation survive a document being superseded
    -- and what lets a notice sourced item (no uploaded document) still be cited.
    source_document_id    uuid,
    source_page_number    integer,
    source_section_label  text NOT NULL DEFAULT '',
    source_quote_hash     text NOT NULL DEFAULT '',
    extraction_method     text NOT NULL DEFAULT '',
    extraction_confidence real NOT NULL DEFAULT 0,
    review_status         text NOT NULL DEFAULT 'extracted'
                            CHECK (review_status IN ('extracted','accepted','edited','rejected','merged','manual')),
    reviewed_by_user_id   uuid,
    reviewed_at           timestamptz,
    parent_scope_item_id  uuid,
    display_order         integer NOT NULL DEFAULT 0,
    created_at            timestamptz NOT NULL DEFAULT now(),
    updated_at            timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (id),
    CONSTRAINT uq_scope_items_org_id UNIQUE (organization_id, id),
    -- Gate 2, enforced in the schema: machine produced scope must be cited.
    CONSTRAINT ck_scope_items_cited CHECK (
        review_status = 'manual' OR source_document_id IS NOT NULL OR source_quote_hash <> ''
    ),
    FOREIGN KEY (organization_id, opportunity_id) REFERENCES tracked_opportunities (organization_id, id),
    FOREIGN KEY (organization_id, source_document_id) REFERENCES opportunity_documents (organization_id, id)
        ON DELETE SET NULL (source_document_id),
    FOREIGN KEY (organization_id, parent_scope_item_id) REFERENCES opportunity_scope_items (organization_id, id)
        ON DELETE SET NULL (parent_scope_item_id),
    FOREIGN KEY (organization_id, reviewed_by_user_id) REFERENCES users (organization_id, id)
        ON DELETE SET NULL (reviewed_by_user_id)
);

-- The review queue reads one opportunity's scope in display order, filtered by status.
CREATE INDEX idx_scope_items_org_opp ON opportunity_scope_items (organization_id, opportunity_id, review_status, display_order);

ALTER TABLE opportunity_scope_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE opportunity_scope_items FORCE  ROW LEVEL SECURITY;
CREATE POLICY scope_items_isolation ON opportunity_scope_items
    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 reviewer accepts, edits, rejects, splits, merges, and adds manually, so the app needs INSERT and
-- UPDATE. Rejection is a status, not a delete, so the extraction record survives its own rejection and the
-- review history stays auditable; there is therefore no DELETE grant.
GRANT SELECT, INSERT, UPDATE ON opportunity_scope_items TO missionmatch_app;

-- +goose Down
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
  SELECT count(*) INTO n FROM opportunity_scope_items;
  IF n > 0 THEN
    RAISE EXCEPTION 'refusing to drop opportunity_scope_items: % reviewed scope rows present', n;
  END IF;
END $$;
-- +goose StatementEnd
DROP TABLE IF EXISTS opportunity_scope_items;