# Administration Database Design --- **Document Version:** 1.0 **Project:** Student Bazaar **Document Type:** Administration Database Schema **Status:** Approved **Last Updated:** 28 June 2026 **Author:** Nemmi Tangha --- # 1. Overview The Administration module provides tools for platform management, moderation, premium memberships, advertising, reporting, and audit logging. The module consists of five tables: 1. reports 2. advertisements 3. premium_subscriptions 4. payments 5. admin_logs These tables are used exclusively by the Super Admin unless otherwise stated. --- # 2. Table: reports ## Purpose Stores reports submitted by students regarding listings, chats, or inappropriate behaviour. --- ## 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 | | reporter_id | BIGINT | No | FK | FK | Student submitting the report | | reported_user_id | BIGINT | Yes | NULL | FK | Reported student | | listing_id | BIGINT | Yes | NULL | FK | Related listing | | conversation_id | BIGINT | Yes | NULL | FK | Related conversation | | reason | VARCHAR(150) | No | — | — | Report reason | | description | TEXT | Yes | NULL | — | Additional details | | status | ENUM('pending','under_review','resolved','dismissed') | No | pending | INDEX | Current report status | | resolved_by | BIGINT | Yes | NULL | FK | Super Admin handling the report | | resolved_at | DATETIME | Yes | NULL | — | Resolution timestamp | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation | --- ## Business Rules * Students may report listings or users. * Reports cannot be edited after submission. * Reports remain in the system for auditing. --- # 3. Table: advertisements ## Purpose Stores advertisements displayed throughout the platform. Supports both banner (internal promotion) advertisements and rewarded advertisements, targeted to specific placements. The design is data-driven so new promotions — and future external advertisers — can be added without code changes. --- ## 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 | | title | VARCHAR(150) | No | — | — | Advertisement title | | description | TEXT | Yes | NULL | — | Short supporting copy | | advertisement_type | ENUM('banner','rewarded') | No | banner | INDEX | Advertisement type (banner = internal promo) | | placement | ENUM('landing','marketplace','chat','sidebar') | No | landing | INDEX | Where the advertisement is displayed (`chat` is shown as "Chat Unlock" in the UI) | | image_path | VARCHAR(255) | Yes | NULL | — | Advertisement image (optional; icon fallback) | | redirect_url | VARCHAR(500) | Yes | NULL | — | Destination URL | | is_active | BOOLEAN | No | TRUE | INDEX | Advertisement visibility | | priority | INT | No | 1 | INDEX | Display priority (higher shown first) | | start_at | DATETIME | Yes | NULL | — | Optional schedule start | | end_at | DATETIME | Yes | NULL | — | Optional schedule end | | impression_count | INT | No | 0 | — | Times the advertisement was displayed | | click_count | INT | No | 0 | — | Times the advertisement was clicked | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation | | updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update | > **Revision note (extended design):** `placement`, `description`, `priority`, > `start_at`, `end_at`, `impression_count`, `click_count` and `updated_at` were > added to support placement-targeted advertising and analytics. `priority` > supersedes the original `display_order`. `image_path` is now optional so > internal promotions can render with a branded icon fallback. Values in > `advertisement_type` are unchanged (`banner` = internal promotion). --- ## Advertisement model (display terminology) The two axes are presented to administrators as: * **Advertisement Type** — *Banner* or *Rewarded* (how the advertisement behaves). * **Placement** — *Landing*, *Marketplace*, *Sidebar* or **Chat Unlock** (where it appears). **Chat Unlock** is the fixed placement used by every Rewarded advertisement (stored as the `chat` enum value). It is assigned automatically and is never chosen by administrators. Everywhere a placement name is shown — badges, cards, filters, analytics, charts, tables, tooltips, empty states and exported reports — the `chat` placement is presented as "Chat Unlock". ## Business Rules * Banner advertisements (internal promotions) are displayed in their target placement (`landing`, `marketplace` or `sidebar`). * Rewarded advertisements (`advertisement_type = rewarded`) always use `placement = chat` — the "Chat Unlock" watch-to-unlock flow. There is no separate rewarded placement: the *type* describes behaviour, the *placement* describes where it appears. The placement is assigned automatically and is not exposed to administrators. * Rewarded advertisements unlock five additional chat messages when watched. * Only active advertisements within their optional schedule window are displayed. * Advertisements are ordered by `priority` (highest first). * Impression and click counts are recorded for reporting. --- # 4. Table: premium_subscriptions ## Purpose Stores premium membership information for students. --- ## 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 | FK | Premium subscriber | | subscription_type | ENUM('monthly','yearly') | No | monthly | INDEX | Subscription plan | | status | ENUM('active','expired','cancelled') | No | active | INDEX | Subscription status | | starts_at | DATETIME | No | — | — | Subscription start | | expires_at | DATETIME | No | — | INDEX | Expiration date | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation | --- ## Business Rules * Only one active subscription is allowed per student. * Premium members receive unlimited messaging. * Expired subscriptions automatically lose premium benefits. --- # 4b. Table: payments ## Purpose Records one row per **payment attempt** for a premium purchase through a payment gateway (Razorpay). Kept deliberately separate from `premium_subscriptions`: a payment records *how* premium was purchased (gateway order/payment ids, amount, status), while the subscription records the *entitlement*. A verified capture links the two via `subscription_id`. This preserves the design goal that a payment gateway replaces the manual approval step **without changing the subscription model**, and keeps the provider isolated behind the `app/payments` module. The manual out-of-band request workflow (`premium_requests`) remains available as a fallback; it does not create `payments` rows. --- ## 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 | FK/IDX | Paying user | | subscription_id | BIGINT | Yes | NULL | FK | Subscription activated on capture | | plan_name | VARCHAR(50) | No | — | — | Purchased plan name | | provider | VARCHAR(20) | No | razorpay | — | Payment gateway identifier | | provider_order_id | VARCHAR(120) | No | — | UNIQUE | Gateway order id (idempotency anchor) | | provider_payment_id | VARCHAR(120) | Yes | NULL | INDEX | Gateway payment id | | provider_signature | VARCHAR(255) | Yes | NULL | — | Verified callback signature | | amount | DECIMAL(10,2) | No | — | — | Charged amount (major units) | | currency | VARCHAR(8) | No | INR | — | ISO 4217 currency | | status | ENUM('created','authorized','captured','failed','refunded') | No | created | INDEX | Payment lifecycle status | | notes | TEXT | Yes | NULL | — | JSON metadata (receipt, failure reason, …) | | paid_at | DATETIME | Yes | NULL | — | Capture (settlement) time | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Record creation | | updated_at | DATETIME | No | CURRENT_TIMESTAMP | — | Last update | --- ## Business Rules * The order amount is computed server-side from the admin-configured plan price — never trusted from the browser. * Premium is activated **only** after a gateway signature (callback or webhook) is verified. * Webhooks are the authoritative status source; processing is idempotent, so a repeated event never creates a second subscription (guarded by payment status). * A `refunded` payment revokes premium (the subscription is expired; history kept). --- # 5. Table: admin_logs ## Purpose Stores an audit trail of administrative actions. --- ## 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 | | admin_id | BIGINT | No | FK | FK | Super Admin performing the action | | action | VARCHAR(150) | No | — | INDEX | Administrative action | | target_type | VARCHAR(100) | No | — | INDEX | Entity affected | | target_id | BIGINT | No | — | INDEX | Affected entity ID | | details | TEXT | Yes | NULL | — | Additional information | | created_at | DATETIME | No | CURRENT_TIMESTAMP | INDEX | Action timestamp | --- ## Business Rules * Every administrative action should generate a log. * Logs cannot be edited or deleted. * Logs support auditing and troubleshooting. --- # 6. Module Integration The Administration module integrates with: ### Marketplace * Listing moderation * Advertisement management ### Chat * Review reported conversations ### Negotiation * Investigate reported negotiations ### Meet-up * Review disputes if necessary ### Notifications * Send administrative announcements ### System Settings * Update platform configuration --- # 7. Security Rules The Administration module enforces: * Super Admin authorization for all actions. * Audit logging for administrative operations. * Ownership validation where appropriate. * SQLAlchemy ORM for database access. * Input validation for all administrative forms. --- # 8. Summary The Administration module provides platform moderation, reporting, advertising management, premium subscription management, and complete audit logging. The design supports: * Platform moderation * Revenue generation * Administrative auditing * Premium memberships * Report management * Future scalability The Administration module follows the approved Student Bazaar database standards and provides the operational backbone for managing the platform.