Fix broken cursor pagination for word_similarity relevance search — floating-point score roundtrip corrupts cursor position

[grantfox-oss]

Difficulty

10/10 — Expert. Estimated effort: 3–5 days for a senior engineer.

Context

SearchService.searchCampaignsByRelevance() and searchDonationsByRelevance() in
src/services/search.service.ts implement keyset (cursor) pagination for relevance-ranked
search results. Cursor pagination is a correctness requirement for this endpoint: offset-based
pagination over a relevance-ranked result set is non-deterministic when new campaigns are created
or scores change between pages, so the API documented a stable cursor.

The cursor encodes a (score, id) pair that the next page's SQL WHERE clause uses to resume
scanning. However, the score value stored in the cursor undergoes a destructive roundtrip that
makes the cursor comparison wrong for scores with more than 6–7 significant decimal digits, which
is the normal output of word_similarity(). This causes pages to overlap or skip rows silently.

Problem statement

The cursor is encoded by encodeCursor(score: number, id: string):

function encodeCursor(score: number, id: string): string {
  return Buffer.from(JSON.stringify({ score, id })).toString('base64');
}

And decoded by decodeCursor(cursor: string): CursorData:

function decodeCursor(cursor: string): CursorData {
  const decoded = Buffer.from(cursor, 'base64').toString('utf-8');
  return JSON.parse(decoded) as CursorData;
}

The SQL query that generates the scores:

SELECT id, GREATEST(
  word_similarity($query, title),
  word_similarity($query, description)
) AS score
FROM "Campaign"
WHERE ... ORDER BY score DESC, id DESC

word_similarity() returns a PostgreSQL float4 (single-precision, 32-bit). When Prisma
deserializes this via $queryRaw, the float4 is decoded to a JavaScript number (IEEE 754
64-bit double) by the pg wire protocol: the conversion is float4 → float8 → JS number. The
round-trip from a 32-bit float through a 64-bit representation introduces trailing digits in the
decimal expansion. For example, word_similarity('relief', 'relief fund') may return the
float4 value 0.5 which becomes the float8 value 0.5000001192092896.

The cursor stores this value as a JSON number (0.5000001192092896), and the next page's SQL
sends it back as a numeric literal:

AND (score, id) < (0.5000001192092896, 'clm...')

PostgreSQL receives this as numeric (arbitrary precision), not float4. The comparison
float4_column < numeric_literal promotes both sides to numeric. The float4 score column
value 0.5 promotes to 0.5 exactly in numeric, but the cursor value 0.5000001192092896 is
slightly larger — so the comparison 0.5 < 0.5000001192092896 is true, meaning rows that
should be excluded (already returned on the previous page) leak onto the next page.

The inverse failure also occurs: if the floating-point representation rounds down
(0.4999998807907104), rows that should be on the next page are skipped.

The severity is not just cosmetic: on campaigns with many similar titles (humanitarian platforms
often have many "Emergency Food Relief" campaigns), large blocks of rows share the same rounded
word_similarity score. The cursor must use the id tiebreaker to distinguish position within
a score block. When the score comparison fails, the id tiebreaker is never reached, and the
entire block is either duplicated or skipped.

There is a second independent bug: the cursor condition is interpolated as:

cursorCondition = Prisma.sql` AND (score, id) < (${lastScore}, ${lastId})`;

The score is used in a tuple comparison (score, id) < (lastScore, lastId). PostgreSQL's row
comparison (a, b) < (x, y) is a < x OR (a = x AND b < y). But score is a computed
expression in the outer query (an alias), not a column name. PostgreSQL requires the alias to
be wrapped in a subquery or repeated inline for the WHERE clause — using the alias name score
directly in WHERE is valid in PostgreSQL only in certain positions. Specifically, for $queryRaw
with a CTE or subquery, the alias is accessible; for a plain SELECT ... WHERE score < x, the
alias is accessible in PostgreSQL (unlike in MySQL/SQLite), so this part works. But the tuple
comparison (float4_expression) < (numeric_literal) still suffers from the type promotion issue.

Current behavior

src/services/search.service.ts — searchCampaignsByRelevance():

const scoreExpr = Prisma.sql`GREATEST(
  word_similarity(${query}, title),
  word_similarity(${query}, description)
)`;

// ...

if (cursor) {
  const { score: lastScore, id: lastId } = decodeCursor(cursor);
  cursorCondition = Prisma.sql` AND (score, id) < (${lastScore}, ${lastId})`;
}

// The score is float4 from word_similarity; lastScore is a JS number from
// JSON.parse — type mismatch causes incorrect row-ordering comparisons.

if (data.length === normalizedLimit && ids.length > 0) {
  const lastResult = data[data.length - 1];
  const lastScore = (scoreMap.get(lastResult.id) ?? 0) as number;
  nextCursor = encodeCursor(lastScore, lastResult.id);
  // lastScore is the JS-deserialized float4 → float8 → JS number
}

Identical bug exists in searchDonationsByRelevance().

Required behavior

After the fix:

1. Cursors are stable across pages: requesting page 1, page 2, page 3 for a static dataset
   returns no duplicate rows and no skipped rows for any query string.

