1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
|
-- Contracts table (hierarchical - contracts can contain sub-contracts)
CREATE TABLE IF NOT EXISTS contracts (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
owner_id UUID NOT NULL REFERENCES owners(id) ON DELETE CASCADE,
parent_contract_id UUID REFERENCES contracts(id) ON DELETE CASCADE, -- NULL = root contract
name VARCHAR(255) NOT NULL,
description TEXT,
phase VARCHAR(32) NOT NULL DEFAULT 'research', -- research/specify/plan/execute/review
status VARCHAR(32) NOT NULL DEFAULT 'active', -- active/completed/archived
version INTEGER NOT NULL DEFAULT 1,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_contracts_owner_id ON contracts(owner_id);
CREATE INDEX idx_contracts_parent_contract_id ON contracts(parent_contract_id);
CREATE INDEX idx_contracts_status ON contracts(status);
CREATE INDEX idx_contracts_phase ON contracts(phase);
-- Contract repositories (1-to-many: contract has multiple repos)
-- Supports: existing remote (GitHub, etc), existing local, or new repos created by daemon
CREATE TABLE IF NOT EXISTS contract_repositories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
name VARCHAR(255) NOT NULL, -- display name / repo name for creation
repository_url VARCHAR(512), -- NULL until created (for new repos)
local_path VARCHAR(512), -- local filesystem path (for local repos)
source_type VARCHAR(32) NOT NULL DEFAULT 'remote', -- remote/local/managed
status VARCHAR(32) NOT NULL DEFAULT 'ready', -- ready/pending/creating/failed
is_primary BOOLEAN NOT NULL DEFAULT false, -- primary repo for task defaults
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- source_type values:
-- 'remote' = existing remote repo (GitHub, GitLab, etc) - has repository_url
-- 'local' = existing local repo - has local_path
-- 'managed' = new repo created/managed by Makima daemon - gets repository_url after creation
-- status values:
-- 'ready' = repo is usable (existing repos start here)
-- 'pending' = waiting for daemon to create
-- 'creating' = daemon is creating the repo
-- 'failed' = creation failed
CREATE INDEX idx_contract_repositories_contract_id ON contract_repositories(contract_id);
CREATE INDEX idx_contract_repositories_status ON contract_repositories(status);
-- Only one primary per contract
CREATE UNIQUE INDEX idx_contract_repositories_primary ON contract_repositories(contract_id) WHERE is_primary = true;
-- Add contract_id to files table (one-to-one)
ALTER TABLE files ADD COLUMN IF NOT EXISTS contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL;
ALTER TABLE files ADD COLUMN IF NOT EXISTS contract_phase VARCHAR(32);
CREATE INDEX IF NOT EXISTS idx_files_contract_id ON files(contract_id);
-- Add contract_id to tasks table (already nullable for backward compat)
ALTER TABLE tasks ADD COLUMN IF NOT EXISTS contract_id UUID REFERENCES contracts(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS idx_tasks_contract_id ON tasks(contract_id);
-- Contract events for audit trail
CREATE TABLE IF NOT EXISTS contract_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
contract_id UUID NOT NULL REFERENCES contracts(id) ON DELETE CASCADE,
event_type VARCHAR(64) NOT NULL, -- phase_change, file_added, task_added, status_change
previous_phase VARCHAR(32),
new_phase VARCHAR(32),
event_data JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_contract_events_contract_id ON contract_events(contract_id);
|
