Codebase security audit

Codebase Security & Database Audit

This skill runs a focused, high-signal audit of a codebase for the two problem areas that most often slip through when code is written quickly (including by LLMs): web security and database correctness/performance. The output is a prioritized report, not a set of automatic edits.

The goal is not to run a generic linter. It is to reason about the code the way a careful security reviewer would: understand the stack, follow the data and trust boundaries, and flag real problems with concrete evidence and fixes — while not generating noise about things that are already handled correctly.

Two golden rules

1. Verify before you flag. Read the surrounding code before reporting anything. A SELECT * FROM users WHERE id = ${id} is only injectable if id is attacker-controlled and not otherwise validated; an ORM call is usually parameterized already. Distinguish a real, reachable vulnerability from a pattern that merely looks suspicious. False positives waste the user's time and erode trust in the report — many users have already fixed the obvious issues, so precision matters more than volume.

2. Report first, fix only with consent. Never edit files as part of the audit. The deliverable is a report plus a prioritized remediation plan (ordered by improvement impact vs. effort). After presenting it, ask the user which items they want to act on and wait for explicit agreement. Only then apply fixes — one finding at a time, re-reading each file before editing, so the user reviews every change. Do not batch-fix, and do not start fixing just because findings exist.

Audit workflow

Follow these steps in order. Adapt scope to what the user asked for (whole codebase vs. specific files/directories).

Step 1 — Map the stack and the attack surface

Before reading logic, read the project to discover its actual shape — do not assume a stack. When run from a project root, detect all of the following from the files themselves so the checklists and stack-specific traps apply correctly:

