# ==============================================================================
# ADTLAS DAI MANAGEMENT SYSTEM - DATABASE ALERTING RULES
# ==============================================================================
#
# This file contains Prometheus alerting rules for monitoring database
# components of the Adtlas DAI Management System including PostgreSQL
# and Redis performance, availability, and resource usage.
#
# Categories:
# - PostgreSQL Health and Performance
# - Redis Health and Performance
# - Connection Pool Monitoring
# - Query Performance
# - Replication Monitoring
# - Backup and Recovery
#
# Author: Adtlas Development Team
# Version: 2.0.0
# Last Updated: 2025-01-20
# ==============================================================================

groups:
  # ==============================================================================
  # POSTGRESQL HEALTH AND PERFORMANCE
  # ==============================================================================
  
  - name: postgresql_health
    interval: 30s
    rules:
      # PostgreSQL instance down
      - alert: PostgreSQLDown
        expr: pg_up == 0
        for: 1m
        labels:
          severity: critical
          service: postgresql
          component: database
        annotations:
          summary: "PostgreSQL instance is down"
          description: "PostgreSQL instance {{ $labels.instance }} is down"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-down"
          dashboard_url: "http://grafana:3000/d/postgresql-overview"
      
      # PostgreSQL too many connections
      - alert: PostgreSQLTooManyConnections
        expr: |
          (
            pg_stat_database_numbackends / pg_settings_max_connections
          ) * 100 > 80
        for: 5m
        labels:
          severity: warning
          service: postgresql
          component: connections
        annotations:
          summary: "PostgreSQL too many connections"
          description: "PostgreSQL instance {{ $labels.instance }} is using more than 80% of max connections. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-too-many-connections"
      
      # PostgreSQL high connection usage
      - alert: PostgreSQLHighConnectionUsage
        expr: |
          (
            pg_stat_database_numbackends / pg_settings_max_connections
          ) * 100 > 95
        for: 2m
        labels:
          severity: critical
          service: postgresql
          component: connections
        annotations:
          summary: "PostgreSQL critical connection usage"
          description: "PostgreSQL instance {{ $labels.instance }} is using more than 95% of max connections. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-critical-connections"
      
      # PostgreSQL slow queries
      - alert: PostgreSQLSlowQueries
        expr: |
          (
            rate(pg_stat_statements_mean_time_ms[5m]) > 1000
          )
        for: 5m
        labels:
          severity: warning
          service: postgresql
          component: query-performance
        annotations:
          summary: "PostgreSQL slow queries detected"
          description: "PostgreSQL instance {{ $labels.instance }} has queries with mean execution time > 1000ms. Current: {{ $value }}ms"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-slow-queries"
      
      # PostgreSQL high CPU usage
      - alert: PostgreSQLHighCPUUsage
        expr: |
          (
            rate(pg_stat_bgwriter_checkpoint_write_time[5m]) +
            rate(pg_stat_bgwriter_checkpoint_sync_time[5m])
          ) > 0.8
        for: 5m
        labels:
          severity: warning
          service: postgresql
          component: performance
        annotations:
          summary: "PostgreSQL high CPU usage"
          description: "PostgreSQL instance {{ $labels.instance }} has high checkpoint CPU usage. Current: {{ $value }}"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-high-cpu"
      
      # PostgreSQL deadlocks
      - alert: PostgreSQLDeadlocks
        expr: rate(pg_stat_database_deadlocks[5m]) > 0.1
        for: 1m
        labels:
          severity: warning
          service: postgresql
          component: concurrency
        annotations:
          summary: "PostgreSQL deadlocks detected"
          description: "PostgreSQL instance {{ $labels.instance }} database {{ $labels.datname }} has deadlocks. Rate: {{ $value }} deadlocks/s"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-deadlocks"
      
      # PostgreSQL high rollback rate
      - alert: PostgreSQLHighRollbackRate
        expr: |
          (
            rate(pg_stat_database_xact_rollback[5m]) /
            (rate(pg_stat_database_xact_commit[5m]) + rate(pg_stat_database_xact_rollback[5m]))
          ) * 100 > 10
        for: 5m
        labels:
          severity: warning
          service: postgresql
          component: transactions
        annotations:
          summary: "PostgreSQL high rollback rate"
          description: "PostgreSQL instance {{ $labels.instance }} database {{ $labels.datname }} has high rollback rate. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-high-rollback"
      
      # PostgreSQL cache hit ratio low
      - alert: PostgreSQLLowCacheHitRatio
        expr: |
          (
            pg_stat_database_blks_hit / (pg_stat_database_blks_hit + pg_stat_database_blks_read)
          ) * 100 < 95
        for: 10m
        labels:
          severity: warning
          service: postgresql
          component: cache
        annotations:
          summary: "PostgreSQL low cache hit ratio"
          description: "PostgreSQL instance {{ $labels.instance }} database {{ $labels.datname }} has low cache hit ratio. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-low-cache-hit"
      
      # PostgreSQL table bloat
      - alert: PostgreSQLTableBloat
        expr: pg_stat_user_tables_n_dead_tup / (pg_stat_user_tables_n_live_tup + pg_stat_user_tables_n_dead_tup) * 100 > 20
        for: 30m
        labels:
          severity: warning
          service: postgresql
          component: maintenance
        annotations:
          summary: "PostgreSQL table bloat detected"
          description: "PostgreSQL table {{ $labels.schemaname }}.{{ $labels.relname }} has high bloat ratio. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-table-bloat"

  # ==============================================================================
  # POSTGRESQL STORAGE AND CAPACITY
  # ==============================================================================
  
  - name: postgresql_storage
    interval: 30s
    rules:
      # PostgreSQL database size growth
      - alert: PostgreSQLDatabaseSizeGrowth
        expr: |
          (
            increase(pg_database_size_bytes[1h]) / 1024 / 1024 / 1024
          ) > 1
        for: 0s
        labels:
          severity: warning
          service: postgresql
          component: storage
        annotations:
          summary: "PostgreSQL database rapid growth"
          description: "PostgreSQL database {{ $labels.datname }} grew by more than 1GB in the last hour. Growth: {{ $value }}GB"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-rapid-growth"
      
      # PostgreSQL WAL files accumulation
      - alert: PostgreSQLWALFilesAccumulation
        expr: pg_stat_archiver_archived_count - pg_stat_archiver_failed_count < -10
        for: 5m
        labels:
          severity: warning
          service: postgresql
          component: wal
        annotations:
          summary: "PostgreSQL WAL files accumulating"
          description: "PostgreSQL instance {{ $labels.instance }} has WAL files accumulating. Difference: {{ $value }}"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-wal-accumulation"
      
      # PostgreSQL checkpoint frequency
      - alert: PostgreSQLHighCheckpointFrequency
        expr: rate(pg_stat_bgwriter_checkpoints_req[5m]) > 0.1
        for: 5m
        labels:
          severity: warning
          service: postgresql
          component: checkpoints
        annotations:
          summary: "PostgreSQL high checkpoint frequency"
          description: "PostgreSQL instance {{ $labels.instance }} has high requested checkpoint frequency. Rate: {{ $value }} checkpoints/s"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-high-checkpoint-freq"

  # ==============================================================================
  # REDIS HEALTH AND PERFORMANCE
  # ==============================================================================
  
  - name: redis_health
    interval: 30s
    rules:
      # Redis instance down
      - alert: RedisDown
        expr: redis_up == 0
        for: 1m
        labels:
          severity: critical
          service: redis
          component: cache
        annotations:
          summary: "Redis instance is down"
          description: "Redis instance {{ $labels.instance }} is down"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-down"
          dashboard_url: "http://grafana:3000/d/redis-overview"
      
      # Redis high memory usage
      - alert: RedisHighMemoryUsage
        expr: |
          (
            redis_memory_used_bytes / redis_memory_max_bytes
          ) * 100 > 80
        for: 5m
        labels:
          severity: warning
          service: redis
          component: memory
        annotations:
          summary: "Redis high memory usage"
          description: "Redis instance {{ $labels.instance }} memory usage is above 80%. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-high-memory"
      
      # Redis critical memory usage
      - alert: RedisCriticalMemoryUsage
        expr: |
          (
            redis_memory_used_bytes / redis_memory_max_bytes
          ) * 100 > 95
        for: 2m
        labels:
          severity: critical
          service: redis
          component: memory
        annotations:
          summary: "Redis critical memory usage"
          description: "Redis instance {{ $labels.instance }} memory usage is above 95%. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-critical-memory"
      
      # Redis high CPU usage
      - alert: RedisHighCPUUsage
        expr: rate(redis_cpu_sys_seconds_total[5m]) + rate(redis_cpu_user_seconds_total[5m]) > 0.8
        for: 5m
        labels:
          severity: warning
          service: redis
          component: cpu
        annotations:
          summary: "Redis high CPU usage"
          description: "Redis instance {{ $labels.instance }} CPU usage is high. Current: {{ $value }}"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-high-cpu"
      
      # Redis too many connections
      - alert: RedisTooManyConnections
        expr: redis_connected_clients > 100
        for: 5m
        labels:
          severity: warning
          service: redis
          component: connections
        annotations:
          summary: "Redis too many connections"
          description: "Redis instance {{ $labels.instance }} has too many connections. Current: {{ $value }}"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-too-many-connections"
      
      # Redis rejected connections
      - alert: RedisRejectedConnections
        expr: rate(redis_rejected_connections_total[5m]) > 0
        for: 1m
        labels:
          severity: critical
          service: redis
          component: connections
        annotations:
          summary: "Redis rejecting connections"
          description: "Redis instance {{ $labels.instance }} is rejecting connections. Rate: {{ $value }} rejections/s"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-rejected-connections"
      
      # Redis slow log entries
      - alert: RedisSlowLogEntries
        expr: rate(redis_slowlog_length[5m]) > 0.1
        for: 5m
        labels:
          severity: warning
          service: redis
          component: performance
        annotations:
          summary: "Redis slow log entries detected"
          description: "Redis instance {{ $labels.instance }} has slow log entries. Rate: {{ $value }} entries/s"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-slow-log"
      
      # Redis keyspace hit ratio low
      - alert: RedisLowHitRatio
        expr: |
          (
            redis_keyspace_hits_total / (redis_keyspace_hits_total + redis_keyspace_misses_total)
          ) * 100 < 80
        for: 10m
        labels:
          severity: warning
          service: redis
          component: cache-efficiency
        annotations:
          summary: "Redis low hit ratio"
          description: "Redis instance {{ $labels.instance }} has low hit ratio. Current: {{ $value }}%"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-low-hit-ratio"
      
      # Redis high eviction rate
      - alert: RedisHighEvictionRate
        expr: rate(redis_evicted_keys_total[5m]) > 10
        for: 5m
        labels:
          severity: warning
          service: redis
          component: memory-management
        annotations:
          summary: "Redis high eviction rate"
          description: "Redis instance {{ $labels.instance }} has high key eviction rate. Rate: {{ $value }} evictions/s"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-high-eviction"
      
      # Redis expired keys rate
      - alert: RedisHighExpiredKeysRate
        expr: rate(redis_expired_keys_total[5m]) > 100
        for: 5m
        labels:
          severity: info
          service: redis
          component: key-management
        annotations:
          summary: "Redis high expired keys rate"
          description: "Redis instance {{ $labels.instance }} has high expired keys rate. Rate: {{ $value }} expirations/s"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-high-expired-keys"

  # ==============================================================================
  # REDIS PERSISTENCE AND REPLICATION
  # ==============================================================================
  
  - name: redis_persistence
    interval: 30s
    rules:
      # Redis RDB save errors
      - alert: RedisRDBSaveErrors
        expr: redis_rdb_last_save_timestamp_seconds < (time() - 3600)
        for: 5m
        labels:
          severity: warning
          service: redis
          component: persistence
        annotations:
          summary: "Redis RDB save errors"
          description: "Redis instance {{ $labels.instance }} hasn't saved RDB for more than 1 hour"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-rdb-save-errors"
      
      # Redis AOF rewrite in progress too long
      - alert: RedisAOFRewriteTooLong
        expr: redis_aof_rewrite_in_progress == 1
        for: 30m
        labels:
          severity: warning
          service: redis
          component: persistence
        annotations:
          summary: "Redis AOF rewrite taking too long"
          description: "Redis instance {{ $labels.instance }} AOF rewrite has been in progress for more than 30 minutes"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-aof-rewrite-long"
      
      # Redis master-slave sync issues
      - alert: RedisMasterSlaveSyncIssues
        expr: redis_master_repl_offset - redis_slave_repl_offset > 1000000
        for: 5m
        labels:
          severity: warning
          service: redis
          component: replication
        annotations:
          summary: "Redis master-slave sync issues"
          description: "Redis slave {{ $labels.instance }} is lagging behind master by {{ $value }} bytes"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-sync-issues"

  # ==============================================================================
  # CONNECTION POOL MONITORING
  # ==============================================================================
  
  - name: connection_pool_monitoring
    interval: 30s
    rules:
      # Django database connection pool exhaustion
      - alert: DjangoDBConnectionPoolExhaustion
        expr: django_db_connections_total > django_db_connections_max * 0.9
        for: 2m
        labels:
          severity: critical
          service: django
          component: database-pool
        annotations:
          summary: "Django database connection pool near exhaustion"
          description: "Django database connection pool is using more than 90% of available connections. Current: {{ $value }}"
          runbook_url: "https://docs.adtlas.com/runbooks/django-db-pool-exhaustion"
      
      # Redis connection pool issues
      - alert: RedisConnectionPoolIssues
        expr: redis_connected_clients > redis_config_maxclients * 0.8
        for: 5m
        labels:
          severity: warning
          service: redis
          component: connection-pool
        annotations:
          summary: "Redis connection pool issues"
          description: "Redis is using more than 80% of max clients. Current: {{ $value }}"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-connection-pool-issues"

  # ==============================================================================
  # BACKUP AND RECOVERY MONITORING
  # ==============================================================================
  
  - name: backup_recovery_monitoring
    interval: 60s
    rules:
      # PostgreSQL backup age
      - alert: PostgreSQLBackupAge
        expr: (time() - pg_stat_archiver_last_archived_time) / 3600 > 24
        for: 1h
        labels:
          severity: warning
          service: postgresql
          component: backup
        annotations:
          summary: "PostgreSQL backup is old"
          description: "PostgreSQL instance {{ $labels.instance }} last backup is older than 24 hours. Age: {{ $value }} hours"
          runbook_url: "https://docs.adtlas.com/runbooks/postgresql-backup-age"
      
      # Redis backup age
      - alert: RedisBackupAge
        expr: (time() - redis_rdb_last_save_timestamp_seconds) / 3600 > 24
        for: 1h
        labels:
          severity: warning
          service: redis
          component: backup
        annotations:
          summary: "Redis backup is old"
          description: "Redis instance {{ $labels.instance }} last RDB save is older than 24 hours. Age: {{ $value }} hours"
          runbook_url: "https://docs.adtlas.com/runbooks/redis-backup-age"

# ==============================================================================
# DATABASE RULE CONFIGURATION NOTES
# ==============================================================================
#
# These database alerting rules monitor:
# 1. PostgreSQL health, performance, and resource usage
# 2. Redis health, performance, and cache efficiency
# 3. Connection pool management and exhaustion
# 4. Query performance and slow operations
# 5. Replication lag and synchronization issues
# 6. Backup and recovery status
# 7. Storage growth and capacity planning
#
# Key metrics monitored:
# - Connection counts and pool usage
# - Query execution times and slow queries
# - Cache hit ratios and efficiency
# - Memory and CPU usage
# - Replication lag and errors
# - Backup freshness and integrity
# - Storage growth and bloat
#
# Alert thresholds are set for production environments:
# - Connection usage: 80% warning, 95% critical
# - Memory usage: 80% warning, 95% critical
# - Cache hit ratio: <95% for PostgreSQL, <80% for Redis
# - Query time: >1000ms for slow queries
# - Backup age: >24 hours
#
# Customization recommendations:
# 1. Adjust thresholds based on your workload patterns
# 2. Add database-specific metrics for your schema
# 3. Configure appropriate retention policies
# 4. Set up automated remediation for common issues
# 5. Create detailed runbooks for each alert type
# ==============================================================================