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 DEFAULT false,
 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-- Create unique index for primary email
 82CREATE UNIQUE INDEX user_emails_user_id_is_primary_idx ON user_emails (user_id) WHERE is_primary;
 83
 84-- Add name to users table
 85ALTER TABLE users ADD COLUMN name TEXT;
 86
 87-- Add handle_id to users table
 88ALTER TABLE users ADD COLUMN handle_id INTEGER;
 89ALTER TABLE users ADD CONSTRAINT handle_id_fk
 90  FOREIGN KEY(handle_id) REFERENCES handles(id)
 91  ON DELETE CASCADE
 92  ON UPDATE CASCADE;
 93
 94-- Migrate user username to handles
 95INSERT INTO handles (handle, updated_at) SELECT username, updated_at FROM users;
 96
 97-- Update handle_id for users
 98UPDATE users SET handle_id = handles.id FROM handles WHERE handles.handle = users.username;
 99
100-- Make handle_id not null and unique
101ALTER TABLE users ALTER COLUMN handle_id SET NOT NULL;
102ALTER TABLE users ADD CONSTRAINT handle_id_unique UNIQUE (handle_id);
103
104-- Drop username from users
105ALTER TABLE users DROP COLUMN username;
106
107-- Add org_id to repos table
108ALTER TABLE repos ADD COLUMN org_id INTEGER;
109ALTER TABLE repos ADD CONSTRAINT org_id_fk
110  FOREIGN KEY(org_id) REFERENCES organizations(id)
111  ON DELETE CASCADE
112  ON UPDATE CASCADE;
113
114-- Alter user_id nullness in repos table
115ALTER TABLE repos ALTER COLUMN user_id DROP NOT NULL;
116
117-- Check that both user_id and org_id can't be null
118ALTER TABLE repos ADD CONSTRAINT user_id_org_id_not_null CHECK ((user_id IS NULL) <> (org_id IS NULL));
119
120-- Add team_id to collabs table
121ALTER TABLE collabs ADD COLUMN team_id INTEGER;
122ALTER TABLE collabs ADD CONSTRAINT team_id_fk
123  FOREIGN KEY(team_id) REFERENCES teams(id)
124  ON DELETE CASCADE
125  ON UPDATE CASCADE;
126
127-- Alter user_id nullness in collabs table
128ALTER TABLE collabs ALTER COLUMN user_id DROP NOT NULL;
129
130-- Check that both user_id and team_id can't be null
131ALTER TABLE collabs ADD CONSTRAINT user_id_team_id_not_null CHECK ((user_id IS NULL) <> (team_id IS NULL));
132
133-- Alter unique constraint on collabs table
134ALTER TABLE collabs DROP CONSTRAINT collabs_user_id_repo_id_key;
135ALTER TABLE collabs ADD CONSTRAINT collabs_user_id_repo_id_team_id_key UNIQUE (user_id, repo_id, team_id);