Save this content to: .cursor/rules/sql/optimalize.mdc

## General
- No N+1: use `with()`, `load()`, or JOINs
- SARGable WHERE clauses (no functions on indexed columns)
- Seek pagination over OFFSET
- Never `SELECT *`; fetch only needed columns
- Use EXPLAIN for new or changed queries: avoid type ALL, high rows, Using filesort, Using temporary
- Prepared statements with bound parameters; never concatenate user input into SQL
- Push filtering, sorting, aggregation into SQL; avoid doing it in application code
- Refactor or split very complex joins when EXPLAIN shows poor plans; when profiling use slow query log and prioritize frequent or longest queries
- **Indexes:** index columns used in WHERE, JOIN, ORDER BY, GROUP BY; composite index order must match query filter/sort; avoid low-cardinality-only indexes; use covering indexes when a query can be satisfied from the index; drop unused or redundant indexes when changing schema; aim for 3–5 well-chosen indexes per table; prefer parallel index creation on large tables
- **Transactions:** keep transactions short; avoid holding locks during app logic; batch writes in single transactions where appropriate; reduce lock contention via batching and suitable isolation levels; use `SHOW ENGINE INNODB STATUS` to diagnose lock waits when investigating issues
- Index JOIN columns on both tables; composite index order matters (left-to-right)

## Query Analysis
- Run `EXPLAIN` on every new or modified query.
- Flag: type `ALL`, high `rows`, `Using filesort`, `Using temporary`.
- Use `EXPLAIN ANALYZE` for actual vs estimated row counts.
- Check slow query log — prioritize frequent or longest-running queries.
- Apply one optimization at a time; measure before and after.

## Performance Non-Regression on Query Changes
Whenever a query is **refactored or changed** (Eloquent / query-builder rewrite, raw-SQL edit, added / removed `JOIN` / `WHERE` / `ORDER BY` / `GROUP BY` / subquery / eager load, pagination change, index-driven rewrite, or a refactor that moves the query into another layer), the changed query **must be at least as fast as the original — ideally faster**. Never ship a query change that is slower without a documented justification.

- **Capture the baseline first.** Before changing the query, record the original query's plan and cost: `EXPLAIN` (and `EXPLAIN ANALYZE` when DB access is available) — note `type`, `key`, `rows`, `filtered`, `Extra`, and the actual execution time / rows examined. This is the number the new query is held against.
- **Compare after the change.** Run the same `EXPLAIN` / `EXPLAIN ANALYZE` on the new query and compare against the captured baseline. The new query passes only when it is **equal or better** on the decisive signals: rows examined, access `type`, index usage, `filesort` / `temporary` avoidance, and measured latency. Equal-or-fewer rows examined and equal-or-lower latency is the gate.
- **When the change is slower, it does not ship silently.** If the changed query is measurably slower than the original, you must document, in the PR description / commit body / analysis report:
  1. **Why it is slower** — the concrete cause (e.g. an added `JOIN` widens the row set, a correctness fix removes an index-only path, a denormalization was undone).
  2. **What further optimization options remain** — concrete, actionable next steps (index addition / reorder, covering projection, SARGable rewrite, batching, caching, denormalization, splitting the query), **or** an explicit statement that no further optimization is possible and why (e.g. the slowdown is the unavoidable cost of a required correctness fix).
  3. **Why the slower query is still acceptable** — the trade-off that justifies shipping it (correctness, removed N+1 elsewhere, net round-trip reduction across the request).
- **A slowdown with no documented reason and no listed optimization options is a defect**, not an acceptable change — treat it as a blocking finding in review and as a stop condition in refactoring.
- **When the change legitimately alters the result set** (a new feature adds a filter / join / aggregation so the query intentionally returns different rows), the equal-or-faster comparison is **informational, not a pass/fail gate** — there is no behavior-preserving baseline to match. The gate is strict for optimization- or refactoring-intent changes (same result, different shape); for result-changing changes, still capture the cost and apply the document-why-slower + optimization-options requirement so the added cost is understood, but do not block solely because the richer query is slower than the narrower original.
- **When DB access is unavailable**, compare statically from the `EXPLAIN` plan shape and index reasoning, hold the same equal-or-better bar against the reconstructed baseline, and state the limitation explicitly — never claim a measured improvement you did not measure.

