# Essential Slow Query Tier Routing

<!-- auto-oncall-optimization-record
{
  "schemaVersion": "auto-oncall-optimization-record-v1",
  "optimizationId": "essential-slow-query-tier-routing-2026-08-06",
  "problems": [
    {
      "problemId": "essential-slow-query-data-excluded-by-data-proxy-only-route",
      "summary": "Auto-oncall expressed Slow Query as generic Data Proxy SQL, so Starter and Essential clusters with data in NGM returned DATASOURCE_NOT_CONFIGURED and lost available workload evidence.",
      "status": "optimized_verified",
      "issueKeys": ["TCID-12965", "TCID-12983"],
      "sourceRefs": [
        "https://tidb.atlassian.net/browse/TCID-12965#problem:essential-slow-query-ngm-route-not-admitted",
        "https://tidb.atlassian.net/browse/TCID-12983#problem:essential-slow-query-ngm-route-not-admitted"
      ],
      "signalTextIncludesAny": [
        "essential slow query data exists in ngm but auto-oncall only calls sql-queries",
        "starter slow query data exists in ngm but auto-oncall only calls sql-queries",
        "slow query datasource not configured for essential cluster",
        "available ngm slow query evidence did not enter the scenario packet"
      ],
      "observedThrough": "2026-08-06",
      "effectiveAt": "2026-08-06T09:12:44Z",
      "verifiedAt": "2026-08-06T09:39:30Z",
      "attemptedAction": "Add one helper-owned slow_query operation that resolves the exact cluster metadata once, routes Starter, Essential, and shared tiers to NGM, routes other tiers to bounded Data Proxy SQL, and emits only allowlisted summary fields with hashed digests.",
      "evidenceUrl": "https://tidb.atlassian.net/browse/TCID-12983"
    }
  ]
}
-->

- Optimization ID: `essential-slow-query-tier-routing-2026-08-06`
- Owner: auto-oncall-services
- Source cases: `TCID-12965` and `TCID-12983`; `TCID-12989` is a separate
  provider-fallback and evidence-selection observation
- Implementation status: deployed to staging in image
  `06da98e49af96c95b9556a22b12717bc935856c7`; current live image
  `918c503fcbb63224521eb32d06d3b06d24367bb2` is a descendant containing the
  same change
- Effective status: `optimized_verified`

## Bounded problem

The deterministic Clinic batch admitted generic SQL, but Starter and Essential
Slow Query data is served through NGM when Data Proxy Slow Query is not
configured. The evidence child therefore had no valid operation that could
retrieve those existing rows.

## Implementation

- Add a typed `slow_query` operation to the existing bounded Clinic batch.
- Resolve and validate the exact org/cluster metadata at most once per batch.
- Route Starter, Essential, and shared clusters to NGM and other tiers to
  bounded Data Proxy SQL without agent-selected fallback behavior.
- Exclude raw SQL, previous statements, plans, and full digests from the
  deterministic result.

## Verification status

Source PR `tidbcloud/aisupport#661` merged as
`06da98e49af96c95b9556a22b12717bc935856c7`. App-delivery PR
`tidbcloud/app-delivery#76441` deployed that exact tag. The staging Deployment
reached Ready with Pod UID `e7b58946-74de-4ac9-b483-69f80d577dff` and container
imageID `sha256:6d0361307176d6cdef1582a74ac883b7c5fab9434f4118e56d49676b127395a2`.
It was later replaced by descendant image `918c503f...`; the replacement Pod
was Ready and its runtime helper still contained the typed Slow Query, NGM,
and exact metadata routes.

- `TCID-12965`: deployed write replay
  `f074ae24-0333-4aed-842d-a2d4444a8966` used a `slow_query` operation and
  returned 100 NGM rows. DB run `120038` recorded provider capability as
  complete. Existing Jira internal comment `23287140` was updated in place and
  read back with `public=false`.
- `TCID-12983`: deployed write replay
  `758fe011-0c08-49d4-9f58-6dca64f4f00e` used `slow_query` in both memory and
  query-execution scenarios, returning 20 and 50 NGM rows. DB run `120042`
  completed and existing Jira internal comment `23342385` was updated in place
  and read back with `public=false`.

These replays directly pass the bounded criterion: existing Essential Slow
Query rows can enter the deterministic Clinic batch without a failed generic
Data Proxy SQL call. Full tests passed with 665 tests before deployment.

## Unresolved gaps

- `TCID-12965` TopSQL still returned `clinic_http_400`; the usable NGM Slow
  Query and Loki evidence remained available instead of being discarded.
- `TCID-12983` retained unrelated metric `clinic_http_500` and empty-series
  gaps. They are source-specific gaps, not failures of this routing criterion.
- `TCID-12989` replay `68a73058-256a-46eb-ac12-2574b747ac2e` no longer stopped
  before Codex: Clinic returned 67, 9, and 440 metric series, DB run `120044`
  completed, and Jira comment `23371042` was updated and read back
  `public=false`. Its planner did not select the typed `slow_query` operation,
  so this case is not used as proof of the NGM routing criterion.

## Next validation action

Track the remaining TopSQL/metric source failures separately. If a future
Essential replay selects `slow_query` but does not reach NGM, treat that newer
evidence as a regression of this verified problem.