Janady13/missionmatch / db/migrations/0043_aioe.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0043_aioe.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0043_aioe.sql
Original SHA-256	cbe464ef78848da271bf02bd6ecf0a47d8e6c62f77c150b9e1438af88e05ca20
Git blob SHA	6bdc619c16919dc5124cdba6aa97c920b9f9561e
Reading-copy SHA-256	cbe464ef78848da271bf02bd6ecf0a47d8e6c62f77c150b9e1438af88e05ca20
Original source ↗
Every recorded occurrence

These 2 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/0043_aioe.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0043_aioe.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original
Recovered source transcript

Download complete source text ↓164 lines · 10.2 KB
-- +goose Up
-- AIOE, the Adaptive Intelligence Optimization Engine (MWT-2212, FSM-002). Three tenant scoped tables:
-- the outcome ledger every module writes, the versioned per tenant weight profiles the scorer reads, and
-- the append only adjustment audit trail. Nothing changes silently: every weight change is one row here.
--
-- Isolation by construction: the batch updater runs as the dedicated role missionmatch_aioe, which is
-- granted these three tables and NOTHING else. It has no grant on candidates, candidate_evaluations, any
-- eeo table, or any other tenant data, so it is physically unable to read a protected attribute. The
-- updater consumes outcome_events ONLY; any scoring context an adjustment needs (the served per dimension
-- scores) is denormalized into outcome_events.metadata by the app at capture time.
--
-- RLS: the app role keeps the exact per org isolation policy every tenant table uses. The aioe role gets
-- its own explicit cross tenant policies on ONLY these tables, because the nightly batch iterates every
-- tenant's profile in one deterministic pass. That cross tenant read is confined to outcome and weight
-- data by the grants above, reviewed here, and reaches no personal record.

-- ---------------------------------------------------------------------------
-- The dedicated updater role missionmatch_aioe is PROVISIONED OUT OF BAND (CREATE ROLE
-- missionmatch_aioe LOGIN NOBYPASSRLS PASSWORD ...), exactly as the app and compliance roles are:
-- the migrator deliberately has no CREATEROLE. This migration only GRANTS to it, so applying 0043
-- requires the role to exist first; the provisioning runbook and the staging setup own that step.
-- ---------------------------------------------------------------------------
-- ---------------------------------------------------------------------------
-- outcome_events: the longitudinal outcome ledger, append only from the app (SELECT, INSERT, no UPDATE
-- and no DELETE). signal_value is the normalized outcome in [-1, 1] (for example advance 1, hold 0,
-- reject -1, placement success 1). This is distinct from bid_outcomes (win loss labels for capture) and
-- candidate_evaluations (the served score cache): this table records what HAPPENED after a decision.
-- ---------------------------------------------------------------------------
CREATE TABLE outcome_events (
    organization_id uuid NOT NULL REFERENCES organizations(id),
    id              uuid NOT NULL DEFAULT gen_random_uuid(),
    module          text NOT NULL CHECK (module IN ('screen', 'match', 'slate', 'command')),
    subject_type    text NOT NULL DEFAULT 'candidate',
    subject_id      uuid NOT NULL,
    event_type      text NOT NULL CHECK (event_type IN (
                        'screening_decision', 'interview_result', 'offer_result',
                        'placement_result', 'slate_result', 'match_feedback')),
    signal_value    numeric(6,4) NOT NULL CHECK (signal_value >= -1 AND signal_value <= 1),
    reason_code     text NOT NULL DEFAULT '',
    actor_id        uuid,
    occurred_at     timestamptz NOT NULL DEFAULT now(),
    metadata        jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at      timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id)
);
CREATE INDEX idx_outcome_events_org_occurred ON outcome_events (organization_id, occurred_at DESC);
CREATE INDEX idx_outcome_events_org_type     ON outcome_events (organization_id, event_type, occurred_at DESC);
CREATE INDEX idx_outcome_events_org_subject  ON outcome_events (organization_id, subject_type, subject_id);

ALTER TABLE outcome_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE outcome_events FORCE  ROW LEVEL SECURITY;
CREATE POLICY outcome_events_isolation ON outcome_events TO missionmatch_app
    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);
GRANT SELECT, INSERT ON outcome_events TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- aioe_weight_profiles: the versioned dimension weight sets. dimension_weights is jsonb of decimal
-- STRINGS (exact, never a float), keys clearance, skills, certifications, customer_experience,
-- experience, location, values summing to exactly 1.0000. At most one active profile per org (partial
-- unique index). A tenant with no active profile scores on the global default weights.
-- ---------------------------------------------------------------------------
CREATE TABLE aioe_weight_profiles (
    organization_id  uuid NOT NULL REFERENCES organizations(id),
    id               uuid NOT NULL DEFAULT gen_random_uuid(),
    profile_version  integer NOT NULL CHECK (profile_version >= 1),
    dimension_weights jsonb NOT NULL,
    status           text NOT NULL DEFAULT 'candidate' CHECK (status IN ('active', 'candidate', 'retired')),
    created_at       timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    UNIQUE (organization_id, profile_version)
);
CREATE UNIQUE INDEX uq_aioe_weight_profiles_one_active
    ON aioe_weight_profiles (organization_id) WHERE status = 'active';

