> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/trailbaseio/trailbase/llms.txt
> Use this file to discover all available pages before exploring further.

# Records API

> CRUD operations for records including create, read, update, delete, list, filtering, and pagination

The Records API provides CRUD operations for your application's data tables through type-safe, configurable REST endpoints.

**Base Path**: `/api/records/v1`

## Create Record

Create a new record in the specified table.

```bash POST /api/records/v1/{name} theme={null}
curl -X POST https://your-instance.com/api/records/v1/posts \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "My First Post",
    "content": "Hello, world!",
    "status": "draft"
  }'
```

### Path Parameters

<ParamField path="name" type="string" required>
  Record API name (configured in your TrailBase setup)
</ParamField>

### Request Body

Single record as JSON object:

```json theme={null}
{
  "field1": "value1",
  "field2": 123,
  "field3": true
}
```

Or bulk create with array of records:

```json theme={null}
[
  {"field1": "value1"},
  {"field1": "value2"},
  {"field1": "value3"}
]
```

<Note>
  Bulk creation is limited to 1024 records per request. All records are created in a transaction - if any record fails, all are rolled back.
</Note>

### Query Parameters

<ParamField query="redirect_uri" type="string">
  Redirect URL after successful creation (for form submissions)
</ParamField>

### Response

```json theme={null}
{
  "ids": [
    "abc123def456",
    "xyz789uvw012"
  ]
}
```

<ResponseField name="ids" type="array">
  Array of base64-encoded record IDs for created records
</ResponseField>

### Auto-fill User ID Columns

If `autofill_missing_user_id_columns` is enabled in the API configuration, TrailBase automatically fills missing user ID foreign key columns with the authenticated user's ID:

```json theme={null}
// Request (no owner field)
{"title": "My Post", "content": "Hello"}

// Actual insert (owner auto-filled)
{"title": "My Post", "content": "Hello", "owner": "current_user_id"}
```

### Conflict Resolution

Configure conflict resolution strategy in the API settings:

* **None** (default): Fail on constraint violations
* **Replace**: Replace existing record with same primary key
* **Ignore**: Skip records that would violate constraints

## Read Record

Retrieve a single record by ID.

```bash GET /api/records/v1/{name}/{record} theme={null}
curl -X GET https://your-instance.com/api/records/v1/posts/abc123def456 \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"
```

### Path Parameters

<ParamField path="name" type="string" required>
  Record API name
</ParamField>

<ParamField path="record" type="string" required>
  Record ID (base64-encoded UUID or integer)
</ParamField>

### Query Parameters

<ParamField query="expand" type="string">
  Comma-separated list of foreign key columns to expand (e.g., `expand=author,category`)
</ParamField>

### Response

```json theme={null}
{
  "id": "abc123def456",
  "title": "My First Post",
  "content": "Hello, world!",
  "status": "draft",
  "created_at": "2026-03-07T12:00:00Z",
  "updated_at": "2026-03-07T12:00:00Z"
}
```

### Foreign Key Expansion

When using the `expand` parameter, foreign key references are replaced with full record data:

```bash theme={null}
curl "https://your-instance.com/api/records/v1/posts/abc123?expand=author"
```

```json theme={null}
{
  "id": "abc123",
  "title": "My Post",
  "author": {
    "id": "user123",
    "email": "author@example.com",
    "name": "John Doe"
  }
}
```

<Note>
  Expansion must be explicitly enabled in the API configuration for each foreign key column.
</Note>

## Update Record

Update an existing record.

```bash PATCH /api/records/v1/{name}/{record} theme={null}
curl -X PATCH https://your-instance.com/api/records/v1/posts/abc123def456 \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "title": "Updated Title",
    "status": "published"
  }'
```

### Path Parameters

<ParamField path="name" type="string" required>
  Record API name
</ParamField>

<ParamField path="record" type="string" required>
  Record ID to update
</ParamField>

### Request Body

Partial record update (only include fields to change):

```json theme={null}
{
  "field_to_update": "new_value"
}
```

### Response

<ResponseField name="200" type="OK">
  Record updated successfully (empty response body)
</ResponseField>

<Warning>
  Update operations only work on tables, not views. The API must be configured with `is_table = true`.
</Warning>

## Delete Record

Delete a record by ID.

