alu-chatbot / schema_organization_admins.sql
Donne120
Claude Sonnet 5
fix(admin): make admin authorization per-organization, not one flat global list
a9f393f
Raw History Blame Contribute Delete
1.36 kB
-- organization_admins: replaces the single flat ADMIN_EMAILS env var with a
-- real, per-organization admin list. An email here can administer ONLY the
-- one organization_id it's tied to — this is the actual fix for the gap
-- where ADMIN_EMAILS being one global set meant there was no structural way
-- to give two different universities separate admin lists.
--
-- Run this once against the live Aurora cluster (CloudShell + psql) before
-- deploying the code that depends on it. Safe to run even if the table
-- already exists (IF NOT EXISTS).
CREATE TABLE IF NOT EXISTS organization_admins (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL REFERENCES organizations(id),
email text NOT NULL,
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (email)
);
-- One admin email can administer exactly one organization. If the same
-- person needs to administer two organizations, they need two separate
-- emails — this constraint is deliberate: it's what makes "an admin can
-- only ever act as their own org" a database guarantee, not just an
-- assumption every route has to individually get right.
CREATE INDEX IF NOT EXISTS idx_organization_admins_org
ON organization_admins (organization_id)
WHERE is_active;