ALTER TABLE aioe_weight_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE aioe_weight_profiles FORCE  ROW LEVEL SECURITY;
CREATE POLICY aioe_weight_profiles_isolation ON aioe_weight_profiles TO missionmatch_app
    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 READS the active profile at scoring time. Only the aioe role writes profiles.
GRANT SELECT ON aioe_weight_profiles TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- aioe_weight_adjustments: the full audit trail, one append only row per dimension change with the old
-- value, the new value, the outcome window consumed, the rationale, and the profile version produced.
-- ---------------------------------------------------------------------------
CREATE TABLE aioe_weight_adjustments (
    organization_id  uuid NOT NULL REFERENCES organizations(id),
    id               uuid NOT NULL DEFAULT gen_random_uuid(),
    profile_id       uuid NOT NULL,
    produced_version integer NOT NULL,
    dimension        text NOT NULL CHECK (dimension IN (
                        'clearance', 'skills', 'certifications',
                        'customer_experience', 'experience', 'location')),
    old_value        numeric(7,6) NOT NULL,
    new_value        numeric(7,6) NOT NULL,
    window_start     timestamptz NOT NULL,
    window_end       timestamptz NOT NULL,
    outcomes_used    integer NOT NULL,
    rationale        text NOT NULL,
    created_at       timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (organization_id, id),
    FOREIGN KEY (organization_id, profile_id)
        REFERENCES aioe_weight_profiles (organization_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_aioe_weight_adjustments_org ON aioe_weight_adjustments (organization_id, created_at DESC);

ALTER TABLE aioe_weight_adjustments ENABLE ROW LEVEL SECURITY;
ALTER TABLE aioe_weight_adjustments FORCE  ROW LEVEL SECURITY;
CREATE POLICY aioe_weight_adjustments_isolation ON aioe_weight_adjustments TO missionmatch_app
    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 reads the trail for the admin history endpoint. Only the aioe role writes it.
GRANT SELECT ON aioe_weight_adjustments TO missionmatch_app;

-- ---------------------------------------------------------------------------
-- aioe_runs: the operational run ledger, one row per updater pass (scheduled or on demand). Like
-- audit_logs it lives OUTSIDE RLS: it carries only counts, never tenant content. It is how the
-- admin status endpoint proves the engine is alive even on a run that changed nothing.
-- ---------------------------------------------------------------------------
CREATE TABLE aioe_runs (
    id               uuid NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
    ran_at           timestamptz NOT NULL DEFAULT now(),
    as_of            timestamptz NOT NULL,
    window_days      integer NOT NULL,
    orgs_scanned     integer NOT NULL,
    outcomes_seen    integer NOT NULL,
    profiles_updated integer NOT NULL,
    triggered_by     text NOT NULL DEFAULT 'schedule' CHECK (triggered_by IN ('schedule', 'admin'))
);
GRANT SELECT ON aioe_runs TO missionmatch_app;
GRANT SELECT, INSERT ON aioe_runs TO missionmatch_aioe;

-- ---------------------------------------------------------------------------
-- candidate_evaluations: stamp WHICH weight profile produced each served score (explainability). The
-- default names the global fallback so every historical row remains truthful.
-- ---------------------------------------------------------------------------
ALTER TABLE candidate_evaluations
    ADD COLUMN weight_profile_version text NOT NULL DEFAULT 'global.default';


GRANT SELECT ON outcome_events TO missionmatch_aioe;
GRANT SELECT, INSERT, UPDATE ON aioe_weight_profiles TO missionmatch_aioe;
GRANT SELECT, INSERT ON aioe_weight_adjustments TO missionmatch_aioe;

-- The batch pass is cross tenant by design (one deterministic nightly sweep over every org), so the aioe
-- role gets explicit unrestricted policies on ONLY these three tables. Its grants reach nothing else.
CREATE POLICY outcome_events_aioe_read ON outcome_events
    FOR SELECT TO missionmatch_aioe USING (true);
CREATE POLICY aioe_weight_profiles_aioe_all ON aioe_weight_profiles
    TO missionmatch_aioe USING (true) WITH CHECK (true);
CREATE POLICY aioe_weight_adjustments_aioe ON aioe_weight_adjustments
    TO missionmatch_aioe USING (true) WITH CHECK (true);

-- +goose Down
ALTER TABLE candidate_evaluations DROP COLUMN IF EXISTS weight_profile_version;
DROP TABLE IF EXISTS aioe_runs;
DROP TABLE IF EXISTS aioe_weight_adjustments;
DROP TABLE IF EXISTS aioe_weight_profiles;
DROP TABLE IF EXISTS outcome_events;
-- The missionmatch_aioe role is intentionally NOT dropped: its table grants vanish with the tables above
-- and dropping a role that other objects may reference is an operator decision, not a migration side effect.