Building Real-Time Multiplayer Apps with Supabase Realtime and PostgreSQL Triggers

Real-time collaboration is no longer a nice-to-have feature—it's table stakes for modern SaaS products. Whether you're building a collaborative document editor, a live project tracker, or a multiplayer gaming interface, users expect to see changes instantly across all connected clients.

The challenge? Coordinating state changes across multiple clients and the database without building complex WebSocket infrastructure yourself. Supabase Realtime combined with PostgreSQL triggers gives you a powerful, maintainable solution that scales.

This guide walks you through building genuine real-time multiplayer features without reinventing the wheel.

Why Supabase Realtime + PostgreSQL Triggers?

Traditional approaches to real-time features require either:

• Rolling your own WebSocket server (expensive, hard to scale)
• Polling the database constantly (wasteful, laggy)
• Using third-party services like Firebase (vendor lock-in, limited control)

Supabase Realtime solves this by giving you PostgreSQL's change data capture (CDC) out of the box. Any INSERT, UPDATE, or DELETE in your database automatically broadcasts to all subscribed clients. PostgreSQL triggers let you enforce business logic at the database layer before data even hits your frontend.

The result: real-time sync that's fast, scalable, and doesn't require building custom backend logic for every collaborative feature.

Setting Up Supabase Realtime

First, enable Realtime on your database. This is done through the Supabase dashboard:

1. Open your Supabase project dashboard
2. Navigate to Database → Replication
3. Enable replication for the tables you want to monitor in real-time
4. Choose which events (INSERT, UPDATE, DELETE) to broadcast

For a collaborative document editor, you'd enable replication on your documents table:

-- Enable Realtime for the documents table
ALTER TABLE documents REPLICA IDENTITY FULL;

Building a Collaborative Document Editor

Let's build a practical example: a shared notes app where multiple users can edit the same document in real-time.

1. Database Schema

-- Create the documents table
CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  owner_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create the collaborators table
CREATE TABLE document_collaborators (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT DEFAULT 'viewer', -- 'editor' or 'viewer'
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(document_id, user_id)
);

-- Create a changelog table for audit trails
CREATE TABLE document_changes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id UUID NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES auth.users(id),
  change_type TEXT, -- 'insert', 'update', 'delete'
  old_content TEXT,
  new_content TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

2. PostgreSQL Trigger for Audit Logging

Create a trigger that logs every change to your document:

CREATE OR REPLACE FUNCTION log_document_change()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO document_changes (document_id, user_id, change_type, old_content, new_content)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    auth.uid(),
    TG_OP,
    CASE WHEN TG_OP = 'DELETE' THEN OLD.content ELSE NULL END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE NEW.content END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER document_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON documents
FOR EACH ROW
EXECUTE FUNCTION log_document_change();

3. Frontend: React + Supabase Client

Here's how to subscribe to real-time changes in your React component:

import { useEffect, useState } from 'react';
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.REACT_APP_SUPABASE_URL,
  process.env.REACT_APP_SUPABASE_ANON_KEY
);

export function DocumentEditor({ documentId }) {
  const [content, setContent] = useState('');
  const [activeUsers, setActiveUsers] = useState(0);

  useEffect(() => {
    // Fetch initial document
    const fetchDocument = async () => {
      const { data } = await supabase
        .from('documents')
        .select('content')
        .eq('id', documentId)
        .single();
      setContent(data?.content || '');
    };

    fetchDocument();

    // Subscribe to real-time updates
    const subscription = supabase
      .from(`documents:id=eq.${documentId}`)
      .on('UPDATE', (payload) => {
        setContent(payload.new.content);
      })
      .subscribe();

    // Cleanup on unmount
    return () => {
      subscription.unsubscribe();
    };
  }, [documentId]);

  // Handle local changes
  const handleChange = async (newContent) => {
    setContent(newContent);
    
    // Update database
    await supabase
      .from('documents')
      .update({ content: newContent, updated_at: new Date() })
      .eq('id', documentId);
  };

  return (
    <textarea
      value={content}
      onChange={(e) => handleChange(e.target.value)}
      className="w-full h-96 p-4 border rounded"
      placeholder="Start typing..."
    />
  );
}

Handling Offline-First with Conflict Resolution

In multiplayer environments, merge conflicts are inevitable. Here's a practical approach:

-- Add a version column for conflict detection
ALTER TABLE documents ADD COLUMN version INT DEFAULT 1;

-- Create a function that increments version on update
CREATE OR REPLACE FUNCTION increment_document_version()
RETURNS TRIGGER AS $$
BEGIN
  NEW.version = OLD.version + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER version_increment_trigger
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION increment_document_version();

Then in your client code, implement optimistic updates with fallback:

const handleSave = async (newContent) => {
  const currentVersion = document.version;
  
  try {
    const { data, error } = await supabase
      .from('documents')
      .update({ content: newContent })
      .eq('id', documentId)
      .eq('version', currentVersion); // Only update if version matches
    
    if (error) {
      // Conflict detected—fetch latest version
      const { data: latest } = await supabase
        .from('documents')
        .select('content, version')
        .eq('id', documentId)
        .single();
      
      setContent(latest.content);
      showConflictNotification('Your changes conflicted. Latest version loaded.');
    }
  } catch (err) {
    console.error('Save failed:', err);
  }
};

Performance Tips for Scale

1. Granular subscriptions: Only subscribe to the specific columns you need
2. Batch updates: Group rapid changes into single database commits
3. Debouncing: Debounce rapid text input to avoid thrashing the database
4. Row-level security: Use PostgreSQL RLS policies to prevent unauthorized access
5. Broadcast filters: Subscribe to changes only from specific users or roles

Real-World Gotchas

• Realtime limits: Supabase Realtime has connection and message rate limits; design your app accordingly
• Network latency: Always assume there's a lag; show optimistic updates instantly and roll back on failure
• Database writes: Too many concurrent writers will cause contention; consider FIFO queues for high-concurrency scenarios
• Webhook delays: Triggers fire immediately, but replication to Realtime can have millisecond delays

Conclusion

Supabase Realtime + PostgreSQL triggers give you real-time multiplayer features that are performant, maintainable, and don't require custom infrastructure. The database becomes the source of truth, and your frontend simply syncs to it.

Start small—enable Realtime on one table, build a basic subscription, and iterate. Once you have the pattern down, scaling to more complex collaborative features becomes straightforward.