feat(table): byte-bounded query drain (5MB) + PostgREST round-trip an…

…d cursor hardening queryRows now drains rows in adaptively-sized bounded batches instead of one unbounded SELECT. Omitted limit returns the entire result and fails fast (400) past the 5MB byte budget; bounded pages byte-cut early and signal remaining rows via nextCursor (witnessed by a peek row, never inferred from page size). Cursor codec gains a compound {k,i,o} shape for unkeyed-row resumes and a keysetValid gate closing the fractional-flag-off keyset/order mismatch. Council must-fix cluster: - Serializer round-trip: whole-pattern quoting (contains with dots), backslash quote escaping, nlike/nilike engine ops (ncontains parses again), isEmpty desugars to or=(f.is.null,f.eq."") preserving null-or-empty semantics - Unconditional name→id translation on PostgREST string paths (session-auth filters no longer silently match zero rows) - Cursor decode hardening (object guard, o>=0), 400 on keyset cursor + order - Block: cursor "null" artifact guard, NaN limit fails fast, filterBuilder required-flag removed (serializer hard-block with agent-set filter string) - Parser: reject empty or=()/and=()/in.(), Number.isFinite, strict sort dirs - TableQueryValidationError moved to client-safe lib/table/errors.ts (drops the drizzle-orm edge from client-bundled block defs) Consumers: export stops on nextCursor (byte-cut pages no longer truncate), legacy/v1 routes expose nextCursor additively, v2 route drops executions, copilot query_rows reports partial pages with a continue offset, v1 Table block tools registered in the minimal registry. Agent catalog regenerated from the copilot fork (PostgREST string filter, order param, new pagination semantics). Co-Authored-By: Claude Fable 5 <noreply@anthropic.com> Claude-Session: https://claude.ai/code/session_01Bd25zCzh21omjFhRz32ov6
TheodoreSpeaks · Jul 30, 2026 · Jun 27, 2026 · Jun 27, 2026 · Jul 3, 2026 · Jul 3, 2026
commit da06034d9c8a2c547687e4f587e080e32e04d980
diff --git a/apps/sim/app/api/table/[tableId]/export/route.ts b/apps/sim/app/api/table/[tableId]/export/route.ts
@@ -87,7 +87,9 @@ export const GET = withRouteHandler(async (request: NextRequest, { params }: Rou
             }
           }
 
-          if (result.rows.length < EXPORT_BATCH_SIZE) break
+          // A page can be cut by the byte budget before reaching EXPORT_BATCH_SIZE,
+          // so a short page does NOT mean the export is done — only a null cursor does.
+          if (!result.nextCursor) break
           offset += result.rows.length
         }
 

