SQLite database

Persist structured app data on your Flexweg site with real SQL — joins, transactions, schema migrations — and safe multi-user concurrent writes. No Firebase, no Supabase, no external account.

A SQLite project is a single .sqlite file living inside your site's storage (e.g. kanban/db.sqlite, blog/posts.sqlite). Flexweg's API serializes every write server-side so two browsers editing simultaneously never lose data.

When to reach for this vs. Forms or BaaS

• Just collecting submissions? Use the simpler Forms API.
• Building a real app (Kanban, todo, gradebook, CRM) and want zero external dependencies? This is for you.
• Need real-time push out of the box, fine-grained row-level security, or 100K+ concurrent writers? Stick with a BaaS — see external databases.

How authentication works

Up to three layers, depending on the use case:

1. Master API key — your account-wide X-API-Key. Used at install time (to mint a scoped token) AND for the very first /auth/register call that creates the bootstrap admin (see user-auth). The browser must never hold this key in steady state.
2. Scoped SQLite token (X-Sqlite-Token header) — grants SQL access to exactly one (site, path) tuple. A token issued for kanban/db.sqlite cannot touch blog/posts.sqlite or any other file on your storage. Safe to ship in a public config.js: it cannot register users or create credentials on its own.
3. End-user session token (X-Sqlite-User-Token header, optional) — when the scoped token was minted with requireUserAuth: true (the default for new installs), every CRUD call must additionally carry a session token obtained from /auth/login. See the SQLite user authentication page for register / login / sessions / admin endpoints.

Skip user auth when you don't need it

Server-to-server callers (CI scripts, internal admin tools, ChatGPT-style agents) typically don't want per-user logins — pass requireUserAuth: false at install time. The scoped token alone then gates every request.

1. INSTALL (once, from an admin browser or CI step)
   master API key  →  /api/v1/sqlite/auth/install  →  scoped token
                                                       ↓
                                                ship in app config.js

2. STEADY STATE (every page load, every action)
   scoped token  →  /api/v1/sqlite/{query,exec,batch,…}

Every scoped token is also pinned to your site's origins — the *.flexweg.com subdomain and any active custom domain attached to the site. Requests coming from any other origin are rejected with 403. You don't configure this list yourself: Flexweg rebuilds it automatically when you attach / detach a custom domain or rename the site slug, so installed apps keep working after the move.

Quick start

1. Create a database + scoped token (use master API key)

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": "Kanban app",
    "permissions": "readwrite",
    "requireUserAuth": false
  }'

201 Created

{
  "success": true,
  "token": "7f328f36fbb3cbd4c92f7508aa2f700f49799817f16872cdf7a69370a841623c",
  "tokenId": 1,
  "path": "kanban/db.sqlite",
  "permissions": "readwrite",
  "requiresUserAuth": false,
  "allowedOrigins": ["https://your-site.flexweg.com"],
  "expiresAt": null
}

The allowedOrigins you see in the response is computed server-side from the site's canonical origins (Flexweg subdomain + any active custom domain). You don't set it — Flexweg keeps it in sync automatically.

requireUserAuth: false for this Quick start

The example above opts out of end-user authentication so the curl snippets stay short. For a real multi-user app, omit requireUserAuth (it defaults to true) and follow SQLite user authentication to register users and use X-Sqlite-User-Token on every CRUD call.

2. Create your schema (use scoped token from now on)

curl -X POST https://www.flexweg.com/api/v1/sqlite/exec \
  -H "X-Sqlite-Token: 7f328f36…" \
  -H "Content-Type: application/json" \
  -d '{"sql": "CREATE TABLE tickets (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, sprint_id TEXT)"}'

3. Read + write from your app

curl -X POST https://www.flexweg.com/api/v1/sqlite/exec \
  -H "X-Sqlite-Token: 7f328f36…" \
  -H "Content-Type: application/json" \
  -d '{"sql": "INSERT INTO tickets (title, sprint_id) VALUES (?, ?)", "params": ["Fix bug", "abc123"]}'

curl -X POST https://www.flexweg.com/api/v1/sqlite/query \
  -H "X-Sqlite-Token: 7f328f36…" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM tickets WHERE sprint_id = ? ORDER BY id DESC", "params": ["abc123"]}'

Auth lifecycle

POST /api/v1/sqlite/auth/install

Create a SQLite database at path (if it doesn't already exist) and mint a scoped token for it. Requires the master X-API-Key — call this once during your app's setup, then store the returned token in your app's config.

Body parameters

Field	Type	Description
path	string (required)	Where the database lives inside your site's storage. Must end with .sqlite or .db. Example: kanban/db.sqlite.
name	string (required)	Human label shown in the dashboard. Max 120 chars.
permissions	string	"read" or "readwrite" (default). Read tokens cannot call /exec, /batch, or /vacuum.
requireUserAuth	boolean	Defaults to true. When true, every CRUD call must additionally carry a X-Sqlite-User-Token issued by /auth/login. Pass false for server-to-server tokens. See user auth.
expiresInDays	integer	Optional expiry. Omit for tokens that never expire.

allowedOrigins is managed for you

You may have seen older docs or app code passing allowedOrigins here — that field is now derived server-side from the site's canonical origins (Flexweg subdomain + active custom domains) and any value you send is ignored. The list is automatically rewritten when you attach a custom domain, remove it, or rename the site slug, so existing tokens keep working after a move.

cURL — read-only token, expires in 30 days

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": "blog/posts.sqlite",
    "name": "Public blog reader",
    "permissions": "read",
    "expiresInDays": 30
  }'

