Slack clone raw sql schema

slack.sql

CREATE TABLE IF NOT EXISTS "users" (
  "id" SERIAL,
  "username" VARCHAR(255) UNIQUE,
  "email" VARCHAR(255) UNIQUE,
  "password" VARCHAR(255),
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  UNIQUE ("username"),
  UNIQUE ("email"),
  PRIMARY KEY ("id")
);

SELECT
  i.relname AS name,
  ix.indisprimary AS primary,
  ix.indisunique AS unique,
  ix.indkey AS indkey,
  array_agg(a.attnum) as column_indexes,
  array_agg(a.attname) AS column_names,
  pg_get_indexdef(ix.indexrelid) AS definition
FROM
  pg_class t,
  pg_class i,
  pg_index ix,
  pg_attribute a
WHERE
  t.oid = ix.indrelid
  AND i.oid = ix.indexrelid
  AND a.attrelid = t.oid
  AND t.relkind = 'r'
  and t.relname = 'users'
GROUP BY
  i.relname,
  ix.indexrelid,
  ix.indisprimary,
  ix.indisunique,
  ix.indkey
ORDER BY
  i.relname;

CREATE TABLE IF NOT EXISTS "teams" (
  "id" SERIAL,
  "name" VARCHAR(255) UNIQUE,
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "owner" INTEGER REFERENCES "users" ("id") ON DELETE
  SET
    NULL ON UPDATE CASCADE,
    UNIQUE ("name"),
    PRIMARY KEY ("id")
);

SELECT
  i.relname AS name,
  ix.indisprimary AS primary,
  ix.indisunique AS unique,
  ix.indkey AS indkey,
  array_agg(a.attnum) as column_indexes,
  array_agg(a.attname) AS column_names,
  pg_get_indexdef(ix.indexrelid) AS definition
FROM
  pg_class t,
  pg_class i,
  pg_index ix,
  pg_attribute a
WHERE
  t.oid = ix.indrelid
  AND i.oid = ix.indexrelid
  AND a.attrelid = t.oid
  AND t.relkind = 'r'
  and t.relname = 'teams'
GROUP BY
  i.relname,
  ix.indexrelid,
  ix.indisprimary,
  ix.indisunique,
  ix.indkey
ORDER BY
  i.relname;

CREATE TABLE IF NOT EXISTS "channels" (
  "id" SERIAL,
  "name" VARCHAR(255),
  "public" BOOLEAN,
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "teamId" INTEGER REFERENCES "teams" ("id") ON DELETE
  SET
    NULL ON UPDATE CASCADE,
    PRIMARY KEY ("id")
);

SELECT
  i.relname AS name,
  ix.indisprimary AS primary,
  ix.indisunique AS unique,
  ix.indkey AS indkey,
  array_agg(a.attnum) as column_indexes,
  array_agg(a.attname) AS column_names,
  pg_get_indexdef(ix.indexrelid) AS definition
FROM
  pg_class t,
  pg_class i,
  pg_index ix,
  pg_attribute a
WHERE
  t.oid = ix.indrelid
  AND i.oid = ix.indexrelid
  AND a.attrelid = t.oid
  AND t.relkind = 'r'
  and t.relname = 'channels'
GROUP BY
  i.relname,
  ix.indexrelid,
  ix.indisprimary,
  ix.indisunique,
  ix.indkey
ORDER BY
  i.relname;

CREATE TABLE IF NOT EXISTS "messages" (
  "id" SERIAL,
  "text" VARCHAR(255),
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "channelId" INTEGER REFERENCES "channels" ("id") ON DELETE
  SET
    NULL ON UPDATE CASCADE,
    "userId" INTEGER REFERENCES "users" ("id") ON DELETE
  SET
    NULL ON UPDATE CASCADE,
    PRIMARY KEY ("id")
);

SELECT
  i.relname AS name,
  ix.indisprimary AS primary,
  ix.indisunique AS unique,
  ix.indkey AS indkey,
  array_agg(a.attnum) as column_indexes,
  array_agg(a.attname) AS column_names,
  pg_get_indexdef(ix.indexrelid) AS definition
FROM
  pg_class t,
  pg_class i,
  pg_index ix,
  pg_attribute a
WHERE
  t.oid = ix.indrelid
  AND i.oid = ix.indexrelid
  AND a.attrelid = t.oid
  AND t.relkind = 'r'
  and t.relname = 'messages'
GROUP BY
  i.relname,
  ix.indexrelid,
  ix.indisprimary,
  ix.indisunique,
  ix.indkey
ORDER BY
  i.relname;

CREATE TABLE IF NOT EXISTS "member" (
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "userId" INTEGER REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  "teamId" INTEGER REFERENCES "teams" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
  PRIMARY KEY ("userId", "teamId")
);

SELECT
  i.relname AS name,
  ix.indisprimary AS primary,
  ix.indisunique AS unique,
  ix.indkey AS indkey,
  array_agg(a.attnum) as column_indexes,
  array_agg(a.attname) AS column_names,
  pg_get_indexdef(ix.indexrelid) AS definition
FROM
  pg_class t,
  pg_class i,
  pg_index ix,
  pg_attribute a
WHERE
  t.oid = ix.indrelid
  AND i.oid = ix.indexrelid
  AND a.attrelid = t.oid
  AND t.relkind = 'r'
  and t.relname = 'member'
GROUP BY
  i.relname,
  ix.indexrelid,
  ix.indisprimary,
  ix.indisunique,
  ix.indkey
ORDER BY
  i.relname;