schema.sql

-- =============================================================
-- Order Supervisor — Database Schema
-- Run this in Supabase SQL Editor
-- =============================================================

-- Supervisor templates (configs)
CREATE TABLE IF NOT EXISTS supervisors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    base_instruction TEXT NOT NULL,
    wake_aggressiveness TEXT DEFAULT 'normal' CHECK (wake_aggressiveness IN ('aggressive', 'normal', 'conservative')),
    available_actions TEXT[] DEFAULT ARRAY[
        'message_customer',
        'message_fulfillment_team',
        'message_payments_team',
        'message_logistics_team',
        'create_internal_note'
    ],
    model_preference TEXT DEFAULT 'groq' CHECK (model_preference IN ('groq', 'gemini')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- One row per order run
CREATE TABLE IF NOT EXISTS runs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    supervisor_id UUID REFERENCES supervisors(id) ON DELETE SET NULL,
    order_id TEXT NOT NULL,
    order_context JSONB DEFAULT '{}',
    status TEXT DEFAULT 'active' CHECK (status IN ('active', 'sleeping', 'interrupted', 'completed', 'terminated')),
    memory_summary TEXT DEFAULT '',
    next_wakeup TIMESTAMPTZ,
    is_interrupted BOOLEAN DEFAULT FALSE,
    final_summary JSONB,
    temporal_workflow_id TEXT UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Universal event and action log
-- type values: event | action | sleep | wakeup | instruction | reasoning | final_output
CREATE TABLE IF NOT EXISTS activity_log (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    run_id UUID REFERENCES runs(id) ON DELETE CASCADE,
    type TEXT NOT NULL CHECK (type IN ('event', 'action', 'sleep', 'wakeup', 'instruction', 'reasoning', 'final_output')),
    payload JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_runs_status ON runs(status);
CREATE INDEX IF NOT EXISTS idx_runs_created_at ON runs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_activity_log_run_id ON activity_log(run_id);
CREATE INDEX IF NOT EXISTS idx_activity_log_created_at ON activity_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_activity_log_type ON activity_log(type);

-- Auto-update updated_at on runs
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_runs_updated_at
    BEFORE UPDATE ON runs
    FOR EACH ROW
    EXECUTE PROCEDURE update_updated_at_column();

-- =============================================================
-- Seed data: Default supervisor templates
-- =============================================================

INSERT INTO supervisors (name, base_instruction, wake_aggressiveness, model_preference)
VALUES
(
    'Standard Order Supervisor',
    'You are supervising a standard e-commerce order. Monitor for payment issues, shipment delays, and customer complaints. Be proactive but not intrusive. Only contact the customer if there is a real problem. Prefer internal notes for routine observations.',
    'normal',
    'groq'
),
(
    'High-Priority Supervisor',
    'You are supervising a high-value or time-sensitive order. Act quickly on any issues. Escalate payment problems immediately. For shipment delays over 4 hours, alert logistics team. Keep the customer informed at every step.',
    'aggressive',
    'groq'
),
(
    'Minimal Intervention Supervisor',
    'You are supervising a routine order. Only intervene on critical failures like payment_failed or refund_requested. Let standard events (shipment, delivery) proceed without action. Minimize interruptions to all teams.',
    'conservative',
    'groq'
);