Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0070_tracked_opportunities.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0070_tracked_opportunities.sql |
| Original SHA-256 | 7f9d43070b37b862581a67672c59d00773887fde95a97e27c022c9aec8200579 |
| Git blob SHA | cda7d4d5149a89e854c45d461073642f73dd8612 |
| Reading-copy SHA-256 | 7f9d43070b37b862581a67672c59d00773887fde95a97e27c022c9aec8200579 |
Recovered source transcript
Download complete source text ↓88 lines · 5.3 KB
-- +goose Up
-- tracked_opportunities is the tenant owned capture layer above the shared public opportunity cache. A
-- tenant tracks a federal opportunity here whether it came from SAM.gov, an agency forecast, a customer or
-- teaming partner scope, a spreadsheet import, or manual entry, and every downstream artifact (versions,
-- documents, scope, requirement drafts, proposals) hangs off this row. It is tenant data, so it carries
-- organization_id, forced RLS, and a composite index leading with organization_id, and it is a composite
-- foreign key target through UNIQUE (organization_id, id). public_opportunity_id is a soft pointer to the
-- shared opportunity_records catalog (no hard foreign key, so a tenant row never couples to the reference
-- data refresh lifecycle), and the unique partial index stops a tenant tracking one public notice twice.
-- source_manifest_id ties the row to its provenance in ingest_manifest. capture_owner_user_id and
-- created_by_user_id are composite foreign keys to users with the column list SET NULL form so a user
-- deletion nulls only that column, never organization_id (migration 0053 exists because the bare form is a
-- trap). Manual and SAM.gov opportunities are the same shape, so a manually created row supports every
-- downstream capability a synced one does.
CREATE TABLE tracked_opportunities (
organization_id uuid NOT NULL REFERENCES organizations(id),
id uuid NOT NULL DEFAULT gen_random_uuid(),
public_opportunity_id uuid, -- soft pointer to opportunity_records.id, nullable
source_type text NOT NULL
CHECK (source_type IN ('sam_gov','agency_forecast','customer','partner','internal','spreadsheet','manual')),
source_record_id text NOT NULL DEFAULT '',
sam_notice_id text NOT NULL DEFAULT '',
title text NOT NULL DEFAULT '',
solicitation_number text NOT NULL DEFAULT '',
agency_name text NOT NULL DEFAULT '',
office_name text NOT NULL DEFAULT '',
notice_type text NOT NULL DEFAULT '',
set_aside_code text NOT NULL DEFAULT '',
naics_code text NOT NULL DEFAULT '',
psc_code text NOT NULL DEFAULT '',
place_of_performance text NOT NULL DEFAULT '',
posted_at timestamptz,
response_deadline timestamptz,
estimated_value_min numeric(16,2),
estimated_value_max numeric(16,2),
ceiling_value numeric(16,2),
period_of_performance_start date,
period_of_performance_end date,
incumbent_name text NOT NULL DEFAULT '',
prime_or_sub text NOT NULL DEFAULT ''
CHECK (prime_or_sub IN ('', 'prime', 'sub')),
capture_owner_user_id uuid,
status text NOT NULL DEFAULT 'identified'
CHECK (status IN ('identified','qualifying','qualified','pursuing','no_bid','submitted','won','lost','cancelled','archived')),
source_url text NOT NULL DEFAULT '',
source_manifest_id uuid REFERENCES ingest_manifest(id) ON DELETE SET NULL,
created_by_user_id uuid,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id),
CONSTRAINT uq_tracked_opps_org_id UNIQUE (organization_id, id),
FOREIGN KEY (organization_id, capture_owner_user_id) REFERENCES users (organization_id, id)
ON DELETE SET NULL (capture_owner_user_id),
FOREIGN KEY (organization_id, created_by_user_id) REFERENCES users (organization_id, id)
ON DELETE SET NULL (created_by_user_id)
);
-- The tenant list and pipeline views read by org, status, and deadline; the composite index leads with
-- organization_id per the tenant index rule.
CREATE INDEX idx_tracked_opps_org_status ON tracked_opportunities (organization_id, status, response_deadline);
-- A tenant cannot track one public notice twice: unique per org wherever a public notice is linked.
CREATE UNIQUE INDEX uq_tracked_opps_org_public ON tracked_opportunities (organization_id, public_opportunity_id)
WHERE public_opportunity_id IS NOT NULL;
ALTER TABLE tracked_opportunities ENABLE ROW LEVEL SECURITY;
ALTER TABLE tracked_opportunities FORCE ROW LEVEL SECURITY;
CREATE POLICY tracked_opps_isolation ON tracked_opportunities
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 creates and updates tracked opportunities and reads them; it never hard deletes (the
-- lifecycle ends in the archived or cancelled status), so the grant is SELECT, INSERT, UPDATE with no DELETE.
GRANT SELECT, INSERT, UPDATE ON tracked_opportunities TO missionmatch_app;
-- +goose Down
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
SELECT count(*) INTO n FROM tracked_opportunities;
IF n > 0 THEN
RAISE EXCEPTION 'refusing to drop tracked_opportunities: % application authored rows present', n;
END IF;
END $$;
-- +goose StatementEnd
DROP TABLE IF EXISTS tracked_opportunities;