ln-651-query-efficiency-auditor

Paths: File paths ( shared/ , references/ , ../ln- ) are relative to skills repo root. If not found at CWD, locate this SKILL.md directory and go up one level for repo root. Query Efficiency Auditor (L3 Worker) Specialized worker auditing database query patterns for redundancy, inefficiency, and misuse. Purpose & Scope Worker in ln-650 coordinator pipeline - invoked by ln-650-persistence-performance-auditor Audit query efficiency (Priority: HIGH) Check redundant fetches, batch operation misuse, caching scope problems Write structured findings to file with severity, location, effort, recommendations Calculate compliance score (X/10) for Query Efficiency category Inputs (from Coordinator) MANDATORY READ: Load shared/references/audit_worker_core_contract.md . Receives contextStore with: tech_stack , best_practices , db_config (database type, ORM settings), codebase_root , output_dir . Domain-aware: Supports domain_mode + current_domain . Workflow MANDATORY READ: Load shared/references/two_layer_detection.md for detection methodology. Parse context from contextStore Extract tech_stack, best_practices, db_config, output_dir Determine scan_path (same logic as ln-624) Scan codebase for violations All Grep/Glob patterns use scan_path Trace call chains for redundant fetches (requires reading caller + callee) Collect findings with severity, location, effort, recommendation Calculate score using penalty algorithm Write Report: Build full markdown report in memory per shared/templates/audit_worker_report_template.md , write to {output_dir}/651-query-efficiency.md in single Write call Return Summary: Return minimal summary to coordinator (see Output Format) Audit Rules (Priority: HIGH) 1. Redundant Entity Fetch What: Same entity fetched from DB twice in a call chain Detection: Find function A that calls repo.get(id) or session.get(Model, id) , then passes id (not object) to function B Function B also calls repo.get(id) or session.get(Model, id) for the same entity Common pattern: acquire_next_pending() returns job, but _process_job(job_id) re-fetches it Detection patterns (Python/SQLAlchemy): Grep for repo.get_by_id|session.get(|session.query.filter.id in service/handler files Trace: if function receives entity_id: int/UUID AND internally does repo.get(entity_id) , check if caller already has entity object Check expire_on_commit setting: if False , objects remain valid after commit Severity: HIGH: Redundant fetch in API request handler (adds latency per request) MEDIUM: Redundant fetch in background job (less critical) Downgrade when: Fetch in initialization/migration code (runs once) → LOW. Admin-only endpoint with low traffic → downgrade one level Recommendation: Pass entity object instead of ID, or remove second fetch when expire_on_commit=False Effort: S (change signature to accept object instead of ID) 2. N-UPDATE/DELETE Loop What: Loop of individual UPDATE/DELETE operations instead of single batch query Detection: Pattern: for item in items: await repo.update(item.id, ...) or for item in items: await repo.delete(item.id) Pattern: for item in items: session.execute(update(Model).where(...)) Detection patterns: Grep for for . in .: followed by repo.(update|delete|reset|save|mark_) within 1-3 lines Grep for for . in .: followed by session.execute(.*update( within 1-3 lines Severity: HIGH: Loop over >10 items (N separate round-trips to DB) MEDIUM: Loop over <=10 items Downgrade when: Loop in bootstrap/migration code (runs once) → LOW. Admin-only endpoint → downgrade one level Recommendation: Replace with single UPDATE ... WHERE id IN (...) or session.execute(update(Model).where(Model.id.in_(ids))) Effort: M (rewrite query + test) 3. Unnecessary Resolve What: Re-resolving a value from DB when it is already available in the caller's scope Detection: Method receives profile_id and resolves engine from it, but caller already determined engine Method receives lang_code and looks up dialect_id, but caller already has both lang and dialect Pattern: function receives X_id , does get(X_id) , extracts .field , when caller already has field Severity: MEDIUM: Extra DB query per invocation, especially in high-frequency paths Recommendation: Split method into two variants: with_known_value(value, ...) and resolving_value(id, ...) ; or pass resolved value directly Effort: S-M (refactor signature, update callers) 4. Over-Fetching What: Loading full ORM model when only few fields are needed Detection: session.query(Model) or select(Model) without .options(load_only(...)) for models with >10 columns Especially in list/search endpoints that return many rows Pattern: loading full entity but only using 2-3 fields Severity: MEDIUM: Large models (>15 columns) in list endpoints LOW: Small models (<10 columns) or single-entity endpoints Recommendation: Use load_only() , defer() , or raw select(Model.col1, Model.col2) for list queries Effort: S (add load_only to query) 5. Missing Bulk Operations What: Sequential INSERT/DELETE/UPDATE instead of bulk operations Detection: for item in items: session.add(item) instead of session.add_all(items) for item in items: session.delete(item) instead of bulk delete Pattern: loop with single INSERT per iteration Severity: MEDIUM: Any sequential add/delete in loop (missed batch optimization) Recommendation: Use session.add_all() , session.execute(insert(Model).values(list_of_dicts)) , bulk_save_objects() Effort: S (replace loop with bulk call) 6. Wrong Caching Scope What: Request-scoped cache for data that rarely changes (should be app-scoped) Detection: Service registered as request-scoped (e.g., via FastAPI Depends() ) with internal cache ( _cache dict, _loaded flag) Cache populated by expensive query (JOINs, aggregations) per each request Data TTL >> request duration (e.g., engine configurations, language lists, feature flags) Detection patterns: Find classes with _cache , _loaded , _initialized attributes Check if class is created per-request (via DI registration scope) Compare: data change frequency vs cache lifetime Severity: HIGH: Expensive query (JOINs, subqueries) cached only per-request MEDIUM: Simple query cached per-request Recommendation: Move cache to app-scoped service (singleton), add TTL-based invalidation, or use CacheService with configurable TTL Effort: M (change DI scope, add TTL logic) Scoring Algorithm MANDATORY READ: Load shared/references/audit_worker_core_contract.md and shared/references/audit_scoring.md . Output Format MANDATORY READ: Load shared/references/audit_worker_core_contract.md and shared/templates/audit_worker_report_template.md . Write report to {output_dir}/651-query-efficiency.md with category: "Query Efficiency" and checks: redundant_fetch, n_update_delete_loop, unnecessary_resolve, over_fetching, missing_bulk_ops, wrong_caching_scope. Return summary to coordinator: Report written: docs/project/.audit/ln-650/{YYYY-MM-DD}/651-query-efficiency.md Score: X.X/10 | Issues: N (C:N H:N M:N L:N) Critical Rules MANDATORY READ: Load shared/references/audit_worker_core_contract.md . Do not auto-fix: Report only Trace call chains: Rules 1 and 3 require reading both caller and callee ORM-aware: Check expire_on_commit , autoflush , session scope before flagging redundant fetches Context-aware: Small datasets or infrequent operations may justify simpler code Exclude tests: Do not flag test fixtures or setup code Definition of Done MANDATORY READ: Load shared/references/audit_worker_core_contract.md . contextStore parsed successfully (including output_dir) scan_path determined (domain path or codebase root) All 6 checks completed: redundant fetch, N-UPDATE loop, unnecessary resolve, over-fetching, bulk ops, caching scope Findings collected with severity, location, effort, recommendation Score calculated using penalty algorithm Report written to {output_dir}/651-query-efficiency.md (atomic single Write call) Summary returned to coordinator Reference Files Audit output schema: shared/references/audit_output_schema.md Version: 1.0.0 Last Updated: 2026-02-04