Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0062_superuser_and_tool_access.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0062_superuser_and_tool_access.sql |
| Original SHA-256 | df5bc6d34aaace8e59eb4ae01ce8265164503feacee489f5cbd405ba7d2d2cb6 |
| Git blob SHA | 2712540e312f19e9ee5b71af357cd331a2a08a35 |
| Reading-copy SHA-256 | df5bc6d34aaace8e59eb4ae01ce8265164503feacee489f5cbd405ba7d2d2cb6 |
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/0062_superuser_and_tool_access.sql | feature/opportunity-to-outcome | 720d47fae476b0d38477ae4401697d372b1ea2bc | Open original |
| Janady13/missionmatch / db/migrations/0062_superuser_and_tool_access.sql | feature/aioe-upgrade | 2178e7298d0e5d9af83afa12402fd587f0ba6ed8 | Open original |
Recovered source transcript
Download complete source text ↓125 lines · 7.2 KB
-- +goose Up
-- Two access control additions: a superuser tier above owner, and per user tool entitlement.
--
-- They answer different questions and are deliberately kept separate. The ROLE says what a person may
-- DO (invite, disable, edit a requirement, run an export). The TOOL SET says what they may SEE (which of
-- the four products appears at all). A recruiter at one client may work only recruitMATCH and slateMATCH
-- while a recruiter at another sees everything, and that is not a difference in authority, so it must
-- not be expressed as a difference in role.
-- ---------------------------------------------------------------------------------------------------
-- 1. The superuser tier.
--
-- The ladder becomes viewer, member, recruiter, manager, admin, owner, superuser. The existing rule
-- stands for everyone: you may only grant a role strictly BELOW your own, so an admin cannot mint an
-- admin and an owner cannot mint an owner.
--
-- Superuser is the single deliberate exception, and the reason is operational rather than theoretical:
-- the tier has to be able to reproduce itself or it cannot survive its holders. The named holders grant
-- it to each other, and it is enforced in the service, not here. This constraint only widens the
-- vocabulary; who may write which value is the auth service's separation of duties check.
ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
CHECK (role IN ('superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));
-- Both vocabularies must move together. Migration 0058 was shipped incomplete because only the users
-- table was widened, and inviting a manager then failed on the invite constraint instead.
ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
CHECK (role IN ('superuser', 'owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));
-- ---------------------------------------------------------------------------------------------------
-- 2. Per user tool entitlement.
--
-- A row grants one user access to one tool. Absence of a row is absence of access: the API and the
-- interface both fail CLOSED, so a user created without an explicit grant sees nothing rather than
-- everything. That direction matters because the failure mode of the opposite default is silent over
-- exposure of a whole product to someone who was never meant to have it.
--
-- The grant is recorded with WHO granted it and WHEN, because an entitlement change is an access control
-- event and the first question after an incident is who widened it.
CREATE TABLE IF NOT EXISTS user_tool_access (
organization_id uuid NOT NULL REFERENCES organizations (id),
user_id uuid NOT NULL,
tool text NOT NULL
CHECK (tool IN ('missionmatch', 'screenmatch', 'slatematch', 'recruitmatch')),
granted_by uuid,
granted_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT user_tool_access_pkey PRIMARY KEY (organization_id, user_id, tool),
-- Composite, leading with organization_id, so the reference cannot resolve across a tenant boundary
-- (CLAUDE.md rule 2 and the composite FK convention the rest of the schema uses).
CONSTRAINT user_tool_access_user_fkey
FOREIGN KEY (organization_id, user_id) REFERENCES users (organization_id, id) ON DELETE CASCADE,
-- The granter is recorded for the audit trail, not for referential integrity of the grant itself: if
-- that account is later deleted the entitlement must SURVIVE, or removing an administrator would
-- silently revoke access for everyone they ever granted. The column list form is required; a bare
-- ON DELETE SET NULL would try to null organization_id too, which is NOT NULL (see migration 0053).
CONSTRAINT user_tool_access_granted_by_fkey
FOREIGN KEY (organization_id, granted_by) REFERENCES users (organization_id, id)
ON DELETE SET NULL (granted_by)
);
-- The hot read is "which tools does this user have", on every session resolve.
CREATE INDEX IF NOT EXISTS idx_user_tool_access_user ON user_tool_access (organization_id, user_id);
-- +goose StatementBegin
DO $$
BEGIN
-- Tenant scoped like every other user facing table: forced RLS so even the table owner is subject to
-- the policy, and no org context yields zero rows.
EXECUTE 'ALTER TABLE user_tool_access ENABLE ROW LEVEL SECURITY';
EXECUTE 'ALTER TABLE user_tool_access FORCE ROW LEVEL SECURITY';
IF NOT EXISTS (SELECT 1 FROM pg_policy WHERE polname = 'user_tool_access_tenant_isolation') THEN
EXECUTE $p$
CREATE POLICY user_tool_access_tenant_isolation ON user_tool_access
USING (organization_id = COALESCE(NULLIF(current_setting('app.current_org_id', true), ''), '00000000-0000-0000-0000-000000000000')::uuid)
WITH CHECK (organization_id = COALESCE(NULLIF(current_setting('app.current_org_id', true), ''), '00000000-0000-0000-0000-000000000000')::uuid)
$p$;
END IF;
END
$$;
-- +goose StatementEnd
GRANT SELECT, INSERT, DELETE ON user_tool_access TO missionmatch_app;
-- ---------------------------------------------------------------------------------------------------
-- 3. Backfill, which is the part that must not be skipped.
--
-- Every existing user today sees all four tools, because tool gating did not exist. Creating the table
-- and failing closed WITHOUT this backfill would revoke every product from every existing account the
-- moment this migration lands, including the owner's, which locks the operator out of the very screen
-- that grants access back. Existing accounts therefore keep exactly what they have; fail closed applies
-- to accounts created from here on.
--
-- granted_by is left NULL: no human granted these, the migration did, and recording a person would be a
-- false audit entry.
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)
ON CONFLICT DO NOTHING;
-- +goose Down
DROP TABLE IF EXISTS user_tool_access;
-- Demote BEFORE narrowing, on both tables. The users half was ordered correctly and the user_invites half
-- was inverted: the CHECK was re added first, so a single pending superuser invite aborted the rollback
-- with a check violation, and because the DROP had already succeeded the invite table was left with NO
-- role vocabulary at all while user_tool_access was already gone. A half applied rollback that removes a
-- constraint and then fails is worse than one that refuses to start.
--
-- Demoting to owner preserves the most authority the narrower vocabulary can express.
UPDATE users SET role = 'owner' WHERE role = 'superuser';
UPDATE user_invites SET role = 'owner' WHERE role = 'superuser';
ALTER TABLE user_invites DROP CONSTRAINT user_invites_role_check;
ALTER TABLE user_invites ADD CONSTRAINT user_invites_role_check
CHECK (role IN ('owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));
ALTER TABLE users DROP CONSTRAINT users_role_check;
ALTER TABLE users ADD CONSTRAINT users_role_check
CHECK (role IN ('owner', 'admin', 'manager', 'recruiter', 'member', 'viewer'));