Notifications Repository #

General #

The Notifications repository is hosted as an Azure SQL as a Service database.

Security #

This repository has been locked down to allow access only from the Azure Function layer within the ProductEntitlements NSG (Network Security Group). No other access is enabled.

In addition to the caller reztrictions, the database can only be interacted with via the use of stored procedures - direct database access is blocked with the default user accounts created.

Entity Relationship Diagram #

The following diagram represnets the basic data storage diagram for the Notifications system.

erd

It is a simple structure based on the requirements defined for the API. Each entity type is based on a primary key (uuid) for each of the elements. The remaining elements are either descriptive in nature or provide metadata context around the objects themselves.

The Notifications and DeletedNotifications tables are simple singular entity tables - containing one entry for each each message sent. The Enumerations table maps defined enumeration values for each of these tables.

All access is only through the Stored Procedures.

Entity Descriptions #

Enumerations #

The Enumerations table consists of three fields:

Field PK Format Description
id X int unique identifier for the enum
type nvarchar (50) the type of enumeration
value nvarchar (100) the value of the enumeration to use

The only index on this table is that of the primary key (id).

This table contains enumerations for status and other properties related to a message.

Notifications #

The Notifications table consists of the following fields:

Field PK Format Description
id X UUID unique identifier for the notification / message
correlationId UUID unique identifier used to track a message across systems
userId UUID unique identifier for the user receiving the message
companyId UUID unique identifier for the users company
deliveryType int delivery type for notification
priority int message priority
subject nvarchar (200) subject content
message nvarchar (4000) message content
source nvarchar (1000) source of message
email nvarchar (1000) email address of recipient (optionl)
phone nvarchar (50) phone number of recipient (optional)
postedTime datetime notification posted time (received by service) (optional)
sentTime datetime notification sent time (from provider) (optional)
deliveredTime datetime notification delivered time (from provider) (optional)
readTime datetime time message was read (optional)
deletedTime datetime time message was deleted by user (optional)
status int message status
smsFK nvarchar (1000) sms provider message foreign key (optional)
emailFK nvarchar (1000) email provider foreign key (optional)

In additon to the primary key on the notification id, the following indexes also exist on this table:

  • correlationId - to facilitate searching by correlationId
  • emailFK - to provide quick searches to update records as email events sent from provider
  • smsFK - to provide quick searches to update recrods as sms events sent from the provider
  • companyId - to facilitate searching for all records for a company
  • userID - to facilitate searching for all messages for a given user

All int fields are FK related to the enumerations table.

DeletedNotifications #

The DeletedNotifications table* represents a deleted message entry. It consists of the following fields:

Field PK Format Description
id X UUID unique identifier for the notification / message
correlationId UUID unique identifier used to track a message across systems
userId UUID unique identifier for the user receiving the message
companyId UUID unique identifier for the users company
deliveryType int delivery type for notification
priority int message priority
subject nvarchar (200) subject content
message nvarchar (4000) message content
source nvarchar (1000) source of message
email nvarchar (1000) email address of recipient (optionl)
phone nvarchar (50) phone number of recipient (optional)
postedTime datetime notification posted time (received by service) (optional)
sentTime datetime notification sent time (from provider) (optional)
deliveredTime datetime notification delivered time (from provider) (optional)
readTime datetime time message was read (optional)
deletedTime datetime time message was deleted by user (optional)
status int message status
smsFK nvarchar (1000) sms provider message foreign key (optional)
emailFK nvarchar (1000) email provider foreign key (optional)

In additon to the primary key on the notification id, the following indexes also exist on this table:

  • correlationId - to facilitate searching by correlationId
  • emailFK - to provide quick searches to update records as email events sent from provider
  • smsFK - to provide quick searches to update recrods as sms events sent from the provider
  • companyId - to facilitate searching for all records for a company
  • userID - to facilitate searching for all messages for a given user

All int fields are FK related to the enumerations table.

Database Creation Scripts #

The database creation scripts for the product entitlements service can be downloaded from here.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- ***********************
-- **   CREATE TABLES   **
-- ***********************

