simstudioai--sim/apps/sim/app/api/table/[tableId]/export/route.ts

import { AuditAction, AuditResourceType, recordAudit } from '@sim/audit'
import { createLogger } from '@sim/logger'
import { type NextRequest, NextResponse } from 'next/server'
import { tableExportFormatSchema, tableIdParamsSchema } from '@/lib/api/contracts/tables'
import { getValidationErrorMessage } from '@/lib/api/server'
import { checkSessionOrInternalAuth } from '@/lib/auth/hybrid'
import { neutralizeCsvFormula } from '@/lib/core/utils/csv'
import { generateRequestId } from '@/lib/core/utils/request'
import { withRouteHandler } from '@/lib/core/utils/with-route-handler'
import { captureServerEvent } from '@/lib/posthog/server'
import { buildNameById, getColumnId, rowDataIdToName } from '@/lib/table/column-keys'
import { queryRows } from '@/lib/table/rows/service'
import { accessError, checkAccess } from '@/app/api/table/utils'

const logger = createLogger('TableExport')

const EXPORT_BATCH_SIZE = 1000

type ExportFormat = 'csv' | 'json'

interface RouteParams {
  params: Promise<{ tableId: string }>
}

/** GET /api/table/[tableId]/export - Streams the full table contents as CSV or JSON. */
export const GET = withRouteHandler(async (request: NextRequest, { params }: RouteParams) => {
  const requestId = generateRequestId()
  const { tableId } = tableIdParamsSchema.parse(await params)

  const auth = await checkSessionOrInternalAuth(request, { requireWorkflowId: false })
  if (!auth.success || !auth.userId) {
    return NextResponse.json({ error: 'Authentication required' }, { status: 401 })
  }
  const userId = auth.userId

  const { searchParams } = new URL(request.url)
  const formatValidation = tableExportFormatSchema.safeParse(
    searchParams.get('format') ?? undefined
  )
  if (!formatValidation.success) {
    return NextResponse.json(
      { error: getValidationErrorMessage(formatValidation.error) },
      { status: 400 }
    )
  }
  const format: ExportFormat = formatValidation.data

  const access = await checkAccess(tableId, auth.userId, 'read')
  if (!access.ok) return accessError(access, requestId, tableId)
  const { table } = access

  const columns = table.schema.columns
  // Stored row data is id-keyed; CSV headers and JSON keys are display names, so
  // translate id → name on the way out (export is a name-friendly boundary).
  const nameById = buildNameById(table.schema)
  const safeName = sanitizeFilename(table.name)
  const filename = `${safeName}.${format}`

  // Audit before streaming: rows leave incrementally, so a mid-stream failure still exfiltrates partial data.
  recordAudit({
    workspaceId: table.workspaceId ?? null,
    actorId: userId,
    action: AuditAction.TABLE_EXPORTED,
    resourceType: AuditResourceType.TABLE,
    resourceId: tableId,
    resourceName: table.name,
    description: `Exported table "${table.name}" as ${format.toUpperCase()}`,
    metadata: { format, rowCount: table.rowCount },
    request,
  })
  if (table.workspaceId) {
    captureServerEvent(
      userId,
      'table_exported',
      { table_id: tableId, workspace_id: table.workspaceId },
      { groups: { workspace: table.workspaceId } }
    )
  }

  const stream = new ReadableStream<Uint8Array>({
    async start(controller) {
      const encoder = new TextEncoder()
      try {
        if (format === 'csv') {
          controller.enqueue(
            encoder.encode(`${toCsvRow(columns.map((c) => neutralizeCsvFormula(c.name)))}\n`)
          )
        } else {
          controller.enqueue(encoder.encode('['))
        }

        let offset = 0
        let firstJsonRow = true
        while (true) {
          const result = await queryRows(
            table,
            { limit: EXPORT_BATCH_SIZE, offset, includeTotal: false },
            requestId
          )

          for (const row of result.rows) {
            if (format === 'csv') {
              const values = columns.map((c) => formatCsvValue(row.data[getColumnId(c)]))
              controller.enqueue(encoder.encode(`${toCsvRow(values)}\n`))
            } else {
              const prefix = firstJsonRow ? '' : ','
              firstJsonRow = false
              controller.enqueue(
                encoder.encode(prefix + JSON.stringify(rowDataIdToName(row.data, nameById)))
              )
            }
          }

          if (result.rows.length < EXPORT_BATCH_SIZE) break
          offset += result.rows.length
        }

        if (format === 'json') controller.enqueue(encoder.encode(']'))
        controller.close()

        logger.info(`[${requestId}] Exported table ${tableId}`, {
          format,
          rowCount: table.rowCount,
        })
      } catch (err) {
        logger.error(`[${requestId}] Export failed for table ${tableId}`, err)
        controller.error(err)
      }
    },
  })

  return new NextResponse(stream, {
    status: 200,
    headers: {
      'Content-Type': format === 'csv' ? 'text/csv; charset=utf-8' : 'application/json',
      'Content-Disposition': `attachment; filename="${filename}"`,
      'Cache-Control': 'no-store',
    },
  })
})

function sanitizeFilename(name: string): string {
  const cleaned = name.replace(/[^a-zA-Z0-9_-]+/g, '_').replace(/^_+|_+$/g, '')
  return cleaned || 'table'
}

/**
 * Serializes a cell for CSV. Only string cells are formula-neutralized; numbers,
 * booleans, dates, and JSON objects can never form a trigger and pass through verbatim.
 */
function formatCsvValue(value: unknown): string {
  if (value === null || value === undefined) return ''
  if (value instanceof Date) return value.toISOString()
  if (typeof value === 'object') return JSON.stringify(value)
  if (typeof value === 'string') return neutralizeCsvFormula(value)
  return String(value)
}

function toCsvRow(values: string[]): string {
  return values.map(escapeCsvField).join(',')
}

function escapeCsvField(field: string): string {
  if (/[",\n\r]/.test(field)) {
    return `"${field.replace(/"/g, '""')}"`
  }
  return field
}