# Chat Database Design --- **Document Version:** 1.0 **Project:** Student Bazaar **Document Type:** Chat Database Schema **Status:** Approved **Last Updated:** 28 June 2026 **Author:** Nemmi Tangha --- # 1. Overview The Chat module enables secure communication between students regarding marketplace listings. Each conversation is associated with a single listing and allows the buyer and seller to negotiate, ask questions, and coordinate a meet-up. The Chat module also supports the platform's monetization model through message limits, rewarded advertisements, and Premium memberships. The module consists of two tables: 1. conversations 2. messages --- # 2. Table: conversations ## Purpose Stores conversation metadata between two students for a specific listing. A conversation is unique for one buyer, one seller, and one listing. --- ## 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 | | listing_id | BIGINT | No | — | FK | References listings.id | | buyer_id | BIGINT | No | — | FK | References users.id | | seller_id | BIGINT | No | — | FK | References users.id | | status | ENUM('active','closed') | No | active | INDEX | Current conversation status | | conversation_started_at | DATETIME | No | CURRENT_TIMESTAMP | — | Time the conversation was created | | first_reply_at | DATETIME | Yes | NULL | — | Time the first reply was received | | free_messages_remaining | INT | No | 10 | — | Remaining free messages after the counter begins | | rewarded_messages_remaining | INT | No | 0 | — | Additional messages unlocked by rewarded ads | | ads_watched | INT | No | 0 | — | Number of rewarded advertisements watched | | is_premium_conversation | BOOLEAN | No | FALSE | INDEX | Indicates unlimited messaging for this conversation | | last_message_at | DATETIME | Yes | NULL | INDEX | Timestamp of the latest message | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation timestamp | | updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update timestamp | --- ## Relationships * One Listing → Many Conversations * One Student → Many Conversations * One Conversation → Many Messages --- ## Indexes * PRIMARY KEY (`id`) * UNIQUE (`public_id`) * INDEX (`listing_id`) * INDEX (`buyer_id`) * INDEX (`seller_id`) * INDEX (`status`) * INDEX (`is_premium_conversation`) * INDEX (`last_message_at`) * INDEX (`created_at`) --- ## Business Rules * Only one conversation may exist between the same buyer, seller, and listing. * Both participants may continue using the same conversation until it is closed. * Each participant is counted independently from their first message. * Free users receive 10 messages per conversation (per participant). * Watching one rewarded advertisement unlocks 5 additional messages. * Premium users have unlimited messaging. * Every new message updates `last_message_at`. * Closed conversations become read-only. --- # 3. Table: messages ## Purpose Stores all chat messages exchanged within a conversation. --- ## 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 | | conversation_id | BIGINT | No | — | FK | References conversations.id | | sender_id | BIGINT | No | — | FK | References users.id | | message | TEXT | No | — | — | Message content | | message_type | ENUM('text','system') | No | text | INDEX | Type of message | | read_at | DATETIME | Yes | NULL | INDEX | Time the message was read | | deleted_at | DATETIME | Yes | NULL | INDEX | Soft deletion timestamp | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation timestamp | --- ## Relationships * One Conversation → Many Messages * One Student → Many Sent Messages --- ## Indexes * PRIMARY KEY (`id`) * UNIQUE (`public_id`) * INDEX (`conversation_id`) * INDEX (`sender_id`) * INDEX (`message_type`) * INDEX (`read_at`) * INDEX (`deleted_at`) * INDEX (`created_at`) --- ## Business Rules * Messages belong to exactly one conversation. * Only conversation participants may send or view messages. * Messages cannot be edited after sending. * Deleted messages are soft deleted. * System messages are generated automatically by the platform. * Every message updates the conversation's `last_message_at`. --- # 4. Chat Workflow The messaging workflow follows this sequence: ```text Conversation Created │ ▼ First Student Sends Message │ ▼ Second Student Replies │ ▼ 10 Free Messages Become Active │ ▼ Message Limit Reached │ ▼ Watch Rewarded Advertisement │ ▼ +5 Messages Unlocked ``` Premium users bypass all message limits. --- # 5. Chat Constraints The Chat module follows these constraints: * One conversation per buyer, seller, and listing. * Messages cannot exist without a conversation. * Only conversation participants may send messages. * Message limits apply only to free users. * Rewarded advertisements unlock exactly 5 additional messages. * Closed conversations cannot receive new messages. --- # 6. Chat Security Rules The Chat module enforces the following security measures: * Authentication is required for all chat actions. * Ownership validation is required before viewing a conversation. * SQLAlchemy ORM must be used for all database operations. * All inputs must be validated before storage. * Soft deletion is used for message removal. * Security events should be logged for deleted messages and blocked messaging attempts. --- # 7. Communication Policy Student Bazaar requires all negotiations to remain within the platform. Before an offer has been accepted, students must not exchange: * Phone numbers * WhatsApp numbers * Email addresses * Telegram usernames * Instagram usernames * Discord usernames * Other external contact information Once an offer has been accepted and a meet-up has been scheduled, students may exchange contact information if necessary. --- # 8. Module Integration ## Marketplace * Links conversations to listings. * Automatically closes conversations when appropriate. --- ## Negotiation * Supports discussion during price negotiation. * Continues after counter-offers until an agreement is reached. --- ## Advertisements * Rewarded advertisements unlock five additional messages. * Advertisement statistics update `ads_watched`. --- ## Premium * Premium memberships enable unlimited messaging. * Message counters are ignored for Premium conversations. --- ## Notifications Notifications are generated when: * A new message is received. * Additional messages are unlocked. * A conversation is closed. --- # 9. Summary The Chat module provides secure communication between students while supporting Student Bazaar's monetization strategy through message limits, rewarded advertisements, and Premium memberships. The design supports: * Conversation management * Message history * Read tracking * Rewarded advertisements * Premium messaging * Marketplace integration * Negotiation support * Future scalability The module follows the approved Student Bazaar database standards and provides the foundation for all in-platform communication.