Update to PlanetScale

{
  "name": "query-insights-and-tags",
  "description": "Use PlanetScale Insights and SQLCommenter-style query tags to attribute database load, identify risky queries, and prepare safe Traffic Control or schema recommendations.",
  "included_files": [],
  "skill_md_contents": "---\nname: query-insights-and-tags\ndescription: Use PlanetScale Insights and SQLCommenter-style query tags to attribute database load, identify risky queries, and prepare safe Traffic Control or schema recommendations.\n---\n\n# Query Insights and tags\n\n## Purpose\n\nUse 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.\n\n## What to inspect\n\n### Query behavior\n\nFor the selected database and branch, inspect:\n\n- Top queries by total time.\n- Top queries by time per execution.\n- Top queries by rows read.\n- Top queries by execution count.\n- For Postgres, top queries by CPU usage (`sort=cpuTime` or\n  `sort=percentCpuTime` on the Insights API).\n- Queries with errors.\n- Notable queries and active anomalies.\n- Query patterns affected by recent deploys.\n- Query patterns attached to schema recommendations.\n- For sharded Vitess databases, vindex usage for each query pattern: the\n  percentage of traffic using relevant vindexes and the vindex-usage trend\n  over time. The API exposes per-pattern `index_usages` and\n  `routing_index_usages`; get the trend from the dashboard Vindexes tab or\n  by comparing API windows. Treat missing or declining relevant-vindex\n  usage as an indexing or routing investigation input, not as proof that a\n  new index is required.\n\n### Insights API surface\n\nQuery Insights is public API: read-only GET endpoints under\n`organizations/{org}/databases/{db}/branches/{branch}`, authorized by a\nservice token or OAuth token with `read_databases`/`read_database`.\n\n- `/insights` — aggregated statistics per query pattern over the requested\n  window. Set the window with `from`/`to` (ISO 8601) or `period` (for\n  example `1h`, `24h`); search SQL patterns with `q`; sort server-side with\n  `sort` and `dir` — sort keys include `count`, `errorCount`, `rowsRead`,\n  `totalTime`, `cpuTime`, `ioTime`, `percentTime`, `percentCpuTime`,\n  `p50Latency`, `p99Latency`, `maxLatency`, `egressBytes`, and the\n  `trafficControlWarnings`/`trafficControlThrottled` family. Filter with\n  `tablet_type` (`primary`, `replica`, `rdonly`) and `type` (`SELECT`,\n  `INSERT`, `UPDATE`, `DELETE`); trim responses with `fields`; paginate\n  with `page`/`per_page`.\n- `/insights/{fingerprint}` — individual collected executions for a\n  pattern (timestamps, duration, rows, username, client address, error\n  message). Available regardless of raw query collection; raw collection\n  adds literal parameter values to these records.\n  `/insights/{fingerprint}/summary` returns the single-pattern aggregate;\n  `/insights/queries/{id}` fetches one execution.\n- `/insights/errors` — error fingerprints with counts and messages (`q`\n  searches the error message; sort by `count`, `lastRun`, `totalTime`, or\n  `timePerQuery`). `/insights/errors/{fingerprint}` lists the failing\n  executions behind one error fingerprint.\n- `/insights/anomalies` and `/insights/anomalies/{id}` — anomaly windows\n  with per-query correlation coefficients identifying which patterns moved\n  with the anomaly.\n- `/insights/tags` — tag keys with observed values (`values_limit`,\n  `literal_values_only`, and `fingerprint`/`keyspace` filters);\n  `/insights/tags/{tag}` for a single key. `/insights/tags/summaries`\n  groups the full statistics schema by one or more tag keys via the `tags`\n  parameter — use it to attribute load to routes, jobs, or features\n  without client-side aggregation.\n- `/insights/{fingerprint}/traffic/budgets` — the Traffic Control budgets\n  and rules that affect a fingerprint (Postgres).\n\nAggregates cover the requested window. Duration fields use names like\n`sum_total_duration_millis`, with explicit share-of-window percent fields\n(`sum_total_duration_percent`); both totals and percentages are reliable\nfor the window requested.\n\nThe response schema is shared across engines, but some fields are\nengine-specific: CPU/IO durations and block-cache statistics\n(`sum_cpu_duration_millis`, `blocks_read`, `block_cache_hit_ratio`, …) are\npopulated for Postgres; shard queries, keyspaces, `tablet_type`, and\nrouting-index (vindex) usage are populated for Vitess.\n\n### Tag coverage\n\nFor each expensive or anomalous query, determine:\n\n- Is it tagged?\n- Which service produced it?\n- Which route, job, controller, or action produced it?\n- Which deployment SHA produced it?\n- Is the tag cardinality safe?\n- Are tags consistent across frameworks and languages?\n- Use the tags API to answer these questions: `/insights/tags` shows which\n  keys and values are present, and `/insights/tags/summaries?tags=...`\n  attributes load per tag value. In the Vitess dashboard, filter the query\n  table with `tag:key:value` and drill into query details to see tags on\n  individual executions. Built-in query metadata and SQLCommenter tags are\n  both valid attribution sources.\n\n### Raw query collection\n\nCheck whether raw query / complete query collection is enabled. On\nPostgres the effective state is the `pginsights.raw_queries` cluster\nparameter (per branch, dashboard Extensions tab, default `false`); the\ndatabase API object's `insights_raw_queries` field is a separate surface.\nWhen the two differ, report the cluster parameter as the effective state\nand do not describe the difference as an inconsistency. On Vitess there\nis no cluster parameter; the database API's `insights_raw_queries` field\nis the effective state.\n\nReport it as a capability state, not a risk posture. Raw query collection\nrecords literal parameter values per execution, which pattern-level Insights\ndata does not provide. It is the mechanism for isolating which specific\ninvocation of a pattern is pathological. Execution-level records are\nretrievable from `/insights/{fingerprint}` with or without raw collection;\nraw collection adds the literal parameter values to those records.\n\nWhen it is disabled, the finding is a capability gap: identify the query\npatterns in this assessment where pattern-level data is insufficient\n(unexplained latency variance within a fingerprint, tenant- or\nparameter-dependent behavior) and state that raw collection would resolve\nthem. State the operational property once, as fact: literal values become\nvisible to the observability pipeline. Where the customer's data-handling\nrequirements constrain this, scoped enablement (incident windows, defined\nretention) and leaving collection disabled are both valid outcomes —\nrecord the rationale rather than a default judgment in either direction.\n\nTags and raw collection are complementary instruments: tags attribute a\npattern to a code path; raw collection identifies the specific invocation.\nAssessments should evaluate both.\n\n## SQLCommenter tag schema\n\nRecommend this baseline tag set:\n\n- `application`: stable app name.\n- `service`: service or process name.\n- `environment`: production, staging, development.\n- `route`: normalized route template, for example `/accounts/:id/orders`, not `/accounts/123/orders`.\n- `controller`: framework controller name where applicable.\n- `action`: framework action name where applicable.\n- `job`: background job class or worker name.\n- `queue`: background queue.\n- `feature`: bounded feature name for traffic classes like export, report, search, billing, checkout.\n- `release_sha`: short git SHA or deploy identifier.\n- `source`: app, worker, script, agent, mcp, bi, integration.\n- `tenant_tier`: free, pro, enterprise, internal, only if bounded.\n\nDo not recommend these tags by default:\n\n- `user_id`\n- `request_id`\n- `tenant_id`\n- `email`\n- `session_id`\n- raw URL\n- unbounded GraphQL operation text\n- access token\n- secret\n\nIf 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.\n\n## Cardinality rules\n\nFlag a tag as unsafe when:\n\n- Values are unbounded.\n- Values include IDs, UUIDs, emails, slugs, or raw paths.\n- The same query pattern emits many unique tag combinations.\n- The tag would make Insights or Traffic Control aggregation noisy.\n\nRecommend normalizing at the application boundary.\n\n## Analysis output\n\nFor each top query pattern, produce:\n\n- Fingerprint or normalized query.\n- Current metrics.\n- Current tags.\n- Missing tags.\n- Likely source in application code.\n- Whether it is a schema recommendation candidate.\n- Whether it is a Traffic Control candidate.\n- Whether it is an application optimization candidate.\n\n## Recommendation classes\n\n### Add tags\n\nRecommend SQLCommenter instrumentation when query attribution is weak.\n\n### Improve tag normalization\n\nRecommend replacing high-cardinality tags with bounded values.\n\n### Add Traffic Control warning budget\n\nFor Postgres only, recommend `warn` mode budgets for expensive but important routes, jobs, analytics, exports, or third-party integrations.\n\n### Add schema recommendation workflow\n\nFor Vitess, recommend turning open schema recommendations into branch/deploy-request work. For Postgres, recommend turning them into reviewed migrations against a non-production branch.\n\n### Fix code path\n\nRecommend a repository PR when the expensive query is caused by N+1, missing pagination, accidental eager load, unbounded export, broad search, or polling.\n\n## Safety rules\n\nDo not:\n\n- Enable raw query collection.\n- Add tags to code.\n- Change Traffic Control budgets.\n- Apply schema recommendations.\n- Run production EXPLAIN ANALYZE on expensive queries.\n\nWithout explicit approval.\n\n## Output\n\nReturn:\n\n- Query risk table.\n- Tag coverage table.\n- Bad/high-cardinality tag table.\n- Recommended tag schema for this application.\n- Candidate Traffic Control slices.\n- Candidate schema and code changes.\n- Proposed changes requiring approval.\n\nEnd with:\n\n“No Insights, tag, repository, or Traffic Control changes have been applied.”\n"
}