SQLite user authentication

Add real per-user logins (email + password) to a SQLite database — register, login, sessions, admin role, account management — without standing up a separate auth service.

Layered on top of the SQLite Database API: every database can have its own isolated user pool, and the scoped Sqlite token gates which database while the user session gates which user.

Do I need this?

• Single trusted operator (your own admin tool, a CI script, ChatGPT-style server-to-server caller): skip user auth — install with requireUserAuth: false, ship the scoped token, you're done.
• App with multiple end-users sharing a database (Kanban for a team, todo list with assignees, member-only blog comments): enable user auth — requireUserAuth: true (the default for new installs).

How the layers fit together

1. Master API key  ──►  /sqlite/auth/install                          (one-time)
                          requireUserAuth: true
                          ↓
                        scoped token  +  flag (requires_user_auth = 1)

2a. Bootstrap (one-time, during install wizard):
    Browser  ──►  /sqlite/auth/register
                  X-Sqlite-Token  +  X-API-Key (master, in hand for setup)
                  ↓
                  first user, role = admin

2b. Onboarding (whenever the admin invites a teammate):
    Browser  ──►  /sqlite/auth/register
                  X-Sqlite-Token  +  X-Sqlite-User-Token (admin session)
                  ↓
                  additional user, role = user

3. Login (any registered user, from anywhere):
   Browser  ──►  /sqlite/auth/login
                  X-Sqlite-Token only
                  ↓
                  user session token (opaque, 64 hex)

4. Daily use:
   Browser  ──►  /sqlite/query  /  /sqlite/exec  /  …
                  X-Sqlite-Token        (which database)
                  X-Sqlite-User-Token   (which user)
                  ↓
                  rows / version / …

Both tokens are required for CRUD on a database that was installed with requireUserAuth: true. The scoped token alone keeps working when requireUserAuth: false — that's the back-compat mode for server-to-server clients.

Public registration is NOT supported

The scoped Sqlite token lives in your app's config.js, which is publicly readable. To prevent anyone who finds the URL from creating accounts in the user pool, /auth/register always requires admin authorization — either the master API key (bootstrap) or an admin's user-token (onboarding). Anonymous calls with only the scoped token return 401.

If your app needs true public sign-up, run a server-side proxy that holds the master API key and forwards filtered requests. Static front-ends cannot self-host this.

Data model

• One user pool per (site, path) tuple — the same email can register independently on kanban/db.sqlite and blog/posts.sqlite because they are separate apps.
• First user in an empty pool becomes admin automatically. Subsequent users get role: "user".
• Passwords are bcrypt-hashed (PHP password_hash, cost 12) before storage. The plain password never lives on Flexweg disk and is never returned in any response.
• Sessions are opaque 64-char hex tokens, stored hashed (SHA-256) at rest. A DB compromise cannot resurrect raw tokens.
• Sliding sessions: every authenticated CRUD call refreshes expires_at to now + 30 days (only when the remaining lifetime drops below 7 days, to keep DB writes down).

Quick start

1. Install a database that requires user auth

curl -X POST https://www.flexweg.com/api/v1/sqlite/auth/install \
  -H "X-API-Key: YOUR_MASTER_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "path": "kanban/db.sqlite",
    "name": "Team Kanban",
    "requireUserAuth": true
  }'

Origin header is required on every call

Scoped tokens are always pinned to your site's canonical origins (the *.flexweg.com subdomain plus any active custom domain) — Flexweg manages that list for you and keeps it in sync when you attach / remove a custom domain or rename the slug. Every subsequent call (register / login / CRUD) must include an Origin header matching one of those values, otherwise the server returns 403 Request Origin is not permitted for this token.

Browsers send Origin automatically on cross-origin requests. curl, Postman, server-to-server tooling don't — add -H "Origin: https://your-site.flexweg.com" to every snippet on this page when testing. For brevity the doc snippets below omit it.

2. Register the first user — BOOTSTRAP (master key required)

# The master API key authorizes the very first registration. The first
# user in the empty pool gets role:"admin" automatically.
curl -X POST https://www.flexweg.com/api/v1/sqlite/auth/register \
  -H "X-Sqlite-Token: SCOPED_TOKEN" \
  -H "X-API-Key: YOUR_MASTER_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "email": "[email protected]",
    "password": "alice-strong-pw",
    "displayName": "Alice"
  }'

2b. (Later) admin onboards another user — admin session required

# Once an admin exists, they can invite teammates without needing
# the master key again. The admin's user-token carries the authorization.
curl -X POST https://www.flexweg.com/api/v1/sqlite/auth/register \
  -H "X-Sqlite-Token: SCOPED_TOKEN" \
  -H "X-Sqlite-User-Token: ALICE_SESSION_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "email": "[email protected]",
    "password": "bob-strong-pw",
    "displayName": "Bob"
  }'

