archon/migration/agent_work_orders_state.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
-- =====================================================