Project management

CREATE TYPE task_priority AS ENUM ('low', 'medium', 'high', 'urgent');

CREATE TABLE people (
    id serial PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL
);

CREATE TABLE workspaces (
    id serial PRIMARY KEY,
    name text NOT NULL,
    owner_id integer NOT NULL
);

CREATE TABLE projects (
    id serial PRIMARY KEY,
    workspace_id integer NOT NULL,
    name text NOT NULL,
    archived boolean DEFAULT false NOT NULL
);

CREATE TABLE tasks (
    id bigserial PRIMARY KEY,
    project_id integer NOT NULL,
    parent_task_id bigint,
    assignee_id integer,
    title text NOT NULL,
    description text,
    priority task_priority DEFAULT 'medium' NOT NULL,
    due_date date,
    completed_at timestamptz
);
CREATE INDEX tasks_project_idx ON tasks (project_id);
CREATE INDEX tasks_assignee_idx ON tasks (assignee_id) WHERE completed_at is null;

CREATE TABLE labels (
    id serial PRIMARY KEY,
    project_id integer NOT NULL,
    name text NOT NULL,
    color char(7) DEFAULT '#6366f1' NOT NULL
);

CREATE TABLE task_labels (
    task_id bigint NOT NULL,
    label_id integer NOT NULL,
    PRIMARY KEY (task_id, label_id)
);

CREATE TABLE task_comments (
    id bigserial PRIMARY KEY,
    task_id bigint NOT NULL,
    author_id integer NOT NULL,
    body text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE time_entries (
    id bigserial PRIMARY KEY,
    task_id bigint NOT NULL,
    person_id integer NOT NULL,
    started_at timestamptz NOT NULL,
    minutes integer NOT NULL,
    CHECK (minutes > 0)
);

ALTER TABLE workspaces ADD FOREIGN KEY (owner_id) REFERENCES people (id);

ALTER TABLE projects ADD FOREIGN KEY (workspace_id) REFERENCES workspaces (id) ON DELETE CASCADE;

ALTER TABLE tasks ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

ALTER TABLE tasks ADD FOREIGN KEY (parent_task_id) REFERENCES tasks (id) ON DELETE CASCADE;

ALTER TABLE tasks ADD FOREIGN KEY (assignee_id) REFERENCES people (id) ON DELETE SET NULL;

ALTER TABLE labels ADD FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE;

ALTER TABLE task_labels ADD FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE;

ALTER TABLE task_labels ADD FOREIGN KEY (label_id) REFERENCES labels (id) ON DELETE CASCADE;

ALTER TABLE task_comments ADD FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE;

ALTER TABLE task_comments ADD FOREIGN KEY (author_id) REFERENCES people (id);

ALTER TABLE time_entries ADD FOREIGN KEY (task_id) REFERENCES tasks (id) ON DELETE CASCADE;

ALTER TABLE time_entries ADD FOREIGN KEY (person_id) REFERENCES people (id);