# Notifications Database Design

---

**Document Version:** 1.0
**Project:** Student Bazaar
**Document Type:** Notifications Database Schema
**Status:** Approved
**Last Updated:** 28 June 2026
**Author:** Nemmi Tangha

---

# 1. Overview

The Notifications module informs students and Super Admins about important activities occurring within the platform.

A single notifications table is used to support all notification types, providing a flexible and scalable design.

The module consists of one table:

1. notifications

The Notifications module integrates with every major module in Student Bazaar.

---

# 2. Table: notifications

## Purpose

Stores notifications generated by the system for students and Super Admins.

Notifications may relate to listings, offers, chats, meet-ups, reports, advertisements, or system events.

---

## Columns

| Column              | Data Type                                                                                                                           | Nullable | Default           | Key    | Description                    |
| ------------------- | ----------------------------------------------------------------------------------------------------------------------------------- | -------- | ----------------- | ------ | ------------------------------ |
| id                  | BIGINT                                                                                                                              | No       | AUTO_INCREMENT    | PK     | Internal primary key           |
| public_id           | CHAR(36)                                                                                                                            | No       | UUID              | UNIQUE | Public identifier              |
| user_id             | BIGINT                                                                                                                              | No       | —                 | FK     | References users.id            |
| notification_type   | ENUM('message','offer','offer_accepted','offer_rejected','meetup','favorite','listing','premium','advertisement','report','system') | No       | system            | INDEX  | Notification category          |
| title               | VARCHAR(150)                                                                                                                        | No       | —                 | —      | Notification title             |
| message             | VARCHAR(500)                                                                                                                        | No       | —                 | —      | Notification content           |
| related_entity_type | ENUM('listing','offer','conversation','message','meetup','report','advertisement','subscription','system')                          | Yes      | NULL              | INDEX  | Related entity type            |
| related_entity_id   | BIGINT                                                                                                                              | Yes      | NULL              | INDEX  | Related entity identifier      |
| read_at             | DATETIME                                                                                                                            | Yes      | NULL              | INDEX  | Time the notification was read |
| expires_at          | DATETIME                                                                                                                            | Yes      | NULL              | INDEX  | Optional expiration timestamp  |
| created_at          | DATETIME                                                                                                                            | No       | CURRENT_TIMESTAMP | INDEX  | Record creation timestamp      |

---

## Relationships

* One User → Many Notifications

---

## Indexes

* PRIMARY KEY (`id`)
* UNIQUE (`public_id`)
* INDEX (`user_id`)
* INDEX (`notification_type`)
* INDEX (`related_entity_type`)
* INDEX (`related_entity_id`)
* INDEX (`read_at`)
* INDEX (`expires_at`)
* INDEX (`created_at`)

---

## Business Rules

* Every notification belongs to one user.
* Notifications are created automatically by the system.
* Notifications become read when `read_at` is populated.
* Expired notifications may be automatically removed or archived.
* Notifications should not expose sensitive information.

---

# 3. Notification Workflow

```text
Platform Event
        │
        ▼
Notification Created
        │
        ▼
Displayed to User
        │
        ▼
User Opens Notification
        │
        ▼
read_at Updated
```

---

# 4. Notification Constraints

The Notifications module follows these constraints:

* Notifications cannot exist without a recipient.
* Related entity references should correspond to valid records.
* Notifications cannot be edited after creation.
* Notifications may expire automatically.

---

# 5. Notification Security Rules

The Notifications module enforces the following security measures:

* Only authenticated users may view their notifications.
* Users may access only their own notifications.
* SQLAlchemy ORM must be used for all database operations.
* Notification content must be validated before storage.
* Unauthorized access attempts should be logged.

---

# 6. Module Integration

## Marketplace

Generates notifications for:

* Listing approval (future)
* Listing expiration
* Favorite activity

---

## Negotiation

Generates notifications when:

* A new offer is received.
* An offer is accepted.
* An offer is rejected.
* An offer expires.

---

## Chat

Generates notifications when:

* A new message is received.
* Additional chat messages are unlocked.

---

## Meet-up

Generates notifications when:

* A meet-up is scheduled.
* A meet-up is updated.
* A meet-up is cancelled.
* A meet-up is completed.

---

## Premium

Generates notifications when:

* Premium membership is activated.
* Premium membership is nearing expiration.
* Premium membership expires.

---

## Administration

Generates notifications for:

* Report updates.
* Administrative announcements.
* System announcements.

---

# 7. Summary

The Notifications module provides a unified notification system for Student Bazaar.

The design supports:

* Marketplace events
* Offer updates
* Chat notifications
* Meet-up reminders
* Premium reminders
* Administrative announcements
* Future scalability

The module follows the approved Student Bazaar database standards and provides a flexible foundation for all notification functionality.