## Query Optimization
- SARGable WHERE clauses only — no functions on indexed columns (`DATE(col)`, `LOWER(col)`, `col + 1`).
- Prefer ranges: `col BETWEEN ? AND ?` instead of `DATE(col) = ?`.
- Seek pagination (`WHERE id > ? LIMIT ?`) instead of OFFSET for large datasets.
- Push filtering, sorting, and aggregation into SQL — never in application code.
- Never `SELECT *` — fetch only needed columns.
- Prepared statements with bound parameters; never concatenate user input into SQL.
- Avoid negative conditions (`<>`, `!=`, `NOT IN`, `NOT LIKE`) — they prevent index usage.
- Prefer `EXISTS` over `COUNT(*)` for existence checks — stops at first match.
- Use set-based operations over row-by-row processing.

```sql
-- Bad — function on indexed column
SELECT * FROM users WHERE DATE(created_at) = '2025-01-01';

-- Good — SARGable range
SELECT * FROM users WHERE created_at BETWEEN '2025-01-01 00:00:00' AND '2025-01-01 23:59:59';

-- Bad — OFFSET pagination on large table
SELECT * FROM users ORDER BY id LIMIT 25 OFFSET 25000;

-- Good — seek pagination
SELECT * FROM users WHERE id > 25000 ORDER BY id LIMIT 25;

-- Bad — COUNT for existence
SELECT * FROM users WHERE (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) > 0;

-- Good — EXISTS
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);
```

## Reuse existing indexes first
- Before adding a new index, verify that the query cannot be rewritten to hit an existing one. Re-order `WHERE` / `JOIN` / `ORDER BY` columns to match an existing composite index (left-to-right rule), strip functions wrapping indexed columns (SARGable rewrite), and project only the columns the index already covers (covering query).
- During code review of new or modified SQL / Eloquent / query-builder code, locate the current schema (migrations, model `$table` metadata, live `SHOW INDEX` when DB access is available) and flag every query that bypasses an existing index that could satisfy it. The fix is a query rewrite, not a new index.
- Add a new index only when the existing schema genuinely lacks one that can cover the query, EXPLAIN confirms the gap, and the rewrite alternative has been ruled out. Then follow the **Index Design** constraints below (composite order, 3–5 indexes per table, drop redundant).

## Index Design
- Index columns used in `WHERE`, `JOIN`, `ORDER BY`, `GROUP BY`.
- Composite index order must match query filter/sort (left-to-right rule).
- Use covering indexes when a query can be satisfied from the index alone.
- Index JOIN columns on both sides.
- Aim for 3–5 well-chosen indexes per table.
- Drop unused or redundant indexes when changing schema.
- Prefer parallel index creation on large tables.
- Do not create indexes on low-cardinality columns alone.
- Do not keep single-column indexes when a composite index already covers them.
- Nullable columns should appear last in composite indexes.
- Know which kind of index you are adding: a **technical** index (backing a foreign key or a uniqueness rule) exists from the first migration; a **performance** index is created from an `EXPLAIN` finding, not from a guess.
- The primary key is implicitly appended to every secondary index — never repeat it as the trailing column.

```sql
-- Query filters by (user_id, status) and sorts by created_at
-- Composite index must match this order:
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);
```

## Batch over per-row operations
- Solve database tasks and queries as a batch whenever possible. Per-row queries issued from inside a loop are a smell — flag during refactoring and code review.
- **Bulk update:** prefer a ModelManager batch method (e.g. `batchUpdate($model, $rows, 'id')` backed by `Iksaku\Laravel\MassUpdate`) over looping per-row `Model::update()` / `$model->save()` calls.
- **Bulk insert:** prefer a ModelManager batch method (e.g. `batchInsert($model, $rows)`) over looping per-row `Model::create()` calls.
- **Bulk delete:** prefer a single `whereIn(...)->delete()` over per-row `Model::delete()` calls.
- **Bulk read:** fetch the whole working set in one query (e.g. `findBy{Attribute}In(...)`) and key it in memory; never look rows up one by one inside a loop.
- **Goal:** minimize DB round-trips and lock contention.
- **Acceptable exception:** per-row work that genuinely cannot be batched because each iteration depends on a side effect of the previous one (e.g. each row triggers a downstream API call that mutates DB state the next row reads). The exception must be justified in a short code comment or in the PR description.

