Kubernetes Database Analyst

---
name: kubernetes-database-analyst
description: Analyzes data from databases in Kubernetes clusters by setting up port forwarding, executing SQL queries, and generating CSV reports.
---
# Instructions

## Phase 1: Initial Setup & Discovery
1.  **Read the Task:** Use `filesystem-read_file` to read the task file (e.g., `task.md`) to understand the analytical questions and required output format.
2.  **Inspect Kubernetes Resources:** Use `k8s-kubectl_get` to list all resources in the target namespace (e.g., `data`). Identify the database `Service` (e.g., `mysql-f1`) and its corresponding `Pod`.
3.  **Establish Database Connection:** Use `k8s-port_forward` to create a persistent port forward from a local port (e.g., `30124`) to the database service's target port (e.g., `3306`). **Note the `portForwardId`** to keep it alive.
4.  **List Workspace Files:** Use `filesystem-list_directory` to identify any template or input files in the workspace.

## Phase 2: Database Schema Exploration
5.  **Connect and Explore:** Use `local-python-execute` to run a Python script that:
    *   Connects to the forwarded database using provided credentials (host=`127.0.0.1`, port=`[LOCAL_PORT]`).
    *   Lists all tables to understand the data structure.
    *   Examines the schema of key tables relevant to the task (e.g., `drivers`, `constructors`, `results`, `races`, `standings` tables).

## Phase 3: Query Execution & Analysis
6.  **Develop and Test Queries:** Based on the task requirements, craft SQL queries within `local-python-execute` blocks. Test them iteratively to ensure correctness.
    *   For ranking/aggregation tasks (e.g., "highest points per year"), join `races`, `standings`, and dimension tables (`drivers`, `constructors`). Use window functions or subqueries with `GROUP BY` and `MAX()`.
    *   For complex conditional logic (e.g., "same constructor in first and last race"), use Common Table Expressions (CTEs) to break down the problem: filter by date/year, calculate first/last race per driver-season, count distinct rounds, and join on constructor ID.
7.  **Validate Results:** Check the row count and sample data of query results for logical consistency before writing to file.

## Phase 4: Report Generation & Cleanup
8.  **Write Results to CSV:** Use `local-python-execute` with the `csv` module to write the final query results to CSV files with the exact names specified in the task (e.g., `results_1.csv`, `results_2.csv`). Ensure headers match the template format.
9.  **Remove Template Files:** Use `local-python-execute` with `os.remove()` to delete any provided template files from the workspace as instructed.
10. **Verify Output:** Use `filesystem-read_multiple_files` to confirm the final CSV files exist and contain the expected data.
11. **Finalize:** The port forward started in Step 3 should remain active. Use `local-claim_done` to signal task completion.