Watchium - Real-Time Anime Watching Platform

A comprehensive backend system for synchronized anime watching experiences built with Supabase, featuring WebSocket-based real-time synchronization, threaded comments, and intelligent room management.

🚀 Features

Core Functionality

• Real-Time Synchronization: WebSocket-based sync with <100ms latency using Supabase Realtime
• Room Management: Public/private rooms with unique IDs and 6-digit access keys
• Threaded Comments: Episode-based discussion system with persistent anchor messages
• Member Management: Real-time member lists with presence-based sync status
• Host Controls: Dedicated host authority for playback control with automatic transfer
• Auto-Cleanup: Intelligent cleanup of inactive rooms and stale data

Advanced Features

• Presence-Based Sync: Efficient sync status tracking via Supabase Presence system
• Database-Driven Events: Realtime broadcasts automatically triggered by database changes
• Optimized API Usage: Minimal HTTP calls designed for sustainable scaling
• Network Resilience: Automatic reconnection and error handling
• Comprehensive Analytics: Detailed sync metrics and room statistics
• Enterprise Security: Row-level security and access validation
• High Performance: Supports 50+ concurrent users per room

📁 Project Structure

supabase/
├── functions/
│   ├── rooms-create/index.ts         # Create room with Realtime optimization
│   ├── rooms-join/index.ts            # Join room with validation
│   ├── rooms-leave/index.ts           # Leave room with host transfer
│   ├── rooms-delete/index.ts          # Delete room (host only)
│   ├── rooms-list-public/index.ts     # Discover public rooms
│   ├── sync-control/index.ts          # Unified playback control (play/pause/seek)
│   ├── sync-get-host-time/index.ts    # Get current host state
│   ├── comments-create/index.ts       # Post new comments
│   ├── comments-get-by-episode/index.ts # Get episode comments
│   ├── comments-get-history/index.ts  # Get comment analytics
│   ├── members-get-list/index.ts      # Get room members with sync status
│   ├── members-update-status/index.ts # Update member information
│   └── shared/
│       ├── types.ts                   # Shared TypeScript definitions
│       └── utils.ts                   # Utility functions
├── migrations/
│   ├── 001_create_rooms_table.sql
│   ├── 002_create_room_members_table.sql
│   ├── 003_create_comments_table.sql
│   ├── 004_create_sync_states_table.sql
│   ├── 005_create_functions_and_triggers.sql
│   ├── 006_create_rls_policies.sql
│   └── 007_realtime_optimizations.sql  # Realtime performance optimizations
└── docs/
    ├── API_REFERENCE.md                # Complete API documentation
    ├── WEBSOCKET_EVENTS.md             # Real-time event specifications
    ├── DATABASE_SCHEMA.md              # Database architecture guide
    ├── SYNC_ALGORITHM.md               # Synchronization system details
    ├── REALTIME_ARCHITECTURE.md       # WebSocket-first design
    └── REALTIME_CLIENT_GUIDE.md        # Client implementation guide

🛠️ Technology Stack

• Database: PostgreSQL with Supabase
• Real-time Engine: Supabase Realtime (WebSocket) - Primary communication layer
• Backend Services: TypeScript Edge Functions - Essential operations only
• Presence System: Supabase Presence for sync status tracking
• Authentication: Not required (open access as specified)
• Security: Row-Level Security (RLS) with data validation
• Rate Limiting: Database-based controls for critical actions

🌐 Deployment

Project URL: https://ahigvhlqlikkkjsjikcj.supabase.co

Prerequisites

• Supabase account and project
• Supabase CLI (for deployment and management)
• TypeScript knowledge
• WebSocket client capabilities

🚀 Quick Start

1. Setup Supabase Project

# Install Supabase CLI
npm install -g supabase

# Login to Supabase
supabase login

# Link to your project
supabase link --project-ref ahigvhlqlikkkjsjikcj

2. Apply Database Migrations

# Apply all migrations in order
supabase db push

# Verify Realtime is enabled
supabase db shell --command "SELECT * FROM pg_publication_tables WHERE pubname = 'supabase_realtime';"

3. Deploy Edge Functions

# Deploy all functions
supabase functions deploy

# Deploy specific function
supabase functions deploy rooms-create

# List all deployed functions
supabase functions list

4. Configure Environment

Set these environment variables in your Supabase project:

# Required for Edge Functions
SUPABASE_URL=https://ahigvhlqlikkkjsjikcj.supabase.co
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key

