> For the complete documentation index, see [llms.txt](https://uci.sunbird.org/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://uci.sunbird.org/use/developer/database-schema/database-user-analytics.md).

# 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.                                                                                                       |
| ---            |           |                                                                                                                                                         |


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://uci.sunbird.org/use/developer/database-schema/database-user-analytics.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.