Function findRowMatches

apps/sim/lib/table/rows/service.ts:791–868

(
  table: TableDefinition,
  options: { q: string; filter?: Filter; sort?: Sort },
  requestId: string
)

 * index on a text projection is the future accelerator if needed.
 */
export async function findRowMatches(
  table: TableDefinition,
  options: { q: string; filter?: Filter; sort?: Sort },
  requestId: string
): Promise<{ matches: FindRowMatch[]; truncated: boolean }> {
  const tableName = USER_TABLE_ROWS_SQL_NAME
  const columns = table.schema.columns
  // Row data is keyed by stable column id, so scan/return JSONB keys as ids.
  const columnIds = columns.map(getColumnId)
  if (columnIds.length === 0) return { matches: [], truncated: false }

  // Same visibility rule as queryRows: don't surface rows a running delete job will remove.
  const deleteMask = await pendingDeleteMask(table)

  const baseConditions = and(
    eq(userTableRows.tableId, table.id),
    eq(userTableRows.workspaceId, table.workspaceId),
    deleteMask
  )
  let whereClause: SQL | undefined = baseConditions
  if (options.filter && Object.keys(options.filter).length > 0) {
    const filterClause = buildFilterClause(options.filter, tableName, columns)
    if (filterClause) whereClause = and(baseConditions, filterClause)
  }

  const fractionalOrdering = await isFeatureEnabled('tables-fractional-ordering')
  const orderBySql = buildRowOrderBySql(options.sort, tableName, columns, fractionalOrdering)
  const pattern = `%${escapeLikePattern(options.q)}%`

  const result = await db.transaction(async (trx) => {
    // Planner flags, not correctness: `enable_* = off` only penalizes a plan shape, so a
    // genuinely required sort still runs. Seqscan off keeps the scan inside the tenant's rows
    // (the lateral ILIKE is unestimatable, so the planner otherwise walks the whole shared
    // relation). On the default order, the remaining flags steer to the already-sorted
    // `(table_id, order_key, id)` index walk so the window function streams without a 100MB+
    // disk sort; a custom sort has no index to stream from, so those flags would only distort
    // that plan.
    await trx.execute(sql`SET LOCAL enable_seqscan = off`)
    if (!options.sort) {
      await trx.execute(sql`SET LOCAL enable_bitmapscan = off`)
      await trx.execute(sql`SET LOCAL enable_sort = off`)
      await trx.execute(sql`SET LOCAL max_parallel_workers_per_gather = 0`)
    }
    return trx.execute<{
      ordinal: string | number
      id: string
      column_name: string
    }>(sql`
      WITH ordered AS (
        SELECT id, data, row_number() OVER (ORDER BY ${orderBySql}) - 1 AS ordinal
        FROM ${userTableRows}
        WHERE ${whereClause}
      )
      SELECT o.ordinal, o.id, kv.key AS column_name
      FROM ordered o
      CROSS JOIN LATERAL jsonb_each_text(o.data) kv
      WHERE kv.value ILIKE ${pattern}
        AND ${inArray(sql`kv.key`, columnIds)}