3. Login and get a session token

curl -X POST https://www.flexweg.com/api/v1/sqlite/auth/login \
  -H "X-Sqlite-Token: SCOPED_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"email": "[email protected]", "password": "alice-strong-pw"}'

4. Use both tokens on every CRUD call

curl -X POST https://www.flexweg.com/api/v1/sqlite/exec \
  -H "X-Sqlite-Token: SCOPED_TOKEN" \
  -H "X-Sqlite-User-Token: USER_SESSION_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql": "INSERT INTO tickets (title) VALUES (?)", "params": ["My ticket"]}'

User endpoints

All endpoints below require the scoped X-Sqlite-Token header. Additional headers per endpoint as noted.

POST /api/v1/sqlite/auth/register

Create a new user account in this database's pool. Requires admin authorization — the scoped Sqlite token alone is never enough (it lives in the public config.js of the consuming app).

The first registration on an empty pool gets role: "admin" automatically; subsequent registrations get role: "user".

Required headers (one of)

Header	Use case
X-API-Key: <master>	Bootstrap. Used by the install wizard to create the very first admin. The master key must own the storage folder the scoped token points at — mismatched master keys are rejected with 403.
X-Sqlite-User-Token: <admin-session>	Onboarding. Used by an admin already logged into the app to invite teammates. The session must belong to a user with role: "admin".

Calling with neither header returns 401 Unauthorized. Rate-limited per IP (5 attempts/min, 20/hour, shared with /login) — 429 on excess.

Body parameters

Field	Type	Description
email	string (required)	Lower-cased and trimmed before storage. Must be a valid email.
password	string (required)	Minimum 8 characters. Trivial passwords (password, 12345678, …) are rejected.
displayName	string	Optional human label (max 120 chars).

201 Created

{
  "success": true,
  "user": {
    "id": 1,
    "email": "[email protected]",
    "displayName": "Alice",
    "role": "admin",
    "disabled": false
  }
}

Errors

Status	Error code	Meaning
400	INVALID_EMAIL	Email is malformed or too long (> 254 chars).
400	PASSWORD_TOO_SHORT	Password under 8 chars.
400	PASSWORD_TOO_COMMON	Password matched the trivial-passwords blocklist.
401	Unauthorized	No X-API-Key AND no X-Sqlite-User-Token. Or one of those was provided but invalid/expired.
403	Forbidden	Master key doesn't own this database, or the provided user-token belongs to a non-admin.
409	EMAIL_ALREADY_REGISTERED	An account already exists for this email in this database.
402	USER_POOL_LIMIT_REACHED	The pool hit the per-plan cap — see limits below.
429	—	IP rate-limit hit.

POST /api/v1/sqlite/auth/login

