Realtime Usage Guide

This guide explains how to use realtime database subscriptions in the Delta application.

Overview

We provide a custom useRealtime hook for subscribing to database changes. The hook is database-agnostic and currently implemented with Supabase Realtime. It supports multiple table subscriptions through a single WebSocket channel for better performance.

The hook handles React Strict Mode, cleanup, and ensures callbacks always use the latest function references. Most importantly, it automatically invalidates React Query cache when database changes occur, ensuring your UI always shows fresh data.

Two Approaches to Realtime Subscriptions

Approach 1: Centralized Realtime Listener (Recommended for App-wide Data)

For data that affects multiple parts of the application (projects, organizations, user access, etc.), we use a centralized RealtimeListener component that's mounted once at the app root. This approach:

• Reduces subscription duplication across components
• Automatically invalidates React Query cache for affected queries
• Simplifies maintenance by centralizing subscriptions

The centralized listener is located at /components/realtime/RealtimeListener.tsx and handles:

• Projects and Project Relationships
• Organizations
• User Access changes
• Menu Items
• Permission Role Links
• Transactions and Transaction Items

Example of centralized listener:

// In RealtimeListener.tsx
export function RealtimeListener() {
  const { user } = useUser();

  useRealtime({
    enabled: !!user,
    tables: [
      {
        table: DatabaseTable.PROJECTS,
        onChange: (payload) => {
          clientLogger.debug('Project changed:', { payload });
          // React Query cache will be automatically invalidated
        },
      },
      // ... other app-wide subscriptions
    ],
  });

  return null;
}

Approach 2: Component-specific Subscriptions (For Complex/Filtered Cases)

Use component-specific useRealtime when you need:

• Complex filtering logic that changes based on component state
• Component-specific callbacks beyond cache invalidation
• Dynamic subscriptions based on props

Examples of component-specific subscriptions:

1. ProcessingStatusBar - Subscribes to AI processing jobs with complex filtering
2. TransactionsList - Subscribes to specific transactions it's displaying
3. DataTableProvider - Dynamic subscriptions based on table configuration

// Example: Component with complex filtering
function ProcessingStatusBar({ processableType }) {
  const { projectId } = useProject();

  useRealtime({
    enabled: true,
    tables: [
      {
        table: DatabaseTable.AI_PROCESSING_JOBS,
        // Instead of database filter, we filter in onChange for stability
        onChange: (payload) => {
          const record = payload.new || payload.old;
          // Complex filtering logic
          if (
            record?.processable_type === processableType &&
            record?.project_id === projectId
          ) {
            handleUpdate();
          }
        },
      },
    ],
  });
}

Basic Usage

import { useRealtime } from '@/hooks/useRealtime';
import { DatabaseTable, DatabaseEvent } from '@/constants/database';

function MyComponent() {
  const { user } = useUser();

  // Subscribe to multiple tables with one channel
  useRealtime({
    enabled: !!user,
    tables: [
      {
        table: DatabaseTable.PROJECTS,
        onChange: (payload) => {
          console.log('Project changed:', payload);
          // payload.eventType will be DatabaseEvent.INSERT/UPDATE/DELETE
          // payload.new contains the new record (for INSERT/UPDATE)
          // payload.old contains the old record (for UPDATE/DELETE)
        },
      },
      {
        table: DatabaseTable.PROJECT_RELATIONSHIPS,
        event: DatabaseEvent.UPDATE,
        onChange: (payload) => {
          console.log('Relationship updated:', payload);
        },
      },
    ],
  });
}

Single Table Subscription

For subscribing to just one table, you can still use the same hook:

useRealtime({
  tables: [
    {
      table: DatabaseTable.PROJECTS,
      event: DatabaseEvent.INSERT,
      onChange: (payload) => {
        console.log('New project:', payload.new);
      },
    },
  ],
});

Hook Options

Option	Type	Default	Description
tables	TableSubscription[]	Required	Array of table subscriptions
enabled	boolean	true	Whether all subscriptions are enabled

Each TableSubscription in the tables array has these options:

Option	Type	Default	Description
table	DatabaseTable	Required	The database table to subscribe to (use enum from @/constants/database)
event	DatabaseEvent	DatabaseEvent.ALL	The type of database event to listen for (use enum from @/constants/database)
filter	string	undefined	PostgreSQL filter string (e.g., 'user_id=eq.123')
onChange	(payload: RealtimePayload<T>) => void	undefined	Callback when a database change occurs. Payload includes eventType, new, old, and table

Real-World Example: Navigation Menu Updates

The NavigationProvider uses useRealtime to efficiently subscribe to all navigation-related tables:

