Database Design for Next.js + Supabase: Optimization Guide

-- Good: Normalized design around business entities
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);
 
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);
 
CREATE TABLE user_organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
  role TEXT NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, organization_id)
);
 
-- Bad: Denormalized design that will cause problems
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY,
  email TEXT,
  organization_name TEXT, -- Duplicated data
  organization_slug TEXT, -- Will become inconsistent
  user_role TEXT,
  organization_created_at TIMESTAMPTZ -- Belongs in organizations table
);

-- Use appropriate data types for better performance
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price DECIMAL(10,2) NOT NULL, -- Use DECIMAL for money, not FLOAT
  tags TEXT[], -- Use arrays for lists
  metadata JSONB, -- Use JSONB for flexible data
  status product_status DEFAULT 'draft', -- Use ENUMs for fixed values
  created_at TIMESTAMPTZ DEFAULT NOW()
);
 
-- Create custom types for better type safety
CREATE TYPE product_status AS ENUM ('draft', 'published', 'archived');
CREATE TYPE user_role AS ENUM ('admin', 'editor', 'viewer');

-- Always use foreign key constraints
CREATE TABLE posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  author_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  
  -- Add check constraints for business rules
  CONSTRAINT valid_title_length CHECK (length(title) >= 1 AND length(title) <= 200),
  CONSTRAINT published_posts_have_content CHECK (
    published_at IS NULL OR (content IS NOT NULL AND length(content) > 0)
  )
);
 
-- Create indexes on foreign keys for better JOIN performance
CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_organization_id ON posts(organization_id);

-- BTREE indexes (default) - best for equality and range queries
CREATE INDEX idx_users_email ON users(email); -- Unique lookups
CREATE INDEX idx_posts_created_at ON posts(created_at); -- Date ranges
 
-- Composite indexes for multi-column queries
CREATE INDEX idx_posts_org_status ON posts(organization_id, status);
CREATE INDEX idx_posts_author_published ON posts(author_id, published_at) 
  WHERE published_at IS NOT NULL;
 
-- GIN indexes for JSONB and array operations
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);
CREATE INDEX idx_products_tags ON products USING GIN(tags);
 
-- Partial indexes for filtered queries
CREATE INDEX idx_published_posts ON posts(created_at) 
  WHERE published_at IS NOT NULL;
 
-- Expression indexes for computed values
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  query,
  calls,
  mean_exec_time,
  total_exec_time,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

CREATE OR REPLACE FUNCTION get_slow_queries(min_duration_ms NUMERIC DEFAULT 100)
RETURNS TABLE (query TEXT, calls BIGINT, mean_exec_time DOUBLE PRECISION, total_exec_time DOUBLE PRECISION)
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT query, calls, mean_exec_time, total_exec_time
  FROM pg_stat_statements
  WHERE mean_exec_time >= min_duration_ms
  ORDER BY mean_exec_time DESC
  LIMIT 50;
$$;

// lib/database/monitoring.ts
import { createClient } from '@supabase/supabase-js'
 
// Calls the get_slow_queries() function defined above — a function
// this project owns, not a Supabase/PostgreSQL built-in.
export async function analyzeSlowQueries(minDurationMs = 100) {
  const supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
 
  const { data: slowQueries, error } = await supabase.rpc('get_slow_queries', {
    min_duration_ms: minDurationMs
  })
 
  if (error) throw error
  return slowQueries
}

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts WHERE organization_id = 'some-uuid' AND published_at IS NOT NULL;

-- Monitor index usage
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_tup_read,
  idx_tup_fetch,
  idx_scan
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;
 
-- Find unused indexes
SELECT 
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes 
WHERE idx_scan = 0
  AND indexrelid NOT IN (
    SELECT indexrelid FROM pg_index WHERE indisunique
  );
 
-- Reindex when needed (usually not required in modern PostgreSQL)
REINDEX INDEX CONCURRENTLY idx_posts_created_at;

// lib/queries/posts.ts
import { createClient } from '@supabase/supabase-js'
 
export class PostQueries {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
 
  // Good: Use indexes effectively
  async getPublishedPostsByOrg(orgId: string, limit = 20, offset = 0) {
    const { data, error } = await this.supabase
      .from('posts')
      .select(`
        id,
        title,
        excerpt,
        published_at,
        author:users(name, avatar_url)
      `)
      .eq('organization_id', orgId)
      .not('published_at', 'is', null)
      .order('published_at', { ascending: false })
      .range(offset, offset + limit - 1)
 
    if (error) throw error
    return data
  }
 
  // Bad: This query will be slow without proper indexes
  async searchPostsSlowly(searchTerm: string) {
    const { data } = await this.supabase
      .from('posts')
      .select('*')
      .or(`title.ilike.%${searchTerm}%,content.ilike.%${searchTerm}%`)
      .order('created_at')
 
    return data
  }
 
