Skip to main content

Activity Events

In today's social-media-driven world, keeping users engaged and informed about relevant activities within your application is crucial for driving interaction and fostering a sense of community. Implementing activity notifications, such as likes, comments, and mentions, enhances the user experience by providing real-time updates on their interactions and connections.

With ElectricSQL, implementing activity events in your local-first application becomes a seamless process. ElectricSQL handles the complex task of synchronizing activity data across multiple users and devices, ensuring that notifications are delivered promptly and consistently, regardless of network conditions or device types.

This recipe demonstrates how to build a basic activity feed with read acknowledgements and dynamic actions that can be used for both notification popovers or toasts.

Schema​

Adapt the schema and DDLX commands below to match your specific use-case.

-- Create a simple activity events table.
CREATE TABLE IF NOT EXISTS activity_events (
  id UUID PRIMARY KEY NOT NULL,
  -- user IDs can be foreign keys to user table to restrict
  -- access for both vieweing and marking as read
  -- e.g. source_user_id UUID NOT NULL REFERENCES users(id)
  source_user_id UUID NOT NULL,
  target_user_id UUID NOT NULL,
  activity_type TEXT NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  message TEXT NOT NULL,
  action TEXT,
  read_at TIMESTAMPTZ
);

-- ⚡ Electrify the table
ALTER TABLE activity_events ENABLE ELECTRIC;

Data Access​

Adapt the headless components below and enhance them with additional features.

import { useCallback } from 'react'
import { useElectric } from '../electric/ElectricWrapper'
import { useLiveQuery } from 'electric-sql/react'

export const useActivityEvents = ({
  maxNumActivities,
  startingFrom,
}: {
  maxNumActivities?: number
  startingFrom?: Date
}) => {
  const { db } = useElectric()!

  // Query for [maxNumActivities] most recent activities
  const { results: recentActivities = [] } = useLiveQuery(
    db.activity_events.liveMany({
      ...(startingFrom !== undefined && { where: { timestamp: { gte: startingFrom } } }),
      orderBy: { timestamp: 'desc' },
      take: maxNumActivities,
    }),
  )

  // Use raw SQL to count all unread activities
  const numberOfUnreadActivities =
    useLiveQuery(
      db.liveRawQuery({
        sql: 'SELECT COUNT(*) AS count FROM activity_events WHERE read_at IS NULL',
      }),
    ).results?.[0]?.count ?? 0
  // Update individual activity's read status through its ID
  const markActivityAsRead = useCallback(
    (activityId: string) =>
      db.activity_events.update({
        data: { read_at: new Date() },
        where: { id: activityId },
      }),
    [db.activity_events],
  )

  // Mark all unread activities as read
  const markAllActivitiesAsRead = useCallback(
    () =>
      db.activity_events.updateMany({
        data: { read_at: new Date() },
        where: { read_at: null },
      }),
    [db.activity_events],
  )

  return {
    recentActivities,
    numberOfUnreadActivities,
    markActivityAsRead,
    markAllActivitiesAsRead,
  }
}

Usage​

Connect the schema and headless components with your UI library of choice to get a working component.

import { ActivityPopoverView } from './ActivityPopoverView'
import { useActivityEvents } from './use_activity_events'

export const ActivityPopover = () => {
  const {
    recentActivities,
    numberOfUnreadActivities,
    markActivityAsRead,
    markAllActivitiesAsRead,
  } = useActivityEvents({ maxNumActivities: 5 })

  return (
    <ActivityPopoverView
      recentActivities={recentActivities}
      numUnreadActivities={numberOfUnreadActivities}
      onActivityRead={markActivityAsRead}
      onAllActivitiesRead={markAllActivitiesAsRead}
    />
  )
}