Database integrity check: add db:check CLI command and /health/db endpoint

[cschanhniem]

Problem

ShadowBrain uses SQLite as its single source of truth — notes, typed links, semantic vectors, audit logs, and content items all live in one file. Neither the Docker entrypoint nor the web UI currently runs PRAGMA integrity_check or PRAGMA quick_check on startup or on demand. A corrupt database (from filesystem errors, partial Docker volume writes, or unexpected process kills) can go undetected until data loss is already visible.

The existing backup reminder spec (docs/superpowers/specs/2026-06-19-backup-reminder-design.md) covers off-site backups but assumes the local database is healthy at backup time. It would be useful to verify integrity before shipping a backup off-site, so a corrupt copy is not propagated.

Proposal

Add a db:check CLI command and a read-only /health/db API endpoint that runs PRAGMA integrity_check against the SQLite database and returns the result.

CLI command

pnpm db:check
# Output on success:  OK - database integrity verified (took 1.2s)
# Output on failure:  FAIL - database integrity check found errors: [list]

Implementation: a short Node.js script in scripts/ that opens the database via the same Drizzle ORM connection used elsewhere, runs PRAGMA integrity_check, and prints the result.

Health endpoint

GET /api/health/db returns:

{
  "status": "ok",
  "integrity_check": "ok",
  "page_count": 12345,
  "page_size": 4096,
  "database_size_mb": 48.2,
  "checked_at": "2026-06-19T23:00:00Z"
}

On failure, status becomes "degraded" and integrity_check lists the error lines.

This endpoint should be read-only — no PRAGMA wal_checkpoint or other write operations. It does not require authentication (it is a liveness signal, gated at the reverse-proxy level if desired).

Integration with backup flow

Before the backup script copies the database to Proton Drive, it should run pnpm db:check. If the check fails, the backup should be skipped and an alert logged to audit_logs.

Prior art

• litestream and sqlite-utils both provide integrity check commands.
• WordPress health check endpoint runs a DB integrity test.
• The security baseline spec already references audit logging as a cross-cutting concern; a health endpoint extends this to operational observability.