Index advisor

The Insights tab on each database has a Suggest indexes button. Click it and PerSQL sends the slow-query corpus from the last 7 days, plus the current schema, to Workers AI. The model proposes up to 5 indexes that would speed up the recurring slow patterns.

Each suggestion ships with:

• A CREATE INDEX … statement (validated to be only an index DDL).
• A one-sentence reason citing the query pattern.
• A Save as migration button — clicking it drops the SQL into a draft migration on the Migrations tab. The advisor never runs the SQL automatically. You review and apply the migration yourself.

Inputs

• Up to 30 worst slow / errored queries from the last 7 days.
• Schema sourced from the same canonical sqlite_master dump used by schema diff — including any indexes you already have, so the model doesn’t suggest duplicates.

Why this works for SQLite

SQLite’s optimizer is rule-based, so the wins from the right indexes are usually obvious to a competent engineer reading EXPLAIN QUERY PLAN output. The advisor’s job is to do that reading at scale — look at every slow query, recognize the common access pattern, suggest the index.

Permissions

• Suggest indexes: anyone with read access to the database.
• Save as migration: write access (creates a draft on the Migrations tab).
• Apply migration: namespace owner / admin.

Limits

• One Workers AI call per click. Costs are absorbed by your namespace’s AI budget.
• The model fails open: if it can’t parse the JSON or finds nothing useful, the panel says so and you keep your dignity.
• Suggestions are not run through EXPLAIN — the advisor isn’t benchmarking; it’s pattern-matching. Always sanity-check the recommended index against your access pattern before applying in production.