SQL runner

/_/sql is a built-in SQL workspace inside the admin panel. Write arbitrary SQL, run it, see results in a table — without leaving the browser or installing a separate SQLite client.

Two modes:

Mode	Backing	Allowed	Cost
Read-only (default)	live DB, opened with `query_only=ON`	`SELECT` / `EXPLAIN` / `PRAGMA`	none
Sandbox (opt-in)	in-memory snapshot of the live DB	anything (incl. `DROP`, `ALTER`, `INSERT`)	RAM ≈ live DB size

Read-only is fast and won’t change anything. Sandbox is for testing mutations safely — the snapshot lives in process memory, never persists, and is dropped when you click Reset or after 1h idle.

Layout

Left rail — saved queries with search, last-run timestamp + row count. Click to load. Hover to run / copy / delete inline.
Top right — Monaco SQL editor with full IDE intellisense (see below).
Bottom right — result table for SELECTs, statement log for mutations, error pane on failure.
Toolbar — Run (⌘↵ / Ctrl+↵), mode toggle, Save / Save as, Reset Sandbox, Drop Sandbox.

IDE-grade intellisense

The editor knows your entire schema + every SQLite built-in:

Tables + views — vb_<collection>, vaultbase_* system, sqlite_master, plus any custom tables you’ve created.
Columns — name, type, NOT NULL / PK / indexed flags, default values, foreign keys.
Indexes + foreign keys — surfaced in hovers; FK targets render as clickable references.
Functions — full SQLite catalog: aggregate / scalar / string / math / date-time / JSON1 / window / FTS5. ~80 functions with signatures, return types, and one-line docs.
Keywords + snippets — SELECT/WHERE/JOIN/CTE/etc., plus templates like sel, joi, cte, expl (tab to expand).

Context awareness — completions are tailored to where the cursor is:

Position	Suggests
`SELECT │`	columns of every in-scope table, then functions, then keywords
`FROM │` / `JOIN │`	table names + collection aliases
`WHERE u.│`	columns of the table that `u` aliases
`ORDER BY │`	columns of in-scope tables
`SELECT u.email FROM users u WHERE u.│`	only `users` columns

A hand-rolled tokenizer parses partial SQL — strings + comments are skipped, parens are tracked at depth, aliases are resolved across FROM/JOIN/UPDATE/INTO. Subqueries and CTEs work; pathological nesting may degrade to broader suggestions but never crashes.

Hover — point at any identifier:

Hover a table → markdown card with column list, types, index list, foreign keys.
Hover a column → name TYPE NOT NULL · indexed · FK → ref.
Hover a function → signature + description + return type.

Schema is fetched from GET /api/v1/admin/sql/schema on page load.

Safety belts

Timeout: 5s wall-clock per query. Slow queries are interrupted via db.interrupt() from a setTimeout.
Row cap: 1000 rows max. Result is sliced + flagged truncated: true so the UI shows a warning pill.
Read-only pre-filter: a regex pass rejects INSERT / UPDATE / DELETE / DROP / ALTER / CREATE / REPLACE / TRUNCATE / ATTACH / DETACH / REINDEX / VACUUM before SQLite ever sees the SQL. Belt-and-suspenders alongside the connection’s readonly flag and the query_only PRAGMA.
Sandbox isolation: snapshot lives in an in-memory SQLite Database. The live DB file is never touched, even on DROP TABLE.
Audit log: every POST /admin/sql/run flows through the standard admin audit pipeline. Look for actor=<email>, path= /api/v1/admin/sql/run, summary truncated to 1024 chars.

Saved queries

Per-admin (private), persisted in vaultbase_sql_queries. Fields:

name (required, ≤100 chars)
sql (required, ≤100 KB)
description (optional, ≤500 chars)

Bookkeeping: last_run_at, last_run_ms, last_row_count, last_error. Updates on every run.

The REST surface (admin JWT only — API tokens cannot reach these endpoints):

GET    /api/v1/admin/sql/queries
POST   /api/v1/admin/sql/queries          { name, sql, description? }
GET    /api/v1/admin/sql/queries/:id
PATCH  /api/v1/admin/sql/queries/:id      { name?, sql?, description? }
DELETE /api/v1/admin/sql/queries/:id
POST   /api/v1/admin/sql/queries/:id/run  { mode: "readonly" | "sandbox" }

POST   /api/v1/admin/sql/run              { sql, mode, params? }
GET    /api/v1/admin/sql/sandbox
POST   /api/v1/admin/sql/sandbox/reset
DELETE /api/v1/admin/sql/sandbox

GET    /api/v1/admin/sql/schema           — full table/view metadata
                                            (columns, types, indexes,
                                            foreign keys) for the
                                            editor's intellisense.

Why admin-JWT only

Raw SQL bypasses every safety net the rest of the system depends on: collection rules, validation, hooks, audit triggers. API tokens can’t reach /admin/sql/* — even tokens with the admin scope. That’s intentional. The intended automation path for AI agents is the MCP tool vaultbase.run_sql (gated by the mcp:sql scope, capped at 100 rows, no sandbox), not this endpoint.

Caveats

Sandbox snapshot omits virtual tables (FTS5 etc.) — they fail to re-create from sqlite_master.sql. Other objects copy fine: tables, views, indexes, triggers.
Browser tabs share the slot. Same admin, two tabs → both see the same sandbox state. A DROP TABLE in tab A is visible in tab B.
Process restart drops every sandbox. Acceptable — this is a developer tool, not a persistent test fixture.
Big DBs eat RAM. Sandbox copies every row into the in-memory Database. For multi-GB deployments, consider running ad-hoc SQL against a separate replica instead.

Demo

Read-only mode against a fresh deployment:

SELECT name, type FROM sqlite_master
WHERE type IN ('table', 'view') AND name NOT LIKE 'sqlite_%'
ORDER BY name;

Sandbox mode — test what a destructive migration would do:

-- ⚠ Sandbox mode active. Live data is untouched.
DELETE FROM vb_users WHERE created_at < strftime('%s', 'now', '-1 year');
SELECT changes();

Switch back to Read-only when you’re done — keeps the snapshot in RAM ready for the next sandbox query, but prevents accidental mutations when testing variants.