Supabase Data Validation: Client and Server-Side Rules

-- NOT NULL constraints (required fields)
create table users (
  id uuid default gen_random_uuid() primary key,
  email text not null,  -- Email is required
  name text not null,   -- Name is required
  bio text,             -- Bio is optional
  created_at timestamp with time zone not null default now()
);

-- UNIQUE constraints (no duplicates)
create table users (
  id uuid primary key,
  email text not null unique,  -- Each email must be unique
  username text not null unique
);

-- Or create unique constraint separately
alter table users add constraint unique_email unique(email);
alter table users add constraint unique_username unique(username);

-- Composite unique constraint (combination must be unique)
create table user_roles (
  user_id uuid references users(id),
  role text not null,
  unique(user_id, role)  -- Same user can't have same role twice
);

-- CHECK constraints (value validation)
create table products (
  id uuid primary key,
  name text not null,
  price numeric not null check (price > 0),  -- Price must be positive
  discount_pct numeric check (discount_pct >= 0 and discount_pct <= 100),
  stock int check (stock >= 0),  -- Stock can't be negative
  rating numeric check (rating >= 1 and rating <= 5)
);

-- CHECK constraint with custom validation
create table posts (
  id uuid primary key,
  title text not null check (length(title) >= 3 and length(title) <= 200),
  content text not null check (length(content) >= 10),
  status text not null check (status in ('draft', 'published', 'archived')),
  publish_date date check (
    (status = 'published' and publish_date is not null) or
    (status != 'published' and publish_date is null)
  )
);

-- FOREIGN KEY constraints (referential integrity)
create table posts (
  id uuid primary key,
  user_id uuid not null references users(id) on delete cascade,
  category_id uuid references categories(id) on delete set null
);

-- Foreign key with custom actions
create table comments (
  id uuid primary key,
  post_id uuid not null references posts(id)
    on delete cascade      -- Delete comments when post deleted
    on update cascade,     -- Update comment.post_id when post.id changes
  user_id uuid not null references users(id)
    on delete set null     -- Set to null when user deleted
);

-- DEFAULT values
create table orders (
  id uuid default gen_random_uuid() primary key,
  status text not null default 'pending',
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now(),
  is_paid boolean default false,
  quantity int default 1 check (quantity > 0)
);

