JSON tables: definition, reads, and queries

JSON tables are the persistence layer for Func: a table is one JSON Schema plus a list of records, the definition lives in a project file, and all reads and writes happen inside Func through ctx.db. It is deliberately not a general-purpose database — no joins, no aggregates, no transactions, no fuzzy matching — and it covers exactly the site-owned business data behind bookings, orders, signups, quotas, and logs.

Define a table

The table must exist before any write. Its definition is the project file /platform/table/<key>.json, and the file name is the table key — the same fact is not expressed twice, so the file itself has no key field.

// /platform/table/appointments.json
{
  "name": "Appointments",
  "desc": "Booked appointment slots",
  "json_schema": {
    "type": "object",
    "properties": {
      "startAt": { "type": "string", "format": "date-time" },
      "day": { "type": "string" },
      "userId": { "type": "string" },
      "service": { "type": "string" },
      "status": { "type": "string", "enum": ["booked", "cancelled"] },
      "note": { "type": "string" }
    },
    "required": ["startAt", "day", "userId", "service"]
  }
}

Exactly three top-level fields are accepted: name (required, the display name), desc (optional), and json_schema (required). Any additional key is rejected with the list of allowed names, so a camelCase typo such as jsonSchema surfaces at save time. The key itself must match ^[A-Za-z0-9][A-Za-z0-9_-]{0,62}$ and cannot live in a subdirectory.

This file is a writable projection, not ordinary site source. Reading it projects the stored table structure as JSON; writing it goes through the same save path as the console. Create and edit it with normal file tools — there is no separate table-schema tool.

What the schema does, and when it is checked

Read this before deciding how much validation your Func needs.

json_schema is therefore a structural declaration: it describes the record shape, drives console tables and forms, and tells the next reader (human or agent) what this table holds. Runtime data correctness is your Func's job — validate, trim, and normalize input before writing instead of expecting the platform to catch it.

Rules enforced when the definition is saved:

The shape of a record

What Func sees is the JSON you wrote plus an id:

ctx.db.insert('appointments', { day: '2026-08-02', service: 'haircut' })
// → { day: '2026-08-02', service: 'haircut', id: 'a1b2c3d4' }

• id is a platform-generated string key. get, update, and delete all address records by it. Do not declare a business field named id — the platform id overwrites it.
• created_at and updated_at are not returned to Func. If you need a creation time, store it as your own field. This is the easiest detail to miss.
• Records also carry a sort weight, set on insert to the current maximum plus 10. It is likewise absent from the return value, but it is the first key of the default ordering, so the default order is newest-inserted first.
• Do not give id business meaning. Order numbers and booking references that get displayed or shared belong in a field you generate yourself.

Writing: insert

// /backend/func/booking.ts
import type { TalizenFuncContext } from 'talizen/func-runtime'

export function create(input, ctx: TalizenFuncContext) {
  // The schema is not checked on write, so the checks live here
  const startAt = (input?.startAt ?? '').trim()
  if (!startAt) throw new Error('startAt is required')
  if (!['haircut', 'color'].includes(input?.service)) {
    throw new Error('unknown service')
  }

  const user = ctx.auth.requireUser()

  const inserted = ctx.db.insert('appointments', {
    startAt,
    day: startAt.slice(0, 10),
    userId: user.id,
    service: input.service,
    status: 'booked',
  })

  return { ok: true, id: inserted.id }
}

ctx.db.* is synchronous; writing await is harmless. The first argument is the table key, resolved inside the current project — Func code neither needs nor may assemble a project_id.

Call requireUser() first and store user.id as the ownership field. Ownership is a server decision: never accept a userId sent by the browser.

Reading: get and query

ctx.db.get(model, id) fetches one record and returns null when it does not exist — it does not throw.

export function detail(input, ctx) {
  const appointment = ctx.db.get('appointments', input.id)
  if (!appointment) return { ok: false, reason: 'not_found' }

  // Private data must check ownership yourself; get performs no access control
  const user = ctx.auth.requireUser()
  if (appointment.userId !== user.id) throw new Error('forbidden')

  return { ok: true, appointment }
}

ctx.db.query(model, query) returns { total, list, limit }. total is the number of matching records, so it drives page counts directly; list is the current page; limit is the effective page size — an oversized request is clamped silently, and comparing this field is how you notice.

where: equality on top-level fields

The simplest form. Multiple fields are AND-ed:

export function mine(input, ctx) {
  const user = ctx.auth.requireUser()

  return ctx.db.query('appointments', {
    where: { userId: user.id, status: 'booked' },
    limit: 20,
  })
}

Matching is JSON containment, which has two consequences worth remembering:

• Only top-level fields are addressable. Values nested inside an object cannot be filtered on — promote anything you filter by to the top level (that is exactly why day exists in the example above).
• Types must match exactly. { code: "1" } will not find a record that stored the number 1.
• When a field holds an array, equality means "the array contains this element", not "the array equals this value".

filter.conditions: not-equal and in

For "not equal" or "one of", use a condition array. There are exactly three operators: equal, not_equal, and in. Anything else returns 400 invalid operator.

