Notifications

Overview

Supasheet ships a complete in-app notifications system. It's optimised for fan-out — one event creates a single notification row and N per-user delivery rows — so you can deliver a single broadcast to every user with a role, every user whose role holds a specific table privilege, or any custom user list, without duplicating payloads.

Defined in supabase/migrations/20251006051303_notifications.sql.

What the User Sees

Once you start creating notifications, every signed-in user gets:

• A bell badge in the header showing the unread count
• A dropdown listing recent notifications (title, body, link, age)
• Mark all as read in the dropdown header
• A click on any notification routes them to the link you supplied (e.g. /desk/resource/tasks/detail/<id>)

Data Model

supasheet.notifications

One row per event — shared across all recipients.

supasheet.user_notifications

One row per (notification, recipient) — holds the per-user delivery state.

Both tables carry "display": "none" in their comment so they don't appear in the resource sidebar — interactions go through the helper functions below.

Creating a Notification

Use supasheet.create_notification(...) — it inserts both the shared payload and the per-user fan-out rows in one call.

SELECT supasheet.create_notification(
  p_type     => 'task_assigned',
  p_title    => 'New task assigned',
  p_body     => 'Ship the Q3 release notes by Friday.',
  p_user_ids => ARRAY['…assignee-uuid…']::uuid[],
  p_metadata => jsonb_build_object('task_id', 'a1b2…'),
  p_link     => '/desk/resource/tasks/detail/a1b2…'
);

Returns the new notification id, or NULL when p_user_ids is empty.

Resolving Recipients

Two helper functions return user lists you can pass straight into p_user_ids:

-- Everyone with a specific role
SELECT supasheet.get_users_with_role('admin');

-- Everyone whose role currently holds a given table privilege
SELECT supasheet.get_users_with_table_privilege('desk', 'tasks', 'update');

Pattern of use:

PERFORM supasheet.create_notification(
  'release_freeze',
  'Release freeze starts Friday',
  'No non-critical merges until Monday.',
  supasheet.get_users_with_role('admin')
);

These helpers mirror your authorisation rules — only users actually granted the role or table privilege receive the notification. Both are SECURITY DEFINER, restricted to service_role — call them from inside your own SECURITY DEFINER notification trigger functions, not from client code.

Triggering from Domain Tables

The most useful pattern is to attach a trigger that creates notifications when something happens in your domain. The migration ships a working example: trg_user_role_changed_notify fires on auth.users itself and notifies a user whenever their role is assigned or changed.

create or replace function supasheet.trg_user_role_changed_notify () returns trigger language plpgsql security definer
set search_path = '' as $$
declare
    v_role text := new.raw_app_meta_data ->> 'role';
begin
    if v_role is not null then
        perform supasheet.create_notification(
            'role_granted',
            'Role assigned',
            'You have been granted the "' || v_role || '" role.',
            array[new.id],
            jsonb_build_object('role', v_role, 'user_id', new.id)
        );
    end if;
    return new;
end;
$$;

create trigger user_role_changed_notify
after update of raw_app_meta_data on auth.users for each row when (
  old.raw_app_meta_data ->> 'role' is distinct from new.raw_app_meta_data ->> 'role'
)
execute function supasheet.trg_user_role_changed_notify ();

Since a user's role lives on auth.users.raw_app_meta_data->>'role' rather than a join table, the trigger fires AFTER UPDATE OF raw_app_meta_data and uses a WHEN clause to only run when the role key actually changed — this covers both the first-time assignment and any subsequent role change with a single trigger.

Example: Notify Assignee on Task Insert

CREATE OR REPLACE FUNCTION desk.notify_task_assigned ()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = '' AS $$
BEGIN
  IF NEW.assignee_id IS NOT NULL THEN
    PERFORM supasheet.create_notification(
      p_type     => 'task_assigned',
      p_title    => 'New task: ' || NEW.title,
      p_body     => left(coalesce(NEW.description, ''), 280),
      p_user_ids => ARRAY[NEW.assignee_id],
      p_metadata => jsonb_build_object('task_id', NEW.id, 'priority', NEW.priority),
      p_link     => '/desk/resource/tasks/' || NEW.id::text || '/detail'
    );
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER trg_notify_task_assigned
  AFTER INSERT ON desk.tasks
  FOR EACH ROW EXECUTE FUNCTION desk.notify_task_assigned();

Convenience Helpers Used by the UI

Both run as security definer and key off auth.uid().

Reading from SQL

Notifications and deliveries are subject to RLS — users only see their own deliveries. The UI calls into the standard PostgREST endpoints:

-- Recent deliveries for the current user, joined to the payload
SELECT un.id, un.read_at, n.title, n.body, n.link, n.created_at
FROM   supasheet.user_notifications un
JOIN   supasheet.notifications      n ON n.id = un.notification_id
WHERE  un.user_id = auth.uid()
  AND  un.archived_at IS NULL
ORDER  BY n.created_at DESC
LIMIT  50;

Permissions

End-users do not need a grant on either table — RLS lets them see their own deliveries through the user-scoped policies. Grant SELECT on the tables above only to admin roles that need to audit the full notification stream.

-- Grant admin visibility into all notifications (already granted to x-admin)
grant select on table supasheet.notifications to "admin";
grant select on table supasheet.user_notifications to "admin";

Best Practices

• Pick stable type values. They make it easy to filter the audit and UI groupings.
• Always set link. A click should take the user somewhere actionable.
• Use metadata for IDs, not free-form strings. You'll thank yourself when you build digest emails or analytics later.
• De-duplicate at the trigger. If you fire on AFTER UPDATE, guard the body with IF NEW.col IS DISTINCT FROM OLD.col so you don't spam users.
• Resolve recipients with the helpers. They follow the same RBAC source of truth as the rest of your app.

Next Steps

• Comments — Pair comments with comment-driven notifications
• Audit Logs — Notifications + audit logs gives you a clean event trail
• Authorization — Resolving recipients with get_users_with_role / get_users_with_table_privilege