Fix: Workflow operations not deducting credits - Modify existing RPC function approach

[ruvnet]

🐛 Bug Description

Workflow operations (workflow_create and workflow_execute) are not deducting credits from user balances, despite being listed in the tool_costs table with defined prices.

📊 Current Behavior vs Expected

Current Behavior

• Sandbox Creation: ✅ Deducts 10 credits correctly
• Sandbox Execution: ✅ Deducts 2 credits correctly
• Workflow Creation: ❌ NO credits deducted (should deduct 5)
• Workflow Execution: ❌ NO credits deducted (should deduct 3)

Root Cause Analysis

The credit deduction system relies on meter_events table insertions which trigger the deduct_credits_master() function.

Working Example (Sandbox):

// mcp-server/src/services/supabase.js:1410-1421
const { data: meterEvent } = await supabase
  .from('meter_events')
  .insert({
    user_id: userId,
    event_name: 'sandbox_create',
    value: 1,
    metadata: {
      sandbox_id: sandbox.id,
      template: template
    }
  })
  .select()
  .single();

Broken Example (Workflow):

// mcp-server/src/services/supabase.js:1238-1282
async function createWorkflow(params, userId) {
  // ... workflow creation logic ...
  
  // MISSING: No meter_events insertion!
  
  return {
    success: true,
    workflow_id: workflow.id,
    // ...
  };
}

🔧 Proposed Solution: Option 3 - Modify Existing RPC Function

Why Option 3?

After analyzing the codebase, the create_workflow function is actually an RPC function in Supabase, not just a JavaScript function:

-- Database function that creates workflows
CREATE OR REPLACE FUNCTION create_workflow(
  p_name text,
  p_description text,
  p_steps jsonb,
  p_user_id uuid
) RETURNS jsonb AS $$
DECLARE
  v_workflow_id uuid;
