Indexes & nested data

Declare a secondary index

Creation and backfill validate against the actual collection before atomic publication. A failed unique build leaves no committed index. Named index metadata is returned by the collections endpoint.

{
  "requestId": "projects-workspace-status-index-v1",
  "operations": [
    {
      "op": "create_index",
      "collection": "projects",
      "name": "by_workspace_status",
      "fields": [
        "workspace",
        "status"
      ],
      "unique": false,
      "sparse": false
    }
  ]
}

Nested and expanded keys

Index paths can traverse objects and explicitly expand arrays, for example passkeys[*].credentialId. Repeated array values within one record count once. Compound expanded paths form independent Cartesian products and do not assert same-array-element matches.

Expansion is bounded to 4,096 traversal steps and keys per record/index. Current admission permits 64 named indexes per collection, 250,000 members per index and 4,000,000 members across indexes including constraints. Unlimited nesting or expansion is not claimed.

Choose uniqueness and null semantics

Nonunique index writes perform no uniqueness count. Changes to unique memberships are checked through the common delta path. Deleting a record removes its primary, secondary and TTL memberships atomically.

Use indexes without losing matches

Dense scalar composite indexes can serve leading equality prefixes and the next scalar range after a complete equality prefix. Sparse or expanded indexes cannot safely serve every prefix query because omitted records may still match. The planner retains a complete candidate path and verifies original predicates.

SELECT _key, score FROM scores
WHERE tenant = :tenant AND score >= :low AND score < :high
LIMIT 100

Expire records with a TTL policy

TTL timestamps are unsigned Unix seconds. Missing/null values do not expire. TTL is asynchronous deletion; records retain their unique values until deletion commits. Authorization must check expiry itself. Embedded hosts schedule db.expire(limit); standalone maintenance schedules bounded sweeps.

{
  "requestId": "sessions-expiry-v1",
  "operations": [
    {
      "op": "create_ttl_index",
      "collection": "sessions",
      "name": "expiry",
      "field": "expiresAtSec",
      "expireAfter": 0
    }
  ]
}