Database & Query Performance rules

Review SQL/database migrations for operations that can lock large tables or cause downtime. Examples: creating indexes without CONCURRENTLY (Postgres), ALTER COLUMN TYPE on big tables, adding NOT NULL without backfill, long-running updates without batching. Require an online migration strategy (CONCURRENTLY, backfill in batches, dual-write/expand-contract) and a rollback plan.

Index frequently filtered or joined columns and verify with query plans.

Index columns used in WHERE/JOIN/ORDER and enforce uniqueness where applicable.

Enforce referential integrity with add_foreign_key and matching indexes.

Wrap multi-record changes in a database transaction and use bang methods to ensure rollback on failure.

Wrap multi-statement DB operations in a transaction; commit on success and rollback on any exception.

Enforce uniqueness at the database level (unique indexes/constraints) for identifiers and natural keys.

Flag patterns that issue network/database calls inside loops; recommend batching (Promise.all), joins, or aggregate endpoints.

When reading from PHI tables, project only authorized fields and avoid SELECT *. Implement whitelists per role and document them. Reject requests lacking a Purpose-Of-Use justification.

Catch and map DB exceptions (e.g., SQLIntegrityConstraintViolationException) to domain results; always close resources with use { }.

Use transactions for multi-step writes; on Throwable, rollback and rethrow with context.

Wrap multi-step writes in a transaction; on failure, roll back and rethrow with context.

Detect transient vs non-transient DB errors; add context and apply retry policies only where safe (idempotent reads/operations).

Prefer JOINs/eager loading over per-row queries to avoid N+1 patterns.

Index columns used in JOIN/WHERE/ORDER BY; verify plans and avoid full scans for hot queries.

Ensure LINQ queries translate to SQL efficiently (avoid client evaluation, use projections, defer ToList until needed).

Replace per-row lookups with JOINs or bulk queries to fetch related data in fewer round-trips.

Use joins for filtering on associations and includes/preload for eager-loading to avoid N+1.

Ensure that SQL queries specify only the necessary fields instead of using `SELECT *` to optimize database performance.

Ensure that batch processing is used instead of executing individual SQL statements inside loops for better database efficiency.

When callbacks/validations are not required, prefer a single UPDATE statement via update_all over iterating records and calling update.

Provide a decorator/context manager that opens/closes DB sessions and handles commit/rollback automatically.

Identify hot lookups/joins/sorts and add appropriate single/compound indexes in migrations.

Index columns used in filters/joins/sorts; validate plans for slow queries and add composite indexes as needed.