My Twitch Clone: 5 Brutal Next.js/Supabase Fixes (2025)

3. The Phantom Viewer Problem: Inaccurate Real-time Presence

Supabase's built-in Realtime presence is great for showing a list of who is currently connected to a channel. I used it to power the "Users in Chat" list. But I made a critical mistake: I assumed the presence count was the same as the viewer count.

The Brutal Problem: A user can be "present" in the chat channel with the browser tab open in the background, but not actually be watching the video stream. This led to inflated viewer counts, which is a cardinal sin for a streaming platform. Furthermore, as the channel size grew, the sheer number of `join` and `leave` events became noisy and resource-intensive.

The Fix: A Server-Side Heartbeat System

To get an accurate count of active viewers, you need confirmation that they are still watching. A heartbeat system is a classic and reliable way to do this.

1. Create a Table: Make a simple table called `active_viewers` with columns like `stream_id`, `user_id` (or a session ID), and `last_seen` (a timestamp).
2. Client-Side Heartbeat: In your video player component, use a `setInterval` to call a Supabase Edge Function every 30 seconds. This is the "heartbeat".
3. Edge Function Logic: The Edge Function receives the heartbeat and performs an `UPSERT` on the `active_viewers` table, updating the `last_seen` timestamp for that user and stream. An `UPSERT` is critical for performance; it's a single database operation.
4. Cleanup Crew: A scheduled Supabase Edge Function (a cron job) runs every minute to delete records from `active_viewers` where `last_seen` is older than, say, 60 seconds. This cleans up disconnected or idle users.
5. The Real Count: Your viewer count is now a simple, fast `SELECT count(*) FROM active_viewers WHERE stream_id = ?`. You can expose this via another Edge Function or an RPC call.

This is far more accurate and scales much better than relying on client-side presence events alone.

4. The Query of Death: Taming Complex Joins in PostgreSQL

As features grew, so did my data relationships. A common page was the main feed, which needed to show: "For the current user, get the streamers they follow, show their live status, and grab their most recent VOD thumbnail."

The Brutal Problem: This translated into a monstrous, multi-level `JOIN` across `users`, `follows`, `streams`, and `vods` tables, with a `LATERAL JOIN` or window function to get the "most recent" VOD. It was slow on the first load and impossible to cache effectively. This single query, my "Query of Death," was bringing the app to its knees.

The Fix: Denormalization and Postgres Functions (RPC)

The relational purist in me screamed, but the pragmatist won. For read-heavy applications, you cannot be afraid to denormalize data or create optimized data access layers.

Here's a comparison of the approaches:

Creating a Postgres function that returns exactly the JSON shape my frontend needs was the ultimate fix. The database does what it's best at—querying and relating data—and sends a perfectly formed, lightweight payload to the client.

-- Simplified example of the RPC function in Postgres

create function get_user_feed(p_user_id uuid)
returns setof json
as $$
begin
  return query
    select json_build_object(
      'streamer_name', u.username,
      'is_live', s.is_live,
      'latest_vod_thumbnail', (select v.thumbnail_url from vods v where v.user_id = s.user_id order by v.created_at desc limit 1)
    )
    from follows f
    join streams s on f.followed_id = s.user_id
    join users u on s.user_id = u.id
    where f.follower_id = p_user_id;
end;
$$ language plpgsql;

// Client call
supabase.rpc('get_user_feed', { p_user_id: userId })

5. Caching vs. Reality: Next.js's Identity Crisis with Live Data

I love Next.js's caching strategies. ISR (Incremental Static Regeneration) is brilliant for content that changes periodically. I set up my stream pages (`/stream/[username]`) to revalidate every 60 seconds.

The Brutal Problem: What happens when a user loads the page 5 seconds after it was cached? They see a stale viewer count, an incorrect "LIVE" status, and a chat that's a minute old. For a live platform, this is disorienting and feels broken. Caching the entire page was fundamentally at odds with the real-time nature of the content.

The Fix: The Static Shell + Dynamic Hydration Model

You have to separate the static parts of the page from the dynamic parts. The server renders a static "shell" of the page, and the client is responsible for fetching and rendering the live data.

1. Static Shell (ISR/SSG): The Next.js page is built at build time or revalidated periodically. It contains all the non-real-time data: the streamer's bio, their social links, panels, and a gallery of past VODs. This makes the initial page load incredibly fast and great for SEO. The video player and chat are just empty containers.
2. Client-Side Fetching (SWR/React Query): Once the page loads in the browser, a main React component (e.g., ``) kicks in. It uses a client-side data fetching library like SWR to immediately fetch the *current* live status.
3. Conditional Rendering:
   • If the streamer is live, the component loads the video player, connects to the chat and presence channels (using our fixes from above!), and starts the heartbeat.
   • If the streamer is offline, it can show a "Streamer is Offline" message or the latest VOD.

This hybrid approach gives you the best of both worlds: the instant perceived performance and SEO benefits of a static site, combined with the liveness and accuracy required for a real-time application.