BEGIN
  -- Insert workflow
  INSERT INTO workflows (name, description, steps, user_id, status)
  VALUES (p_name, p_description, p_steps, p_user_id, 'active')
  RETURNING id INTO v_workflow_id;
  
  -- MISSING: meter_events insertion for billing!
  
  RETURN jsonb_build_object(
    'success', true,
    'workflow_id', v_workflow_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Implementation Plan

Step 1: Modify create_workflow RPC Function

CREATE OR REPLACE FUNCTION create_workflow(
  p_name text,
  p_description text,
  p_steps jsonb,
  p_user_id uuid
) RETURNS jsonb AS $$
DECLARE
  v_workflow_id uuid;
  v_meter_event_id uuid;
BEGIN
  -- Original workflow creation
  INSERT INTO workflows (name, description, steps, user_id, status)
  VALUES (p_name, p_description, p_steps, p_user_id, 'active')
  RETURNING id INTO v_workflow_id;
  
  -- ADD: Insert meter event for billing
  INSERT INTO meter_events (
    user_id,
    event_name,
    value,
    metadata
  ) VALUES (
    p_user_id,
    'workflow_create',
    1,
    jsonb_build_object(
      'workflow_id', v_workflow_id,
      'workflow_name', p_name
    )
  ) RETURNING id INTO v_meter_event_id;
  
  RETURN jsonb_build_object(
    'success', true,
    'workflow_id', v_workflow_id,
    'meter_event_id', v_meter_event_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Step 2: Create/Modify execute_workflow RPC Function

CREATE OR REPLACE FUNCTION execute_workflow(
  p_workflow_id uuid,
  p_input_data jsonb,
  p_user_id uuid
) RETURNS jsonb AS $$
DECLARE
  v_execution_id uuid;
  v_meter_event_id uuid;
BEGIN
  -- Original execution logic
  INSERT INTO workflow_executions (workflow_id, input_data, user_id, status)
  VALUES (p_workflow_id, p_input_data, p_user_id, 'running')
  RETURNING id INTO v_execution_id;
  
  -- ADD: Insert meter event for billing
  INSERT INTO meter_events (
    user_id,
    event_name,
    value,
    metadata
  ) VALUES (
    p_user_id,
    'workflow_execute',
    1,
    jsonb_build_object(
      'workflow_id', p_workflow_id,
      'execution_id', v_execution_id
    )
  ) RETURNING id INTO v_meter_event_id;
  
  RETURN jsonb_build_object(
    'success', true,
    'execution_id', v_execution_id,
    'meter_event_id', v_meter_event_id
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Advantages of This Approach

1. Server-side enforcement: Credits deducted at database level, cannot be bypassed
2. Atomic transactions: Workflow creation and billing happen in same transaction
3. Consistency: Matches pattern used by challenge creation (which also deducts in DB)
4. Rollback safety: If billing fails, workflow creation rolls back
5. No client changes: MCP server continues calling RPC functions as normal

Testing Plan

1. Pre-implementation Testing:

   -- Check current balance
   SELECT available_credits FROM ruv_balances WHERE user_id = 'test-user-id';
   
   -- Create workflow via RPC
   SELECT create_workflow('Test', 'Description', '[]'::jsonb, 'test-user-id');
   
   -- Verify meter_events created
   SELECT * FROM meter_events WHERE user_id = 'test-user-id' ORDER BY created_at DESC;
   
   -- Verify credits deducted
   SELECT available_credits FROM ruv_balances WHERE user_id = 'test-user-id';
   

2. Edge Cases to Test:

   • Insufficient credits (should fail entire transaction)
   • Concurrent workflow creations
   • Workflow creation followed by immediate execution
   • Multiple workflow executions in parallel

Migration Script

-- Migration: Add billing to workflow operations
BEGIN;

-- Update create_workflow function
CREATE OR REPLACE FUNCTION create_workflow(...) AS $$ 
  -- Implementation above
$$;

-- Update execute_workflow function  
CREATE OR REPLACE FUNCTION execute_workflow(...) AS $$
  -- Implementation above
$$;

-- Verify tool_costs table has correct entries
INSERT INTO tool_costs (tool_name, cost_per_use, description)
VALUES 
  ('workflow_create', 5, 'Create a new workflow'),
  ('workflow_execute', 3, 'Execute an existing workflow')
ON CONFLICT (tool_name) DO UPDATE
SET cost_per_use = EXCLUDED.cost_per_use;

COMMIT;

Rollback Plan

If issues arise, revert to original functions:

-- Restore original functions without billing
CREATE OR REPLACE FUNCTION create_workflow(...) AS $$
  -- Original implementation
$$;

📈 Impact Analysis

• Users Affected: All users creating/executing workflows
• Financial Impact: Currently missing revenue from workflow operations
• Performance Impact: Minimal - one additional INSERT per operation
• Security Impact: Positive - enforces billing at database level

✅ Definition of Done

• [ ] RPC functions modified to insert meter_events
• [ ] Migration script tested in staging
• [ ] Edge cases validated
• [ ] Documentation updated
• [ ] Monitoring added for workflow billing events
• [ ] Rollback procedure documented and tested

🏷️ Labels

• bug
• billing
• high-priority
• database
• security

📝 Additional Notes

This approach (Option 3) is preferred over:

• Option 1 (Client-side): Can be bypassed, not secure
• Option 2 (Edge function): Adds latency, more complex
• Option 4 (New billing service): Over-engineered for this use case

The modification aligns with existing patterns in the codebase where challenges table operations also handle billing at the database level through triggers and functions.

[ruvnet]

🔧 Implementation Corrections Required

After reviewing the actual database functions, several corrections are needed to the proposed implementation:

⚠️ Issues Found

1. Function Signature Mismatch

   • Actual create_workflow: (p_name text, p_description text DEFAULT NULL, p_specification jsonb DEFAULT '{}', p_priority integer DEFAULT 5)
   • Issue showed: (p_name text, p_description text, p_steps jsonb, p_user_id uuid)

2. User ID Source

   • Functions use auth.uid() for user identification, not passed parameters
   • No p_user_id parameter in create_workflow

3. Schema References

   • Workflows are stored in workflow_system.workflows, not public.workflows

4. Missing Balance Validation

   • Should verify sufficient credits before deduction

✅ Corrected Implementation Available

The full corrected implementation with proper function signatures, auth handling, and balance validation has been documented in commit 9e6ac04.

Key corrections:

• ✅ Function signatures match actual database
• ✅ Uses auth.uid() for authentication
• ✅ References correct schema workflow_system.workflows
• ✅ Includes balance validation before credit deduction
• ✅ Comprehensive error handling

See the full implementation details in /issue-744-corrections.md in the repository.