Rds

Aurora PostgreSQL Skill

Read-only Aurora PostgreSQL exploration and business analysis via IAM authentication. No passwords or secrets required — your AWS IAM identity is your credential. Cross-platform (Mac + Windows). Works with any AI coding agent.

All scripts are in ${CLAUDE_SKILL_DIR}/scripts/ and require Python 3, AWS CLI, and psycopg2.

Python Command

Read ~/.rds-skill/config.json and use the "python" key as the Python command. If config doesn't exist yet, try python3 --version first, falling back to python --version. Throughout this document, PYTHON means the detected Python command.

First-Time Setup

You cannot run the setup wizard directly — it requires interactive terminal input.

Check if ~/.rds-skill/config.json exists:

• If it exists: Read it to discover the configured connections (the connections map and the default key).
• If it doesn't exist: Tell the user to run the setup wizard in their terminal:

Run this in your terminal to configure the Aurora connection:

python3 scripts/setup.py

(On Windows, use python instead of python3)

Wait for the user to confirm setup is complete before running any queries.

Connection Selection

The skill supports multiple named connections (e.g. prod, staging, local). Config shape:

{
  "default": "prod",
  "connections": {
    "prod": { "host": "...", "database": "main", "db_user": "...", "region": "eu-west-1", "profile": "...", "write_mode": "reject" },
    "staging": { "...": "..." }
  },
  "python": "/usr/bin/python3"
}

• Every script accepts --connection NAME to pick a specific connection.
• Without --connection, scripts use the default connection.
• To list connections: PYTHON ${CLAUDE_SKILL_DIR}/scripts/setup.py --list
• To switch defaults: PYTHON ${CLAUDE_SKILL_DIR}/scripts/setup.py --set-default NAME
• To remove one: PYTHON ${CLAUDE_SKILL_DIR}/scripts/setup.py --remove NAME
• To add another: re-run PYTHON ${CLAUDE_SKILL_DIR}/scripts/setup.py (interactive — instruct the user to run it themselves).

When the user mentions "prod" / "staging" / a specific cluster name, map that to the matching connection and pass --connection NAME on every script invocation. If they don't specify, use the default and mention which one you're using.

Quick Reference

Common options (all RDS scripts)

Output and File Saving

All query results are automatically saved to ~/rds-exports/query-{timestamp}.csv. The terminal shows an aligned txt preview (first 200 rows). The saved file defaults to CSV for spreadsheet compatibility.

This means you always have:

• Inline preview (200 rows in txt format) — enough to understand the data shape and answer quick questions
• Full CSV on disk — for deeper analysis with analyze.py or for the user to open in a spreadsheet

--format controls terminal display (default: txt). --save-format controls the saved file format (default: csv). Use --save-sql to also save the SQL query as a matching .sql file. Use --no-save to skip auto-save. Use --save=PATH to save to a specific location.

Defensive Guardrails

These rules protect the cluster from expensive queries. Follow them — but use judgement. If the user explicitly asks for something that bends a rule, explain the trade-off and proceed if they confirm.

• Never SELECT * from tables with >10K rows — use aggregations, filters, or sample.py instead. For smaller tables, SELECT * is fine.
• Always add LIMIT when exploring unfamiliar tables (default LIMIT 100).
• Check row counts first — run tables.py --schema=X before writing queries so you know what you're dealing with.
• Prefer aggregations for large tables — COUNT, SUM, AVG with GROUP BY over pulling raw rows.
• Filter on indexed columns for large tables — check indexes via columns.py (the indexes column). Filtering on indexed columns avoids full table scans.
• Joins are fine — as long as the join condition is correct and you aggregate/filter the result appropriately.
• Avoid accidental cross joins — always include ON/USING.
• Prefer LIMIT + ORDER BY over unbounded selects when exploring.

Size awareness:

SQL Standards

Every SQL query you write must follow these rules.

Always comment your SQL

Explain the business intent, not just the mechanics.

Always show the SQL to the user

• Short queries (<10 lines): show the full SQL inline with comments
• Long queries (>10 lines): save to ~/rds-exports/query-{timestamp}.sql, show the key parts inline, and reference the saved file

Use --sql-file for complex queries

