Replace single-instance SSE with a horizontally-scalable pub/sub real-time layer

[Lakes41]

Difficulty: Expert
Type: Performance

Background
The activity feed's SSE-plus-polling-fallback design (per .env.example's NEXT_PUBLIC_ACTIVITY_REFRESH_MS documentation) implicitly assumes a single dashboard server instance holding open SSE connections and pushing events as they're received (e.g. from an incoming webhook). This is a common and reasonable starting design, but doesn't scale horizontally: if the dashboard runs as multiple instances behind a load balancer, a webhook processed by instance A has no way to notify SSE clients connected to instance B.

Problem
As currently designed, running more than one dashboard instance for availability or load would silently break real-time activity delivery for a subset of connected clients (those attached to an instance that isn't the one handling the relevant webhook), with no visible error — clients would just quietly fall back to the polling interval, defeating the purpose of the SSE fast-path.

Expected outcome
Activity events are published to a shared pub/sub layer (e.g. Postgres LISTEN/NOTIFY if Issue #6's Postgres-backed durable mode is in place — a pragmatic choice that avoids introducing a new infra dependency like Redis solely for this — or an explicitly justified alternative) that any dashboard instance can subscribe to, so an event ingested by any instance is broadcast to all SSE clients connected to any instance.

Suggested implementation

• Evaluate and explicitly document the trade-off between Postgres LISTEN/NOTIFY (no new infra, payload size limits, at-most-once semantics) vs. introducing Redis pub/sub (a new required dependency, better throughput/features) — pick one with justification rather than defaulting silently.
• Implement a small publish/subscribe abstraction so the SSE route subscribes on connection and republishes matching events to its connected clients.
• Ensure the reconnect/backfill behavior from Issue Improve IntegrationClient error handling #7 still works correctly in a multi-instance world (a client's backfill request may land on a different instance than the one it was previously connected to, so backfill must read from shared durable storage, not instance-local memory).
• Add a way to run and test this locally with at least two dashboard instances (e.g. two pnpm dev processes on different ports, or documented Docker Compose setup) to validate cross-instance delivery.
• Clearly document that this feature depends on durable storage mode (Issue Add a server-wide role sync command for admins #6); mock mode can retain today's single-instance-only SSE behavior with a note in the docs.

Acceptance criteria

• With two dashboard instances running against the same durable backend, a webhook processed by instance A results in the event reaching an SSE client connected to instance B.
• The chosen pub/sub mechanism and its trade-offs are documented in the PR/design doc.
• Reconnect/backfill (Issue Improve IntegrationClient error handling #7) continues to function correctly across instances.
• Single-instance/mock-mode behavior is unchanged and doesn't require the new pub/sub layer.
• A documented local reproduction (script or Compose file) demonstrates the multi-instance scenario for reviewers.
• Tests and pnpm typecheck pass.

Likely affected files/directories
Activity SSE route (apps/dashboard/app/api/activity/** or equivalent), new pub/sub abstraction module, webhook handler (publish side), apps/dashboard/lib/storage/ (if reusing Postgres LISTEN/NOTIFY), documentation/dev tooling for local multi-instance testing.

[Chigybillionz]

Hi, I’ve reviewed this issue and I’m confident I can fix it. I’d like to take it on.

Thank you very much

[Emmanuellsensai]

I'd like to take this one.

Plan:

• Confirm whether Issue Add a server-wide role sync command for admins #6's Postgres durable storage mode is merged yet, since this depends on it being in place before pub/sub can use LISTEN/NOTIFY.
• Add a small pub/sub abstraction (publish/subscribe interface) so the webhook handler publishes events and the SSE route (apps/dashboard/app/api/activity/**) subscribes per connection instead of writing directly to in-memory client lists.
• Implement the Postgres LISTEN/NOTIFY backend for that abstraction, and document why it was chosen over Redis (no new infra dependency, with the payload size and at-most-once tradeoffs noted).
• Update backfill (Issue Improve IntegrationClient error handling #7) to read from the shared durable store rather than instance-local memory, so a client reconnecting to a different instance still gets the right history.
• Add a local setup for running two dashboard instances (two pnpm dev processes or a Compose file) to verify cross-instance delivery, and confirm mock mode keeps today's single-instance behavior.

I'll wait for assignment before opening a PR.

[grantfox-oss]

🦊 GrantFox — @Chigybillionz has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #265)
2. Your PR will be reviewed by the Adamantine-guild maintainers

Good luck! Track your progress on GrantFox.