TJNotifications - Database Schema

Database Schema

The notifications uses the following tables.


This table contains 3 sets of columns per provider. When we add support for more providers, we will need to add 3 columns for each. the _status column indicates if that provider is enabled. The _body column stores the default body for the template. The _subject column is for providers that need a subject / title (eg: email, notifications).

Column Name Data Type
id INT
client VARCHAR
email_status INT
sms_status INT
push_status INT
web_status INT
email_body TEXT
sms_body TEXT
push_body TEXT
web_body TEXT
email_subject TEXT
sms_subject TEXT
push_subject TEXT
web_subject TEXT
state INT
created_on DATE
updated_on DATE
is_override INT


Master table for the providers

Column Name Data Type
provider VARCHAR
state INT


This table stores the user level settings. If a user disables some emails for some providers, then that exclusion is added to this table.

Column Name Data Type
user_id INT
client VARCHAR
provider VARCHAR


This table has a queue of outbound communications. TJNotifications adds its communication to this queue.

Column Name Data Type Index Comments
queue_id INT Primary
provider VARCHAR INDEX Communication provider, eg: email, sms
context_id VARCHAR INDEX
body TEXT
subject TEXT
recipient VARCHAR The recipient changes based on the provider. Eg: If provider is email, the recipient is an email. If the provider is SMS, the recipient is phone number
created DATETIME
retries INT How many times was sending tried
last_attempted DATETIME For future use. Stores the last attempt date if it is being retried multiple times
status TINYINT INDEX Status of sending. 1=Sent, 0=Not sent
options TEXT

Last update: June 18, 2020