planetscale-query-insights-and-tags

The full skill.

Original instructions from the publisher’s SKILL.md

# Query Insights and tags

## Purpose

Use PlanetScale Insights to understand query behavior, then recommend SQLCommenter-compatible tags that make future diagnosis and Traffic Control possible. Do not change database settings or repository code without approval.

## What to inspect

### Query behavior

For the selected database and branch, inspect:

- Top queries by total time.
- Top queries by time per execution.
- Top queries by rows read.
- Top queries by execution count.
- For Postgres, top queries by CPU usage (`sort=cpuTime` or
  `sort=percentCpuTime` on the Insights API).
- Queries with errors.
- Notable queries and active anomalies.
- Query patterns affected by recent deploys.
- Query patterns attached to schema recommendations.
- For sharded Vitess databases, vindex usage for each query pattern: the
  percentage of traffic using relevant vindexes and the vindex-usage trend
  over time. The API exposes per-pattern `index_usages` and
  `routing_index_usages`; get the trend from the dashboard Vindexes tab or
  by comparing API windows. Treat missing or declining relevant-vindex
  usage as an indexing or routing investigation input, not as proof that a
  new index is required.

### Insights API surface

Query Insights is public API: read-only GET endpoints under
`organizations/{org}/databases/{db}/branches/{branch}`, authorized by a
service token or OAuth token with `read_databases`/`read_database`.

- `/insights` — aggregated statistics per query pattern over the requested
  window. Set the window with `from`/`to` (ISO 8601) or `period` (for
  example `1h`, `24h`); search SQL patterns with `q`; sort server-side with
  `sort` and `dir` — sort keys include `count`, `errorCount`, `rowsRead`,
  `totalTime`, `cpuTime`, `ioTime`, `percentTime`, `percentCpuTime`,
  `p50Latency`, `p99Latency`, `maxLatency`, `egressBytes`, and the
  `trafficControlWarnings`/`trafficControlThrottled` family. Filter with
  `tablet_type` (`primary`, `replica`, `rdonly`) and `type` (`SELECT`,
  `INSERT`, `UPDATE`, `DELETE`); trim responses with `fields`; paginate
  with `page`/`per_page`.
- `/insights/{fingerprint}` — individual collected executions for a
  pattern (timestamps, duration, rows, username, client address, error
  message). Available regardless of raw query collection; raw collection
  adds literal parameter values to these records.
  `/insights/{fingerprint}/summary` returns the single-pattern aggregate;
  `/insights/queries/{id}` fetches one execution.
- `/insights/errors` — error fingerprints with counts and messages (`q`
  searches the error message; sort by `count`, `lastRun`, `totalTime`, or
  `timePerQuery`). `/insights/errors/{fingerprint}` lists the failing
  executions behind one error fingerprint.
- `/insights/anomalies` and `/insights/anomalies/{id}` — anomaly windows
  with per-query correlation coefficients identifying which patterns moved
  with the anomaly.
- `/insights/tags` — tag keys with observed values (`values_limit`,
  `literal_values_only`, and `fingerprint`/`keyspace` filters);
  `/insights/tags/{tag}` for a single key. `/insights/tags/summaries`
  groups the full statistics schema by one or more tag keys via the `tags`
  parameter — use it to attribute load to routes, jobs, or features
  without client-side aggregation.
- `/insights/{fingerprint}/traffic/budgets` — the Traffic Control budgets
  and rules that affect a fingerprint (Postgres).

Aggregates cover the requested window. Duration fields use names like
`sum_total_duration_millis`, with explicit share-of-window percent fields
(`sum_total_duration_percent`); both totals and percentages are reliable
for the window requested.

The response schema is shared across engines, but some fields are
engine-specific: CPU/IO durations and block-cache statistics
(`sum_cpu_duration_millis`, `blocks_read`, `block_cache_hit_ratio`, …) are
populated for Postgres; shard queries, keyspaces, `tablet_type`, and
routing-index (vindex) usage are populated for Vitess.

### Tag coverage

For each expensive or anomalous query, determine:

- Is it tagged?
- Which service produced it?
- Which route, job, controller, or action produced it?
- Which deployment SHA produced it?
- Is the tag cardinality safe?
- Are tags consistent across frameworks and languages?
- Use the tags API to answer these questions: `/insights/tags` shows which
  keys and values are present, and `/insights/tags/summaries?tags=...`
  attributes load per tag value. In the Vitess dashboard, filter the query
  table with `tag:key:value` and drill into query details to see tags on
  individual executions. Built-in query metadata and SQLCommenter tags are
  both valid attribution sources.

