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

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

```json
{
  "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.

:::callout
**Every authorization denial here answers 404.** Missing record, invisible record, and "visible but you may not delete it" are indistinguishable by design. `403` is not returned on this endpoint.
:::

See [Soft Delete & Restore](/en/docs/records-soft-delete) 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.            |

:::callout
**`?format=raw` is rejected on a single-record read.** `GET /api/tables/:t/records/:id` accepts `display` only; any other value, `raw` included, returns `400 VALIDATION_ERROR`. Omit the parameter to get raw values. On the list endpoint `raw` is accepted and is a no-op.
:::

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](/en/docs/records-crud) — single-record writes and optimistic locking.
- [Soft Delete & Restore](/en/docs/records-soft-delete) — trash, restore, and cascades.
- [Batch Operations](/en/docs/records-batch) — bulk create, update, delete and restore.
- [Records Overview](/en/docs/records-overview) — envelope, authorship, cross-cutting rules.
- [File Lifecycle](/en/docs/file-lifecycle) — what `?purge=true` cleans up.