> 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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

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

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.