Databases
Keyset Pagination for a Changing CRM Activity Feed
By Vivek Jalondhara · 5 min read
Published
Page through CRM activity using a stable boundary, while making ordering, permissions, and changes between requests explicit.
A CRM activity feed keeps changing while someone reads it. New events arrive, old entries may be removed, and several actions can share a timestamp. A page number by itself does not describe which activity the reader has already seen.
This educational example is connected to SmackDab’s activity and audit domain. It proposes a feed contract and PostgreSQL query; it does not claim that the private CRM uses this pagination design.
Define the reading experience first
For a recent-activity feed, the useful action is usually to continue after the oldest event currently displayed. Keyset pagination expresses that boundary with the last row’s sort values. It avoids describing progress as the number of rows to skip, which can shift when earlier rows are inserted or deleted.
Offset pagination remains reasonable for a small, stable result set or an interface that truly requires numbered pages. The choice should follow the interaction. A support agent jumping directly to an arbitrary page has a different need from a person scrolling through a record’s history.
Choose a total order
The example sorts newest first by creation time and then event ID. The ID breaks ties when timestamps match. Both columns are non-null, and creation time stays unchanged after insertion. With both directions descending, the next-page predicate selects tuple values less than the last displayed tuple.
Do not silently reuse that predicate for a mixed ascending and descending order. Nullable sort values also require an explicit ordering and cursor rule. A status or last-edited sort can move an item across the cursor after it was shown, so prefer stable event fields for an append-oriented activity stream.
Treat a cursor as validated input
A cursor can contain its format version, timestamp, event ID, and a digest of the filter definition. Encode it for transport, then validate types, lengths, and allowed versions on every request. Base64 makes a token convenient to transmit; it does not make its contents trustworthy or confidential.
Bind the cursor to the search context so changing from all activity to one record does not continue from an unrelated position. Reapply tenant and record permissions from the authenticated session. A valid cursor must never become a substitute for those checks, even when its contents are signed.
Fetch one extra row
For a requested page size of twenty, fetch at most twenty-one authorized rows. Return the first twenty and use the extra row only to indicate that more results were visible to this query. Build the next cursor from the last returned row, not the extra row, or that extra event will be skipped.
Keep the cursor timestamp at the database’s precision. Converting a PostgreSQL timestamp through JavaScript Date can discard sub-millisecond precision. If the boundary moves because of serialization, rows sharing that fraction can be missed. Use a lossless driver representation or a deliberate timestamp precision in the schema and contract.
Explain what changes between pages
A cursor is a position, not a frozen snapshot. A newly inserted event that sorts before the boundary will not appear in the next older page. That is usually sensible for a feed; offer a refresh action for new activity. Deleting the cursor’s original row is harmless when the token contains its sort values.
For an export that must represent one consistent dataset, ordinary requests with cursors are insufficient. A transaction snapshot or a separately materialized export may be needed. Even an upper timestamp bound does not freeze later updates or prevent a newly inserted backdated event from entering the result. State the consistency promise before choosing storage mechanics.
Match the index and test the boundary
The suggested index starts with tenant identity, followed by the ordering columns. Evaluate it against the real authorization and filtering predicates. An additional selective record filter can justify a different index; adding every possible combination would make writes and maintenance more expensive.
Use fixtures with repeated timestamps, adjacent IDs, and two tenants. Page through them without duplicates or missing IDs, then delete the boundary row and repeat. Insert a newer event between requests and confirm the documented refresh behavior. Inspect the query plan for later pages; cursor syntax alone does not prove that a particular query avoids excessive scanning.
Continue after the last displayed event
Educational query for a tenant feed, assuming id and created_at are non-null and immutable. Supply the authorized tenant, exact cursor timestamp, and cursor ID as bound parameters. Apply any additional record permissions in the query.
Illustrative example · SQL · Simplified for explanation
CREATE INDEX activity_feed_order
ON activity_events (tenant_id, created_at DESC, id DESC);
-- Next page after the last returned row:
SELECT id, created_at, action
FROM activity_events
WHERE tenant_id = $1
AND (created_at, id) < ($2::timestamptz, $3::uuid)
ORDER BY created_at DESC, id DESC
LIMIT 21;
-- First page uses the same query without the tuple predicate.
-- Return 20 rows; build the next cursor from row 20.- Validate the cursor and filters
- Apply current permissions
- Seek after the ordering tuple
- Return the page and next boundary
Conceptual flow for the example above.
Tradeoff to keep in mind
This is sequential navigation through a changing feed. It does not provide arbitrary page jumps, a historical snapshot, or a complete permission policy.