For long SQL, write it to a file first and execute with --sql-file:

PYTHON ${CLAUDE_SKILL_DIR}/scripts/query.py --sql-file=~/rds-exports/my_query.sql

Tip — inline multiline SQL without reading/writing in the LLM context:

Mac/Linux — heredoc:

cat > "/path/to/query.sql" << 'EOSQL'
-- Your SQL here
SELECT ...
EOSQL
PYTHON ${CLAUDE_SKILL_DIR}/scripts/query.py --sql-file="/path/to/query.sql" --save="/path/to/results.csv" --save-sql

Cross-platform — Python:

PYTHON -c "
sql = '''
-- Your SQL here
SELECT ...
'''
open('/path/to/query.sql', 'w').write(sql.strip())
"
PYTHON ${CLAUDE_SKILL_DIR}/scripts/query.py --sql-file="/path/to/query.sql" --save="/path/to/results.csv" --save-sql

Tip — reuse SQL files by replacing values: When you already have a SQL file and just need to change specific values (e.g. rolling dates forward a week), use a one-liner to swap them without rewriting the whole file in the LLM context:

Mac/Linux — sed:

sed "s/2026-03-15/2026-03-22/g; s/2026-03-21/2026-03-28/g" original_query.sql > query.sql

Cross-platform — Python:

PYTHON -c "open('query.sql','w').write(open('original_query.sql').read().replace('2026-03-15','2026-03-22').replace('2026-03-21','2026-03-28'))"

Then execute:

PYTHON ${CLAUDE_SKILL_DIR}/scripts/query.py --sql-file=query.sql --save="/path/to/results.csv" --save-sql

For templates with many replacements, named placeholders like {{WC_START}} can be used instead of literal dates.

Formatting conventions

------------------------------------------------------------------------------------------------------------------------
-- Monthly revenue by region
-- Purpose: Aggregate order line items by region for the last 12 months
-- Filters: Excludes cancelled orders and zero-quantity items
------------------------------------------------------------------------------------------------------------------------
WITH monthly_revenue AS (
    SELECT  r.region_name,
            DATE_TRUNC('month', o.order_date)::DATE                                  AS month_nk,
            SUM(o.quantity)                                                           AS total_quantity,
            SUM(o.line_total)                                                         AS total_revenue,
            COUNT(DISTINCT o.customer_id)                                             AS unique_customers
    FROM    sales.fact_order_lines o
    JOIN    sales.dim_regions r                     USING (region_id)
    WHERE   o.order_date >= NOW() - INTERVAL '12 months'
            AND o.quantity > 0
            AND o.order_status <> 'cancelled'
    GROUP   BY 1, 2
)
SELECT  region_name,
        month_nk,
        total_quantity,
        total_revenue,
        unique_customers,
        ROUND(total_revenue / NULLIF(total_quantity, 0), 2)                          AS revenue_per_unit
FROM    monthly_revenue
ORDER   BY region_name, month_nk

Key formatting rules:

• Section headers with -- dashes above the query
• SELECT, FROM, JOIN, WHERE, GROUP BY, ORDER BY left-aligned
• One column per line — each column expression gets its own line with its AS alias
• Column aliases aligned at a consistent position
• Inline comments explaining non-obvious business logic
• CTE names that describe the business concept

PostgreSQL-specific SQL notes

• Use NOW() - INTERVAL '12 months' for date arithmetic (not DATEADD)
• Use EXTRACT(epoch FROM timestamp) for epoch seconds
• Use DATE_TRUNC('month', col) for period truncation
• Use NULLIF(expr, 0) to avoid divide-by-zero
• GROUP BY 1, 2, 3 positional references work in PostgreSQL
• Use ILIKE for case-insensitive pattern matching
• Use ::TYPE for casting (e.g. col::DATE, col::TEXT, col::NUMERIC)

Generous commenting

Every query must include comments that explain the why, not just the what. Treat SQL as documentation for the next person (or the next agent) who reads it.

• Header block at the top of every query: purpose, sources, key assumptions, parameters
• Section separators (dashed -- lines) between logical blocks (e.g. between actuals, budget, pivot CTEs)
• Inline comments on non-obvious expressions (e.g. customer estimation formulas, business rules, why a filter exists)
• CTE-level comments explaining what each CTE produces and why it exists as a separate step

