Supabase Hardening Guide: From Flat Tables to Enterprise Relational Schema

When tools like v0 or Lovable initialize a database in Supabase, they often generate a single table with tons of nullable columns, storing complex JSON blobs or stringified arrays.

This flat-table approach is rapid for testing features, but it has severe limitations when you launch to actual users. It introduces data duplication, makes querying slow, and results in security vulnerabilities.

Here is why flat schemas fail, and how to structure and harden your Supabase PostgreSQL database for scaling.

1. Why AI "Flat Tables" Break in Production

AI code tools tend to create tables that mirror a simple Excel sheet: one table containing everything. For example, a orders table might list customer names, emails, billing addresses, product details, prices, and status updates directly in every order row.

Data Inconsistency

If a user updates their billing address, you must locate and update every historical order row associated with them. If a single row fails, your data gets corrupted.

Redundant Storage

Storing customer info like customer_name and customer_email millions of times wastes storage and slows down index speeds.

Zero Relational Integrity

Without foreign key constraints, you can successfully insert an order for a customer ID that does not exist.

2. Normalizing Your Schema

To move from flat tables to a proper relational schema, we use normalization. This involves separating unrelated data types into their own tables and linking them using primary/foreign key keys.

erDiagram
    USERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : ordered-in
    
    USERS {
        uuid id PK
        string email
        string name
    }
    ORDERS {
        uuid id PK
        uuid user_id FK
        timestamp created_at
        string status
    }
    ORDER_ITEMS {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        integer quantity
        numeric price
    }
    PRODUCTS {
        uuid id PK
        string title
        numeric cost
    }

Instead of a single flat table, structure your database using distinct tables:

1. profiles / users: Holds user account information.
2. orders: Holds metadata about a transaction (e.g., date, status).
3. order_items: Associates a products table with orders (quantity, price at purchase).
4. products: Holds core product catalog information.

3. Implementing PostgreSQL Foreign Keys and Cascades

To ensure relational integrity, define relationships explicitly. Here is the SQL schema to build a secure, normalized relationship structure:

-- Create core profiles table linked to Supabase auth.users
CREATE TABLE profiles (
  id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
  full_name TEXT,
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Create products table
CREATE TABLE products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  title TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  sku TEXT UNIQUE NOT NULL
);

-- Create orders table
CREATE TABLE orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES profiles(id) ON DELETE RESTRICT NOT NULL,
  status TEXT DEFAULT 'pending'::text NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

Note on Delete Rules:

• ON DELETE CASCADE on profiles ensures that if a user deletes their account, their profile records are automatically cleaned up.
• ON DELETE RESTRICT on orders prevents deleting a user profile if they have active order records, preserving your accounting audit logs.

4. Hardening RLS with Authenticated Policies

Once your tables are relational, you must secure the queries. In Supabase, if RLS policies are not properly structured, users might query related tables they shouldn't access.

For example, when reading order_items, the user should only see rows that belong to their own orders:

-- Enable RLS on order items
ALTER TABLE order_items ENABLE ROW LEVEL SECURITY;

-- Secure order items using a nested exists subquery
CREATE POLICY "Users can only read items in their own orders"
ON order_items
FOR SELECT
TO authenticated
USING (
  EXISTS (
    SELECT 1 FROM orders
    WHERE orders.id = order_items.order_id
    AND orders.user_id = auth.uid()
  )
);

This ensures a user cannot query an order_id belonging to someone else to retrieve product transactions.

5. Adding PostgreSQL Indexes for Scalability

AI tools never create custom indexes because they do not know what queries your users will run.

The Risk

When tables grow to 100,000+ rows, running SELECT * FROM orders WHERE user_id = '...' forces PostgreSQL to perform a slow "sequential scan" of the entire table, leading to database timeouts.

The Fix

Always index foreign keys and columns commonly used in WHERE, JOIN, or ORDER BY clauses.

-- Index foreign keys
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Index commonly ordered timestamp queries
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);