git:20260824.9c2e24b to git:20260824.63449ef

35 added, 87 removed. Audit A to A.

---

name: sql-performance-tuner

- description: Phân tích và tối ưu SQL queries — EXPLAIN ANALYZE, index strategy, N+1 detection, slow query review

+ description: Audit hiệu năng persistence adapter theo Architecture Profile, gồm N+1, index/query-plan gap và layer violation

user-invocable: true

---

- # Skill: SQL Performance Tuner (`/sql-performance-tuner`)

+ # SQL Performance Tuner (`/sql-performance-tuner`)

- Sử dụng skill này để phân tích, phát hiện và tối ưu SQL queries trong codebase. Đóng gói tri thức Senior DBA.

+ Audit persistence performance cho TypeScript + Node.js + Clean Architecture. Chỉ `src/infra/` được truy cập DB; DB/ORM call trong domain, usecase hoặc interface là `ARCH-01` violation.

## Tham số

- - `--file=<path>`: File cần audit (repository, migration, query file).

- - `--query=<sql>`: SQL query cụ thể cần phân tích.

- - `--mode=audit|fix|index`: `audit` (phân tích), `fix` (đề xuất sửa), `index` (thiết kế indexes).

- ---

- ## Checklist Phân Tích (Chạy theo thứ tự)

- ### 1. N+1 Query Detection

- Dấu hiệu N+1 trong code:

- ```typescript

- // ❌ N+1: Loop với query bên trong

- for (const order of orders) {

- const items = await itemRepo.findByOrderId(order.id); // N queries

- }

- // ✅ Fix: JOIN hoặc IN clause

- const items = await itemRepo.findByOrderIds(orders.map(o => o.id)); // 1 query

- ```

- Phát hiện pattern: Grep tìm `await` bên trong `for`, `.forEach`, `.map` có async callback.

- ### 2. Missing Index Analysis

- Indexes BẮT BUỘC cho:

- - Foreign key columns (`user_id`, `order_id`, `*_id`)

- - Columns trong `WHERE` clause thường xuyên

- - Columns trong `ORDER BY` + `LIMIT` (pagination)

- - Compound index cho queries có multiple WHERE conditions

- ```sql

- -- Kiểm tra query plan

- EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)

- SELECT * FROM orders WHERE user_id = $1 AND status = $2 ORDER BY created_at DESC LIMIT 20;

- -- Nếu thấy "Seq Scan" trên bảng lớn → cần index

- ```

- ### 3. Slow Query Patterns (Phát hiện và sửa)

- | Pattern | Vấn đề | Fix |

- | :----------------------------- | :----------------------------- | :----------------------------------- |

- | `SELECT *` | Over-fetching columns | Chọn đúng columns cần dùng |

- | `LIKE '%keyword%'` | Full table scan | Dùng Full-Text Search hoặc pg_trgm |

- | `ORDER BY rand()` | Filesort toàn bảng | Keyset pagination |

- | `NOT IN (subquery)` | Correlated subquery | `NOT EXISTS` hoặc `LEFT JOIN IS NULL`|

- | `COUNT(*)` trên table lớn | Expensive aggregation | Materialized view hoặc counter table |

- | Implicit type cast trong WHERE | Index skip | Cast explicit hoặc đúng type |

- ### 4. Pagination Anti-patterns

- ```sql

- -- ❌ Offset pagination chậm ở trang sau

- SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;

- -- ✅ Keyset pagination (cursor-based)

- SELECT * FROM orders WHERE id > :last_seen_id ORDER BY id LIMIT 20;

- ```

- ### 5. Transaction & Lock Analysis

- - Detect long transactions: queries nằm trong transaction scope không cần thiết

- - Identify lock contention: `SELECT FOR UPDATE` scope quá rộng

- - Deadlock patterns: Kiểm tra thứ tự acquire locks nhất quán

+ - `--file=<path>`: Persistence adapter cần audit, phải thuộc `src/infra/`.