------------------------------------------------------------------------------------------------------------------------
-- Weekly Sales Scorecard
-- Purpose: Core trading metrics with TY/LP/LY comparisons and BU/LV variances
-- Source:  fact_basket_items (actuals), fact_commercial_budget_all (BU)
-- Params:  Change the date in the periods CTE to set the reporting period
-- Notes:
--   - Customer estimation done per channel first, then summed to total
--   - BU/LV only have sales and margin — derivative ratios are actuals-only
------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------
-- ACTUALS: per channel for correct customer estimation
------------------------------------------------------------------------------------------------------------------------

Schema Exploration Workflow

Use this graduated approach when exploring an unfamiliar schema. Don't follow this rigidly — if the user's intent is clear, skip straight to the relevant step.

1. Understand the landscape — schemas.py → see what schemas exist
2. Browse tables — tables.py --schema=X → check row counts and sizes
3. Understand structure — columns.py --schema=X --table=Y → column types, indexes
4. Sample first — sample.py --limit=5 → see actual data values
5. Profile if needed — profile.py → nulls, cardinality, min/max per column
6. Write targeted queries — now you know enough to write safe, efficient SQL
7. Analyze locally — analyze.py on saved results for follow-up

Shortcut: If the user says "how many orders last month?", don't run 5 discovery scripts. Check the table size, write the query, run it.

Business Analysis Workflows

Understanding business performance

1. Start with the headline metric — total revenue, order count, customer count for the period
2. Break down by dimensions — time (daily/weekly/monthly), region, channel, category
3. Compare periods — this period vs last period, year-over-year
4. Identify outliers — which segments are significantly above or below expectations?

Root cause analysis

When something looks wrong:

1. Confirm the anomaly — is it real? Check the data source
2. Decompose the metric — volume vs value issue?
3. Slice by dimensions — which region/channel/category drove the change?
4. Correlate with events — promotions, price changes, stock-outs, seasonality

Common analyst patterns

Script Details

query.py — Run SQL

PYTHON ${CLAUDE_SKILL_DIR}/scripts/query.py "SELECT count(1) FROM sales.orders"
PYTHON ${CLAUDE_SKILL_DIR}/scripts/query.py --sql-file=~/rds-exports/my_query.sql

run_file.py — Run a .sql file

For files query.py cannot take: migrations, DDL, backfills — anything with more than one statement, or with a DO block that COMMITs per batch.

PYTHON ${CLAUDE_SKILL_DIR}/scripts/run_file.py --dry-run migrations/add_search.sql
PYTHON ${CLAUDE_SKILL_DIR}/scripts/run_file.py --allow-write --timing migrations/add_search.sql
PYTHON ${CLAUDE_SKILL_DIR}/scripts/run_file.py --connection prod --allow-write --allow-prod migrations/add_search.sql

Always --dry-run first — it splits the file and lists the statements without connecting. Show that list to the user before running for real.

What it does differently from query.py:

• Splits on top-level semicolons only — dollar-quoted bodies, string literals, quoted identifiers and comments are left intact. It is psql-lite, not psql: no \ meta-commands, no variable interpolation.
• One session, autocommit, no wrapping transaction. Session-scoped state (SET ROLE, hypopg, advisory locks) survives across statements, and a DO block may COMMIT.
• Prints RAISE NOTICE output after each statement, so backfill progress is visible.
• Shows result rows for statements that return them (first 50).

By default there is no rollback. A failure at statement 40 leaves the first 39 committed. Say this to the user before running anything that isn't idempotent, and prefer files that can be re-run safely (IF NOT EXISTS, CREATE OR REPLACE, batched backfills with a resume condition).

Choosing the transaction mode

--single-transaction refuses to combine with --continue-on-error — once a statement fails the transaction is aborted, so every later statement fails anyway. Before running, it scans the file and warns about statements PostgreSQL will not allow inside a transaction block; that warning shows in --dry-run too, so you can check the mode is right without connecting.

Pick the default for anything long or lock-sensitive on a busy table — holding every lock for the duration of a big migration is usually worse than a partial apply you can re-run.

schemas.py — List schemas

