Function checkColumns

apps/sim/lib/table/validation.ts:585–648

(ex: UniqueCheckExecutor)

  // LOCAL dies at its commit; it only penalizes plan shape, and the statements
  // that follow in those transactions are tenant-scoped writes).
  const checkColumns = async (ex: UniqueCheckExecutor) => {
    for (const [columnId, { values, column }] of valuesByColumn) {
      if (values.size === 0) continue

      if (!NAME_PATTERN.test(columnId)) {
        throw new Error(`Invalid column id: ${columnId}`)
      }

      const valueArray = Array.from(values)
      const valueConditions = valueArray.map((normalizedValue) => {
        // Check if the original values are strings (normalized values for strings are lowercase)
        // We need to determine the type from the column definition or the first row that has this value
        const isStringColumn = column.type === 'string'

        if (isStringColumn) {
          return sql`lower(${userTableRows.data}->>${sql.raw(`'${columnId}'`)}) = ${normalizedValue}`
        }
        return sql`(${userTableRows.data}->${sql.raw(`'${columnId}'`)})::jsonb = ${normalizedValue}::jsonb`
      })

      const conflictingRows = await ex
        .select({
          id: userTableRows.id,
          data: userTableRows.data,
          position: userTableRows.position,
        })
        .from(userTableRows)
        .where(and(eq(userTableRows.tableId, tableId), or(...valueConditions)))
        .limit(valueArray.length) // We only need up to one conflict per value

      // Map conflicts back to batch rows
      for (const conflict of conflictingRows) {
        const conflictData = conflict.data as RowData
        const conflictValue = conflictData[columnId]
        const normalizedConflictValue =
          typeof conflictValue === 'string'
            ? conflictValue.toLowerCase()
            : JSON.stringify(conflictValue)

        // Find which batch rows have this conflicting value
        for (let i = 0; i < rows.length; i++) {
          const rowValue = rows[i][columnId]
          if (rowValue === null || rowValue === undefined) continue

          const normalizedRowValue =
            typeof rowValue === 'string' ? rowValue.toLowerCase() : JSON.stringify(rowValue)

          if (normalizedRowValue === normalizedConflictValue) {
            // Check if this row already has errors for this column
            let rowError = rowErrors.find((e) => e.row === i)
            if (!rowError) {
              rowError = { row: i, errors: [] }
              rowErrors.push(rowError)
            }

            const errorMsg = `Column "${column.name}" must be unique. Value "${rowValue}" already exists in row ${conflict.position + 1}`
            if (!rowError.errors.includes(errorMsg)) {
              rowError.errors.push(errorMsg)