N8N Automation for Database Operations: Complete Guide for DBAs

const dbaAutomationBenefits = {
  visualWorkflows: "No-code/low-code workflow creation",
  databaseConnectors: "Native support for major databases",
  scheduling: "Cron-based workflow execution",
  errorHandling: "Built-in retry and error management",
  integrations: "200+ pre-built integrations",
  selfHosted: "Complete control over your automation",
  apiIntegration: "RESTful API for external triggers"
};

# Create N8N directory structure
mkdir -p ~/n8n-dba/{data,workflows,credentials}
cd ~/n8n-dba

# Create docker-compose.yml
cat > docker-compose.yml << 'EOF'
version: '3.8'
services:
  n8n:
    image: n8nio/n8n:latest
    container_name: n8n-dba
    ports:
      - "5678:5678"
    environment:
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=admin
      - N8N_BASIC_AUTH_PASSWORD=securepassword123
      - N8N_HOST=localhost
      - N8N_PROTOCOL=http
      - N8N_PORT=5678
      - WEBHOOK_URL=http://localhost:5678/
      - GENERIC_TIMEZONE=UTC
    volumes:
      - ./data:/home/node/.n8n
      - ./workflows:/home/node/workflows
      - /var/run/docker.sock:/var/run/docker.sock
    restart: unless-stopped
    
  # Optional: PostgreSQL for N8N data storage
  postgres:
    image: postgres:13
    container_name: n8n-postgres
    environment:
      POSTGRES_DB: n8n
      POSTGRES_USER: n8n
      POSTGRES_PASSWORD: n8n_password
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: unless-stopped

volumes:
  postgres_data:
EOF

# Start N8N
docker-compose up -d

# Check status
docker-compose ps

// Database connection credentials in N8N
const databaseCredentials = {
  postgresql: {
    host: "your-postgres-host",
    port: 5432,
    database: "your_database",
    user: "monitoring_user",
    password: "secure_password",
    ssl: true,
    allowUnauthorizedCerts: false
  },
  
  mysql: {
    host: "your-mysql-host", 
    port: 3306,
    database: "information_schema",
    user: "monitoring_user",
    password: "secure_password"
  },
  
  mongodb: {
    connectionString: "mongodb://monitoring_user:password@host:27017/admin"
  }
};

