query-design
Skill2 starsChanged 36 days agoMITView on GitHub
Design or review SQL reads, writes, scans, backfills, and slow database paths. Use before changing a store method or any loop that queries. Covers round-trip arithmetic, batching, keyset paging, index evidence, row budgets, PlanetScale Vitess limits, Hyperdrive connections, statement-count tests, and statement-template fences.
11 other projects use a version of this.
Adopted by
Most recently taken up by
- microsoft/awesome-rayfintheir own version, first seen Nov 15, 2026
- microsoft/DataHub-App-for-Microsoft-Copilot-Agents-Cowork-and-Autopilotstheir own version, first seen Nov 8, 2026
- microsoft/fabric-apps-analytic-templatestheir own version, first seen Nov 3, 2026
- microsoft/ValueLens-for-Microsoft-Copilottheir own version, first seen Oct 25, 2026
- microsoft/ConsumptionCentral-for-Microsoft-Copilottheir own version, first seen Oct 25, 2026
- microsoft/frontiertour27-LTG291-turn-your-data-into-production-apps-with-rayfintheir own version, first seen Oct 17, 2026
Used with
Projects with it also use rayfin-functions.
Other files people use alongside this one.
Files found in the same public projects as a version of this one, more often than they turn up anywhere else. Each project counts once, large collections count for less, and this file's own repository is left out.
MicrosoftSkillrayfin-functions
Use when writing Rayfin user-defined functions (UDFs), invoking functions from frontend code, or using functions CLI commands. Triggers: rayfin functions, udf.func, UserDataFunctions, RayfinContext, FabricContext, AppFunctionsSchema, functions init, dev functions apply, client.functions, invoke, func start, function_app.ts, types.ts, rayfin/functions/, FunctionClient, FunctionsSchema, fabric-user-data-functions, functionsBaseUrl, VITE_RAYFIN_FUNCTIONS_URL, bootstrap.ts, rayfinClient.ts
Also in 3 projects that use this
Other versions11 projects use a version of this.
Word for word · 0
None found.
Edited · 0
None found.
Wrote their own version · 11
- microsoft/rayfin★ 646same name
- microsoft/awesome-rayfin★ 114same name
- microsoft/ConsumptionCentral-for-Microsoft-Copilot★ 33same name
- microsoft/fabric-apps-analytic-templates★ 30same name
- microsoft/ValueLens-for-Microsoft-Copilot★ 23same name
- microsoft/AnalyticsHub-Dashboard★ 11same name
- microsoft/DataHub-App-for-Microsoft-Copilot-Agents-Cowork-and-Autopilots★ 11same name
- microsoft/aitour27-LTG291-turn-your-data-into-production-apps-with-rayfin★ 2same name
- microsoft/frontiertour27-LTG291-turn-your-data-into-production-apps-with-rayfin★ 2same name
- microsoft/aitour27-BRK390-building-ai-applications-with-microsoft-databases-and-fabric★ 1same name
1 more not listed
How matches are found. Word for word: the exact same text. Edited: shares passages with this file (“99% same” means 99% of its five-word phrases also appear here; 10% or more counts). Their own version: similar meaning, or, weakest, the same name. We can’t tell which project had it first. Projects are counted among the 200 most-starred candidates, and mentions are links to the repository we found, up to 50 per site, so both counts are a floor.
In this repository15 other files it gives its agents.
- AGENTS.mdAGENTS.md
- CLAUDE.mdCLAUDE.md
- add-a-capabilitySkill
- add-a-lifecycle-machineSkill
- add-a-storeSkill
- gardenerSkill
- investigate-a-failureSkill
- keep-or-cutSkill
- learn-alchemySkill
- profile-a-workerSkill
- rat-stack-modeSkill
- shipSkill
- test-behaviourSkill
- uncomplectSkill
- write-a-wiki-pageSkill
The file
We split this skill into the rules it gives an agent
4 rules. 0 are common, 3 are distinctive, and 1 is very specific to this project.
0
Common
Most of the 11 edited versions we read keep these.
3
Distinctive
This author's own choices. Worth a look.
1
Very specific
Tied to their project's names, files or tools. Probably skip.
- Common
- Distinctive
- Very specific
- Distinctive: line 36
Consider an illustrative scan of about 9k threads:
- Distinctive: line 68
Select only columns the caller uses: Keep large bodies, JSON, and text fields out of counts and metadata scans.
- Distinctive: line 70
State a concurrency bound: Start production scans with serial pages over one connection.
- Very specific: line 178
Run the consumer's native checks and provider checks.
“Common” is measured: kept by at least 6 in 10 of the 11 edited versions of this file we could read. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.
Files
Design queries around round trips
Count round trips before writing SQL. Small tables can still produce slow runs when each row needs several database calls.
round trips per call × calls per run × RTT = wall timeRTT means round-trip time. This arithmetic estimates serial network time; query execution, decoding, and connection acquisition add costs.
Consider an illustrative scan of about 9k threads:
- Per-thread reads: 5 round trips × about 9.3k threads × 81 ms ≈ 63 minutes.
- Paged and batched reads: 2 round trips × 93 pages × about 100 ms ≈ 20 seconds.
- These are planning inputs. Measure RTT where the caller runs before choosing its budget.
A capability call has a constant round-trip budget, independent of result size. State that number and bound its result. A scan costs O(pages), never O(rows). Count transaction control statements and any final empty-page probe too.
Done means the store implementation matches a written query plan, and checks detect violations of its statement and access-path budgets.
1. Name the question, caller, and rate
Record these before changing the store:
| Field | Evidence to record |
| Question | The fact or change the caller needs |
| Caller | Capability, scan, export, or background job |
| Rate | Calls per invocation and invocations per second or run |
| Authority | System of record, derived copies, and writer |
| Row budget | Maximum rows read with tables at 100× today's size |
| Statements | Round trips per call, per page, and per run |
| Timing | Measured RTT, predicted wall time, and observed page time |
| Concurrency | Maximum simultaneous queries or transactions |
Measure table sizes and inspect existing indexes. Keep credentials and row contents out of measurements and reports. Route operator, export, and monitor reads to a replica or a copy. Record any consistency tradeoff. Compare repeated joins or aggregates with a keyed read model before adding a hot-path scan.
2. Apply the shared rules
- Never query inside a loop over rows. Page parents, fetch their children with one batched
IN, then join in memory. Apply the same authorization filter to both queries. Keep analysis pure over the fetched rows. Bound child fan-out and result bytes too. Page children separately when one parent can exceed the batch budget. - Use keyset paging, never OFFSET. Order by an indexed tuple that ends in a unique column. Match the cursor predicate to each column's sort direction. Fetch
limit + 1to detect another page. Emit a cursor from the last returned row. Exclude the extra probe row from the returned page. - Name each query's index and prove it with EXPLAIN. Use representative data and the actual query builder. Record the chosen
key, accesstype, estimatedrows, and sorting work for MySQL. Expectconst,eq_ref,ref,range, or an index-ordered limit that meets the row budget. Equivalent plan fields apply to other engines. Inspect examined rows when the engine supports execution analysis. Fix full scans or filesorts on growing tables. Justify any index hint with plan evidence. - Select only columns the caller uses. Keep large bodies, JSON, and text fields out of counts and metadata scans.
- Keep transactions short. Name the atomic writes or consistent reads that require one. Never wrap an entire scan in a transaction. Keep external I/O outside the transaction.
- State a concurrency bound. Start production scans with serial pages over one connection. Measure before adding parallelism. Explain the load budget and why more concurrent work helps.
- Show progress on long runs. Emit phase, completed pages, counts, and elapsed time to stderr. Use counts-only output. Set a measured stall deadline and return a typed failure with phase and completed count.
- Use a store boundary. A port declares the operations that callers need. An adapter implements those operations for a provider. A Layer builds services and supplies their construction dependencies. Follow add-a-store for job-shaped operations and adapter Layers. Keep SQL, Drizzle, connection pools, and driver calls inside the adapter. Callers depend on the port.
A result limit bounds returned rows. EXPLAIN evidence must also prove the scanned-work budget. For tenant-scoped MySQL stores, lead the supporting index with the tenant key and preserve that filter throughout paging.
3. Check PlanetScale Vitess limits
These limits apply to PlanetScale Vitess transactional workloads. They do not describe PlanetScale Postgres. The Vitess system limits page supplies the published values.
| Limit | Published value | Design consequence |
| Transaction timeout | 20 s | Treat it as fixed in application configuration. Use short transactions. |
| Autocommit query timeout | 900 s | Page work well before the timeout. |
| Rows returned, updated, or deleted per query | 100k | Bound reads and bulk writes. |
| Result size per query | 64 MiB | Bound bytes as well as rows. |
| Transaction pool | Capped; capacity varies by instance type | Limit simultaneous transactions and keep them short. |
The limits page says support can sometimes adjust limits per database. It offers no application setting for the transaction timeout. Do not make a design depend on a larger timeout.
Map a killed transaction to a typed adapter failure. Preserve the operation, provider code, and redacted cause. An error containing 1105 and context deadline exceeded needs timeout handling; 1105 alone is a generic error code. The errors page also documents 2013 deadline errors and 1105 transaction-aborted timeout errors.
Vitess pools MySQL connections through vttablet. SHOW PROCESSLIST shows those pool connections, rather than a one-to-one application-session list. Use application-side timing and provider query diagnostics to inspect a stalled run.
4. Keep Hyperdrive connections request-scoped
For the shared MySQL path, use mysql2 text-protocol .query() inside the adapter, as drovr does. Keep that protocol choice explicit in the query runner. Count every statement it sends.
Create the connection within the Worker request and reuse it for that request's statements. Release it through the request lifecycle. Hyperdrive owns reuse of underlying database connections across requests. Avoid a second pool or a module-global driver connection. See Cloudflare's MySQL example for connection lifetime details.
5. Make checks stronger than prose
Count statements through the real store
Use it.effect and a counting executor Layer at the adapter boundary. The executor port describes statement execution; its test wrapper counts calls while delegating to the real fixture executor. Keep counters scoped to each test. Exclude fixture setup from the measured call count. This is pseudocode for a consumer-owned port, rather than a shipped rat-stack MySQL API:
it.effect("scan obeys its statement budget", Effect program):
generate N parents and bounded children
seed the native database fixture
provide the real store with a counting executor Layer
reset the statement counter after setup
scan all pages through the store port
verify complete results, ordering, and authorization filters
assert statements <= planned statements per page × page count
+ planned fixed overheadGenerate cases below, at, and above page boundaries. Include empty pages and parents with different child counts. Vary parent count within one page: its statement count must remain constant. Include cases where the planted N+1 exceeds the budget.
Replace the batched child read with one query per parent. The count test must fail. Restore batching and run it again. Record both outcomes before trusting the check. Check rows and bytes separately; a statement-count test cannot prove index use or bounded child fan-out.
Commit statement templates and enforce a plan fence
A fence is an enforced check that rejects prohibited work. A handler is the server-side implementation of an action. A stronger check records every statement template the native suites issue, with its source site and exercising handler. Normalize placeholder-list lengths in IN and multi-row VALUES. Never record parameter values.
committed manifest entry:
template: normalized statement text
sites: source locations that issue it
handlers: named handlers that exercise it
sentBy: Worker | operator | fixture | diagnostic
executor plan fence:
normalize each application statement
fail when its template is absent from the manifest
fail when its source or execution surface is disallowed
inspect each query's plan against its index and row budget
coverage check:
fail when a required template has no exercising handler
compare issued templates with each handler's declared templatesCount begin, commit, and rollback. Distinguish fixture setup from application statements. Capture driver or auth pools separately if they bypass the measured executor. Report uncovered paths as test gaps. Run the same real handlers natively and through a non-production Hyperdrive connection against a PlanetScale development branch. The native plan proves local access paths. The provider run proves protocol and decoding behavior on that route.
Plant a missing manifest entry and observe the fence fail. Restore it and record the passing coverage result. A manifest proves exercised statement coverage. Add fixtures for untested branches before claiming broader coverage.
6. Keep consumer details in their playbooks
- drovr: Uses Hyperdrive text-protocol
.query(), a committed statement-template manifest, and a plan fence. Native MySQL 8.4 CI checks exercise its real store handlers. A provider runner checks the Hyperdrive route separately. - front-desk: Uses Drizzle behind store ports. Scans from an operator computer page parents and batch child reads. Its playbook owns connection setup, scan phases, and progress counters.
- AIH support: Uses reader-role credentials for support reads. Its playbook owns allowed columns and authorization filters.
These examples describe consumer choices. A cartridge is a package for one job, with its implementation and required infrastructure. rat-stack's current database cartridge supplies D1 and Hyperdrive Postgres adapters. A consumer supplies its MySQL executor port and counting Layer through the same store boundary.
7. Read the prior art
PlanetScale database-skills is MIT-licensed. Read its material for deeper engine guidance:
- mysql and vitess.
- n-plus-one and explain-analysis.
- composite-indexes and covering-indexes.
- query-optimization-pitfalls and connection-management.
Markus Winand's No OFFSET, from Use The Index, Luke, explains keyset pagination. Measure real limits: count store calls as well as rows. Batching can move more rows without multiplying network round trips.
8. Finish with evidence
Report:
- Question, caller, rate, row budget, and concurrency bound.
- Each query, its named index, and pasted EXPLAIN evidence.
- Round trips per call and run, predicted wall time, and observed timing.
- Store port, adapter, statement-count results, and planted N+1 failure.
- Manifest coverage, plan fence result, and planted missing-template failure when that stronger check exists.
- Progress behavior, typed timeout handling, and untested provider paths.
Run the consumer's native checks and provider checks. In rat-stack, finish with pnpm turbo run check test build.
Does it work?
Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.
No reviews yet
People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.
- It worked
- Partly
- It didn't
Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.
Your trail
Files you open will collect here, so you can find them again.
Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.
Now do this for your own.
Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.

