#107 — Webhook Cursor Is a UUID String Comparison: Pagination Can Skip or Repeat Rows

[Topmatrixmor2014]

Labels: Official Campaign | FWC26 GrantFox OSS Maybe Rewarded backend webhooks api bug

This is a backend issue for the GrantFox FWC26 campaign. Replace the string cursor with a stable, sortable keyset so webhook-event pagination is exact.

Requirements and Context

backend/src/services/webhookService.js — getEvents:

if (cursor) query.andWhere("e.id", "<", cursor);
query.limit(limit);

• e.id is a UUIDv4; lexicographic < comparison on UUIDs is not chronological — rows with created_at ordering can be skipped or repeated across pages.
• The route GET /api/webhooks/:publicKey/events (in backend/src/routes/webhooks.js) additionally re-paginates in memory with (b.timestamp || 0) - (a.timestamp || 0) on top of the DB query, so two different orderings fight each other.
• Batch-3 bug(backend): federation lookup does not validate returned Stellar address format #80 covered getEvents cursor pagination for the events route; this issue is the service-layer getEvents/replayEvents cursor semantics — the DB-level cursor that feeds the route.

Objectives

1. Change the DB cursor to a monotonic column (created_at + id tiebreaker) or add a numeric seq column; make andWhere a keyset comparison.
2. Remove the redundant in-memory re-sort in the route so a single ordering is authoritative.
3. Add tests: two pages of events with equal timestamps produce no overlap and no gap.

Suggested Execution

1. Fork and branch: git checkout -b fix/webhook-events-keyset.
2. Patch getEvents in webhookService.js and the route in routes/webhooks.js.
3. Add keyset pagination tests; run npm run lint && npm test in backend/.

Acceptance Criteria

• Paginating webhook events returns each row exactly once across pages.
• Equal-timestamp rows are ordered deterministically.
• ≥3 tests; npm run lint clean.

Guidelines

• If a schema migration is needed, use the project's knex migration convention and keep the change backward-compatible.

Timeframe: 24 hours

[github-actions]

🤖 Greptile AI Context

Greptile has indexed this repository and can provide AI-assisted code review
and context for this issue.

How to use Greptile on this issue:

• Comment @greptileai to ask about relevant code, files, or implementation approaches
• Reference specific files or error messages for targeted analysis
• Use @greptileai explain <file> to get an explanation of any file

Greptile is configured with project-specific rules in .greptile/config.json.

[rexx010]

Hi, I’m interested in this issue. I’d replace the UUID cursor with deterministic keyset pagination using created_at plus an id tiebreaker, remove the conflicting in-memory sorting, and add tests covering equal timestamps, gaps, and duplicate events across pages.

[grantfox-oss]

🦊 GrantFox — @rexx010 has been assigned to this issue as part of the Third Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #888)
2. Your PR will be reviewed by the FinChippay maintainers

Good luck! Track your progress on GrantFox.