```php
// Bad — per-row update inside a loop
foreach ($users as $user) {
    $user->update(['status' => 'active']);
}

// Good — single batch update via ModelManager
$this->userModelManager->batchUpdate(User::class, $rows, 'id');

// Bad — per-row delete
foreach ($staleIds as $id) {
    Token::query()->whereKey($id)->delete();
}

// Good — single bulk delete
Token::query()->whereIn('id', $staleIds)->delete();

// Bad — per-row lookup inside a loop
foreach ($externalIds as $externalId) {
    $contact = $this->contactRepository->findByExternalId($externalId);
    // ...
}

// Good — one bulk read keyed in memory
$contacts = $this->contactRepository->findByExternalIdIn($externalIds)->keyBy('external_id');
foreach ($externalIds as $externalId) {
    $contact = $contacts->get($externalId);
    // ...
}
```

## Bulk and streaming processing of large datasets
Batching is not only a write-side concern. Any code path whose working set grows with production data must be **batch-first and memory-bounded**. A *large dataset* is any collection whose cardinality is driven by data — rows in a table, lines in an uploaded file, items behind a paginated API — never by a fixed constant written in the code. Code that is correct for ten rows and unusable for a million is a defect, not a trade-off.

- **Never materialize an unbounded result set.** `->get()`, `->all()`, `->pluck()`, `->toArray()`, and `iterator_to_array()` over a query with no `LIMIT` and no bounded filter load the whole table into PHP memory. Stream instead: `chunkById(1000, ...)`, `lazyById(1000)`, or `cursor()` for reads, and a `LazyCollection` for the pipeline built on top of them.
- **`chunkById()` over `chunk()` whenever the loop writes.** `chunk()` paginates with `OFFSET`, so a loop that updates the column the query filters on shifts the window under itself and silently skips rows. Every chunked loop that mutates the filtered set uses `chunkById()` / `lazyById()`.
- **Size the batch explicitly.** Neither one row at a time nor one statement for everything: 500–1000 rows per statement is the working default, bounded by the number of placeholders and `max_allowed_packet`. The size lives in a named constant, never as a magic literal repeated across call sites, and is tuned from measurement rather than guessed.
- **One short transaction per batch**, never a single transaction wrapping the whole run — a long transaction holds locks for the entire duration, inflates the undo log, and turns one failure into a full rollback. A batch run is **resumable and idempotent**: it checkpoints its cursor so a mid-run failure restarts from the last committed batch instead of from the beginning.
- **Batch the side effects too.** A loop body that dispatches a job, sends an HTTP request, sends a mail, or writes a cache key per row multiplies latency by the row count. Dispatch one job per chunk (`Bus::batch()` for a chunked fan-out), send one bulk request per chunk where the vendor's API supports it, and use `Cache::putMany()` / `Redis::pipeline()` instead of per-key round trips.
- **The pushdown rule applies to an already-loaded collection too.** *Query Optimization* above already requires filtering, sorting, and aggregation to happen in SQL; the scale-specific case is `count()`, `sum()`, `max()`, grouping, sorting, or de-duplication applied to a collection that has **already been materialized** — it re-implements the database in application memory over the full row set. Move it onto the query (`->count()`, `->sum()`, `->groupBy()`, `->exists()`) so only the result crosses the wire.
- **Imports and exports stream.** Generate the payload row by row through a generator / `LazyCollection` written straight to an open stream (`fputcsv`, a streamed response, a disk writer); never assemble the full array or the full string in memory first.
- **Move a large run off the request.** A synchronous HTTP request must not iterate an unbounded set — it blocks the worker, hits `max_execution_time` and the memory limit, and cannot be retried. Queue the work as chunked jobs and report progress back to the user.

