AGENT SKILL

ln-651-query-efficiency-auditor

Checks redundant fetches, N-UPDATE/DELETE loops, unnecessary resolves, over-fetching, missing bulk operations, wrong caching scope. Returns findings with severity.

License 许可
MIT
最近更新
2026-05-02
Stars
★ 3
Forks
1

01 前言 Front Matter

name
ln-651-query-efficiency-auditor
license
MIT
description
Checks redundant fetches, N-UPDATE/DELETE loops, unnecessary resolves, over-fetching, missing bulk operations, wrong caching scope. Returns findings with severity.
allowed-tools
Read, Grep, Glob, Bash

03 SKILL.md 正文

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.

  1. Parse context from contextStore

    • Extract tech_stack, best_practices, db_config, output_dir
    • Determine scan_path (same logic as ln-624)
  2. Scan codebase for violations

    • All Grep/Glob patterns use scan_path
    • Trace call chains for redundant fetches (requires reading caller + callee)
  3. Collect findings with severity, location, effort, recommendation

  4. Calculate score using penalty algorithm

  5. 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

  6. 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

04 同仓库更多 Skills

levalencia
levalencia/agent-god-mode

zarr-python

Chunked N-D arrays for cloud storage. Compressed arrays, parallel I/O, S3/GCS integration, NumPy/Dask/Xarray compatible, for large-scale scientific computing pipelines.

★ 3
levalencia
levalencia/agent-god-mode

zabbix

Zabbix monitoring system automation via API and Python. Use when: (1) Managing hosts, templates, items, triggers, or host groups, (2) Automating monitoring configuration, (3) Sending data via Zabbix trapper/sender, (4) Querying historical data or events, (5) Bulk operations on Zabbix objects, (6) Maintenance window management, (7) User/permission management

开发工程师后端与接口Skill与智能体开发
★ 3
levalencia
levalencia/agent-god-mode

x-twitter-growth

X/Twitter growth engine for building audience, crafting viral content, and analyzing engagement. Use when the user wants to grow on X/Twitter, write tweets or threads, analyze their X profile, research competitors on X, plan a posting strategy, or optimize engagement. Complements social-content (generic multi-platform) with X-specific depth: algorithm mechanics, thread engineering, reply strategy, profile optimization, and competitive intelligence via web search.

★ 3
levalencia
levalencia/agent-god-mode

what-if-oracle

Run structured What-If scenario analysis with multi-branch possibility exploration. Use this skill when the user asks speculative questions like "what if...", "what would happen if...", "what are the possibilities", "explore scenarios", "scenario analysis", "possibility space", "what could go wrong", "best case / worst case", "risk analysis", "contingency planning", "strategic options", or any question about uncertain futures. Also trigger when the user faces a fork-in-the-road decision, wants to stress-test an idea, or needs to think through consequences before committing.

★ 3
levalencia
levalencia/agent-god-mode

vercel-react-best-practices

React and Next.js performance optimization guidelines from Vercel Engineering. This skill should be used when writing, reviewing, or refactoring React/Next.js code to ensure optimal performance patterns. Triggers on tasks involving React components, Next.js pages, data fetching, bundle optimization, or performance improvements.

前端开发
★ 3
levalencia
levalencia/agent-god-mode

venue-templates

Access comprehensive LaTeX templates, formatting requirements, and submission guidelines for major scientific publication venues (Nature, Science, PLOS, IEEE, ACM), academic conferences (NeurIPS, ICML, CVPR, CHI), research posters, and grant proposals (NSF, NIH, DOE, DARPA). This skill should be used when preparing manuscripts for journal submission, conference papers, research posters, or grant proposals and need venue-specific formatting requirements and templates.

内容创作者写作与文案资料检索与研究
★ 3