How to fix validation_failed: Input validation failed in Supabase

-- Check table schema and constraints
SELECT
  column_name,
  data_type,
  is_nullable,
  column_default,
  character_maximum_length
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'your_table_name'
ORDER BY ordinal_position;

-- Check constraints on the table
SELECT
  tc.constraint_name,
  tc.constraint_type,
  kcu.column_name,
  ccu.table_name AS foreign_table_name,
  ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
LEFT JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
LEFT JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.table_schema = 'public'
AND tc.table_name = 'your_table_name';

// Test with minimal valid data
const testData = {
  // Only include required fields first
  required_field: 'value'
};

const { data, error } = await supabase
  .from('your_table')
  .insert(testData);

-- See exact data types for each column
SELECT
  column_name,
  data_type,
  udt_name,
  is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'your_table_name';

// Wrong: Sending string to integer column
const badData = { id: '123' }; // id is integer

// Right: Convert to number
const goodData = { id: parseInt('123', 10) };

// Wrong: Invalid date format
const badData = { created_at: '2023-13-45' };

// Right: ISO 8601 format
const goodData = { created_at: '2023-12-12T10:30:00Z' };

// Or use Date object
const goodData2 = { created_at: new Date().toISOString() };

// Wrong: Sending string instead of object
const badData = { metadata: '{"key": "value"}' };

// Right: Send actual object
const goodData = { metadata: { key: 'value' } };

// Wrong: Sending string instead of array
const badData = { tags: 'tag1,tag2' };

// Right: Send actual array
const goodData = { tags: ['tag1', 'tag2'] };

-- Find NOT NULL columns without defaults
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'your_table_name'
AND is_nullable = 'NO'
AND column_default IS NULL;

// Check your data includes all required fields
const requiredFields = ['id', 'user_id', 'created_at'];
const hasAllRequired = requiredFields.every(field => field in data);

if (!hasAllRequired) {
  console.error('Missing required fields:', requiredFields.filter(f => !(f in data)));
}

-- Add default value for created_at
ALTER TABLE your_table
ALTER COLUMN created_at SET DEFAULT now();

-- Add default value for status
ALTER TABLE your_table
ALTER COLUMN status SET DEFAULT 'pending';

-- Create trigger to set timestamps
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at_trigger
BEFORE UPDATE ON your_table
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- Make column nullable
ALTER TABLE your_table
ALTER COLUMN optional_field DROP NOT NULL;

-- Find unique constraints
SELECT
  tc.constraint_name,
  kcu.column_name,
  tc.constraint_type
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
WHERE tc.table_schema = 'public'
AND tc.table_name = 'your_table_name'
AND tc.constraint_type = 'UNIQUE';

// Before inserting, check if value already exists
const { data: existing } = await supabase
  .from('users')
  .select('email')
  .eq('email', newUser.email)
  .single();

if (existing) {
  // Handle duplicate - update instead of insert
  const { data } = await supabase
    .from('users')
    .update(newUser)
    .eq('email', newUser.email);
} else {
  // Safe to insert
  const { data } = await supabase
    .from('users')
    .insert(newUser);
}

// Supabase handles duplicates automatically with upsert
const { data, error } = await supabase
  .from('users')
  .upsert(newUser, {
    onConflict: 'email' // Column with unique constraint
  });

// For columns like usernames, generate unique versions
async function getUniqueUsername(baseUsername) {
  let username = baseUsername;
  let counter = 1;

  while (true) {
    const { data: existing } = await supabase
      .from('users')
      .select('username')
      .eq('username', username)
      .single();

    if (!existing) return username;

    username = baseUsername + counter;
    counter++;
  }
}

-- Remove unique constraint
ALTER TABLE your_table
DROP CONSTRAINT constraint_name;

-- Or create partial unique constraint
CREATE UNIQUE INDEX unique_active_email
ON users(email)
WHERE status = 'active';

-- Find foreign key constraints
SELECT
  tc.constraint_name,
  kcu.column_name AS foreign_key_column,
  ccu.table_name AS referenced_table,
  ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
