""" 📦 DATABASE INITIALIZATION SCRIPT - UPDATED WITH ALL 7 MODELS """ import sys from pathlib import Path # Add project root to path project_root = Path(__file__).parent.parent sys.path.insert(0, str(project_root)) from sqlalchemy import create_engine, text from sqlalchemy.exc import OperationalError from database.config import DATABASE_CONFIG, init_database from database.models.base import Base # Import all 7 models from database.models.deployment_identity import DeploymentIdentity from database.models.model_registry import ModelRegistry from database.models.security_memory import SecurityMemory from database.models.autonomous_decisions import AutonomousDecision from database.models.policy_versions import PolicyVersion from database.models.operator_interactions import OperatorInteraction from database.models.system_health_history import SystemHealthHistory def create_database(): """Create database if it doesn't exist""" try: # First, connect to default PostgreSQL database admin_engine = create_engine(DATABASE_CONFIG.test_connection_string) with admin_engine.connect() as conn: # Check if database exists result = conn.execute( text("SELECT 1 FROM pg_database WHERE datname = :dbname"), {"dbname": DATABASE_CONFIG.database} ).fetchone() if not result: print(f"Creating database: {DATABASE_CONFIG.database}") conn.execute(text("COMMIT")) # Exit transaction conn.execute(text(f'CREATE DATABASE "{DATABASE_CONFIG.database}"')) print("✅ Database created") else: print(f"✅ Database already exists: {DATABASE_CONFIG.database}") except OperationalError as e: print(f"❌ Failed to connect to PostgreSQL: {e}") print("\n🔧 TROUBLESHOOTING:") print(" 1. Install PostgreSQL: https://www.postgresql.org/download/") print(" 2. Or use Docker: docker run --name security-db -p 5432:5432 -e POSTGRES_PASSWORD=postgres -d postgres") print(" 3. Verify PostgreSQL service is running") print(" 4. Update credentials in database/config.py if needed") return False return True def create_tables(): """Create all 7 tables in the database""" try: # Initialize database connection if not init_database(): print("❌ Failed to initialize database connection") return False # Create all tables Base.metadata.create_all(bind=DATABASE_CONFIG.engine) print("✅ All tables created successfully") # Count tables created table_count = len(Base.metadata.tables) print(f"📊 Tables created: {table_count}") # List all tables with DATABASE_CONFIG.engine.connect() as conn: result = conn.execute(text(""" SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name """)) tables = [row[0] for row in result] print("📋 Table list:") for table in tables: print(f" - {table}") return True except Exception as e: print(f"❌ Failed to create tables: {e}") import traceback traceback.print_exc() return False def create_initial_deployment(): """Create initial deployment identity""" from database.config import get_db_session import hashlib import platform import json from datetime import datetime with get_db_session() as session: # Check if deployment already exists existing = session.query(DeploymentIdentity).order_by(DeploymentIdentity.created_at.desc()).first() if existing: print(f"✅ Deployment already exists: {existing.deployment_id}") return existing # Create environment fingerprint env_data = { "platform": platform.platform(), "python_version": platform.python_version(), "hostname": platform.node(), "processor": platform.processor(), "init_time": datetime.utcnow().isoformat() } env_json = json.dumps(env_data, sort_keys=True) env_hash = hashlib.sha256(env_json.encode()).hexdigest() # Create new deployment deployment = DeploymentIdentity( environment_hash=env_hash, environment_summary=env_data, default_risk_posture="balanced", system_maturity_score=0.1, # Just starting policy_envelopes={ "max_aggressiveness": 0.7, "false_positive_tolerance": 0.3, "learning_enabled": True, "emergency_ceilings": { "confidence_threshold": 0.95, "block_rate": 0.5 } } ) session.add(deployment) session.commit() print(f"✅ Initial deployment created: {deployment.deployment_id}") print(f" Environment hash: {env_hash[:16]}...") print(f" Risk posture: {deployment.default_risk_posture}") print(f" Maturity score: {deployment.system_maturity_score}") return deployment def register_existing_models(): """Register existing models from Phase 4/5""" from database.config import get_db_session from database.models.model_registry import ModelRegistry with get_db_session() as session: # Check if models already registered existing_count = session.query(ModelRegistry).count() if existing_count > 0: print(f"✅ Models already registered: {existing_count}") return existing_count # Register Phase 5 ecosystem models models_to_register = [ { "model_id": "mnist_cnn_v1", "domain": "vision", "risk_tier": "medium", "confidence_baseline": 0.85, "robustness_baseline": 0.88, "inherited_intelligence_score": 0.1, "owner": "adversarial-ml-suite" }, { "model_id": "fraud_detector_v2", "domain": "tabular", "risk_tier": "critical", "confidence_baseline": 0.92, "robustness_baseline": 0.75, "inherited_intelligence_score": 0.3, "owner": "fraud-team" }, { "model_id": "sentiment_analyzer_v1", "domain": "text", "risk_tier": "high", "confidence_baseline": 0.88, "robustness_baseline": 0.70, "inherited_intelligence_score": 0.2, "owner": "nlp-team" }, { "model_id": "time_series_forecast_v3", "domain": "time_series", "risk_tier": "medium", "confidence_baseline": 0.85, "robustness_baseline": 0.65, "inherited_intelligence_score": 0.15, "owner": "forecasting-team" }, { "model_id": "vision_segmentation_v2", "domain": "vision", "risk_tier": "high", "confidence_baseline": 0.89, "robustness_baseline": 0.72, "inherited_intelligence_score": 0.25, "owner": "vision-team" } ] registered = 0 for model_data in models_to_register: model = ModelRegistry(**model_data) session.add(model) registered += 1 session.commit() print(f"✅ Registered {registered} models in database") # Show registered models models = session.query(ModelRegistry).all() print("📋 Registered models:") for model in models: print(f" - {model.model_id} ({model.domain}/{model.risk_tier})") return registered def create_initial_policies(): """Create initial policy versions""" from database.config import get_db_session from database.models.policy_versions import PolicyVersion import hashlib import json with get_db_session() as session: # Check if policies exist existing = session.query(PolicyVersion).count() if existing > 0: print(f"✅ Policies already exist: {existing}") return existing policies = [] # 1. Confidence Threshold Policy confidence_policy = { "model_confidence_threshold": 0.7, "emergency_confidence_threshold": 0.5, "confidence_drop_tolerance": 0.3 } content = { "policy_type": "confidence_threshold", "policy_scope": "global", "version": 1, "parameters": confidence_policy, "constraints": {"max_allowed_confidence_drop": 0.5} } version_hash = hashlib.sha256(json.dumps(content, sort_keys=True).encode()).hexdigest() policies.append(PolicyVersion( policy_type="confidence_threshold", policy_scope="global", version_number=1, version_hash=version_hash, policy_parameters=confidence_policy, policy_constraints={"max_allowed_confidence_drop": 0.5}, change_reason="Initial deployment", change_trigger="human_intervention" )) # 2. Rate Limiting Policy rate_policy = { "requests_per_minute": 100, "burst_capacity": 50, "emergency_rate_limit": 20 } content = { "policy_type": "rate_limiting", "policy_scope": "global", "version": 1, "parameters": rate_policy, "constraints": {"min_requests_per_minute": 1} } version_hash = hashlib.sha256(json.dumps(content, sort_keys=True).encode()).hexdigest() policies.append(PolicyVersion( policy_type="rate_limiting", policy_scope="global", version_number=1, version_hash=version_hash, policy_parameters=rate_policy, policy_constraints={"min_requests_per_minute": 1}, change_reason="Initial deployment", change_trigger="human_intervention" )) # Add all policies for policy in policies: session.add(policy) session.commit() print(f"✅ Created {len(policies)} initial policies") return len(policies) def main(): """Main initialization routine""" print("\n" + "="*80) print("🧠 DATABASE INITIALIZATION - SECURITY NERVOUS SYSTEM (7 TABLES)") print("="*80) # Step 1: Create database print("\n1️⃣ CHECKING/CREATING DATABASE...") if not create_database(): return False # Step 2: Create tables print("\n2️⃣ CREATING 7 TABLES...") if not create_tables(): return False # Step 3: Create initial deployment print("\n3️⃣ CREATING DEPLOYMENT IDENTITY...") deployment = create_initial_deployment() if not deployment: return False # Step 4: Register existing models print("\n4️⃣ REGISTERING EXISTING MODELS...") model_count = register_existing_models() # Step 5: Create initial policies print("\n5️⃣ CREATING INITIAL POLICIES...") policy_count = create_initial_policies() print("\n" + "="*80) print("✅ DATABASE INITIALIZATION COMPLETE") print("="*80) print(f"Deployment ID: {deployment.deployment_id}") print(f"Models registered: {model_count}") print(f"Policies created: {policy_count}") print(f"Tables ready: 7 core tables") print("\n📋 TABLE SCHEMA SUMMARY:") print(" 1. deployment_identity - Personalization per installation") print(" 2. model_registry - Model governance across domains") print(" 3. security_memory - Compressed threat experience") print(" 4. autonomous_decisions - Autonomous decision audit trail") print(" 5. policy_versions - Governance over time") print(" 6. operator_interactions - Human-aware security") print(" 7. system_health_history - Self-healing diagnostics") print("\n🚀 Database layer is now operational for Phase 5") return True if __name__ == "__main__": success = main() sys.exit(0 if success else 1)