Notifications operational database

🌐 This document is available in both English and Ukrainian. Use the language toggle in the top right corner to switch between versions.

1. General overview

The purpose of the notifications database is to store operational data related to templates and records of sent inbox notifications to users.

2. Database schema

Legend

Diagram notation Table notation Meaning

Diagram notation	Table notation	Meaning
ο and [PK]	`(PK)`	Primary key
[FK]	`(references table.column)`	Foreign key
●	`column name` in bold	Required field (not null)
	`column name` in regular font	Optional field (null)

ο and [PK]

(PK)

Primary key

[FK]

(references table.column)

Foreign key

●

column name in bold

Required field (not null)

column name in regular font

Optional field (null)

3. Data structures

3.1. inbox_notification

In-app user messages for display in the Citizen portal

Column name Description
`id uuid (PK)` Row identifier
`recipient_id text` Identifier of the user receiving the message
`subject text` Message subject
`message text` Message content
`is_acknowledged boolean` Message status (read/unread)
`created_at timestamp with time zone` Date/time of message creation
`updated_at timestamp with time zone` Date/time of message update

Column name
Description

id uuid (PK)
Row identifier

recipient_id text
Identifier of the user receiving the message

subject text
Message subject

message text
Message content

is_acknowledged boolean
Message status (read/unread)

created_at timestamp with time zone
Date/time of message creation

updated_at timestamp with time zone
Date/time of message update

3.2. notification_template

Notification template data

Column name Description
`id uuid (PK)` Row identifier
`name text` Template message internal name. Unique in combination with `channel`.
`channel text` Communication channel for using the message template. Unique in combination with `name`. inbox - Citizen portal email - email diia - Diia application (Ukrainian citizen-facing solution, UA-specific)
`content text` Text template of the message body for future data filling
`checksum text` SHA-256 checksum generated for the CONTENT field
`created_at timestamp with time zone` Date/time of template creation/publication
`updated_at timestamp with time zone` Date/time of last change within template publication
`title text` Textual message title
`ext_template_id text` Unique identifier of the template copy saved in an external system for further message sending using it
`ext_published_at timestamp with time zone` Date/Time of the last publication of the record in the external systemBD

Column name
Description

id uuid (PK)
Row identifier

name text
Template message internal name. Unique in combination with channel.

channel text

Communication channel for using the message template. Unique in combination with name.

inbox - Citizen portal
email - email
diia - Diia application (Ukrainian citizen-facing solution, UA-specific)

content text
Text template of the message body for future data filling

checksum text
SHA-256 checksum generated for the CONTENT field

created_at timestamp with time zone
Date/time of template creation/publication

updated_at timestamp with time zone
Date/time of last change within template publication

title text
Textual message title

ext_template_id text
Unique identifier of the template copy saved in an external system for further message sending using it

ext_published_at timestamp with time zone
Date/Time of the last publication of the record in the external systemBD

Uniqueness constraints

Uniqueness constraints
`ext_template_id`
`name,channel`

ext_template_id

name,channel

3.3. notification_template_attr

Additional attributes of templates based on the requirements of a specific communication channel

Column name Description
`id uuid (PK)` Row identifier
`template_id uuid` (references notification_template.id) Identifier of the message template
`name text` Attribute’s operational name
`value text` Attribute’s value

Column name
Description

id uuid (PK)
Row identifier

template_id uuid (references notification_template.id)
Identifier of the message template

name text
Attribute’s operational name

value text
Attribute’s value