Per-user Data APIs

Trust model summary. PickLayer’s E2EE story spans three storage classes with distinct guarantees:

• Files (Files API, Encryption) — end-to-end encrypted when uploaded with a per-file file_key. Per-file keys are derived as HKDF-SHA256(UMK, "picklayer:file:" + file_id). The UMK never leaves the client. Per-request keys travel only when an operation needs server-side decryption (file download, chat reference) and are redacted from logs.
• Stored responses (Responses API) — end-to-end encrypted when encrypt_content: true. Sealed-envelope V2 wraps the content DEK against the user’s X25519 public key. Operational metadata (model, usage, timestamps, request metadata field) remains plaintext on the server.
• Per-user data tables (this page) — not end-to-end encrypted. Stored as plaintext jsonb in Postgres for query, indexing, and RLS enforcement. Suitable for preferences, app state, leaderboards, and other non-secret structured data. Do not put unencrypted secrets, raw API keys, or sensitive PII here.

The UMK never leaves the client; file_keys maps on chat requests are request-scoped and never persisted (logger redaction enforced).

The datastore now supports multiple “tables” per app, each with its own storage profile:

• single mode – one JSON blob per user (ideal for preferences or profile state). Slug default is provisioned automatically.
• collection mode – many documents per user (leaderboards, saved objects, etc.). Each row has a caller-controlled documentId (falls back to a UUID).
• app-wide mode – shared collections that aren’t tied to a specific user. Builders can restrict read access to specific billing plans via plan_access.read. App-wide tables are read-only from the end-user API; writes happen via the Builder UI or server-side services.

All writes are soft-limited to 5 MB per document. We still store JSON in Postgres jsonb (max 1GB), but anything above 5 MB is rejected so that we don’t incur runaway RDS I/O or cache misses. Every call records bytes/rows via the datastore-usage.record-io service so billing can pass RDS costs (plus markup) back to the app.

REST endpoints

Responses include id, tableSlug, documentId, name (alias for documentId), data, createdAt, and updatedAt. nextCursor is a base64 blob containing {created_at,id}; pass it back to paginate collection/app-wide tables. Soft deletes aren’t supported—removing a row is permanent.

Row-Level Security still guards user-owned rows (user_id = X-Hasura-User-Id). App-wide queries run through the API service, which enforces plan gating before issuing a Hasura call, so clients can’t bypass it.

Field projection

The GET /data/items endpoint supports three query parameters for controlling which fields are returned in each item’s data object. This is useful for reducing payload size when you only need specific fields from large documents.

Rules:

• fields and exclude are mutually exclusive — sending both returns a 400 error.
• metadata_only=true takes precedence over fields/exclude if combined.
• When none of these params are provided, all fields are returned (default behavior).

Examples

# Return only title and author fields
GET /data/items?table=articles&fields=title,author
 
# Return everything except the large "body" field
GET /data/items?table=articles&exclude=body
 
# List items without payloads (fastest)
GET /data/items?table=articles&metadata_only=true

Querying with field filters

Use POST /v1/data/items/query to load items matching arbitrary field-level filters. This is more efficient than fetching all items and filtering client-side when your table has many records.

Request body

The query endpoint has a 120-second timeout (vs 30 seconds for other data endpoints) to accommodate large filtered result sets that may require multiple paginated requests.

Filter syntax

Filter values can be:

• A primitive (string, number, boolean, or null) — equality match
• An operator object — { "$gte": 5 } for comparisons

A single key can combine multiple operators: { "score": { "$gte": 10, "$lte": 100 } }.

Filter keys must match ^[a-zA-Z0-9_]+$. Maximum 5 filter fields per request.

Examples

# Equality filter — all chat messages for a specific chat
curl -X POST https://api.picklayer.com/v1/data/items/query \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"table": "chat-messages", "filter": {"chat_id": "abc-123"}, "sort": "asc", "limit": 50}'
 
# Comparison filter — paginate messages after index 5
curl -X POST https://api.picklayer.com/v1/data/items/query \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"table": "chat-messages", "filter": {"chat_id": "abc-123", "message_index": {"$gte": 5}}, "sort": "asc"}'

# Python SDK
resp = client.query_items(
    table="chat-messages",
    filter={"chat_id": "abc-123"},
    sort="asc",
    limit=50,
)
items = resp.data.get("items", [])
next_cursor = resp.data.get("nextCursor")
 
# Range filter
resp = client.query_items(
    table="leaderboard",
    filter={"score": {"$gte": 1000, "$lte": 9999}},
    sort="desc",
)

The response shape is identical to GET /v1/data/items: { items, nextCursor }.

Pagination

Results are paginated using cursor-based pagination. Each response includes a nextCursor value — pass it back in the next request along with the same filter, sort, and limit to fetch the next page:

# Paginate through all messages for a chat
all_items = []
cursor = None
while True:
    resp = client.query_items(
        table="chat-messages",
        filter={"chat_id": "abc-123"},
        sort="asc",
        limit=100,
        cursor=cursor,
    )
    all_items.extend(resp.data.get("items", []))
    cursor = resp.data.get("nextCursor")
    if not cursor:
        break

Each page query is efficient — the cursor tells Postgres exactly where to resume, so it doesn’t re-scan previous pages. For a 200-message chat, this takes 2 requests (100 + 100) instead of 200 individual fetches.

The filter is stateless — it must be included in every page request. The server does not retain filter state between requests.

Bulk deleting items

POST /data/items/bulk-delete removes multiple items in a single request. There are two modes:

• By IDs — pass an explicit list of row IDs. Up to 100 IDs per request.
• By filter — pass a field-level filter (same syntax as the query endpoint). Up to 10,000 matching items are deleted.

In both modes, only items owned by the authenticated user are affected. Associated app_user_data_fields rows are cascade-deleted automatically.

Request body

* Exactly one of ids or filter must be provided.

Examples

# Delete by ID list
curl -X POST https://api.picklayer.com/v1/data/items/bulk-delete \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"table": "chat-messages", "ids": ["id-1", "id-2", "id-3"]}'
 
# Delete by filter — remove all messages for a specific chat
curl -X POST https://api.picklayer.com/v1/data/items/bulk-delete \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"table": "chat-messages", "filter": {"chat_id": "abc-123"}}'

# Python SDK — delete by IDs
client.bulk_delete_items(table="chat-messages", ids=["id-1", "id-2", "id-3"])
 
# Python SDK — delete by filter
client.bulk_delete_items(table="chat-messages", filter={"chat_id": "abc-123"})

Response

A 200 response means all targeted items were deleted successfully:

{ "deleted": 3 }

A 207 response means the operation partially succeeded — some items were deleted and some were not. Each failure includes the item ID and a reason:

{
  "deleted": 2,
  "failed": [
    { "id": "id-3", "reason": "not found" }
  ]
}

Constraints

• Max 100 IDs per request when using ids mode.
• Max 10,000 items deleted per request when using filter mode. If the filter matches more than 10,000 items, the request is rejected with a 400 error — paginate your deletes instead.
• Deletes are user-scoped: rows belonging to other users are silently skipped.
• app_user_data_fields rows associated with deleted items are cascade-deleted automatically.
• Deletions are permanent — soft deletes are not supported.