import { insightsUrl } from './setup.ts'
import { githubRefFromConfig } from './loop-helpers.ts'
import type {
LoopDraft,
LoopTarget,
PlanetscaleAnomalySummary,
PlanetscaleAuthInput,
PlanetscaleQuerySummary,
} from './types.ts'
import { optionalString, optionalStringArray, requireRecord, requireString } from './types.ts'
import { listAnomalies } from './list-anomalies.ts'
import { listQueries } from './list-queries.ts'
function sqlSnippet(sql: string | null, max = 400): string {
const oneLine = (sql ?? '').replace(/\s+/g, ' ').trim()
if (!oneLine) return 'unknown query'
if (oneLine.length <= max) return oneLine
return `${oneLine.slice(0, max)}…`
}
function topCorrelations(anomaly: PlanetscaleAnomalySummary, limit = 3) {
return [...anomaly.correlations]
.sort((a, b) => (b.r ?? 0) - (a.r ?? 0))
.slice(0, limit)
}
function queryStats(query: PlanetscaleQuerySummary): Array<string> {
return [
query.p99Latency != null ? `- p99 latency: ${query.p99Latency}s` : '',
query.timePerQuery != null ? `- time per query: ${query.timePerQuery}s` : '',
query.sumRowsRead != null ? `- rows read: ${query.sumRowsRead}` : '',
query.queryCount != null ? `- query count: ${query.queryCount}` : '',
query.errorCount != null ? `- errors: ${query.errorCount}` : '',
query.tables.length ? `- tables: ${query.tables.join(', ')}` : '',
].filter(Boolean)
}
export function buildLoopDraft(input: {
target: LoopTarget
anomaly?: PlanetscaleAnomalySummary | null
topQueries?: Array<PlanetscaleQuerySummary>
}): LoopDraft {
const github = githubRefFromConfig(input.target)
const dashboard = insightsUrl(input.target.organization, input.target.database, input.target.branch)
const anomaly = input.anomaly ?? null
const topQueries = input.topQueries ?? []
const top = anomaly ? topCorrelations(anomaly) : []
const firstSql = top[0]?.normalizedSql ?? topQueries[0]?.normalizedSql ?? null
const fromQueryOnly = !anomaly && topQueries[0]
const title = fromQueryOnly
? `PlanetScale Insights: expensive query on ${input.target.database}/${input.target.branch}`
: `PlanetScale Insights: slow query on ${input.target.database}/${input.target.branch}`
const correlationLines = top
.map((item, index) => {
const r = item.r == null ? 'n/a' : item.r.toFixed(3)
return `${index + 1}. r=${r} fingerprint \`${item.fingerprint ?? 'unknown'}\`\n \`${sqlSnippet(item.normalizedSql)}\``
})
.join('\n')
const issueBody = [
`PlanetScale Insights flagged \`${input.target.organization}/${input.target.database}/${input.target.branch}\`.`,
'',
anomaly
? [
`- Anomaly id: \`${anomaly.id}\``,
`- Period: ${anomaly.periodStart ?? 'unknown'} → ${anomaly.periodEnd ?? 'unknown'}`,
`- Minutes in violation: ${anomaly.minutesInViolation ?? 'unknown'}`,
`- Duration: ${anomaly.duration ?? 'unknown'}s`,
].join('\n')
: '- Source: Insights query list (no active anomaly id)',
`- Dashboard: ${dashboard}`,
'',
'### Correlated queries',
correlationLines || (topQueries[0] ? `1. fingerprint \`${topQueries[0].fingerprint ?? 'unknown'}\`\n \`${sqlSnippet(topQueries[0].normalizedSql)}\`` : '_No correlations returned._'),
fromQueryOnly ? ['', '### Query stats', ...queryStats(topQueries[0])].join('\n') : '',
'',
'### Goal',
'Make this query (and the app path that issues it) fast enough that Insights stops flagging it. Repeat until the app is fast.',
'',
'### Suggested loop',
'1. Implementer opens a PR that reduces p99 / rows read / query count for the fingerprint above.',
'2. Reviewer agent reviews that PR.',
'3. Human reviews and merges if it is actually faster and correct.',
]
.filter(Boolean)
.join('\n')
const repoHint = github
? `Repo: ${github.fullName}`
: 'Repo: (set githubOwner + githubRepo, or repositoryUrl, when you run the loop)'
const cursorHint = github
? `Cursor repository URL: ${github.repositoryUrl}`
: 'Cursor repository URL: (https GitHub URL)'
const implementerPrompt = [
'You are the implementer in a PlanetScale Insights → PR loop.',
repoHint,
cursorHint,
'',
`PlanetScale Insights flagged a slow query on ${input.target.organization}/${input.target.database}/${input.target.branch}.`,
anomaly
? `Anomaly ${anomaly.id} from ${anomaly.periodStart ?? 'unknown'} to ${anomaly.periodEnd ?? 'unknown'}. Minutes in violation: ${anomaly.minutesInViolation ?? 'unknown'}.`
: 'This draft came from the Insights query list, not an anomaly id.',
`Dashboard: ${dashboard}`,
'',
'Highest-correlation SQL:',
sqlSnippet(firstSql, 800),
'',
top.length ? `Fingerprints: ${top.map((item) => item.fingerprint).filter(Boolean).join(', ')}` : '',
'',
'Investigate the application path that emits this query. Prefer an index, query rewrite, or N+1 fix over guessing. Open a PR that makes this query cheaper and does not change product behavior. Include before/after reasoning in the PR body.',
'',
'If this task results in code changes, you MUST push your branch and create or update the pull request using Cursor Cloud ManagePullRequest tools before finishing. Do not ask Kody or GitHub to open the PR as a substitute. The PR should be authored by the Cursor GitHub account so the standard AI reviewer is triggered.',
]
.filter(Boolean)
.join('\n')
const reviewerPrompt = [
'You are the reviewer in a PlanetScale Insights → PR loop.',
'',
'Check that the PR actually targets the Insights anomaly, not a drive-by cleanup.',
'Reject schema changes that do not help the correlated fingerprint.',
'Reject "optimizations" that change results, hide the query, or add unbounded new queries.',
`Dashboard: ${dashboard}`,
anomaly ? `Anomaly: ${anomaly.id}` : '',
top[0]?.fingerprint ? `Primary fingerprint: ${top[0].fingerprint}` : '',
]
.filter(Boolean)
.join('\n')
const humanPing = [
`PlanetScale Insights anomaly on ${input.target.database}/${input.target.branch}.`,
'Issue + implementer PR are ready for human review.',
dashboard,
firstSql ? `Top query: ${sqlSnippet(firstSql, 160)}` : '',
]
.filter(Boolean)
.join('\n')
return {
title,
issueBody,
implementerPrompt,
reviewerPrompt,
humanPing,
insightsUrl: dashboard,
anomaly,
topQueries,
target: input.target,
}
}
export type DraftLoopInput = PlanetscaleAuthInput & {
organization: string
database: string
branch?: string
anomalyId?: string
anomaly?: PlanetscaleAnomalySummary
githubOwner?: string
githubRepo?: string
repositoryUrl?: string
labels?: Array<string>
}
/**
* Format a GitHub issue, implementer prompt, reviewer prompt, and human ping
* from PlanetScale Insights data. Always safe — does not open issues or agents.
* @example
* import draftLoop from 'kody:@kody/planetscale/draft-loop'
* const draft = await draftLoop({
* organization: 'acme',
* database: 'app',
* branch: 'main',
* })
*/
export async function draftLoop(input: DraftLoopInput): Promise<LoopDraft> {
const organization = requireString(input.organization, 'organization')
const database = requireString(input.database, 'database')
const branch = optionalString(input.branch, 'branch') ?? 'main'
const target: LoopTarget = {
organization,
database,
branch,
githubOwner: optionalString(input.githubOwner, 'githubOwner'),
githubRepo: optionalString(input.githubRepo, 'githubRepo'),
repositoryUrl: optionalString(input.repositoryUrl, 'repositoryUrl'),
labels: optionalStringArray(input.labels, 'labels'),
}
if (input.anomaly) {
return buildLoopDraft({ target, anomaly: input.anomaly })
}
const anomalies = await listAnomalies({
organization,
database,
branch,
account: input.account,
secretName: input.secretName,
})
const anomaly = input.anomalyId
? anomalies.items.find((item) => item.id === input.anomalyId)
: anomalies.items.find((item) => item.active === true) ?? anomalies.items[0]
if (anomaly) {
return buildLoopDraft({ target, anomaly })
}
const queries = await listQueries({
organization,
database,
branch,
account: input.account,
secretName: input.secretName,
sort: 'p99_latency',
perPage: 5,
})
return buildLoopDraft({
target,
anomaly: null,
topQueries: queries.items.slice(0, 3),
})
}
/**
* Format a GitHub issue, implementer prompt, reviewer prompt, and human ping
* from PlanetScale Insights data. Always safe — does not open issues or agents.
* @example
* import draftLoop from 'kody:@kody/planetscale/draft-loop'
* const draft = await draftLoop({ organization: 'acme', database: 'app', branch: 'main' })
*/
export default async function draftLoopEntrypoint(
params: Partial<DraftLoopInput> & Record<string, unknown> = {},
) {
const input = requireRecord(params, 'draft-loop')
return draftLoop({
organization: requireString(input.organization, 'organization'),
database: requireString(input.database, 'database'),
branch: optionalString(input.branch, 'branch'),
anomalyId: optionalString(input.anomalyId, 'anomalyId'),
anomaly: input.anomaly as PlanetscaleAnomalySummary | undefined,
githubOwner: optionalString(input.githubOwner, 'githubOwner'),
githubRepo: optionalString(input.githubRepo, 'githubRepo'),
repositoryUrl: optionalString(input.repositoryUrl, 'repositoryUrl'),
labels: optionalStringArray(input.labels, 'labels'),
account: optionalString(input.account, 'account'),
secretName: optionalString(input.secretName, 'secretName'),
})
}