diff --git a/apps/sim/app/api/table/[tableId]/query/route.test.ts b/apps/sim/app/api/table/[tableId]/query/route.test.ts
@@ -0,0 +1,167 @@
+/**
+ * @vitest-environment node
+ *
+ * v2 query route: PostgREST string parsing, unconditional name→id translation
+ * (session auth included — the string grammar is name-keyed for every caller),
+ * cursor validation, and the response envelope.
+ */
+import { hybridAuthMockFns } from '@sim/testing'
+import { NextRequest } from 'next/server'
+import { beforeEach, describe, expect, it, vi } from 'vitest'
+import type { TableDefinition } from '@/lib/table/types'
+
+const { mockCheckAccess, mockQueryRows } = vi.hoisted(() => ({
+  mockCheckAccess: vi.fn(),
+  mockQueryRows: vi.fn(),
+}))
+
+vi.mock('@/app/api/table/utils', async () => {
+  const { NextResponse } = await import('next/server')
+  return {
+    checkAccess: mockCheckAccess,
+    accessError: (result: { status: number }) =>
+      NextResponse.json({ error: 'Access denied' }, { status: result.status }),
+  }
+})
+
+vi.mock('@/lib/table', async () => {
+  // row-wire pulls the column-keys helpers through this barrel.
+  const columnKeys = await import('@/lib/table/column-keys')
+  return { ...columnKeys }
+})
+
+vi.mock('@/lib/table/rows/service', () => ({
+  queryRows: mockQueryRows,
+}))
+
+import { encodeCursor } from '@/lib/table/rows/cursor'
+import { POST } from '@/app/api/table/[tableId]/query/route'
+
+function buildTable(): TableDefinition {
+  return {
+    id: 'tbl_1',
+    name: 'People',
+    description: null,
+    schema: {
+      columns: [
+        { id: 'col_aaa', name: 'name', type: 'string' },
+        { id: 'col_bbb', name: 'wins', type: 'number' },
+      ],
+    },
+    metadata: null,
+    rowCount: 0,
+    maxRows: 100,
+    workspaceId: 'workspace-1',
+    createdBy: 'user-1',
+    archivedAt: null,
+    createdAt: new Date('2024-01-01'),
+    updatedAt: new Date('2024-01-01'),
+  }
+}
+
+function authAs(authType: 'session' | 'internal_jwt') {
+  hybridAuthMockFns.mockCheckSessionOrInternalAuth.mockResolvedValue({
+    success: true,
+    userId: 'user-1',
+    authType,
+  })
+}
+
+function callQuery(body: Record<string, unknown>) {
+  const req = new NextRequest('http://localhost:3000/api/table/tbl_1/query', {
+    method: 'POST',
+    headers: { 'Content-Type': 'application/json' },
+    body: JSON.stringify(body),
+  })
+  return POST(req, { params: Promise.resolve({ tableId: 'tbl_1' }) })
+}
+
+const EMPTY_RESULT = {
+  rows: [],
+  rowCount: 0,
+  totalCount: 0,
+  limit: 0,
+  offset: 0,
+  nextCursor: null,
+}
+
+describe('POST /api/table/[tableId]/query', () => {
+  beforeEach(() => {
+    vi.clearAllMocks()
+    mockCheckAccess.mockResolvedValue({ ok: true, table: buildTable() })
+    mockQueryRows.mockResolvedValue(EMPTY_RESULT)
+  })
+
+  it('translates filter/order column names to storage ids for SESSION auth too', async () => {
+    authAs('session')
+    const res = await callQuery({
+      workspaceId: 'workspace-1',
+      filter: 'name=eq.John&wins=gte.10',
+      order: 'wins.desc',
+    })
+
+    expect(res.status).toBe(200)
+    const options = mockQueryRows.mock.calls[0][1]
+    expect(options.predicate).toEqual({
+      all: [
+        { field: 'col_aaa', op: 'eq', value: 'John' },
+        { field: 'col_bbb', op: 'gte', value: 10 },
+      ],
+    })
+    expect(options.sort).toEqual({ col_bbb: 'desc' })
+    expect(options.withExecutions).toBe(false)
+  })
+
+  it('rejects a keyset cursor combined with a custom order', async () => {
+    authAs('internal_jwt')
+    const cursor = encodeCursor({
+      lastRow: { id: 'row_1', orderKey: 'a1' },
+      keysetValid: true,
+      nextOffset: 1,
+    })
+    const res = await callQuery({ workspaceId: 'workspace-1', order: 'wins.desc', cursor })
+
+    expect(res.status).toBe(400)
+    const body = await res.json()
+    expect(body.error).toMatch(/not valid for a sorted query/)
+    expect(mockQueryRows).not.toHaveBeenCalled()
+  })
+
+  it('returns 400 (not 500) for a cursor that decodes to a JSON primitive', async () => {
+    authAs('internal_jwt')
+    const res = await callQuery({
+      workspaceId: 'workspace-1',
+      cursor: Buffer.from('42').toString('base64url'),
+    })
+
+    expect(res.status).toBe(400)
+    expect((await res.json()).error).toBe('Invalid cursor')
+  })
+
+  it('returns 400 with the parser message for an invalid filter', async () => {
+    authAs('internal_jwt')
+    const res = await callQuery({ workspaceId: 'workspace-1', filter: 'wins=bogus.1' })
+
+    expect(res.status).toBe(400)
+    expect((await res.json()).error).toMatch(/Unknown filter operator/)
+  })
+
+  it('passes nextCursor through the response envelope and skips the count on later pages', async () => {
+    authAs('internal_jwt')
+    mockQueryRows.mockResolvedValue({ ...EMPTY_RESULT, nextCursor: 'tok' })
+    const cursor = encodeCursor({
+      lastRow: { id: 'row_1', orderKey: 'a1' },
+      keysetValid: true,
+      nextOffset: 1,
+    })
+
+    const res = await callQuery({ workspaceId: 'workspace-1', cursor })
+
+    expect(res.status).toBe(200)
+    const body = await res.json()
+    expect(body.data.nextCursor).toBe('tok')
+    const options = mockQueryRows.mock.calls[0][1]
+    expect(options.includeTotal).toBe(false)
+    expect(options.after).toEqual({ orderKey: 'a1', id: 'row_1' })
+  })
+})
diff --git a/apps/sim/app/api/table/[tableId]/query/route.ts b/apps/sim/app/api/table/[tableId]/query/route.ts
@@ -7,10 +7,11 @@ import { checkSessionOrInternalAuth } from '@/lib/auth/hybrid'
 import { generateRequestId } from '@/lib/core/utils/request'
 import { withRouteHandler } from '@/lib/core/utils/with-route-handler'
 import type { Sort, TableSchema } from '@/lib/table'