+ - `--query=<sql>`: Query cần phân tích; chỉ dùng dialect-specific analysis khi DB đã approved.

+ - `--mode=audit|fix|index`: `audit` phân tích, `fix` tạo recommendation, `index` thiết kế index theo DB approved.

- ---

+ ## Architecture Profile gate

- ## Output Format

+ 1. Đọc Architecture Profile, Constitution, CLAUDE và constraints.

+ 2. Xác minh DB, ORM/query layer, migration mechanism và test command đã `APPROVED` cùng evidence.

+ 3. DB/ORM chưa chọn thì chỉ audit layer boundary và báo `CONFIGURATION GAP`; không sinh SQL dialect, index syntax, migration, ORM method, package name hoặc command suy đoán.

+ 4. Binding đã chọn thì chỉ dùng syntax/API/command của binding đó.

- ```

- 📊 SQL PERFORMANCE AUDIT REPORT

- ═══════════════════════════════

+ ## Checklist

- 🔴 CRITICAL (Fix ngay):

- [N+1] order-repository.ts:45 — N+1 query trong loop fetchItems

- [INDEX] Missing index on orders.user_id + status (Seq Scan)

+ - Không có DB client/query builder/ORM import ngoài `src/infra/`; usecase chỉ phụ thuộc port.

+ - Không có database call trong loop khi selected adapter có batch loading, join, relation loading hoặc `IN` query phù hợp.

+ - Chỉ review query plan, index, filter, join, sort, pagination, deletion/soft-delete strategy và migration sau khi DB binding được chọn; soft-delete chỉ áp dụng khi `SPEC.md` và persistence binding đã xác định.

+ - Không `SELECT *` nếu usecase cần ít field; parameterize input; transaction scope nhỏ; lock order nhất quán.

+ - Production index/schema change cần safety constraint, rollback plan và Human Final Review.

- 🟡 WARNING (Plan to fix):

- [SLOW] SELECT * dùng ở 3 chỗ — over-fetching columns

- [PAGINATE] Offset pagination ở orders-list — slow at page > 500

+ ## Output

- 🟢 OK:

- ✓ Foreign keys đều có indexes

- ✓ Compound index cho search queries

+ ```text

+ PERSISTENCE PERFORMANCE AUDIT REPORT

+ Feature: {slug} | Profile: v{version} | Binding: {approved database + ORM/query layer}

- 📋 INDEX RECOMMENDATIONS:

- CREATE INDEX CONCURRENTLY idx_orders_user_status

- ON orders(user_id, status) WHERE deleted_at IS NULL;

+ LAYER VIOLATION:

+ [ARCH-01] {path}:{line} — {DB access outside src/infra}

+ CRITICAL:

+ [N+1|INDEX|LOCK] {path}:{line} — {evidence and impact}

+ WARNING:

+ [QUERY|PAGINATION|PROJECTION] {path}:{line} — {finding}

+ CONFIGURATION GAP:

+ {unselected binding; no adapter-specific remediation generated}

+ REMEDIATION:

+ - Binding: {approved binding}

+ - Change: {profile-compatible action}

+ - Verification: {exact approved command or N/A with reason}

+ - Spec/Plan impact: {artifact or N/A with reason}

```

- ---

- ## Integration với SDD

- Sau khi audit, nếu phát hiện query pattern phải thay đổi thiết kế schema:

- 1. **Fix the Spec, NOT the Code** — cập nhật PLAN.md hoặc SPEC.md của feature tương ứng

- 2. Tạo migration file với index `CONCURRENTLY` (không lock production)

- 3. Gắn `@ears SPEC.md#PERF-XXX` vào optimized query

+ Nếu performance change đổi data contract, consistency, SLA hoặc schema behavior, cập nhật `SPEC.md`/`PLAN.md` trước code. Dùng `/sdd-review` cho profile/schema decision trước execution.