Function checkBatchUniqueConstraintsDb

apps/sim/lib/table/validation.ts:521–661

(
  tableId: string,
  rows: RowData[],
  schema: TableSchema,
  executor: UniqueCheckExecutor = db
)

 * default `db` connection only observes committed state.
 */
export async function checkBatchUniqueConstraintsDb(
  tableId: string,
  rows: RowData[],
  schema: TableSchema,
  executor: UniqueCheckExecutor = db
): Promise<{ valid: boolean; errors: Array<{ row: number; errors: string[] }> }> {
  const uniqueColumns = getUniqueColumns(schema)
  const rowErrors: Array<{ row: number; errors: string[] }> = []

  if (uniqueColumns.length === 0) {
    return { valid: true, errors: [] }
  }

  // Build a set of all unique values for each column to check against DB.
  // Keyed by the stable column id (the row-data storage key).
  const valuesByColumn = new Map<string, { values: Set<string>; column: ColumnDefinition }>()

  for (const column of uniqueColumns) {
    valuesByColumn.set(getColumnId(column), { values: new Set(), column })
  }

  // Collect all unique values from the batch and check for duplicates within the batch
  const batchValueMap = new Map<string, Map<string, number>>() // columnId -> (normalizedValue -> firstRowIndex)

  for (const column of uniqueColumns) {
    batchValueMap.set(getColumnId(column), new Map())
  }

  for (let i = 0; i < rows.length; i++) {
    const rowData = rows[i]
    const currentRowErrors: string[] = []

    for (const column of uniqueColumns) {
      const key = getColumnId(column)
      const value = rowData[key]
      if (value === null || value === undefined) continue

      const normalizedValue =
        typeof value === 'string' ? value.toLowerCase() : JSON.stringify(value)

      // Check for duplicate within batch
      const columnValueMap = batchValueMap.get(key)!
      if (columnValueMap.has(normalizedValue)) {
        const firstRowIndex = columnValueMap.get(normalizedValue)!
        currentRowErrors.push(
          `Column "${column.name}" must be unique. Value "${value}" duplicates row ${firstRowIndex + 1} in batch`
        )
      } else {
        columnValueMap.set(normalizedValue, i)
        valuesByColumn.get(key)!.values.add(normalizedValue)
      }
    }

    if (currentRowErrors.length > 0) {
      rowErrors.push({ row: i, errors: currentRowErrors })
    }
  }