Physical model for data storage
| 🌐 This document is available in both English and Ukrainian. Use the language toggle in the top right corner to switch between versions. |
As part of the implementation of functional requirements, it is necessary to create a separate scheme NOTIFICATIONS and expand the physical model with additional tables:
-
NOTIFICATION_TEMPLATE - storage of notification template data
-
NOTIFICATION_TEMPLATE_ATTR - storage of additional template attributes depending on the requirements of a separate communication channel
-
INBOX_NOTIFICATION - storage of in-app user notifications for display in Citizen portal
1. Data structure
1.1. Notification Template Data Structure (NOTIFICATION_TEMPLATE)
| Field | Type | Restriction | Default Value | Description |
|---|---|---|---|---|
ID |
UUID |
Primary Key |
uuid_generate_v4() |
A unique auto-generated identifier for the record |
NAME |
TEXT |
Not Null, Unique Constraint* |
- |
Service name of the notification template |
CHANNEL |
ENUM |
Not Null, Unique Constraint*, [inbox, email, diia] |
- |
The communication channel for using the message template |
TITLE |
TEXT |
- |
- |
Text header of notification |
CONTENT |
TEXT |
Not Null |
- |
Notification body text template for further filling with data |
CHECKSUM |
TEXT |
Not Null |
- |
SHA-256 checksum generated for the contents of the CONTENT field |
CREATED_AT |
TIMESTAMP |
Not Null |
now() |
Date/Time of creation/publishing template |
UPDATED_AT |
TIMESTAMP |
Not Null |
now() |
Date/Time of the last modification within the template publication |
EXT_TEMPLATE_ID |
TEXT |
Unique Constraint |
- |
The unique identifier of the record in the external system |
EXT_PUBLISHED_AT |
TIMESTAMP |
- |
Date/Time of the last publication of the record in the external system |
1.2. Notification Template Attribute Data Structure (NOTIFICATION_TEMPLATE_ATTR)
| Field | Type | Restriction | Default value | Description |
|---|---|---|---|---|
ID |
UUID |
Primary Key |
uuid_generate_v4() |
A unique auto-generated identifier for the record |
TEMPLATE_ID |
UUID |
Not Null, Foreign Key |
- |
Communication with the record NOTIFICATION_TEMPLATE |
NAME |
TEXT |
Not Null |
- |
Attribute service name |
VALUE |
TEXT |
Not Null |
- |
Attribute value |
1.3. Data structure of in-app notifications INBOX_NOTIFICATION
| Field | Type | Restriction | Default value | Description |
|---|---|---|---|---|
ID |
UUID |
Primary Key |
uuid_generate_v4() |
A unique auto-generated identifier for the record |
RECIPIENT_ID |
TEXT |
Not Null |
- |
The ID of the user who should receive the message |
SUBJECT |
TEXT |
Not Null |
- |
Notification header |
MESSAGE |
TEXT |
Not Null |
- |
Notification content |
IS_ACKNOWLEDGED |
BOOLEAN |
Not Null |
false |
Notification status (read/unread) |
CREATED_AT |
TIMESTAMP |
Not Null |
now() |
Date/Time creation of notification |
UPDATED_AT |
TIMESTAMP |
Not Null |
now() |
Date/Notification update time |
2. Roles/ system users of DB
To maintain database interaction operations, it is necessary to create roles/users with defined access rights for use by the relevant system components:
| Component of the system | Role/User | Privileges |
|---|---|---|
notification-service |
notification_service_user |
GRANT SELECT, INSERT, UPDATE, DELETE ON NOTIFICATION_TEMPLATE GRANT SELECT, INSERT, UPDATE, DELETE ON NOTIFICATION_TEMPLATE_ATTR GRANT SELECT, INSERT, UPDATE, DELETE ON INBOX_NOTIFICATION |