Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0071_opportunity_versions.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0071_opportunity_versions.sql |
| Original SHA-256 | cc2f9ec0964e4b2373c1dd1e317dbdd0c943af36887612a0c93cd83f14105a50 |
| Git blob SHA | 2290467bc60e81bc3000d939da42ff1bd38a63f6 |
| Reading-copy SHA-256 | cc2f9ec0964e4b2373c1dd1e317dbdd0c943af36887612a0c93cd83f14105a50 |
Recovered source transcript
Download complete source text ↓94 lines · 5.6 KB
-- +goose Up
-- opportunity_versions is the immutable version history of a tracked opportunity. Each source refresh or
-- amendment inserts a new row and never rewrites an old one, so the full lineage of a solicitation is
-- preserved for the compliance record. is_current marks the live version and is the ONLY column that may
-- change after insert: it flips to false inside the same transaction that inserts the successor. A trigger
-- in the audit style enforces this, refusing any other update and refusing delete outright, the same
-- posture as audit_logs. Tenant data, 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.
-- supersedes_version_id is a self reference to the version this one replaces, and ingest_manifest_id ties
-- the snapshot to its provenance.
CREATE TABLE opportunity_versions (
organization_id uuid NOT NULL REFERENCES organizations(id),
id uuid NOT NULL DEFAULT gen_random_uuid(),
opportunity_id uuid NOT NULL,
version_number integer NOT NULL,
amendment_number text NOT NULL DEFAULT '', -- the source amendment label, blank for the base
source_version_label text NOT NULL DEFAULT '',
source_payload_hash text NOT NULL DEFAULT '', -- hash of the source payload this version captured
normalized_payload jsonb NOT NULL DEFAULT '{}', -- the normalized snapshot of the opportunity fields
fetched_at timestamptz NOT NULL DEFAULT now(),
effective_at timestamptz, -- when this version became operative, nullable
supersedes_version_id uuid, -- the prior version this one replaces, nullable
ingest_manifest_id uuid REFERENCES ingest_manifest(id) ON DELETE SET NULL,
is_current boolean NOT NULL DEFAULT true, -- the live version; the only mutable column
created_by_user_id uuid,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (id),
CONSTRAINT uq_opp_versions_org_id UNIQUE (organization_id, id),
CONSTRAINT uq_opp_versions_number UNIQUE (organization_id, opportunity_id, version_number),
FOREIGN KEY (organization_id, opportunity_id) REFERENCES tracked_opportunities (organization_id, id),
FOREIGN KEY (organization_id, supersedes_version_id) REFERENCES opportunity_versions (organization_id, id)
ON DELETE SET NULL (supersedes_version_id),
FOREIGN KEY (organization_id, created_by_user_id) REFERENCES users (organization_id, id)
ON DELETE SET NULL (created_by_user_id)
);
-- Version reads are per opportunity, newest first; the composite index leads with organization_id.
CREATE INDEX idx_opp_versions_org_opp ON opportunity_versions (organization_id, opportunity_id, version_number DESC);
-- The one live version per opportunity is found through a partial index on is_current.
CREATE INDEX idx_opp_versions_current ON opportunity_versions (organization_id, opportunity_id) WHERE is_current;
ALTER TABLE opportunity_versions ENABLE ROW LEVEL SECURITY;
ALTER TABLE opportunity_versions FORCE ROW LEVEL SECURITY;
CREATE POLICY opp_versions_isolation ON opportunity_versions
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 immutability trigger: a version may change ONLY its is_current flag, and may never be deleted, so the
-- solicitation lineage cannot be silently rewritten. Every other column being unchanged is asserted by
-- comparing the immutable tuple of OLD and NEW.
-- +goose StatementBegin
CREATE OR REPLACE FUNCTION opportunity_versions_immutable() RETURNS trigger AS $$
BEGIN
IF TG_OP = 'DELETE' THEN
RAISE EXCEPTION 'opportunity_versions is immutable: DELETE is not permitted';
END IF;
IF (OLD.organization_id, OLD.id, OLD.opportunity_id, OLD.version_number, OLD.amendment_number,
OLD.source_version_label, OLD.source_payload_hash, OLD.normalized_payload, OLD.fetched_at,
OLD.effective_at, OLD.supersedes_version_id, OLD.ingest_manifest_id, OLD.created_by_user_id, OLD.created_at)
IS DISTINCT FROM
(NEW.organization_id, NEW.id, NEW.opportunity_id, NEW.version_number, NEW.amendment_number,
NEW.source_version_label, NEW.source_payload_hash, NEW.normalized_payload, NEW.fetched_at,
NEW.effective_at, NEW.supersedes_version_id, NEW.ingest_manifest_id, NEW.created_by_user_id, NEW.created_at) THEN
RAISE EXCEPTION 'opportunity_versions is immutable except the is_current flag';
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd
CREATE TRIGGER opportunity_versions_immutable_trg
BEFORE UPDATE OR DELETE ON opportunity_versions
FOR EACH ROW EXECUTE FUNCTION opportunity_versions_immutable();
-- The app inserts versions and flips is_current; the trigger confines the update and blocks delete, so the
-- grant is SELECT, INSERT, UPDATE with no DELETE.
GRANT SELECT, INSERT, UPDATE ON opportunity_versions TO missionmatch_app;
-- +goose Down
-- +goose StatementBegin
DO $$
DECLARE n bigint;
BEGIN
SELECT count(*) INTO n FROM opportunity_versions;
IF n > 0 THEN
RAISE EXCEPTION 'refusing to drop opportunity_versions: % immutable version rows present', n;
END IF;
END $$;
-- +goose StatementEnd
DROP TRIGGER IF EXISTS opportunity_versions_immutable_trg ON opportunity_versions;
DROP FUNCTION IF EXISTS opportunity_versions_immutable();
DROP TABLE IF EXISTS opportunity_versions;