Function upsertRow

apps/sim/lib/table/rows/service.ts:508–735

(
  data: UpsertRowData,
  table: TableDefinition,
  requestId: string
)

 * @throws Error if no unique columns, ambiguous conflict target, or capacity exceeded
 */
export async function upsertRow(
  data: UpsertRowData,
  table: TableDefinition,
  requestId: string
): Promise<UpsertResult> {
  const schema = table.schema
  const uniqueColumns = getUniqueColumns(schema)

  if (uniqueColumns.length === 0) {
    throw new Error(
      'Upsert requires at least one unique column in the schema. Please add a unique constraint to a column or use insert instead.'
    )
  }

  // Determine the single conflict target column, resolving to its stable
  // storage id (the row-data key). `conflictTarget` may arrive as an id
  // (first-party) or a name (legacy/internal) — match either.
  let targetColumnKey: string
  if (data.conflictTarget) {
    const col = uniqueColumns.find(
      (c) => getColumnId(c) === data.conflictTarget || c.name === data.conflictTarget
    )
    if (!col) {
      throw new Error(
        `Column "${data.conflictTarget}" is not a unique column. Available unique columns: ${uniqueColumns.map((c) => c.name).join(', ')}`
      )
    }
    targetColumnKey = getColumnId(col)
  } else if (uniqueColumns.length === 1) {
    targetColumnKey = getColumnId(uniqueColumns[0])
  } else {
    throw new Error(
      `Table has multiple unique columns (${uniqueColumns.map((c) => c.name).join(', ')}). Specify a conflict column to indicate which one to match on.`
    )
  }

  // Validate row data
  const sizeValidation = validateRowSize(data.data)
  if (!sizeValidation.valid) {
    throw new Error(sizeValidation.errors.join(', '))
  }

  const schemaValidation = coerceRowToSchema(data.data, schema)
  if (!schemaValidation.valid) {
    throw new Error(`Schema validation failed: ${schemaValidation.errors.join(', ')}`)
  }

  // Read the conflict-target value *after* coercion so `matchFilter` branches on
  // the persisted type (e.g. a coerced `"123"` → `123` matches existing rows).
  const targetValue = data.data[targetColumnKey]
  if (targetValue === undefined || targetValue === null) {
    // Surface the display name, not the internal id — v1 callers pass a name.
    const targetColumnName =
      uniqueColumns.find((c) => getColumnId(c) === targetColumnKey)?.name ?? targetColumnKey
    throw new Error(`Upsert requires a value for the conflict target column "${targetColumnName}"`)
  }

  // `data->` and `data->>` accept the JSON key as a parameterized text value;