Records API - TrailBase

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.

POST /api/records/v1/{name}

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

string

required

Record API name (configured in your TrailBase setup)

Request Body

Single record as JSON object:

{
  "field1": "value1",
  "field2": 123,
  "field3": true
}

Or bulk create with array of records:

[
  {"field1": "value1"},
  {"field1": "value2"},
  {"field1": "value3"}
]

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

Query Parameters

string

Redirect URL after successful creation (for form submissions)

Response

{
  "ids": [
    "abc123def456",
    "xyz789uvw012"
  ]
}

array

Array of base64-encoded record IDs for created records

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:

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

GET /api/records/v1/{name}/{record}

curl -X GET https://your-instance.com/api/records/v1/posts/abc123def456 \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"

Path Parameters

string

required

Record API name

string

required

Record ID (base64-encoded UUID or integer)

Query Parameters

string

Comma-separated list of foreign key columns to expand (e.g., expand=author,category)

Response

{
  "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:

curl "https://your-instance.com/api/records/v1/posts/abc123?expand=author"

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

Expansion must be explicitly enabled in the API configuration for each foreign key column.

Update Record

Update an existing record.

PATCH /api/records/v1/{name}/{record}

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

string

required

Record API name

string

required

Record ID to update

Request Body

Partial record update (only include fields to change):

{
  "field_to_update": "new_value"
}

Response

Record updated successfully (empty response body)

Update operations only work on tables, not views. The API must be configured with is_table = true.

Delete Record

Delete a record by ID.

DELETE /api/records/v1/{name}/{record}

curl -X DELETE https://your-instance.com/api/records/v1/posts/abc123def456 \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"

Path Parameters

string

required

Record API name

string

required

Record ID to delete

Response

Record deleted successfully

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.

GET /api/records/v1/{name}

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

Path Parameters

string

required

Record API name

Query Parameters

integer

default:"50"

Maximum number of records to return

integer

default:"0"

Number of records to skip (alternative to cursor)

string

Encrypted cursor for pagination (returned in previous response)

boolean

Include total count of matching records (adds total_count to response)

string

Sort order. Prefix with - for descending (e.g., -created_at or status,-created_at)

string

Comma-separated foreign keys to expand

Response

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

array

Array of record objects matching the query

string

Encrypted cursor for fetching the next page (omitted if no more pages)

integer

Total number of matching records (only if count=true)

Filtering

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

Simple Filters

# 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

# 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

# Contains (case-insensitive)
filter[title][$like]=%hello%

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

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

Multiple Values (IN)

# Any of these values
filter[status][$in][0]=draft&filter[status][$in][1]=published&filter[status][$in][2]=archived

Null Checks

# Is null
filter[deleted_at][$null]=true

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

Complex Filter Example

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"

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

Pagination Strategies

Cursor-Based Pagination (Recommended)

Use encrypted cursors for consistent pagination:

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

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

GET /api/records/v1/{name}?geojson={column}

curl "https://your-instance.com/api/records/v1/locations?geojson=geometry" \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"

Response

{
  "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:

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

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

GET /api/records/v1/{name}/schema

curl -X GET https://your-instance.com/api/records/v1/posts/schema \
  -H "Authorization: Bearer YOUR_AUTH_TOKEN"

Response

{
  "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 documentation for working with file uploads in records.

Transaction API

For atomic multi-record operations:

POST /api/transaction/v1/execute

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

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

Error Responses

Bad Request

Invalid parameters, malformed JSON, or constraint violation

Forbidden

Access denied by ACL or row-level access rule

Not Found

Record or API not found

Method Not Allowed

API not found or operation not supported (e.g., update on view)

​Create Record

​Path Parameters

​Request Body

​Query Parameters

​Response

​Auto-fill User ID Columns

​Conflict Resolution

​Read Record

​Path Parameters

​Query Parameters

​Response

​Foreign Key Expansion

​Update Record

​Path Parameters

​Request Body

​Response

​Delete Record

​Path Parameters

​Response

​File Cleanup

​List Records

​Path Parameters

​Query Parameters

​Response

​Filtering

​Simple Filters

​Comparison Operators

​Pattern Matching

​Multiple Values (IN)

​Null Checks

​Complex Filter Example

​Pagination Strategies

​Cursor-Based Pagination (Recommended)

​Offset-Based Pagination

​GeoJSON Support

​Response

​Access Control

​Table-Level ACLs

​Row-Level Access Rules

​Access Control Example

​JSON Schema Validation

​Response

​File Columns

​Transaction API

​Best Practices

​Error Responses

Create Record

Path Parameters

Request Body

Query Parameters

Response

Auto-fill User ID Columns

Conflict Resolution

Read Record

Path Parameters

Query Parameters

Response

Foreign Key Expansion

Update Record

Path Parameters

Request Body

Response

Delete Record

Path Parameters

Response

File Cleanup

List Records

Path Parameters

Query Parameters

Response

Filtering

Simple Filters

Comparison Operators

Pattern Matching

Multiple Values (IN)

Null Checks

Complex Filter Example

Pagination Strategies

Cursor-Based Pagination (Recommended)

Offset-Based Pagination

GeoJSON Support

Response

Access Control

Table-Level ACLs

Row-Level Access Rules

Access Control Example

JSON Schema Validation

Response

File Columns

Transaction API

Best Practices

Error Responses