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

## 🐛 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):**
```typescript
// 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):**
```typescript
// 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:

```sql
-- 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
```sql
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
```sql
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**:
```sql
-- 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

```sql
-- 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:
```sql
-- 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.

Start in mcp-server/src/services/supabase.js around the workflow creation and execution paths, then locate the corresponding create_workflow and execute_workflow RPC definitions. Review the meter_events and tool_costs entries and test the RPC calls against ruv_balances, including insufficient-credit and concurrent-operation cases. Done means both workflow operations create billing events, deduct the configured credits, and pass the listed edge-case checks.

Written by the indexing model from the issue text.