Your DB as a REST API

Build a REST API shows the hand-written CRUD. This page is the generic version: point Synsema at a database and serve every whitelisted table through the same three routes — discovery by introspection, access by roles, and per-row filtering by a row-level-security lambda. No framework, no codegen, no new language features.

-- Doc example: expose your DB as a REST resource — introspection + whitelist + RLS.
-- The pattern is pure userland: sql_tables() discovers the schema, EXPOSED whitelists
-- what's public, and row-level security is one lambda per table. The serve routes on
-- top are in the page (44-db-api); everything testable without a server lives here.
intent: "doc example: DB-as-API — introspection, whitelist, row-level security"
require db(":memory:")

db_open(":memory:")
sql_exec("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
sql_exec("CREATE TABLE carts (id INTEGER PRIMARY KEY, customer_phone TEXT, status TEXT)")
sql_exec("CREATE TABLE internal_audit (id INTEGER PRIMARY KEY, note TEXT)")
sql_exec("INSERT INTO products (name, price) VALUES ('Notebook Aurora 14', 899)")
sql_exec("INSERT INTO carts (customer_phone, status) VALUES ('+549115555', 'open')")
sql_exec("INSERT INTO carts (customer_phone, status) VALUES ('+549119999', 'open')")
sql_exec("INSERT INTO internal_audit (note) VALUES ('never expose me')")

-- The whitelist IS the API surface: introspection finds tables, YOU decide what's public.
let EXPOSED be where(sql_tables(), (t) => contains(["products", "carts"], t))

-- Row-level security: one lambda per table, (row, user) => bool. Discipline, not
-- enforcement — a forgotten rls_allow is a leak, so keep ALL reads behind table_rows.
task rls_allow(t, row, user)
    when t == "carts"
        give user.role == "admin" or row.customer_phone == user.phone
    give true

task table_rows(t, user)
    when not contains(EXPOSED, t)
        give nothing
    let rows be sql("SELECT * FROM " + t)
    give where(rows, (r) => rls_allow(t, r, user))

let ADMIN be {"user": "root", "role": "admin", "phone": ""}
let MARIA be {"user": "maria", "role": "customer", "phone": "+549115555"}

print("exposed tables: " + text(EXPOSED))

test "introspection + whitelist: only public tables are exposed"
    assert(contains(EXPOSED, "products"))
    assert(contains(EXPOSED, "carts"))
    assert(not contains(EXPOSED, "internal_audit"))

test "a non-exposed table is a 404, not an error"
    assert_eq(table_rows("internal_audit", ADMIN), nothing)
    assert_eq(table_rows("no_such_table", ADMIN), nothing)

test "RLS: admin sees every cart, a customer only their own"
    assert_eq(length(table_rows("carts", ADMIN)), 2)
    let mine be table_rows("carts", MARIA)
    assert_eq(length(mine), 1)
    assert_eq((mine[0])["customer_phone"], "+549115555")

test "RLS: tables without a rule are public to any authenticated user"
    assert_eq(length(table_rows("products", MARIA)), 1)

The three pieces

1. Introspection + whitelist. sql_tables() lists what exists; the whitelist decides what's public. The whitelist IS the API surface — internal tables simply don't exist to the outside:

let EXPOSED be where(sql_tables(), (t) => contains(["products", "carts"], t))

2. Row-level security = one lambda per table. (row, user) => bool, applied to every read. An admin sees everything; a customer sees only their rows:

task rls_allow(t, row, user)
    when t == "carts"
        give user.role == "admin" or row.customer_phone == user.phone
    give true

3. One gate for every read. table_rows(t, user) checks the whitelist, queries, filters through RLS — and every route calls it. Never query around it.

The serve wiring

The generic routes sit on top (auth comes from serve's native bearer auth — see Serve):

serve on 8080
    auth with check_token

    route "GET /api" requires auth
        give EXPOSED                                  -- discoverable surface

    route "GET /api/:table" requires auth
        let rows be table_rows(params.table, user of request)
        give when rows == nothing then not_found("no such resource") otherwise rows

    route "GET /api/:table/:id" requires auth
        let rows be table_rows(params.table, user of request)
        when rows == nothing
            give not_found("no such resource")
        let hit be find_first(rows, (r) => r.id == number(params.id))
        give when hit == nothing then not_found("no such row") otherwise hit

Writes stay explicit — a POST route per writable resource, gated by role (when not contains(WRITERS, u.role) → fail(403, ...)), so the write path validates with expect body {...} like any hand-written API.

This works on Postgres and MySQL, unchanged

The example uses :memory: SQLite so it runs offline, but nothing in the pattern is SQLite-specific: sql()/sql_exec() are engine-routed, and sql_tables() introspects each engine's own catalog (sqlite_master / pg_catalog.pg_tables / information_schema.tables). To serve your Postgres, only the connection changes:

require db("postgres://localhost/appdb")     -- scope = canonical URL (no credentials)
db_open("postgres://user:pw@localhost:5432/appdb")

Everything else — whitelist, RLS, routes — is identical. (Mongo and Redis are different APIs, not SQL: for Mongo build the same pattern over mongo_collections() + mongo_find; see SQL, Mongo & Redis.) Note the pattern uses your application data (require db) — it does not touch the agent's declared memory (require memory), which is a separate plane (see Memory & state).

Honest warning: this is discipline, not enforcement. The pattern is airtight only while every read goes through table_rows — one route that queries the table directly is a leak. Keep the RLS gate in one task, review any new sql(...) in routes.

The same whitelist + RLS declaration also powers the MCP version of this API — see Your DB as MCP tools.