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,
    description TEXT NOT NULL DEFAULT '',
    meta jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX users_email_idx ON users USING btree (email);
CREATE INDEX users_name_idx ON users USING btree (name);
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,
    default_access access_type DEFAULT 'none',

    slug varchar(128) NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9-]+$'),
    name varchar(128) NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    meta jsonb NOT NULL DEFAULT '{}'::jsonb,

    CONSTRAINT projects_owner FOREIGN KEY (owner) REFERENCES public.users(id) ON DELETE cascade ON UPDATE no action
);
CREATE INDEX projects_owner_idx ON projects USING btree (owner);
CREATE INDEX projects_slug_idx ON projects USING btree (slug);


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
);
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);
ALTER TABLE accesses ADD UNIQUE (project, owner);


CREATE TABLE access_tokens (
    id uuid PRIMARY KEY,
    created_at timestamptz DEFAULT now(),
    project uuid NOT NULL,
    access_type access_type NOT NULL,
    token uuid NOT NULL UNIQUE,

    CONSTRAINT access_tokens_project FOREIGN KEY (project) REFERENCES public.projects(id) ON DELETE cascade ON UPDATE no action
);
CREATE INDEX access_tokens_project_idx ON access_tokens USING btree (project);
CREATE INDEX access_tokens_token_idx ON access_tokens USING btree (token);


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);

CREATE TABLE ssh_keys (
    public_key text PRIMARY KEY,
    user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    hostname text,
    created_at timestamptz DEFAULT now()
);

CREATE INDEX ssh_keys_user_id_idx ON ssh_keys(user_id);