-- CREATE THE ENUMERATIONS TABLE
CREATE TABLE [dbo].[Enumerations](
	[id] [int] NOT NULL IDENTITY,
	[type] [nvarchar](50) NOT NULL,
    [value] [nvarchar](100) NOT NULL
 CONSTRAINT [PK_Enumerations] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE INDEX idx_EnumerationType ON dbo.Enumerations ([type])
GO

-- CREATE NOTIFICATIONS TABLE
CREATE TABLE [dbo].[Notifications](
	[id] [uniqueidentifier] NOT NULL,
    [correlationId] [uniqueidentifier] NULL,
	[userId] [uniqueidentifier] NOT NULL,
    [companyId] [uniqueidentifier] NOT NULL,
	[deliverytype] [int] NOT NULL,
    [priority] [int] NOT NULL,
    [subject] [nvarchar](200) NULL,
    [message] [nvarchar](4000) NOT NULL,
    [source] [nvarchar] (1000) NOT NULL,
    [email] [nvarchar](1000) NULL,
    [phone] [nvarchar](50) NULL,
    [postedTime] [datetime] NOT NULL,
    [sentTime] [datetime] NULL,
    [deliveredTime] [datetime] NULL,
    [readTime] [datetime] NULL,
    [deletedTime] [datetime] NULL,
    [status] [int] NOT NULL,
    [smsFK] [nvarchar](1000) NULL,
    [emailFK] [nvarchar](1000) NULL
 CONSTRAINT [PK_Notifications] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Notifications] ADD  CONSTRAINT [DF_Notifications_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[Notifications]  WITH CHECK ADD  CONSTRAINT [FK_Notifications_Enumerations] FOREIGN KEY([status])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[Notifications]  WITH CHECK ADD  CONSTRAINT [FK_Notifications_Enumerations2] FOREIGN KEY([priority])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[Notifications]  WITH CHECK ADD  CONSTRAINT [FK_Notifications_Enumerations3] FOREIGN KEY([deliverytype])
REFERENCES [dbo].[Enumerations] ([id])
GO
CREATE INDEX idx_NotificationsUserId ON dbo.Notifications ([userId])
GO
CREATE INDEX idx_NotificationsCompanyId ON dbo.Notifications ([companyId])
GO
CREATE INDEX idx_NotificationsByCorrelationId ON dbo.Notifications ([correlationId])
GO
CREATE INDEX idx_NotificationsBySmsFK ON dbo.Notifications ([smsFK])
GO
CREATE INDEX idx_NotificationsByEmailFK ON dbo.Notifications ([emailFK])
GO
-- CREATE THE DELETED NOTIFICATIONS TABLE
CREATE TABLE [dbo].[DeletedNotifications](
	[id] [uniqueidentifier] NOT NULL,
    [correlationId] [uniqueidentifier] NULL,
	[userId] [uniqueidentifier] NOT NULL,
    [companyId] [uniqueidentifier] NOT NULL,
	[deliverytype] [int] NOT NULL,
    [priority] [int] NOT NULL,
    [subject] [nvarchar](200) NULL,
    [message] [nvarchar](4000) NOT NULL,
    [source] [nvarchar] (1000) NOT NULL,
    [email] [nvarchar](1000) NULL,
    [phone] [nvarchar](50) NULL,
    [postedTime] [datetime] NOT NULL,
    [sentTime] [datetime] NULL,
    [deliveredTime] [datetime] NULL,
    [readTime] [datetime] NULL,
    [deletedTime] [datetime] NULL,
    [status] [int] NOT NULL,
    [smsFK] [nvarchar](1000) NULL,
    [emailFK] [nvarchar](1000) NULL
 CONSTRAINT [PK_DeletedNotifications] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DeletedNotifications]  WITH CHECK ADD  CONSTRAINT [FK_DeletedNotifications_Enumerations] FOREIGN KEY([status])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[DeletedNotifications]  WITH CHECK ADD  CONSTRAINT [FK_DeletedNotifications_Enumerations2] FOREIGN KEY([priority])
REFERENCES [dbo].[Enumerations] ([id])
GO
ALTER TABLE [dbo].[DeletedNotifications]  WITH CHECK ADD  CONSTRAINT [FK_DeletedNotifications_Enumerations3] FOREIGN KEY([deliverytype])
REFERENCES [dbo].[Enumerations] ([id])
GO
CREATE INDEX idx_DeletedNotificationsUserId ON dbo.DeletedNotifications ([userId])
GO
CREATE INDEX idx_DeletedNotificationsCompanyId ON dbo.DeletedNotifications ([companyId])
GO
CREATE INDEX idx_DeletedNotificationsCorrelationId ON dbo.DeletedNotifications ([correlationId])
GO
CREATE INDEX idx_DeletedNotificationsBySmsFK ON dbo.DeletedNotifications ([smsFK])
GO
CREATE INDEX idx_DeletedNotificationsByEmailFK ON dbo.DeletedNotifications ([emailFK])
GO

-- ***********************
-- ** CREATE PROCEDURES **
-- ***********************

-- GET ENUMERATIONS BY TYPE
CREATE PROCEDURE usp_getEnum_ByType
(
    @type nvarchar(20)
)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT
        *
    FROM [dbo].[Enumerations]
    WHERE [type] = @type
    ORDER BY id
END
GO

-- GET ALL MESSAGES WITH a SPECIFIC NotificationID
CREATE PROCEDURE usp_getNotifications_ById
(
    @limit INT = 10,
    @skip INT = 0,
    @id UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE id = @id 
        UNION 
        SELECT * FROM DeletedNotifications WHERE id = @id
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [id] = @id)
        +
        (SELECT COUNT(*) as totalcount2 
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [id] = @id)
	RETURN @TOTALROWS
END 
GO

-- GET ALL ACTIVE MESSAGES FOR A USER
CREATE PROCEDURE usp_getNotifications_Active_ByUserId
(
    @limit INT = 10,
    @skip INT = 0,
    @userId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT
        *
    FROM [dbo].[Notifications]
    WHERE [userId] = @userId
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [userId] = @userId)
	RETURN @TOTALROWS
END 
GO

-- GET ALL MESSAGES WITH a SPECIFIC CORRELATIONID
CREATE PROCEDURE usp_getNotifications_ByCorrelationId
(
    @limit INT = 10,
    @skip INT = 0,
    @correlationId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE correlationId = @correlationId 
        UNION 
        SELECT * FROM DeletedNotifications WHERE correlationId = @correlationId
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [correlationId] = @correlationId)
        +
        (SELECT COUNT(*) as totalcount2
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [correlationId] = @correlationId)
	RETURN @TOTALROWS
END 
GO

-- GET ALL MESSAGES WITH a SPECIFIC COMPANYID
CREATE PROCEDURE usp_getNotifications_ByCompanyId
(
    @limit INT = 10,
    @skip INT = 0,
    @companyId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE companyId = @companyId 
        UNION 
        SELECT * FROM DeletedNotifications WHERE companyId = @companyId
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [companyId] = @companyId)
        + 
        (SELECT COUNT(*) as totalcount2
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [companyId] = @companyId)
	RETURN @TOTALROWS
END 
GO

-- GET ALL MESSAGES FOR A USER (BOTH ACTIVE AND DELETED)
CREATE PROCEDURE usp_getNotifications_ByUserId
(
    @limit INT = 10,
    @skip INT = 0,
    @userId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    (
        SELECT * FROM Notifications WHERE userId = @userId 
        UNION 
        SELECT * FROM DeletedNotifications WHERE userId = @userId
    )
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [userId] = @userId)
        + 
        (SELECT COUNT(*) as totalcount2
	    FROM [dbo].[DeletedNotifications] 
	    WHERE [userId] = @userId)
	RETURN @TOTALROWS
END 
GO

-- GET ONLY DELETED MESSAGES FOR A USER
CREATE PROCEDURE usp_getNotifications_Deleted_ByUserId
(
    @limit INT = 10,
    @skip INT = 0,
    @userId UNIQUEIDENTIFIER
)
AS 
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM DeletedNotifications WHERE userId = @userId
    ORDER BY postedTime
	OFFSET @skip ROWS
    FETCH NEXT @limit ROWS ONLY OPTION (RECOMPILE);

	DECLARE @TOTALROWS INT;
	SET @TOTALROWS = 
        (SELECT COUNT(*) as totalcount 
	    FROM [dbo].[Notifications] 
	    WHERE [userId] = @userId)
	RETURN @TOTALROWS
END 
GO

-- INSERT/ADD A NOTIFICATION
CREATE PROCEDURE [dbo].[usp_Notifications_Insert]
(
    @correlationId UNIQUEIDENTIFIER,
    @userId UNIQUEIDENTIFIER,
    @email NVARCHAR(1000),
    @phone NVARCHAR(50),
    @companyId UNIQUEIDENTIFIER,
    @priority INT,
    @deliverytype INT,
    @subject NVARCHAR(200),
    @message NVARCHAR(4000),
    @source NVARCHAR(1000),
    @postedTime DATETIME,
    @status INT
)
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @out_id UNIQUEIDENTIFIER
	SET @out_id = NEWID()
    INSERT INTO [dbo].[Notifications]
    (
		[id],
        [correlationId],
        [userId],
        [email],
        [phone],
        [companyId],
        [deliverytype],
        [priority],
        [subject],
        [message],
        [source],
        [postedTime],
        [status]
    )
    VALUES
    (
        @out_id,
        @correlationId,
		@userId,
        @email,
        @phone,
        @companyId,
        @deliverytype,
        @priority,
        @subject,
        @message,
        @source,
        @postedTime,
        @status
    )
	SELECT @out_id as id
END 
GO

-- UPDATE AN EXISTING NOTIFICATION - INTERNAL USER BY SNEDING PROVIDER
CREATE PROCEDURE [dbo].[usp_Notifications_UpdateFK]
(
    @id UNIQUEIDENTIFIER,
    @smsFK nvarchar (1000),
    @emailFK nvarchar (1000)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[Notifications]
    SET
        [smsFK] = @smsFK,
        [emailFK] = @emailFK
    WHERE
    (
        ([id] = @id) 
    )
END 
GO
-- UPDATE AN EXISTING NOTIFICATION - ONLY TIMESTAMPS ALLOWED
CREATE PROCEDURE [dbo].[usp_Notifications_Update]
(
    @id UNIQUEIDENTIFIER,
    @sentTime DATETIME,
    @deliveredTime DATETIME,
    @readTime DATETIME,
    @status INT
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE [dbo].[Notifications]
    SET
        [sentTime] = @sentTime,
        [deliveredTime] = @deliveredTime,
        [readTime] = @readTime,
        [status] = @status
    WHERE
    (
        ([id] = @id) 
    )
END 
GO
-- UPDATE AN EXISTING NOTIFICATION STATUS BY FK - 
CREATE PROCEDURE [dbo].[usp_Notifications_UpdateByFK]
(
    @fk NVARCHAR(1000),
    @timestamp DATETIME,
    @type NVARCHAR(20),
    @source NVARCHAR(20),
    @status INT
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @statement NVARCHAR(4000);
    DECLARE @paramDefinition NVARCHAR(4000)
    DECLARE @col NVARCHAR(20);
    DECLARE @keyCol NVARCHAR(20);
    SELECT @col = 
        CASE @type
            WHEN 'sent' THEN 'sentTime'
            WHEN 'delivered' THEN 'deliveredTime'
            WHEN 'read' THEN 'readTime'
            WHEN 'deleted' THEN 'deletedTime'
            ELSE 'invalid'
        END
    SELECT @keyCol = 
        CASE @source
            WHEN 'email' THEN 'emailFK'
            WHEN 'sms' THEN 'smsFK'
            ELSE 'invalid'
        END
    SET @statement = 'UPDATE [dbo].[Notifications] SET [' + @col + ']=''' + CAST( @timestamp AS NVARCHAR )+ ''', [status]='+ CAST(@status AS NVARCHAR) + ' WHERE ( [' + @keyCol + ']=''' + @fk + ''')'
    EXECUTE sp_executesql @statement
END 
GO
CREATE PROCEDURE [dbo].[usp_Notifications_Delete]
(
    @id uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;
    -- update the original entry to set deleted flag
    UPDATE [dbo].[Notifications]
    SET
        [deletedTime] = getutcdate()
    WHERE
    (
        ([id] = @id) 
    )
    -- create row in DeletedNotifications
    INSERT INTO [dbo].[DeletedNotifications]
    SELECT * FROM [dbo].[Notifications] WHERE [id] = @id
    -- remove original row
    DELETE FROM [dbo].[Notifications]
    WHERE [id] = @id
END 
GO
CREATE PROCEDURE [dbo].[usp_Notifications_DeleteByCorrelation]
(
    @correlationId uniqueidentifier
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with UPDATE statements.
    SET NOCOUNT ON;
    -- update the original entry to set deleted flag
    UPDATE [dbo].[Notifications]
    SET
        [deletedTime] = getutcdate()
    WHERE
    (
        ([correlationId] = @correlationId) 
    )
    -- create row in DeletedNotifications
    INSERT INTO [dbo].[DeletedNotifications]
    SELECT * FROM [dbo].[Notifications] WHERE [correlationId] = @correlationId
    -- remove original row
    DELETE FROM [dbo].[Notifications]
    WHERE [correlationId] = @correlationId
END 
GO
-- ***************************************
-- **   SET ENUMERATIONS TABLE VALUES   **
-- ***************************************
SET IDENTITY_INSERT dbo.Enumerations ON;  
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (1, 'PRIORITY', 'INFO')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (2, 'PRIORITY', 'WARNING')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (3, 'PRIORITY', 'ALERT')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (4, 'PRIORITY', 'CRITICAL')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (5, 'DELIVERYTYPE', 'SMS')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (6, 'DELIVERYTYPE', 'EMAIL')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (7, 'DELIVERYTYPE', 'NONE')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (8, 'STATUS', 'UNPROCESSED')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (9, 'STATUS', 'PROCESSING')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (10, 'STATUS', 'SENT')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (11, 'STATUS', 'DELIVERED')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (12, 'STATUS', 'READ')
GO
INSERT dbo.Enumerations ([id], [type], [value]) VALUES (13, 'STATUS', 'DELETED')
GO
SET IDENTITY_INSERT dbo.Enumerations OFF;  
GO

-- ****************************************
-- **   SET NOTIFICATIONS TABLE VALUES   **
-- ****************************************
INSERT dbo.Notifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('8fc9bacb-65f3-4178-807e-7312a583a695', 'd9e30bfa-4377-4e07-aaf5-6a7a60db06ce', '6ee32909-a9e7-4f85-b94e-8d2be6fbc055', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subject', 'message', 'SC.Test', null, null, '2020-10-01 06:00:00.000', null, null, null, null, 9)
GO
INSERT dbo.Notifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('1ff3c44f-5f6d-4f9c-9045-8188d56a12f8', 'fbf9c5a0-a69f-4226-bf43-1a657624540c', '6ee32909-a9e7-4f85-b94e-8d2be6fbc055', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subject2', 'message2', 'SC.Test', null, null, '2020-10-01 06:01:00.000', '2020-10-01 06:01:00.000', null, null, null, 10)
GO
INSERT dbo.Notifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('c113bfd3-48bc-413f-82d3-88d3f243080b', '267f4117-1800-47de-90d2-8e93c2cf1f3e', '2a839f9e-834b-4d50-af09-a3a97757ba68', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subject3', 'message3', 'SC.Test', null, null, '2020-10-01 06:05:00.000', null, null, null, null, 9)
GO
INSERT dbo.DeletedNotifications (id, correlationId, userId, companyId, deliverytype, priority, subject, message, source, email, phone,postedTime, sentTime, deliveredTime, readTime, deletedTime, status) VALUES ('9fc9bacb-65f3-4178-807e-7312a583a695', 'd9e30bfa-4377-4e07-aaf5-6a7a60db06ce', '6ee32909-a9e7-4f85-b94e-8d2be6fbc055', '42db421e-0ddb-4847-85ed-0ce5df677a6b', 7, 1, 'subjectD', 'messageD', 'SC.Test', null, null, '2020-10-01 05:00:00.000', '2020-10-01 05:01:00.000', '2020-10-01 05:02:00.000', '2020-10-01 05:03:00.000', '2020-10-01 05:04:00.000', 13)
GO