• Repository layout: is it a monorepo? Look for workspace config (pnpm-workspace.yaml, turbo.json, nx.json, lerna.json, root workspaces in package.json, apps/* + packages/* folders). Enumerate each app and shared package and note what each one is.
• Per-app framework: for every app/package, read its manifest to identify the framework and role (e.g. a Next.js web app, a NestJS/Express API, a shared library). Frameworks differ per package in a monorepo — don't assume one.
• Data layer: which database and ORM/query builder (Prisma, TypeORM, Sequelize, Drizzle, SQLAlchemy, etc.) or raw driver. Find the schema (schema.prisma, migrations) — it reveals models, relations, and indexes.
• Shared types / validation strategy: is there a shared package of schemas or types (e.g. Zod schemas, shared DTOs) imported by multiple apps? If so, determine whether it's used only for client-side convenience or actually enforced as the server-side validation boundary. This is a common source of real gaps — see the shared-schema notes in the stack-specific reference.
• Auth mechanism: sessions, JWT, OAuth, framework guards/middleware — and crucially, where it's enforced in each app.
• Entry points: API routes/controllers/resolvers, server actions, route handlers, webhooks, and any public endpoints — where most findings live.
• Config, secrets, and client/server boundary: how env vars/secrets load, and which values cross into client bundles (framework-specific public prefixes, shared config packages imported by frontend code).
• Deployment target if visible (serverless vs. long-running) — it changes connection-pooling advice.

Write down the detected stack; you'll state it in the report summary and use it to select stack-specific pitfalls in Step 4.

Step 2 — Prioritize what to read

You usually can't read every line. Concentrate effort where impact is highest, roughly in this order: authentication/authorization middleware and its usage → route/controller handlers that read or mutate data → the database access layer → input handling and serialization → configuration and secret loading → error handling. Skim tests and generated code; they rarely hold the real issues.

Step 3 — Run the two core checklists

Load and work through the two reference checklists. Each item tells you what to look for, how to find it, why it matters, and what a fix looks like:

• references/web-security.md — web/application security checklist.
• references/database.md — database correctness and performance checklist.

For each candidate finding, apply Golden Rule #1 (verify reachability and that it isn't already mitigated) before adding it to the report.

Step 4 — Apply stack-specific pitfalls

The core checklists are framework-agnostic; many real bugs are specific to the exact stack you detected in Step 1. Consult references/stack-specific.md and, for each technology present in the project (each app's framework, the ORM, the shared-schema/validation approach, monorepo boundaries, the deployment model), work through the traps that apply.

Treat that reference as a pattern, not an exhaustive list: if the project uses a framework not covered there, reason from first principles about that framework's own trust boundaries and defaults, and check them the same way. The point is that the audit adapts to this project's real composition.

Step 5 — Write the report

Produce the report in the format below. Save it as a Markdown file (default SECURITY_AUDIT.md in the project root, or wherever the user prefers) so it's a durable artifact they can work through and track. Present the file when done.

Severity rubric

Rate each finding so the user knows what to fix first. Consider both the impact if exploited and how easily it's reached.

• Critical — Direct path to data breach, account takeover, remote code execution, or financial loss with little/no precondition. (e.g., IDOR exposing other users' data, SQL injection in a reachable endpoint, plaintext passwords, live secret committed to the repo.)
• High — Serious impact but needs a precondition, or a strong defense-in- depth gap. (e.g., missing authz on a less-sensitive endpoint, JWT in localStorage combined with a plausible XSS sink, missing rate limiting on login.)
• Medium — Real weakness with limited or indirect impact, or missing hardening. (e.g., no security headers, overly broad CORS without credentials, N+1 that will hurt at scale.)
• Low / Info — Best-practice deviations, hygiene, or things worth noting. (e.g., missing index on a small table, verbose logging, no explicit migration history.)

If something looks suspicious but you couldn't confirm it (e.g., you couldn't trace whether an input is validated upstream), report it under its likely severity but mark it Needs verification and say exactly what to check.

Report structure

ALWAYS use this structure:

# Security & Database Audit — <project name>
_Audited: <date> · Scope: <what was reviewed>_

## Summary
Stack: <detected layout & per-app frameworks / DB / ORM / shared-schema approach / auth>
Findings by severity: Critical <n> · High <n> · Medium <n> · Low <n>
Top priorities: <1–3 sentence plain-language "fix these first">

## Findings

### [SEC-01] <short title>
- **Severity:** Critical | High | Medium | Low  (+ "Needs verification" if unconfirmed)
- **Category:** <e.g., Broken Object-Level Authorization (IDOR)>
- **Location:** <path/to/file.ext:line> (list all instances if repeated)
- **What's wrong:** <1–3 sentences, concrete>
- **Evidence:**
  ```<lang>
  <the minimal offending snippet>

• Why it matters: <the concrete risk if exploited>
• Recommended fix: <specific, actionable — show a corrected snippet when it helps>

[DB-01] <short title>

...same fields...

[STK-01] <short title> ← stack-specific findings use the STK- prefix

...same fields...

Recommended remediation order

<The prioritized plan. Rank the findings by improvement impact vs. effort so the user can decide what to do first. Present as an ordered list, each line: "1. [SEC-01] <title> — <why it's first: impact> · effort: low/med/high". Group obvious quick wins ("high impact, low effort — do these first") and call out anything that needs care (migrations, schema changes, secret rotation). End with an explicit question asking which items to proceed on — do NOT start fixing anything yet.>

Already handled (good)

<Short list of protections you confirmed are correctly in place. This tells the user what NOT to worry about and shows the audit was thorough. Keep it brief.>

Not assessed / out of scope

<Anything you couldn't reach: files not read, areas needing runtime testing, infra concerns like backups/secrets management that live outside the code.>

Number findings with a category prefix so they're easy to reference later:
`SEC-` for web/application security, `DB-` for database, `STK-` for
stack-specific findings.

## Style guidance for findings

- Be specific and evidence-based. Every finding needs a real location and a
  minimal snippet — no generic "you should validate inputs" without pointing at
  where.
- Keep snippets minimal (copyright and readability): show just the offending
  lines, not whole files.
- Order findings by severity within the report, Critical first.
- Prefer concrete fixes over lectures. A corrected 5-line snippet beats a
  paragraph of theory.
- Don't pad the report. If a whole category is clean, say so in "Already
  handled" rather than inventing weak findings.
- If the codebase is large and you sampled, say what you covered so the user
  knows the report isn't exhaustive.

## After the report — the consent gate

Do not fix anything automatically. The flow is always:

1. Deliver the report, ending with the **Recommended remediation order**.
2. Ask the user which items they'd like to address, e.g. "Where would you like to
   start? I'd suggest the high-impact/low-effort items first — say the finding IDs
   and I'll fix them one at a time for you to review."
3. Wait for an explicit choice. Only then implement the agreed items, **one
   finding at a time**: re-read the target file, make the change, briefly explain
   what changed and why, and move to the next only after the user is comfortable.

Never batch-edit the codebase, and never begin fixing solely because problems
were found. The user decides scope and order.