Janady13/missionmatch / db/migrations/0001_foundation.sql

1-- +goose Up 2-- Foundation: extensions, tenancy root, users, and the append-only audit log. 3-- Run as the BYPASSRLS migrator role. See docs/STACK_DECISIONS.md §2/§4. 4 5CREATE EXTENSION IF NOT EXISTS vector; -- Azure: the binary/extension name is "vector", NOT "pgvector" 6 7-- --------------------------------------------------------------------------- 8-- organizations — the tenant root. RLS scopes a session to its own org row. 9-- INSERT (provisioning a new org at signup) is PRIVILEGED (migrator/provisioning 10-- path), so the app role gets no INSERT grant. status/slug (org lifecycle) are 11-- NOT app-editable — only `name` is (column-level grant below), so a tenant 12-- cannot self-un-suspend by flipping status back to 'active' [review RLS-2]. 13-- --------------------------------------------------------------------------- 14CREATE TABLE organizations ( 15 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 16 name text NOT NULL, 17 slug text NOT NULL UNIQUE, 18 status text NOT NULL DEFAULT 'active' 19 CHECK (status IN ('active', 'suspended')), 20 created_at timestamptz NOT NULL DEFAULT now(), 21 updated_at timestamptz NOT NULL DEFAULT now() 22); 23ALTER TABLE organizations ENABLE ROW LEVEL SECURITY; 24ALTER TABLE organizations FORCE ROW LEVEL SECURITY; 25CREATE POLICY org_self_select ON organizations FOR SELECT 26 USING (id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 27CREATE POLICY org_self_update ON organizations FOR UPDATE 28 USING (id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 29 WITH CHECK (id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 30 31-- --------------------------------------------------------------------------- 32-- users — tenant-scoped. UNIQUE (organization_id, id) lets other tables form a 33-- COMPOSITE FK that pins created_by/decided_by to the same org [review RLS-1/INT-4]. 34-- --------------------------------------------------------------------------- 35CREATE TABLE users ( 36 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 37 organization_id uuid NOT NULL REFERENCES organizations(id), 38 email text NOT NULL, 39 full_name text NOT NULL, 40 role text NOT NULL DEFAULT 'member' 41 CHECK (role IN ('owner', 'admin', 'recruiter', 'member', 'viewer')), 42 status text NOT NULL DEFAULT 'active' 43 CHECK (status IN ('active', 'invited', 'disabled')), 44 last_login_at timestamptz, 45 created_at timestamptz NOT NULL DEFAULT now(), 46 updated_at timestamptz NOT NULL DEFAULT now(), 47 CONSTRAINT uq_users_org_id UNIQUE (organization_id, id) 48); 49CREATE UNIQUE INDEX uq_users_org_email ON users (organization_id, lower(email)); 50CREATE INDEX idx_users_org ON users (organization_id, created_at DESC); -- composite leads with org 51ALTER TABLE users ENABLE ROW LEVEL SECURITY; 52ALTER TABLE users FORCE ROW LEVEL SECURITY; 53CREATE POLICY users_tenant_isolation ON users 54 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 55 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 56 57-- --------------------------------------------------------------------------- 58-- audit_logs — OUTSIDE RLS, APPEND-ONLY. Defense in depth: (1) app role granted 59-- INSERT/SELECT only; (2) a row trigger blocks UPDATE/DELETE; (3) a statement 60-- trigger blocks TRUNCATE [review AUDIT-1]; (4) explicit REVOKEs survive default- 61-- privilege drift [review AUDIT-5]. Reads MUST filter by org (no RLS here). 62-- --------------------------------------------------------------------------- 63CREATE TABLE audit_logs ( 64 id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 65 organization_id uuid, -- nullable: pre-auth events (e.g. failed login) have no org yet 66 actor_user_id uuid, -- nullable: system/anonymous actor 67 action text NOT NULL, -- 'candidate.create','slate.generate','export','login','candidate.search',... 68 entity_type text, 69 entity_id text, 70 old_values jsonb, 71 new_values jsonb, 72 ip_address text, 73 user_agent text, 74 created_at timestamptz NOT NULL DEFAULT now() 75); 76CREATE INDEX idx_audit_org_time ON audit_logs (organization_id, created_at DESC); 77CREATE INDEX idx_audit_entity ON audit_logs (entity_type, entity_id); 78-- Privileged read path for NULL-org (platform/security) events so they aren't a black hole [review AUDIT-3]. 79CREATE INDEX idx_audit_system ON audit_logs (created_at DESC) WHERE organization_id IS NULL; 80 81-- +goose StatementBegin 82CREATE OR REPLACE FUNCTION forbid_mutation() RETURNS trigger AS $$ 83BEGIN 84 RAISE EXCEPTION 'append-only table: % not permitted', TG_OP; 85END; 86$$ LANGUAGE plpgsql; 87-- +goose StatementEnd 88CREATE TRIGGER audit_logs_append_only 89 BEFORE UPDATE OR DELETE ON audit_logs 90 FOR EACH ROW EXECUTE FUNCTION forbid_mutation(); 91CREATE TRIGGER audit_logs_no_truncate 92 BEFORE TRUNCATE ON audit_logs 93 FOR EACH STATEMENT EXECUTE FUNCTION forbid_mutation(); 94 95-- --------------------------------------------------------------------------- 96-- Grants. organizations: SELECT + UPDATE(name) ONLY (no status/slug self-edit). 97-- audit_logs: INSERT/SELECT only, with explicit REVOKEs as belt-and-suspenders. 98-- --------------------------------------------------------------------------- 99GRANT SELECT ON organizations TO missionmatch_app; 100GRANT UPDATE (name) ON organizations TO missionmatch_app; 101GRANT SELECT, INSERT, UPDATE, DELETE ON users TO missionmatch_app; 102GRANT SELECT, INSERT ON audit_logs TO missionmatch_app; 103REVOKE ALL ON audit_logs FROM PUBLIC; 104REVOKE UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON audit_logs FROM missionmatch_app; 105 106-- +goose Down 107DROP TABLE IF EXISTS audit_logs; 108DROP FUNCTION IF EXISTS forbid_mutation(); 109DROP TABLE IF EXISTS users; 110DROP TABLE IF EXISTS organizations; 111