+import { buildIdByName, predicateNamesToIds, sortSpecNamesToIds } from '@/lib/table/column-keys'
+import { TableQueryValidationError } from '@/lib/table/errors'
 import { parsePostgrestFilter, parsePostgrestOrder } from '@/lib/table/query-builder/postgrest'
 import { decodeCursor } from '@/lib/table/rows/cursor'
 import { queryRows } from '@/lib/table/rows/service'
-import { TableQueryValidationError } from '@/lib/table/sql'
 import { rowWireTranslators } from '@/app/api/table/row-wire'
 import { accessError, checkAccess } from '@/app/api/table/utils'
 
@@ -55,13 +56,25 @@ export const POST = withRouteHandler(
       const wire = rowWireTranslators(authResult.authType, schema)
       const cursor = body.cursor ? decodeCursor(body.cursor) : undefined
 
-      // PostgREST filter/order arrive as strings (column NAMES); parse with the
-      // schema for type coercion, then translate names → storage ids.
+      // A keyset cursor encodes a position on the DEFAULT `(order_key, id)`
+      // order; combining it with a custom order would silently return page 1
+      // of the sorted view relabeled as a next page.
+      if (cursor?.after && body.order) {
+        return NextResponse.json(
+          { error: 'Cursor is not valid for a sorted query. Restart paging without the cursor.' },
+          { status: 400 }
+        )
+      }
+
+      // PostgREST filter/order strings are column-NAME-keyed by construction
+      // (the parser validates against names), so translate names → storage ids
+      // unconditionally — unlike row data, this is not authType-dependent.
+      const idByName = buildIdByName(schema)
       const predicate = body.filter
-        ? wire.predicateIn(parsePostgrestFilter(body.filter, schema.columns))
+        ? predicateNamesToIds(parsePostgrestFilter(body.filter, schema.columns), idByName)
         : undefined
       const sortSpec = body.order
-        ? wire.sortSpecIn(parsePostgrestOrder(body.order, schema.columns))
+        ? sortSpecNamesToIds(parsePostgrestOrder(body.order, schema.columns), idByName)
         : undefined
       const sort: Sort | undefined = sortSpec?.length
         ? Object.fromEntries(sortSpec.map((s) => [s.field, s.direction]))
@@ -77,6 +90,9 @@ export const POST = withRouteHandler(
           offset: cursor?.offset,
           // Only the first page (no inbound cursor) pays for the total count.
           includeTotal: !body.cursor,
+          // Executions are grid UI state; the v2 surface returns row data only
+          // and the byte budget deliberately measures just `data`.
+          withExecutions: false,
         },
         requestId
       )

