File size: 1,360 Bytes
a9f393f
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 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;