infrastructure/terraform/modules/rds/main.tf

# =============================================================================
# RDS Module — Production-Grade PostgreSQL with Security-First Design
# =============================================================================
# Features:
#   - Multi-AZ deployment
#   - Encryption at rest (KMS)
#   - Encryption in transit (force SSL)
#   - Private subnets only (no public access)
#   - Automated backups with cross-region replica
#   - Performance Insights enabled
#   - Enhanced Monitoring enabled
#   - Deletion protection enabled
#   - Automated major version upgrade controlled
# =============================================================================

resource "aws_db_subnet_group" "this" {
  name       = "${var.name}-db-subnet-group"
  subnet_ids = var.database_subnet_ids

  tags = merge(var.tags, {
    Name = "${var.name}-db-subnet-group"
  })
}

resource "aws_rds_cluster" "this" {
  count = var.engine_mode == "serverless" ? 1 : 0

  cluster_identifier     = "${var.name}-aurora"
  engine                 = var.engine
  engine_version         = var.engine_version
  engine_mode            = var.engine_mode
  database_name          = var.database_name
  master_username        = var.master_username
  master_password        = var.master_password
  db_subnet_group_name   = aws_db_subnet_group.this.name
  vpc_security_group_ids = [aws_security_group.rds.id]

  storage_encrypted = true
  kms_key_id        = var.kms_key_arn

  backup_retention_period = var.backup_retention_period
  preferred_backup_window = "03:00-05:00"

  deletion_protection      = true
  skip_final_snapshot      = false
  final_snapshot_identifier = "${var.name}-final-snapshot"

  enable_http_endpoint = var.engine_mode == "serverless"

  tags = merge(var.tags, {
    Name = "${var.name}-aurora-cluster"
  })
}

resource "aws_db_instance" "this" {
  count = var.engine_mode != "serverless" ? 1 : 0

  identifier     = "${var.name}-postgres"
  engine         = var.engine
  engine_version = var.engine_version
  instance_class = var.instance_class

  allocated_storage     = var.allocated_storage
  storage_type          = "gp3"
  storage_encrypted    = true
  kms_key_id           = var.kms_key_arn

  db_name  = var.database_name
  username = var.master_username
  password = var.master_password

  multi_az = var.multi_az

  db_subnet_group_name   = aws_db_subnet_group.this.name
  vpc_security_group_ids = [aws_security_group.rds.id]

  backup_retention_period = var.backup_retention_period
  preferred_backup_window = "03:00-05:00"
  backup_target           = "region"

  deletion_protection      = true
  skip_final_snapshot      = false
  final_snapshot_identifier = "${var.name}-final-snapshot"

  performance_insights_enabled          = true
  performance_insights_kms_key_id       = var.kms_key_arn
  performance_insights_retention_period = 7

  monitoring_interval = 30
  monitoring_role_arn = aws_iam_role.rds_monitoring.arn

  enabled_cloudwatch_logs_exports = ["postgresql", "upgrade"]

  auto_minor_version_upgrade = true
  major_engine_version_auto_upgrade = false # Controlled manually

  tags = merge(var.tags, {
    Name = "${var.name}-postgres"
  })
}

# ---------- Security Group: RDS ----------
resource "aws_security_group" "rds" {
  name        = "${var.name}-rds-sg"
  description = "RDS security group - restrict ingress to app tier"
  vpc_id      = var.vpc_id

  # Only allow ingress from application security group
  ingress {
    description     = "PostgreSQL from app tier"
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    security_groups = var.allowed_security_group_ids
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = merge(var.tags, {
    Name = "${var.name}-rds-sg"
  })
}

# ---------- RDS Enhanced Monitoring Role ----------
resource "aws_iam_role" "rds_monitoring" {
  name = "${var.name}-rds-monitoring-role"

  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [{
      Action = "sts:AssumeRole"
      Effect = "Allow"
      Principal = {
        Service = "monitoring.rds.amazonaws.com"
      }
    }]
  })
}

resource "aws_iam_role_policy_attachment" "rds_monitoring" {
  policy_arn = "arn:aws:iam::aws:policy/service-role/AmazonRDSEnhancedMonitoringRole"
  role       = aws_iam_role.rds_monitoring.name
}