diff --git a/apps/sim/app/api/table/[tableId]/rows/find/route.test.ts b/apps/sim/app/api/table/[tableId]/rows/find/route.test.ts
@@ -121,7 +121,7 @@ describe('GET /api/table/[tableId]/rows/find', () => {
   })
 
   it('maps a TableQueryValidationError to 400', async () => {
-    const { TableQueryValidationError } = await import('@/lib/table/sql')
+    const { TableQueryValidationError } = await import('@/lib/table/errors')
     mockFindRowMatches.mockRejectedValueOnce(new TableQueryValidationError('Invalid field name'))
     const res = await callGet({ workspaceId: 'workspace-1', q: 'foo' })
     expect(res.status).toBe(400)

diff --git a/apps/sim/app/api/table/[tableId]/rows/find/route.ts b/apps/sim/app/api/table/[tableId]/rows/find/route.ts
@@ -6,8 +6,8 @@ import { checkSessionOrInternalAuth } from '@/lib/auth/hybrid'
 import { generateRequestId } from '@/lib/core/utils/request'
 import { withRouteHandler } from '@/lib/core/utils/with-route-handler'
 import type { Sort } from '@/lib/table'
+import { TableQueryValidationError } from '@/lib/table/errors'
 import { findRowMatches } from '@/lib/table/rows/service'
-import { TableQueryValidationError } from '@/lib/table/sql'
 import { accessError, checkAccess } from '@/app/api/table/utils'
 
 const logger = createLogger('TableRowsFindAPI')

diff --git a/apps/sim/app/api/table/[tableId]/rows/route.test.ts b/apps/sim/app/api/table/[tableId]/rows/route.test.ts
@@ -6,11 +6,20 @@ import { NextRequest } from 'next/server'
 import { beforeEach, describe, expect, it, vi } from 'vitest'
 import type { TableDefinition } from '@/lib/table'
 
-const { mockCheckAccess, mockInsertRow, mockValidateRowData, mockQueryRows } = vi.hoisted(() => ({
+const {
+  mockCheckAccess,
+  mockInsertRow,
+  mockValidateRowData,
+  mockQueryRows,
+  mockUpdateRowsByFilter,
+  mockDeleteRowsByFilter,
+} = vi.hoisted(() => ({
   mockCheckAccess: vi.fn(),
   mockInsertRow: vi.fn(),
   mockValidateRowData: vi.fn(),
   mockQueryRows: vi.fn(),
+  mockUpdateRowsByFilter: vi.fn(),
+  mockDeleteRowsByFilter: vi.fn(),
 }))
 
 vi.mock('@/app/api/table/utils', async () => {
@@ -31,9 +40,9 @@ vi.mock('@/lib/table', async () => {
     insertRow: mockInsertRow,
     batchInsertRows: vi.fn(),
     batchUpdateRows: vi.fn(),
-    deleteRowsByFilter: vi.fn(),
+    deleteRowsByFilter: mockDeleteRowsByFilter,
     deleteRowsByIds: vi.fn(),
-    updateRowsByFilter: vi.fn(),
+    updateRowsByFilter: mockUpdateRowsByFilter,
     validateBatchRows: vi.fn(),
     validateRowData: mockValidateRowData,
     validateRowSize: vi.fn(() => ({ valid: true })),
@@ -48,7 +57,7 @@ vi.mock('@/lib/table/sql', () => ({
   TableQueryValidationError: class TableQueryValidationError extends Error {},
 }))
 
-import { GET, POST } from '@/app/api/table/[tableId]/rows/route'
+import { DELETE, GET, POST, PUT } from '@/app/api/table/[tableId]/rows/route'
 
 function buildTable(): TableDefinition {
   return {
@@ -218,3 +227,55 @@ describe('GET /api/table/[tableId]/rows', () => {
     expect(body.data.rows[0].data).toEqual({ col_aaa: 'Ada', col_bbb: 36 })
   })
 })
+
+describe('PUT/DELETE /api/table/[tableId]/rows — PostgREST string filters', () => {
+  beforeEach(() => {
+    vi.clearAllMocks()
+    mockCheckAccess.mockResolvedValue({ ok: true, table: buildTable() })
+    mockUpdateRowsByFilter.mockResolvedValue({ affectedCount: 1, affectedRowIds: ['row_1'] })
+    mockDeleteRowsByFilter.mockResolvedValue({ affectedCount: 1, affectedRowIds: ['row_1'] })
+  })
+
+  function callPut(body: Record<string, unknown>) {
+    const req = new NextRequest('http://localhost:3000/api/table/tbl_1/rows', {
+      method: 'PUT',
+      headers: { 'Content-Type': 'application/json' },
+      body: JSON.stringify(body),
+    })
+    return PUT(req, { params: Promise.resolve({ tableId: 'tbl_1' }) })
+  }
+
+  function callDelete(body: Record<string, unknown>) {
+    const req = new NextRequest('http://localhost:3000/api/table/tbl_1/rows', {
+      method: 'DELETE',
+      headers: { 'Content-Type': 'application/json' },
+      body: JSON.stringify(body),
+    })
+    return DELETE(req, { params: Promise.resolve({ tableId: 'tbl_1' }) })
+  }
+
+  it('PUT translates a name-keyed PostgREST string to an id-keyed filter under SESSION auth', async () => {
+    authAs('session')
+    const res = await callPut({
+      workspaceId: 'workspace-1',
+      filter: 'Name=eq.Ada',
+      data: { col_aaa: 'Grace' },
+    })
+
+    expect(res.status).toBe(200)
+    const args = mockUpdateRowsByFilter.mock.calls[0][1]
+    expect(args.filter).toEqual({ $and: [{ col_aaa: 'Ada' }] })
+  })
+
+  it('DELETE accepts the PostgREST string and rejects an invalid one with 400', async () => {
+    authAs('internal_jwt')
+    const ok = await callDelete({ workspaceId: 'workspace-1', filter: 'Age=gte.30' })
+    expect(ok.status).toBe(200)
+    const args = mockDeleteRowsByFilter.mock.calls[0][1]
+    expect(args.filter).toEqual({ $and: [{ col_bbb: { $gte: 30 } }] })
+
+    const bad = await callDelete({ workspaceId: 'workspace-1', filter: 'Age=bogus.1' })
+    expect(bad.status).toBe(400)
+    expect((await bad.json()).error).toMatch(/Unknown filter operator/)
+  })
+})