Module - SQL Admin

A drop-in SQL console to run raw queries against your database, gated by a role. By default a query runs with ['read']: a READ ONLY transaction, one statement at a time (Postgres extended protocol), so the database itself rejects writes. The “allow writes” box adds 'write'. Comes with a companion <SqlAdmin /> Svelte component styled with raw Tailwind utilities (no daisyUI / shadcn plugin needed).

Once you have it setup, assign you the role "SqlAdmin.Admin" (You can get it via Roles_SqlAdmin.SqlAdmin_Admin), or rely on the global FF_Role.FF_Role_Admin.

Results of every query are also logged to the browser console as for AI: <json rows> - handy for chrome-devtools / AI agents inspecting the page via list_console_messages.

Installation

npm add firstly@latest -D

Setup

import { remultApi } from 'remult/remult-sveltekit'
import { sqlAdmin } from 'firstly/sqlAdmin/server'

export const api = remultApi({
  modules: [
    sqlAdmin({
      // OPTIONAL - the route where you mount <SqlAdmin />, used in the AI hint logged on boot.
      // path: '/sql/admin',
      // OPTIONAL - override the SqlDatabase used to execute queries.
      // Defaults to `SqlDatabase.getDb()` (the active Remult data provider).
      // dp: async () => myCustomSqlDatabase,
    }),
  ],
})

Usage

Mount the component on a protected route. The component is styled with raw Tailwind utilities, so your project needs Tailwind set up - nothing else.

<!-- src/routes/(admin)/sql/admin/+page.svelte -->
<script lang="ts">
  import { SqlAdmin } from 'firstly/sqlAdmin'
</script>

<SqlAdmin />

Then assign the role to your user:

import { Roles_SqlAdmin } from 'firstly/sqlAdmin'

// somewhere where you grant roles
user.roles = [Roles_SqlAdmin.SqlAdmin_Admin]

That’s it! 🎉

SQL tokens - run SQL from outside the browser

Bearer tokens for a script or an AI on a dev machine that needs a fact only the production database holds. Nothing is registered unless you opt in - no entities, no endpoints.

sqlAdmin({
  // `sqlAdmin: false` registers no controller at all; combined with `tokens` it throws,
  // because tokens run through that same `exec` endpoint.
  tokens: {
    // Which capabilities may be minted. `read` runs inside a READ ONLY transaction,
    // one statement at a time (extended query protocol). `write` runs SQL as is.
    capabilities: ['read'],
    // OPTIONAL - the token then acts as its minter with their LIVE roles
    // (lose admin, tokens die). Without it the token is its own authority.
    userFromId: (id) => loadUser(id),
    // OPTIONAL - prefix: 'ffsql_', apiPath: '/api', callLogRetentionDays: 30,
    // pool (defaults to the Postgres pool behind your data provider)
  },
})

<script lang="ts">
  import { SqlTokens } from 'firstly/sqlAdmin'
</script>

<!-- Defaults to a ready-to-run `ff-sql` line; override `command` for your own. -->
<SqlTokens capabilities={['read']} />

The rules the module enforces:

A token is a bag of capabilities that acts as its minter, for a fixed lifetime (1h, 24h, 7d). Only the sha256 is stored; the raw value is shown once. Leave the name empty to get an adjective-animal-3f9 one.
Minting and revoking need a live session - a token can never mint, extend or revoke a token.
A token answers exactly one path: the console’s own /api/ff/sqlAdmin/exec. Anywhere else it authenticates nobody. Revoking is a plain repo(SqlToken).update(id, { revokedAt }) - the only writable field, and final.
Revoke and delete are different gestures: revoking is the kill switch and keeps the row and its calls (the audit trail); deleting is cleanup and takes the calls with it. A live token can only be revoked - deleting one throws. SqlAdminController.purgeTokens() deletes every expired or revoked token at once (the “Delete N dead” button).
Every call is logged in _ff_sql_token_calls (SQL text, row count, ms, error - never the rows) and pruned after callLogRetentionDays.
remult.context.sqlTokenBearer is set whenever a bearer with the prefix is seen, valid or not - your own initRequest can return on it so a token request never falls back to a cookie.

Calling it

firstly ships an ff-sql bin, and the mint screen hands over the whole command - token, origin and api path inlined - so there is nothing to configure, no env file to point at:

FF_SQL_TOKEN=ffsql_… npx ff-sql --origin=https://my.app "select count(*) from users"
FF_SQL_TOKEN=ffsql_… npx ff-sql --origin=https://my.app --json "select * from users limit 3"
FF_SQL_TOKEN=ffsql_… npx ff-sql --origin=https://my.app << 'SQL'
  select handle from "users" where "createdAt" > now() - interval '7 days'
SQL

The token sits in the command line, so paste it with a leading space (or keep it in an env file) to keep it out of your shell history. Multi-line SQL goes through stdin (the heredoc above); as an argument, anything that is not flag-shaped is SQL, and -- ends the flags - so a leading -- comment is fine.

--origin also reads FF_SQL_ORIGIN, so an app that queries the same place all day can wrap it: "sql:prod": "FF_SQL_ORIGIN=https://my.app ff-sql" (package scripts have ff-sql on their PATH).

Rows go to stdout (a table, or --json), the N row(s) · X ms summary to stderr. ff-sql --help lists the flags. Or skip the bin entirely:

curl -X POST https://my.app/api/ff/sqlAdmin/exec \
  -H "authorization: Bearer ffsql_..." -H "content-type: application/json" \
  -d '{"args":["select count(*) from users"]}'

Errors point at the right identifier

Callers writing SQL against a schema they cannot see mostly fail on names - entity columns are quoted camelCase, so created_at and key_value do not exist. Any failed query comes back with Postgres’ own HINT when there is one, or the closest names from the catalog, plus the SQLSTATE:

column a.analysisversion does not exist · Did you mean "activities"."analysisVersion"? · [42703 at 8]
relation "key_value" does not exist · Did you mean "keyValues"? · [42P01 at 15]

That enriched message is also what lands in the call log.