Data Validation and Constraints for Self-Hosted Supabase: A Complete Guide

When you self-host Supabase, you own the entire stack—including responsibility for data integrity. Unlike managed services where guardrails are pre-configured, self-hosted deployments require you to think deliberately about validation at the database level. Getting this right prevents bad data from corrupting your application, reduces debugging time, and makes your schema self-documenting.

This guide covers PostgreSQL's constraint system, enums, domains, and practical patterns for building bulletproof data validation in your self-hosted Supabase instance.

Why Database-Level Validation Matters

Application-level validation is necessary but insufficient. Users can bypass your frontend, APIs can be called directly, and migrations can introduce inconsistent data. PostgreSQL constraints are your last line of defense—they guarantee data integrity regardless of how data enters your database.

For self-hosters, this matters even more. Without Supabase Cloud's managed safeguards, you need to implement these protections yourself. The good news? PostgreSQL gives you powerful tools that are often underused.

Check Constraints: Flexible Validation Rules

Check constraints are the most versatile validation mechanism in PostgreSQL. They let you define arbitrary conditions that must be true for every row.

Basic Check Constraints

-- Ensure positive values
ALTER TABLE products 
ADD CONSTRAINT positive_price CHECK (price > 0);

-- Validate email format (basic)
ALTER TABLE users 
ADD CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Restrict string values
ALTER TABLE orders 
ADD CONSTRAINT valid_status CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'));

Safe Constraint Migration for Large Tables

Adding constraints to large production tables can lock them for extended periods. In 2026, the recommended approach is a two-step "safe migration":

-- Step 1: Add constraint as NOT VALID (instant, no lock)
ALTER TABLE large_orders 
ADD CONSTRAINT valid_amount CHECK (amount >= 0) NOT VALID;

-- Step 2: Validate in background (doesn't block reads/writes)
ALTER TABLE large_orders VALIDATE CONSTRAINT valid_amount;

This pattern is essential for self-hosters running production workloads. The NOT VALID keyword applies the rule to new data immediately while skipping validation of existing rows. The separate VALIDATE command checks existing data without blocking other operations.

Multi-Column Constraints

Check constraints can reference multiple columns:

ALTER TABLE events 
ADD CONSTRAINT valid_date_range CHECK (end_date > start_date);

ALTER TABLE discounts 
ADD CONSTRAINT valid_discount_type CHECK (
  (discount_type = 'percentage' AND discount_value BETWEEN 0 AND 100) OR
  (discount_type = 'fixed' AND discount_value > 0)
);

Enums: Fixed Value Sets

Enums define a strict set of allowed values at the type level. They're ideal for status fields, categories, or any column with a small, known set of options.

Creating and Using Enums

-- Create the enum type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- Use in table definition
CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  status order_status NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ DEFAULT now()
);

-- Insert with enum value
INSERT INTO orders (status) VALUES ('processing');

-- This will fail with a clear error
INSERT INTO orders (status) VALUES ('invalid_status');
-- ERROR: invalid input value for enum order_status: "invalid_status"

Enum Trade-offs

Enums offer performance benefits—PostgreSQL stores them as integers internally, making comparisons fast. They also make your schema self-documenting.

However, modifying enums requires schema changes:

-- Adding a value (safe)
ALTER TYPE order_status ADD VALUE 'on_hold';

-- Removing a value (complex - requires recreating the type)
-- This is why enums work best for stable value sets

For values that change frequently, consider using a lookup table with foreign key constraints instead.

Self-Hosted Studio Consideration

When using custom schemas in self-hosted Supabase, enum types may not appear in Studio's "Database > Enums" view. They're still functional—you can query them via SQL:

SELECT t.typname AS enum_name, e.enumlabel AS enum_value
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE t.typname = 'order_status';

Domains: Reusable Validation Types

Domains are custom types that wrap existing types with added constraints. They're perfect when you need the same validation rule across multiple tables.

Creating Domains

-- Email domain with format validation
CREATE DOMAIN email_address AS TEXT
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

-- Positive money domain
CREATE DOMAIN positive_money AS NUMERIC(10, 2)
CHECK (VALUE > 0);

-- URL domain
CREATE DOMAIN url AS TEXT
CHECK (VALUE ~* '^https?://[^\s]+$');

-- Phone number domain (E.164 format)
CREATE DOMAIN phone_number AS TEXT
CHECK (VALUE ~ '^\+[1-9]\d{1,14}$');

