Skip to main content

Database Schema

This page documents the core relational data model used by AuctioHub.

ER Summary​

  • users owns products.
  • products maps to auction lifecycle state.
  • bids references products and users.
  • chat_messages references auction context and sender.
  • auction_participant_bans references auction and banned user.
  • auction_history records status transitions and actions.
  • notification_events references target users and event payload.

Table Reference​

users​

Purpose: identity, profile, and role management.

Key columns:

  • id (PK)
  • email (unique)
  • display_name
  • role (user or admin)
  • created_at, updated_at

products​

Purpose: auction listing record and active state.

Key columns:

  • id (PK)
  • seller_id (FK -> users.id)
  • title, description
  • starting_price, current_price
  • status (draft/active/completed/canceled)
  • auction_start_at, auction_end_at
  • winner_user_id (nullable FK -> users.id)

bids​

Purpose: immutable bid history.

Key columns:

  • id (PK)
  • product_id (FK -> products.id)
  • user_id (FK -> users.id)
  • amount
  • created_at

chat_messages​

Purpose: auction session chat transcript.

Key columns:

  • id (PK)
  • product_id (FK -> products.id)
  • sender_user_id (FK -> users.id)
  • message
  • created_at

auction_participant_bans​

Purpose: moderation restrictions per auction.

Key columns:

  • id (PK)
  • product_id (FK -> products.id)
  • user_id (FK -> users.id)
  • reason
  • created_by_admin_id (FK -> users.id)
  • created_at

auction_history​

Purpose: audit timeline of auction actions.

Key columns:

  • id (PK)
  • product_id (FK -> products.id)
  • event_type
  • event_payload (JSON)
  • created_at

notification_events​

Purpose: durable event feed for user notifications.

Key columns:

  • id (PK)
  • user_id (FK -> users.id)
  • event_type
  • payload (JSON)
  • is_read
  • created_at

Purpose: admin-managed homepage carousel assets.

Key columns:

  • id (PK)
  • image_url
  • target_url
  • display_order
  • is_active

new_arrivals​

Purpose: admin-curated featured items.

Key columns:

  • id (PK)
  • product_id (FK -> products.id)
  • display_order
  • is_active

admin_settings​

Purpose: centralized operational configuration values.

Key columns:

  • key (PK)
  • value (JSON/text)
  • updated_at

Key Relationships​

  • One user to many products.
  • One product to many bids.
  • One product to many chat messages.
  • One product to many auction history records.
  • One user to many notification events.

Index Strategy​

  • users(email) unique index.
  • products(status, auction_end_at) for listing and finalization scans.
  • bids(product_id, amount DESC, created_at DESC) for winner lookup.
  • chat_messages(product_id, created_at) for polling efficiency.
  • notification_events(user_id, created_at DESC) for user feed retrieval.

Constraints And Checks​

  • Bid amount must be positive.
  • Product current_price must be greater than or equal to starting_price.
  • Role values constrained to supported enum.
  • Foreign keys enforce referential integrity.

Trigger And Derived Behavior​

  • On valid bid insert, update product current price and winner snapshot.
  • On participant ban, winner may be recomputed if top bidder is affected.
  • On payment completion, auction status transition is written to history.