```php
// Bad — the whole table in memory, per-row write, per-row job, per-row HTTP call
foreach (Order::query()->where('status', 'pending')->get() as $order) {
    $order->update(['status' => 'processed']);
    ProcessOrder::dispatch($order->id);
    $this->client->post('/orders/sync', ['id' => $order->id]);
}

// Good — bounded memory, one write / one job / one request per chunk
Order::query()
    ->where('status', 'pending')
    ->chunkById(self::BATCH_SIZE, function (Collection $orders): void {
        $this->orderModelManager->batchUpdate(Order::class, $this->orderDataBuilder->buildProcessedRows($orders), 'id');
        ProcessOrders::dispatch($orders->modelKeys());
        $this->client->post('/orders/sync', ['ids' => $orders->modelKeys()]);
    });

// Bad — counting and filtering a materialized collection in PHP
$total = Order::query()->get()->where('total', '>', 1000)->count();

// Good — the database does the work
$total = Order::query()->where('total', '>', 1000)->count();
```

Code review treats every violation of this section as a finding; the detection patterns, severities, and fix templates live in `@rules/code-review/general.mdc` *Batch-First Processing & Performance at Scale*.

## Transactions and Locking
- Keep transactions short — no external calls or heavy logic inside.
- Batch related writes in a single transaction.
- Use appropriate isolation levels for the use case.
- Deadlock-prone operations must include retry logic.
- Reduce lock contention via batching and suitable isolation levels.
- Use `SHOW ENGINE INNODB STATUS` to diagnose lock waits.

## Schema Design
- Primary keys on every table.
- InnoDB engine.
- `lower_case_snake_case` naming.
- Normalized unless denormalization is justified by read performance.
- Partition large tables by range where beneficial.
- Non-blocking index creation on large tables where supported.

> **Scope of the standard below.** It is **MySQL / MariaDB**-specific (collations, `TIMESTAMP`, `tinyint(1)`, `ENUM`) — on another engine defer to that engine's skill (`@skills/postgres-patterns/SKILL.md` for Postgres, where `timestamptz` is the correct type). It governs **new tables and new columns**; never demand a rename or a retro-migration of an existing schema. Where a project already has an established convention (Laravel's plural table names, `$table->timestamps()` across every existing table), that convention wins for consistency per `@rules/php/core-standards.mdc` *Project Context* — apply the standard to greenfield schemas and to a table the project is already reshaping, and raise a deviation only there.

### Strict mode is the prerequisite
- The server must run with a strict `sql_mode`: `STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION`.
- Without it every declared domain degrades to a silent fallback instead of an error — an over-long value is truncated, an out-of-range number is clamped, an invalid date becomes `0000-00-00`, and a `NULL` into a `NOT NULL` column is replaced by the implicit default. None of the rules below are actually enforced. Verify it before treating a schema as validated.

### Naming
- **Table names are singular** — `post`, not `posts`. Singular maps 1:1 onto the class (`Post` → `post`) and keeps a family of tables together when sorted (`post`, `post_slug`, `post_tag`). In Laravel this means declaring `protected $table = 'post';` because Eloquent pluralizes by default.
- Primary key is always `id`; a foreign key is `<singular_table>_id` (`author_id` → `user`); a self-reference is `parent_id`. Add a module prefix only when the flat namespace starts to hurt.
- **Name indexes and constraints yourself** — never ship generated names like `post_ibfk_1`. Indexes carry no prefix; constraints do: `fk_post_blog`, `uq_post_slug`, `chk_post_lang_format`.
- **Boolean columns are adjectives with positive polarity** — `published`, `visible`, `pinned`. Convert noun-shaped names (`use_avatar` → `avatar_enabled`, `hide_price` → `price_visible`); fall back to `is_` / `can_` only when no adjective fits (`is_premium`, `can_edit`). Before naming it, confirm it is genuinely a boolean and not a state timestamp (`published_at`) or a set of more than two states (a status column). Type is `tinyint(1) NOT NULL` with a domain-meaningful default.
- **Time columns carry a suffix that matches their type** — `_at` for a moment in time (`DATETIME`): `created_at`, `published_at`, `expires_at`; `_date` for a calendar day independent of any zone (`DATE`): `birth_date`, `invoice_date`. A calendar day stored as a datetime shifts across time zones — a 15 March birth date becomes 14 March 23:00.

