Postgres User Analytics Database

Introduction

This database is built using PostgreSQL, and its primary role is to keep track of the delivery status of notifications and their current status. It helps us generate final reports that show which notifications were sent, which users received them, and which users clicked on them. We use this database to store information about messages that were successfully delivered and messages that were read by users.

1. Table Schema

• Table Name - delivery_report

Column Name	Type	Description
id	UUID	Unique identifier for the record.
external_id	Varchar	A unique identifier generated by the transaction layer when sending notifications. This identifier is used on the Android side to manage notifications.
user_id	Varchar	Identifier of the user its kind of Mobile/Phone number.
bot_id	Varchar	This is the bot id to identifiy bot related information.
bot_name	Varchar	This is the bot name to show the bot name on the ui.
fcm_token	Varchar	Firebase Cloud Messaging (FCM) token for notifications.
message_state	Varchar	State or status of the message like DELIVERED, READ.
cass_id	Varchar	Cassandra id to check the notification related information like xmessage, fcm messageid etc.
created_on	Timestamp	Timestamp indicating when the record was created.
---