### Raw query collection

Check whether raw query / complete query collection is enabled. On
Postgres the effective state is the `pginsights.raw_queries` cluster
parameter (per branch, dashboard Extensions tab, default `false`); the
database API object's `insights_raw_queries` field is a separate surface.
When the two differ, report the cluster parameter as the effective state
and do not describe the difference as an inconsistency. On Vitess there
is no cluster parameter; the database API's `insights_raw_queries` field
is the effective state.

Report it as a capability state, not a risk posture. Raw query collection
records literal parameter values per execution, which pattern-level Insights
data does not provide. It is the mechanism for isolating which specific
invocation of a pattern is pathological. Execution-level records are
retrievable from `/insights/{fingerprint}` with or without raw collection;
raw collection adds the literal parameter values to those records.

When it is disabled, the finding is a capability gap: identify the query
patterns in this assessment where pattern-level data is insufficient
(unexplained latency variance within a fingerprint, tenant- or
parameter-dependent behavior) and state that raw collection would resolve
them. State the operational property once, as fact: literal values become
visible to the observability pipeline. Where the customer's data-handling
requirements constrain this, scoped enablement (incident windows, defined
retention) and leaving collection disabled are both valid outcomes —
record the rationale rather than a default judgment in either direction.

Tags and raw collection are complementary instruments: tags attribute a
pattern to a code path; raw collection identifies the specific invocation.
Assessments should evaluate both.

## SQLCommenter tag schema

Recommend this baseline tag set:

- `application`: stable app name.
- `service`: service or process name.
- `environment`: production, staging, development.
- `route`: normalized route template, for example `/accounts/:id/orders`, not `/accounts/123/orders`.
- `controller`: framework controller name where applicable.
- `action`: framework action name where applicable.
- `job`: background job class or worker name.
- `queue`: background queue.
- `feature`: bounded feature name for traffic classes like export, report, search, billing, checkout.
- `release_sha`: short git SHA or deploy identifier.
- `source`: app, worker, script, agent, mcp, bi, integration.
- `tenant_tier`: free, pro, enterprise, internal, only if bounded.

Do not recommend these tags by default:

- `user_id`
- `request_id`
- `tenant_id`
- `email`
- `session_id`
- raw URL
- unbounded GraphQL operation text
- access token
- secret

If the customer needs tenant-level isolation, recommend a bounded abstraction first, such as tenant tier, cell, shard, or customer class. Tenant ID is only acceptable with explicit approval after cardinality and privacy review.

## Cardinality rules

Flag a tag as unsafe when:

- Values are unbounded.
- Values include IDs, UUIDs, emails, slugs, or raw paths.
- The same query pattern emits many unique tag combinations.
- The tag would make Insights or Traffic Control aggregation noisy.

Recommend normalizing at the application boundary.

## Analysis output

For each top query pattern, produce:

- Fingerprint or normalized query.
- Current metrics.
- Current tags.
- Missing tags.
- Likely source in application code.
- Whether it is a schema recommendation candidate.
- Whether it is a Traffic Control candidate.
- Whether it is an application optimization candidate.

## Recommendation classes

### Add tags

Recommend SQLCommenter instrumentation when query attribution is weak.

### Improve tag normalization

Recommend replacing high-cardinality tags with bounded values.

### Add Traffic Control warning budget

For Postgres only, recommend `warn` mode budgets for expensive but important routes, jobs, analytics, exports, or third-party integrations.

### Add schema recommendation workflow

For Vitess, recommend turning open schema recommendations into branch/deploy-request work. For Postgres, recommend turning them into reviewed migrations against a non-production branch.

### Fix code path

Recommend a repository PR when the expensive query is caused by N+1, missing pagination, accidental eager load, unbounded export, broad search, or polling.

## Safety rules

Do not:

- Enable raw query collection.
- Add tags to code.
- Change Traffic Control budgets.
- Apply schema recommendations.
- Run production EXPLAIN ANALYZE on expensive queries.

Without explicit approval.

## Output

Return:

- Query risk table.
- Tag coverage table.
- Bad/high-cardinality tag table.
- Recommended tag schema for this application.
- Candidate Traffic Control slices.
- Candidate schema and code changes.
- Proposed changes requiring approval.

End with:

“No Insights, tag, repository, or Traffic Control changes have been applied.”