2. The score comparison in the WHERE clause is type-safe: the cursor value and the computed
   score are compared in the same PostgreSQL type.

3. The cursor is opaque to the client (base64 encoded); the encoding format can change without
   breaking the API contract as long as cursor strings from before the fix are gracefully rejected
   (not silently misinterpreted).

4. Pagination is stable even when multiple rows share the exact same word_similarity score —
   the id tiebreaker correctly partitions these rows across pages.

Constraints

• The fix must not change the API response schema or the nextCursor field name.
• Cursors from before the fix (if any are in production) must be handled gracefully: either a
  version field in the cursor allows detection and rejection with a 400, or the cursor is treated
  as invalid and the first page is returned.
• The fix must handle the case where word_similarity returns NULL for rows where the column is
  NULL (the description column can be NULL in the schema; COALESCE(word_similarity(...), 0)
  is already used for donations but not for the GREATEST() in campaign search — this must be
  verified and fixed consistently).
• Do not change the SQL scoring expression (the GREATEST(word_similarity, ...) pattern) — only
  the cursor encoding and the WHERE clause comparison are in scope.
• The same fix must be applied to both searchCampaignsByRelevance() and
  searchDonationsByRelevance().
• Do not rely on repository snapshots or point-in-time repo states; work against the live default
  branch only.

Acceptance criteria

• Unit test: encodeCursor → decodeCursor roundtrip for a float4-range value such as
  0.5000001192092896 produces a cursor that, when sent to the next page SQL, returns exactly
  zero overlapping rows with the previous page.
• The cursor encoding stores the score in a format that is losslessly comparable with
  PostgreSQL word_similarity output — either as a string representation of the exact float4
  hex value, or by casting the score to float8 explicitly in SQL before the comparison.
• Integration test with a seeded dataset: requesting all pages of a 50-campaign dataset (10
  per page) for a query where all 50 match returns exactly 50 unique campaign IDs across all
  pages with no duplicates and no gaps.
• Adversarial test: 20 campaigns all with word_similarity = 0.5 for a given query — all 20
  appear exactly once across two pages, partitioned by id descending.
• Adversarial test: a cursor from a prior page is sent with a manipulated score (e.g., score: -1) — the query returns results without crashing and without exposing more data than intended.
• NULL description campaigns are handled correctly — GREATEST(word_similarity(q, title), COALESCE(word_similarity(q, description), 0)) is used in campaign search, matching the
  existing handling in donation search.
• The fix is applied to both searchCampaignsByRelevance() and searchDonationsByRelevance().
• All existing tests in src/services/search.service.test.ts and
  tests/performance/search.performance.test.ts pass.

Out of scope

• Changing the relevance scoring algorithm (word_similarity vs similarity vs full-text).
• Offset-based pagination for relevance search.
• Cursor pagination for the non-relevance (field-sort) search paths.
• The beneficiary search path (separate implementation).

Hints and references

• PostgreSQL float4/float8 promotion: word_similarity() returns float4. When a JavaScript
  number is sent as a query parameter via Prisma's $queryRaw, it is bound as a float8
  (PostgreSQL numeric in some drivers, float8 in others — check with SELECT pg_typeof($1)).
  The safest fix is to CAST(score AS float8) in the SQL and bind the cursor value as float8
  to force consistent type comparison. Alternatively, store the cursor score as the IEEE 754
  hex string score.toString(16) and parse it back with parseFloat('0x' + hex) for exact
  reconstruction.
• PostgreSQL row comparison semantics: (a, b) < (x, y) is a < x OR (a = x AND b < y).
  This is standard SQL row comparison; PostgreSQL supports it for ORDER BY (score DESC, id DESC)
  with WHERE (score, id) < (lastScore, lastId) — but only when score and lastScore are
  the same type. Mixed-type comparisons involving float4 and numeric literals can produce
  counter-intuitive results due to implicit casting.
• Stable cursor design: a well-known alternative is to abandon the tuple comparison and use
  WHERE id < lastId AND score <= lastScore OR score < lastScore. This is equivalent for the
  (score DESC, id DESC) ordering and avoids the tuple comparison operator type issue entirely.
• tests/performance/search.performance.test.ts: the existing performance test file should be
  extended with a pagination stability test.
• Related: src/services/search.service.ts lines 348–420 (campaign relevance) and 465–540
  (donation relevance) — the two affected methods.

[Stanley-Owoh]

Good day maintainer, i would like to work on this issue. i will fix the relevance cursor score/type handling in both search paths, add versioned cursor validation, handle NULL descriptions consistently, and add pagination stability tests for duplicates, gaps, and equal scores.

[Petah1]

ill like to solve this issue

[grantfox-oss]

🦊 GrantFox — @Petah1 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 #194)
2. Your PR will be reviewed by the aid-linkk maintainers

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Third Campaign campaign!

@Petah1's PR #201 was approved and merged by @BarryArinze.

🏆 @Petah1: You earned 35 FoxPoints for this contribution! Your current tier: Explorer (564 total points). Track your full progress on GrantFox.

👏 Great work, @Petah1! Keep contributing to aid-linkk.