Upsert & Delete

The two write operations that are not a plain create or update — merging on a key, and removing a row — plus the format parameter that decides how values come back on a read.

Upsert a record

Upsert creates records or updates existing ones matched on one or more unique fields, in a single call. It is the endpoint for idempotent synchronisation from an external system: replaying the same payload converges instead of duplicating.

There is one upsert endpoint and it is inherently multi-record — the body always carries a records array, even for a single row.

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

Property	Description
records	Required. Array of { fields } envelopes, 1–100 entries.
fieldsToMergeOn	Required. Field names forming the merge key, at least one. Alias: matchFields.
returnRecords	Return the affected rows in the response. Defaults to false.

The response reports counts, not a per-row verdict:

{
  "records": [],
  "created": 1,
  "updated": 0
}

records is populated only when returnRecords is true. A body shaped as a single { "fields": … } with no records array fails validation with 400 VALIDATION_ERROR.

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
DELETE /api/tables/contacts/records/42?purge=true

Mode	Behaviour	Success
Default (soft)	Trashes the row; recoverable via restore.	204, no body
?permanent=true	Hard-deletes the row. Admin only.	200
?purge=true	Deletes attached storage files, then hard-deletes the row.	204

?permanent=true is gated on the caller being an admin, and a non-admin receives 404 rather than 403 — the same anti-enumeration rule that applies to every other denial in the records path. ?purge=true is not admin-gated: it needs only the normal delete permission, and is the mode to use when the row owns uploaded files that should not be orphaned.

See Soft Delete & Restore for trash, restore, and cascade behaviour on related records.

Display vs raw formatting

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

format	Behaviour
(omitted)	Stored values, unchanged — the default, and what programmatic clients want.
display	Formatted fields become an object carrying both the raw and rendered value.

GET /api/tables/orders/records?format=display&timezone=Europe/Paris

Under format=display a formatted field wraps rather than replaces: the value becomes { value, displayValue, … }, so the raw form is still available for calculations. Only these field types are formatted; everything else is returned unchanged.

Field type	Display formatting
currency	Symbol, decimal places, and locale grouping.
date / datetime / time	Configured format; ?timezone= (IANA) overrides the rendering zone.
duration	h:mm, h:mm:ss, or decimal hours per the field config.
single-attachment / multiple-attachments	Declared upload constraints, when the field declares any.

Round-tripping is not supported: read with display, and write back the raw value, never the rendered one. Signed attachment URLs are added by a separate enrichment step and appear regardless of format.

Related pages

• Create, Read & Update — single-record writes and optimistic locking.
• Soft Delete & Restore — trash, restore, and cascades.
• Batch Operations — bulk create, update, delete and restore.
• Records Overview — envelope, authorship, cross-cutting rules.
• File Lifecycle — what ?purge=true cleans up.