Appwrite Database + Appwrite Functions: Ensuring consistent pagination under concurrent writes

Direct Answers

1. Yes, implement manual keyset pagination. Order by a monotonic, indexed field (e.g., $createdAt) with $id as a tiebreaker, and use Query.greaterThan on the last seen key instead of Query.offset. This prevents concurrent inserts before the cursor from shifting the window.
2. Use the maximum page size of 100 documents. Fewer round trips reduce the time window for concurrent writes to interleave and lower total Function execution time. Smaller batches only add more consistency boundaries without improving correctness.
3. Design for idempotent processing, not strict server-side exactly-once. Appwrite Functions can be retried, and concurrent writes make true exactly-once delivery impractical. Record processed document IDs (or upsert by $id) so duplicates are harmless. If a true snapshot is required, capture a high-water mark (max $createdAt) once, then scan only documents at or below that mark.

Keyset Pagination Pattern

// Inside an Appwrite Function (Node.js Server SDK)
const PAGE_SIZE = 100;

// Persisted cursor: { createdAt: '...', id: 'doc123' }
let cursor = await loadCursor(); // from a state document

while (true) {
  const queries = [
    Query.limit(PAGE_SIZE),
    Query.orderAsc('$createdAt'),
    Query.orderAsc('$id')
  ];
  if (cursor) {
    queries.push(Query.greaterThan('$createdAt', cursor.createdAt));
    queries.push(Query.greaterThanEqual('$id', cursor.id));
  }

  const page = await databases.listDocuments(DB_ID, COLLECTION, queries);
  if (page.documents.length === 0) break;

  for (const doc of page.documents) {
    await processIdempotently(doc); // upsert by $id
  }

  const last = page.documents[page.documents.length - 1];
  cursor = { createdAt: last.$createdAt, id: last.$id };
  await saveCursor(cursor);
}

Why This Works

• Stable against inserts and deletes behind the cursor: new documents with a $createdAt earlier than the cursor are not re-scanned; deletes before the cursor simply shrink the remaining set.
• Tiebreaker is mandatory: $createdAt has limited resolution, and bulk writes often share the same timestamp. Adding $id as a secondary sort guarantees a total order and prevents skipped documents.
• Index requirement: the sort attributes should be covered by an Appwrite index. Create an index on the monotonic field (and any filter fields) before running large scans, since unindexed sorts on big collections can be slow or rejected.

Handling Duplicates and Gaps

Keyset pagination cannot provide a transactional snapshot. Documents updated to move ahead of the cursor, or inserted ahead of it during the scan, may be picked up or missed depending on timing. The pragmatic approach:

• Make per-document processing safe to run multiple times (upsert by $id in a target collection, or call an idempotent external API).
• If the business process demands a point-in-time view, fetch the current maximum $createdAt once before the loop, then add Query.lessThanEqual('$createdAt', maxCreatedAt) to every page query. Documents created after that mark are handled in the next run.

Verification Steps

1. Create a test collection with an index on the sort attributes.
2. Run the keyset-paginated Function while a separate script inserts, updates, and deletes documents concurrently.
3. Assert that the union of processed $id values equals the expected set with no duplicates (accounting for the high-water mark if used).
4. Confirm the deployed Appwrite server enforces the 100-document limit and that Query.greaterThan and orderAsc exist in the installed Server SDK version.
5. Simulate a Function timeout mid-scan and verify the persisted cursor allows resumption without reprocessing earlier batches.

One Diagnostic Detail Needed

What are the exact Appwrite server and Server SDK versions in your environment? The 100-document cap and the availability of specific Query helpers vary by version, and confirming them avoids runtime surprises.