Store

The web app talks to MySQL through Cloudflare Hyperdrive. Two paths exist:

  • Worker (production): mysql2 + Drizzle against the HYPERDRIVE binding. A connection is created per request and closed when the response finishes, because Workers cannot reuse a socket created in another request handler. Queries within one request are serialized.
  • Local Node dev: an in-memory store. No database is needed to run the app or its tests.

Two Hyperdrive settings matter for correctness:

  • --database offense — the PlanetScale integration otherwise defaults to the mysql system database, which made unqualified tables resolve to mysql.users.
  • --caching-disabled — Hyperdrive caches SELECTs for up to 60 seconds, which would serve stale balances. Do not re-enable it for this database.

Core tables

Table Holds
users Account row per sign-in, workspace membership, free-run grant
workspaces Credits in cents and micros, plan, retention window, Stripe IDs, nudge timestamps
targets / target identities Registered host, verification state, encrypted identities
assessments Run state, budget, coverage, engine, model, report fields
assessment_jobs Durable queue rows: claim, attempt, lease, heartbeat, per-attempt token hash
findings Candidate/confirmed state, severity, evidence refs, retest status
api_keys Scope list, allowed targets, SHA-256 hash, expiry
sessions / connection rows Encrypted provider credentials (GitHub, AWS, Vercel, Cloudflare, Slack)

The durable queue is the source of truth for work: the queue message carries only the assessment id, and claiming is conditional on status and attempt, so a duplicate delivery acknowledges without creating a second sandbox.

Migrations

Migrations live in web/db/migrations/*.sql and are applied in order, after web/db/schema.sql. Applying them is a manual step for a deploy — the app does not run migrations at boot.

# PlanetScale dashboard → offense → dev → SQL console
# paste db/schema.sql, then every db/migrations/*.sql in order

Or over MySQL TLS with the PlanetScale password, which the deploy tooling reads from the environment and never writes into the repository.

Production was at one point one migration behind, which surfaced as a 500 on /app/swarms because 0040-finding-retest-evidence.sql had not been applied. The fix was the missing column, not a code change — a reminder that a migration guard is worth having.

Do not renumber existing migration files. Prefixes in this directory are not all unique historically (0025- and 0038- each appear more than once), and the applied order in production is what matters, not a tidy sequence. Add a new file with a new prefix instead.

Retention

Each workspace has a retention window (retention_days, default 30). A daily cron at 04:00 UTC purges expired assessments, evidence and findings. Evidence lives in the private EVIDENCE R2 bucket, or the database when the binding is absent; both honour the same window.

Deletion behaviour must be described precisely: what is promised is the cron's retention purge, not an instantaneous erase on request.

Backups

PlanetScale manages the branch's backups. Restoring a branch is a PlanetScale operation. Treat any deletion-deadline promise as pending until backup deletion is finalised.