### Nullability and defaults
- **Nullable only when the absence of a value is a fact about the world, not a technical state.** Strings are usually `NOT NULL DEFAULT ''`; the default must be domain-meaningful at the moment the row is created (`status varchar(20) NOT NULL DEFAULT 'draft'`).
- NULL is not free: `WHERE col <> 'x'` skips NULL rows, `NOT IN (subquery)` returns nothing when the subquery yields a NULL, and `UNIQUE` treats every NULL as a distinct value.

### Types
- **Never `TIMESTAMP`** — it dies in 2038 and silently converts by the session time zone on read and write. Use `DATETIME` and keep the whole database in one time zone. `DEFAULT CURRENT_TIMESTAMP` records the session zone, so historical rows can carry a different zone than new ones.
- **`modified_at` and `updated_at` are two different columns.** `modified_at` records a *domain* change and is touched only when relevant columns change (a `BEFORE UPDATE` trigger comparing under a `_bin` collation so a diacritics-only edit is detected). `updated_at` is a row version protecting against concurrent edits and uses `ON UPDATE CURRENT_TIMESTAMP`. Every magically maintained column carries a `COMMENT` explaining what maintains it and why.
- **`VARCHAR(255)` is cargo cult.** The length is a domain declaration that strict mode enforces — pick a number you can explain: `email varchar(254)` (RFC), `country char(2)` (ISO 3166-1), `slug varchar(100)` (a deliberately chosen ceiling).
- `TEXT` holds 64 KB of **bytes**, not characters — use `MEDIUMTEXT` for HTML article bodies. `ENUM` is legitimate for a small fixed set (one byte inside indexes, `ALGORITHM=INSTANT` when appending a value).
- **Money and every exact decimal is `DECIMAL`** — never `FLOAT` / `DOUBLE`, whose binary approximation corrupts `SUM()`. `DECIMAL(9,2)` for amounts, `(9,4)` for unit prices, `(12,6)` for exchange rates. A value inserted with higher precision is silently rounded, so round deliberately in the application; MySQL `ROUND` is half-away-from-zero. Enforce non-negativity with a `CHECK`, not `UNSIGNED` — `UNSIGNED` on `DECIMAL` / `FLOAT` / `DOUBLE` is deprecated since MySQL 8.0.17 (on integer types it stays fine).
- **The primary key propagates** — it is physically stored in every secondary index and every referencing foreign key, so its width multiplies across the schema. Default to `INT UNSIGNED AUTO_INCREMENT`, `BIGINT UNSIGNED` for high-volume tables. `AUTO_INCREMENT` follows the maximum value ever used, not the row count; when in doubt take `BIGINT`.

### Charset and collation
- Always `utf8mb4` — the legacy `utf8` is a three-byte alias that cannot store the full range. Use a collation from the `utf8mb4_0900_*` family (UCA 9.0), not `general_ci`, legacy `unicode_ci`, or `czech_ci`.
- **`CHECK` and `REGEXP` comparisons respect the column collation.** Under the default `_ai_ci` a format check does not distinguish case — `WHERE col REGEXP '^[a-z]{2}$'` also accepts `'CS'`. Format constraints belong on a column declared `_bin`, `ascii_bin`, or `_as_cs`.
- **Charset follows the source of the compared values, not the stored content.** Comparing an `ascii` column against a non-ASCII literal raises `ERROR 1267` — a 500 where a missing row should have produced a 404. Publicly queried columns (slugs, codes, identifiers reachable from user input) get `utf8mb4_0900_bin`; `ascii` stays for internal columns whose values never come from a user (`lang`, `country`, `ip_address`).

