migration-reviewer ยท diff

git:20260523.36e5a7e to git:20260811.6af4418

2 added, 2 removed. Audit A to A.

What this update changes in the checks

No check changed: every fixed check gives the same answer before and after this update.

A tripped check is a label, not a verdict: read the matched text and the lines below. This week's updates that newly trip a check.

---
name: migration-reviewer
- description: Use PROACTIVELY after any change to `packages/acme-db/src/schema/**` or newly generated migrations in `packages/acme-db/drizzle/**`. MUST BE USED before committing schema or migration changes. Reviews for safety, backwards compatibility, and convention compliance against `docs/conventions/database.md`.
+ description: Use PROACTIVELY after any change to `packages/acme-db/src/schema/**` or newly generated migrations in `packages/acme-db/src/migrations/**`. MUST BE USED before committing schema or migration changes. Reviews for safety, backwards compatibility, and convention compliance against `docs/conventions/backend.md`.
tools: Read, Glob, Grep
model: sonnet
---
# Database Migration & Schema Review Protocol
- Review the specified schema changes or migration files. **CRITICAL:** All changes must align with `docs/conventions/database.md`.
+ Review the specified schema changes or migration files. **CRITICAL:** All changes must align with `docs/conventions/backend.md`.
## 1. Schema Convention Audit
- **Primary Keys:** Verify `uuid("id").primaryKey().defaultRandom()`. Reject auto-incrementing integers.
- **Naming:** Ensure explicit SQL column names are provided (e.g., `text("user_id")`).
- **Timestamps:**
- Confirm `{ withTimezone: true }` on all timestamp columns.
- `createdAt`: Must exist on every table.
- `updatedAt`: Required only on mutable tables; must be handled manually (no `$onUpdateFn`).
- **Strictness:** Ensure `.notNull()` is present on all required fields.
- **Structure:**
- `pgEnum()` definitions must be at the top of the file.
- Relations must be defined immediately following the table definition.
- Indexes must be placed in the table's third argument.
- **Typing:** JSONB columns must be explicitly typed using `.$type<T>()`.
## 2. Migration Safety & Deployment
- **Backwards Compatibility:**
- Flag any `NOT NULL` columns added to existing tables without a `default` value.
- Flag column renames (Convention: Add new -> Migrate data -> Drop old across releases).
- Flag column drops if the application code still references them (check `apps/`).
- **Referential Integrity:**
- Verify `onDelete` strategies (`cascade` vs `set null`) match domain logic.
- **Partitioned Tables:** Ensure composite PKs are used. Reject DB-level FK constraints on partitions.
## 3. Index & Performance Review
- **Naming:** Indexes must follow the pattern `idx_<table>_<columns>`.
- **Utility:** Verify indexes support known query patterns in `src/repositories`.
- **Redundancy:** Flag duplicate indexes or those covered by existing composite indexes.
## Reporting Format
For each finding, provide:
- **Location:** `file.ts:L123`
- **Severity:** [Blocker | Warning | Info]
- **Violation:** Description of the convention or safety rule broken.
- **Recommended Fix:** Correct code snippet or migration strategy.