#SQLite schema guide

Loomup turns your SQLite tables into REST + realtime endpoints. This guide covers schema conventions, introspection, and compatibility limits.

#Compatible tables

• Base tables only — views are rejected for REST/CDC with a clear validation error.
• Single-column primary keys — composite primary keys are detected and rejected for single-id routes (/api/:table/:id). Prefer INTEGER PRIMARY KEY or a single TEXT/UUID PK.
• Identifiers — REST path segments and config primary_key values accept arbitrary SQLite names when free of NUL, double-quote, backslash, and control characters (max 128 bytes). Spaces, Unicode, apostrophes, and other punctuation are allowed (e.g. "user data", "订单"). Always URL-encode path segments and let Loomup quote identifiers in SQL. A conservative regex like [A-Za-z_][A-Za-z0-9_]* is a portability recommendation, not a hard runtime limit.

#Column types

#Defaults and generated columns

• Columns with DEFAULT accept omitted fields on insert.
• GENERATED ALWAYS columns are surfaced in schema introspection (generated: true, optional generated_expr).
• Insert responses re-SELECT the row after the statement so SQLite AFTER triggers and generated values appear in the API body.

#Constraints and indexes

Introspection (admin UI + describe_table) includes:

• Primary keys (including composite listing for detection)
• Foreign keys (PRAGMA foreign_key_list)
• Indexes with UNIQUE flag and partial index WHERE clauses when present
• Table-level CHECK constraints parsed from CREATE TABLE SQL

#System / internal tables

Tables whose names start with _ are private (not REST-exposed):

External writes to CDC/internal tables via the public REST API are rejected (not exposed). Prefer migrations and CLI for system schema.

#CDC triggers

When a table has realtime = true and is exposed, Loomup installs AFTER INSERT/UPDATE/DELETE triggers that append to _cdc_log. Triggers use stock SQLite only (no Loomup-only functions), so /usr/bin/sqlite3 and other clients can write safely.

#Size quotas (free-tier defaults)

New hosted managed projects also use the 1 GiB database default. Existing project configurations keep their explicit limits.

• DB size is measured as (page_count - freelist_count) * page_size (not WAL/SHM). Deleted rows free freelist pages and reduce used quota without VACUUM.
• Growth writes (REST create/update, auth register, admin user create) return 507 with code database_quota_exceeded when the limit is already reached.
• Deletes, reads, login/refresh, and migrations are not blocked by the DB quota (so you can free space or bootstrap).
• Per-object uploads still use storage.max_upload_bytes (413 when exceeded). Total storage overage uses 507 storage_quota_exceeded (reported usage is the projected total after the rejected upload).
• Live usage appears on GET /admin/api/metrics under quotas; limits appear on GET /admin/api/settings.
• When both storage caps are set, config requires max_upload_bytes <= max_total_bytes.

Raise these for self-host / paid tiers without code changes.

#Migrations

Place ordered *.sql files under migrations/. Run:

loomup migrate --config loomup.toml

Migrations are checksummed; changing an applied file fails closed.

#Sample init table

loomup init creates:

CREATE TABLE IF NOT EXISTS todos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  completed INTEGER NOT NULL DEFAULT 0,
  user_id TEXT
);

See Access rules for row-level authorization on these columns.

#Arbitrary identifiers

Table and column names with spaces, Unicode, or leading digits are accepted when free of quotes/NUL/control characters. They are always double-quoted in generated SQL. REST paths must URL-encode such names (e.g. /api/user%20data).