export function board(input, ctx) {
  return ctx.db.query('appointments', {
    filter: {
      conditions: [
        { fieldId: 'status', operator: 'not_equal', value: 'cancelled' },
        { fieldId: 'service', operator: 'in', value: ['haircut', 'color'] },
      ],
    },
    limit: 50,
  })
}

Paging and ordering

The expression is comma separated, each term <field> or <field> asc|desc. System columns you can name directly are id, sort, user_id, created_at, and updated_at. Business fields require the body. prefix, for example body.startAt desc. Omitting it returns 400 with a "did you mean body.<field>" hint.

export function page(input, ctx) {
  const size = Math.min(Number(input.size) || 20, 100)
  const page = Math.max(Number(input.page) || 1, 1)

  const result = ctx.db.query('appointments', {
    where: { status: 'booked' },
    order_by: 'body.startAt asc, id desc',
    limit: size,
    offset: (page - 1) * size,
  })

  return { list: result.list, total: result.total, page, size }
}

When you need the whole set, take it one page at a time. Looping the entire table into memory hits both the execution timeout and the result-size limit.

Updating and deleting

ctx.db.update(model, id, data) is a shallow top-level merge, not a replacement. Keys present in data overwrite, keys absent are preserved, and a null value deletes that field. A nested object is replaced whole; it is not merged recursively.

export function cancel(input, ctx) {
  const user = ctx.auth.requireUser()
  const appointment = ctx.db.get('appointments', input.id)

  if (!appointment) return { ok: false, reason: 'not_found' }
  if (appointment.userId !== user.id) throw new Error('forbidden')
  if (appointment.status === 'cancelled') return { ok: true, already: true }

  // Only status changes; other fields stay. note: null removes the note
  ctx.db.update('appointments', input.id, { status: 'cancelled', note: null })
  return { ok: true }
}

Read, decide, then write. update and delete only take an id; they will not check who owns the record for you.

ctx.db throws strings, not Error objects. Inside catch (e), e.message is undefined — use String(e) to read the message.

There are no transactions and no cross-record atomic operations. A constraint such as "a slot can only be booked once" leaves a race between the read and the write; move the mutual exclusion to a single point such as ctx.cache.incr, or accept duplicates and reconcile them.

Managing tables and records from the CLI

Table structure can be edited as a file or through the CLI. Records are high-volume and are not exposed as files — manage them with the CLI or platform tools.

creght table list   --site_id=<project_id>/<site_id>
creght table get    --site_id=<project_id>/<site_id> --key=appointments
creght table create --site_id=<project_id>/<site_id> --key=appointments \
  --name=Appointments --schema=./schema.json

creght table record list   --site_id=<project_id>/<site_id> --table=appointments \
  --where=./where.json --limit=20 --order_by='body.startAt asc'
creght table record create --site_id=<project_id>/<site_id> --table=appointments --data=./record.json
creght table record get    --site_id=<project_id>/<site_id> --table=appointments --id=<record_id>
creght table record update --site_id=<project_id>/<site_id> --table=appointments --id=<record_id> --data=./patch.json
creght table record delete --site_id=<project_id>/<site_id> --table=appointments --id=<record_id>

--schema accepts either a bare JSON Schema or a full table definition file. After writing the Func, self-test it with sample input to confirm the table and the query behave:

creght func run --site_id=<project_id>/<site_id> --key=booking.create --input=./input.json

Constraints and modeling

Tables belong to the project, not to a site version

This one catches people out because it contradicts the intuition that everything is a site file:

• A table definition is project-level live state. Multiple sites in one project share the same tables, so a structural change affects all of them.
• Table definitions are not captured in site version snapshots. Publishing or reverting a site version moves site files only; nothing under /platform/ follows. Reverting code leaves the new schema in place — a combination that never existed.
• Treat a schema change as irreversible: add the field, keep writing both shapes, then change the code, then clean up. Do not rely on a rollback to save you.

Renaming and deletion are deliberately restricted

Modeling guidance

• Use the platform user.id as the ownership key. Never use an email as an identity key, and never create identity tables such as users or auth_users — accounts are a platform capability, see Sign In From a Func.
• Promote anything you filter on to a top-level field and keep its type stable across writes; structures used only for display can stay nested.
• Store your own timestamp field when you need one — the platform timestamps are not readable from Func.
• Store URLs and metadata for files. Never put base64 in a table, see Asset uploads.
• Short-lived state, counters, and rate limits belong in ctx.cache, not in a table.
• Equality filters are indexed; ordering by a business field is not, and every query also computes total. Size these tables like one site's business data, not like a log warehouse.

Acceptance checklist

• /platform/table/<key>.json exists and has only name, desc, and json_schema at the top level.
• Input validation lives in the Func; nothing assumes the schema will block bad data.
• Write paths call requireUser() and store user.id.
• get, update, and delete check ownership rather than acting on an id alone.
• Conditions use fieldId, ordering uses order_by, and business fields carry the body. prefix.
• List endpoints pass limit and offset and page through total instead of reading the whole table.
• No project_id, site_id, or table ID reaches a browser payload.