Exchange credentials for a session token. Rate-limited per IP (5 attempts/min, 20/hour, shared with the rest of Flexweg's login attempts) — 429 on excess.

cURL

curl -X POST https://www.flexweg.com/api/v1/sqlite/auth/login \
  -H "X-Sqlite-Token: SCOPED_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"email": "[email protected]", "password": "alice-strong-pw"}'

200 OK

{
  "success": true,
  "userToken": "177343f96b0022e548af45d09c9d659005b1f921dd76c1927d13fe13f29154c1",
  "expiresAt": "2026-06-22T17:43:52+00:00",
  "user": {
    "id": 1,
    "email": "[email protected]",
    "displayName": "Alice",
    "role": "admin",
    "disabled": false
  }
}

The response is sent with Cache-Control: no-store so the credential never lands in an intermediary cache.

Errors

Status	Error code	Meaning
401	INVALID_CREDENTIALS	Bad email or bad password — returned identically in both cases to avoid leaking which one.
403	ACCOUNT_DISABLED	An admin has disabled this account.
429	—	IP rate-limit hit.

POST /api/v1/sqlite/auth/logout

Invalidate the current session token. Idempotent — a 204 is returned even if the token didn't exist.

Headers: X-Sqlite-Token, X-Sqlite-User-Token

Response: 204 No Content

GET /api/v1/sqlite/auth/me

Return the user attached to the current session.

Headers: X-Sqlite-Token, X-Sqlite-User-Token

200 OK

{
  "success": true,
  "user": {
    "id": 1,
    "email": "[email protected]",
    "displayName": "Alice",
    "role": "admin",
    "disabled": false,
    "createdAt": "2026-05-23T17:43:32+00:00",
    "lastLoginAt": "2026-05-23T17:43:52+00:00"
  }
}

Use this on app boot to detect a stale localStorage token — a 401 here means "show the login screen".

POST /api/v1/sqlite/auth/change-password

Change your own password. Verifies currentPassword server-side; on success, invalidates every other active session for this user (the current one stays alive so the caller isn't kicked out).

Headers: X-Sqlite-Token, X-Sqlite-User-Token

Body:

{ "currentPassword": "old-pw", "newPassword": "new-strong-pw" }

Response: 204 No Content

Errors: same 400 PASSWORD_TOO_SHORT / PASSWORD_TOO_COMMON as register, 401 INVALID_CREDENTIALS if currentPassword is wrong.

Admin endpoints

All endpoints in this section require X-Sqlite-Token + X-Sqlite-User-Token, and the calling user must have role: "admin". Non-admins get 403 Admin role required.

GET /api/v1/sqlite/auth/users

List every user in this database's pool (ordered by createdAt ascending).

200 OK

{
  "success": true,
  "users": [
    {
      "id": 1,
      "email": "[email protected]",
      "displayName": "Alice",
      "role": "admin",
      "disabled": false,
      "createdAt": "2026-05-23T17:43:32+00:00",
      "lastLoginAt": "2026-05-23T17:43:52+00:00"
    },
    {
      "id": 2,
      "email": "[email protected]",
      "displayName": "Bob",
      "role": "user",
      "disabled": false,
      "createdAt": "2026-05-23T17:43:33+00:00",
      "lastLoginAt": null
    }
  ]
}

PATCH /api/v1/sqlite/auth/users/{id}

Update a user's role, disabled state, or displayName. Partial — only provided fields are touched.

Body (any combination):

{ "role": "admin", "disabled": false, "displayName": "Alice Doe" }

Side-effects

• Setting disabled: true immediately revokes all of that user's sessions.
• Changing role or disabled is blocked by self-action guards below.

POST /api/v1/sqlite/auth/users/{id}/reset-password

Admin-initiated password reset — does not require knowing the old password. Invalidates every session for the target user. The new password is communicated out-of-band (Slack, etc.) — Flexweg does not send emails.

Body:

{ "newPassword": "new-strong-pw" }

Response: 204 No Content

DELETE /api/v1/sqlite/auth/users/{id}

Delete a user account. Cascades into their sessions.

Response: 204 No Content

Guard rails

Server-side checks that protect a database from being locked out of admin access:

Action	Block	Error code
Admin changes their own role	Always blocked	CANNOT_CHANGE_OWN_ROLE
Admin disables their own account	Always blocked	CANNOT_DISABLE_SELF
Admin deletes their own account	Always blocked	CANNOT_DELETE_SELF
Demoting / disabling / deleting the last active admin	Always blocked	LAST_ADMIN

All return 403 Forbidden.

Integrating with CRUD

When the scoped token has requires_user_auth = 1 (set at /auth/install via requireUserAuth: true, default for new installs), every CRUD endpoint (/query, /exec, /batch, /version, /schema, /vacuum) requires:

1. X-Sqlite-Token — the scoped token (as before).
2. X-Sqlite-User-Token — a valid, non-expired session token.

Missing or invalid user token → 401 Unauthorized. Account disabled → also 401 (the session is dropped server-side the moment the admin flips disabled: true).

/vacuum is admin-only

When requires_user_auth = 1, only an admin can run VACUUM. Non-admins get 403 VACUUM requires an admin user.

When requires_user_auth = 0 (legacy / server-to-server tokens), any holder of the scoped token can call /vacuum.

A minimal browser client

lib/sqlite-auth.js

const ENDPOINT = 'https://www.flexweg.com/api/v1/sqlite/auth';

export const auth = {
  // Bootstrap the very first admin. Pass the master API key obtained
  // during the install wizard — discard it from memory afterwards.
  async registerAdmin(scopedToken, masterApiKey, email, password, displayName) {
    return req(scopedToken, '/register', { email, password, displayName }, 'POST', {
      'X-API-Key': masterApiKey,
    });
  },

  // Admin invites a teammate. The current admin must be logged in;
  // their session token from localStorage carries the authorization.
  async inviteUser(scopedToken, email, password, displayName) {
    const userToken = localStorage.getItem('sqliteUserToken');
    if (!userToken) throw new Error('Must be logged in as admin to invite users');
    return req(scopedToken, '/register', { email, password, displayName }, 'POST', {
      'X-Sqlite-User-Token': userToken,
    });
  },

  async login(scopedToken, email, password) {
    const res = await req(scopedToken, '/login', { email, password });
    if (res.success) localStorage.setItem('sqliteUserToken', res.userToken);
    return res;
  },

  async me(scopedToken) {
    const userToken = localStorage.getItem('sqliteUserToken');
    if (!userToken) return null;
    const res = await fetch(`${ENDPOINT}/me`, {
      headers: {
        'X-Sqlite-Token': scopedToken,
        'X-Sqlite-User-Token': userToken,
      },
    });
    if (res.status === 401) {
      localStorage.removeItem('sqliteUserToken');
      return null;
    }
    return (await res.json()).user;
  },

  async logout(scopedToken) {
    const userToken = localStorage.getItem('sqliteUserToken');
    if (userToken) {
      await fetch(`${ENDPOINT}/logout`, {
        method: 'POST',
        headers: { 'X-Sqlite-Token': scopedToken, 'X-Sqlite-User-Token': userToken },
      });
      localStorage.removeItem('sqliteUserToken');
    }
  },

  getUserToken() {
    return localStorage.getItem('sqliteUserToken');
  },
};

async function req(scopedToken, path, body, method = 'POST', extraHeaders = {}) {
  const res = await fetch(`${ENDPOINT}${path}`, {
    method,
    headers: {
      'X-Sqlite-Token': scopedToken,
      'Content-Type': 'application/json',
      ...extraHeaders,
    },
    body: body ? JSON.stringify(body) : undefined,
  });
  return res.json();
}

Then the minimal SQLite client from the previous page just needs to read the user token alongside the scoped token:

lib/sqlite.js — updated

import { SQLITE } from './config.js';
import { auth } from './sqlite-auth.js';

async function call(path, body, method = 'POST') {
  const headers = {
    'X-Sqlite-Token': SQLITE.token,
    'Content-Type': 'application/json',
  };
  const userToken = auth.getUserToken();
  if (userToken) headers['X-Sqlite-User-Token'] = userToken;

  const res = await fetch(`${SQLITE.endpoint}${path}`, {
    method,
    headers,
    body: body ? JSON.stringify(body) : undefined,
  });
  const data = await res.json();
  if (res.status === 401) {
    // Session expired or invalidated — kick to login.
    localStorage.removeItem('sqliteUserToken');
    location.href = '/login.html';
    throw new Error('Authentication required');
  }
  if (!res.ok) throw new Error(data.message || 'SQLite error');
  return data;
}

Security model

Risk	Mitigation
Anonymous account creation via the public config.js	/register requires X-API-Key (master, bootstrap) OR X-Sqlite-User-Token of an admin (onboarding). Scoped token alone returns 401.
Password brute force on /login	bcrypt cost 12 + IP rate-limit (5/min, 20/hour).
Brute-forcing /register to enumerate users / spam admin invites	Same IP rate-limit as /login (shared bucket).
Session token leak via XSS	Sessions stored hashed at rest (SHA-256); same exfiltration risk as the scoped token; mitigate at the app layer (CSP, no untrusted HTML rendering).
DB compromise	Both password_hash and token_hash are one-way functions — raw secrets cannot be recovered.
Last admin locked out	Server-side guards block demoting/disabling/deleting the last admin.
Token replay across databases	Sessions are scope-checked: a token for kanban/db.sqlite cannot be used against blog/posts.sqlite even if both share a scoped token (which they can't).
Email enumeration on /login	Generic INVALID_CREDENTIALS returned for both "bad email" and "bad password".
Email enumeration on /register	Restricted to admins / master-key holder — they already see the user list via /auth/users, so the 409 is not extra info.
Master API key leak / theft	Master key is only used at install + bootstrap registration. Compromise allows full SQLite admin on the site. Rotate via the dashboard; existing scoped tokens & user sessions stay valid.
Browser-based exfiltration from a malicious site (CSRF-style)	Every scoped token is automatically pinned to the site's canonical origins (Flexweg subdomain + active custom domains). Requests with a missing or non-matching Origin header return 403. Browsers can't spoof Origin. Server-to-server callers can, so this is defense-in-depth rather than a hard gate.

Limits by plan

Maximum users per SQLite database (separate from the database count cap):

Plan	Users per database
Free	3
Standard	25
Premium	250
Business	2500
Enterprise	Unlimited

Hitting the cap surfaces 402 USER_POOL_LIMIT_REACHED on /register.

Out of scope (today)

This first cut intentionally keeps the surface tight. Not provided in v1:

• Public sign-up (anyone visiting the app can create an account). By design — the scoped token in config.js is public. To enable it you'd need a server-side proxy that holds the master API key (or an admin user-token) and forwards filtered requests to /auth/register.
• Email verification of registered addresses (no SMTP integration yet).
• Self-service password reset by email. Admin reset only.
• OAuth / SSO / SAML.
• MFA / 2FA / WebAuthn.
• Per-row authorization helpers. Apps still enforce row-level rules in their own SQL (WHERE assignee_id = :current_user_id …).
• Long-lived API keys per user for automation.

If you need any of these today, fall back to an external IdP — the SQLite database can still hold app state alongside.

See also

• SQLite Database API — the CRUD endpoints this layer extends.
• External databases — when to pick a BaaS over the built-in SQLite + auth.