1CREATE TABLE IF NOT EXISTS handles (
  2  id SERIAL PRIMARY KEY,
  3  handle TEXT NOT NULL UNIQUE,
  4  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  5  updated_at TIMESTAMP NOT NULL
  6);
  7
  8CREATE TABLE IF NOT EXISTS organizations (
  9  id SERIAL PRIMARY KEY,
 10  name TEXT,
 11  contact_email TEXT NOT NULL,
 12  handle_id INTEGER NOT NULL,
 13  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 14  updated_at TIMESTAMP NOT NULL,
 15  CONSTRAINT handle_id_fk
 16  FOREIGN KEY(handle_id) REFERENCES handles(id)
 17  ON DELETE CASCADE
 18  ON UPDATE CASCADE
 19);
 20
 21CREATE TABLE IF NOT EXISTS organization_members (
 22  id SERIAL PRIMARY KEY,
 23  org_id INTEGER NOT NULL,
 24  user_id INTEGER NOT NULL,
 25  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 26  updated_at TIMESTAMP NOT NULL,
 27  UNIQUE (org_id, user_id),
 28  CONSTRAINT org_id_fk
 29  FOREIGN KEY(org_id) REFERENCES organizations(id)
 30  ON DELETE CASCADE
 31  ON UPDATE CASCADE,
 32  CONSTRAINT user_id_fk
 33  FOREIGN KEY(user_id) REFERENCES users(id)
 34  ON DELETE CASCADE
 35  ON UPDATE CASCADE
 36);
 37
 38CREATE TABLE IF NOT EXISTS teams (
 39  id SERIAL PRIMARY KEY,
 40  name TEXT NOT NULL,
 41  org_id INTEGER NOT NULL,
 42  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 43  updated_at TIMESTAMP NOT NULL,
 44  UNIQUE (name, org_id),
 45  CONSTRAINT org_id_fk
 46  FOREIGN KEY(org_id) REFERENCES organizations(id)
 47  ON DELETE CASCADE
 48  ON UPDATE CASCADE
 49);
 50
 51CREATE TABLE IF NOT EXISTS team_members (
 52  id SERIAL PRIMARY KEY,
 53  team_id INTEGER NOT NULL,
 54  user_id INTEGER NOT NULL,
 55  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 56  updated_at TIMESTAMP NOT NULL,
 57  UNIQUE (team_id, user_id),
 58  CONSTRAINT team_id_fk
 59  FOREIGN KEY(team_id) REFERENCES teams(id)
 60  ON DELETE CASCADE
 61  ON UPDATE CASCADE,
 62  CONSTRAINT user_id_fk
 63  FOREIGN KEY(user_id) REFERENCES users(id)
 64  ON DELETE CASCADE
 65  ON UPDATE CASCADE
 66);
 67
 68CREATE TABLE IF NOT EXISTS user_emails (
 69  id SERIAL PRIMARY KEY,
 70  user_id INTEGER NOT NULL,
 71  email TEXT NOT NULL UNIQUE,
 72  is_primary BOOLEAN NOT NULL,
 73  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 74  updated_at TIMESTAMP NOT NULL,
 75  CONSTRAINT user_id_fk
 76  FOREIGN KEY(user_id) REFERENCES users(id)
 77  ON DELETE CASCADE
 78  ON UPDATE CASCADE
 79);
 80
 81-- Add name to users table
 82ALTER TABLE users ADD COLUMN name TEXT;
 83
 84-- Add handle_id to users table
 85ALTER TABLE users ADD COLUMN handle_id INTEGER;
 86ALTER TABLE users ADD CONSTRAINT handle_id_fk
 87  FOREIGN KEY(handle_id) REFERENCES handles(id)
 88  ON DELETE CASCADE
 89  ON UPDATE CASCADE;
 90
 91-- Migrate user username to handles
 92INSERT INTO handles (handle, updated_at) SELECT username, updated_at FROM users;
 93
 94-- Update handle_id for users
 95UPDATE users SET handle_id = handles.id FROM handles WHERE handles.handle = users.username;
 96
 97-- Make handle_id not null and unique
 98ALTER TABLE users ALTER COLUMN handle_id SET NOT NULL;
 99ALTER TABLE users ADD CONSTRAINT handle_id_unique UNIQUE (handle_id);
100
101-- Drop username from users
102ALTER TABLE users DROP COLUMN username;
103
104-- Add org_id to repos table
105ALTER TABLE repos ADD COLUMN org_id INTEGER;
106ALTER TABLE repos ADD CONSTRAINT org_id_fk
107  FOREIGN KEY(org_id) REFERENCES organizations(id)
108  ON DELETE CASCADE
109  ON UPDATE CASCADE;
110
111-- Alter user_id nullness in repos table
112ALTER TABLE repos ALTER COLUMN user_id DROP NOT NULL;
113
114-- Check that both user_id and org_id can't be null
115ALTER TABLE repos ADD CONSTRAINT user_id_org_id_not_null CHECK (user_id IS NULL <> org_id IS NULL);
116
117-- Add team_id to collabs table
118ALTER TABLE collabs ADD COLUMN team_id INTEGER;
119ALTER TABLE collabs ADD CONSTRAINT team_id_fk
120  FOREIGN KEY(team_id) REFERENCES teams(id)
121  ON DELETE CASCADE
122  ON UPDATE CASCADE;
123
124-- Alter user_id nullness in collabs table
125ALTER TABLE collabs ALTER COLUMN user_id DROP NOT NULL;
126
127-- Check that both user_id and team_id can't be null
128ALTER TABLE collabs ADD CONSTRAINT user_id_team_id_not_null CHECK (user_id IS NULL <> team_id IS NULL);
129
130-- Alter unique constraint on collabs table
131ALTER TABLE collabs DROP CONSTRAINT collabs_user_id_repo_id_key;
132ALTER TABLE collabs ADD CONSTRAINT collabs_user_id_repo_id_team_id_key UNIQUE (user_id, repo_id, team_id);