Overview

Notifications

Application notification management, events, and user feedback.

Functional Architecture

Description: the feature collects application events, persists them in _notifications, and propagates them in real-time to the UI.

  • Storage: metadata DB table dbo._notifications.
  • Write path: POST /api/Notifications/enqueue endpoint using dbo.sp_enqueue_notification.
  • Read path: GET /api/Notifications/unread/{userId} endpoint.
  • Real-time path: WebSocket /ws/notifications?userId=... with snapshot push.
  • UI: wuic-notification-bell component (notification list popover, unread badge, mark-read and clear-read).

Access and session

  • The api/Notifications/* endpoints and the WebSocket require a valid session; without one they answer 401 errors.auth.unauthenticated.
  • Notifications belong only to their recipient, administrators included: unread, markallread, clearread and dismiss-progress with another user's userId answer 403 errors.auth.notification_forbidden; markread and delete-read on someone else's notification answer 404, as for a non-existent id.
  • enqueue towards other users is still allowed with a valid session, under these rules (since 1.7.28): the guest session is refused (403 errors.auth.operation_disabled, reason: guest); createdBy is written by the server (the session username, the client value is ignored); targetJson accepts only in-app paths in path/route (e.g. #/orders/list) and Tmp_export/ files in downloadFile/downloadUrl/url, otherwise 400 errors.input.invalid; sending to whole roles (roleIds) is for the superadmin or for the roles listed in notificationRoleFanOutRoles (ids or names separated by commas, * = every non-guest user; missing = superadmin only), otherwise 403 reason: role_fanout; non-superadmins send to at most 50 recipients per call (403 reason: too_many_recipients) and 200 recipients every 10 minutes (429 reason: rate_limited). This rule applies to free sends; sends from a workflow follow the rule below.
  • Since round 2 of 1.7.28: a role that is not allowed no longer makes the whole call fail. Explicit users (userId/userIds) are still served, the discarded roles come back in refusedRoleIds of the response (403 reason: role_fanout only when no recipient is left). Besides the super-admin and notificationRoleFanOutRoles, a user may notify a role that a saved workflow names as a number in its notifications (wtoolbox.enqueueNotification({ roleId: 21, ... }) in a callback of _wuic_workflow_graph), if the call carries the context of that workflow (wtoolbox.enqueueNotification sends it by itself: routeContext with workflowGraphId and the route of the screen) and the user is authorized on the workflow route (from the runner, on at least one of its routes); without the context, or from a route that is not part of the workflow, the roles are refused: this is how the notifications of the tutorial workflow wf_po_approvazione work. A roleId computed at runtime (variable) does not count: that needs notificationRoleFanOutRoles. Tmp_export/ links are checked after decoding (%2e%2e, .%2e, %2f refused, like ; and the backslash) and the bell opens the download only if the resolved URL is a file of the app's /Tmp_export/. Tmp_export files are deleted after tmpExportRetentionHours (default 24 h, see AppSettings).
  • Since round 3 of 1.7.28: download links (downloadFile/downloadUrl/url in targetJson) are sent only by the super-admin (otherwise 403 reason: download_link): anyone can create a Tmp_export file with any content, and the "Export completed" notification is written by the server anyway. A non-super-admin whose window is already full (200 recipients every 10 minutes) gets 429 reason: rate_limited before any work on the roles. If the users of a role would take the call over 50 recipients, that role is discarded and the explicit users are still served; the same happens when the roles cannot be resolved. The response gives the reason of every discarded role in refusedRoleReasons (role_fanout, too_many_recipients, role_resolution_failed) and wtoolbox.enqueueNotification returns false when a role was discarded or the call failed. In workflow callbacks only a roleId written inside the object passed to enqueueNotification({ ... }) counts.
  • The WebSocket /ws/notifications?userId=... accepts only the session's userId (otherwise 403). Behind a loopback reverse proxy (e.g. the nginx of the Linux packages) it uses the client IP from X-Forwarded-For, the same one recorded at login: the proxy must forward that header.
  • With multi-tenant enabled, notifications, WebSocket and watcher work on the metadata DB of the user's tenant (see Multi-tenant).

_notifications Table

Main fields managed by the framework:

  • id: notification identifier.
  • user_id: target user.
  • type: type (e.g., info, warning, success, ...).
  • message: text displayed in UI.
  • target_json: click destination (navigation route/path).
  • payload_json: free payload for application detail.
  • is_read, read_at: read status.
  • created_at, deleted_at: timeline and soft-delete. created_at is in UTC: since 1.7.13 the column default is aligned to UTC at startup on all 4 DBMS (migration fix-notifications-created-at-utc-default). Code that inserts rows by hand must write UTC time.
  • created_by, source: origin traceability.

Triggers / Automations Populating _notifications

Description: the recommended pattern is to centralize insertion in sp_enqueue_notification, called from triggers or jobs.

  • The framework provides the stored procedure dbo.sp_enqueue_notification (script: scripts/add-notifications-framework.sql).
  • Application SQL triggers or backend jobs can populate _notifications in two ways:
  • calling dbo.sp_enqueue_notification (recommended approach);
  • inserting directly into the table respecting the minimum fields (user_id, type, message).
  • Typical pattern: trigger on a business table (AFTER INSERT/UPDATE) that, when a condition is met, creates a notification for one or more users.
Snippet 1SQL
DECLARE @dbName NVARCHAR(256) = DB_NAME();
DECLARE @isBrokerEnabled BIT;

SELECT @isBrokerEnabled = is_broker_enabled
FROM sys.databases
WHERE name = @dbName;
  • userId: 100275
  • type: "warning"
  • message: "Order delayed"
  • targetJson.route: "#/orders/list"
  • targetJson.filter: "status||eq||late"
  • payloadJson.orderId: 12345

Metadata-Driven Triggers from md_props_bag.notifications.triggerRules

Rules can be centralized in the table metadata (md_props_bag.notifications.triggerRules) and translated into runtime SQL triggers.

  • Rule structure: event, watchColumns, userIdExpr, typeTemplate, messageTemplate, targetTemplate, payloadTemplate, source, triggerName.
  • Pipeline: metadata route -> SQL trigger generation on server -> sp_enqueue_notification -> websocket bell (/ws/notifications?...).
  • DBMS: on SQL Server the trigger calls sp_enqueue_notification in the metadata DB; on MySQL, PostgreSQL and Oracle the trigger is row-level and writes to _wuic_notification_outbox (same schema as the table, same transaction), and the backend delivers it to sp_enqueue_notification every Notifications:TriggerOutboxPollSeconds seconds (default 2). A template starting with = is dialect SQL: outside SQL Server the row is read as NEW.column / OLD.column (Oracle :NEW."COLUMN"). Trigger names stay within the DBMS limit (Oracle 30, PostgreSQL 60, MySQL 64), shortened with a hash when needed.
  • Removal: saving md_props_bag without rules does not drop existing triggers; to remove them call MetaService.removeNotificationTriggers with {"route":"<route>"} (admin).
  • Inline snippet (insert): {"enabled":true,"event":"insert","watchColumns":[],"userIdExpr":"{{owner_user_id}}","messageTemplate":"New record {{id}}"}.
  • Inline snippet (update): {"enabled":true,"event":"update","watchColumns":["status"],"userIdExpr":"{{owner_user_id}}","messageTemplate":"Updated {{id}}: {{status}}"}.
  • Inline snippet (delete): {"enabled":true,"event":"delete","watchColumns":[],"userIdExpr":"{{owner_user_id}}","targetTemplate":"{\"path\":\"/{{md_route_name}}/list\"}"}.
  • Result: notification rules versioned at metadata level, no logic duplication in individual manual triggers.
  • Metadata context reference: see Metadata (md_props_bag section and advanced nodes).

Prerequisite: SQL Server Service Broker

The SqlDependency mode requires that Service Broker be enabled on the metadata database. Without Service Broker the watcher cannot register the notifications queries and logs:

Unable to start SqlDependency for _notifications. System.InvalidOperationException: The SQL Server Service Broker for the current database is not enabled...

Starting from the current version, the watcher performs an automatic fallback to polling when the broker is unavailable. To achieve optimal performance (instant push without polling) you must enable the broker.

New Installations (first-run wizard)

The first-run wizard automatically enables Service Broker after creating the metadata database. No manual action required.

Existing Installations (upgrade / manual deploy)

Open SQL Server Management Studio, select the metadata database in the dropdown, and execute:

Snippet 2SQL
SELECT name, is_broker_enabled FROM sys.databases WHERE name = DB_NAME();

After execution, restart the application (IIS Application Pool recycle). The watcher will detect the active broker and switch from polling to native push.

The script is also available as a standalone file: dbms/scripts/first-run/enable-service-broker.mssql.sql.

Quick Check

To check broker status on the current database:

Expected result: is_broker_enabled = 1.

SqlDependency / Polling

  • Backend watcher: NotificationSqlDependencyWatcher registered as hosted service.
  • Configurable modes:
  • SqlDependency (default): registers a dependency on the _notifications unread query and sends a snapshot when the result changes.
  • Polling: fallback with periodic polling every PollSeconds. Automatically activated if Service Broker is not available.
  • Push service: NotificationPushService sends snapshots only to users with connected sockets.

UI and User Behavior

  • notification-bell shows a badge with unread count.
  • Panel opening: notification list with date/time and type.
  • Click on notification:
  • mark-read via POST /api/Notifications/markread/{id};
  • navigation to route read from target_json.
  • Remove read action: POST /api/Notifications/clearread/{userId} (soft-delete of read notifications).

Export/Import Progress

  • Progress notifications contain progressGuid and reference route in target_json.
  • Click on progress notification:

- reopens the progress dialog linked to the same progressGuid;

- does not force redirect if already on the current route.

  • Summary notifications (end of import/export) use target_json.path to navigate to the grid route.

Relevant AppSettings

Description: these keys enable the notification subsystem and the table change listening mode.

  • "Notifications.Enabled": true
  • "Notifications.Mode": "SqlDependency" or "Polling"
  • "Notifications.PollSeconds": 5
  • Enabled: enables/disables the entire notification subsystem.
  • Mode: SqlDependency or Polling.
  • PollSeconds: interval in seconds when Mode = Polling.

Frontend support:

  • WtoolboxService.appSettings.notifications.enabled (and compatible aliases) can hide the bell and stop the real-time connection on the client side.

Result:

  • backend and frontend stay aligned on notification state;
  • in SqlDependency the UI receives push updates, in Polling it uses periodic refresh.

Screenshot

Notification UI state in the Notifications subsection
Notification UI state in the Notifications subsection