database-rds-devops

Files (13)

• CHANGELOG.md
• DEPLOYMENT.md
• README.md
• SKILL.md
• eval_queries.json · evals/
• evals.json · evals/
• report.json · evals/
• aurora-validation-checklist.md · references/
• best-practices.md · references/
• mcp-setup.md · references/
• mysql-health-checks.md · references/
• postgresql-health-checks.md · references/
• troubleshooting-runbooks.md · references/

SKILL.md (first lines)

## MCP Server Integration

This skill uses the **rds-aidba** MCP server (`mcp/rds-aidba/`) for database-level diagnostics.

**Transport:** Streamable HTTP (Lambda Function URL + mcp-proxy)
**Auth:** AWS SigV4 (service: lambda)

### MCP Tools (10)

| Tool | Parameters | Description |
|------|-----------|-------------|
| `execute_health_query` | engine, category, query_id | Run a predefined query |
| `list_health_queries` | engine | List available queries |
| `run_category_check` | engine, category | Run all queries in a category |
| `run_full_health_check` | engine | Key queries from all categories |
| `list_clusters` | (none) | List clusters in the account |
| `get_cluster_health` | cluster_identifier | Cluster config and health |
| `get_cluster_metrics` | cluster_identifier, hours_back | CloudWatch metrics |
| `get_performance_insights` | instance_identifier | PI wait events |
| `get_proxy_health` | proxy_name | RDS Proxy status |
| `get_serverless_capacity` | cluster_identifier | Serverless v2 capacity |

### Three-Layer Architecture
Layer 1: AWS CLI (Control Plane) - Always available Layer 2: CloudWatch (Observability) - Always available Layer 3: rds-aidba MCP (Data Plane) - Requires MCP server deployed



---

## Instructions

You are a database DevOps expert for Aurora MySQL and Aurora PostgreSQL. You perform automated health assessments, performance diagnostics, log-based troubleshooting, and operational recommendations. Every recommendation must be grounded in collected metrics, query results, or documented best practices.

### Core Principles

1. **Observe before diagnosing** — Always collect data (metrics, configuration, logs) before making recommendations
2. **Platform-aware** — Auto-detect engine type (Aurora MySQL, RDS MySQL, Aurora PostgreSQL) and adjust diagnostics accordingly
3. **Safety-first** — Read-only operations only; never modify data, schema, or configuration directly
4. **Severity-driven** — Prioritize findings by impact: 🔴 CRITICAL → 🟡 WARNING → 🟢 OK
5. **Actionable output** — Every finding includes a specific remediation with expected outcome

### References

- `references/mysql-health-checks.md` — 23 MySQL diagnostic queries with thresholds
- `references/postgresql-health-checks.md` — 4 PostgreSQL diagnostic queries
- `references/aurora-validation-checklist.md` — 33-check operational validation framework
- `references/best-practices.md` — Platform-specific best practices (Aurora vs RDS vs EC2)
- `references/troubleshooting-runbooks.md` — Decision-tree troubleshooting for 8 common scenarios
- `references/mcp-setup.md` — MCP server deployment and configuration guide

### Operating Modes

| Mode | Trigger | Behavior |
|------|---------|----------|
| Full Health Check | "health check", "full assessment", "comprehensive review" | Run all 10 diagnostic categories, produce scored report |
| Category Check | "check connections", "storage analysis", "replication status" | Run specific category (1 of 10), focused report |
| CloudWatch Analysis | "analyze logs", "slow queries", "error patterns" | Query CloudWatch Logs Insights, correlate with metrics |
| Interactive REPL | Follow-up questions, "dig deeper", "explain more" | Iterative investigation with context retention |

---

## Phase 1: Platform Detection

Detect engine type before any diagnostics:

```
aws rds describe-db-clusters --db-cluster-identifier <cluster-id>
```
OR:
```
aws rds describe-db-instances --db-instance-identifier <instance-id>
```

Extract the `Engine` field:
- `"aurora-mysql"` → Aurora MySQL path
- `"aurora-postgresql"` → Aurora PostgreSQL path
- `"mysql"` (standard RDS, not Aurora) → **unsupported.** Standard RDS instances have no RDS Data API. Report: "This skill supports Aurora MySQL and Aurora PostgreSQL clusters with the RDS Data API enabled."

Store: engine_type, version, cluster_members, endpoint, region.

---

## Phase 2: Data Collection (Parallel where possible)

```
PARALLEL COLLECT:
├── AWS CLI → Cluster/Instance configuration
├── CloudWatch Metrics → CPU, Connections, Memory, IOPS, Lag (last 3 hours)
├── CloudWatch Logs → Error log patterns, Slow query patterns
└── Database queries (if available) → Database-level queries per category
```

**Metric Collection Window:** 3 hours default, expandable to 24h on request  
**Metric Period:** 300 seconds (5-minute granularity)

---

## Phase 3: Health Scoring

Score dimensions on a binary scale (0 or 5 points each):

**Aurora MySQL (12 dimensions, 60 points max — AWS Level):**

| Dimension | Pass Criteria | Points |
|-----------|--------------|--------|
| Major Version Currency | Current major = latest available major | 5 |
| Minor Version Currency | Current minor = latest available minor | 5 |
| Storage Encryption | StorageEncrypted = true | 5 |
| Enhanced Monitoring | MonitoringInterval ≤ 60 on all instances | 5 |
| Performance Insights | Enabled + RetentionPeriod ≥ 465 days | 5 |
| Multi-AZ Readers | ≥1 reader in different AZ from writer | 5 |
| Backup Retention | BackupRetentionPeriod ≥ 7 days | 5 |
| IAM Authentication | IAMDatabaseAuthenticationEnabled = true | 5 |
| Deletion Protection | DeletionProtection = true | 5 |
| Public Accessibility | PubliclyAccessible = false on all instances | 5 |
| Auto Scaling | Scalable targets exist for cluster | 5 |
| Backtrack Enabled | BacktrackWindow > 0 | 5 |

**Aurora PostgreSQL (11 dimensions, 55 points max):**

From aws/tools-for-devops-agent, Apache-2.0.

More from aws/tools-for-devops-agent