### Referential integrity
- Pick `ON DELETE` from the nature of the relation — is the child *part of* the parent, or does it only *point at* it?
  - **Composition → `CASCADE`**: `order_item` → `customer_order`.
  - **Association → `RESTRICT`**: `post.author_id` → `user`.
  - **Meaningful detachment → `SET NULL`**: `log.user_id` after anonymization.
- `ON UPDATE CASCADE` as the default — inert with surrogate keys, but it protects the schema if a key is ever re-coded.
- Cascading deletes do **not** fire triggers and do **not** apply to soft deletes — logic that depends on them must live where the delete actually happens.

### CHECK constraints
- `CHECK` enforces **domain invariants** (timeless truths about the data), never **business policy** (rules that change with a decision) — policy belongs in the application.
- Express a conditional obligation as an implication: `CONSTRAINT chk_order_shipped_needs_date CHECK (status <> 'shipped' OR shipped_at IS NOT NULL)`.
- Name a constraint after the **rule, not the columns** — the name is what surfaces in the error the user sees.
- A `CHECK` cannot span rows, use non-deterministic functions, or reference an `AUTO_INCREMENT` column.

## Deployment Safety of Schema Changes
A schema change runs **once, on production data, during a deploy window, while the previous release is still serving traffic**. That is a different risk surface from query performance: a migration that is fast and correct on an empty dev database can lock a table for minutes, break the running release, or fail halfway with no way back. Every diff that adds or modifies a migration (`Schema::create` / `Schema::table` / `Schema::drop*` / `DB::statement` DDL / raw `ALTER TABLE`) must satisfy every clause below before it merges.

- **Destructive changes ship in a later release than the code (expand / contract).** During a rolling deploy the old and the new release run **at the same time** against **one** database, so a dropped or renamed column, a dropped table, a narrowed type (`varchar(255)` → `varchar(50)`, `bigint` → `int`), a tightened `ENUM`, or a re-purposed column breaks whichever release is not deployed yet. Split it into three releases: **expand** (add the new nullable / defaulted column or table; the old code ignores it), **migrate** (backfill and dual-write / dual-read until both releases agree), **contract** (a later release drops the old surface once no running code reads it). A single migration that drops or renames a column the current release still reads is a deploy-time outage, not a refactor.
- **DDL on a populated table must be non-blocking.** State the algorithm and lock mode explicitly — `ALGORITHM=INSTANT` where MySQL 8 supports it (adding a column at the end, widening `VARCHAR` within the same length prefix, adding a virtual column), otherwise `ALGORITHM=INPLACE, LOCK=NONE`. A change MySQL can only run as `ALGORITHM=COPY` (dropping a column before 8.0.29, changing a column type, changing charset / collation, adding a `FULLTEXT` index) rebuilds the whole table under a metadata lock: route it through an online-schema-change tool (`pt-online-schema-change`, `gh-ost`) or a deliberate maintenance window, and say which in the PR description. Never let a deploy discover the lock.
- **Every migration is reversible and re-runnable.** `down()` restores the exact prior structure — a destructive migration whose `down()` is empty or `// no-op` makes the deploy one-way and turns a bad release into a restore-from-backup. Guard structure changes so a partially-applied migration can be replayed: `Schema::hasTable()` / `Schema::hasColumn()` / `Schema::hasIndex()` / `Schema::hasForeignKey()` before creating or dropping. A migration that throws on its second run blocks the retry that a failed deploy needs.
- **Data backfills do not belong in a schema migration.** A migration is a short DDL transaction; an `UPDATE` over every existing row inside it holds locks and the undo log for the whole run, times out on a large table, and rolls the whole deploy back on the last row. Ship the DDL in the migration and the backfill as a separate chunked, resumable, idempotent command or queued job per *Bulk and streaming processing of large datasets* — checkpointing its cursor so a mid-run failure resumes instead of restarting.
- **A new constraint on an existing table is pre-flighted against real data.** `NOT NULL` without a default on a table that already has rows, a `UNIQUE` index on a column that already holds duplicates, a `CHECK` existing rows violate, and a foreign key with orphaned children all **pass on an empty dev database and fail on production**. Before adding the constraint, run the counting query that finds the violating rows (`SELECT COUNT(*) … WHERE col IS NULL`, `GROUP BY col HAVING COUNT(*) > 1`, the anti-join for orphans), remediate them in a backfill, and only then tighten the schema. State the pre-flight result in the PR description.
- **Index the columns the release starts querying, in the same release.** A foreign key gets its backing index, and a column the new code filters, joins, or sorts on gets an index shipped with that code — a new query hitting an unindexed column on a production-sized table is a full scan under production traffic, even though it returns instantly on dev data. Follow *Index Design* for the composite order and the 3–5 index budget, and create the index non-blocking on a large table.
- **The PR states the deploy order and the rollback path.** Say whether the migration runs **before** the code (expand: new nullable column added first) or **after** it (contract: old surface dropped once nothing reads it), name anything that must not run concurrently (a backfill job, a queue worker reading the old shape), and state how the change is undone — `down()`, a compensating migration, or an explicit "irreversible; restore from backup" that the reviewer gets to weigh. A schema change with no stated deploy order is a schema change nobody can safely deploy.

