-- MCP Server ROI Transactional Functions
-- These functions ensure atomic operations for complex data operations
-- Function: create_project_with_details
-- Purpose: Atomically create a project with all its use cases and generate initial projection
-- Returns: JSON object with project_id, projection_id, and success status
CREATE OR REPLACE FUNCTION create_project_with_details(
p_project JSONB,
p_use_cases JSONB,
p_implementation_costs JSONB,
p_timeline_months INTEGER,
p_confidence_level NUMERIC DEFAULT 0.95
) RETURNS JSONB AS $$
DECLARE
v_project_id UUID;
v_projection_id UUID;
v_use_case_ids UUID[];
v_use_case JSONB;
v_total_investment NUMERIC;
v_result JSONB;
BEGIN
-- Start transaction block
-- Validate inputs
IF p_project IS NULL OR p_use_cases IS NULL OR p_implementation_costs IS NULL THEN
RAISE EXCEPTION 'Missing required parameters';
END IF;
IF p_timeline_months < 1 OR p_timeline_months > 120 THEN
RAISE EXCEPTION 'Timeline must be between 1 and 120 months';
END IF;
IF p_confidence_level < 0 OR p_confidence_level > 1 THEN
RAISE EXCEPTION 'Confidence level must be between 0 and 1';
END IF;
-- Step 1: Create project
INSERT INTO projects (
client_name,
project_name,
industry,
description,
status
)
VALUES (
p_project->>'client_name',
p_project->>'project_name',
p_project->>'industry',
p_project->>'description',
COALESCE(p_project->>'status', 'active')
)
RETURNING id INTO v_project_id;
-- Step 2: Create use cases
FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases)
LOOP
INSERT INTO use_cases (
project_id,
name,
category,
current_state,
future_state,
implementation
)
VALUES (
v_project_id,
v_use_case->>'name',
v_use_case->>'category',
v_use_case->'current_state',
v_use_case->'future_state',
v_use_case->'implementation'
)
RETURNING id INTO v_use_case_ids[array_length(v_use_case_ids, 1) + 1];
END LOOP;
-- Step 3: Calculate total investment
v_total_investment :=
COALESCE((p_implementation_costs->>'software_licenses')::NUMERIC, 0) +
COALESCE((p_implementation_costs->>'development_hours')::NUMERIC, 0) * 150 + -- $150/hour default rate
COALESCE((p_implementation_costs->>'training_costs')::NUMERIC, 0) +
COALESCE((p_implementation_costs->>'infrastructure')::NUMERIC, 0);
-- Step 4: Create projection with placeholder calculations
-- Note: Actual calculations should be done by the ROI engine
INSERT INTO projections (
project_id,
scenario_name,
metadata,
implementation_costs,
timeline_months,
financial_metrics,
calculations
)
VALUES (
v_project_id,
'Base Case',
jsonb_build_object(
'confidence_level', p_confidence_level,
'assumptions', jsonb_build_array(
'Standard implementation timeline',
'Average industry adoption rates',
'No major technical obstacles'
),
'created_via', 'transactional_function'
),
p_implementation_costs,
p_timeline_months,
jsonb_build_object(
'conservative', jsonb_build_object('roi', 0, 'npv', 0),
'expected', jsonb_build_object('roi', 0, 'npv', 0),
'optimistic', jsonb_build_object('roi', 0, 'npv', 0)
),
jsonb_build_object(
'total_investment', v_total_investment,
'net_present_value', 0,
'internal_rate_of_return', 0,
'payback_period_months', 0,
'five_year_roi', 0,
'break_even_date', NULL,
'requires_calculation', true
)
)
RETURNING id INTO v_projection_id;
-- Build success response
v_result := jsonb_build_object(
'success', true,
'project_id', v_project_id,
'projection_id', v_projection_id,
'use_case_count', array_length(v_use_case_ids, 1),
'total_investment', v_total_investment,
'message', 'Project created successfully. ROI calculations pending.'
);
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
-- Rollback is automatic in case of exception
RETURN jsonb_build_object(
'success', false,
'error', SQLERRM,
'error_detail', SQLSTATE
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function: update_project_with_use_cases
-- Purpose: Atomically update project and its use cases, regenerate projections
-- Returns: JSON object with update status
CREATE OR REPLACE FUNCTION update_project_with_use_cases(
p_project_id UUID,
p_project_updates JSONB DEFAULT NULL,
p_use_cases_to_add JSONB DEFAULT NULL,
p_use_cases_to_update JSONB DEFAULT NULL,
p_use_cases_to_delete UUID[] DEFAULT NULL,
p_regenerate_projection BOOLEAN DEFAULT false
) RETURNS JSONB AS $$
DECLARE
v_result JSONB;
v_use_case JSONB;
v_update_count INTEGER := 0;
v_add_count INTEGER := 0;
v_delete_count INTEGER := 0;
v_project_exists BOOLEAN;
BEGIN
-- Check if project exists
SELECT EXISTS(SELECT 1 FROM projects WHERE id = p_project_id) INTO v_project_exists;
IF NOT v_project_exists THEN
RAISE EXCEPTION 'Project % not found', p_project_id;
END IF;
-- Update project if updates provided
IF p_project_updates IS NOT NULL THEN
UPDATE projects
SET
client_name = COALESCE(p_project_updates->>'client_name', client_name),
project_name = COALESCE(p_project_updates->>'project_name', project_name),
industry = COALESCE(p_project_updates->>'industry', industry),
description = COALESCE(p_project_updates->>'description', description),
status = COALESCE(p_project_updates->>'status', status)
WHERE id = p_project_id;
END IF;
-- Add new use cases
IF p_use_cases_to_add IS NOT NULL THEN
FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases_to_add)
LOOP
INSERT INTO use_cases (
project_id,
name,
category,
current_state,
future_state,
implementation
)
VALUES (
p_project_id,
v_use_case->>'name',
v_use_case->>'category',
v_use_case->'current_state',
v_use_case->'future_state',
v_use_case->'implementation'
);
v_add_count := v_add_count + 1;
END LOOP;
END IF;
-- Update existing use cases
IF p_use_cases_to_update IS NOT NULL THEN
FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases_to_update)
LOOP
UPDATE use_cases
SET
name = COALESCE(v_use_case->>'name', name),
category = COALESCE(v_use_case->>'category', category),
current_state = COALESCE(v_use_case->'current_state', current_state),
future_state = COALESCE(v_use_case->'future_state', future_state),
implementation = COALESCE(v_use_case->'implementation', implementation)
WHERE id = (v_use_case->>'id')::UUID
AND project_id = p_project_id;
v_update_count := v_update_count + 1;
END LOOP;
END IF;
-- Delete use cases
IF p_use_cases_to_delete IS NOT NULL THEN
DELETE FROM use_cases
WHERE id = ANY(p_use_cases_to_delete)
AND project_id = p_project_id;
GET DIAGNOSTICS v_delete_count = ROW_COUNT;
END IF;
-- Mark existing projections for recalculation if requested
IF p_regenerate_projection THEN
UPDATE projections
SET calculations = jsonb_set(
calculations,
'{requires_recalculation}',
'true'::jsonb
)
WHERE project_id = p_project_id;
END IF;
-- Build result
v_result := jsonb_build_object(
'success', true,
'project_id', p_project_id,
'use_cases_added', v_add_count,
'use_cases_updated', v_update_count,
'use_cases_deleted', v_delete_count,
'projection_marked_for_update', p_regenerate_projection
);
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
-- Rollback is automatic
RETURN jsonb_build_object(
'success', false,
'error', SQLERRM,
'error_detail', SQLSTATE
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function: create_projection_with_simulation
-- Purpose: Create a projection and optionally run simulation in one transaction
-- Returns: JSON object with projection details
CREATE OR REPLACE FUNCTION create_projection_with_simulation(
p_project_id UUID,
p_projection_data JSONB,
p_run_simulation BOOLEAN DEFAULT false,
p_simulation_iterations INTEGER DEFAULT 10000
) RETURNS JSONB AS $$
DECLARE
v_projection_id UUID;
v_simulation_id UUID;
v_result JSONB;
BEGIN
-- Validate project exists
IF NOT EXISTS(SELECT 1 FROM projects WHERE id = p_project_id) THEN
RAISE EXCEPTION 'Project % not found', p_project_id;
END IF;
-- Create projection
INSERT INTO projections (
project_id,
scenario_name,
metadata,
implementation_costs,
timeline_months,
financial_metrics,
calculations
)
VALUES (
p_project_id,
COALESCE(p_projection_data->>'scenario_name', 'Base Case'),
p_projection_data->'metadata',
p_projection_data->'implementation_costs',
(p_projection_data->>'timeline_months')::INTEGER,
p_projection_data->'financial_metrics',
p_projection_data->'calculations'
)
RETURNING id INTO v_projection_id;
-- Create simulation result placeholder if requested
IF p_run_simulation THEN
INSERT INTO simulation_results (
projection_id,
simulation_type,
results
)
VALUES (
v_projection_id,
'monte_carlo',
jsonb_build_object(
'status', 'pending',
'iterations', p_simulation_iterations,
'created_at', NOW()
)
)
RETURNING id INTO v_simulation_id;
END IF;
-- Build result
v_result := jsonb_build_object(
'success', true,
'projection_id', v_projection_id,
'simulation_id', v_simulation_id,
'simulation_pending', p_run_simulation
);
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
RETURN jsonb_build_object(
'success', false,
'error', SQLERRM,
'error_detail', SQLSTATE
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function: delete_project_cascade
-- Purpose: Safely delete a project and all related data
-- Returns: JSON object with deletion summary
CREATE OR REPLACE FUNCTION delete_project_cascade(
p_project_id UUID,
p_confirm_delete BOOLEAN DEFAULT false
) RETURNS JSONB AS $$
DECLARE
v_use_case_count INTEGER;
v_projection_count INTEGER;
v_simulation_count INTEGER;
v_metrics_count INTEGER;
v_result JSONB;
BEGIN
-- Get counts before deletion
SELECT COUNT(*) INTO v_use_case_count FROM use_cases WHERE project_id = p_project_id;
SELECT COUNT(*) INTO v_projection_count FROM projections WHERE project_id = p_project_id;
SELECT COUNT(*) INTO v_simulation_count
FROM simulation_results sr
JOIN projections p ON sr.projection_id = p.id
WHERE p.project_id = p_project_id;
SELECT COUNT(*) INTO v_metrics_count
FROM actual_metrics am
JOIN projections p ON am.projection_id = p.id
WHERE p.project_id = p_project_id;
IF NOT p_confirm_delete THEN
-- Return what would be deleted without actually deleting
RETURN jsonb_build_object(
'success', false,
'project_id', p_project_id,
'would_delete', jsonb_build_object(
'use_cases', v_use_case_count,
'projections', v_projection_count,
'simulations', v_simulation_count,
'metrics', v_metrics_count
),
'message', 'Set confirm_delete to true to proceed with deletion'
);
END IF;
-- Perform cascade delete (relies on ON DELETE CASCADE)
DELETE FROM projects WHERE id = p_project_id;
v_result := jsonb_build_object(
'success', true,
'project_id', p_project_id,
'deleted', jsonb_build_object(
'use_cases', v_use_case_count,
'projections', v_projection_count,
'simulations', v_simulation_count,
'metrics', v_metrics_count
)
);
RETURN v_result;
EXCEPTION
WHEN OTHERS THEN
RETURN jsonb_build_object(
'success', false,
'error', SQLERRM,
'error_detail', SQLSTATE
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function: validate_project_data
-- Purpose: Validate project data before creation/update
-- Returns: JSON object with validation results
CREATE OR REPLACE FUNCTION validate_project_data(
p_project JSONB,
p_use_cases JSONB
) RETURNS JSONB AS $$
DECLARE
v_errors JSONB[] := '{}';
v_warnings JSONB[] := '{}';
v_use_case JSONB;
v_idx INTEGER := 0;
BEGIN
-- Validate project data
IF p_project->>'client_name' IS NULL OR length(p_project->>'client_name') < 1 THEN
v_errors := array_append(v_errors, jsonb_build_object(
'field', 'client_name',
'message', 'Client name is required'
));
END IF;
IF p_project->>'project_name' IS NULL OR length(p_project->>'project_name') < 1 THEN
v_errors := array_append(v_errors, jsonb_build_object(
'field', 'project_name',
'message', 'Project name is required'
));
END IF;
IF p_project->>'industry' NOT IN (
'financial_services', 'healthcare', 'retail', 'manufacturing',
'technology', 'education', 'government', 'other'
) THEN
v_errors := array_append(v_errors, jsonb_build_object(
'field', 'industry',
'message', 'Invalid industry value'
));
END IF;
-- Validate use cases
IF jsonb_array_length(p_use_cases) = 0 THEN
v_errors := array_append(v_errors, jsonb_build_object(
'field', 'use_cases',
'message', 'At least one use case is required'
));
END IF;
FOR v_use_case IN SELECT * FROM jsonb_array_elements(p_use_cases)
LOOP
v_idx := v_idx + 1;
-- Check required fields
IF v_use_case->>'name' IS NULL THEN
v_errors := array_append(v_errors, jsonb_build_object(
'field', format('use_cases[%s].name', v_idx),
'message', 'Use case name is required'
));
END IF;
-- Validate current state
IF NOT (v_use_case->'current_state' ? 'volume_per_month') THEN
v_errors := array_append(v_errors, jsonb_build_object(
'field', format('use_cases[%s].current_state.volume_per_month', v_idx),
'message', 'Volume per month is required'
));
ELSIF (v_use_case->'current_state'->>'volume_per_month')::NUMERIC <= 0 THEN
v_warnings := array_append(v_warnings, jsonb_build_object(
'field', format('use_cases[%s].current_state.volume_per_month', v_idx),
'message', 'Volume per month should be greater than 0'
));
END IF;
-- Validate future state percentages
IF (v_use_case->'future_state'->>'automation_percentage')::NUMERIC > 1 THEN
v_errors := array_append(v_errors, jsonb_build_object(
'field', format('use_cases[%s].future_state.automation_percentage', v_idx),
'message', 'Automation percentage cannot exceed 100%'
));
END IF;
END LOOP;
RETURN jsonb_build_object(
'valid', array_length(v_errors, 1) = 0,
'errors', to_jsonb(v_errors),
'warnings', to_jsonb(v_warnings)
);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Grant execute permissions on functions
GRANT EXECUTE ON FUNCTION create_project_with_details TO authenticated;
GRANT EXECUTE ON FUNCTION update_project_with_use_cases TO authenticated;
GRANT EXECUTE ON FUNCTION create_projection_with_simulation TO authenticated;
GRANT EXECUTE ON FUNCTION delete_project_cascade TO authenticated;
GRANT EXECUTE ON FUNCTION validate_project_data TO authenticated;
-- Add helpful comments
COMMENT ON FUNCTION create_project_with_details IS 'Atomically creates a project with use cases and initial projection';
COMMENT ON FUNCTION update_project_with_use_cases IS 'Atomically updates project and related use cases';
COMMENT ON FUNCTION create_projection_with_simulation IS 'Creates projection with optional simulation placeholder';
COMMENT ON FUNCTION delete_project_cascade IS 'Safely deletes project and all related data with confirmation';
COMMENT ON FUNCTION validate_project_data IS 'Validates project and use case data before operations';