# Optional: For scheduled cleanup
CRON_SCHEDULE="0 2 * * *"  # Daily at 2 AM UTC

📖 Usage Guide

Creating a Room

const response = await fetch('https://ahigvhlqlikkkjsjikcj.supabase.co/functions/v1/rooms-create', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    title: "Watching Attack on Titan S1E1 Together",
    anime_id: "aot-season-1",
    anime_title: "Attack on Titan",
    episode_number: 1,
    video_url: "https://example.com/video.mp4",
    host_user_id: "user123",
    host_username: "AnimeFan99",
    is_public: true
  })
});

const { data } = await response.json();
console.log('Room created:', data.room.room_id);
console.log('Realtime channel:', data.realtime_channel);

Joining a Room

const response = await fetch('https://ahigvhlqlikkkjsjikcj.supabase.co/functions/v1/rooms-join', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    room_id: "abc123def4",
    user_id: "user456",
    username: "Viewer123",
    avatar_url: "https://example.com/avatar.jpg"
  })
});

const { data } = await response.json();
console.log('Joined room:', data.room);

Real-time Synchronization

The platform uses Supabase Realtime for efficient, low-latency synchronization:

import { createClient } from '@supabase/supabase-js';

const supabase = createClient(url, key);

// Connect to room channel with presence tracking
const channel = supabase.channel(`room:${roomId}`, {
  config: {
    broadcast: { self: true },
    presence: { key: userId }
  }
});

// Listen for room state changes (play/pause/seek)
channel.on('postgres_changes', 
  { event: 'UPDATE', schema: 'public', table: 'rooms', filter: `room_id=eq.${roomId}` },
  (payload) => {
    // Auto-sync video to host state
    if (payload.new.host_user_id !== userId) {
      video.currentTime = payload.new.current_playback_time;
      if (payload.new.is_playing) {
        video.play();
      } else {
        video.pause();
      }
    }
  }
);

// Track member presence for sync status
setInterval(() => {
  channel.track({
    user_id: userId,
    current_time: video.currentTime,
    is_playing: !video.paused
  });
}, 3000);

// Host controls (minimal HTTP calls)
async function controlPlayback(action) {
  await fetch('/functions/v1/sync/control', {
    method: 'POST',
    body: JSON.stringify({
      room_id: roomId,
      user_id: userId,
      action: action, // 'play', 'pause', or 'seek'
      current_time: video.currentTime
    })
  });
}

await channel.subscribe();

Posting Comments

const response = await fetch('https://ahigvhlqlikkkjsjikcj.supabase.co/functions/v1/comments-create', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    anime_id: "aot-1",
    room_id: "abc123def4",
    episode_number: 1,
    user_id: "user456",
    username: "Viewer123",
    message: "This opening is amazing!",
    video_timestamp: 120.5
  })
});

📊 Architecture & Performance

Realtime-First Design

The system uses Supabase Realtime as the primary communication layer, providing:

• Instant Synchronization: <100ms latency for all real-time events
• Efficient Resource Usage: Presence-based sync eliminates polling
• Automatic Reconnection: Built-in resilience for network interruptions
• Scalable Broadcasting: Database-driven events scale with user count

API Usage Profile

Feature	Communication Method	Frequency	Purpose
Presence Sync	WebSocket	Every 3 seconds	Sync status tracking
Room State Changes	Database Triggers	On host action	Play/pause/seek events
Member Updates	Database Triggers	On join/leave	Member list management
Comments	Database Triggers	On new comment	Real-time chat
Room Management	HTTP API	As needed	Create/join/leave rooms

Realtime Channel Structure

• Room Channel: room:${roomId} - All room-specific real-time events
• Presence Tracking: Automatic sync status via Supabase Presence
• Database Change Events: Automatic broadcasts for table updates
• Minimal HTTP: Essential operations only (room management, host controls)

Database Configuration

The system is optimized for high-concurrency real-time applications:

• Concurrent Users: 50+ users per room with minimal performance impact
• Room Cleanup: Automatic deletion after 24 hours of inactivity
• Sync Tolerance: 2 seconds threshold for considering users "in sync"
• Presence Interval: 3 seconds for optimal balance of accuracy and efficiency
• Data Retention: 6 hours for sync states, indefinite for comments

Performance Tuning

