B システム設計/インフラ — Cloud SQL for PostgreSQL 本番HA基盤（Private IP × REGIONAL 自動フェイルオーバー × IAM DB 認証 パスワードレス × 自動バックアップ/PITR × deletion_protection + prevent_destroy × リードレプリカ読み取り分離 × Cloud SQL Auth Proxy + PgBouncer 接続プーリング）（MOps キャンペーンDB Bad→Good）

📋 コピー# ── VPC + Private Service Access（Private IP の前提）──────────
resource "google_compute_network" "vpc" {
  name                    = "mops-vpc"
  auto_create_subnetworks = false
}
resource "google_compute_global_address" "private_ip_range" {
  name          = "mops-sql-private-ip"
  purpose       = "VPC_PEERING"
  address_type  = "INTERNAL"
  prefix_length = 16
  network       = google_compute_network.vpc.id
}
resource "google_service_networking_connection" "private_vpc" {
  network                 = google_compute_network.vpc.id
  service                 = "servicenetworking.googleapis.com"
  reserved_peering_ranges = [google_compute_global_address.private_ip_range.name]
}

# ── Cloud SQL プライマリ（HA + Private IP + IAM 認証 + PITR）──
resource "google_sql_database_instance" "campaign_db" {
  name                = "mops-campaign-db"
  database_version    = "POSTGRES_16"
  region              = var.region
  deletion_protection = true                  # 修正⑤(1): API 側
  depends_on          = [google_service_networking_connection.private_vpc]

  settings {
    tier              = "db-custom-4-16384"
    availability_type = "REGIONAL"            # 修正②: HA + 自動フェイルオーバー
    disk_autoresize   = true

    ip_configuration {                        # 修正①: パブリックIP廃止
      ipv4_enabled                                  = false
      private_network                               = google_compute_network.vpc.id
      enable_private_path_for_google_cloud_services = true
      ssl_mode                                      = "ENCRYPTED_ONLY"
    }

    backup_configuration {                    # 修正④: バックアップ + PITR
      enabled                        = true
      start_time                     = "17:00"  # UTC（JST 02:00）
      point_in_time_recovery_enabled = true
      transaction_log_retention_days = 7
      backup_retention_settings {
        retained_backups = 14
        retention_unit   = "COUNT"
      }
    }

    database_flags {                          # 修正③: IAM DB 認証
      name  = "cloudsql.iam_authentication"
      value = "on"
    }
    insights_config { query_insights_enabled = true }
  }

  lifecycle { prevent_destroy = true }        # 修正⑤(2): Terraform 側
}

resource "google_sql_database" "campaign" {
  name     = "campaign"
  instance = google_sql_database_instance.campaign_db.name
}

# 修正③: IAM DB ユーザー（.gserviceaccount.com を除いた名前で登録）
resource "google_sql_user" "app_iam_user" {
  name     = "mops-campaign-app@${var.project_id}.iam"
  instance = google_sql_database_instance.campaign_db.name
  type     = "CLOUD_IAM_SERVICE_ACCOUNT"
}

# 修正⑥: リードレプリカ（読み取り分離）
resource "google_sql_database_instance" "campaign_db_replica" {
  name                 = "mops-campaign-db-ro"
  database_version     = "POSTGRES_16"
  region               = var.region
  master_instance_name = google_sql_database_instance.campaign_db.name
  deletion_protection  = true
  settings {
    tier              = "db-custom-2-8192"
    availability_type = "ZONAL"             # レプリカは ZONAL で可
    ip_configuration {
      ipv4_enabled    = false
      private_network = google_compute_network.vpc.id
    }
    database_flags { name = "cloudsql.iam_authentication"; value = "on" }
  }
  lifecycle { prevent_destroy = true }
}

# アプリ SA + 最小権限（Auth Proxy 接続 + IAM DB ログイン）
resource "google_service_account" "app_sa" {
  account_id = "mops-campaign-app"
}
resource "google_project_iam_member" "app_sql_client" {
  project = var.project_id
  role    = "roles/cloudsql.client"
  member  = "serviceAccount:${google_service_account.app_sa.email}"
}
resource "google_project_iam_member" "app_sql_instance_user" {
  project = var.project_id
  role    = "roles/cloudsql.instanceUser"
  member  = "serviceAccount:${google_service_account.app_sa.email}"
}
resource "google_service_account_iam_member" "app_wif" {  # 修正③: WIF
  service_account_id = google_service_account.app_sa.name
  role               = "roles/iam.workloadIdentityUser"
  member             = "serviceAccount:${var.project_id}.svc.id.goog[mops/campaign-app]"
}