WHERE tc.table_schema = 'public'
AND tc.table_name = 'your_table_name'
AND tc.constraint_type = 'FOREIGN KEY';

// Check if referenced record exists before insert
const { data: referencedRecord } = await supabase
  .from('referenced_table')
  .select('id')
  .eq('id', foreignKeyValue)
  .single();

if (!referencedRecord) {
  // Create the referenced record first
  const { data: newRef } = await supabase
    .from('referenced_table')
    .insert({ id: foreignKeyValue, ...otherData });
}

// Use Supabase's transaction support
const { data, error } = await supabase.rpc('create_with_references', {
  main_data: mainRecord,
  ref_data: referencedRecord
});

// Or implement client-side transaction logic
async function insertWithReferences(mainData, refData) {
  // Insert reference first
  const { data: ref, error: refError } = await supabase
    .from('referenced_table')
    .insert(refData);

  if (refError) throw refError;

  // Then insert main record with foreign key
  const { data: main, error: mainError } = await supabase
    .from('main_table')
    .insert({
      ...mainData,
      ref_id: ref.id
    });

  if (mainError) {
    // Rollback: delete the reference we just created
    await supabase.from('referenced_table').delete().eq('id', ref.id);
    throw mainError;
  }

  return { main, ref };
}

-- Make foreign key nullable
ALTER TABLE your_table
ALTER COLUMN foreign_key_column DROP NOT NULL;

-- Add ON DELETE CASCADE or SET NULL
ALTER TABLE your_table
DROP CONSTRAINT fk_constraint_name;

ALTER TABLE your_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (foreign_key_column)
REFERENCES referenced_table(id)
ON DELETE SET NULL;

// Comprehensive validation function
function validateTableData(tableName, data) {
  const validations = {
    users: (data) => {
      const errors = [];

      // Required fields
      if (!data.email) errors.push('Email is required');
      if (!data.name) errors.push('Name is required');

      // Data type validation
      if (data.email && typeof data.email !== 'string') {
        errors.push('Email must be a string');
      }

      if (data.age && (typeof data.age !== 'number' || data.age < 0)) {
        errors.push('Age must be a positive number');
      }

      // Format validation
      if (data.email && !isValidEmail(data.email)) {
        errors.push('Invalid email format');
      }

      // Length validation
      if (data.name && data.name.length > 100) {
        errors.push('Name must be 100 characters or less');
      }

      return errors;
    },

    products: (data) => {
      const errors = [];

      if (!data.name) errors.push('Product name is required');
      if (!data.price || data.price < 0) errors.push('Price must be positive');
      if (data.stock !== undefined && !Number.isInteger(data.stock)) {
        errors.push('Stock must be an integer');
      }

      return errors;
    }
  };

  const validator = validations[tableName];
  return validator ? validator(data) : [];
}

// Usage
const errors = validateTableData('users', userData);
if (errors.length > 0) {
  console.error('Validation errors:', errors);
  return;
}

// Sanitize data before sending
function sanitizeTableData(tableName, data) {
  const sanitizers = {
    users: (data) => ({
      ...data,
      email: data.email?.trim().toLowerCase(),
      name: data.name?.trim(),
      age: data.age ? parseInt(data.age, 10) : null
    }),

    products: (data) => ({
      ...data,
      name: data.name?.trim(),
      price: parseFloat(data.price),
      stock: data.stock ? parseInt(data.stock, 10) : null
    })
  };

  const sanitizer = sanitizers[tableName];
  return sanitizer ? sanitizer(data) : data;
}

// Use validated and sanitized data
const cleanData = sanitizeTableData('users', userData);
const { data, error } = await supabase
  .from('users')
  .insert(cleanData);

import { z } from 'zod';

const userSchema = z.object({
  email: z.string().email(),
  name: z.string().min(1).max(100),
  age: z.number().int().positive().optional()
});

try {
  const validatedData = userSchema.parse(userData);
  // Data is valid, proceed with insert
} catch (error) {
  console.error('Validation failed:', error.errors);
}