security_demo/init-db.sql

-- AI SOC Database Initialization Script
-- This script initializes the PostgreSQL database for AI SOC Analyst platform
-- This script runs when PostgreSQL container first starts

\set ON_ERROR_STOP on
\echo '🚀 Starting AI SOC database initialization...'

-- Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- Full-text search support
CREATE EXTENSION IF NOT EXISTS "btree_gin"; -- Composite index support

-- Set timezone
SET timezone = 'UTC';

-- Create schema if not exists (for better organization)
CREATE SCHEMA IF NOT EXISTS public;

-- Grant privileges
GRANT ALL PRIVILEGES ON SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO postgres;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO postgres;

-- Default privileges for future objects
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO postgres;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO postgres;

-- Performance tuning for security analytics workload
-- Note: These settings should be applied at the server level
-- or through ALTER DATABASE if the database already exists
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET random_page_cost = 1.1;
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET effective_cache_size = '4GB';
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET work_mem = '64MB';
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET maintenance_work_mem = '256MB';
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET max_parallel_workers_per_gather = 2;
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET effective_io_concurrency = 200;

-- Query optimization settings
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET enable_partitionwise_join = on;
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET enable_partitionwise_aggregate = on;

-- Logging configuration
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET log_statement = 'mod';
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET log_min_duration_statement = 1000;
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET log_connections = on;
ALTER DATABASE ${POSTGRES_DB:-ai_soc} SET log_disconnections = on;

-- Create helper function for safe index creation
CREATE OR REPLACE FUNCTION create_index_if_table_exists(
    idx_name TEXT,
    tbl_name TEXT,
    idx_definition TEXT
) RETURNS VOID AS $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_schema = 'public'
        AND table_name = tbl_name
    ) THEN
        EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I %s',
            idx_name, tbl_name, idx_definition);
        RAISE NOTICE 'Index % created on table %', idx_name, tbl_name;
    ELSE
        RAISE NOTICE 'Table % does not exist, skipping index %', tbl_name, idx_name;
    END IF;
EXCEPTION WHEN OTHERS THEN
    RAISE WARNING 'Failed to create index % on table %: %', idx_name, tbl_name, SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Create performance monitoring views
CREATE OR REPLACE VIEW v_table_stats AS
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size,
    n_tup_ins AS inserts,
    n_tup_upd AS updates,
    n_tup_del AS deletes
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Create slow query monitoring view
CREATE OR REPLACE VIEW v_slow_queries AS
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- queries slower than 1 second
ORDER BY mean_exec_time DESC
LIMIT 20;

-- Success message
DO $$
BEGIN
    RAISE NOTICE '✅ AI SOC database initialized successfully';
    RAISE NOTICE 'Database: %', current_database();
    RAISE NOTICE 'Timezone: UTC';
    RAISE NOTICE 'Extensions: uuid-ossp, pg_trgm, btree_gin';

    -- Log table creation
    RAISE NOTICE 'Checking database initialization...';

    -- Verify database is ready
    PERFORM 1;  -- Simple test query

    RAISE NOTICE '🎉 Database initialization completed - Database is ready for connections!';
END $$;