You've already forked agentic-coding-workflow
58 lines
2.6 KiB
SQL
58 lines
2.6 KiB
SQL
-- ============ Project ============
|
|
CREATE TABLE projects (
|
|
id UUID PRIMARY KEY,
|
|
slug TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
visibility TEXT NOT NULL DEFAULT 'private',
|
|
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
archived_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT projects_visibility_check CHECK (visibility IN ('private','internal'))
|
|
);
|
|
CREATE INDEX idx_projects_owner ON projects(owner_id);
|
|
CREATE INDEX idx_projects_archived ON projects(archived_at) WHERE archived_at IS NULL;
|
|
|
|
-- ============ Requirement ============
|
|
CREATE TABLE requirements (
|
|
id UUID PRIMARY KEY,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
number INT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
phase TEXT NOT NULL DEFAULT 'planning',
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
closed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (project_id, number),
|
|
CONSTRAINT requirements_phase_check CHECK (phase IN ('planning','auditing','implementing','reviewing','done')),
|
|
CONSTRAINT requirements_status_check CHECK (status IN ('open','closed'))
|
|
);
|
|
CREATE INDEX idx_requirements_project_phase ON requirements(project_id, phase);
|
|
CREATE INDEX idx_requirements_project_status ON requirements(project_id, status);
|
|
CREATE INDEX idx_requirements_owner ON requirements(owner_id);
|
|
|
|
-- ============ Issue ============
|
|
CREATE TABLE issues (
|
|
id UUID PRIMARY KEY,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
requirement_id UUID REFERENCES requirements(id) ON DELETE SET NULL,
|
|
number INT NOT NULL,
|
|
title TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status TEXT NOT NULL DEFAULT 'open',
|
|
assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
created_by UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
closed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (project_id, number),
|
|
CONSTRAINT issues_status_check CHECK (status IN ('open','closed'))
|
|
);
|
|
CREATE INDEX idx_issues_requirement ON issues(requirement_id) WHERE requirement_id IS NOT NULL;
|
|
CREATE INDEX idx_issues_project_status ON issues(project_id, status);
|
|
CREATE INDEX idx_issues_assignee ON issues(assignee_id);
|