65 lines
2.8 KiB
SQL
65 lines
2.8 KiB
SQL
-- Fix Schema Conflicts and Prepare for Clean Deployment
|
|
-- This script resolves duplicate key constraint violations
|
|
|
|
-- Create n8n schema if it doesn't exist
|
|
CREATE SCHEMA IF NOT EXISTS n8n;
|
|
|
|
-- Clean up any existing conflicting types/tables
|
|
DROP TYPE IF EXISTS claude_recommendations CASCADE;
|
|
DROP TABLE IF EXISTS claude_recommendations CASCADE;
|
|
|
|
-- Clean up n8n related tables if they exist in public schema
|
|
DROP TABLE IF EXISTS public.n8n_credentials_entity CASCADE;
|
|
DROP TABLE IF EXISTS public.n8n_execution_entity CASCADE;
|
|
DROP TABLE IF EXISTS public.n8n_workflow_entity CASCADE;
|
|
DROP TABLE IF EXISTS public.n8n_webhook_entity CASCADE;
|
|
DROP TABLE IF EXISTS public.n8n_tag_entity CASCADE;
|
|
DROP TABLE IF EXISTS public.n8n_workflows_tags CASCADE;
|
|
|
|
-- Reset any conflicting sequences
|
|
DROP SEQUENCE IF EXISTS claude_recommendations_id_seq CASCADE;
|
|
|
|
-- Ensure proper permissions for n8n schema
|
|
GRANT ALL PRIVILEGES ON SCHEMA n8n TO pipeline_admin;
|
|
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA n8n TO pipeline_admin;
|
|
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA n8n TO pipeline_admin;
|
|
|
|
-- Create claude_recommendations table properly (if needed by services)
|
|
CREATE TABLE IF NOT EXISTS claude_recommendations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
request_id VARCHAR(255) UNIQUE NOT NULL,
|
|
domain VARCHAR(100) NOT NULL,
|
|
budget DECIMAL(10,2) NOT NULL,
|
|
preferred_technologies TEXT[],
|
|
template_id UUID,
|
|
stack_name VARCHAR(255) NOT NULL,
|
|
monthly_cost DECIMAL(10,2) NOT NULL,
|
|
setup_cost DECIMAL(10,2) NOT NULL,
|
|
team_size VARCHAR(50) NOT NULL,
|
|
development_time INTEGER NOT NULL,
|
|
satisfaction INTEGER NOT NULL CHECK (satisfaction >= 0 AND satisfaction <= 100),
|
|
success_rate INTEGER NOT NULL CHECK (success_rate >= 0 AND success_rate <= 100),
|
|
frontend VARCHAR(100) NOT NULL,
|
|
backend VARCHAR(100) NOT NULL,
|
|
database VARCHAR(100) NOT NULL,
|
|
cloud VARCHAR(100) NOT NULL,
|
|
testing VARCHAR(100) NOT NULL,
|
|
mobile VARCHAR(100),
|
|
devops VARCHAR(100) NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_claude_recommendations_request_id ON claude_recommendations(request_id);
|
|
CREATE INDEX IF NOT EXISTS idx_claude_recommendations_domain ON claude_recommendations(domain);
|
|
CREATE INDEX IF NOT EXISTS idx_claude_recommendations_created_at ON claude_recommendations(created_at);
|
|
|
|
-- Ensure proper ownership
|
|
ALTER TABLE claude_recommendations OWNER TO pipeline_admin;
|
|
|
|
-- Log the cleanup
|
|
INSERT INTO schema_migrations (service, version, description, applied_at)
|
|
VALUES ('database-cleanup', 'fix_schema_conflicts', 'Fixed schema conflicts and prepared for clean deployment', CURRENT_TIMESTAMP)
|
|
ON CONFLICT (service, version) DO NOTHING;
|