B. Quality, Speed & Resilience · Prompt 18

Database & Query Health

Get the free PDF

Why it matters

A query can look fine on a small test database and time out after real data arrives.

Modeled on

PostgreSQL EXPLAIN and pg_stat_statements, or the equivalent for your database.

How to run this prompt

Switch to a mode that does not edit files. In Cursor that is Ask or Plan. In Claude Code that is Plan mode.
Paste the audit prompt. Wait for the report. It must stop and ask which IDs to fix.
Read the report. Keep the IDs you agree with.
Switch to a mode that can edit. Paste the fix prompt and the IDs you chose.
Switch back to the read-only mode and paste the same audit prompt again. Confirm those IDs are gone.

Cursor: audit in Ask mode or Plan mode. Fix in Agent mode.
Claude Code: audit in Plan mode (Shift+Tab cycles to it). Fix in Normal mode, which can edit.
Any other tool: audit in Chat, Discuss, or Plan mode, whichever answers without editing files. If the tool has no such mode, the prompt itself forbids edits. Fix in the mode that is allowed to edit files.

The audit prompt

MODE: AUDIT ONLY. Do not create, edit, or delete any file. Do not run commands
that change anything: no installs, migrations, git commits, deploys, or "--fix" flags.
If your tool has an Ask, Plan, Chat, or Discuss mode, use it for this prompt.

Before you start:
- Tell me the stack you detect (framework, language, database, auth, hosting,
  payment provider) and which folders you will review.
- If a check below does not apply to this stack, write "Not applicable" and why.
- If you can run read-only commands, run the ones listed. If you cannot, list them
  so I can run them and paste the output.

Database & Query Health: what to check
Review queries for correctness and for how they will behave as tables grow. Do not run EXPLAIN ANALYZE: it executes the statement. Use plain EXPLAIN only on reads, and only if I have said this database is safe to touch. Never EXPLAIN a write against production.
1. Flag queries that filter a growing table on a column with no index in migrations. Cite the query and the migration.
2. Flag a query shape that will scan the whole table once the table is large. Say why. Do not invent a row count you did not measure.
3. Flag missing foreign keys where a child row can outlive a deleted parent, if the schema shows that relationship.
4. Flag code that loads a whole table and filters in application memory.
5. Flag multi-step writes that must succeed or fail together (for example debit one balance and credit another) with no transaction.
6. Flag list queries with no LIMIT or page size.
7. For each issue, show the query or the ORM call, the problem, and a corrected version as a suggestion. Do not apply it.
8. If the database is SQLite, Firestore, or MongoDB, use that engine's terms instead of pretending it is Postgres. Say which engine you detected.

Evidence rules:
- Every finding cites a file path and line number, or the exact command output used.
- Mark each finding Confirmed (seen in the code) or Needs manual check (depends on
  something outside the repo, such as a dashboard setting or production data).
- Never print a full secret. Show the first 4 characters and the location only.
- If you are not sure, say so. Do not invent files, settings, or results.

Severity: Critical = exploitable now, or leaks real data or money. High = serious
with little effort. Medium = weakens defenses or needs a second bug. Low = hygiene.

Report:
- Summary: count of findings by severity.
- Table: ID | Severity | Confirmed? | Finding | Evidence | Why it matters | Suggested fix | Effort
  (IDs for this prompt use the prefix P18, for example P18-1, P18-2.)
- Checked and fine: what you verified is already OK.
- Could not check: what I need to look at myself, and where.

Then stop. Do not fix anything. Ask me which IDs I want fixed.

Get the free PDF

Related prompts

Share this prompt

WhatsApp LinkedIn X

How to run this prompt

Switch to a mode that does not edit files. In Cursor that is Ask or Plan. In Claude Code that is Plan mode.

Paste the audit prompt. Wait for the report. It must stop and ask which IDs to fix.

Read the report. Keep the IDs you agree with.

Switch to a mode that can edit. Paste the fix prompt and the IDs you chose.

Switch back to the read-only mode and paste the same audit prompt again. Confirm those IDs are gone.

Cursor: audit in Ask mode or Plan mode. Fix in Agent mode.

Claude Code: audit in Plan mode (Shift+Tab cycles to it). Fix in Normal mode, which can edit.

Any other tool: audit in Chat, Discuss, or Plan mode, whichever answers without editing files. If the tool has no such mode, the prompt itself forbids edits. Fix in the mode that is allowed to edit files.

The audit prompt

MODE: AUDIT ONLY. Do not create, edit, or delete any file. Do not run commands that change anything: no installs, migrations, git commits, deploys, or "--fix" flags. If your tool has an Ask, Plan, Chat, or Discuss mode, use it for this prompt. Before you start: - Tell me the stack you detect (framework, language, database, auth, hosting, payment provider) and which folders you will review. - If a check below does not apply to this stack, write "Not applicable" and why. - If you can run read-only commands, run the ones listed. If you cannot, list them so I can run them and paste the output. Database & Query Health: what to check Review queries for correctness and for how they will behave as tables grow. Do not run EXPLAIN ANALYZE: it executes the statement. Use plain EXPLAIN only on reads, and only if I have said this database is safe to touch. Never EXPLAIN a write against production. 1. Flag queries that filter a growing table on a column with no index in migrations. Cite the query and the migration. 2. Flag a query shape that will scan the whole table once the table is large. Say why. Do not invent a row count you did not measure. 3. Flag missing foreign keys where a child row can outlive a deleted parent, if the schema shows that relationship. 4. Flag code that loads a whole table and filters in application memory. 5. Flag multi-step writes that must succeed or fail together (for example debit one balance and credit another) with no transaction. 6. Flag list queries with no LIMIT or page size. 7. For each issue, show the query or the ORM call, the problem, and a corrected version as a suggestion. Do not apply it. 8. If the database is SQLite, Firestore, or MongoDB, use that engine's terms instead of pretending it is Postgres. Say which engine you detected. Evidence rules: - Every finding cites a file path and line number, or the exact command output used. - Mark each finding Confirmed (seen in the code) or Needs manual check (depends on something outside the repo, such as a dashboard setting or production data). - Never print a full secret. Show the first 4 characters and the location only. - If you are not sure, say so. Do not invent files, settings, or results. Severity: Critical = exploitable now, or leaks real data or money. High = serious with little effort. Medium = weakens defenses or needs a second bug. Low = hygiene. Report: - Summary: count of findings by severity. - Table: ID | Severity | Confirmed? | Finding | Evidence | Why it matters | Suggested fix | Effort (IDs for this prompt use the prefix P18, for example P18-1, P18-2.) - Checked and fine: what you verified is already OK. - Could not check: what I need to look at myself, and where. Then stop. Do not fix anything. Ask me which IDs I want fixed.