esql

name	esql
description	Review Elastic ES|QL queries for Elasticsearch 9.4/9.5 compatibility, correctness, Prometheus and 01.metrics operational-metric semantics, alert reliability, and query performance. Use when Codex is asked to inspect, critique, fix, or rewrite ES|QL pipelines, especially Elastic/Kibana alert queries embedded in .yaml/.yml files or standalone .esql queries.

ES|QL Query Review

Workflow

1. Identify the target Elasticsearch version. If the user says 9.4 or 9.5, treat exact syntax and behavior as version-sensitive and verify against official Elastic docs or matching-version material already supplied inside this project before making hard version claims.
2. Detect the input format before extracting queries:
   • For .yaml/.yml, parse the YAML and review each ES|QL block scalar such as query: |. Read the surrounding alert metadata and comments before reviewing the query.
   • For .esql, treat the file as one query unless the user explicitly identifies multiple queries.
3. Infer the query intent, source grain, time range, whether the alert runner injects the time window, metric format and type, grouping grain, and alert condition before judging the query. Distinguish traditional Prometheus metrics from the 01.metrics operational format before reviewing aggregation semantics. In YAML alerts, use fields such as name, description, severity, interval_sec, and time_window as part of that contract.
4. Read references/review-checklist.md for any non-trivial review. Read references/01-metrics.md when the query uses operational fields such as metrics.calls, metrics.failures, or metrics.duration_*, or when the metric format is ambiguous. Read good-examples.esql and bad-examples.esql when matching this repository's preferred review style or adding new examples. In bad-examples.esql, text after PROBLEM: or Problem: is annotation, not ES|QL.
5. Review in passes: syntax/version, source scope, filter placement, aggregation grain, metrics semantics, null and multivalue behavior, arithmetic, alert determinism, result limits, and output shape.
6. Prefer findings over generic advice. Every finding should name the risky fragment, explain why it matters, and propose a concrete fix or safer rewrite.

Review Scope

• Keep filesystem inspection inside the current ES|QL project and files explicitly supplied by the user.
• Do not search sibling repositories, parent workspaces, application code, deployment code, or metric producers to infer undocumented behavior unless the user explicitly asks for that investigation.
• Use the query, YAML metadata and comments, bundled references, examples, and official Elastic documentation. If these do not establish a data contract, state the assumption or uncertainty in the review instead of searching external code.

Review Stance

• Be strict about correctness, false negatives, false positives, arbitrary alert rows, and obviously excessive shard scans.
• Be careful with inferred data contracts. If a problem depends on undocumented assumptions such as "one document per metric per timestamp", say that explicitly.
• Do not claim that a final KEEP or DROP improves aggregation cost. It mostly reduces response payload after upstream work has already happened.
• Do not rewrite the whole query unless the user asks. For review, preserve the original intent and give minimal corrected fragments.
• Treat good-examples.esql as style calibration: narrow sources where possible, filter early, name intermediate fields, use conditional STATS, use deterministic SORT before top-N LIMIT, and produce alert-friendly details.

Output Format

Start with findings, ordered by severity. Use this shape:

[severity] Short title
Fragment: `...`
Why: ...
Fix: ...

Use severities:

• blocker: invalid ES|QL, wrong version, or query cannot return intended result.
• high: likely false alert, missed alert, or severe avoidable cluster cost.
• medium: fragile metric semantics, cardinality risk, nondeterministic output, or maintainability issue that can affect operations.
• low: readability, naming, comments, magic constants, or minor style.

After findings, include a corrected fragment or full corrected query only when it materially helps. If there are no material issues, say so and list residual assumptions that could not be verified from the query alone.

Version Notes

For exact 9.4/9.5 behavior, prefer current official Elastic documentation or a checked-out matching Elastic branch over memory. Useful starting points are in references/elastic-docs.md.