-- MCP Server ROI Database Schema
-- Run this in your Supabase SQL editor to set up the database
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Projects table
CREATE TABLE IF NOT EXISTS projects (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID, -- Added for user isolation (set by trigger)
client_name TEXT NOT NULL,
project_name TEXT NOT NULL,
industry TEXT NOT NULL CHECK (industry IN (
'financial_services', 'healthcare', 'retail', 'manufacturing',
'technology', 'education', 'government', 'other'
)),
description TEXT,
status TEXT DEFAULT 'draft' CHECK (status IN (
'draft', 'active', 'completed', 'archived'
)),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Use cases table
CREATE TABLE IF NOT EXISTS use_cases (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID, -- Added for user isolation (set by trigger)
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
name TEXT NOT NULL,
category TEXT NOT NULL CHECK (category IN (
'automation', 'analytics', 'customer_service', 'operations',
'sales_marketing', 'hr_recruiting', 'finance_accounting', 'custom'
)),
current_state JSONB NOT NULL CHECK (
current_state ? 'process_time_hours' AND
current_state ? 'cost_per_transaction' AND
current_state ? 'error_rate' AND
current_state ? 'volume_per_month' AND
current_state ? 'fte_required'
),
future_state JSONB NOT NULL CHECK (
future_state ? 'automation_percentage' AND
future_state ? 'time_reduction_percentage' AND
future_state ? 'error_reduction_percentage' AND
future_state ? 'scalability_factor'
),
implementation JSONB NOT NULL CHECK (
implementation ? 'development_hours' AND
implementation ? 'complexity_score' AND
implementation ? 'dependencies' AND
implementation ? 'risk_factors'
),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Projections table
CREATE TABLE IF NOT EXISTS projections (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID, -- Added for user isolation (set by trigger)
project_id UUID REFERENCES projects(id) ON DELETE CASCADE,
scenario_name TEXT DEFAULT 'Base Case',
metadata JSONB NOT NULL CHECK (
metadata ? 'confidence_level' AND
metadata ? 'assumptions'
),
implementation_costs JSONB NOT NULL CHECK (
implementation_costs ? 'software_licenses' AND
implementation_costs ? 'development_hours' AND
implementation_costs ? 'training_costs' AND
implementation_costs ? 'infrastructure'
),
timeline_months INTEGER NOT NULL CHECK (timeline_months > 0 AND timeline_months <= 120),
financial_metrics JSONB NOT NULL CHECK (
financial_metrics ? 'conservative' AND
financial_metrics ? 'expected' AND
financial_metrics ? 'optimistic'
),
calculations JSONB NOT NULL CHECK (
calculations ? 'total_investment' AND
calculations ? 'net_present_value' AND
calculations ? 'internal_rate_of_return' AND
calculations ? 'payback_period_months' AND
calculations ? 'five_year_roi' AND
calculations ? 'break_even_date'
),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Simulation results table
CREATE TABLE IF NOT EXISTS simulation_results (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID, -- Added for user isolation (set by trigger)
projection_id UUID REFERENCES projections(id) ON DELETE CASCADE,
simulation_type TEXT NOT NULL DEFAULT 'monte_carlo',
results JSONB NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Actual metrics tracking table
CREATE TABLE IF NOT EXISTS actual_metrics (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
user_id UUID, -- Added for user isolation (set by trigger)
projection_id UUID REFERENCES projections(id) ON DELETE CASCADE,
period TEXT NOT NULL, -- e.g., '2024-01', '2024-Q1'
metrics JSONB NOT NULL CHECK (
metrics ? 'cost_savings' AND
metrics ? 'time_savings_hours' AND
metrics ? 'revenue_increase' AND
metrics ? 'quality_improvements' AND
metrics ? 'user_adoption'
),
evidence JSONB DEFAULT '[]'::jsonb,
tracked_at TIMESTAMPTZ DEFAULT NOW()
);
-- Create indexes for performance
CREATE INDEX idx_projects_client ON projects(client_name);
CREATE INDEX idx_projects_status ON projects(status);
CREATE INDEX idx_projects_industry ON projects(industry);
CREATE INDEX idx_use_cases_project ON use_cases(project_id);
CREATE INDEX idx_use_cases_category ON use_cases(category);
CREATE INDEX idx_projections_project ON projections(project_id);
CREATE INDEX idx_projections_created ON projections(created_at DESC);
CREATE INDEX idx_simulation_results_projection ON simulation_results(projection_id);
CREATE INDEX idx_actual_metrics_projection ON actual_metrics(projection_id);
CREATE INDEX idx_actual_metrics_period ON actual_metrics(period);
-- Create updated_at trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
-- Apply updated_at triggers
CREATE TRIGGER update_projects_updated_at BEFORE UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_use_cases_updated_at BEFORE UPDATE ON use_cases
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_projections_updated_at BEFORE UPDATE ON projections
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
-- Create views for common queries
-- Active projects with latest projection
CREATE VIEW active_projects_summary AS
SELECT
p.*,
latest_proj.id as latest_projection_id,
latest_proj.calculations->>'five_year_roi' as latest_roi,
latest_proj.calculations->>'payback_period_months' as latest_payback_months,
latest_proj.created_at as projection_date,
COUNT(DISTINCT uc.id) as use_case_count
FROM projects p
LEFT JOIN LATERAL (
SELECT * FROM projections
WHERE project_id = p.id
ORDER BY created_at DESC
LIMIT 1
) latest_proj ON true
LEFT JOIN use_cases uc ON uc.project_id = p.id
WHERE p.status = 'active'
GROUP BY p.id, latest_proj.id, latest_proj.calculations, latest_proj.created_at;
-- Project performance tracking
CREATE VIEW project_performance AS
SELECT
p.id as project_id,
p.project_name,
p.client_name,
proj.id as projection_id,
proj.calculations->>'five_year_roi' as projected_roi,
proj.calculations->>'payback_period_months' as projected_payback,
am.period,
am.metrics->>'cost_savings' as actual_cost_savings,
am.metrics->>'user_adoption' as adoption_rate,
am.tracked_at
FROM projects p
JOIN projections proj ON proj.project_id = p.id
LEFT JOIN actual_metrics am ON am.projection_id = proj.id
ORDER BY p.id, am.period;
-- SECURITY NOTICE:
-- This schema now includes user_id columns for proper data isolation.
-- Row Level Security (RLS) is REQUIRED for production use.
--
-- To enable security:
-- 1. Run the migration file: database/001_security_update.sql
-- 2. Ensure Supabase Auth is configured
-- 3. All data access will be automatically filtered by user
--
-- The dangerous GRANT statements have been removed.
-- Users can only access their own data through RLS policies.
