# JSON Tables in Func | Creght API for AI

> Define and use JSON tables with ctx.db in Creght Func: the /platform/table file format, when schemas are validated, query where/filter operators, paging and ordering rules, shallow-merge updates, and the common traps.

[![Creght](https://ugc.talizen.com/_assets/site/2061660904709165056/1780797461299__creght_logo.png)API for AI](/)

[View llms.txt](/llms.txt)

Overview

- [Creght API for AI](/api.md)

Discoverability

- [How to optimize llms.txt](/api/optimize-llms-txt.md)

Site configuration

- [Implement domain-based locale routing](/api/domain-locale-routing.md)

Backend

- [Calling external APIs on the server and managing the cache](/api/ssr-external-api-cache.md)
- [Build site backend workflows with Func](/api/func-backend.md)
- [JSON tables: definition, reads, and queries](/api/func-json-tables.md)
- [Uploads: signed direct upload and Func-generated files](/api/func-assets-upload.md)
- [Timeouts and streaming responses](/api/func-timeout-streaming.md)
- [Integrate Alipay PC Web Payment with Func](/api/func-alipay-payment.md)

Integrations

- [Send Email and Verification Codes with Integrations](/api/func-email-integration.md)
- [Take Alipay payments with an integration](/api/func-alipay-integration.md)

Auth

- [Require a Verified Email to Sign Up](/api/auth-verified-registration.md)
- [Reset and Change Passwords](/api/auth-password-reset.md)
- [Sign In From a Func](/api/auth-func-login.md)
- [Query users from a Func](/api/func-user-directory.md)

On this page

- [Define a table](#define)
- [What the schema does, and when it is checked](#schema-rules)
- [Saving the definition: strictly validated](#saving-the-definition-strictly-validated)
- [Writing a record: not validated](#writing-a-record-not-validated)
- [The shape of a record](#record-shape)
- [Writing: insert](#insert)
- [Reading: get and query](#read)
- [where: equality on top-level fields](#where)
- [filter.conditions: not-equal and in](#filter)
- [Paging and ordering](#paging)
- [Updating and deleting](#update-delete)
- [Managing tables and records from the CLI](#cli)
- [Constraints and modeling](#constraints)
- [Tables belong to the project, not to a site version](#scope)
- [Renaming and deletion are deliberately restricted](#lifecycle)
- [Renaming is refused](#renaming-is-refused)
- [A table with records cannot be deleted](#a-table-with-records-cannot-be-deleted)
- [Modeling guidance](#modeling)
- [Acceptance checklist](#checklist)

Backend/JSON tables: definition, reads, and queries

# JSON tables: definition, reads, and queries

The full persistence layer for Func: the table definition file and what is validated when, the real shape of a record, the where/filter operators and the ones that fail silently, paging and ordering limits, merge-style updates, and the boundary people miss — tables belong to the project, not to a site version.

Copy Markdown link

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.

**Agent objective**

Declare the table in `/platform/table/<key>.json`, then read and write it with `ctx.db`. Table keys are literals in Func source, record ownership uses the platform `user.id`, and table IDs or `project_id` never appear in code or browser payloads.

## 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.

```json
// /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.

### Saving the definition: strictly validated

A malformed schema is rejected with 400 at save time, and the message carries line and column numbers you can fix directly.

### Writing a record: not validated

`ctx.db.insert` and `update` do **not** check records against the schema. Undeclared fields are stored, and a missing `required` field raises no error.

> `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:

| Rule | On violation |
| --- | --- |
| `type` must be `object` | `"json_schema".type must be "object"` |
| `properties` is required and non-empty; every value must be an object | `"json_schema".properties is empty; declare at least one field` |
| `required` may only name declared properties | The error lists the declared field names — this is the most common mistake |
| `"format": "file"` is not supported | Use `{ "type": "string", "format": "uri", "contentMediaType": "image/*" }` and store the [uploaded URL](/api/func-assets-upload.md) |
| JSON syntax errors | Reported with **line and column** |

## The shape of a record

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

```typescript
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

```typescript
// /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.

```typescript
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:

```typescript
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`.

```typescript
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,
  })
}
```

| Trap | Behaviour |
| --- | --- |
| The field key must be `fieldId` | Writing `field_id` raises no error — the condition is **silently dropped** and the query quietly matches more rows |
| Conditions are always AND-ed | `filter.match: "or"` is accepted and **ignored**. For OR, run two queries and merge |
| `in` with an empty array | No error; matches zero records |
| Range comparison, LIKE, full-text search | **None are supported.** For date ranges, store a field that can be matched by equality; for search, use an external search service rather than pulling the table into Func |

### Paging and ordering

| Parameter | Meaning |
| --- | --- |
| `limit` | Page size. **Default 20, maximum 1000**, clamped silently; the returned `limit` is what actually applied |
| `offset` | Offset for paging. There is no `page` parameter |
| `order_by` | Order expression, default `sort desc, id desc`. **The underscore spelling is required** — `orderBy` is silently ignored |

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.

```typescript
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.

```typescript
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 }
}
```

| Method | Returns | When the record is missing |
| --- | --- | --- |
| `get` | The record or `null` | Returns `null`, no error |
| `insert` | The written object plus `id` | — |
| `update` | `{ ok: true }`, **never the updated record** | Throws (404 record not found) |
| `delete` | `{ ok: true }` | **No error**; still returns ok |

**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.

```bash
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:

```bash
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

### Renaming is refused

The file name is the key, and Func code references it as a literal, so a rename would silently change backend behaviour. To rename: create the new table, move the data deliberately, then remove the old one.

### A table with records cannot be deleted

An empty table can be removed; a populated one is refused with its record count, so deleting a file cannot take a table's data with it. Delete the records first, or use the console.

### 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](/api/auth-func-login.md).
- 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](/api/func-assets-upload.md).
- 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.

**Completion criteria**

The table structure is readable and diffable as a file, input validation is written explicitly in Func, queries use the index and return one page at a time, and every read and write can answer "who owns this record, and why is this caller allowed to touch it".

![Creght](https://ugc.talizen.com/_assets/site/2061660904709165056/1780797461299__creght_logo.png)

This website is built with [Creght](/)

[Discord](https://discord.gg/Qvq2nmZNnb)

## Links

- [Pricing](/price.md)
- [Solutions](/solution.md)
- [Customers](/customers.md)
- [Help Center](/help.md)
- [Contact Us](/contact.md)
- [Update Logs & Blogs](/blogs.md)
- [Refund Policy](/tuikuan.md)

## Resources

- [All Resources](/resources.md)
- [Templates](/templates.md)
- [Components](https://creghtlib.site.creght.com)
- [Animations](/design/effects.md)
- [Figma to Creght](/figma2creght.md)
- [API for AI](/api.md)

## Terms

- [Terms of Service](/legal/terms.md)
- [Privacy Policy](/legal/privacy.md)
- [Acceptable Use Policy](/legal/acceptable-use.md)

## Social Media

- [Twitter](https://twitter.com)
- [Facebook](https://www.facebook.com)
- [LinkedIn](https://www.linkedin.com)

[蜀ICP备2023038192号-2](https://beian.miit.gov.cn)