Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Data modeling for gist clone service
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)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.