Treat the master key like a password

The master key is the equivalent of a database admin password. It should live only in admin browsers, CI environment variables, or trusted backend code — never in a shipped JS bundle. The whole point of the scoped token is that you can rotate or revoke it without touching the master key.

Origin pinning applies to every call

The scoped token is always pinned to your site's canonical origins, so every call (CRUD, auth, schema…) must carry an Origin header matching either your *.flexweg.com subdomain or one of your active custom domains. A mismatched or missing Origin returns 403 Request Origin is not permitted for this token.

• Browsers send Origin automatically on cross-origin fetch() calls — nothing to do. Code hosted on your own Flexweg site or custom domain just works.
• curl / Postman / server-to-server callers must add it explicitly:

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 "Origin: https://your-site.flexweg.com" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT 1"}'

If you attach (or remove) a custom domain after install, the allow-list is rebuilt automatically — you don't need to re-mint the token.

GET /api/v1/sqlite/auth/tokens

List every scoped token under your site (active + revoked + expired). Requires the master X-API-Key.

200 OK

{
  "success": true,
  "tokens": [
    {
      "id": 1,
      "path": "kanban/db.sqlite",
      "name": "Kanban app",
      "permissions": "readwrite",
      "requiresUserAuth": true,
      "allowedOrigins": ["https://your-site.flexweg.com", "https://kanban.acme.com"],
      "createdAt": "2026-05-23T14:46:10+02:00",
      "lastUsedAt": "2026-05-23T14:46:32+02:00",
      "expiresAt": null,
      "revokedAt": null
    }
  ]
}

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

Revoke a token. Subsequent requests with that token get 401. Requires the master X-API-Key.

cURL

curl -X DELETE https://www.flexweg.com/api/v1/sqlite/auth/tokens/1 \
  -H "X-API-Key: YOUR_MASTER_API_KEY"

200 OK

{
  "success": true,
  "tokenId": 1,
  "revokedAt": "2026-05-23T14:46:50+02:00"
}

Revocation is forever

Revoked tokens cannot be unrevoked. To restore access, issue a new token via /auth/install (re-use the existing path — the database itself is untouched).

CRUD endpoints

All endpoints below require the scoped X-Sqlite-Token header. Every successful response includes a monotonic version integer that bumps on every write — use it for change detection from polling clients.

POST /api/v1/sqlite/query

Run a single SELECT (or any other read). Uses positional ? placeholders — never concatenate user input into the sql string.

Body parameters

Field	Type	Description
sql	string (required)	A single SQL statement with optional ? placeholders.
params	array	Bound parameters, in order. Omit or use [] for no params.

cURL

curl -X POST https://www.flexweg.com/api/v1/sqlite/query \
  -H "X-Sqlite-Token: YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT * FROM tickets WHERE sprint_id = ? ORDER BY id DESC LIMIT 50",
    "params": ["abc123"]
  }'

200 OK

{
  "success": true,
  "rows": [
    { "id": 73, "title": "Fix bug", "sprint_id": "abc123" }
  ],
  "rowCount": 1,
  "version": 142
}

POST /api/v1/sqlite/exec

Run one write statement (INSERT / UPDATE / DELETE / CREATE TABLE / ALTER TABLE …). Acquires an exclusive lock on the file while the write is in flight, then commits and uploads back to S3.

cURL

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

200 OK

{
  "success": true,
  "rowsAffected": 1,
  "lastInsertRowId": 73,
  "version": 143
}

POST /api/v1/sqlite/batch

Run multiple statements in a single SQLite transaction — all-or-nothing. If any statement fails the whole transaction rolls back and you get a 400 with the failing statement index.

Body parameters

Field	Type	Description
statements	array (required)	List of { sql, params? } objects, executed in order inside a BEGIN/COMMIT block.

cURL

curl -X POST https://www.flexweg.com/api/v1/sqlite/batch \
  -H "X-Sqlite-Token: YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "statements": [
      { "sql": "UPDATE tickets SET status = ? WHERE id = ?", "params": ["done", 73] },
      { "sql": "INSERT INTO activity (action, ticket_id) VALUES (?, ?)", "params": ["completed", 73] }
    ]
  }'

200 OK

{
  "success": true,
  "results": [
    { "rowsAffected": 1, "lastInsertRowId": null },
    { "rowsAffected": 1, "lastInsertRowId": 412 }
  ],
  "version": 144
}

GET /api/v1/sqlite/version

Lightweight change-detection endpoint — returns the current version integer + file size, without acquiring a write lock. Cheap to poll.

cURL

curl https://www.flexweg.com/api/v1/sqlite/version \
  -H "X-Sqlite-Token: YOUR_TOKEN"

200 OK

