# Records CRUD & Upsert

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

## Create a record

```json
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](records-overview) (`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](table-permissions).

## Update a record

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

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

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

:::callout
**Tables opt in via the `updated-at` system field.** Add a field of `type: 'updated-at'` so the column auto-bumps on every write. A plain `datetime` field is user-editable and is **not** auto-managed, so it cannot back the optimistic-lock contract.
:::

## 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](records-soft-delete) 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).

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

```json
{
  "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](records-batch) 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                        |

:::callout
**Raw is the source of truth.** `format=display` is a presentation convenience layered on top of the stored value. Round-tripping (read with `display`, write back) is not guaranteed — always write the raw value. Numeric and date fields keep their raw form available for calculations.
:::

## Related pages

- [Records Overview](records-overview) — envelope, authorship, cross-cutting rules
- [Filtering, Sorting & Pagination](records-filtering-sorting) — list query grammar
- [Batch Operations](records-batch) — bulk create/update/delete/upsert
- [Soft Delete & Restore](records-soft-delete) — trash and recovery
- [Table Permissions](table-permissions) — RBAC and field-level access