-- Optimized for real-time workloads
ALTER SYSTEM SET max_connections = 100;
ALTER SYSTEM SET shared_buffers = '256MB';
ALTER SYSTEM SET work_mem = '4MB';
ALTER SYSTEM SET maintenance_work_mem = '64MB';

-- Realtime-optimized indexes
CREATE INDEX CONCURRENTLY idx_room_members_presence 
ON room_members (room_id, last_heartbeat DESC, is_synced);

CREATE INDEX CONCURRENTLY idx_rooms_state 
ON rooms (room_id, is_playing, current_playback_time, updated_at DESC);

🧪 Testing

Supabase Environment Testing

# Test functions directly in Supabase Dashboard
# Use the Function Editor to test individual functions

# Test Realtime connection
# Use Supabase Dashboard → Realtime → Inspect connections

# Test database operations
# Use Supabase SQL Editor for direct database testing

Realtime Testing

// Test WebSocket connection
const { data, error } = await supabase
  .channel('test-room')
  .subscribe();

if (error) {
  console.error('Realtime connection failed:', error);
} else {
  console.log('Realtime connected successfully');
}

Integration Testing

# Test functions via Supabase Dashboard
# Navigate to Edge Functions → Test each function

# Monitor Realtime events
# Use Supabase Dashboard → Realtime → Monitor channels

📊 Monitoring

Key Realtime Metrics

Monitor these metrics in your Supabase dashboard:

• Active Rooms: Number of rooms with recent presence activity
• Concurrent Users: Total users connected via Realtime channels
• Sync Latency: Average time difference between host and members
• Presence Events: Frequency of presence updates per room
• Database Performance: Query execution times and connection usage
• Realtime Connections: Active WebSocket connections and message throughput

Health Checks

// Realtime health check
app.get('/health/realtime', async (req, res) => {
  const { data, error } = await supabase
    .from('rooms')
    .select('count', { count: 'exact', head: true });
  
  // Test Realtime connection
  const testChannel = supabase.channel('health-check');
  const realtimeStatus = await testChannel.subscribe();
  
  res.json({
    status: 'healthy',
    active_rooms: data?.count || 0,
    realtime_connected: !realtimeStatus.error,
    timestamp: new Date().toISOString()
  });
});

Alerting

Set up alerts for:

• Realtime connection failures
• Sync latency > 2 seconds
• Room creation failures
• Database connection exhaustion
• High error rates (>5%)
• Presence update failures

🚀 Deployment

Production Deployment

# Deploy database migrations with Realtime
supabase db push  # Applies all migrations including Realtime optimizations

# Deploy Edge Functions
supabase functions deploy  # Deploys all optimized functions

# Verify Realtime is enabled
supabase db shell --command "SELECT * FROM pg_publication_tables WHERE pubname = 'supabase_realtime';"

# Test deployment
curl -X POST https://ahigvhlqlikkkjsjikcj.supabase.co/functions/v1/rooms-create \
  -H "Content-Type: application/json" \
  -d '{"title":"Production Test","anime_id":"test","anime_title":"Test","episode_number":1,"video_url":"https://test.com","host_user_id":"test","host_username":"TestUser","is_public":true}'

🔒 Security

Row Level Security

All tables have RLS policies enabled for secure data access:

• Public Rooms: Anyone can view public rooms
• Private Rooms: Valid 6-digit access key required
• Member Actions: Users can only modify their own records
• Host Controls: Only verified hosts can control playback
• Realtime Access: Channel-level security for WebSocket connections

Rate Limiting

Implemented for critical actions only:

// Rate limits per user
const RATE_LIMITS = {
  'room_creation': { max: 10, window: '1h' },
  'sync_control': { max: 2, window: '1s' },  // play/pause/seek
  'comments': { max: 5, window: '1m' },
  'room_management': { max: 20, window: '1h' }
};

🐛 Troubleshooting

Common Issues

1. Realtime Connection Issues: Check WebSocket connectivity and browser console
2. Sync Problems: Verify presence tracking and database triggers
3. Room Creation Failures: Check RLS policies and database constraints
4. Performance Issues: Monitor database connections and query performance

📄 License

This project is open source and available under the MIT License.

🤝 Contributing

Contributions are welcome! Please read our contributing guidelines and submit pull requests to the main branch.

📞 Support

For support and questions:

• Create an issue in the GitHub repository
• Check the documentation in the /docs folder
• Review the API reference for detailed endpoint information

---

Built with ❤️ using Supabase Realtime for seamless anime watching experiences!