import { DatabaseTable } from '@/constants/database';

export function NavigationProvider({ children }: NavigationProviderProps) {
  const { user } = useAuth();
  const currentProjectId = useProjectId();

  // Fetch menu data function
  const fetchMenuData = useCallback(async () => {
    // ... fetch menu implementation
  }, [user, currentProjectId]);

  // Subscribe to all navigation-related tables in one channel
  useRealtime({
    enabled: !!user && !!currentProjectId,
    tables: [
      {
        table: DatabaseTable.MENU_ITEMS,
        onChange: () => {
          clientLogger.info('Menu items changed, refreshing navigation...');
          fetchMenuData();
        },
      },
      {
        table: DatabaseTable.PERMISSION_ROLES,
        onChange: () => {
          clientLogger.info(
            'Role permissions changed, refreshing navigation...'
          );
          fetchMenuData();
        },
      },
    ],
  });
}

Benefits of Multiple Table Subscriptions

1. Single WebSocket Channel: All subscriptions share one channel, reducing overhead
2. Easier Management: All related subscriptions are defined in one place
3. Better Performance: Fewer connections mean less resource usage
4. Cleaner Code: More readable and maintainable than multiple individual hooks

Automatic React Query Cache Invalidation

IMPORTANT: The useRealtime hook automatically invalidates React Query cache for the affected tables. This happens regardless of where you call useRealtime from (centralized listener or component-specific).

The hook wraps all onChange callbacks to:

1. Execute your custom onChange logic (if provided)
2. Automatically call queryClient.invalidateQueries() for the relevant query keys

This means when a database change occurs:

• Your custom onChange runs (if you provided one)
• React Query cache is invalidated for that table
• Components using React Query hooks will automatically refetch and re-render with fresh data

Example flow:

1. User updates a project name in the database
2. Realtime event triggers for DatabaseTable.PROJECTS
3. useRealtime calls your onChange (if any)
4. useRealtime automatically invalidates queryKeys.projects()
5. All components using useProjectQuery() or useProjectsQuery() refetch
6. UI updates with the new project name

Important Notes

⚠️ CRITICAL: Supabase Realtime Filter Limitations

Supabase Realtime only supports single filter conditions! This is a critical limitation that affects how you write filters:

• ✅ Supported: filter: 'user_id=eq.123' (single condition)
• ❌ NOT Supported: filter: 'user_id=eq.123,project_id=eq.456' (multiple conditions)
• ❌ NOT Supported: filter: 'user_id=eq.123 AND project_id=eq.456' (AND conditions)

Workaround for Multiple Conditions

When you need to filter by multiple conditions, you must:

1. Filter by the most selective single condition
2. Manually check additional conditions in the callback

Example from NavigationProvider:

useRealtime({
  tables: [
    {
      table: DatabaseTable.USER_ACCESSES,
      // Can only filter by user_id
      filter: `user_id=eq.${user?.id}`,
      onChange: (payload) => {
        // Manually check additional conditions in the callback
        const changedRecord = payload.new || payload.old;
        if (
          changedRecord &&
          changedRecord.scope_type === 'project' &&
          changedRecord.scope_id === currentProjectId
        ) {
          // Only refresh if it's for the current project
          fetchMenuData();
        }
      },
    },
  ],
});

This limitation is important to understand because:

• You might receive more events than needed (filtered by only one condition)
• You must handle additional filtering in your callback
• Performance implications: callbacks may fire more often than expected

Database-Agnostic Architecture

The realtime subscription system follows a database-agnostic pattern:

• Type Safety: Use DatabaseTable and DatabaseEvent enums from @/constants/database instead of string literals
• Abstraction Layer: The realtimeHelper handles all database-specific implementation details
• Consistent Patterns: Follows the same abstraction pattern as databaseHelper and storageHelper
• Future-Proof: Easy to swap database providers without changing component code

React Strict Mode

The hook includes a 100ms delay before establishing subscriptions to avoid issues with React Strict Mode's double-mounting behavior in development.

Callback References

The hook automatically handles callback references internally using refs, so you don't need to manually manage refs for your onChange callbacks. The hook ensures that the latest version of your callback is always called.

Database Requirements

1. Enable Realtime on Tables: Tables must have realtime enabled in the database:

ALTER PUBLICATION supabase_realtime ADD TABLE your_table;

1. Row Level Security (RLS): If RLS is enabled on a table, ensure users have appropriate SELECT permissions to receive realtime events.

2. Performance: Be mindful of enabling realtime on frequently updated tables as it can impact performance.

Debugging

The hook logs useful information to help debug issues:

