eKoEN Backend SQL Performance Review
Overview
Act as a backend reviewer focused on database access cost. Prefer concrete query-count, latency, memory, and load risks over style feedback, and keep findings tied to code evidence.
Review Workflow
- Define the scope from the user request and changed files.
- Read
.specify/memory/constitution.md when working in the eKoEN backend so the review respects brownfield constraints and the runtime raw-SQL restriction.
- Read references/red-flags.md before writing findings.
- Trace the full data path from caller to ORM boundary. Do not review a single helper in isolation when it sits inside a loop or hot path.
- Prefer targeted searches and cheap validation commands over broad exploration. Typical search targets are
findOne, find, count, populate, Promise.all, .map(async, for ( and execute(.
- Report findings ordered by severity. Findings come first. Summary stays brief.
Prioritize These Problems
- N+1 reads caused by ORM calls inside loops,
Promise.all, or nested mapping.
- Repeated lookups of the same table by ID or foreign key when one batch query plus a map would do.
- Fetching full entities or populated relations when only a small field set is needed.
- Duplicate queries for existence checks, metadata, or related names inside the same request path.
- Broad
populate usage that hydrates large graphs for a small derived value.
- Filtering, sorting, grouping, or deduplicating large datasets in Node when the database can do it earlier.
- Unbounded reads on hot paths, especially missing limits, pagination, or selective conditions.
- Runtime raw SQL strings or query-builder escapes that violate the repo constitution.
- Query patterns that scale linearly with rule count, device count, or event count on every request.
Validate Findings
Only report issues you can support with code evidence.
For each finding:
- Point to the exact file and line.
- Name the query anti-pattern.
- Explain the concrete scaling problem.
- Describe the smallest safe fix that matches surrounding code.
- Prefer fixes such as batch fetch plus map, narrower field selection, relation preloading, request-scope memoization, or moving filtering into SQL.
If you find no actionable problems, say so explicitly and mention what you did not measure, such as production query counts or missing benchmarks.
Output Format
Produce a Markdown report with these sections for each finding:
Issue summarySeverity as Low, Medium, High, or CriticalCode locationQuery anti-patternWhy it gets expensiveSuggested solutionExample safer pattern
Keep examples short and aligned with local TypeScript and MikroORM patterns.
1---2name: ekoen-backend-sql-performance-review3description: Review skill for eKoEN backend database-access performance. Use when asked to review, audit, inspect, or harden TypeScript backend changes that touch MikroORM, repositories, services, controllers, cron jobs, or SQL-facing logic, especially to catch N+1 queries, per-item lookups inside loops, repeated findOne/find patterns, wasteful hydration, duplicate reads, broad populate usage, app-side filtering of large result sets, or raw SQL escapes before merge.4---56# eKoEN Backend SQL Performance Review78## Overview910Act as a backend reviewer focused on database access cost. Prefer concrete query-count, latency, memory, and load risks over style feedback, and keep findings tied to code evidence.1112## Review Workflow13141. Define the scope from the user request and changed files.152. Read `.specify/memory/constitution.md` when working in the eKoEN backend so the review respects brownfield constraints and the runtime raw-SQL restriction.163. Read [references/red-flags.md](references/red-flags.md) before writing findings.174. Trace the full data path from caller to ORM boundary. Do not review a single helper in isolation when it sits inside a loop or hot path.185. Prefer targeted searches and cheap validation commands over broad exploration. Typical search targets are `findOne`, `find`, `count`, `populate`, `Promise.all`, `.map(async`, `for (` and `execute(`.196. Report findings ordered by severity. Findings come first. Summary stays brief.2021## Prioritize These Problems2223- N+1 reads caused by ORM calls inside loops, `Promise.all`, or nested mapping.24- Repeated lookups of the same table by ID or foreign key when one batch query plus a map would do.25- Fetching full entities or populated relations when only a small field set is needed.26- Duplicate queries for existence checks, metadata, or related names inside the same request path.27- Broad `populate` usage that hydrates large graphs for a small derived value.28- Filtering, sorting, grouping, or deduplicating large datasets in Node when the database can do it earlier.29- Unbounded reads on hot paths, especially missing limits, pagination, or selective conditions.30- Runtime raw SQL strings or query-builder escapes that violate the repo constitution.31- Query patterns that scale linearly with rule count, device count, or event count on every request.3233## Validate Findings3435Only report issues you can support with code evidence.3637For each finding:38391. Point to the exact file and line.402. Name the query anti-pattern.413. Explain the concrete scaling problem.424. Describe the smallest safe fix that matches surrounding code.435. Prefer fixes such as batch fetch plus map, narrower field selection, relation preloading, request-scope memoization, or moving filtering into SQL.4445If you find no actionable problems, say so explicitly and mention what you did not measure, such as production query counts or missing benchmarks.4647## Output Format4849Produce a Markdown report with these sections for each finding:5051- `Issue summary`52- `Severity` as `Low`, `Medium`, `High`, or `Critical`53- `Code location`54- `Query anti-pattern`55- `Why it gets expensive`56- `Suggested solution`57- `Example safer pattern`5859Keep examples short and aligned with local TypeScript and MikroORM patterns.
shaowei-g