Data modeling for gist clone service

gists_modeling.sql

CREATE EXTENSION IF NOT EXISTS uuid-ossp;

CREATE TABLE IF NOT EXISTS users (
  user_id       uuid PRIMARY KEY DEFAULT uuid_generate_v1mc(),
  email         text UNIQUE,
  password_hash text,
  username      text,
  avatar_url    text
);

CREATE TABLE IF NOT EXISTS gists (
  gist_id       uuid      PRIMARY KEY DEFAULT uuid_generate_v1mc(),
  user_id       text      REFERENCES users (user_id)
  -- head          text      REFERENCES revisions (revision_id),
  name          text,
  description   text,
  private       boolean   DEFAULT FALSE
);

CREATE TABLE IF NOT EXISTS revisions (
  id            text PRIMARY KEY DEFAULT uuid_generate_v1mc(),
  parent_id     text REFERENCES revisions (id),
  gist_id       text REFERENCES gists (gist_id),
  -- I don't think I need parent_id here since we have the created_at
  -- and can order by the timestamp to know how to display
  -- the revision's files in the correct order
  -- parent_id     text REFERENCES revisions (revision_id),
  created_at    timestamp NOT NULL
);
CREATE UNIQUE INDEX first_revision ON revisions USING btree (id) WHERE (parent_id IS NULL);
CREATE UNIQUE INDEX subsequent_revision ON revisions USING btree (gist_id, parent_id);


CREATE TABLE IF NOT EXISTS files (
  file_id       text PRIMARY KEY DEFAULT uuid_generate_v1mc(),
  gist_id       text REFERENCES gists (gist_id),
  -- Do we still need parent_id here if we know which revision this
  -- file is a part of, and then can diff the content between this
  -- file and the file from the previously created revision ?
  -- parent_id     text,
  filename      text NOT NULL,
  content       text NOT NULL,
  diff          text
);

CREATE TABLE IF NOT EXISTS revision_files (
  revision_id   text REFERENCES revisions (revision_id),
  file_id       text REFERENCES files (file_id),
  PRIMARY KEY (revision_id, file_id),
);

CREATE TABLE IF NOT EXISTS comments (
  comment_id    text PRIMARY KEY DEFAULT uuid_generate_v1mc(),
  gist_id       text REFERENCES gists (gist_id),
  user_id       text REFERENCES users (user_id),
  content       text NOT NULL,
  created_at    timestamp NOT NULL,
  updated_at    timestamp NOT NULL
);

CREATE TABLE IF NOT EXISTS subscriptions (
  gist_id       text REFERENCES gists (uuid),
  user_id       text REFERENCES users (uuid),
  PRIMARY KEY (gist_id, user_id)
);

CREATE TABLE IF NOT EXISTS stars (
  gist_id       text REFERENCES gists (uuid),
  user_id       text REFERENCES users (uuid),
  PRIMARY KEY (gist_id, user_id)
);