{
  "success": true,
  "version": 144,
  "sizeBytes": 20480
}

A typical client polls this every 5–15 seconds; when version changes, it re-runs its query calls.

GET /api/v1/sqlite/schema

Return the CREATE TABLE statements for every user table (system tables and the internal _meta table are filtered out).

200 OK

{
  "success": true,
  "tables": [
    {
      "name": "tickets",
      "sql": "CREATE TABLE tickets (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, sprint_id TEXT)"
    }
  ]
}

POST /api/v1/sqlite/vacuum

Run VACUUM to reclaim space after large deletes. Heavy — holds the write lock for the whole compaction. Run from an admin tool or a cron, not on every action.

200 OK

{
  "success": true,
  "sizeBefore": 4194304,
  "sizeAfter": 524288,
  "durationMs": 312,
  "version": 145
}

Errors

All error responses follow the standard shape (see Errors):

400 — SQL error

{
  "error": "SQL error",
  "message": "SQLSTATE[HY000]: General error: 1 no such table: tickets"
}

401 — Missing or invalid scoped token

{
  "error": "Unauthorized",
  "message": "Invalid, expired, or revoked SQLite token."
}

401 — User authentication required (requireUserAuth=true)

{
  "error": "Unauthorized",
  "message": "User authentication required. Send X-Sqlite-User-Token.",
  "hint_for_ai": "Call POST /api/v1/sqlite/auth/login to obtain a user token."
}

401 — User session expired / invalid

{
  "error": "Unauthorized",
  "message": "Invalid or expired user session."
}

403 — Read-only token attempting a write

{
  "error": "Forbidden",
  "message": "This token is read-only."
}

403 — VACUUM by non-admin (requireUserAuth=true)

{
  "error": "Forbidden",
  "message": "VACUUM requires an admin user."
}

403 — Origin not allowed

{
  "error": "Forbidden",
  "message": "Request Origin is not permitted for this token."
}

402 — SQLite count limit reached

{
  "error": "SQLite count limit reached",
  "message": "Your plan allows 1 SQLite database(s). Upgrade or revoke an existing token."
}

413 — SQLite size limit exceeded

{
  "error": "SQLite size limit exceeded",
  "message": "This SQLite database is 6 MB; your plan cap is 5 MB. Upgrade or delete rows + VACUUM to shrink.",
  "sizeBytes": 6291456,
  "limitBytes": 5242880
}

Limits by plan

The SQLite caps are enforced independently of your generic file storage quota.

Plan	Databases	Size per database
Free	1	5 MB
Standard	5	50 MB
Premium	20	200 MB
Business	100	1 GB
Enterprise	Unlimited	Unlimited

Hitting the size cap surfaces a 413 on the very write that pushed over the line — the write did apply (and the file was uploaded), but subsequent writes will keep failing until you free space or upgrade.

Hitting the count cap blocks new /auth/install calls.

Concurrency model

Every write acquires an exclusive lock keyed by (site, path). Two writes on the same file are serialized; writes on different files run in parallel. Reads skip locking (S3 is strongly consistent — you'll see either version N or N+1, both are valid snapshots).

The lock has a 30-second TTL. If the PHP worker dies mid-request, the lock expires and the next caller proceeds against the pre-crash state on S3 — the in-flight write is simply lost. Nothing half-applied ever reaches S3.

What clients ship

A JS client typically holds:

config.js (deployed alongside your app)

export const SQLITE = {
  endpoint: 'https://www.flexweg.com/api/v1/sqlite',
  token:    '7f328f36fbb3cbd4c92f7508aa2f700f49799817f16872cdf7a69370a841623c',
};

lib/sqlite.js — minimal wrapper

import { SQLITE } from './config.js';

async function call(path, body, method = 'POST') {
  const res = await fetch(`${SQLITE.endpoint}${path}`, {
    method,
    headers: { 'X-Sqlite-Token': SQLITE.token, 'Content-Type': 'application/json' },
    body: body ? JSON.stringify(body) : undefined,
  });
  const data = await res.json();
  if (!res.ok) throw new Error(data.message || 'SQLite error');
  return data;
}

export const sql = {
  query:   (sql, params = []) => call('/query', { sql, params }),
  exec:    (sql, params = []) => call('/exec',  { sql, params }),
  batch:   (statements)       => call('/batch', { statements }),
  version: ()                 => call('/version', null, 'GET'),
  schema:  ()                 => call('/schema',  null, 'GET'),
};

Then in your app:

import { sql } from './lib/sqlite.js';

const { rows } = await sql.query('SELECT * FROM tickets WHERE sprint_id = ?', ['abc123']);
await sql.exec('INSERT INTO tickets (title) VALUES (?)', ['New ticket']);

Cross-references

• Multi-user app? Read SQLite user authentication for the register / login / sessions / admin endpoints that pair with the CRUD API.
• The .sqlite file is a regular object in your site's S3 storage (with private ACL — Flexweg sets this for you). It counts toward your file count but not toward your generic storage cap; SQLite limits are tracked separately.
• For a comparison with external BaaS options, see External databases.
• For form submissions specifically, prefer the simpler Forms API.