Realtime

# Postgres Changes alternative

For a small prototype you can subscribe directly:

```ts
const channel = supabase
  .channel("task-postgres-changes")
  .on(
    "postgres_changes",
    {
      event: "*",
      schema: "public",
      table: "Task",
    },
    () => queryClient.invalidateQueries({ queryKey: taskKeys.all }),
  )
  .subscribe();
```

TaskFlow Phase 26 prefers Broadcast for database-change delivery because Supabase currently recommends Broadcast for scalability/security. Keep this alternative for comparison and troubleshooting, not as the production default.

# TaskFlow Phase 26 Realtime Snapshot

This reference implements Realtime as a freshness layer, not as a new backend.

- Writes still go Browser -> NestJS -> business rules -> repository -> PostgreSQL.
- PostgreSQL trigger emits private Supabase Broadcast events after Task changes.
- Browser subscribes to one private Workspace Task channel.
- Realtime Authorization checks existing TaskFlow Membership.
- Realtime events invalidate TanStack Query Task keys.
- Reconnect/subscription success triggers an authoritative refetch.
- No direct browser Task writes are introduced.

# Realtime Decision Table

| Need | Default |
|---|---|
| Coworker Task changes should appear quickly | Private Broadcast + query invalidation |
| Tiny prototype / simplest DB CDC | Postgres Changes |
| Who is online / viewing board | Presence |
| Rare settings changes | Normal query refetch only |
| Payroll finalization | HTTP mutation + explicit confirmation; Realtime only as notification |
| High-frequency cursor movement | Broadcast, not database persistence |

Realtime is optional. HTTP correctness must remain intact without it.

# Sources

- [Supabase - Subscribing to Database Changes](https://supabase.com/docs/guides/realtime/subscribing-to-database-changes) - Broadcast is the recommended database-change method for scalability and security; Postgres Changes is simpler but scales less well. Includes realtime.broadcast_changes trigger pattern.
- [Supabase - Realtime Authorization](https://supabase.com/docs/guides/realtime/authorization) - Private channels use RLS policies on realtime.messages; permissions are evaluated when the client joins a topic.
- [Supabase - Realtime Concepts](https://supabase.com/docs/guides/realtime/concepts) - Defines Channels, topics, Broadcast, Presence and Postgres Changes.
- [Supabase - Realtime Architecture](https://supabase.com/docs/guides/realtime/architecture) - Describes Broadcast from Postgres, WAL/replication behavior and Realtime internals.
- [Supabase - Broadcast](https://supabase.com/docs/guides/realtime/broadcast) - Documents database-triggered Broadcast, private channels, replay and broadcast payloads.
- [Supabase - Realtime Protocol](https://supabase.com/docs/guides/realtime/protocol) - Documents access-token refresh, server events, reconnection behavior and broadcast protocol.
- [Supabase JS - removeChannel](https://supabase.com/docs/reference/javascript/removechannel) - Documents explicit channel teardown and removal.
- [Supabase JS - setAuth](https://supabase.com/docs/reference/javascript/setauth) - Documents the JWT used for Realtime authorization and RLS.
- [Supabase - Realtime Limits](https://supabase.com/docs/guides/realtime/limits) - Documents connection/message limits and Postgres Changes scaling considerations.
- [TanStack Query - QueryClient](https://tanstack.com/query/latest/docs/reference/QueryClient) - Provides invalidateQueries and setQueryData used to synchronize Realtime events with query cache.

import type { ReactNode } from "react";
import { WorkspaceTaskRealtimeProvider } from "@/components/realtime/workspace-task-realtime-provider";
import { getAuthorizedWorkspace } from "@/lib/workspaces/get-authorized-workspace";

export default async function WorkspaceLayout({
  children,
  params,
}: {
  children: ReactNode;
  params: Promise<{ workspaceSlug: string }>;
}) {
  const { workspaceSlug } = await params;
  const workspace = await getAuthorizedWorkspace(workspaceSlug);

  return (
    <WorkspaceTaskRealtimeProvider workspaceId={workspace.id}>
      {children}
    </WorkspaceTaskRealtimeProvider>
  );
}

"use client";

import type { ReactNode } from "react";
import { useWorkspaceTaskRealtime } from "@/hooks/use-workspace-task-realtime";

export function WorkspaceTaskRealtimeProvider({
  workspaceId,
  children,
}: {
  workspaceId: string;
  children: ReactNode;
}) {
  useWorkspaceTaskRealtime(workspaceId);
  return children;
}

"use client";

import { useEffect, useMemo } from "react";
import type { RealtimeChannel } from "@supabase/supabase-js";
import { useQueryClient } from "@tanstack/react-query";
import { createBrowserSupabaseClient } from "@/lib/supabase/client";
import { taskKeys } from "@/lib/query/task-keys";

export function useWorkspaceTaskRealtime(workspaceId: string) {
  const queryClient = useQueryClient();
  const supabase = useMemo(() => createBrowserSupabaseClient(), []);

  useEffect(() => {
    let channel: RealtimeChannel | undefined;
    let disposed = false;

    const refresh = () => {
      void queryClient.invalidateQueries({
        queryKey: taskKeys.all,
      });

-- Phase 26: broadcast committed Task changes to Workspace-scoped private topics.

CREATE OR REPLACE FUNCTION public.broadcast_task_changes()
RETURNS trigger
SECURITY DEFINER
SET search_path = ''
LANGUAGE plpgsql
AS $$
DECLARE
  project_id uuid;
  workspace_id uuid;
BEGIN
  project_id := CASE
    WHEN TG_OP = 'DELETE' THEN OLD."projectId"
    ELSE NEW."projectId"
  END;

  SELECT p."workspaceId"
  INTO workspace_id
  FROM public."Project" p

-- Phase 26: authorize private Workspace Task topics from TaskFlow membership.
-- This policy protects Realtime channel joins/reads. NestJS still authorizes API writes.

CREATE OR REPLACE FUNCTION public.can_receive_task_topic(
  requested_topic text
)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public."Membership" m
    JOIN public."User" u
      ON u.id = m."userId"
    WHERE u."authSubject" = (SELECT auth.uid())::text
      AND requested_topic =
        'workspace:' || m."workspaceId"::text || ':tasks'

-- Learning-only alternative: Postgres Changes requires the table in the publication.
-- TaskFlow production direction in Phase 26 prefers Broadcast for database changes.

ALTER PUBLICATION supabase_realtime
ADD TABLE public."Task";

-- If you require old row values for UPDATE/DELETE payloads:
-- ALTER TABLE public."Task" REPLICA IDENTITY FULL;

# Phase 26 Realtime Test Matrix

| Scenario | Expected |
|---|---|
| User A and B belong to same Workspace | Both can subscribe to private Task topic |
| User B does not belong to Workspace | User B cannot receive Workspace Task broadcasts |
| User A updates Task through NestJS | DB trigger emits UPDATE after commit |
| User B is online | Task query invalidates/refetches automatically |
| User B offline during several changes | Reconnect/SUBSCRIBED refetch catches current state |
| Local optimistic update succeeds | Realtime + mutation invalidations still converge to server truth |
| Local optimistic update gets 409 | Local rollback works; Realtime does not override business error semantics |
| Workspace switch | Old channel removed; new Workspace channel joined |
| Component unmount | removeChannel called |
| Bad topic / no membership | subscription denied / no data delivered |
| CHANNEL_ERROR or TIMED_OUT | full error logged without token |
| Realtime disabled | normal HTTP query/mutation flow still works |