-- Migration: Workflow Engine v5.3 — Enterprise Features
-- New: event journal, waitpoint tokens, flow control, guest approval, 20+ templates
-- Date: 2026-02-14
-- ============================================================================
-- 1. EVENT JOURNAL — append-only state transition log for time-travel debugging
-- ============================================================================
CREATE TABLE IF NOT EXISTS workflow_events (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
run_id UUID NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
step_run_id UUID REFERENCES workflow_step_runs(id) ON DELETE SET NULL,
event_type TEXT NOT NULL,
payload JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE workflow_events ENABLE ROW LEVEL SECURITY;
CREATE POLICY events_service_role ON workflow_events FOR ALL USING (true) WITH CHECK (true);
CREATE INDEX IF NOT EXISTS idx_workflow_events_run ON workflow_events(run_id, created_at);
CREATE INDEX IF NOT EXISTS idx_workflow_events_type ON workflow_events(event_type, created_at);
-- pg_notify trigger for real-time event streaming
CREATE OR REPLACE FUNCTION notify_workflow_event() RETURNS trigger AS $$
BEGIN
PERFORM pg_notify('workflow_event', json_build_object(
'id', NEW.id, 'run_id', NEW.run_id, 'event_type', NEW.event_type,
'step_run_id', NEW.step_run_id, 'payload', NEW.payload
)::TEXT);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS trg_workflow_event ON workflow_events;
CREATE TRIGGER trg_workflow_event AFTER INSERT ON workflow_events
FOR EACH ROW EXECUTE FUNCTION notify_workflow_event();
-- ============================================================================
-- 2. WAITPOINT TOKENS — generalized wait-for-external-signal primitive
-- ============================================================================
CREATE TABLE IF NOT EXISTS waitpoint_tokens (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
token UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
run_id UUID NOT NULL REFERENCES workflow_runs(id) ON DELETE CASCADE,
step_run_id UUID NOT NULL REFERENCES workflow_step_runs(id) ON DELETE CASCADE,
store_id UUID NOT NULL,
label TEXT,
status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'completed', 'expired', 'cancelled')),
completion_data JSONB DEFAULT '{}',
completed_at TIMESTAMPTZ,
expires_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE waitpoint_tokens ENABLE ROW LEVEL SECURITY;
CREATE POLICY waitpoints_service_role ON waitpoint_tokens FOR ALL USING (true) WITH CHECK (true);
CREATE INDEX IF NOT EXISTS idx_waitpoint_token ON waitpoint_tokens(token);
CREATE INDEX IF NOT EXISTS idx_waitpoint_run ON waitpoint_tokens(run_id);
CREATE INDEX IF NOT EXISTS idx_waitpoint_status ON waitpoint_tokens(status, expires_at) WHERE status = 'pending';
CREATE INDEX IF NOT EXISTS idx_waitpoint_store ON waitpoint_tokens(store_id);
-- Expire pending waitpoints (called by workflow worker)
CREATE OR REPLACE FUNCTION expire_pending_waitpoints() RETURNS void AS $$
BEGIN
UPDATE waitpoint_tokens
SET status = 'expired'
WHERE status = 'pending' AND expires_at < now();
-- Also fail the waiting step runs
UPDATE workflow_step_runs
SET status = 'failed', error_message = 'Waitpoint expired',
completed_at = now()
WHERE id IN (
SELECT step_run_id FROM waitpoint_tokens
WHERE status = 'expired' AND step_run_id IS NOT NULL
) AND status = 'waiting';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================================================
-- 3. FLOW CONTROL COLUMNS on workflow_steps (for step-level rate limiting)
-- ============================================================================
ALTER TABLE workflow_steps ADD COLUMN IF NOT EXISTS concurrency_limit INTEGER;
ALTER TABLE workflow_steps ADD COLUMN IF NOT EXISTS concurrency_key TEXT;
ALTER TABLE workflow_steps ADD COLUMN IF NOT EXISTS rate_limit INTEGER;
ALTER TABLE workflow_steps ADD COLUMN IF NOT EXISTS rate_window_seconds INTEGER DEFAULT 60;
ALTER TABLE workflow_steps ADD COLUMN IF NOT EXISTS priority INTEGER DEFAULT 5;
-- ============================================================================
-- 4. PRIORITY COLUMN on workflow_runs (for global priority ordering)
-- ============================================================================
ALTER TABLE workflow_runs ADD COLUMN IF NOT EXISTS priority INTEGER DEFAULT 5;
-- Update claim_pending_steps to respect priority
CREATE OR REPLACE FUNCTION claim_pending_steps(p_batch_size INTEGER DEFAULT 10)
RETURNS SETOF JSONB AS $$
DECLARE
v_step RECORD;
v_result JSONB;
BEGIN
FOR v_step IN
SELECT sr.id as step_run_id, sr.run_id, sr.step_id, sr.step_key, sr.step_type,
sr.input, sr.attempt_count, sr.parent_step_run_id,
w.id as workflow_id, w.store_id, w.max_steps_per_run,
ws.step_config, ws.on_success, ws.on_failure, ws.timeout_seconds,
ws.input_schema, ws.max_retries, ws.retry_delay_seconds,
ws.concurrency_limit, ws.concurrency_key, ws.rate_limit, ws.rate_window_seconds,
r.step_outputs, r.trigger_payload, r.priority as run_priority
FROM workflow_step_runs sr
JOIN workflow_runs r ON r.id = sr.run_id
JOIN workflows w ON w.id = r.workflow_id
JOIN workflow_steps ws ON ws.id = sr.step_id
WHERE sr.status = 'pending'
AND r.status = 'running'
AND w.is_active = true
ORDER BY COALESCE(r.priority, 5) ASC, sr.created_at ASC
LIMIT p_batch_size
FOR UPDATE OF sr SKIP LOCKED
LOOP
UPDATE workflow_step_runs SET status = 'running', started_at = now()
WHERE id = v_step.step_run_id;
v_result := jsonb_build_object(
'step_run_id', v_step.step_run_id, 'run_id', v_step.run_id,
'workflow_id', v_step.workflow_id, 'store_id', v_step.store_id,
'step_id', v_step.step_id, 'step_key', v_step.step_key,
'step_type', v_step.step_type, 'step_config', COALESCE(v_step.step_config, '{}'),
'on_success', v_step.on_success, 'on_failure', v_step.on_failure,
'timeout_seconds', COALESCE(v_step.timeout_seconds, 60),
'input_schema', v_step.input_schema,
'step_outputs', COALESCE(v_step.step_outputs, '{}'),
'trigger_payload', COALESCE(v_step.trigger_payload, '{}'),
'attempt_count', v_step.attempt_count,
'max_attempts', COALESCE(v_step.max_retries, 3) + 1,
'max_steps_per_run', COALESCE(v_step.max_steps_per_run, 50),
'input', v_step.input,
'parent_step_run_id', v_step.parent_step_run_id,
'retry_delay_seconds', COALESCE(v_step.retry_delay_seconds, 10)
);
RETURN NEXT v_result;
END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- ============================================================================
-- 5. SEED TEMPLATES — 20+ pre-built workflow templates
-- ============================================================================
-- Helper function to create templates (idempotent — skips if exists)
DO $$
DECLARE
v_wf_id UUID;
BEGIN
-- ========================
-- Template 1: Low Stock Alert
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Low Stock Alert' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Low Stock Alert', 'Monitors inventory velocity and sends alerts when stock drops below threshold', 'exclamationmark.triangle', 'active', true, 'schedule', '{"interval_minutes": 60}', true, 'inventory', ARRAY['inventory','alerts','stock'], 1, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'check_velocity', 'tool', true, 'evaluate', '{"tool_name":"inventory","args_template":{"action":"velocity","days":7}}', 0),
(v_wf_id, 'evaluate', 'condition', false, 'alert', '{"expression":"steps.check_velocity.output.some(p => p.days_of_stock < 7)","on_true":"alert","on_false":"done"}', 100),
(v_wf_id, 'alert', 'tool', false, 'done', '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.alert_email}}","subject":"Low Stock Alert","html":"<h2>Low Stock Items</h2><p>{{steps.check_velocity.output}}</p>"}}', 200),
(v_wf_id, 'done', 'noop', false, NULL, '{}', 300);
END IF;
-- ========================
-- Template 2: Daily Sales Report
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Daily Sales Report' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Daily Sales Report', 'Generates and emails a daily sales summary every morning', 'chart.bar', 'active', true, 'schedule', '{"interval_minutes": 1440}', true, 'analytics', ARRAY['analytics','reports','email'], 1, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'get_sales', 'tool', true, 'format', '{"tool_name":"analytics","args_template":{"action":"summary","period":"yesterday"}}', 0),
(v_wf_id, 'format', 'transform', false, 'send_email', '{"mapping":{"subject":"Daily Sales Report - {{now}}","revenue":"{{steps.get_sales.output.total_revenue}}","orders":"{{steps.get_sales.output.total_orders}}"}}', 100),
(v_wf_id, 'send_email', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.report_email}}","subject":"{{steps.format.output.subject}}","html":"<h2>Revenue: ${{steps.format.output.revenue}}</h2><p>Orders: {{steps.format.output.orders}}</p>"}}', 200);
END IF;
-- ========================
-- Template 3: New Customer Welcome
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'New Customer Welcome' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('New Customer Welcome', 'Sends a welcome email to new customers and adds an internal note', 'person.badge.plus', 'active', true, 'webhook', '{}', true, 'crm', ARRAY['crm','email','onboarding'], 5, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'extract', 'transform', true, 'send_welcome', '{"mapping":{"name":"{{trigger.first_name}}","email":"{{trigger.email}}","customer_id":"{{trigger.customer_id}}"}}', 0),
(v_wf_id, 'send_welcome', 'tool', false, 'add_note', '{"tool_name":"email","args_template":{"action":"send_template","to":"{{steps.extract.output.email}}","template":"welcome","template_data":{"name":"{{steps.extract.output.name}}"}}}', 100),
(v_wf_id, 'add_note', 'tool', false, NULL, '{"tool_name":"customers","args_template":{"action":"add_note","customer_id":"{{steps.extract.output.customer_id}}","note":"Welcome email sent","created_by":"workflow"}}', 200);
END IF;
-- ========================
-- Template 4: Inventory Reorder with Approval
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Inventory Reorder with Approval' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Inventory Reorder with Approval', 'Checks stock velocity, creates a PO, and waits for manager approval before placing', 'arrow.triangle.2.circlepath', 'active', true, 'schedule', '{"interval_minutes": 10080}', true, 'supply-chain', ARRAY['inventory','purchasing','approval'], 1, 15)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'check_stock', 'tool', true, 'needs_reorder', '{"tool_name":"inventory","args_template":{"action":"velocity","days":30,"limit":50}}', 0),
(v_wf_id, 'needs_reorder', 'condition', false, NULL, '{"expression":"steps.check_stock.output.some(p => p.days_of_stock < 14)","on_true":"create_po","on_false":"skip"}', 100),
(v_wf_id, 'create_po', 'tool', false, 'approve_po', '{"tool_name":"supply_chain","args_template":{"action":"po_create","notes":"Auto-reorder from workflow"}}', 200),
(v_wf_id, 'approve_po', 'approval', false, NULL, '{"title":"Approve Purchase Order","prompt":"Review the auto-generated PO for low-stock items","options":["approve","reject"],"timeout_seconds":172800,"timeout_action":"fail","on_approve":"place_order","on_reject":"cancelled"}', 300),
(v_wf_id, 'place_order', 'tool', false, 'notify', '{"tool_name":"supply_chain","args_template":{"action":"po_approve","purchase_order_id":"{{steps.create_po.output.id}}"}}', 400),
(v_wf_id, 'notify', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.notify_email}}","subject":"PO Approved and Placed","html":"Purchase order approved and sent to supplier."}}', 500),
(v_wf_id, 'cancelled', 'noop', false, NULL, '{}', 500),
(v_wf_id, 'skip', 'noop', false, NULL, '{}', 200);
END IF;
-- ========================
-- Template 5: Order Status Webhook (Slack/Discord)
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Order Status Webhook' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Order Status Webhook', 'Sends order updates to Slack or Discord via webhook', 'bell.badge', 'active', true, 'webhook', '{}', true, 'integrations', ARRAY['orders','slack','discord','webhook'], 10, 5)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'format', 'transform', true, 'send', '{"mapping":{"text":"Order #{{trigger.order_id}} status: {{trigger.status}} — ${{trigger.total}}"}}', 0),
(v_wf_id, 'send', 'webhook_out', false, NULL, '{"url":"{{trigger.webhook_url}}","method":"POST","body_template":{"content":"{{steps.format.output.text}}"}}', 100);
END IF;
-- ========================
-- Template 6: Stripe Payment Handler
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Stripe Payment Handler' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Stripe Payment Handler', 'Processes Stripe webhook events to update orders and send confirmations', 'creditcard', 'active', true, 'webhook', '{}', true, 'payments', ARRAY['stripe','payments','orders'], 10, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'check_event', 'condition', true, NULL, '{"expression":"trigger.type === ''payment_intent.succeeded''","on_true":"update_order","on_false":"skip"}', 0),
(v_wf_id, 'update_order', 'tool', false, 'send_receipt', '{"tool_name":"orders","args_template":{"action":"update","order_id":"{{trigger.data.object.metadata.order_id}}","status":"paid"}}', 100),
(v_wf_id, 'send_receipt', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send_template","to":"{{trigger.data.object.receipt_email}}","template":"order-confirmation"}}', 200),
(v_wf_id, 'skip', 'noop', false, NULL, '{}', 100);
END IF;
-- ========================
-- Template 7: Customer Win-Back Campaign
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Customer Win-Back Campaign' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Customer Win-Back Campaign', 'Identifies inactive customers and sends targeted re-engagement emails', 'person.crop.circle.badge.clock', 'active', true, 'schedule', '{"interval_minutes": 10080}', true, 'marketing', ARRAY['crm','email','marketing','retention'], 1, 15)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'find_inactive', 'tool', true, 'filter', '{"tool_name":"analytics","args_template":{"action":"customers","period":"last_90"}}', 0),
(v_wf_id, 'filter', 'code', false, 'send_emails', '{"language":"javascript","code":"const inactive = (input.steps?.find_inactive?.output || []).filter(c => c.days_since_last_order > 60); output = inactive.slice(0, 50);"}', 100),
(v_wf_id, 'send_emails', 'for_each', false, NULL, '{"items":"{{steps.filter.output}}","step_key":"send_one"}', 200),
(v_wf_id, 'send_one', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send_template","to":"{{input.email}}","template":"win-back","template_data":{"name":"{{input.first_name}}"}}}', 300);
END IF;
-- ========================
-- Template 8: Multi-Location Inventory Sync
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Multi-Location Inventory Sync' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Multi-Location Inventory Sync', 'Balances inventory across locations by creating transfers for imbalanced stock', 'arrow.left.arrow.right', 'active', true, 'schedule', '{"interval_minutes": 1440}', true, 'inventory', ARRAY['inventory','transfers','multi-location'], 1, 20)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'get_stock', 'tool', true, 'analyze', '{"tool_name":"inventory","args_template":{"action":"summary"}}', 0),
(v_wf_id, 'analyze', 'agent', false, 'create_transfers', '{"agent_id":"default","prompt":"Analyze this inventory data and suggest transfers to balance stock across locations. Return a JSON array of {from_location_id, to_location_id, product_id, quantity} objects: {{steps.get_stock.output}}","max_turns":3}', 100),
(v_wf_id, 'create_transfers', 'approval', false, NULL, '{"title":"Approve Inventory Transfers","prompt":"Review suggested transfers: {{steps.analyze.output.response}}","options":["approve","reject"],"on_approve":"execute","on_reject":"skip"}', 200),
(v_wf_id, 'execute', 'noop', false, NULL, '{}', 300),
(v_wf_id, 'skip', 'noop', false, NULL, '{}', 300);
END IF;
-- ========================
-- Template 9: Fraud Detection Alert
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Fraud Detection Alert' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Fraud Detection Alert', 'Monitors for suspicious order patterns and flags them for review', 'shield.lefthalf.filled', 'active', true, 'schedule', '{"interval_minutes": 60}', true, 'security', ARRAY['security','fraud','orders','alerts'], 1, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'check_fraud', 'tool', true, 'evaluate', '{"tool_name":"analytics","args_template":{"action":"fraud","period":"last_7"}}', 0),
(v_wf_id, 'evaluate', 'condition', false, NULL, '{"expression":"steps.check_fraud.output.high_risk_count > 0","on_true":"alert","on_false":"done"}', 100),
(v_wf_id, 'alert', 'tool', false, 'done', '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.security_email}}","subject":"Fraud Alert: {{steps.check_fraud.output.high_risk_count}} suspicious orders","html":"<h2>Suspicious Activity Detected</h2>"}}', 200),
(v_wf_id, 'done', 'noop', false, NULL, '{}', 300);
END IF;
-- ========================
-- Template 10: Employee Performance Report
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Employee Performance Report' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Employee Performance Report', 'Weekly report of employee sales performance with rankings', 'person.3', 'active', true, 'schedule', '{"interval_minutes": 10080}', true, 'analytics', ARRAY['analytics','employees','reports'], 1, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'get_performance', 'tool', true, 'format', '{"tool_name":"analytics","args_template":{"action":"employee_performance","period":"last_7"}}', 0),
(v_wf_id, 'format', 'agent', false, 'send', '{"agent_id":"default","prompt":"Create a formatted HTML email report from this employee performance data. Include a leaderboard table: {{steps.get_performance.output}}","max_turns":2}', 100),
(v_wf_id, 'send', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.manager_email}}","subject":"Weekly Performance Report","html":"{{steps.format.output.response}}"}}', 200);
END IF;
-- ========================
-- Template 11: Product Price Update Pipeline
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Product Price Update Pipeline' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Product Price Update Pipeline', 'Bulk price updates with review, approval, and notification', 'tag', 'active', true, 'manual', '{}', true, 'catalog', ARRAY['products','pricing','approval'], 1, 15)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'review', 'approval', true, NULL, '{"title":"Approve Price Changes","prompt":"Review the following price updates: {{trigger.changes}}","options":["approve","reject","modify"],"on_approve":"apply","on_reject":"cancelled"}', 0),
(v_wf_id, 'apply', 'agent', false, 'notify', '{"agent_id":"default","prompt":"Apply these price changes to the product catalog: {{trigger.changes}}. Use the products tool to update each product.","max_turns":10}', 100),
(v_wf_id, 'notify', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.notify_email}}","subject":"Price Updates Applied","html":"Price changes have been applied to the catalog."}}', 200),
(v_wf_id, 'cancelled', 'noop', false, NULL, '{}', 100);
END IF;
-- ========================
-- Template 12: Customer Loyalty Tier Upgrade
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Customer Loyalty Tier Upgrade' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Customer Loyalty Tier Upgrade', 'Checks customer spending and upgrades loyalty tiers automatically', 'star.circle', 'active', true, 'schedule', '{"interval_minutes": 1440}', true, 'crm', ARRAY['crm','loyalty','automation'], 1, 15)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'find_eligible', 'agent', true, 'upgrade', '{"agent_id":"default","prompt":"Find customers whose total spending in the last 90 days qualifies them for a loyalty tier upgrade. Use analytics and customer tools. Return a JSON array of {customer_id, new_tier, total_spent}.","max_turns":5}', 0),
(v_wf_id, 'upgrade', 'agent', false, 'notify_all', '{"agent_id":"default","prompt":"Upgrade these customers to their new loyalty tiers: {{steps.find_eligible.output.response}}. Use the customers tool to update each one.","max_turns":10}', 100),
(v_wf_id, 'notify_all', 'noop', false, NULL, '{}', 200);
END IF;
-- ========================
-- Template 13: Scheduled Inventory Audit
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Scheduled Inventory Audit' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Scheduled Inventory Audit', 'Starts an inventory audit, sends count sheets, and waits for completion', 'checklist', 'active', true, 'manual', '{}', true, 'inventory', ARRAY['inventory','audit','compliance'], 1, 15)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'start_audit', 'tool', true, 'notify_staff', '{"tool_name":"inventory","args_template":{"action":"audit_start"}}', 0),
(v_wf_id, 'notify_staff', 'tool', false, 'wait_completion', '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.staff_email}}","subject":"Inventory Audit Started","html":"Please complete your count sheets."}}', 100),
(v_wf_id, 'wait_completion', 'waitpoint', false, 'generate_report', '{"label":"Audit count completion","timeout_seconds":86400}', 200),
(v_wf_id, 'generate_report', 'tool', false, NULL, '{"tool_name":"inventory","args_template":{"action":"audit_summary"}}', 300);
END IF;
-- ========================
-- Template 14: Multi-Step Data Export
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Multi-Step Data Export' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Multi-Step Data Export', 'Generates a comprehensive export with products, inventory, and sales data', 'square.and.arrow.up', 'active', true, 'manual', '{}', true, 'analytics', ARRAY['export','data','reports'], 1, 15)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'get_products', 'tool', true, 'get_inventory', '{"tool_name":"products","args_template":{"action":"find","limit":500}}', 0),
(v_wf_id, 'get_inventory', 'tool', false, 'get_sales', '{"tool_name":"inventory","args_template":{"action":"summary"}}', 100),
(v_wf_id, 'get_sales', 'tool', false, 'compile', '{"tool_name":"analytics","args_template":{"action":"summary","period":"last_30"}}', 200),
(v_wf_id, 'compile', 'agent', false, NULL, '{"agent_id":"default","prompt":"Compile a comprehensive data export from: Products: {{steps.get_products.output}}, Inventory: {{steps.get_inventory.output}}, Sales: {{steps.get_sales.output}}. Format as a structured report.","max_turns":3}', 300);
END IF;
-- ========================
-- Template 15: Webhook Relay (Generic)
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Webhook Relay' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Webhook Relay', 'Receives a webhook and forwards it to multiple destinations', 'arrow.triangle.branch', 'active', true, 'webhook', '{}', true, 'integrations', ARRAY['webhook','relay','integration'], 10, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'forward', 'parallel', true, NULL, '{"child_steps":["dest1","dest2"]}', 0),
(v_wf_id, 'dest1', 'webhook_out', false, NULL, '{"url":"{{trigger.destination_1}}","method":"POST","body_template":"{{trigger}}"}', 100),
(v_wf_id, 'dest2', 'webhook_out', false, NULL, '{"url":"{{trigger.destination_2}}","method":"POST","body_template":"{{trigger}}"}', 100);
END IF;
-- ========================
-- Template 16: AI-Powered Customer Support Triage
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'AI Customer Support Triage' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('AI Customer Support Triage', 'AI agent triages support emails, categorizes them, and routes to the right team', 'bubble.left.and.exclamationmark.bubble.right', 'active', true, 'webhook', '{}', true, 'ai', ARRAY['ai','support','email','triage'], 10, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'triage', 'agent', true, 'route', '{"agent_id":"default","prompt":"Analyze this support email and categorize it. Return JSON with {category, priority, suggested_response, needs_human}: Subject: {{trigger.subject}} Body: {{trigger.body}}","max_turns":2,"allowed_tools":["customers","orders"]}', 0),
(v_wf_id, 'route', 'condition', false, NULL, '{"expression":"steps.triage.output.response.includes(''needs_human'': true)","on_true":"escalate","on_false":"auto_reply"}', 100),
(v_wf_id, 'auto_reply', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.from}}","subject":"Re: {{trigger.subject}}","html":"{{steps.triage.output.response}}"}}', 200),
(v_wf_id, 'escalate', 'approval', false, NULL, '{"title":"Review AI Response","prompt":"AI suggested response for {{trigger.subject}}: {{steps.triage.output.response}}","options":["send","edit","skip"],"on_approve":"send_edited","on_reject":"skip"}', 200),
(v_wf_id, 'send_edited', 'noop', false, NULL, '{}', 300),
(v_wf_id, 'skip', 'noop', false, NULL, '{}', 300);
END IF;
-- ========================
-- Template 17: Scheduled PO Receiving Reminder
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'PO Receiving Reminder' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('PO Receiving Reminder', 'Checks for overdue purchase orders and sends reminder emails', 'clock.badge.exclamationmark', 'active', true, 'schedule', '{"interval_minutes": 1440}', true, 'supply-chain', ARRAY['purchasing','reminders','supply-chain'], 1, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'check_overdue', 'tool', true, 'has_overdue', '{"tool_name":"supply_chain","args_template":{"action":"po_list","status":"approved"}}', 0),
(v_wf_id, 'has_overdue', 'condition', false, NULL, '{"expression":"steps.check_overdue.output && steps.check_overdue.output.length > 0","on_true":"remind","on_false":"done"}', 100),
(v_wf_id, 'remind', 'tool', false, 'done', '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.receiving_email}}","subject":"Overdue POs Pending","html":"<p>{{steps.check_overdue.output.length}} POs are waiting to be received.</p>"}}', 200),
(v_wf_id, 'done', 'noop', false, NULL, '{}', 300);
END IF;
-- ========================
-- Template 18: AI Product Description Generator
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'AI Product Description Generator' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('AI Product Description Generator', 'Uses AI to generate SEO-optimized product descriptions for new products', 'wand.and.stars', 'active', true, 'manual', '{}', true, 'ai', ARRAY['ai','products','catalog','content'], 3, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'get_product', 'tool', true, 'generate', '{"tool_name":"products","args_template":{"action":"get","product_id":"{{trigger.product_id}}"}}', 0),
(v_wf_id, 'generate', 'agent', false, 'review', '{"agent_id":"default","prompt":"Generate an SEO-optimized product description for: {{steps.get_product.output.name}}. Category: {{steps.get_product.output.category}}. Include a short tagline, full description, and 5 bullet points. Return as JSON.","max_turns":2}', 100),
(v_wf_id, 'review', 'approval', false, NULL, '{"title":"Review Generated Description","prompt":"{{steps.generate.output.response}}","options":["approve","edit","regenerate"],"on_approve":"apply","on_reject":"done"}', 200),
(v_wf_id, 'apply', 'tool', false, 'done', '{"tool_name":"products","args_template":{"action":"update","product_id":"{{trigger.product_id}}","description":"{{steps.generate.output.response}}"}}', 300),
(v_wf_id, 'done', 'noop', false, NULL, '{}', 400);
END IF;
-- ========================
-- Template 19: End-of-Day Close-Out Report
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'End-of-Day Close-Out' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('End-of-Day Close-Out', 'Runs a comprehensive end-of-day report with sales, inventory, and employee summaries', 'sunset', 'active', true, 'schedule', '{"interval_minutes": 1440}', true, 'operations', ARRAY['operations','reports','daily'], 1, 15)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'gather', 'parallel', true, 'compile', '{"child_steps":["sales","inventory","employees"]}', 0),
(v_wf_id, 'sales', 'tool', false, NULL, '{"tool_name":"analytics","args_template":{"action":"summary","period":"today"}}', 100),
(v_wf_id, 'inventory', 'tool', false, NULL, '{"tool_name":"analytics","args_template":{"action":"inventory_intelligence"}}', 100),
(v_wf_id, 'employees', 'tool', false, NULL, '{"tool_name":"analytics","args_template":{"action":"employee","period":"today"}}', 100),
(v_wf_id, 'compile', 'agent', false, 'send', '{"agent_id":"default","prompt":"Create a formatted end-of-day report from: Sales: {{steps.sales.output}}, Inventory: {{steps.inventory.output}}, Employees: {{steps.employees.output}}","max_turns":2}', 200),
(v_wf_id, 'send', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.report_email}}","subject":"End-of-Day Report","html":"{{steps.compile.output.response}}"}}', 300);
END IF;
-- ========================
-- Template 20: Custom Webhook Integration
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Custom Webhook Integration' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Custom Webhook Integration', 'Template for building custom integrations with external services via webhooks', 'link.circle', 'active', true, 'webhook', '{}', true, 'integrations', ARRAY['webhook','custom','integration','sdk'], 10, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'validate', 'condition', true, NULL, '{"expression":"trigger.api_key === ''expected_key''","on_true":"process","on_false":"reject"}', 0),
(v_wf_id, 'process', 'custom', false, 'respond', '{"url":"https://your-service.com/process","headers":{"X-API-Key":"your-key"}}', 100),
(v_wf_id, 'respond', 'webhook_out', false, NULL, '{"url":"{{trigger.callback_url}}","method":"POST","body_template":{"status":"completed","result":"{{steps.process.output}}"}}', 200),
(v_wf_id, 'reject', 'noop', false, NULL, '{}', 100);
END IF;
-- ========================
-- Template 21: Category Performance Analysis
-- ========================
IF NOT EXISTS (SELECT 1 FROM workflows WHERE name = 'Category Performance Analysis' AND is_template = true) THEN
INSERT INTO workflows (name, description, icon, status, is_active, trigger_type, trigger_config, is_template, template_category, template_tags, max_concurrent_runs, max_steps_per_run)
VALUES ('Category Performance Analysis', 'Weekly analysis of category performance with AI insights and recommendations', 'chart.pie', 'active', true, 'schedule', '{"interval_minutes": 10080}', true, 'analytics', ARRAY['analytics','categories','ai','insights'], 1, 10)
RETURNING id INTO v_wf_id;
INSERT INTO workflow_steps (workflow_id, step_key, step_type, is_entry_point, on_success, step_config, position_y) VALUES
(v_wf_id, 'get_data', 'tool', true, 'analyze', '{"tool_name":"analytics","args_template":{"action":"by_category","period":"last_7"}}', 0),
(v_wf_id, 'analyze', 'agent', false, 'send', '{"agent_id":"default","prompt":"Analyze this category performance data and provide 5 actionable recommendations: {{steps.get_data.output}}","max_turns":2}', 100),
(v_wf_id, 'send', 'tool', false, NULL, '{"tool_name":"email","args_template":{"action":"send","to":"{{trigger.manager_email}}","subject":"Weekly Category Insights","html":"{{steps.analyze.output.response}}"}}', 200);
END IF;
END $$;
