Skip to main content
View as Markdown

Records CRUD & Upsert

This page covers single-record lifecycle operations: create, read, update, delete, and upsert. For listing many records see Filtering, Sorting & Pagination; for bulk writes see Batch Operations. All write bodies use the canonical { "fields": { ... } } envelope described in the Records Overview.

Create a record

POST /api/tables/contacts/records
{
  "fields": {
    "email": "john@example.com",
    "first_name": "John",
    "last_name": "Doe"
  }
}
Status Meaning
201 Created Record created; body is the stored record with id and authorship
400 Bad Request Missing required field, invalid field-type value, or unique-constraint violation
401 Unauthorized No active session
404 Not Found Table does not exist (or caller may not access it)

The response carries the generated id, the fields echo, and the authorship metadata (createdBy, createdAt, updatedAt).

Read a record

GET /api/tables/contacts/records/42
Status Meaning
200 OK Record returned
401 Unauthorized No active session
404 Not Found Record absent or not visible to the caller (anti-enumeration)

Fields the caller lacks read permission for are omitted from the response — the same record can return a different field set depending on the caller's role and field-level permissions.

Update a record

PATCH performs a partial update: only the fields present in the body are written; omitted fields are left untouched.

PATCH /api/tables/contacts/records/42
{
  "fields": {
    "status": "active"
  }
}
Status Meaning
200 OK Record updated; updatedBy/updatedAt re-stamped
400 Bad Request Invalid field-type value or constraint violation
401 Unauthorized No active session
404 Not Found Record absent or not visible
409 Conflict Optimistic-lock failure (stale write)

Optimistic locking

Include a top-level updatedAt token alongside fields to guard against lost updates. The server compares it to the stored updated_at column and rejects a divergent write with 409 Conflict.

PATCH /api/tables/contacts/records/42
{
  "fields": { "status": "active" },
  "updatedAt": "2025-01-15T10:30:00Z"
}

Delete a record

By default DELETE is a soft delete: it sets deletedAt/deletedBy and leaves the row recoverable.

DELETE /api/tables/contacts/records/42
DELETE /api/tables/contacts/records/42?permanent=true
Status Meaning
200 OK / 204 No Content Record soft-deleted (or hard-deleted with ?permanent=true)
401 Unauthorized No active session
403/404 Caller lacks delete permission, or record not visible

Hard delete (?permanent=true) requires the permanentDelete permission and is irreversible. See Soft Delete & Restore for trash, restore, and cascade behavior on related records.

Upsert a record

Upsert creates a record or updates an existing one matched on one or more unique fields, in a single call. Use matchFields (alias: fieldsToMergeOn) to name the merge key(s).

POST /api/tables/contacts/records/upsert
{
  "fields": {
    "email": "john@example.com",
    "name": "John Doe",
    "status": "active"
  },
  "matchFields": ["email"]
}

The response reports whether the row was created or updated:

{
  "id": 123,
  "operation": "created",
  "record": {
    "id": 123,
    "email": "john@example.com",
    "name": "John Doe",
    "status": "active"
  }
}

When an existing row matches the matchFields, operation is "updated" instead and the matched row is patched. Upsert is ideal for idempotent synchronization from external systems — see Batch Operations for the multi-record upsert variant.

Display vs raw formatting

The format query parameter controls how field values are serialized on read endpoints.

format Behavior
raw (default) Stored values, unchanged — preferred for programmatic clients
display Human-readable values: formatted currency, dates, durations, attachment display names 
GET /api/tables/orders/records?format=display&timezone=Europe/Paris

Formatting is driven per field type:

Field type Display formatting
currency Symbol, decimal places, and locale grouping
datetime Configured date/time format; ?timezone= (IANA) overrides the rendering timezone
duration h:mm, h:mm:ss, or decimal hours per the field config
attachment / multiple-attachments URL plus metadata and display names
json Serialized per the field config and the format parameter