  // Good: Use full-text search with proper indexes
  async searchPostsEfficiently(searchTerm: string, orgId: string) {
    const { data, error } = await this.supabase
      .rpc('search_posts', {
        search_term: searchTerm,
        org_id: orgId
      })
 
    if (error) throw error
    return data
  }
}

-- Create a full-text search function
CREATE OR REPLACE FUNCTION search_posts(
  search_term TEXT,
  org_id UUID,
  result_limit INTEGER DEFAULT 20
)
RETURNS TABLE (
  id UUID,
  title TEXT,
  excerpt TEXT,
  published_at TIMESTAMPTZ,
  rank REAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT 
    p.id,
    p.title,
    p.excerpt,
    p.published_at,
    ts_rank(
      to_tsvector('english', p.title || ' ' || COALESCE(p.content, '')),
      plainto_tsquery('english', search_term)
    ) as rank
  FROM posts p
  WHERE p.organization_id = org_id
    AND p.published_at IS NOT NULL
    AND to_tsvector('english', p.title || ' ' || COALESCE(p.content, '')) 
        @@ plainto_tsquery('english', search_term)
  ORDER BY rank DESC, p.published_at DESC
  LIMIT result_limit;
END;
$$ LANGUAGE plpgsql;
 
-- Create indexes for full-text search
CREATE INDEX idx_posts_fts ON posts 
USING GIN(to_tsvector('english', title || ' ' || COALESCE(content, '')));

// lib/queries/analytics.ts
export class AnalyticsQueries {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
 
  // Good: Efficient JOIN with proper filtering
  async getUserEngagementStats(orgId: string, startDate: string, endDate: string) {
    const { data, error } = await this.supabase
      .rpc('get_user_engagement_stats', {
        org_id: orgId,
        start_date: startDate,
        end_date: endDate
      })
 
    if (error) throw error
    return data
  }
}

-- Efficient aggregation function
CREATE OR REPLACE FUNCTION get_user_engagement_stats(
  org_id UUID,
  start_date DATE,
  end_date DATE
)
RETURNS TABLE (
  user_id UUID,
  user_name TEXT,
  posts_created INTEGER,
  comments_made INTEGER,
  total_engagement INTEGER
) AS $$
BEGIN
  RETURN QUERY
  WITH user_posts AS (
    SELECT 
      p.author_id,
      COUNT(*) as post_count
    FROM posts p
    WHERE p.organization_id = org_id
      AND p.created_at::DATE BETWEEN start_date AND end_date
    GROUP BY p.author_id
  ),
  user_comments AS (
    SELECT 
      c.author_id,
      COUNT(*) as comment_count
    FROM comments c
    JOIN posts p ON c.post_id = p.id
    WHERE p.organization_id = org_id
      AND c.created_at::DATE BETWEEN start_date AND end_date
    GROUP BY c.author_id
  )
  SELECT 
    u.id,
    u.name,
    COALESCE(up.post_count, 0)::INTEGER,
    COALESCE(uc.comment_count, 0)::INTEGER,
    (COALESCE(up.post_count, 0) + COALESCE(uc.comment_count, 0))::INTEGER
  FROM users u
  JOIN user_organizations uo ON u.id = uo.user_id
  LEFT JOIN user_posts up ON u.id = up.author_id
  LEFT JOIN user_comments uc ON u.id = uc.author_id
  WHERE uo.organization_id = org_id
    AND (up.post_count > 0 OR uc.comment_count > 0)
  ORDER BY total_engagement DESC;
END;
$$ LANGUAGE plpgsql;

-- Reuses the get_slow_queries() function defined earlier for pg_stat_statements.
 
-- Table sizes, from the real pg_stat_user_tables view + pg_total_relation_size()
-- (https://www.postgresql.org/docs/current/monitoring-stats.html)
CREATE OR REPLACE FUNCTION get_table_sizes()
RETURNS TABLE (table_name TEXT, total_size TEXT, total_size_bytes BIGINT)
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_total_relation_size(relid) AS total_size_bytes
  FROM pg_stat_user_tables
  ORDER BY pg_total_relation_size(relid) DESC;
$$;
 
-- Index usage, from the real pg_stat_user_indexes view shown below
CREATE OR REPLACE FUNCTION get_index_usage_stats()
RETURNS TABLE (schema_name TEXT, table_name TEXT, index_name TEXT, idx_scan BIGINT)
LANGUAGE sql
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT schemaname, tablename, indexname, idx_scan
  FROM pg_stat_user_indexes
  ORDER BY idx_scan DESC;
$$;

// lib/monitoring/database.ts
import { createClient } from '@supabase/supabase-js'
 
// Calls the get_slow_queries(), get_table_sizes() and get_index_usage_stats()
// functions defined above — real functions this project owns, not
// Supabase/PostgreSQL built-ins. `DatabaseMonitoring` is a generic name
// for this pattern; scope/rename it to your actual monitoring surface.
export class DatabaseMonitoring {
  private supabase = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
 
  async getSlowQueries(minDurationMs = 100) {
    const { data, error } = await this.supabase
      .rpc('get_slow_queries', { min_duration_ms: minDurationMs })
 
    if (error) throw error
    return data
  }
 
  async getTableSizes() {
    const { data, error } = await this.supabase
      .rpc('get_table_sizes')
 
    if (error) throw error
    return data
  }
 
  async getIndexUsage() {
    const { data, error } = await this.supabase
      .rpc('get_index_usage_stats')
 
    if (error) throw error
    return data
  }
}
 
// Usage in API route
// app/api/admin/database-stats/route.ts
import { DatabaseMonitoring } from '@/lib/monitoring/database'
 
export async function GET() {
  const monitoring = new DatabaseMonitoring()
  
  const [slowQueries, tableSizes, indexUsage] = await Promise.all([
    monitoring.getSlowQueries(),
    monitoring.getTableSizes(),
    monitoring.getIndexUsage()
  ])
 
  return Response.json({
    slowQueries,
    tableSizes,
    indexUsage,
    timestamp: new Date().toISOString()
  })
}

// lib/database/pool.ts
import { createClient } from '@supabase/supabase-js'
 
// Configure connection pooling for different environments
const getSupabaseConfig = () => {
  const baseConfig = {
    url: process.env.SUPABASE_URL!,
    key: process.env.SUPABASE_SERVICE_ROLE_KEY!,
  }
 
  if (process.env.NODE_ENV === 'production') {
    return {
      ...baseConfig,
      options: {
        db: {
          // Connection pool settings for production
          pool_size: 20,
          max_lifetime: 3600, // 1 hour
          max_idle_time: 300, // 5 minutes
        },
        global: {
          headers: {
            'x-application-name': 'nextjs-app-production'
          }
        }
      }
    }
  }
 
  return baseConfig
}
 
export const supabase = createClient(
  getSupabaseConfig().url,
  getSupabaseConfig().key,
  getSupabaseConfig().options
)

-- Declared partitioned from creation — this is the piece the snippet
-- above skips, and the reason PARTITION OF fails on an existing table.
CREATE TABLE posts_partitioned (LIKE posts INCLUDING ALL)
PARTITION BY RANGE (created_at);
 
CREATE TABLE posts_2026 PARTITION OF posts_partitioned
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
 
CREATE TABLE posts_2025 PARTITION OF posts_partitioned
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
 
-- Create indexes on partitioned tables
CREATE INDEX idx_posts_2026_org_created ON posts_2026(organization_id, created_at);
CREATE INDEX idx_posts_2025_org_created ON posts_2025(organization_id, created_at);
 
-- Archive old data to separate tables
CREATE TABLE posts_archive AS SELECT * FROM posts WHERE created_at < '2024-01-01';
DELETE FROM posts WHERE created_at < '2024-01-01';

// lib/database/replicas.ts
import { createClient } from '@supabase/supabase-js'
 
export class DatabaseRouter {
  private writeClient = createClient(
    process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
 
  private readClient = createClient(
    process.env.SUPABASE_READ_REPLICA_URL || process.env.SUPABASE_URL!,
    process.env.SUPABASE_SERVICE_ROLE_KEY!
  )
 
  // Route read operations to read replica
  getReadClient() {
    return this.readClient
  }
 
  // Route write operations to primary database
  getWriteClient() {
    return this.writeClient
  }
 
  // Smart routing based on operation type
  getClient(operation: 'read' | 'write' = 'read') {
    return operation === 'write' ? this.writeClient : this.readClient
  }
}
 
// Usage in data access layer
export class PostRepository {
  private db = new DatabaseRouter()
 
  async findById(id: string) {
    // Use read replica for queries
    const { data, error } = await this.db.getReadClient()
      .from('posts')
      .select('*')
      .eq('id', id)
      .single()
 
    if (error) throw error
    return data
  }
 
  async create(post: CreatePostData) {
    // Use primary database for writes
    const { data, error } = await this.db.getWriteClient()
      .from('posts')
      .insert(post)
      .select()
      .single()
 
    if (error) throw error
    return data
  }
}

-- migrations/20260320a_add_post_categories_column.sql
-- Safe migration: Add new column with default value
ALTER TABLE posts 
ADD COLUMN category_id UUID REFERENCES categories(id);

-- migrations/20260320b_add_post_categories_index.sql
-- Own file, own non-transactional step — see the note above.
CREATE INDEX CONCURRENTLY idx_posts_category_id ON posts(category_id);

-- migrations/20260320c_backfill_post_categories.sql
-- Backfill data in batches to avoid long locks
DO $$
DECLARE
  batch_size INTEGER := 1000;
  processed INTEGER := 0;
BEGIN
  LOOP
    UPDATE posts 
    SET category_id = (SELECT id FROM categories WHERE name = 'General' LIMIT 1)
    WHERE category_id IS NULL
      AND id IN (
        SELECT id FROM posts 
        WHERE category_id IS NULL 
        LIMIT batch_size
      );
    
    GET DIAGNOSTICS processed = ROW_COUNT;
    EXIT WHEN processed = 0;
    
    -- Small delay to avoid overwhelming the database
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

// scripts/test-migration.ts
import { execFileSync } from 'child_process'
import { createClient } from '@supabase/supabase-js'
 
async function testMigration() {
  const supabase = createClient(
    process.env.SUPABASE_TEST_URL!,
    process.env.SUPABASE_TEST_SERVICE_KEY!
  )
 
  try {
    // Create test data
    await setupTestData(supabase)
 
    // Apply each migration file with psql, against the test database's
    // direct Postgres connection string — not via a JS-client RPC. Three
    // separate files (see above) because CREATE INDEX CONCURRENTLY can't
    // run inside psql -f's implicit transaction alongside the other steps.
    for (const file of [
      './migrations/20260320a_add_post_categories_column.sql',
      './migrations/20260320b_add_post_categories_index.sql',
      './migrations/20260320c_backfill_post_categories.sql',
    ]) {
      execFileSync('psql', [
        process.env.SUPABASE_TEST_DB_URL!,
        '-v', 'ON_ERROR_STOP=1',
        '-f', file,
      ], { stdio: 'inherit' })
    }
 
    // Verify migration results
    await verifyMigration(supabase)
 
    console.log('Migration test passed!')
  } catch (error) {
    console.error('Migration test failed:', error)
    process.exit(1)
  }
}
 
async function setupTestData(supabase: any) {
  // Insert test data that will be affected by migration
  await supabase.from('posts').insert([
    { title: 'Test Post 1', content: 'Content 1' },
    { title: 'Test Post 2', content: 'Content 2' }
  ])
}
 
async function verifyMigration(supabase: any) {
  // Verify the migration worked correctly
  const { data, error } = await supabase
    .from('posts')
    .select('id, category_id')
    .limit(5)
 
  if (error) throw error
  
  // Check that all posts have category_id set
  const postsWithoutCategory = data.filter(post => !post.category_id)
  if (postsWithoutCategory.length > 0) {
    throw new Error('Some posts missing category_id after migration')
  }
}
 
testMigration()

-- Optimize JSONB queries with proper indexes
CREATE INDEX idx_products_metadata_brand ON products 
USING GIN((metadata->'brand'));
 
CREATE INDEX idx_products_metadata_price ON products 
USING BTREE(((metadata->>'price')::NUMERIC));
 
-- Use JSONB operators efficiently
SELECT * FROM products 
WHERE metadata @> '{"brand": "Apple"}' -- Use containment operator
  AND (metadata->>'price')::NUMERIC BETWEEN 100 AND 1000;
 
-- Create partial indexes for common JSONB queries
CREATE INDEX idx_products_featured ON products 
USING GIN(metadata) 
WHERE (metadata->>'featured')::BOOLEAN = true;

EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM posts WHERE organization_id = 'some-uuid' AND published_at IS NOT NULL;

// lib/database/query-analyzer.ts
// Takes the JSON plan produced by `EXPLAIN (ANALYZE, FORMAT JSON) ...`
// (run manually or from a migration/CI step) and flags optimization
// opportunities. This is plain JSON parsing — no RPC, no built-in.
export class QueryPlanAnalyzer {
  analyzePlan(planJson: any[]) {
    return this.parseExecutionPlan(planJson[0].Plan)
  }
 
  private parseExecutionPlan(node: any, analysis = {
    totalCost: node['Total Cost'] || 0,
    slowOperations: [] as Array<{ operation: string; cost: number; recommendation: string }>,
    missingIndexes: [] as string[],
    recommendations: [] as string[]
  }) {
    // `EXPLAIN (FORMAT JSON)` returns a real, documented tree of nodes —
    // https://www.postgresql.org/docs/current/using-explain.html — each
    // with a "Node Type", a "Total Cost", and a "Plans" array of children.
    if (node['Node Type'] === 'Seq Scan' && node['Relation Name']) {
      analysis.missingIndexes.push(node['Relation Name'])
    }
 
    if (node['Node Type'] === 'Sort' && node['Total Cost'] > 1000) {
      analysis.slowOperations.push({
        operation: 'Sort',
        cost: node['Total Cost'],
        recommendation: 'Consider adding an index for the ORDER BY clause'
      })
    }
 
    for (const child of node.Plans || []) {
      this.parseExecutionPlan(child, analysis)
    }
 
    return analysis
  }
}