Function checkUniqueConstraintsDb

apps/sim/lib/table/validation.ts:421–504

(
  tableId: string,
  data: RowData,
  schema: TableSchema,
  excludeRowId?: string,
  executor: UniqueCheckExecutor = db
)

 * writes; otherwise the default `db` connection only observes committed state.
 */
export async function checkUniqueConstraintsDb(
  tableId: string,
  data: RowData,
  schema: TableSchema,
  excludeRowId?: string,
  executor: UniqueCheckExecutor = db
): Promise<ValidationResult> {
  const errors: string[] = []
  const uniqueColumns = getUniqueColumns(schema)

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

  // Build conditions for each unique column value
  const conditions: Array<{ column: ColumnDefinition; value: unknown; sql: SQL }> = []

  for (const column of uniqueColumns) {
    const key = getColumnId(column)
    if (!NAME_PATTERN.test(key)) {
      throw new Error(`Invalid column id: ${key}`)
    }

    const value = data[key]
    if (value === null || value === undefined) continue

    if (typeof value === 'string') {
      conditions.push({
        column,
        value,
        sql: sql`lower(${userTableRows.data}->>${sql.raw(`'${key}'`)}) = ${value.toLowerCase()}`,
      })
    } else {
      // For other types, use direct JSONB comparison
      conditions.push({
        column,
        value,
        sql: sql`(${userTableRows.data}->${sql.raw(`'${key}'`)})::jsonb = ${JSON.stringify(value)}::jsonb`,
      })
    }
  }

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

  // Query for each unique column separately to provide specific error messages.
  // Tenant-bounded: `lower(data->>'col') = ...` is unestimatable, so the planner
  // otherwise seq-scans the whole shared relation per check — 3.5s on every
  // insert/edit when the value is unique (no early exit). With an external
  // transaction the flag is set on it directly — opening our own transaction
  // inside the caller's would be the nested pool checkout the migration-
  // hardening work eliminated (self-deadlock under pool exhaustion).
  const checkConditions = async (ex: UniqueCheckExecutor) => {
    for (const condition of conditions) {
      const baseCondition = and(eq(userTableRows.tableId, tableId), condition.sql)

      const whereClause = excludeRowId