-- DOMAIN types (reusable validation)
create domain email as text
  check (value ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

create domain phone as text
  check (value ~ '^\+?[1-9]\d{1,14}$');

create table contacts (
  id uuid primary key,
  email email not null,  -- Uses email domain
  phone phone            -- Uses phone domain
);

-- Add constraints to existing tables
alter table users
  add constraint check_email_format 
    check (email ~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$');

alter table products
  add constraint check_price_positive
    check (price > 0);

-- Remove constraints
alter table users drop constraint check_email_format;

-- Validation trigger function
create or replace function validate_user_data()
returns trigger as $$
begin
  -- Validate email format
  if new.email !~ '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$' then
    raise exception 'Invalid email format: %', new.email;
  end if;
  
  -- Validate username length and format
  if length(new.username) < 3 or length(new.username) > 30 then
    raise exception 'Username must be between 3 and 30 characters';
  end if;
  
  if new.username !~ '^[a-zA-Z0-9_-]+$' then
    raise exception 'Username can only contain letters, numbers, hyphens, and underscores';
  end if;
  
  -- Validate age
  if new.age is not null and (new.age < 13 or new.age > 120) then
    raise exception 'Age must be between 13 and 120';
  end if;
  
  return new;
end;
$$ language plpgsql;

-- Create trigger
create trigger validate_user_before_insert_update
  before insert or update on users
  for each row
  execute function validate_user_data();

-- Complex business logic validation
create or replace function validate_order_data()
returns trigger as $$
begin
  -- Validate order total matches line items
  if new.total != (select sum(quantity * price) from order_items where order_id = new.id) then
    raise exception 'Order total does not match line items';
  end if;
  
  -- Validate shipping address for physical products
  if exists (
    select 1 from order_items oi
    join products p on p.id = oi.product_id
    where oi.order_id = new.id and p.is_physical = true
  ) and new.shipping_address is null then
    raise exception 'Shipping address required for physical products';
  end if;
  
  -- Validate discount code
  if new.discount_code is not null then
    if not exists (
      select 1 from discount_codes
      where code = new.discount_code
        and is_active = true
        and valid_from <= now()
        and valid_until >= now()
    ) then
      raise exception 'Invalid or expired discount code';
    end if;
  end if;
  
  return new;
end;
$$ language plpgsql;

create trigger validate_order
  before insert or update on orders
  for each row
  execute function validate_order_data();

-- Prevent invalid state transitions
create or replace function validate_status_transition()
returns trigger as $$
begin
  -- Only allow specific status transitions
  if old.status = 'draft' and new.status not in ('draft', 'published') then
    raise exception 'Draft can only transition to published';
  end if;
  
  if old.status = 'published' and new.status not in ('published', 'archived') then
    raise exception 'Published can only transition to archived';
  end if;
  
  if old.status = 'archived' then
    raise exception 'Archived posts cannot be modified';
  end if;
  
  return new;
end;
$$ language plpgsql;

create trigger check_status_transition
  before update on posts
  for each row
  when (old.status is distinct from new.status)
  execute function validate_status_transition();

// Install Zod
// npm install zod

import { z } from 'zod'

// Basic validation schema
const userSchema = z.object({
  email: z.string().email('Invalid email address'),
  username: z.string()
    .min(3, 'Username must be at least 3 characters')
    .max(30, 'Username must be at most 30 characters')
    .regex(/^[a-zA-Z0-9_-]+$/, 'Username can only contain letters, numbers, hyphens, and underscores'),
  age: z.number()
    .int('Age must be an integer')
    .min(13, 'You must be at least 13 years old')
    .max(120, 'Invalid age')
    .optional(),
  password: z.string()
    .min(8, 'Password must be at least 8 characters')
    .regex(/[A-Z]/, 'Password must contain at least one uppercase letter')
    .regex(/[a-z]/, 'Password must contain at least one lowercase letter')
    .regex(/[0-9]/, 'Password must contain at least one number'),
})

// Infer TypeScript type from schema
type User = z.infer<typeof userSchema>

// Validate data
try {
  const userData = userSchema.parse({
    email: '[email protected]',
    username: 'john_doe',
    age: 25,
    password: 'SecurePass123',
  })
  console.log('Valid:', userData)
} catch (error) {
  if (error instanceof z.ZodError) {
    console.error('Validation errors:', error.errors)
  }
}

// Complex validation schemas
const productSchema = z.object({
  name: z.string().min(3).max(100),
  description: z.string().min(10).max(1000),
  price: z.number().positive('Price must be positive'),
  discountPrice: z.number().positive().optional(),
  stock: z.number().int().min(0, 'Stock cannot be negative'),
  category: z.enum(['electronics', 'clothing', 'food', 'books']),
  tags: z.array(z.string()).min(1, 'At least one tag required').max(10),
  metadata: z.record(z.string(), z.any()).optional(),
}).refine(
  (data) => !data.discountPrice || data.discountPrice < data.price,
  {
    message: 'Discount price must be less than regular price',
    path: ['discountPrice'],
  }
)

// React form validation
import { useState } from 'react'
import { createClient } from '@/lib/supabase/client'

function SignUpForm() {
  const [errors, setErrors] = useState<Record<string, string>>({})
  const supabase = createClient()

  async function handleSubmit(e: React.FormEvent<HTMLFormElement>) {
    e.preventDefault()
    setErrors({})

    const formData = new FormData(e.currentTarget)
    const data = {
      email: formData.get('email') as string,
      username: formData.get('username') as string,
      password: formData.get('password') as string,
    }

    // Client-side validation
    try {
      const validated = userSchema.parse(data)

      // Submit to Supabase
      const { error } = await supabase.auth.signUp({
        email: validated.email,
        password: validated.password,
        options: {
          data: {
            username: validated.username,
          },
        },
      })

      if (error) throw error

      console.log('User created successfully')
    } catch (error) {
      if (error instanceof z.ZodError) {
        // Convert Zod errors to form errors
        const formErrors: Record<string, string> = {}
        error.errors.forEach((err) => {
          const field = err.path[0] as string
          formErrors[field] = err.message
        })
        setErrors(formErrors)
      } else {
        console.error('Sign up error:', error)
      }
    }
  }

  return (
    <form onSubmit={handleSubmit} className="space-y-4">
      <div>
        <input
          type="email"
          name="email"
          placeholder="Email"
          className="w-full px-4 py-2 border rounded"
        />
        {errors.email && (
          <p className="text-red-600 text-sm mt-1">{errors.email}</p>
        )}
      </div>

      <div>
        <input
          type="text"
          name="username"
          placeholder="Username"
          className="w-full px-4 py-2 border rounded"
        />
        {errors.username && (
          <p className="text-red-600 text-sm mt-1">{errors.username}</p>
        )}
      </div>

      <div>
        <input
          type="password"
          name="password"
          placeholder="Password"
          className="w-full px-4 py-2 border rounded"
        />
        {errors.password && (
          <p className="text-red-600 text-sm mt-1">{errors.password}</p>
        )}
      </div>

      <button
        type="submit"
        className="w-full bg-blue-600 text-white py-2 rounded hover:bg-blue-700"
      >
        Sign Up
      </button>
    </form>
  )
}

// API route validation (Next.js App Router)
import { NextRequest, NextResponse } from 'next/server'
import { z } from 'zod'
import { createClient } from '@/lib/supabase/server'

const createPostSchema = z.object({
  title: z.string().min(3).max(200),
  content: z.string().min(10),
  categoryId: z.string().uuid(),
  tags: z.array(z.string()).max(10),
  published: z.boolean().default(false),
})

export async function POST(request: NextRequest) {
  try {
    // Parse and validate request body
    const body = await request.json()
    const validated = createPostSchema.parse(body)

    // Get authenticated user
    const supabase = createClient()
    const { data: { user }, error: authError } = await supabase.auth.getUser()

    if (authError || !user) {
      return NextResponse.json(
        { error: 'Unauthorized' },
        { status: 401 }
      )
    }

    // Additional server-side validation
    // Check if category exists
    const { data: category } = await supabase
      .from('categories')
      .select('id')
      .eq('id', validated.categoryId)
      .single()

    if (!category) {
      return NextResponse.json(
        { error: 'Invalid category' },
        { status: 400 }
      )
    }

    // Check rate limit
    const recentPosts = await supabase
      .from('posts')
      .select('id', { count: 'exact', head: true })
      .eq('user_id', user.id)
      .gte('created_at', new Date(Date.now() - 3600000).toISOString())

    if (recentPosts.count && recentPosts.count >= 5) {
      return NextResponse.json(
        { error: 'Rate limit exceeded. Max 5 posts per hour.' },
        { status: 429 }
      )
    }

    // Create post
    const { data: post, error: dbError } = await supabase
      .from('posts')
      .insert({
        ...validated,
        user_id: user.id,
        category_id: validated.categoryId,
      })
      .select()
      .single()

    if (dbError) {
      throw dbError
    }

    return NextResponse.json({ post }, { status: 201 })
  } catch (error) {
    if (error instanceof z.ZodError) {
      return NextResponse.json(
        {
          error: 'Validation failed',
          details: error.errors,
        },
        { status: 400 }
      )
    }

    console.error('Create post error:', error)
    return NextResponse.json(
      { error: 'Internal server error' },
      { status: 500 }
    )
  }
}

// Sanitization utilities
import DOMPurify from 'isomorphic-dompurify'

function sanitizeHTML(html: string): string {
  return DOMPurify.sanitize(html, {
    ALLOWED_TAGS: ['p', 'br', 'strong', 'em', 'u', 'a', 'ul', 'ol', 'li'],
    ALLOWED_ATTR: ['href', 'target'],
  })
}

function sanitizeInput(input: string): string {
  // Remove dangerous characters
  return input
    .replace(/[<>"']/g, '')  // Remove HTML chars
    .trim()
}