data-sql

name	data-sql
description	SQL query design, optimization, EXPLAIN analysis, index strategy, pagination, upserts, and N+1 prevention for relational databases (PostgreSQL, MySQL, SQL Server, SQLite, Oracle). USE FOR: SQL queries, schema review, performance tuning, index strategy, query optimization, EXPLAIN plan analysis. DO NOT USE FOR: GraphQL APIs (data-graphql), MongoDB queries (data-mongodb), Redis caching (data-redis), full backend implementation (impl-* skills).
argument-hint	Paste a query, point me at a schema, or describe what data you need.
phase	4
phase-family	implementation

SQL Query Design and Optimization

When to Use

• Writing complex SQL queries (JOINs, CTEs, window functions, upserts).
• Reviewing existing SQL for correctness, performance, and security.
• Diagnosing slow queries via EXPLAIN plans.
• Designing or reviewing index strategy.
• Fixing N+1 query patterns in application code.
• Schema scanning and onboarding to a database.

When Not to Use

• GraphQL schema design or resolvers — use data-graphql.
• MongoDB queries or aggregation pipelines — use data-mongodb.
• Redis caching, rate limiting, or pub/sub — use data-redis.
• Full backend feature implementation — use impl-python, impl-typescript-backend, or other impl-* skills.
• Architecture or planning decisions — use architecture-planning.

Procedure

1. Detect database — Identify the RDBMS and version from project files (package.json, prisma/schema.prisma, appsettings.json, settings.py, *.csproj, connection strings). Supported: PostgreSQL, MySQL/MariaDB, SQL Server, SQLite, Oracle.
2. Scan schema — If a database project or schema definition exists (ORM models, migration files, .sqlproj), catalog tables, columns, types, constraints, indexes, and relationships. Produce a summary.
3. Analyze the request — Determine what is needed: write a query, review existing SQL, diagnose performance, or explain a schema.
4. Write or optimize SQL — Produce correct, performant SQL following the standards below. Adapt syntax to the detected dialect.
5. Verify with EXPLAIN — If executable, run EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) to validate the plan and interpret results.
6. Recommend indexes — Suggest indexes that would improve performance for the queries at hand.
7. Produce the output contract — Write the Implementation Complete Report (see Output Contract below).

Standards

Database Coverage

Detect the target database from connection strings, ORM configuration, migration files, or database project files:

• PostgreSQL — pg, Prisma with postgresql, Django psycopg2, EF Core Npgsql
• MySQL / MariaDB — mysql2, Prisma with mysql, Django mysqlclient
• SQL Server — mssql, .sqlproj, .dacpac, EF Core SqlServer
• SQLite — better-sqlite3, Prisma with sqlite, Django sqlite3
• Oracle — oracledb, cx_Oracle

Adapt syntax, functions, and optimization strategies to the detected dialect.

Schema Scanning Sources

ORM / Schema sources:

• Prisma: schema.prisma — models, fields, relations, indexes, enums
• TypeORM: entity decorators — @Entity, @Column, @ManyToOne, @Index
• Sequelize: model definitions — define, associations
• Django: models.py — Model classes, fields, Meta, ForeignKey
• EF Core: DbContext, entity configurations, migrations
• SQL Server projects: .sqlproj, .sql table/view/procedure definitions

Migration sources:

• migrations/ folders (Prisma, TypeORM, Sequelize, Django, Alembic, Flyway, Liquibase)
• Raw .sql migration files

Query Writing Patterns

SELECT with JOINs

SELECT
    o.id AS order_id,
    o.created_at,
    c.name AS customer_name,
    SUM(li.quantity * li.unit_price) AS order_total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
INNER JOIN line_items li ON li.order_id = o.id
WHERE o.status = 'completed'
    AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY o.id, o.created_at, c.name
HAVING SUM(li.quantity * li.unit_price) > 100
ORDER BY order_total DESC;

CTEs and Window Functions

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100,
        2
    ) AS growth_pct
FROM monthly_sales
ORDER BY month;

Upsert (PostgreSQL)

INSERT INTO inventory (product_id, warehouse_id, quantity)
VALUES ($1, $2, $3)
ON CONFLICT (product_id, warehouse_id)
DO UPDATE SET
    quantity = inventory.quantity + EXCLUDED.quantity,
    updated_at = NOW();

EXPLAIN / Performance Diagnosis

Always use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN (MySQL) to verify query performance:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Red flags in explain plans:

• Seq Scan on large tables — missing index
• Nested Loop with high row estimates — consider hash or merge join
• Sort with high memory — add index to avoid sorting
• Hash Join with spills to disk — increase work_mem or reduce result set
• Bitmap Heap Scan with many recheck conditions — partial index may help

Common Bottlenecks and Fixes

Bottleneck	Symptom	Fix
Missing index	Seq Scan on filtered column	CREATE INDEX on WHERE/JOIN columns
N+1 queries	Many small queries in a loop	Use JOINs or batch queries
Full table scan	No index used despite WHERE clause	Check column types match, add composite index
Lock contention	Queries waiting on locks	Reduce transaction scope, use SKIP LOCKED
Large result sets	Slow response, high memory	Add pagination (LIMIT/OFFSET or cursor)
Unoptimized subquery	Correlated subquery re-executes per row	Rewrite as JOIN or CTE
Missing statistics	Planner chooses bad plan	Run ANALYZE on the table

Index Strategy

• Index columns used in WHERE, JOIN ON, ORDER BY, GROUP BY.
• Use composite indexes for multi-column filters (leftmost prefix rule).
• Consider partial indexes for filtered subsets (WHERE active = true).
• Use covering indexes (INCLUDE) to avoid heap lookups.
• Avoid over-indexing — each index slows writes.

N+1 Prevention

• Identify loops that issue one query per iteration.
• Replace with JOINs, subqueries, or batch IN (...) queries.
• When using ORMs, use eager loading (include, prefetch_related, Include()).

Pagination

• Offset-based: LIMIT $1 OFFSET $2 — simple but degrades on deep pages.
• Cursor-based: WHERE id > $cursor ORDER BY id LIMIT $1 — stable performance at any depth.
• Always pair pagination with a deterministic ORDER BY.

Transaction Scope

• Keep transactions as short as possible.
• Use appropriate isolation levels (READ COMMITTED for most OLTP, SERIALIZABLE only when required).
• Use SKIP LOCKED for queue-style processing to avoid contention.

SQL Injection Prevention

• Always use parameterized queries ($1, ?, @param). Never concatenate user input into SQL strings.
• Validate and sanitize inputs at the application layer before they reach the query.

NULL Handling

• Use COALESCE or NULLIF for safe NULL comparisons.
• Remember: NULL != NULL — use IS NULL / IS NOT NULL.
• Aggregations ignore NULLs — use COALESCE in SUM/AVG when zero-default is needed.
• Be cautious with NOT IN containing NULLs — prefer NOT EXISTS.

Query Review Checklist

• Correctness — Does the query return the right results? Check JOIN conditions, WHERE filters, GROUP BY columns
• SQL injection — Are user inputs parameterized? Never concatenate strings into queries
• Performance — Are appropriate indexes used? Run EXPLAIN to verify
• N+1 patterns — Is the query called in a loop? Batch or join instead
• NULL handling — Are NULLs handled correctly in comparisons and aggregations?
• Data types — Do comparisons use matching types? Implicit casts defeat indexes
• Transaction scope — Is the transaction as short as possible? Are isolation levels appropriate?
• Idempotency — Can the query be safely retried? Use upserts where appropriate

Output Contract

All skills in the implementation phase family use this identical report. Present it in chat before logging progress.

### Implementation Complete Report

**Implementation summary**
[2-4 sentences: what was delivered and how it matches the request.]

**Scope**
- In scope: [bullets or "As specified in task"]
- Out of scope / deferred: [bullets or "None"]

**Acceptance criteria mapping**
| AC / criterion | Evidence |
|----------------|----------|
| [AC-1 or description] | [file path, test name, or behavior] |

_Use `N/A — [reason]` if no formal AC list exists._

**Changes**
| Path | Purpose |
|------|---------|
| `path/to/file` | [one line] |

**Verification**
- [command] — [result: pass/fail/skip]
- _If not run, state why._

**Risks and follow-ups**
- [concrete items] or **None**

**Suggested next step**
[Handoff target agent name or human action.]

Guardrails

• Adapt all SQL syntax to the detected database dialect. Do not assume PostgreSQL when the project uses MySQL or SQL Server.
• Do not introduce schema changes unless explicitly requested — focus on queries and indexes.
• Do not speculate on missing schema; scan the project or ask for clarification.
• Use data-graphql when the task involves GraphQL schema or resolvers.
• Use data-mongodb when the task involves MongoDB queries or aggregation.
• Use data-redis when the task involves Redis caching or data structures.
• Use impl-* skills when the task requires full backend feature implementation beyond SQL.