```php
// Bad — one migration drops the column the running release still reads,
// backfills every row inside the DDL transaction, and cannot be reverted
public function up(): void
{
    Schema::table('customer_order', function (Blueprint $table): void {
        $table->string('recipient_email', 254)->nullable(false);
    });

    DB::table('customer_order')->update(['recipient_email' => DB::raw('billing_email')]);

    Schema::table('customer_order', function (Blueprint $table): void {
        $table->dropColumn('billing_email');
    });
}

public function down(): void
{
    // no-op
}
```

```php
// Good — release 1 expands only: nullable column, non-blocking, reversible.
// The backfill runs as a chunked command, and 'billing_email' is dropped
// by a contract migration in a later release once no running code reads it.
public function up(): void
{
    Schema::table('customer_order', function (Blueprint $table): void {
        $table->string('recipient_email', 254)->nullable();
    });
}

public function down(): void
{
    Schema::table('customer_order', function (Blueprint $table): void {
        $table->dropColumn('recipient_email');
    });
}
```

Code review treats every violation of this section as a finding; the detection patterns, severities, and Suggested-Fix artifacts live in `@rules/code-review/general.mdc` *Database Change Deployment Safety*.

## Advanced SQL Patterns
- Use CTEs for complex multi-step queries — prefer over nested subqueries.
- Use window functions (`ROW_NUMBER`, `RANK`, `LAG`, `LEAD`) for analytics and ranking without self-joins.
- Use recursive CTEs for hierarchical data (categories, org trees).

```sql
-- CTE for multi-step logic
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT au.name, COUNT(o.id) AS order_count
FROM active_users au
JOIN orders o ON o.user_id = au.id
GROUP BY au.id, au.name;

-- Window function for ranking
SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank_num
FROM orders;

-- Recursive CTE for hierarchical data
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
```

## New storage reuse analysis
When a diff introduces a new storage surface — a new DB table (`Schema::create(...)` in a migration), a new cache store or Redis namespace, a new filesystem disk, or a new NoSQL / DynamoDB table — an explicit analysis must appear in the PR description or a PR comment before the change merges. The analysis must answer: *"Can this data be stored in an existing storage without a drastic impact on performance?"* It must name the candidate existing storage(s) evaluated and state the reason they were ruled out — or confirm that an existing storage is reused instead. A diff that adds a new storage surface without this documented analysis is a **Moderate** finding in code review (see `@skills/code-review/SKILL.md` *New storage reuse analysis*). Do not flag migrations that only add a column or index to an existing table — only net-new storage surfaces trigger this check.

## Caching at DB Level
- Use query result caching for repeated expensive queries with same parameters.
- Invalidate cache on relevant data changes.
- Mitigate cache stampede (locking, stale-while-revalidate).