1CREATE TABLE IF NOT EXISTS "orgs" (
 2    "id" SERIAL PRIMARY KEY,
 3    "name" VARCHAR NOT NULL,
 4    "slug" VARCHAR NOT NULL
 5);
 6
 7CREATE UNIQUE INDEX "index_orgs_slug" ON "orgs" ("slug");
 8
 9CREATE TABLE IF NOT EXISTS "org_memberships" (
10    "id" SERIAL PRIMARY KEY,
11    "org_id" INTEGER REFERENCES orgs (id) NOT NULL,
12    "user_id" INTEGER REFERENCES users (id) NOT NULL,
13    "admin" BOOLEAN NOT NULL
14);
15
16CREATE INDEX "index_org_memberships_user_id" ON "org_memberships" ("user_id");
17CREATE UNIQUE INDEX "index_org_memberships_org_id_and_user_id" ON "org_memberships" ("org_id", "user_id");
18
19CREATE TABLE IF NOT EXISTS "channels" (
20    "id" SERIAL PRIMARY KEY,
21    "owner_id" INTEGER NOT NULL,
22    "owner_is_user" BOOLEAN NOT NULL,
23    "name" VARCHAR NOT NULL
24);
25
26CREATE UNIQUE INDEX "index_channels_owner_and_name" ON "channels" ("owner_is_user", "owner_id", "name");
27
28CREATE TABLE IF NOT EXISTS "channel_memberships" (
29    "id" SERIAL PRIMARY KEY,
30    "channel_id" INTEGER REFERENCES channels (id) NOT NULL,
31    "user_id" INTEGER REFERENCES users (id) NOT NULL,
32    "admin" BOOLEAN NOT NULL
33);
34
35CREATE INDEX "index_channel_memberships_user_id" ON "channel_memberships" ("user_id");
36CREATE UNIQUE INDEX "index_channel_memberships_channel_id_and_user_id" ON "channel_memberships" ("channel_id", "user_id");
37
38CREATE TABLE IF NOT EXISTS "channel_messages" (
39    "id" SERIAL PRIMARY KEY,
40    "channel_id" INTEGER REFERENCES channels (id) NOT NULL,
41    "sender_id" INTEGER REFERENCES users (id) NOT NULL,
42    "body" TEXT NOT NULL,
43    "sent_at" TIMESTAMP
44);
45
46CREATE INDEX "index_channel_messages_channel_id" ON "channel_messages" ("channel_id");