-- Security Update Migration
-- This migration adds proper security to the MCP Server ROI database
-- 1. Add user_id columns to all tables for proper user isolation
ALTER TABLE projects ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE use_cases ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE projections ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE simulation_results ADD COLUMN IF NOT EXISTS user_id UUID;
ALTER TABLE actual_metrics ADD COLUMN IF NOT EXISTS user_id UUID;
-- 2. Create indexes on user_id columns for performance
CREATE INDEX IF NOT EXISTS idx_projects_user_id ON projects(user_id);
CREATE INDEX IF NOT EXISTS idx_use_cases_user_id ON use_cases(user_id);
CREATE INDEX IF NOT EXISTS idx_projections_user_id ON projections(user_id);
CREATE INDEX IF NOT EXISTS idx_simulation_results_user_id ON simulation_results(user_id);
CREATE INDEX IF NOT EXISTS idx_actual_metrics_user_id ON actual_metrics(user_id);
-- 3. Revoke dangerous permissions from anon role
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM anon;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM anon;
-- 4. Grant minimal permissions to anon (read-only on specific views)
-- Note: anon users should only access data through RLS policies
GRANT USAGE ON SCHEMA public TO anon;
-- 5. Grant appropriate permissions to authenticated users
GRANT USAGE ON SCHEMA public TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO authenticated;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO authenticated;
-- 6. Enable Row Level Security on all tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE use_cases ENABLE ROW LEVEL SECURITY;
ALTER TABLE projections ENABLE ROW LEVEL SECURITY;
ALTER TABLE simulation_results ENABLE ROW LEVEL SECURITY;
ALTER TABLE actual_metrics ENABLE ROW LEVEL SECURITY;
-- 7. Create RLS policies for projects table
CREATE POLICY "Users can view their own projects"
ON projects FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can create their own projects"
ON projects FOR INSERT
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can update their own projects"
ON projects FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete their own projects"
ON projects FOR DELETE
USING (auth.uid() = user_id);
-- 8. Create RLS policies for use_cases table
CREATE POLICY "Users can view their own use cases"
ON use_cases FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can create use cases for their projects"
ON use_cases FOR INSERT
WITH CHECK (
auth.uid() = user_id AND
EXISTS (
SELECT 1 FROM projects
WHERE projects.id = use_cases.project_id
AND projects.user_id = auth.uid()
)
);
CREATE POLICY "Users can update their own use cases"
ON use_cases FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete their own use cases"
ON use_cases FOR DELETE
USING (auth.uid() = user_id);
-- 9. Create RLS policies for projections table
CREATE POLICY "Users can view their own projections"
ON projections FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can create projections for their projects"
ON projections FOR INSERT
WITH CHECK (
auth.uid() = user_id AND
EXISTS (
SELECT 1 FROM projects
WHERE projects.id = projections.project_id
AND projects.user_id = auth.uid()
)
);
CREATE POLICY "Users can update their own projections"
ON projections FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete their own projections"
ON projections FOR DELETE
USING (auth.uid() = user_id);
-- 10. Create RLS policies for simulation_results table
CREATE POLICY "Users can view their own simulation results"
ON simulation_results FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can create simulation results for their projections"
ON simulation_results FOR INSERT
WITH CHECK (
auth.uid() = user_id AND
EXISTS (
SELECT 1 FROM projections
WHERE projections.id = simulation_results.projection_id
AND projections.user_id = auth.uid()
)
);
CREATE POLICY "Users can delete their own simulation results"
ON simulation_results FOR DELETE
USING (auth.uid() = user_id);
-- 11. Create RLS policies for actual_metrics table
CREATE POLICY "Users can view their own metrics"
ON actual_metrics FOR SELECT
USING (auth.uid() = user_id);
CREATE POLICY "Users can create metrics for their projections"
ON actual_metrics FOR INSERT
WITH CHECK (
auth.uid() = user_id AND
EXISTS (
SELECT 1 FROM projections
WHERE projections.id = actual_metrics.projection_id
AND projections.user_id = auth.uid()
)
);
CREATE POLICY "Users can update their own metrics"
ON actual_metrics FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete their own metrics"
ON actual_metrics FOR DELETE
USING (auth.uid() = user_id);
-- 12. Update views to include user_id filtering
DROP VIEW IF EXISTS active_projects_summary;
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'
AND p.user_id = auth.uid() -- Add user filtering
GROUP BY p.id, latest_proj.id, latest_proj.calculations, latest_proj.created_at;
DROP VIEW IF EXISTS project_performance;
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
WHERE p.user_id = auth.uid() -- Add user filtering
ORDER BY p.id, am.period;
-- 13. Create a function to automatically set user_id on insert
CREATE OR REPLACE FUNCTION set_user_id()
RETURNS TRIGGER AS $$
BEGIN
NEW.user_id = auth.uid();
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- 14. Create triggers to automatically set user_id
CREATE TRIGGER set_projects_user_id
BEFORE INSERT ON projects
FOR EACH ROW
EXECUTE FUNCTION set_user_id();
CREATE TRIGGER set_use_cases_user_id
BEFORE INSERT ON use_cases
FOR EACH ROW
EXECUTE FUNCTION set_user_id();
CREATE TRIGGER set_projections_user_id
BEFORE INSERT ON projections
FOR EACH ROW
EXECUTE FUNCTION set_user_id();
CREATE TRIGGER set_simulation_results_user_id
BEFORE INSERT ON simulation_results
FOR EACH ROW
EXECUTE FUNCTION set_user_id();
CREATE TRIGGER set_actual_metrics_user_id
BEFORE INSERT ON actual_metrics
FOR EACH ROW
EXECUTE FUNCTION set_user_id();
-- 15. Add comments explaining the security model
COMMENT ON COLUMN projects.user_id IS 'UUID of the user who owns this project';
COMMENT ON COLUMN use_cases.user_id IS 'UUID of the user who owns this use case';
COMMENT ON COLUMN projections.user_id IS 'UUID of the user who owns this projection';
COMMENT ON COLUMN simulation_results.user_id IS 'UUID of the user who owns this simulation result';
COMMENT ON COLUMN actual_metrics.user_id IS 'UUID of the user who owns this metric';