PYTHON ${CLAUDE_SKILL_DIR}/scripts/schemas.py

schema_name   owner   table_count
-----------   -----   -----------
public        admin   12
sales         etl     45

tables.py — List tables

PYTHON ${CLAUDE_SKILL_DIR}/scripts/tables.py --schema=sales

table_name       table_type  row_count  total_size  table_size  index_size
--------------   ----------  ---------  ----------  ----------  ----------
dim_customers    BASE TABLE  250000     120 MB      85 MB       35 MB
fact_orders      BASE TABLE  8500000    4500 MB     3800 MB     700 MB

columns.py — List columns

PYTHON ${CLAUDE_SKILL_DIR}/scripts/columns.py --schema=sales --table=orders

pos  column_name   data_type          max_len  is_nullable  column_default  indexes
---  ------------  -----------------  -------  -----------  --------------  -------
1    order_id      INTEGER                     NO           nextval(...)    orders_pkey
2    order_date    TIMESTAMP WITHOUT           NO                           idx_orders_date
3    customer_id   INTEGER                     NO                           idx_orders_cust

search.py — Search objects

PYTHON ${CLAUDE_SKILL_DIR}/scripts/search.py --pattern=order
PYTHON ${CLAUDE_SKILL_DIR}/scripts/search.py --pattern=revenue --type=column

sample.py — Sample data

PYTHON ${CLAUDE_SKILL_DIR}/scripts/sample.py --schema=sales --table=orders --limit=5

profile.py — Data profiling

PYTHON ${CLAUDE_SKILL_DIR}/scripts/profile.py --schema=sales --table=orders

column_name   data_type  total_rows  null_count  null_pct  distinct_count  min_val     max_val     avg_val
-----------   ---------  ----------  ----------  --------  --------------  ----------  ----------  -------
order_id      INTEGER    8500000     0           0.0       8500000         1           8500000     4250000
order_date    TIMESTAMP  8500000     0           0.0       1825            2022-01-01  2026-12-31

analyze.py — Local analytics (no Aurora)

Analyze previously saved CSV/JSON files locally. No network access needed — great for follow-up analysis without hitting Aurora again.

PYTHON ${CLAUDE_SKILL_DIR}/scripts/analyze.py ~/rds-exports/query-*.csv --describe
PYTHON ${CLAUDE_SKILL_DIR}/scripts/analyze.py data.csv --sum=revenue
PYTHON ${CLAUDE_SKILL_DIR}/scripts/analyze.py data.csv --group-by=region --sum=sales
PYTHON ${CLAUDE_SKILL_DIR}/scripts/analyze.py data.csv --filter='year=2024' --sort=amount --desc --top=10
PYTHON ${CLAUDE_SKILL_DIR}/scripts/analyze.py data.csv --hist=price

Available operations: --count, --describe, --sum=COL, --avg=COL, --min=COL, --max=COL, --median=COL, --group-by=COL, --filter=EXPR, --sort=COL, --desc, --top=N, --hist=COL

Write-Mode Behaviour

Each connection has a write_mode field (reject / accept / ask / auto) that controls whether non-read-only SQL is allowed. Read it from the connection's config entry before writing or running any SQL — your behaviour changes per mode.

Operation classification

Behaviour matrix

How to "stop and ask"

When the matrix says to ask the user before running the query (this happens at the LLM level, not the script — by the time the script runs, the answer is already yes):

1. Compose the SQL.
2. Use the agent's structured-question tool if available — AskUserQuestion in Claude Code, equivalent prompt tools in Codex / Cursor / etc. — rather than free-text Q&A.
3. The question must show:
   • the SQL (formatted),
   • the connection name + database,
   • the target objects,
   • a blast-radius estimate (rows affected, whether reversible).
4. Only proceed on an explicit yes. On no, abort.

Example confirm question for DELETE FROM events WHERE created_at < '2024-01-01' on the prod connection (write_mode = auto, low-risk because it has a WHERE):

Connection prod is in auto mode and this is a low-risk write — running directly. (No confirmation needed.)

Example for DELETE FROM events (no WHERE) on the same connection:

Connection prod (write_mode auto) — this is a high-risk write. Confirm before I run:

DELETE FROM events

