Janady13/missionmatch / db/migrations/0063_provision_org_grants_tools.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/migrations/0063_provision_org_grants_tools.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/migrations/0063_provision_org_grants_tools.sql
Original SHA-256	37ea93ec59a50b7eba7052b2637abd7a436415e5ce6a707d043614fe160fb4b8
Git blob SHA	3d85a0db5317df5bc8732c6959a6f52594205588
Reading-copy SHA-256	37ea93ec59a50b7eba7052b2637abd7a436415e5ce6a707d043614fe160fb4b8

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/0063_provision_org_grants_tools.sql	feature/opportunity-to-outcome	720d47fae476b0d38477ae4401697d372b1ea2bc	Open original
Janady13/missionmatch / db/migrations/0063_provision_org_grants_tools.sql	feature/aioe-upgrade	2178e7298d0e5d9af83afa12402fd587f0ba6ed8	Open original

Recovered source transcript

Download complete source text ↓56 lines · 3.0 KB

-- +goose Up
-- A newly provisioned organization's owner must start with every tool, or the organization is born
-- locked out of itself.
--
-- Migration 0062 made tool entitlement fail closed and backfilled every user that EXISTED at that moment.
-- provision_org, which creates a brand new organization and its first owner, was never taught to grant
-- anything. So an org provisioned after 0062 produced an owner who could authenticate and then found
-- every product 404, with no way back: SetUserTools refuses a self edit, and the possession rule means an
-- actor can only confer tools they already hold, which is none. There is no second account to ask for
-- help, because the owner IS the first account. Recovery required direct database access.
--
-- Fixing it here rather than in the application matters: the owner and the grants must appear in the SAME
-- transaction that creates the organization. Doing it in the CLI afterwards would leave a window, and a
-- failure in that window produces exactly the stranded org this migration exists to prevent.

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION provision_org_grant_owner_tools() RETURNS trigger AS $$
BEGIN
    -- Only the first owner of an organization is seeded this way. Every later account is created through
    -- the invite path, which carries an explicit tool set chosen by a human, and must keep failing closed.
    IF NEW.role = 'owner' AND NOT EXISTS (
        SELECT 1 FROM users u
         WHERE u.organization_id = NEW.organization_id AND u.id <> NEW.id
    ) THEN
        INSERT INTO user_tool_access (organization_id, user_id, tool)
        SELECT NEW.organization_id, NEW.id, t.tool
          FROM (VALUES ('missionmatch'),('screenmatch'),('slatematch'),('recruitmatch')) AS t(tool)
        ON CONFLICT DO NOTHING;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;
-- +goose StatementEnd

-- A trigger rather than an edit to provision_org itself, because provision_org is a large SECURITY
-- DEFINER function defined in 0009 and re declaring it here would fork its definition across two
-- migrations, so a later change to 0009 would silently not apply. The trigger states the one new
-- invariant, next to the table it constrains, and survives any future rewrite of the provisioning path.
DROP TRIGGER IF EXISTS trg_provision_org_grant_owner_tools ON users;
CREATE TRIGGER trg_provision_org_grant_owner_tools
    AFTER INSERT ON users
    FOR EACH ROW EXECUTE FUNCTION provision_org_grant_owner_tools();

-- Repair any organization already provisioned into the stranded state.
INSERT INTO user_tool_access (organization_id, user_id, tool)
SELECT u.organization_id, u.id, t.tool
  FROM users u
  CROSS JOIN (VALUES ('missionmatch'),('screenmatch'),('slatematch'),('recruitmatch')) AS t(tool)
 WHERE u.role IN ('owner', 'superuser')
   AND NOT EXISTS (SELECT 1 FROM user_tool_access a WHERE a.user_id = u.id)
ON CONFLICT DO NOTHING;

-- +goose Down
DROP TRIGGER IF EXISTS trg_provision_org_grant_owner_tools ON users;
DROP FUNCTION IF EXISTS provision_org_grant_owner_tools();