Dennis
50e25e3ee8
Rename subdirectories for a cleaner single-repo layout: - website-monitoring-backend/ → backend/ - website-monitoring-frontend/ → frontend/ - website-monitoring-devops/ → devops/ Update all references in package.json scripts, CI workflows, docker-compose, pre-commit hooks, and documentation. Co-authored-by: Copilot <223556219+Copilot@users.noreply.github.com>
87 lines
4.4 KiB
PL/PgSQL
87 lines
4.4 KiB
PL/PgSQL
-- Add missing enum types
|
|
DO $$ BEGIN
|
|
CREATE TYPE scan_status AS ENUM ('pending', 'queued', 'running', 'completed', 'failed', 'cancelled');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE severity_level AS ENUM ('low', 'medium', 'high', 'critical');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE comparison_operator AS ENUM ('equals', 'not_equals', 'greater_than', 'less_than', 'contains', 'not_contains');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN null;
|
|
END $$;
|
|
|
|
-- Add missing columns to scans table
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS scheduled_at TIMESTAMP WITH TIME ZONE;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS trigger_type TEXT DEFAULT 'manual';
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS website_id UUID REFERENCES websites(id);
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS triggered_by UUID REFERENCES auth.users(id);
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS scan_type TEXT DEFAULT 'lighthouse';
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS priority INTEGER DEFAULT 5;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS categories TEXT[] DEFAULT ARRAY['performance', 'seo', 'accessibility', 'best_practices'];
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS device_type TEXT DEFAULT 'desktop';
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS user_agent TEXT;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS lighthouse_version TEXT;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS chrome_version TEXT;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS environment TEXT DEFAULT 'production';
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS started_at TIMESTAMP WITH TIME ZONE;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS completed_at TIMESTAMP WITH TIME ZONE;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS duration_ms INTEGER;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS error_message TEXT;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS retry_count INTEGER DEFAULT 0;
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}';
|
|
ALTER TABLE scans ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();
|
|
|
|
-- Add missing columns to pages table
|
|
ALTER TABLE pages ADD COLUMN IF NOT EXISTS status scan_status DEFAULT 'pending';
|
|
ALTER TABLE pages ADD COLUMN IF NOT EXISTS last_scanned_at TIMESTAMP WITH TIME ZONE;
|
|
ALTER TABLE pages ADD COLUMN IF NOT EXISTS scan_count INTEGER DEFAULT 0;
|
|
ALTER TABLE pages ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}';
|
|
|
|
-- Add missing columns to websites table
|
|
ALTER TABLE websites ADD COLUMN IF NOT EXISTS crawl_settings JSONB DEFAULT '{}';
|
|
ALTER TABLE websites ADD COLUMN IF NOT EXISTS alert_settings JSONB DEFAULT '{}';
|
|
ALTER TABLE websites ADD COLUMN IF NOT EXISTS last_crawled_at TIMESTAMP WITH TIME ZONE;
|
|
ALTER TABLE websites ADD COLUMN IF NOT EXISTS crawl_session_id UUID;
|
|
ALTER TABLE websites ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}';
|
|
|
|
-- Add missing columns to users table
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS organization_id UUID REFERENCES organizations(id);
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS role TEXT DEFAULT 'user';
|
|
ALTER TABLE users ADD COLUMN IF NOT EXISTS preferences JSONB DEFAULT '{}';
|
|
|
|
-- Add missing columns to organizations table
|
|
ALTER TABLE organizations ADD COLUMN IF NOT EXISTS settings JSONB DEFAULT '{}';
|
|
ALTER TABLE organizations ADD COLUMN IF NOT EXISTS subscription_plan TEXT DEFAULT 'free';
|
|
ALTER TABLE organizations ADD COLUMN IF NOT EXISTS subscription_status TEXT DEFAULT 'active';
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_scans_website_id ON scans(website_id);
|
|
CREATE INDEX IF NOT EXISTS idx_scans_status ON scans(status);
|
|
CREATE INDEX IF NOT EXISTS idx_scans_created_at ON scans(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_pages_website_id ON pages(website_id);
|
|
CREATE INDEX IF NOT EXISTS idx_pages_status ON pages(status);
|
|
CREATE INDEX IF NOT EXISTS idx_users_organization_id ON users(organization_id);
|
|
|
|
-- Add triggers for updated_at
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
CREATE TRIGGER update_scans_updated_at BEFORE UPDATE ON scans FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
CREATE TRIGGER update_websites_updated_at BEFORE UPDATE ON websites FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
CREATE TRIGGER update_pages_updated_at BEFORE UPDATE ON pages FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Refresh schema cache
|
|
NOTIFY pgrst, 'reload schema';
|