Skip to content

DB Query Rules

Use this file before adding or changing SQL.

Mandatory Rules

  • Do not use subqueries in SELECT, WHERE, FROM, or JOIN.
  • Do not use derived tables, IN (SELECT ...), or EXISTS (SELECT ...).
  • FOR UPDATE is permanently forbidden, without exceptions, in executable SQL across Legacy, New-System, migrations and operational scripts. Existing code is not exempt. Use tenant-scoped conditional UPDATE/CAS predicates and the caller's transaction or savepoint boundary for concurrency-sensitive writes.
  • Protect absence/range decisions (first creation, empty slots, queue ordering) with an existing native unique constraint or a narrowly scoped SERIALIZABLE transaction when row CAS cannot cover them. Set one-shot isolation before beginning the transaction, retain conditional writes, and verify competing connections plus rollback. Do not replace the clause with an unchecked read or a process-local mutex.
  • Write WHERE and JOIN ... ON clauses so existing indexes remain usable.
  • Do not wrap indexed columns in functions inside WHERE or JOIN ... ON.
  • Avoid indexed-column wrappers such as COALESCE, IFNULL, NULLIF, DATE, DATE_FORMAT, YEAR, MONTH, LOWER, UPPER, TRIM, REPLACE, SUBSTRING, CONCAT, CAST, and CONVERT.
  • Exceptions discussed for query-shape/index rules never permit FOR UPDATE. No Task, test expectation, performance argument or concurrency requirement overrides this prohibition.
  • Before approving SQL changes or delivery, run php scripts/tests/unit/no_for_update_runtime_contract_test.php; any violation is FAIL, never an advisory or accepted baseline. Repair locking dependencies with conditional writes/CAS and meaningful concurrency/rollback checks; do not merely delete the clause. Tests must not require the forbidden clause to be present.

Write Safety

  • Do not run MySQL schema changes such as ALTER, DROP, or table rebuilds without explicit user confirmation.
  • Do not write to production DB without explicit user confirmation.
  • Use .agents/rules/DEV_ENV.md for DB hosts, local DB target confirmation, and local DB command details.

Sargable Patterns

  • Date equality: use created_at >= '2026-04-04 00:00:00' AND created_at < '2026-04-05 00:00:00', not DATE(created_at) = '2026-04-04'.
  • Nullable comparison: use explicit branches such as (status = 'Y' OR status IS NULL), not COALESCE(status, 'Y') = 'Y'.
  • Case-insensitive search: prefer normalized stored values or collation, not LOWER(column).
  • Type matching: compare with the native column type, not CAST(column AS ...).

Review Checklist

  • Main filters use indexes.
  • Tenant-scoped queries include CLIENT_ID.
  • Nullable filters are explicit.
  • No subquery or derived-table variant was added.
  • No indexed column is function-wrapped in filtering or join conditions.

---

Source: .agents/rules/DB_QUERY_RULES.md — ctc repository · This page is an English translation of the source document. Identifiers, file paths and code symbols are unchanged.