# Negotiation Database Design --- **Document Version:** 1.0 **Project:** Student Bazaar **Document Type:** Negotiation Database Schema **Status:** Approved **Last Updated:** 28 June 2026 **Author:** Nemmi Tangha --- # 1. Overview The Negotiation module enables students to negotiate listing prices through structured offers and counter-offers. Every offer submitted during a negotiation is stored as a separate record, preserving the complete negotiation history for each listing. The module consists of one table: 1. offers The Negotiation module integrates directly with the Marketplace, Chat, Meet-up, and Notifications modules. --- # 2. Table: offers ## Purpose Stores all offers and counter-offers exchanged between students for marketplace listings. Each record represents one negotiation action and contributes to the complete negotiation history. --- ## 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 | | sender_id | BIGINT | No | — | FK | References users.id | | receiver_id | BIGINT | No | — | FK | References users.id | | amount | DECIMAL(10,2) | No | — | — | Offered price | | offer_type | ENUM('initial_offer','counter_offer') | No | initial_offer | INDEX | Type of offer | | sequence_number | INT | No | 1 | — | Position of the offer within the negotiation | | status | ENUM('pending','accepted','rejected','withdrawn','expired') | No | pending | INDEX | Current offer status | | is_final_offer | BOOLEAN | No | FALSE | INDEX | Indicates whether this is the accepted final offer | | message | VARCHAR(300) | Yes | NULL | — | Optional message accompanying the offer | | expires_at | DATETIME | Yes | NULL | INDEX | Offer expiration timestamp | | response_at | DATETIME | Yes | NULL | INDEX | Date and time the offer was accepted, rejected, withdrawn, or expired | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation timestamp | | updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update timestamp | --- ## Relationships * One Listing → Many Offers * One Student → Many Sent Offers * One Student → Many Received Offers --- ## Indexes * PRIMARY KEY (`id`) * UNIQUE (`public_id`) * INDEX (`listing_id`) * INDEX (`sender_id`) * INDEX (`receiver_id`) * INDEX (`offer_type`) * INDEX (`status`) * INDEX (`is_final_offer`) * INDEX (`expires_at`) * INDEX (`response_at`) * INDEX (`created_at`) --- ## Business Rules * Every offer belongs to exactly one listing. * Every offer has one sender and one receiver. * The first offer for a listing must use `initial_offer`. * Every subsequent offer uses `counter_offer`. * Offer amounts must always be greater than zero. * Students cannot submit offers on their own listings. * Listings marked as **Sold** cannot receive new offers. * Listings marked as **Removed** cannot receive new offers. * Only one offer in a negotiation may have `is_final_offer = TRUE`. * Accepted offers become the final agreed selling price. * Offer history must never be deleted. * Withdrawn offers remain visible in the negotiation history. * Expired offers cannot be accepted. * `response_at` is recorded whenever an offer is accepted, rejected, withdrawn, or expires. --- # 3. Negotiation Workflow The negotiation process follows this sequence: ```text Listing Available │ ▼ Initial Offer │ ▼ Counter Offer(s) │ ▼ Accepted Offer │ ▼ Listing Status → Reserved │ ▼ Meet-up Scheduled │ ▼ Listing Status → Sold ``` Rejected, withdrawn, or expired offers end the current negotiation but do not prevent future negotiations while the listing remains available. --- # 4. Negotiation Constraints The Negotiation module follows these constraints: * Offers are linked to a single listing. * Sequence numbers increase sequentially. * Only the receiver of an offer may accept or reject it. * Offers cannot be edited after submission. * Negotiation history is permanent. * A student cannot negotiate with themselves. --- # 5. Negotiation Security Rules The Negotiation module enforces the following security measures: * Only authenticated students may submit offers. * Ownership validation is required before responding to an offer. * Unauthorized users cannot access private negotiations. * SQLAlchemy ORM must be used for all database operations. * Parameterized queries prevent SQL injection. * All offer data must be validated before processing. * Offer amounts must be positive decimal values. * Accepted, rejected, withdrawn, and expired offers should be logged for auditing. --- # 6. Module Integration ## Marketplace The Negotiation module: * Reads listing information. * Updates the listing status to **Reserved** after an accepted offer. * Updates the listing's `offer_count`. * Stores the agreed amount in `sold_price` when the sale is completed. --- ## Chat The Chat module allows students to negotiate and discuss listings. All communication before an agreement must occur through the built-in chat system. --- ## Meet-up A meet-up can only be scheduled after an offer has been accepted. --- ## Notifications Notifications are generated when: * A new offer is submitted. * A counter-offer is received. * An offer is accepted. * An offer is rejected. * An offer is withdrawn. * An offer expires. --- # 7. Summary The Negotiation module provides a complete and transparent bargaining system for Student Bazaar. The design supports: * Complete negotiation history * Counter-offers * Automatic listing status updates * Integration with Chat * Meet-up scheduling * Notification generation * Marketplace analytics * Future scalability The module follows the approved Student Bazaar database standards and provides the foundation for all price negotiation functionality.