-- 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;