Using Domains Across Tables

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email email_address NOT NULL UNIQUE,
  phone phone_number,
  website url
);

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  billing_email email_address NOT NULL,
  support_email email_address
);

Now the email format is validated consistently everywhere. If you need to update the validation rule, you modify the domain once.

JSON Schema Validation

Supabase includes the pg_jsonschema extension for validating JSON data against JSON Schema documents. This is particularly useful for flexible data structures that still need some structure.

Enable the Extension

CREATE EXTENSION IF NOT EXISTS pg_jsonschema;

Add JSON Validation

CREATE TABLE user_preferences (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users NOT NULL,
  settings JSONB NOT NULL,
  CONSTRAINT valid_settings CHECK (
    jsonb_matches_schema(
      '{
        "type": "object",
        "required": ["theme", "notifications"],
        "properties": {
          "theme": {"type": "string", "enum": ["light", "dark", "system"]},
          "notifications": {"type": "boolean"},
          "language": {"type": "string", "pattern": "^[a-z]{2}$"}
        },
        "additionalProperties": false
      }',
      settings
    )
  )
);

This validates that every settings value conforms to your schema—catching errors at insert time rather than in application code.

Foreign Key Constraints for Referential Integrity

Foreign keys ensure relationships between tables remain valid:

CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users ON DELETE CASCADE,
  organization_id UUID REFERENCES organizations ON DELETE SET NULL,
  name TEXT NOT NULL
);

The ON DELETE clause determines what happens when the referenced row is deleted:

CASCADE: Delete the dependent rows
SET NULL: Set the foreign key to NULL
RESTRICT (default): Prevent deletion if references exist

For self-hosted Supabase deployments, these constraints mirror real-world logic and prevent orphaned records without application code intervention.

NOT NULL and UNIQUE Constraints

These fundamental constraints are often overlooked:

CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  description TEXT, -- nullable is intentional
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Be explicit about which columns can be NULL. The default nullable behavior can lead to unexpected data issues.

Combining Constraints with RLS

Constraints work alongside Row Level Security policies. RLS controls who can access data; constraints control what data is valid:

-- Constraint: ensure valid status values
ALTER TABLE tickets 
ADD CONSTRAINT valid_priority CHECK (priority IN ('low', 'medium', 'high', 'urgent'));

-- RLS: users can only see their own tickets
CREATE POLICY "Users see own tickets" ON tickets
FOR SELECT USING (auth.uid() = user_id);

-- RLS: users can only set priority up to 'high'
CREATE POLICY "Users create tickets" ON tickets
FOR INSERT WITH CHECK (
  auth.uid() = user_id 
  AND priority IN ('low', 'medium', 'high')
);

TypeScript Type Generation

When you use constraints effectively, your generated TypeScript types become more accurate:

npx supabase gen types typescript --db-url "postgres://..." > database.types.ts

The generated types reflect NOT NULL constraints as non-nullable properties and detect generated columns. However, check constraints and domains appear as their base types—you'll need manual type narrowing for those.

Practical Pattern: Audit Fields

A common pattern for self-hosted deployments is adding audit fields with constraints:

-- Add audit columns to any table
ALTER TABLE orders
ADD COLUMN created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
ADD COLUMN created_by UUID REFERENCES auth.users,
ADD CONSTRAINT updated_after_created CHECK (updated_at >= created_at);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Managing Constraints in Migrations

For self-hosted Supabase, maintain your constraints in migration files:

-- migrations/20260528_add_order_constraints.sql

-- Add constraints to existing table
ALTER TABLE orders
ADD CONSTRAINT positive_total CHECK (total >= 0),
ADD CONSTRAINT valid_status CHECK (status IN ('draft', 'pending', 'completed', 'cancelled'));

-- Create a domain for reuse
CREATE DOMAIN positive_integer AS INTEGER CHECK (VALUE > 0);

-- Use in new table
CREATE TABLE order_items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products,
  quantity positive_integer NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0)
);

Debugging Constraint Violations

When constraints fail, PostgreSQL provides clear error messages:

ERROR: new row for relation "orders" violates check constraint "positive_total"
DETAIL: Failing row contains (550e8400-e29b-41d4-a716-446655440000, -50.00, pending, ...).

For self-hosted instances, you can query existing constraints:

SELECT 
  conname AS constraint_name,
  contype AS type,
  pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;