Supabase Architecture Design: PostgreSQL + Realtime Features Practice

Supabase provides a powerful backend-as-a-service built on PostgreSQL with realtime capabilities. As you build modern applications, understanding how to design effective Supabase architectures becomes crucial for scalability, performance, and user experience. This guide shows you how to design Supabase architectures, including PostgreSQL best practices and realtime feature implementation. You'll learn how to organize your database schema, implement realtime subscriptions, optimize performance, and follow best practices that ensure your application scales effectively. Whether you're building a new application or optimizing an existing one, these architecture patterns will help you leverage Supabase's full potential. We'll explore real-world examples, common patterns, and practical strategies that you can apply immediately to your projects.

Understanding Supabase Architecture

Supabase combines several powerful technologies:

Core Components:

• PostgreSQL database
• Realtime subscriptions
• Authentication
• Storage
• Edge Functions

Why Supabase?

Benefits:

• PostgreSQL reliability and features
• Real-time capabilities
• Built-in authentication
• Easy to scale
• Open source

Database Design Patterns

Schema Organization

Organize your database schema logically:

-- User-related tables
CREATE TABLE users (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  email TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Feature-specific tables
CREATE TABLE qr_codes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES users(id),
  content TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Analytics tables
CREATE TABLE qr_analytics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  qr_code_id UUID REFERENCES qr_codes(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

Indexing Strategy

Create indexes for frequently queried columns:

-- Index for user lookups
CREATE INDEX idx_qr_codes_user_id ON qr_codes(user_id);

-- Index for date queries
CREATE INDEX idx_qr_codes_created_at ON qr_codes(created_at DESC);

-- Composite index for common queries
CREATE INDEX idx_qr_codes_user_created ON qr_codes(user_id, created_at DESC);

Realtime Features

When implementing realtime features, you'll also need to ensure proper security. Learn about Row Level Security best practices to secure your realtime subscriptions and ensure users only receive updates for data they're authorized to see.

Enabling Realtime

Enable realtime on tables that need live updates:

-- Enable realtime on qr_codes table
ALTER PUBLICATION supabase_realtime ADD TABLE qr_codes;

-- Enable realtime on analytics table
ALTER PUBLICATION supabase_realtime ADD TABLE qr_analytics;

Realtime Subscriptions

Subscribe to database changes in your application:

// lib/supabase/realtime.ts
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

// Subscribe to QR code changes
export function subscribeToQRCodes(
  userId: string,
  callback: (qrCode: QRCode) => void
) {
  return supabase
    .channel('qr_codes_changes')
    .on(
      'postgres_changes',
      {
        event: '*', // INSERT, UPDATE, DELETE
        schema: 'public',
        table: 'qr_codes',
        filter: `user_id=eq.${userId}`,
      },
      (payload) => {
        callback(payload.new as QRCode);
      }
    )
    .subscribe();
}

Real-World Example

// components/qr/QRCodeList.tsx
'use client';

import { useEffect, useState } from 'react';
import { subscribeToQRCodes } from '@/lib/supabase/realtime';

export function QRCodeList({ userId }: { userId: string }) {
  const [qrCodes, setQrCodes] = useState<QRCode[]>([]);

  useEffect(() => {
    // Subscribe to realtime updates
    const subscription = subscribeToQRCodes(userId, (newQRCode) => {
      setQrCodes(prev => {
        // Update list when new QR code is created
        const exists = prev.find(qr => qr.id === newQRCode.id);
        if (exists) {
          return prev.map(qr => 
            qr.id === newQRCode.id ? newQRCode : qr
          );
        }
        return [...prev, newQRCode];
      });
    });

    return () => {
      subscription.unsubscribe();
    };
  }, [userId]);

  return (
    <div>
      {qrCodes.map(qr => (
        <QRCodeCard key={qr.id} qrCode={qr} />
      ))}
    </div>
  );
}

ContentQR Architecture

Database Schema

-- Users table (extends auth.users)
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  email TEXT,
  subscription_tier TEXT DEFAULT 'free',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- QR Codes table
CREATE TABLE qr_codes (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES user_profiles(id) NOT NULL,
  content TEXT NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('url', 'text', 'email', 'phone')),
  settings JSONB DEFAULT '{}',
  scan_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Analytics table
CREATE TABLE qr_analytics (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  qr_code_id UUID REFERENCES qr_codes(id) ON DELETE CASCADE,
  event_type TEXT NOT NULL CHECK (event_type IN ('scan', 'view', 'click')),
  user_agent TEXT,
  ip_address INET,
  country TEXT,
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_qr_codes_user_id ON qr_codes(user_id);
CREATE INDEX idx_qr_codes_created_at ON qr_codes(created_at DESC);
CREATE INDEX idx_qr_analytics_qr_code_id ON qr_analytics(qr_code_id);
CREATE INDEX idx_qr_analytics_created_at ON qr_analytics(created_at DESC);

RLS Policies

-- Enable RLS
ALTER TABLE qr_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE qr_analytics ENABLE ROW LEVEL SECURITY;

-- QR Codes policies
CREATE POLICY "Users can view own QR codes"
ON qr_codes FOR SELECT
USING (auth.uid() = user_id);

CREATE POLICY "Users can create QR codes"
ON qr_codes FOR INSERT
WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own QR codes"
ON qr_codes FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Analytics policies
CREATE POLICY "Users can view own analytics"
ON qr_analytics FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM qr_codes
    WHERE qr_codes.id = qr_analytics.qr_code_id
    AND qr_codes.user_id = auth.uid()
  )
);

Realtime Implementation

QR Code Creation Updates

// lib/supabase/qr-codes.ts
import { createClient } from '@supabase/supabase-js';

export async function createQRCode(data: {
  content: string;
  type: string;
  userId: string;
}) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );

  const { data: qrCode, error } = await supabase
    .from('qr_codes')
    .insert({
      content: data.content,
      type: data.type,
      user_id: data.userId,
    })
    .select()
    .single();

  if (error) throw error;
  return qrCode;
}