```bash DELETE /api/records/v1/{name}/{record} theme={null}
curl -X DELETE https://your-instance.com/api/records/v1/posts/abc123def456 \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"
```

### Path Parameters

<ParamField path="name" type="string" required>
  Record API name
</ParamField>

<ParamField path="record" type="string" required>
  Record ID to delete
</ParamField>

### Response

<ResponseField name="200" type="OK">
  Record deleted successfully
</ResponseField>

### File Cleanup

If the record has file columns, associated files are marked for deletion and cleaned up asynchronously.

## List Records

Retrieve multiple records with filtering, sorting, and pagination.

```bash GET /api/records/v1/{name} theme={null}
curl -X GET "https://your-instance.com/api/records/v1/posts?limit=10&order=-created_at" \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"
```

### Path Parameters

<ParamField path="name" type="string" required>
  Record API name
</ParamField>

### Query Parameters

<ParamField query="limit" type="integer" default="50">
  Maximum number of records to return
</ParamField>

<ParamField query="offset" type="integer" default="0">
  Number of records to skip (alternative to cursor)
</ParamField>

<ParamField query="cursor" type="string">
  Encrypted cursor for pagination (returned in previous response)
</ParamField>

<ParamField query="count" type="boolean">
  Include total count of matching records (adds `total_count` to response)
</ParamField>

<ParamField query="order" type="string">
  Sort order. Prefix with `-` for descending (e.g., `-created_at` or `status,-created_at`)
</ParamField>

<ParamField query="expand" type="string">
  Comma-separated foreign keys to expand
</ParamField>

### Response

```json theme={null}
{
  "records": [
    {
      "id": "abc123",
      "title": "Post 1",
      "status": "published"
    },
    {
      "id": "def456",
      "title": "Post 2",
      "status": "draft"
    }
  ],
  "cursor": "encrypted_cursor_string",
  "total_count": 42
}
```

<ResponseField name="records" type="array">
  Array of record objects matching the query
</ResponseField>

<ResponseField name="cursor" type="string">
  Encrypted cursor for fetching the next page (omitted if no more pages)
</ResponseField>

<ResponseField name="total_count" type="integer">
  Total number of matching records (only if `count=true`)
</ResponseField>

## Filtering

Filter records using query parameters with `filter[field]=value` syntax.

### Simple Filters

```bash theme={null}
# Exact match
curl "https://your-instance.com/api/records/v1/posts?filter[status]=published"

# Multiple conditions (AND)
curl "https://your-instance.com/api/records/v1/posts?filter[status]=published&filter[author]=john"
```

### Comparison Operators

```bash theme={null}
# Greater than
filter[age][$gt]=18

# Greater than or equal
filter[age][$gte]=18

# Less than
filter[age][$lt]=65

# Less than or equal
filter[age][$lte]=65

# Not equal
filter[status][$ne]=deleted
```

### Pattern Matching

```bash theme={null}
# Contains (case-insensitive)
filter[title][$like]=%hello%

# Starts with
filter[email][$like]=admin%

# Ends with
filter[domain][$like]=%.com
```

### Multiple Values (IN)

```bash theme={null}
# Any of these values
filter[status][$in][0]=draft&filter[status][$in][1]=published&filter[status][$in][2]=archived
```

### Null Checks

```bash theme={null}
# Is null
filter[deleted_at][$null]=true

# Is not null
filter[deleted_at][$null]=false
```

### Complex Filter Example

```bash theme={null}
curl "https://your-instance.com/api/records/v1/posts?filter[status]=published&filter[created_at][$gte]=2026-01-01&filter[author][$in][0]=john&filter[author][$in][1]=jane&order=-created_at&limit=20"
```

<Note>
  Filters are validated against the table schema - unknown columns are silently ignored to prevent SQL injection.
</Note>

## Pagination Strategies

### Cursor-Based Pagination (Recommended)

Use encrypted cursors for consistent pagination:

```bash theme={null}
# First page
curl "https://your-instance.com/api/records/v1/posts?limit=10"

# Next page (use cursor from response)
curl "https://your-instance.com/api/records/v1/posts?limit=10&cursor=abc123..."
```

**Advantages:**

* Consistent results even with concurrent modifications
* Better performance for deep pagination
* Encrypted to prevent tampering

### Offset-Based Pagination

Use offset for simpler pagination:

