mirror of
https://github.com/coleam00/Archon.git
synced 2025-12-23 18:29:18 -05:00
357 lines
13 KiB
SQL
357 lines
13 KiB
SQL
-- =====================================================
|
|
-- Agent Work Orders - State Management
|
|
-- =====================================================
|
|
-- This migration creates tables for agent work order state persistence
|
|
-- in PostgreSQL, replacing file-based JSON storage with ACID-compliant
|
|
-- database backend.
|
|
--
|
|
-- Features:
|
|
-- - Atomic state updates with ACID guarantees
|
|
-- - Row-level locking for concurrent access control
|
|
-- - Foreign key constraints for referential integrity
|
|
-- - Indexes for fast queries by status, repository, and timestamp
|
|
-- - JSONB metadata for flexible storage
|
|
-- - Automatic timestamp management via triggers
|
|
-- - Step execution history with ordering
|
|
--
|
|
-- Run this in your Supabase SQL Editor
|
|
-- =====================================================
|
|
|
|
-- =====================================================
|
|
-- SECTION 1: CREATE TABLES
|
|
-- =====================================================
|
|
|
|
-- Create archon_agent_work_orders table
|
|
CREATE TABLE IF NOT EXISTS archon_agent_work_orders (
|
|
-- Primary identification (TEXT not UUID since generated by id_generator.py)
|
|
agent_work_order_id TEXT PRIMARY KEY,
|
|
|
|
-- Core state fields (frequently queried as separate columns)
|
|
repository_url TEXT NOT NULL,
|
|
sandbox_identifier TEXT NOT NULL,
|
|
git_branch_name TEXT,
|
|
agent_session_id TEXT,
|
|
status TEXT NOT NULL CHECK (status IN ('pending', 'running', 'completed', 'failed')),
|
|
|
|
-- Flexible metadata (JSONB for infrequently queried fields)
|
|
-- Stores: sandbox_type, github_issue_number, current_phase, error_message, etc.
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
|
|
|
-- Timestamps (automatically managed)
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Create archon_agent_work_order_steps table
|
|
-- Stores step execution history with foreign key to work orders
|
|
CREATE TABLE IF NOT EXISTS archon_agent_work_order_steps (
|
|
-- Primary identification
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
-- Foreign key to work order (CASCADE delete when work order deleted)
|
|
agent_work_order_id TEXT NOT NULL REFERENCES archon_agent_work_orders(agent_work_order_id) ON DELETE CASCADE,
|
|
|
|
-- Step execution details
|
|
step TEXT NOT NULL, -- WorkflowStep enum value (e.g., "create-branch", "planning")
|
|
agent_name TEXT NOT NULL, -- Name of agent that executed step
|
|
success BOOLEAN NOT NULL, -- Whether step succeeded
|
|
output TEXT, -- Step output (nullable)
|
|
error_message TEXT, -- Error message if failed (nullable)
|
|
duration_seconds FLOAT NOT NULL, -- Execution duration
|
|
session_id TEXT, -- Agent session ID (nullable)
|
|
executed_at TIMESTAMP WITH TIME ZONE NOT NULL, -- When step was executed
|
|
step_order INT NOT NULL -- Order within work order (0-indexed for sorting)
|
|
);
|
|
|
|
-- =====================================================
|
|
-- SECTION 2: CREATE INDEXES
|
|
-- =====================================================
|
|
|
|
-- Indexes on archon_agent_work_orders for common queries
|
|
|
|
-- Index on status for filtering by work order status
|
|
CREATE INDEX IF NOT EXISTS idx_agent_work_orders_status
|
|
ON archon_agent_work_orders(status);
|
|
|
|
-- Index on created_at for ordering by most recent
|
|
CREATE INDEX IF NOT EXISTS idx_agent_work_orders_created_at
|
|
ON archon_agent_work_orders(created_at DESC);
|
|
|
|
-- Index on repository_url for filtering by repository
|
|
CREATE INDEX IF NOT EXISTS idx_agent_work_orders_repository
|
|
ON archon_agent_work_orders(repository_url);
|
|
|
|
-- GIN index on metadata JSONB for flexible queries
|
|
CREATE INDEX IF NOT EXISTS idx_agent_work_orders_metadata
|
|
ON archon_agent_work_orders USING GIN(metadata);
|
|
|
|
-- Indexes on archon_agent_work_order_steps for step history queries
|
|
|
|
-- Index on agent_work_order_id for retrieving all steps for a work order
|
|
CREATE INDEX IF NOT EXISTS idx_agent_work_order_steps_work_order_id
|
|
ON archon_agent_work_order_steps(agent_work_order_id);
|
|
|
|
-- Index on executed_at for temporal queries
|
|
CREATE INDEX IF NOT EXISTS idx_agent_work_order_steps_executed_at
|
|
ON archon_agent_work_order_steps(executed_at);
|
|
|
|
-- =====================================================
|
|
-- SECTION 3: CREATE TRIGGER
|
|
-- =====================================================
|
|
|
|
-- Apply auto-update trigger for updated_at timestamp
|
|
-- Reuses existing update_updated_at_column() function from Archon migrations
|
|
CREATE TRIGGER update_agent_work_orders_updated_at
|
|
BEFORE UPDATE ON archon_agent_work_orders
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- =====================================================
|
|
-- SECTION 4: ROW LEVEL SECURITY
|
|
-- =====================================================
|
|
|
|
-- Enable Row Level Security on both tables
|
|
ALTER TABLE archon_agent_work_orders ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE archon_agent_work_order_steps ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Policy 1: Service role has full access (for API operations)
|
|
CREATE POLICY "Allow service role full access to archon_agent_work_orders"
|
|
ON archon_agent_work_orders
|
|
FOR ALL
|
|
USING (auth.role() = 'service_role');
|
|
|
|
CREATE POLICY "Allow service role full access to archon_agent_work_order_steps"
|
|
ON archon_agent_work_order_steps
|
|
FOR ALL
|
|
USING (auth.role() = 'service_role');
|
|
|
|
-- Policy 2: Authenticated users can read and update (for frontend operations)
|
|
CREATE POLICY "Allow authenticated users to read and update archon_agent_work_orders"
|
|
ON archon_agent_work_orders
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (true);
|
|
|
|
CREATE POLICY "Allow authenticated users to read and update archon_agent_work_order_steps"
|
|
ON archon_agent_work_order_steps
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (true);
|
|
|
|
-- =====================================================
|
|
-- SECTION 5: TABLE COMMENTS
|
|
-- =====================================================
|
|
|
|
-- Comments on archon_agent_work_orders table
|
|
COMMENT ON TABLE archon_agent_work_orders IS
|
|
'Stores agent work order state and metadata with ACID guarantees for concurrent access';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.agent_work_order_id IS
|
|
'Unique work order identifier (TEXT format generated by id_generator.py)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.repository_url IS
|
|
'GitHub repository URL for the work order';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.sandbox_identifier IS
|
|
'Unique identifier for sandbox environment (worktree directory name)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.git_branch_name IS
|
|
'Git branch name created for work order (nullable if not yet created)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.agent_session_id IS
|
|
'Agent session ID for tracking agent execution (nullable if not yet started)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.status IS
|
|
'Current status: pending, running, completed, or failed';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.metadata IS
|
|
'JSONB metadata including sandbox_type, github_issue_number, current_phase, error_message, etc.';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.created_at IS
|
|
'Timestamp when work order was created';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_orders.updated_at IS
|
|
'Timestamp when work order was last updated (auto-managed by trigger)';
|
|
|
|
-- Comments on archon_agent_work_order_steps table
|
|
COMMENT ON TABLE archon_agent_work_order_steps IS
|
|
'Stores step execution history for agent work orders with foreign key constraints';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.id IS
|
|
'Unique UUID identifier for step record';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.agent_work_order_id IS
|
|
'Foreign key to work order (CASCADE delete on work order deletion)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.step IS
|
|
'WorkflowStep enum value (e.g., "create-branch", "planning", "execute")';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.agent_name IS
|
|
'Name of agent that executed the step';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.success IS
|
|
'Boolean indicating if step execution succeeded';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.output IS
|
|
'Step execution output (nullable)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.error_message IS
|
|
'Error message if step failed (nullable)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.duration_seconds IS
|
|
'Step execution duration in seconds';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.session_id IS
|
|
'Agent session ID for tracking (nullable)';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.executed_at IS
|
|
'Timestamp when step was executed';
|
|
|
|
COMMENT ON COLUMN archon_agent_work_order_steps.step_order IS
|
|
'Order of step within work order (0-indexed for sorting)';
|
|
|
|
-- =====================================================
|
|
-- SECTION 6: VERIFICATION
|
|
-- =====================================================
|
|
|
|
-- Verify archon_agent_work_orders table creation
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'archon_agent_work_orders'
|
|
) THEN
|
|
RAISE NOTICE '✓ Table archon_agent_work_orders created successfully';
|
|
ELSE
|
|
RAISE EXCEPTION '✗ Table archon_agent_work_orders was not created';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify archon_agent_work_order_steps table creation
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name = 'archon_agent_work_order_steps'
|
|
) THEN
|
|
RAISE NOTICE '✓ Table archon_agent_work_order_steps created successfully';
|
|
ELSE
|
|
RAISE EXCEPTION '✗ Table archon_agent_work_order_steps was not created';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify indexes on archon_agent_work_orders
|
|
DO $$
|
|
BEGIN
|
|
IF (
|
|
SELECT COUNT(*) FROM pg_indexes
|
|
WHERE tablename = 'archon_agent_work_orders'
|
|
) >= 4 THEN
|
|
RAISE NOTICE '✓ Indexes on archon_agent_work_orders created successfully';
|
|
ELSE
|
|
RAISE WARNING '⚠ Expected at least 4 indexes on archon_agent_work_orders, found fewer';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify indexes on archon_agent_work_order_steps
|
|
DO $$
|
|
BEGIN
|
|
IF (
|
|
SELECT COUNT(*) FROM pg_indexes
|
|
WHERE tablename = 'archon_agent_work_order_steps'
|
|
) >= 2 THEN
|
|
RAISE NOTICE '✓ Indexes on archon_agent_work_order_steps created successfully';
|
|
ELSE
|
|
RAISE WARNING '⚠ Expected at least 2 indexes on archon_agent_work_order_steps, found fewer';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify trigger
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM pg_trigger
|
|
WHERE tgrelid = 'archon_agent_work_orders'::regclass
|
|
AND tgname = 'update_agent_work_orders_updated_at'
|
|
) THEN
|
|
RAISE NOTICE '✓ Trigger update_agent_work_orders_updated_at created successfully';
|
|
ELSE
|
|
RAISE EXCEPTION '✗ Trigger update_agent_work_orders_updated_at was not created';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify RLS policies on archon_agent_work_orders
|
|
DO $$
|
|
BEGIN
|
|
IF (
|
|
SELECT COUNT(*) FROM pg_policies
|
|
WHERE tablename = 'archon_agent_work_orders'
|
|
) >= 2 THEN
|
|
RAISE NOTICE '✓ RLS policies on archon_agent_work_orders created successfully';
|
|
ELSE
|
|
RAISE WARNING '⚠ Expected at least 2 RLS policies on archon_agent_work_orders, found fewer';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify RLS policies on archon_agent_work_order_steps
|
|
DO $$
|
|
BEGIN
|
|
IF (
|
|
SELECT COUNT(*) FROM pg_policies
|
|
WHERE tablename = 'archon_agent_work_order_steps'
|
|
) >= 2 THEN
|
|
RAISE NOTICE '✓ RLS policies on archon_agent_work_order_steps created successfully';
|
|
ELSE
|
|
RAISE WARNING '⚠ Expected at least 2 RLS policies on archon_agent_work_order_steps, found fewer';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- Verify foreign key constraint
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM information_schema.table_constraints
|
|
WHERE table_name = 'archon_agent_work_order_steps'
|
|
AND constraint_type = 'FOREIGN KEY'
|
|
) THEN
|
|
RAISE NOTICE '✓ Foreign key constraint on archon_agent_work_order_steps created successfully';
|
|
ELSE
|
|
RAISE EXCEPTION '✗ Foreign key constraint on archon_agent_work_order_steps was not created';
|
|
END IF;
|
|
END $$;
|
|
|
|
-- =====================================================
|
|
-- SECTION 7: ROLLBACK INSTRUCTIONS
|
|
-- =====================================================
|
|
|
|
/*
|
|
To rollback this migration, run the following commands:
|
|
|
|
-- Drop tables (CASCADE will also drop indexes, triggers, and policies)
|
|
DROP TABLE IF EXISTS archon_agent_work_order_steps CASCADE;
|
|
DROP TABLE IF EXISTS archon_agent_work_orders CASCADE;
|
|
|
|
-- Verify tables are dropped
|
|
SELECT table_name FROM information_schema.tables
|
|
WHERE table_schema = 'public'
|
|
AND table_name LIKE 'archon_agent_work_order%';
|
|
-- Should return 0 rows
|
|
|
|
-- Note: The update_updated_at_column() function is shared and should NOT be dropped
|
|
*/
|
|
|
|
-- =====================================================
|
|
-- MIGRATION COMPLETE
|
|
-- =====================================================
|
|
-- The archon_agent_work_orders and archon_agent_work_order_steps tables
|
|
-- are now ready for use.
|
|
--
|
|
-- Next steps:
|
|
-- 1. Set STATE_STORAGE_TYPE=supabase in environment
|
|
-- 2. Restart Agent Work Orders service
|
|
-- 3. Verify health endpoint shows database status healthy
|
|
-- 4. Test work order creation via API
|
|
-- =====================================================
|