You've already forked agentic-coding-workflow
63 lines
2.5 KiB
SQL
63 lines
2.5 KiB
SQL
-- ============ Workspaces ============
|
|
CREATE TABLE workspaces (
|
|
id UUID PRIMARY KEY,
|
|
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
slug TEXT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
git_remote_url TEXT NOT NULL,
|
|
default_branch TEXT NOT NULL DEFAULT 'main',
|
|
main_path TEXT NOT NULL,
|
|
sync_status TEXT NOT NULL DEFAULT 'cloning',
|
|
last_synced_at TIMESTAMPTZ,
|
|
last_sync_error TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT workspaces_sync_status_check CHECK (sync_status IN ('cloning','idle','syncing','error')),
|
|
UNIQUE (project_id, slug),
|
|
UNIQUE (project_id, id)
|
|
);
|
|
CREATE INDEX idx_workspaces_project ON workspaces(project_id);
|
|
|
|
-- ============ Worktrees ============
|
|
CREATE TABLE workspace_worktrees (
|
|
id UUID PRIMARY KEY,
|
|
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
branch TEXT NOT NULL,
|
|
path TEXT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'idle',
|
|
active_holder TEXT,
|
|
acquired_at TIMESTAMPTZ,
|
|
last_used_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT worktrees_status_check CHECK (status IN ('idle','active','pruning')),
|
|
UNIQUE (workspace_id, branch),
|
|
UNIQUE (workspace_id, path)
|
|
);
|
|
CREATE INDEX idx_worktrees_ws_status ON workspace_worktrees(workspace_id, status, last_used_at);
|
|
|
|
-- ============ Git credentials ============
|
|
CREATE TABLE git_credentials (
|
|
id UUID PRIMARY KEY,
|
|
workspace_id UUID NOT NULL UNIQUE REFERENCES workspaces(id) ON DELETE CASCADE,
|
|
kind TEXT NOT NULL,
|
|
username TEXT,
|
|
encrypted_secret BYTEA,
|
|
fingerprint TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT git_credentials_kind_check CHECK (kind IN ('pat','ssh_key','none'))
|
|
);
|
|
|
|
-- ============ ALTER existing PM tables ============
|
|
ALTER TABLE issues ADD COLUMN workspace_id UUID NULL;
|
|
ALTER TABLE issues ADD CONSTRAINT issues_project_workspace_fk
|
|
FOREIGN KEY (project_id, workspace_id)
|
|
REFERENCES workspaces (project_id, id) MATCH SIMPLE
|
|
ON DELETE SET NULL;
|
|
CREATE INDEX idx_issues_workspace_status ON issues(workspace_id, status);
|
|
|
|
ALTER TABLE requirements ADD COLUMN workspace_id UUID NULL
|
|
REFERENCES workspaces(id) ON DELETE SET NULL;
|
|
CREATE INDEX idx_requirements_workspace ON requirements(workspace_id);
|