```bash theme={null}
# First page (records 0-9)
curl "https://your-instance.com/api/records/v1/posts?limit=10&offset=0"

# Second page (records 10-19)
curl "https://your-instance.com/api/records/v1/posts?limit=10&offset=10"
```

**Disadvantages:**

* Results may shift if records are added/deleted
* Slower performance for large offsets

## GeoJSON Support

For tables with geometry columns, fetch results as GeoJSON:

```bash GET /api/records/v1/{name}?geojson={column} theme={null}
curl "https://your-instance.com/api/records/v1/locations?geojson=geometry" \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"
```

### Response

```json theme={null}
{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [-122.4194, 37.7749]
      },
      "properties": {
        "id": "abc123",
        "name": "San Francisco"
      }
    }
  ]
}
```

## Access Control

Record APIs support both table-level and row-level access control.

### Table-Level ACLs

Configure in API settings:

* `acl_world`: Permissions for unauthenticated users
* `acl_authenticated`: Permissions for authenticated users

Permissions: `Create`, `Read`, `Update`, `Delete`, `Schema`

### Row-Level Access Rules

Define SQL expressions for fine-grained access control:

```sql theme={null}
-- Only record owners can update
update_access_rule: "_ROW_.owner = _USER_.id"

-- Only members of the same team can read
read_access_rule: "EXISTS(SELECT 1 FROM team_members WHERE team = _ROW_.team_id AND user = _USER_.id)"

-- Anyone can create if authenticated
create_access_rule: "_USER_.id IS NOT NULL"
```

**Available Variables:**

* `_USER_.id`: Current user's UUID (NULL if not authenticated)
* `_ROW_`: The record being accessed (read, update, delete)
* `_REQ_`: The request data (create, update)
* `__fields`: Array of field names in the request (JSON string)

### Access Control Example

```bash theme={null}
# Fails: User not authorized
curl -X DELETE https://your-instance.com/api/records/v1/posts/abc123 \
  -H "Authorization: Bearer USER_TOKEN"
# Response: 403 Forbidden

# Success: Owner can delete
curl -X DELETE https://your-instance.com/api/records/v1/posts/abc123 \
  -H "Authorization: Bearer OWNER_TOKEN"
# Response: 200 OK
```

## JSON Schema Validation

Retrieve the JSON schema for a record API:

```bash GET /api/records/v1/{name}/schema theme={null}
curl -X GET https://your-instance.com/api/records/v1/posts/schema \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"
```

### Response

```json theme={null}
{
  "type": "object",
  "properties": {
    "id": {"type": "string"},
    "title": {"type": "string", "maxLength": 255},
    "content": {"type": "string"},
    "status": {"enum": ["draft", "published", "archived"]},
    "created_at": {"type": "string", "format": "date-time"}
  },
  "required": ["title", "content"]
}
```

## File Columns

See the [Files API](/api/files) documentation for working with file uploads in records.

## Transaction API

For atomic multi-record operations:

```bash POST /api/transaction/v1/execute theme={null}
curl -X POST https://your-instance.com/api/transaction/v1/execute \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "operations": [
      {"type": "create", "api": "posts", "data": {...}},
      {"type": "update", "api": "users", "id": "abc123", "data": {...}},
      {"type": "delete", "api": "comments", "id": "def456"}
    ]
  }'
```

All operations execute atomically - if any fails, all are rolled back.

## Best Practices

1. **Use cursor pagination** for consistent results with concurrent modifications
2. **Request only needed fields** using projection (if supported)
3. **Filter on indexed columns** for better query performance
4. **Limit list queries** to avoid performance issues with large result sets
5. **Use bulk create** for inserting multiple records (up to 1024 per request)
6. **Enable row-level access** for multi-tenant applications
7. **Validate data client-side** before submission to reduce API round trips
8. **Cache JSON schemas** to avoid repeated schema fetches

## Error Responses

<ResponseField name="400" type="Bad Request">
  Invalid parameters, malformed JSON, or constraint violation
</ResponseField>

<ResponseField name="403" type="Forbidden">
  Access denied by ACL or row-level access rule
</ResponseField>

<ResponseField name="404" type="Not Found">
  Record or API not found
</ResponseField>

<ResponseField name="405" type="Method Not Allowed">
  API not found or operation not supported (e.g., update on view)
</ResponseField>