From 87044a747b47bd83249d61a45842c7f7b2eae56d Mon Sep 17 00:00:00 2001
From: soryu <soryu@soryu.co>
Date: Sun, 11 Jan 2026 05:52:14 +0000
Subject:  Contract system

---
 .../migrations/20250110100000_create_contracts.sql | 71 ++++++++++++++++++++++
 1 file changed, 71 insertions(+)
 create mode 100644 makima/migrations/20250110100000_create_contracts.sql

(limited to 'makima/migrations/20250110100000_create_contracts.sql')

diff --git a/makima/migrations/20250110100000_create_contracts.sql b/makima/migrations/20250110100000_create_contracts.sql
new file mode 100644
index 0000000..3532b8e
--- /dev/null
+++ b/makima/migrations/20250110100000_create_contracts.sql
@@ -0,0 +1,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);
-- 
cgit v1.2.3