• "Setting up realtime subscriptions for: [table names]" - When subscriptions are initiated
• "Realtime subscription status for [table names]: [status]" - Connection status updates
• "Realtime event for [table]:" - When an event is received (logged in realtimeHelper)
• "Cleaning up realtime subscriptions for: [table names]" - When subscriptions are cleaned up

Best Practices

Avoiding Subscription Conflicts

When multiple components need realtime updates from the same table, it's better to have a single shared subscription that fetches all the data, rather than multiple filtered subscriptions that can conflict.

Problem Example:

// ❌ Bad: Multiple components with conflicting subscriptions

// In Component A
useRealtime({
  tables: [
    {
      table: DatabaseTable.PROJECTS,
      filter: `id=eq.${projectId}`,
      onChange: () => {
        /* ... */
      },
    },
  ],
});

// In Component B
useRealtime({
  tables: [
    {
      table: DatabaseTable.PROJECTS, // Same table, different/no filter
      onChange: () => {
        /* ... */
      },
    },
  ],
});

Solution:

// ✅ Good: Single shared subscription with data distribution

// In a shared hook (e.g., useProjects)
export function useProjects() {
  const [projects, setProjects] = useState<Project[]>([]);

  useRealtime({
    tables: [
      {
        table: DatabaseTable.PROJECTS,
        onChange: async () => {
          const allProjects = await fetchAllProjects();
          setProjects(allProjects);
        },
      },
    ],
  });

  return { projects };
}

// In Component A
const { projects } = useProjects();
const currentProject = projects.find((p) => p.id === projectId);

// In Component B
const { projects } = useProjects();
// Use all projects as needed

This pattern prevents subscription conflicts where filtered and unfiltered subscriptions to the same table can interfere with each other, causing some components to stop receiving updates.

Troubleshooting Filter Issues

If you experience issues with realtime updates not working properly when using filters (especially dynamic filters that change based on state), consider moving the filtering logic to the onChange handler instead of using database filters:

Problem: Dynamic filters causing subscription recreation

// ❌ Problematic: Filter changes cause subscription recreation
const jobIds = jobs.map((j) => j.id); // This array changes as jobs update
useRealtime({
  tables: [
    {
      table: DatabaseTable.JOB_STEPS,
      filter: `job_id=in.(${jobIds.join(',')})`, // Filter recreates subscription
      onChange: () => {
        /* ... */
      },
    },
  ],
});

Solution: Client-side filtering in onChange

// ✅ Better: Stable subscription with client-side filtering
const trackedJobIds = useRef(new Set()); // Stable reference

// Add job IDs to tracked set (only add, never remove)
useEffect(() => {
  jobs.forEach((job) => trackedJobIds.current.add(job.id));
}, [jobs]);

useRealtime({
  tables: [
    {
      table: DatabaseTable.JOB_STEPS,
      // No filter - subscribe to all events
      onChange: (payload) => {
        const record = payload.new || payload.old;
        // Filter on client side
        if (record && trackedJobIds.current.has(record.job_id)) {
          // Process only events for tracked jobs
          handleUpdate();
        }
      },
    },
  ],
});

Benefits of client-side filtering:

• Stable subscriptions: No recreation when state changes
• No missed events: Events aren't lost during subscription recreation
• Better performance: Avoids WebSocket reconnection overhead
• More flexible: Can apply complex filtering logic not supported by database filters

When to use this pattern:

• When filters depend on frequently changing state
• When experiencing missing realtime updates
• When you need complex filtering logic (multiple conditions, OR operations)
• When tracking a dynamic set of records

Trade-offs:

• Receives more events than needed (filtered client-side)
• Slightly higher bandwidth usage
• More callback invocations (but filtered early)

This approach has proven effective in fixing realtime reliability issues, particularly with AI processing status updates where job IDs change frequently as jobs complete.

Environment Configuration

Ensure your .env.local file has the correct Supabase URL:

NEXT_PUBLIC_SUPABASE_URL=http://localhost:54321  # For local development

Note: WebSocket connections work with localhost but may have issues with 127.0.0.1 in some environments.

Available Database Tables and Events

Database Tables (from @/constants/database)

export enum DatabaseTable {
  PROJECTS = 'projects',
  USER_ACCESSES = 'user_accesses',
  PROJECT_RELATIONSHIPS = 'project_relationships',
  PERMISSION_ROLE_LINKS = 'permission_role_links',
  MENU_ITEMS = 'menu_items',
  // ... other tables
}

Database Events (from @/constants/database)

export enum DatabaseEvent {
  ALL = '*',
  INSERT = 'INSERT',
  UPDATE = 'UPDATE',
  DELETE = 'DELETE',
}

Always import and use these enums instead of string literals for type safety and consistency.