Estimated rows affected: ~12.3M (entire table). Irreversible. Run? [yes / no]

Multi-statement queries

Multi-statement queries (; followed by another statement) are blocked in all write modes — that's an injection defence, not a read-only thing.

The one exception is run_file.py, which exists precisely to apply a multi-statement file. It is not a way around the guard: it runs a file you can point at and read, never inline SQL, and it is gated twice on top of write_mode:

Both must pass. An unknown target is treated as production, so a newly added connection is protected until it is named explicitly. Neither flag is yours to add on the user's behalf — if a run refuses, show them the refusal and the file, and let them decide.

Defensive defaults

If you can't determine the write_mode (config missing, malformed), assume reject and only run reads.

How IAM Authentication Works

This skill uses AWS IAM database authentication — no passwords or secrets are stored anywhere.

1. Your AWS CLI profile (de_rds or similar) provides your identity
2. The skill calls aws rds generate-db-auth-token to get a temporary 15-minute token
3. The token is used as the database password over an SSL-encrypted connection (sslmode=require)
4. The token expires automatically — no credential rotation needed

Prerequisites (one-time setup, done by infra):

• Aurora cluster: iam_database_authentication_enabled = true
• DB user: GRANT rds_iam TO rds_skill_user
• IAM policy: rds-db:connect on the cluster + user ARN
• VPN: connected to corporate VPN to reach the DB endpoint

Advanced SQL Templates

For ad-hoc exploration via query.py. PostgreSQL system catalogs — no extensions or admin views required (except pg_stat_statements where noted).

Running queries:

SELECT pid,
       usename                                                       AS user_name,
       datname                                                       AS db_name,
       state,
       query_start,
       NOW() - query_start                                           AS duration,
       LEFT(query, 200)                                              AS query_preview
FROM   pg_stat_activity
WHERE  state <> 'idle'
       AND pid <> pg_backend_pid()
ORDER  BY query_start

Long-running / blocking queries:

SELECT pid,
       usename,
       state,
       wait_event_type,
       wait_event,
       NOW() - query_start                                           AS duration,
       LEFT(query, 200)                                              AS query_preview
FROM   pg_stat_activity
WHERE  state = 'active'
       AND NOW() - query_start > INTERVAL '30 seconds'
ORDER  BY query_start

Query history (requires pg_stat_statements extension):

SELECT LEFT(query, 200)                                              AS sql,
       calls,
       ROUND(total_exec_time::NUMERIC, 1)                            AS total_ms,
       ROUND(mean_exec_time::NUMERIC, 1)                             AS mean_ms,
       ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::NUMERIC, 1)  AS pct_of_total
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20

If pg_stat_statements isn't installed, this query will error with relation "pg_stat_statements" does not exist — that's a one-time install at the cluster level (CREATE EXTENSION pg_stat_statements).

Table dependencies (FK relationships):

SELECT n1.nspname || '.' || c1.relname                               AS source_table,
       n2.nspname || '.' || c2.relname                               AS referenced_table,
       con.conname                                                    AS constraint_name
FROM   pg_constraint con
JOIN   pg_class c1     ON con.conrelid    = c1.oid
JOIN   pg_namespace n1 ON c1.relnamespace = n1.oid
JOIN   pg_class c2     ON con.confrelid   = c2.oid
JOIN   pg_namespace n2 ON c2.relnamespace = n2.oid
WHERE  con.contype = 'f'
ORDER  BY source_table

Index usage stats (which indexes are unused?):

SELECT schemaname,
       relname                                                       AS table_name,
       indexrelname                                                  AS index_name,
       idx_scan                                                      AS times_used,
       pg_size_pretty(pg_relation_size(indexrelid))                  AS index_size
FROM   pg_stat_user_indexes
WHERE  schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER  BY idx_scan, pg_relation_size(indexrelid) DESC

Table bloat estimate:

SELECT schemaname,
       relname                                                       AS table_name,
       n_live_tup                                                    AS live_rows,
       n_dead_tup                                                    AS dead_rows,
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1)  AS dead_pct,
       last_vacuum,
       last_autovacuum
FROM   pg_stat_user_tables
WHERE  n_dead_tup > 0
ORDER  BY n_dead_tup DESC
LIMIT  20