// Subscribe to new QR codes
export function subscribeToNewQRCodes(
  userId: string,
  onNewQRCode: (qrCode: QRCode) => void
) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );

  return supabase
    .channel(`qr_codes:${userId}`)
    .on(
      'postgres_changes',
      {
        event: 'INSERT',
        schema: 'public',
        table: 'qr_codes',
        filter: `user_id=eq.${userId}`,
      },
      (payload) => {
        onNewQRCode(payload.new as QRCode);
      }
    )
    .subscribe();
}

Analytics Updates

// Subscribe to analytics updates
export function subscribeToAnalytics(
  qrCodeId: string,
  onUpdate: (analytics: Analytics) => void
) {
  const supabase = createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
  );

  return supabase
    .channel(`analytics:${qrCodeId}`)
    .on(
      'postgres_changes',
      {
        event: 'INSERT',
        schema: 'public',
        table: 'qr_analytics',
        filter: `qr_code_id=eq.${qrCodeId}`,
      },
      (payload) => {
        onUpdate(payload.new as Analytics);
      }
    )
    .subscribe();
}

Best Practices

1. Use Connection Pooling

Supabase handles connection pooling automatically, but be mindful:

// ✅ Good: Reuse Supabase client
const supabase = createClient(url, key);

// ❌ Bad: Creating new clients frequently
function getData() {
  const supabase = createClient(url, key); // New client each time
  return supabase.from('table').select();
}

2. Optimize Queries

Use select to fetch only needed columns:

// ✅ Good: Select specific columns
const { data } = await supabase
  .from('qr_codes')
  .select('id, content, created_at')
  .eq('user_id', userId);

// ❌ Bad: Fetch all columns
const { data } = await supabase
  .from('qr_codes')
  .select('*')
  .eq('user_id', userId);

3. Handle Realtime Subscriptions

Always clean up subscriptions:

useEffect(() => {
  const subscription = subscribeToQRCodes(userId, handleUpdate);
  
  return () => {
    subscription.unsubscribe(); // Clean up
  };
}, [userId]);

4. Use Database Functions

For complex operations, use PostgreSQL functions:

-- Function to get QR code statistics
CREATE OR REPLACE FUNCTION get_qr_statistics(p_user_id UUID)
RETURNS TABLE (
  total_codes BIGINT,
  total_scans BIGINT,
  recent_codes BIGINT
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    COUNT(*)::BIGINT as total_codes,
    COALESCE(SUM(scan_count), 0)::BIGINT as total_scans,
    COUNT(*) FILTER (WHERE created_at > NOW() - INTERVAL '7 days')::BIGINT as recent_codes
  FROM qr_codes
  WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

5. Monitor Performance

Use Supabase dashboard to monitor:

• Query performance
• Connection usage
• Realtime subscriptions
• Database size

Common Patterns

Pattern 1: Pagination

async function getQRCodes(userId: string, page: number = 1, pageSize: number = 10) {
  const from = (page - 1) * pageSize;
  const to = from + pageSize - 1;

  const { data, error, count } = await supabase
    .from('qr_codes')
    .select('*', { count: 'exact' })
    .eq('user_id', userId)
    .order('created_at', { ascending: false })
    .range(from, to);

  return { data, count, totalPages: Math.ceil((count || 0) / pageSize) };
}

Pattern 2: Upsert Operations

async function upsertQRCode(qrCode: QRCode) {
  const { data, error } = await supabase
    .from('qr_codes')
    .upsert({
      id: qrCode.id,
      content: qrCode.content,
      updated_at: new Date().toISOString(),
    }, {
      onConflict: 'id',
    })
    .select()
    .single();

  return { data, error };
}

Pattern 3: Batch Operations

async function createMultipleQRCodes(qrCodes: Omit<QRCode, 'id'>[]) {
  const { data, error } = await supabase
    .from('qr_codes')
    .insert(qrCodes)
    .select();

  return { data, error };
}

Conclusion

Supabase provides a powerful foundation for building scalable applications with PostgreSQL and realtime capabilities. By following these architecture patterns and best practices, you can build robust, secure, and performant applications. The key is to design your schema with RLS in mind, optimize for performance with proper indexing, and use realtime features judiciously where they add value.

Key Takeaways:

• Design schema with RLS in mind from the start
• Use indexes strategically for performance optimization
• Enable realtime only where needed to reduce overhead
• Clean up subscriptions properly to prevent memory leaks
• Monitor performance regularly and optimize based on metrics
• Use database functions for complex operations to reduce round trips

Next Steps:

• Review your current Supabase schema and identify optimization opportunities
• Implement RLS policies for all user data tables
• Set up realtime subscriptions only for features that require live updates
• Create indexes for frequently queried columns
• Monitor query performance and optimize slow queries
• Document your architecture decisions for future reference

For more Supabase insights, check out our articles on Row Level Security Best Practices and ContentQR Architecture Evolution.