Ben / RubHub

CREATE TYPE public.access_type AS ENUM('none', 'read', 'write', 'admin');
CREATE TYPE public.user_type AS ENUM('normal', 'temporary');

CREATE TABLE users (
    id uuid PRIMARY KEY,
    created_at timestamptz DEFAULT now(),
    last_login timestamptz DEFAULT now(),
    user_type user_type NOT NULL,
    email varchar(255) NOT NULL,
    name varchar(128) NOT NULL,
    password_hash TEXT NOT NULL
);
CREATE INDEX users_last_login_idx ON users USING btree (last_login);
CREATE INDEX users_user_type_idx ON users USING btree (user_type);
CREATE UNIQUE INDEX users_email_lower_idx ON users (lower(email));
CREATE UNIQUE INDEX users_name_lower_idx ON users (lower(name));


CREATE TABLE sessions (
    id uuid PRIMARY KEY,
    expires_at timestamptz DEFAULT now() + interval '1 month',
    owner uuid NOT NULL,
    CONSTRAINT sessions_user FOREIGN KEY (owner) REFERENCES public.users(id) ON DELETE cascade ON UPDATE no action
);
CREATE INDEX sessions_owner_idx ON sessions USING btree (owner);
CREATE INDEX sessions_expires_at_idx ON sessions USING btree (expires_at);


CREATE TABLE projects (
    id uuid PRIMARY KEY,
    created_at timestamptz DEFAULT now(),
    owner uuid NOT NULL,
    public_access access_type NOT NULL DEFAULT 'none',

    slug varchar(128) NOT NULL,
    name varchar(128) NOT NULL,
    description TEXT NOT NULL DEFAULT '',

    CONSTRAINT projects_owner FOREIGN KEY (owner) REFERENCES public.users(id) ON DELETE cascade ON UPDATE no action,
    CONSTRAINT projects_owner_slug_unique UNIQUE (owner, slug),
    CONSTRAINT projects_slug_format CHECK (slug ~ '^[a-z0-9_.-]+$')
);
CREATE INDEX projects_owner_idx ON projects USING btree (owner);


CREATE TABLE accesses (
    id uuid PRIMARY KEY,
    created_at timestamptz DEFAULT now(),
    project uuid NOT NULL,
    owner uuid NOT NULL,
    access_type access_type NOT NULL,

    CONSTRAINT access_user FOREIGN KEY (owner) REFERENCES public.users(id) ON DELETE cascade ON UPDATE no action,
    CONSTRAINT access_project FOREIGN KEY (project) REFERENCES public.projects(id) ON DELETE cascade ON UPDATE no action,
    UNIQUE (project, owner)
);
CREATE INDEX accesses_project_idx ON accesses USING btree (project);
CREATE INDEX accesses_owner_idx ON accesses USING btree (owner);
CREATE INDEX accesses_project_owner_idx ON accesses (project, owner);


CREATE TABLE ssh_keys (
    public_key text PRIMARY KEY,
    user_id uuid NOT NULL,
    hostname text,
    created_at timestamptz DEFAULT now(),

    CONSTRAINT ssh_keys_user FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE cascade ON UPDATE no action
);
CREATE INDEX ssh_keys_user_id_idx ON ssh_keys(user_id);


CREATE TABLE project_messages (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at timestamptz DEFAULT now(),
    project uuid NOT NULL,

    owner uuid NOT NULL,
    to_path varchar(255) NOT NULL DEFAULT '/',
    body TEXT NOT NULL,

    CONSTRAINT project_messages_owner FOREIGN KEY (owner) REFERENCES public.users(id) ON DELETE cascade ON UPDATE no action,
    CONSTRAINT project_messages_project FOREIGN KEY (project) REFERENCES public.projects(id) ON DELETE cascade ON UPDATE no action
);
CREATE INDEX project_messages_project_idx ON project_messages USING btree (project);
CREATE INDEX project_messages_project_created_at_idx ON project_messages (project, created_at);
CREATE INDEX project_messages_project_id_idx ON project_messages (project, id);