US13
Create Broadcast Message Management
As a Super Admin or Admin, I want to create and manage broadcast messages targeted at all or selected tenants so that important announcements are stored centrally and consumed by the Intelli Broadcast application when it is ready.
Context
A separate team is building the Intelli Broadcast application, which will surface broadcast messages to tenant universities and their users. The Norvia Backoffice is responsible for authoring and managing these messages; the Intelli Broadcast application will read from the shared NorviaBroadcast table. The Backoffice side is being implemented now so that the schema and data are ready before the broadcast application is complete.
Workflow
- A Super Admin or Admin opens Broadcast Management in the Backoffice.
- They create a new broadcast by supplying a Message, an optional Description, and a Tenant target — either a specific tenant from the dropdown or All Tenants.
- When All Tenants is selected the record is saved with
TenantID = 0; when a specific tenant is chosen the actual TenantID is stored. - The record is saved with
IsActive = 1 and CreatedDate set to the current UTC timestamp. - The Intelli Broadcast application (when ready) queries
NorviaBroadcast for active records where TenantID = 0 (all tenants) or matches the consuming tenant's ID. - The operator can filter the list by Tenant and by a Created Date range to locate records quickly.
- The operator can delete a message (hard delete or deactivate via
IsActive = 0) from the Backoffice; the broadcast application will no longer serve it once it is inactive or removed.
Database — Create Table
Run the following script once against the target database. The guard ensures the table is only created if it does not already exist:
IF NOT EXISTS (
SELECT 1 FROM sys.tables
WHERE name = 'NorviaBroadcast'
AND SCHEMA_NAME(schema_id) = 'dbo'
)
BEGIN
CREATE TABLE dbo.NorviaBroadcast (
ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
TenantID INT NULL, -- 0 = all tenants; specific ID = targeted tenant
Message NVARCHAR(500) NOT NULL,
Description NVARCHAR(MAX) NULL,
CreatedDate DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME(),
IsActive BIT NOT NULL DEFAULT 1
);
CREATE INDEX IX_NorviaBroadcast_TenantID
ON dbo.NorviaBroadcast (TenantID)
INCLUDE (IsActive, CreatedDate);
END
TenantID is nullable for schema flexibility. By convention, TenantID = 0 means the message targets all tenants. A future migration can tighten this with a foreign-key constraint once the broadcast application's read contract is confirmed.
API / back-end requirements
- Create and delete are restricted to Super Admin and Admin roles; Users cannot access this feature.
- On create, validate that Message is non-empty and that TenantID is either 0 (all) or a valid active tenant ID.
- Provide a list endpoint that supports filtering by
TenantID and a CreatedDate range (from/to). - Delete may be a soft delete (set
IsActive = 0) or hard delete, per team decision; document the chosen behaviour in the API contract. - The table must not be modified by the Backoffice beyond the fields it owns — the Intelli Broadcast application is a read-only consumer of this table.
UI requirements
- List view shows Message (truncated), Tenant (name or "All Tenants"), Created Date, and Active status.
- Filter bar provides a Tenant dropdown (including an "All Tenants" option) and a Created Date range picker.
- Create form collects Message (required), Description (optional), and Tenant target (required — dropdown with "All Tenants" as the first option).
- Each row has a Delete action; a confirmation prompt is shown before deletion.
- No edit/update form is required for this story — messages are write-once; amendment is handled by delete and recreate.
Acceptance criteria
- The CREATE TABLE script runs without error on the development database and is idempotent (safe to re-run).
- A broadcast with
TenantID = 0 is saved when "All Tenants" is selected. - A broadcast with a specific
TenantID is saved when a named tenant is selected; only valid active tenants appear in the dropdown. - The list can be filtered by Tenant (including "All Tenants") and by Created Date range, independently or combined.
- Deleting a message removes or deactivates it; the confirmation prompt is shown before the action is committed.
- Admin and User roles cannot create or delete messages; the feature is not visible in navigation for the User role.
- The Intelli Broadcast application team is provided the table schema and the
TenantID = 0 convention as part of handoff documentation.