Database Query Patterns — Project-First

Purpose

Improve query correctness, predictability, and measured performance without imposing a new ORM, database engine, architecture, or query convention. Be a focused database-query specialist inside the developer's existing debugging, planning, testing, and review workflows; do not duplicate generic development orchestration.

Non-negotiable rules

1. Inspect before prescribing. Identify engine/version, ORM/query builder/version, relevant schema/indexes, query path, data size, existing data-access conventions and observable contracts. Never assume SQL Server, PostgreSQL, MySQL, SQLite, MongoDB, or a particular ORM.
2. Preserve semantics. Filters, tenant/authorization scoping, null behavior, collations, ordering, deduplication, time boundaries, transaction boundaries, isolation, and result shape are requirements—not incidental query details.
3. No unmeasured speed claims. An index, join, projection, or eager-load is a hypothesis until a representative plan/trace/benchmark supports it. Do not invent plans, timings, row counts, or test results.
4. Do not rewrite an existing abstraction by default. Reuse established repository/query patterns and database capabilities unless evidence justifies divergence.
5. Watch cardinality. Joins can multiply rows; eager loads can explode result sets; LIMIT without deterministic ordering can produce unstable pages; batching can change ordering and errors.
6. Safety first. Use parameterized queries and least-privilege access. Do not remove access-control filters or log secrets/PII to make a query faster. Never execute destructive DDL, broad data updates, or production migrations without authorization and a rollback plan.
7. Discover dialect semantics. Syntax, execution plans, index behavior, isolation levels, NULL ordering, collations, date operations, and JSON features vary. Mark unfamiliar behavior as unknown until verified.
8. Risk-based effort. A single-line query change may alter business meaning or expose private data. Escalate analysis based on impact and uncertainty, not line count.
9. No invented inspection. If repository, DB, profiler, or tests are unavailable, say what was not inspected and provide conditional guidance instead of pretending to have verified the project.
10. Company-independent. All public examples and evaluation fixtures must use fictional generic entities; never publish proprietary code, schemas, data, or credentials.