{
  "name": "PostgreSQL Health Monitor",
  "nodes": [
    {
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "minutes",
              "minutesInterval": 15
            }
          ]
        }
      },
      "name": "Every 15 Minutes",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [240, 300]
    },
    {
      "parameters": {
        "operation": "executeQuery",
        "query": "SELECT \n  current_database() as database_name,\n  version() as pg_version,\n  pg_database_size(current_database()) as db_size_bytes,\n  (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') as active_connections,\n  (SELECT count(*) FROM pg_stat_activity) as total_connections,\n  (SELECT setting FROM pg_settings WHERE name = 'max_connections') as max_connections,\n  (SELECT count(*) FROM pg_locks WHERE mode = 'ExclusiveLock') as exclusive_locks,\n  now() as check_time"
      },
      "name": "Database Health Query",
      "type": "n8n-nodes-base.postgres",
      "typeVersion": 1,
      "position": [460, 300],
      "credentials": {
        "postgres": "postgresql-monitoring"
      }
    },
    {
      "parameters": {
        "conditions": {
          "number": [
            {
              "value1": "={{$json[\"active_connections\"]}}",
              "operation": "larger",
              "value2": "={{Math.floor($json[\"max_connections\"] * 0.8)}}"
            }
          ]
        }
      },
      "name": "High Connection Alert",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [680, 300]
    },
    {
      "parameters": {
        "subject": "PostgreSQL High Connection Alert",
        "message": "Database: {{$node[\"Database Health Query\"].json[\"database_name\"]}}\nActive Connections: {{$node[\"Database Health Query\"].json[\"active_connections\"]}}\nMax Connections: {{$node[\"Database Health Query\"].json[\"max_connections\"]}}\nConnection Usage: {{Math.round(($node[\"Database Health Query\"].json[\"active_connections\"] / $node[\"Database Health Query\"].json[\"max_connections\"]) * 100)}}%",
        "options": {}
      },
      "name": "Send Alert Email",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 1,
      "position": [900, 200],
      "credentials": {
        "smtp": "smtp-alerts"
      }
    }
  ],
  "connections": {
    "Every 15 Minutes": {
      "main": [
        [
          {
            "node": "Database Health Query",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Database Health Query": {
      "main": [
        [
          {
            "node": "High Connection Alert",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "High Connection Alert": {
      "main": [
        [
          {
            "node": "Send Alert Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

// MySQL performance metrics collection workflow
const mysqlMonitoringWorkflow = {
  trigger: "cron: 0 */10 * * * *", // Every 10 minutes
  
  steps: [
    {
      node: "MySQL Query",
      query: `
        SELECT 
          'connections' as metric_type,
          variable_name,
          variable_value,
          NOW() as timestamp
        FROM performance_schema.global_status 
        WHERE variable_name IN (
          'Threads_connected', 'Threads_running', 'Max_used_connections',
          'Aborted_connects', 'Connection_errors_max_connections'
        )
        
        UNION ALL
        
        SELECT 
          'innodb' as metric_type,
          variable_name,
          variable_value,
          NOW() as timestamp
        FROM performance_schema.global_status 
        WHERE variable_name IN (
          'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads',
          'Innodb_buffer_pool_pages_dirty', 'Innodb_buffer_pool_pages_free'
        )
        
        UNION ALL
        
        SELECT 
          'queries' as metric_type,
          variable_name,
          variable_value,
          NOW() as timestamp
        FROM performance_schema.global_status 
        WHERE variable_name IN (
          'Queries', 'Slow_queries', 'Com_select', 'Com_insert', 
          'Com_update', 'Com_delete'
        )
      `
    },
    {
      node: "Store Metrics",
      action: "insert_into_influxdb", // or your preferred time-series DB
      database: "mysql_metrics",
      measurement: "performance_stats"
    },
    {
      node: "Check Thresholds",
      conditions: [
        {
          metric: "buffer_pool_hit_ratio",
          operator: "<",
          threshold: 0.99,
          alert: "Buffer pool hit ratio below 99%"
        },
        {
          metric: "slow_query_rate", 
          operator: ">",
          threshold: 5,
          alert: "Slow query rate exceeding 5 per minute"
        }
      ]
    }
  ]
};

// PostgreSQL backup workflow with validation
const postgresBackupWorkflow = {
  name: "PostgreSQL Automated Backup",
  schedule: "0 2 * * *", // Daily at 2 AM
  
  workflow: [
    {
      node: "Pre-backup Check",
      type: "postgres",
      query: `
        SELECT 
          pg_database_size(current_database()) as db_size,
          pg_is_in_recovery() as is_replica,
          version() as pg_version,
          current_timestamp as backup_start_time
      `
    },
    {
      node: "Execute Backup",
      type: "executeCommand",
      command: `
        #!/bin/bash
        BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
        BACKUP_FILE="/backups/postgres_{{$json.database_name}}_${BACKUP_DATE}.sql"
        
        # Create backup
        pg_dump -h {{$credentials.postgres.host}} \\
                -U {{$credentials.postgres.user}} \\
                -d {{$credentials.postgres.database}} \\
                --verbose --clean --if-exists \\
                --format=custom \\
                --file="${BACKUP_FILE}"
        
        # Compress backup
        gzip "${BACKUP_FILE}"
        
        # Verify backup
        if [ -f "${BACKUP_FILE}.gz" ]; then
          BACKUP_SIZE=$(stat -f%z "${BACKUP_FILE}.gz" 2>/dev/null || stat -c%s "${BACKUP_FILE}.gz")
          echo "{"
          echo "  \\"backup_file\\": \\"${BACKUP_FILE}.gz\\","
          echo "  \\"backup_size\\": ${BACKUP_SIZE},"
          echo "  \\"backup_date\\": \\"${BACKUP_DATE}\\","
          echo "  \\"status\\": \\"success\\""
          echo "}"
        else
          echo "{"
          echo "  \\"status\\": \\"failed\\","
          echo "  \\"error\\": \\"Backup file not created\\""
          echo "}"
          exit 1
        fi
      `
    },
    {
      node: "Upload to S3",
      type: "aws-s3",
      operation: "upload",
      bucketName: "database-backups",
      fileName: "{{$json.backup_file}}",
      binaryData: true
    },
    {
      node: "Update Backup Log",
      type: "postgres",
      query: `
        INSERT INTO backup_log (
          database_name, backup_date, backup_file, backup_size, 
          backup_duration, status, s3_location
        ) VALUES (
          '{{$json.database_name}}',
          '{{$json.backup_date}}',
          '{{$json.backup_file}}',
          {{$json.backup_size}},
          EXTRACT(EPOCH FROM (NOW() - '{{$json.backup_start_time}}'::timestamp)),
          '{{$json.status}}',
          's3://database-backups/{{$json.backup_file}}'
        )
      `
    },
    {
      node: "Cleanup Old Backups",
      type: "executeCommand",
      command: `
        # Remove local backups older than 7 days
        find /backups -name "postgres_*.sql.gz" -mtime +7 -delete
        
        # Remove S3 backups older than 30 days
        aws s3api list-objects-v2 --bucket database-backups \\
          --query 'Contents[?LastModified<=\`$(date -d "30 days ago" -Iseconds)\`].Key' \\
          --output text | xargs -I {} aws s3 rm s3://database-backups/{}
      `
    },
    {
      node: "Send Success Notification",
      type: "slack",
      message: `
        ✅ PostgreSQL Backup Completed Successfully
        
        Database: {{$json.database_name}}
        Backup Size: {{Math.round($json.backup_size / 1024 / 1024)}} MB
        Duration: {{Math.round($json.backup_duration)}} seconds
        Location: s3://database-backups/{{$json.backup_file}}
        
        Backup validation: ✅ Passed
      `
    }
  ],
  
  errorHandling: {
    node: "Send Failure Alert",
    type: "email",
    subject: "❌ PostgreSQL Backup Failed",
    message: `
      Backup failed for database: {{$json.database_name}}
      Error: {{$json.error}}
      Time: {{new Date().toISOString()}}
      
      Please investigate immediately.
    `
  }
};

// MongoDB backup workflow
const mongoBackupWorkflow = {
  trigger: "webhook", // Can be triggered via API or schedule
  
  steps: [
    {
      node: "MongoDB Backup",
      type: "executeCommand",
      command: `
        #!/bin/bash
        BACKUP_DATE=$(date +%Y%m%d_%H%M%S)
        BACKUP_DIR="/backups/mongodb_${BACKUP_DATE}"
        
        # Create backup directory
        mkdir -p "${BACKUP_DIR}"
        
        # Execute mongodump
        mongodump --host {{$credentials.mongodb.host}} \\
                  --port {{$credentials.mongodb.port}} \\
                  --username {{$credentials.mongodb.username}} \\
                  --password {{$credentials.mongodb.password}} \\
                  --out "${BACKUP_DIR}" \\
                  --gzip
        
        # Create archive
        tar -czf "${BACKUP_DIR}.tar.gz" -C "/backups" "mongodb_${BACKUP_DATE}"
        
        # Cleanup directory
        rm -rf "${BACKUP_DIR}"
        
        # Output backup info
        BACKUP_SIZE=$(stat -c%s "${BACKUP_DIR}.tar.gz")
        echo "{"
        echo "  \\"backup_file\\": \\"${BACKUP_DIR}.tar.gz\\","
        echo "  \\"backup_size\\": ${BACKUP_SIZE},"
        echo "  \\"backup_date\\": \\"${BACKUP_DATE}\\""
        echo "}"
      `
    },
    {
      node: "Validate Backup",
      type: "executeCommand", 
      command: `
        # Extract and validate backup
        TEMP_DIR="/tmp/mongo_validate_$(date +%s)"
        mkdir -p "${TEMP_DIR}"
        
        tar -xzf "{{$json.backup_file}}" -C "${TEMP_DIR}"
        
        # Check if databases exist in backup
        DATABASES=$(find "${TEMP_DIR}" -type d -name "*.metadata.json" | wc -l)
        
        if [ ${DATABASES} -gt 0 ]; then
          echo '{"validation": "passed", "databases_found": '${DATABASES}'}'
        else
          echo '{"validation": "failed", "error": "No databases found in backup"}'
          exit 1
        fi
        
        # Cleanup
        rm -rf "${TEMP_DIR}"
      `
    }
  ]
};

{
  "name": "Database Alert Manager",
  "nodes": [
    {
      "parameters": {
        "httpMethod": "POST",
        "path": "database-alert",
        "options": {}
      },
      "name": "Alert Webhook",
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 1,
      "position": [240, 300]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json[\"body\"][\"severity\"]}}",
              "value2": "critical"
            }
          ]
        }
      },
      "name": "Check Severity",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [460, 300]
    },
    {
      "parameters": {
        "resource": "message",
        "channel": "database-alerts",
        "text": "🚨 CRITICAL DATABASE ALERT\n\nServer: {{$json[\"body\"][\"server\"]}}\nDatabase: {{$json[\"body\"][\"database\"]}}\nAlert: {{$json[\"body\"][\"message\"]}}\nTime: {{$json[\"body\"][\"timestamp\"]}}\n\n@channel",
        "otherOptions": {
          "includeLinkToWorkflow": false
        }
      },
      "name": "Slack Critical Alert",
      "type": "n8n-nodes-base.slack",
      "typeVersion": 1,
      "position": [680, 200],
      "credentials": {
        "slackApi": "slack-dba-alerts"
      }
    },
    {
      "parameters": {
        "to": "[email protected]",
        "subject": "CRITICAL: Database Alert - {{$json[\"body\"][\"server\"]}}",
        "message": "Critical database alert detected:\n\nServer: {{$json[\"body\"][\"server\"]}}\nDatabase: {{$json[\"body\"][\"database\"]}}\nMetric: {{$json[\"body\"][\"metric\"]}}\nValue: {{$json[\"body\"][\"value\"]}}\nThreshold: {{$json[\"body\"][\"threshold\"]}}\nMessage: {{$json[\"body\"][\"message\"]}}\nTimestamp: {{$json[\"body\"][\"timestamp\"]}}\n\nImmediate attention required.",
        "options": {}
      },
      "name": "Email Critical Alert",
      "type": "n8n-nodes-base.emailSend",
      "typeVersion": 1,
      "position": [680, 300],
      "credentials": {
        "smtp": "smtp-alerts"
      }
    },
    {
      "parameters": {
        "message": "Database Alert: {{$json[\"body\"][\"message\"]}}",
        "title": "{{$json[\"body\"][\"server\"]}} - {{$json[\"body\"][\"database\"]}}",
        "priority": 2
      },
      "name": "PagerDuty Alert",
      "type": "n8n-nodes-base.pagerDuty",
      "typeVersion": 1,
      "position": [680, 400],
      "credentials": {
        "pagerDutyApi": "pagerduty-dba"
      }
    }
  ]
}

// Intelligent alert escalation workflow
const alertEscalationWorkflow = {
  trigger: "webhook",
  
  logic: [
    {
      node: "Alert Deduplication",
      type: "function",
      code: `
        const alertKey = \`\${$json.server}_\${$json.metric}_\${$json.database}\`;
        const now = new Date().getTime();
        const cooldown = 30 * 60 * 1000; // 30 minutes
        
        // Check if similar alert was sent recently
        const lastAlert = $context.get(alertKey);
        
        if (lastAlert && (now - lastAlert) < cooldown) {
          return {
            skip: true,
            reason: "Alert in cooldown period",
            last_sent: new Date(lastAlert).toISOString()
          };
        }
        
        // Store current alert timestamp
        $context.set(alertKey, now);
        
        return {
          skip: false,
          alert_key: alertKey,
          ...$json
        };
      `
    },
    {
      node: "Severity Assessment",
      type: "function", 
      code: `
        const { metric, value, threshold, server } = $json;
        let severity = 'info';
        let escalation_level = 1;
        
        // Define severity rules
        const severityRules = {
          'cpu_usage': {
            warning: 80,
            critical: 95
          },
          'memory_usage': {
            warning: 85,
            critical: 95
          },
          'disk_usage': {
            warning: 85,
            critical: 95
          },
          'connection_count': {
            warning: 80, // % of max_connections
            critical: 95
          },
          'replication_lag': {
            warning: 60, // seconds
            critical: 300
          }
        };
        
        const rules = severityRules[metric];
        if (rules) {
          if (value >= rules.critical) {
            severity = 'critical';
            escalation_level = 3;
          } else if (value >= rules.warning) {
            severity = 'warning';
            escalation_level = 2;
          }
        }
        
        return {
          ...($json),
          severity,
          escalation_level,
          formatted_message: \`\${metric.toUpperCase()}: \${value}\${metric.includes('usage') ? '%' : ''} (threshold: \${threshold}\${metric.includes('usage') ? '%' : ''})\`
        };
      `
    },
    {
      node: "Route by Severity",
      type: "switch",
      conditions: [
        {
          case: "critical",
          actions: ["slack_critical", "email_critical", "pagerduty"]
        },
        {
          case: "warning", 
          actions: ["slack_warning", "email_warning"]
        },
        {
          case: "info",
          actions: ["slack_info"]
        }
      ]
    }
  ]
};

// Automated performance data collection for dashboards
const performanceDashboardWorkflow = {
  schedule: "*/2 * * * *", // Every 2 minutes
  
  collectors: [
    {
      name: "PostgreSQL Metrics",
      query: `
        WITH metrics AS (
          SELECT 
            'connections' as category,
            'active' as metric_name,
            count(*) as metric_value
          FROM pg_stat_activity 
          WHERE state = 'active'
          
          UNION ALL
          
          SELECT 
            'connections' as category,
            'total' as metric_name, 
            count(*) as metric_value
          FROM pg_stat_activity
          
          UNION ALL
          
          SELECT 
            'performance' as category,
            'tps' as metric_name,
            (SELECT sum(xact_commit + xact_rollback) / 60.0 
             FROM pg_stat_database 
             WHERE datname = current_database()) as metric_value
             
          UNION ALL
          
          SELECT 
            'locks' as category,
            'exclusive' as metric_name,
            count(*) as metric_value
          FROM pg_locks 
          WHERE mode LIKE '%Exclusive%'
          
          UNION ALL
          
          SELECT 
            'size' as category,
            'database_mb' as metric_name,
            pg_database_size(current_database()) / 1024 / 1024 as metric_value
        )
        SELECT 
          category,
          metric_name,
          metric_value,
          NOW() as timestamp,
          '{{$credentials.postgres.host}}' as server,
          current_database() as database_name
        FROM metrics
      `
    },
    {
      name: "MySQL Metrics", 
      query: `
        SELECT 
          'connections' as category,
          'threads_connected' as metric_name,
          VARIABLE_VALUE as metric_value,
          NOW() as timestamp,
          @@hostname as server
        FROM performance_schema.global_status 
        WHERE VARIABLE_NAME = 'Threads_connected'
        
        UNION ALL
        
        SELECT 
          'performance' as category,
          'qps' as metric_name,
          VARIABLE_VALUE as metric_value,
          NOW() as timestamp,
          @@hostname as server
        FROM performance_schema.global_status 
        WHERE VARIABLE_NAME = 'Queries'
        
        UNION ALL
        
        SELECT 
          'innodb' as category,
          'buffer_pool_hit_ratio' as metric_name,
          ROUND(
            (1 - (reads.VARIABLE_VALUE / requests.VARIABLE_VALUE)) * 100, 2
          ) as metric_value,
          NOW() as timestamp,
          @@hostname as server
        FROM 
          performance_schema.global_status reads,
          performance_schema.global_status requests
        WHERE 
          reads.VARIABLE_NAME = 'Innodb_buffer_pool_reads'
          AND requests.VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
      `
    }
  ],
  
  storage: {
    type: "influxdb",
    database: "database_metrics",
    measurement: "performance_stats",
    tags: ["server", "database_name", "category"],
    fields: ["metric_name", "metric_value"]
  }
};

// Automated slow query detection workflow
const slowQueryAnalysisWorkflow = {
  name: "Slow Query Detector",
  schedule: "0 */6 * * *", // Every 6 hours
  
  steps: [
    {
      node: "Collect Slow Queries",
      type: "postgres",
      query: `
        SELECT 
          query,
          calls,
          total_time,
          mean_time,
          rows,
          100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
          queryid
        FROM pg_stat_statements 
        WHERE mean_time > 1000 -- queries slower than 1 second
        ORDER BY total_time DESC 
        LIMIT 20
      `
    },
    {
      node: "Analyze Query Patterns",
      type: "function",
      code: `
        const queries = $json;
        const analysis = {
          total_slow_queries: queries.length,
          top_offender: queries[0],
          patterns: {},
          recommendations: []
        };
        
        // Analyze query patterns
        queries.forEach(query => {
          // Extract query type
          const queryType = query.query.trim().split(' ')[0].toUpperCase();
          if (!analysis.patterns[queryType]) {
            analysis.patterns[queryType] = {
              count: 0,
              total_time: 0,
              queries: []
            };
          }
          
          analysis.patterns[queryType].count++;
          analysis.patterns[queryType].total_time += query.total_time;
          analysis.patterns[queryType].queries.push(query);
          
          // Generate recommendations
          if (query.hit_percent < 95) {
            analysis.recommendations.push({
              query_id: query.queryid,
              issue: "Low buffer cache hit ratio",
              suggestion: "Consider adding indexes or optimizing query"
            });
          }
          
          if (query.mean_time > 5000) {
            analysis.recommendations.push({
              query_id: query.queryid,
              issue: "Very slow average execution time",
              suggestion: "Review query execution plan and consider optimization"
            });
          }
        });
        
        return analysis;
      `
    },
    {
      node: "Generate Report",
      type: "function",
      code: `
        const analysis = $json;
        
        let report = \`# Slow Query Analysis Report
        
Generated: \${new Date().toISOString()}

## Summary
- Total slow queries detected: \${analysis.total_slow_queries}
- Top offender execution time: \${Math.round(analysis.top_offender?.total_time || 0)}ms

## Query Patterns\`;

        Object.entries(analysis.patterns).forEach(([type, data]) => {
          report += \`
### \${type} Queries
- Count: \${data.count}
- Total time: \${Math.round(data.total_time)}ms
- Average time: \${Math.round(data.total_time / data.count)}ms\`;
        });
        
        if (analysis.recommendations.length > 0) {
          report += \`

## Recommendations\`;
          analysis.recommendations.forEach((rec, index) => {
            report += \`
\${index + 1}. **Query ID \${rec.query_id}**
   - Issue: \${rec.issue}
   - Suggestion: \${rec.suggestion}\`;
          });
        }
        
        return {
          report,
          analysis,
          timestamp: new Date().toISOString()
        };
      `
    },
    {
      node: "Send Report",
      type: "slack",
      channel: "database-performance",
      message: "{{$json.report}}"
    }
  ]
};

// PostgreSQL index maintenance workflow
const indexMaintenanceWorkflow = {
  name: "PostgreSQL Index Maintenance",
  schedule: "0 3 * * 0", // Weekly on Sunday at 3 AM
  
  workflow: [
    {
      node: "Analyze Index Usage",
      type: "postgres",
      query: `
        WITH index_stats AS (
          SELECT 
            schemaname,
            tablename,
            indexname,
            idx_tup_read,
            idx_tup_fetch,
            pg_relation_size(indexrelid) as index_size,
            pg_stat_get_live_tuples(indexrelid) as live_tuples
          FROM pg_stat_user_indexes
        ),
        table_stats AS (
          SELECT 
            schemaname,
            tablename,
            seq_scan,
            seq_tup_read,
            idx_scan,
            idx_tup_fetch as table_idx_tup_fetch
          FROM pg_stat_user_tables
        )
        SELECT 
          i.schemaname,
          i.tablename,
          i.indexname,
          i.idx_tup_read,
          i.idx_tup_fetch,
          i.index_size,
          t.seq_scan,
          t.idx_scan,
          CASE 
            WHEN i.idx_tup_read = 0 THEN 'UNUSED'
            WHEN i.idx_tup_read < 1000 AND i.index_size > 1048576 THEN 'RARELY_USED'
            ELSE 'ACTIVE'
          END as usage_category,
          pg_size_pretty(i.index_size) as size_pretty
        FROM index_stats i
        JOIN table_stats t ON i.schemaname = t.schemaname AND i.tablename = t.tablename
        ORDER BY i.index_size DESC
      `
    },
    {
      node: "Identify Maintenance Actions",
      type: "function",
      code: `
        const indexes = $json;
        const actions = {
          reindex: [],
          drop_candidates: [],
          analyze_needed: []
        };
        
        indexes.forEach(idx => {
          // Identify indexes that need reindexing (large, heavily used)
          if (idx.usage_category === 'ACTIVE' && idx.index_size > 100 * 1024 * 1024) {
            actions.reindex.push({
              schema: idx.schemaname,
              table: idx.tablename,
              index: idx.indexname,
              size: idx.index_size,
              reason: 'Large active index - reindex for performance'
            });
          }
          
          // Identify unused indexes for potential dropping
          if (idx.usage_category === 'UNUSED' && idx.index_size > 10 * 1024 * 1024) {
            actions.drop_candidates.push({
              schema: idx.schemaname,
              table: idx.tablename,
              index: idx.indexname,
              size: idx.index_size,
              reason: 'Large unused index - consider dropping'
            });
          }
          
          // Tables that might need ANALYZE
          if (idx.seq_scan > idx.idx_scan * 10) {
            actions.analyze_needed.push({
              schema: idx.schemaname,
              table: idx.tablename,
              reason: 'High sequential scan ratio - may need ANALYZE'
            });
          }
        });
        
        return actions;
      `
    },
    {
      node: "Execute Maintenance",
      type: "postgres",
      query: `
        -- Reindex critical indexes during maintenance window
        {{#each $json.reindex}}
        REINDEX INDEX CONCURRENTLY {{this.schema}}.{{this.index}};
        {{/each}}
        
        -- Analyze tables that need statistics update
        {{#each $json.analyze_needed}}
        ANALYZE {{this.schema}}.{{this.table}};
        {{/each}}
        
        -- Log maintenance actions
        INSERT INTO maintenance_log (
          maintenance_date, 
          maintenance_type, 
          details, 
          executed_by
        ) VALUES (
          NOW(),
          'index_maintenance',
          '{{json $json}}',
          'n8n_automation'
        );
      `
    },
    {
      node: "Generate Maintenance Report",
      type: "function",
      code: `
        const actions = $json;
        
        let report = \`# Database Index Maintenance Report
        
Executed: \${new Date().toISOString()}

## Actions Performed\`;

        if (actions.reindex.length > 0) {
          report += \`

### Reindexed Indexes (\${actions.reindex.length})
\`;
          actions.reindex.forEach(action => {
            report += \`- \${action.schema}.\${action.index} (\${Math.round(action.size / 1024 / 1024)}MB)
\`;
          });
        }
        
        if (actions.analyze_needed.length > 0) {
          report += \`

### Tables Analyzed (\${actions.analyze_needed.length})
\`;
          actions.analyze_needed.forEach(action => {
            report += \`- \${action.schema}.\${action.table}
\`;
          });
        }
        
        if (actions.drop_candidates.length > 0) {
          report += \`

### Unused Index Candidates for Review (\${actions.drop_candidates.length})
\`;
          actions.drop_candidates.forEach(action => {
            report += \`- \${action.schema}.\${action.index} (\${Math.round(action.size / 1024 / 1024)}MB) - \${action.reason}
\`;
          });
        }
        
        return {
          report,
          summary: {
            reindexed: actions.reindex.length,
            analyzed: actions.analyze_needed.length,
            drop_candidates: actions.drop_candidates.length
          }
        };
      `
    }
  ]
};

// Automatically provision Grafana dashboards based on detected databases
const grafanaProvisioningWorkflow = {
  trigger: "database_discovery",
  
  steps: [
    {
      node: "Discover Databases",
      type: "postgres",
      query: `
        SELECT 
          datname as database_name,
          pg_database_size(datname) as size_bytes,
          (SELECT count(*) FROM pg_stat_activity WHERE datname = d.datname) as connections
        FROM pg_database d 
        WHERE datistemplate = false 
        AND datname != 'postgres'
      `
    },
    {
      node: "Generate Dashboard JSON",
      type: "function",
      code: `
        const databases = $json;
        
        const dashboardTemplate = {
          dashboard: {
            id: null,
            title: "Database Overview - Auto Generated",
            tags: ["database", "auto-generated"],
            timezone: "browser",
            panels: [],
            time: {
              from: "now-1h",
              to: "now"
            },
            refresh: "30s"
          }
        };
        
        // Generate panels for each database
        databases.forEach((db, index) => {
          const panel = {
            id: index + 1,
            title: \`\${db.database_name} - Connections\`,
            type: "stat",
            targets: [{
              expr: \`postgresql_connections{database="\${db.database_name}"}\`,
              refId: "A"
            }],
            fieldConfig: {
              defaults: {
                thresholds: {
                  steps: [
                    { color: "green", value: null },
                    { color: "yellow", value: 50 },
                    { color: "red", value: 80 }
                  ]
                }
              }
            },
            gridPos: {
              h: 8,
              w: 12,
              x: (index % 2) * 12,
              y: Math.floor(index / 2) * 8
            }
          };
          
          dashboardTemplate.dashboard.panels.push(panel);
        });
        
        return dashboardTemplate;
      `
    },
    {
      node: "Deploy to Grafana",
      type: "httpRequest",
      method: "POST",
      url: "{{$credentials.grafana.url}}/api/dashboards/db",
      headers: {
        "Authorization": "Bearer {{$credentials.grafana.token}}",
        "Content-Type": "application/json"
      },
      body: "{{$json}}"
    }
  ]
};

# Generate Terraform configuration based on database requirements
apiVersion: v1
kind: ConfigMap
metadata:
  name: n8n-terraform-generator
data:
  workflow.json: |
    {
      "name": "Database Infrastructure Provisioning",
      "trigger": "webhook",
      "workflow": [
        {
          "node": "Parse Requirements",
          "type": "function",
          "code": "const requirements = $json.body; return { database_type: requirements.type, environment: requirements.env, size: requirements.size, backup_retention: requirements.backup_days || 30 };"
        },
        {
          "node": "Generate Terraform",
          "type": "function", 
          "code": "const config = $json; const terraform = `resource \"aws_db_instance\" \"${config.environment}_${config.database_type}\" { identifier = \"${config.environment}-${config.database_type}\" engine = \"${config.database_type}\" instance_class = \"${config.size}\" allocated_storage = 100 backup_retention_period = ${config.backup_retention} }`; return { terraform_config: terraform };"
        },
        {
          "node": "Commit to Repository",
          "type": "git",
          "operation": "commit",
          "repository": "infrastructure-configs",
          "file_path": "databases/{{$json.environment}}-{{$json.database_type}}.tf",
          "content": "{{$json.terraform_config}}"
        }
      ]
    }

// Secure credential rotation workflow
const credentialRotationWorkflow = {
  name: "Database Credential Rotation",
  schedule: "0 2 1 * *", // Monthly on the 1st at 2 AM
  
  steps: [
    {
      node: "Generate New Password",
      type: "function",
      code: `
        const crypto = require('crypto');
        const newPassword = crypto.randomBytes(16).toString('base64');
        
        return {
          new_password: newPassword,
          rotation_date: new Date().toISOString()
        };
      `
    },
    {
      node: "Update Database User",
      type: "postgres",
      query: `
        ALTER USER monitoring_user WITH PASSWORD '{{$json.new_password}}';
      `
    },
    {
      node: "Update N8N Credentials",
      type: "httpRequest",
      method: "PUT",
      url: "{{$credentials.n8n.url}}/api/v1/credentials/{{$credentials.postgres_monitoring.id}}",
      headers: {
        "Authorization": "Bearer {{$credentials.n8n.token}}"
      },
      body: {
        name: "postgresql-monitoring",
        type: "postgres",
        data: {
          host: "{{$credentials.postgres.host}}",
          database: "{{$credentials.postgres.database}}",
          user: "monitoring_user",
          password: "{{$json.new_password}}"
        }
      }
    },
    {
      node: "Test New Credentials",
      type: "postgres",
      query: "SELECT version() as test_query",
      credentials: "postgresql-monitoring"
    },
    {
      node: "Log Rotation",
      type: "postgres",
      query: `
        INSERT INTO credential_rotation_log (
          service_name, 
          rotation_date, 
          status, 
          rotated_by
        ) VALUES (
          'postgresql-monitoring',
          '{{$json.rotation_date}}',
          'success',
          'n8n_automation'
        )
      `
    }
  ]
};