Skip to content

database.md

Latest commit

 

History

History
133 lines (103 loc) · 5.87 KB

database.md

File metadata and controls

133 lines (103 loc) · 5.87 KB
Raw

CartBuddy Database Design

This document details the MongoDB schema, data models, entity-relationship designs, and indexes implemented in CartBuddy.

Database Architecture Overview

CartBuddy uses a document-oriented database architecture powered by MongoDB and accessed using the Mongoose ODM framework. Documents are dynamically normalized to maintain performance on high-frequency transactions (such as chats, live locations, and status queries) while embedding static/low-cardinality components (like user address sub-documents).

Entity-Relationship Diagram (Logical)

erDiagram
    USER ||--o{ GROUP : "hosts"
    USER ||--o{ GROUP_MEMBER : "member of"
    USER ||--o{ REFRESH_TOKEN : "owns"
    USER ||--o{ NOTIFICATION : "receives"
    USER ||--o{ BUDDY_SCORE_LOG : "receives score updates"
    USER ||--o{ RATING : "submits/receives ratings"
    
    GROUP ||--|{ GROUP_MEMBER : "has members"
    GROUP ||--o{ MESSAGE : "contains chat history"
    GROUP ||--o{ RATING : "has post-trip rating"
    
    GROUP_MEMBER }|--|| USER : "references"
    GROUP_MEMBER }|--|| GROUP : "references"
Loading

Schema Reference & Collections

1. users Collection

Stores credential data, verification state, notification configurations, and location information.

Field Type Attributes / Validation Description
_id ObjectId Primary Key Unique user identifier
name String Required, Trimmed Display name of the user
email String Required, Unique, Lowercase User email address
passwordHash String Required, Hidden (select: false) Hashed bcrypt password
phone String Trimmed Optional phone number
profileImageUrl String Default: "" Hosted Avatar image URL
isVerified Boolean Default: false Email verification flag
verificationTier String Enum: UNVERIFIED, EMAIL_VERIFIED, ID_VERIFIED Account verification status
role String Enum: USER, ADMIN Account access role
currentLocation Point Geospatial { type: "Point", coordinates: [lng, lat] } Last updated coordinates
nearbyRadiusMeters Number Default: 1000 Search radius for nearby groups
savedAddresses Array Max length: 10, Embedded schema List of saved addresses
buddyScore Number Default: 100, Range: 0 to 100 Reputation/trust score
lastSeenAt Date Default: Date.now Last timestamp user was online
timestamps Date CreatedAt, UpdatedAt Standard audit dates

Indexes:

  • { currentLocation: "2dsphere" } - Crucial for radial geolocation searches.
  • { buddyScore: -1 } - Optimizes search ranking for highly rated users.

2. groups Collection

Represents a shared shopping trip cart created by a Host.

Field Type Attributes / Validation Description
_id ObjectId Primary Key Unique group identifier
platform String Required, Enum (e.g. Zepto, Blinkit) Target shopping site
createdBy ObjectId Ref: User, Required Creator (Host) of the group
title String Required, Min: 3, Max: 50 Searchable title
description String Max: 250 Extended cart context
meetingPoint Point Geospatial { type: "Point", coordinates: [lng, lat] } Delivery handover location
maxMembers Number Required, Range: 2 to 100 Max membership capacity
currentMemberCount Number Default: 1 Denormalized count of joined members
orderDeadline Date Required Expiration deadline for cart edits
status String Enum: OPEN, FULL, LOCKED, COMPLETED, CANCELLED Current group status

Indexes:

  • { meetingPoint: "2dsphere" } - Supports spatial $near search queries.
  • { status: 1, orderDeadline: 1 } - Used by group-expiration cron jobs.

3. groupmembers Collection

Join table resolving many-to-many user memberships in groups.

Field Type Attributes / Validation Description
_id ObjectId Primary Key Unique record identifier
groupId ObjectId Ref: Group, Required Targeted group
userId ObjectId Ref: User, Required Targeted user
role String Enum: HOST, MEMBER Role inside group
status String Enum: JOINED, LEFT, KICKED Membership state
joinedAt Date Default: Date.now Date of joining group
noShow Boolean Default: false Flag set if member failed to show up

Indexes:

  • { groupId: 1, userId: 1 } (Unique) - Prevents redundant join states.

4. messages Collection

Stores chat log transcripts exchanged inside a group room.

Field Type Attributes / Validation Description
_id ObjectId Primary Key Unique message identifier
groupId ObjectId Ref: Group, Required Group chat container
senderId ObjectId Ref: User, Required Author of message
content String Required, Trimmed Text payload
type String Enum: TEXT, SYSTEM Chat content type
readBy Array Ref: User Users who have read the message
timestamps Date CreatedAt, UpdatedAt Timestamp of exchange

Indexes:

  • { groupId: 1, createdAt: 1 } - Essential for loading sequential message history.

5. otps Collection

Stores short-lived verification codes.

Field Type Attributes / Validation Description
_id ObjectId Primary Key Unique document identifier
email String Required, Lowercase Destination email
code String Required, 6 digits Security token string
purpose String Enum: VERIFY_EMAIL, PASSWORD_RESET Target operation type
expiresAt Date Required Expiry timestamp

Indexes:

  • { expiresAt: 1 } (TTL: 0) - Triggers MongoDB automatic garbage cleanup on expiry.