# Greentik Chats: MongoDB database structure

Database name: `greentikchats`. The Mongoose models live in `server/src/models/`, and indexes are created automatically when the server starts.

## Collections at a glance

| Collection | Purpose | Main relations |
|---|---|---|
| `users` | One document per account (mobile number + verified email) | referenced by every other collection |
| `otps` | Login codes sent by email (stored hashed, auto-expire) | linked to a user by `email` / `phone` |
| `sessions` | Logged-in browsers/devices, so tokens can be revoked | `user → users` |
| `conversations` | 1-to-1 chats and groups, including each member's settings | `participants.user → users`, `lastMessage._id → messages` |
| `messages` | Every message, with ticks, reactions, stars and deletes | `conversation → conversations`, `sender → users`, `replyTo → messages` |
| `reports` | Users reporting spam/abuse | `reporter/targetUser → users`, `conversation`, `message` |

```
users ──< sessions
  │
  ├──< conversations.participants[].user
  │         │
  │         └──< messages (conversation)
  │                 └── replyTo → messages
  └──< reports
otps (by email, TTL)
```

---

## 1. `users`

| Field | Type | Notes |
|---|---|---|
| `_id` | ObjectId | |
| `phone` | String, **unique** | E.164 format, e.g. `+919876543210`. Login identity |
| `countryCode` | String | `+91` |
| `mobile` | String | `9876543210` |
| `email` | String, **unique**, lowercase | OTP is sent here |
| `emailVerified` | Boolean | `true` after the first successful OTP |
| `name` | String (max 60) | |
| `about` | String (max 140) | default "Hey there! I am using Greentik Chats." |
| `avatarUrl` | String | |
| `lastSeenAt` | Date | updated when the last socket disconnects |
| `privacy.lastSeen` | `everyone` \| `contacts` \| `nobody` | |
| `privacy.profilePhoto` | same | |
| `privacy.about` | same | |
| `privacy.readReceipts` | Boolean | `false` = no blue ticks |
| `contacts` | [ObjectId → users] | reserved for contact sync |
| `blockedUsers` | [ObjectId → users] | |
| `status` | `active` \| `suspended` \| `deleted` | |
| `lastLoginAt`, `createdAt`, `updatedAt` | Date | |

Indexes: `phone` (unique), `email` (unique).

A mobile number is permanently paired with one email. Login is refused if either one is already linked to a different partner.

```json
{
  "_id": "66f1a2b3c4d5e6f708091a2b",
  "phone": "+919876543210",
  "countryCode": "+91",
  "mobile": "9876543210",
  "email": "rahul@example.com",
  "emailVerified": true,
  "name": "Rahul Kumar",
  "about": "Hey there! I am using Greentik Chats.",
  "avatarUrl": "",
  "lastSeenAt": "2026-09-23T11:40:00Z",
  "privacy": { "lastSeen": "everyone", "profilePhoto": "everyone", "about": "everyone", "readReceipts": true },
  "blockedUsers": [],
  "status": "active"
}
```

## 2. `otps`

| Field | Type | Notes |
|---|---|---|
| `email` | String | |
| `phone` | String | the number the code was requested for |
| `purpose` | `login` | |
| `codeHash` | String | HMAC-SHA256 of `email:code` with `OTP_SECRET`. The plain code is never stored |
| `attempts` | Number | wrong tries (max 5) |
| `lastSentAt` | Date | enforces the 30-second resend wait |
| `sendHistory` | [Date] | enforces a maximum of 5 emails per hour |
| `requestIp` | String | |
| `expiresAt` | Date | now + 5 minutes |

Indexes: `{ email: 1, purpose: 1 }` (unique), and `expiresAt` as a TTL index. Documents are deleted automatically 1 hour after they expire.

## 3. `sessions`

| Field | Type | Notes |
|---|---|---|
| `user` | ObjectId → users | indexed |
| `userAgent`, `ip` | String | shown in a future "linked devices" screen |
| `lastActiveAt` | Date | |
| `revokedAt` | Date | set on logout / "log out of all devices" |
| `expiresAt` | Date | TTL index, matches the JWT expiry (30 days) |

The JWT holds `{ sub: userId, sid: sessionId }`. Every request checks that the session is still valid.

## 4. `conversations`

| Field | Type | Notes |
|---|---|---|
| `type` | `direct` \| `group` | |
| `participants[]` | array | one entry per member, see below |
| `directKey` | String | direct chats only: `"<smallerUserId>_<largerUserId>"`, **unique**. Prevents two chats between the same pair |
| `group.name` | String (max 80) | |
| `group.description` | String (max 500) | |
| `group.avatarUrl` | String | |
| `group.createdBy` | ObjectId → users | |
| `group.onlyAdminsCanSend` | Boolean | |
| `group.onlyAdminsCanEdit` | Boolean | |
| `lastMessage` | `{ _id, sender, type, text, createdAt }` | copy of the latest message so the chat list needs one query |
| `lastMessageAt` | Date | sort key for the chat list |

**`participants[]`** holds the member's own settings for that chat:

| Field | Type | Notes |
|---|---|---|
| `user` | ObjectId → users | |
| `role` | `admin` \| `member` | |
| `joinedAt` | Date | |
| `unreadCount` | Number | increased on each new message, reset when the chat is opened |
| `lastReadAt` | Date | |
| `pinned`, `archived` | Boolean | |
| `mutedUntil` | Date | |
| `clearedAt` | Date | "Clear chat": older messages are hidden for this member only |

Indexes: `{ 'participants.user': 1, lastMessageAt: -1 }` for the chat list, and `directKey` (unique, partial).

```json
{
  "_id": "66f1b0000000000000000001",
  "type": "group",
  "participants": [
    { "user": "66f1a2b3...a2b", "role": "admin",  "unreadCount": 0, "pinned": true,  "archived": false },
    { "user": "66f1a2b3...a2c", "role": "member", "unreadCount": 3, "pinned": false, "archived": false }
  ],
  "group": { "name": "Solar Sales Team", "description": "Leads and proposals", "createdBy": "66f1a2b3...a2b",
             "onlyAdminsCanSend": false, "onlyAdminsCanEdit": false },
  "lastMessage": { "_id": "66f1c...", "sender": "66f1a2b3...a2c", "type": "text", "text": "Proposal sent ✅", "createdAt": "2026-09-23T11:45:00Z" },
  "lastMessageAt": "2026-09-23T11:45:00Z"
}
```

## 5. `messages`

| Field | Type | Notes |
|---|---|---|
| `conversation` | ObjectId → conversations | |
| `sender` | ObjectId → users | |
| `type` | `text` \| `image` \| `video` \| `audio` \| `document` \| `system` | `system` = "Rahul added Priya" etc. |
| `text` | String (max 4096) | message text, or the caption for media |
| `media` | `{ url, mimeType, fileName, size, width, height, duration }` | |
| `replyTo` | ObjectId → messages | quoted message |
| `forwarded` | Boolean | |
| `reactions[]` | `{ user, emoji }` | one reaction per user (👍 ❤️ 😂 😮 😢 🙏) |
| `deliveredTo[]` | `{ user, at }` | grey double tick when every recipient is listed |
| `readBy[]` | `{ user, at }` | blue double tick when every recipient is listed |
| `starredBy` | [ObjectId → users] | |
| `editedAt` | Date | editing allowed for 15 minutes |
| `deletedForEveryone` | Boolean | allowed for 1 hour; text and media are wiped |
| `deletedFor` | [ObjectId → users] | "delete for me" |
| `clientMsgId` | String | id created by the browser. Stops a retried send from being saved twice |
| `createdAt`, `updatedAt` | Date | |

Indexes: `{ conversation: 1, createdAt: -1 }` (chat history pages), `{ sender: 1, clientMsgId: 1 }` (unique, partial), and `{ starredBy: 1 }`.

**Tick logic:** `recipients = participants − sender`. The message is **read** if `readBy.length ≥ recipients`, **delivered** if `deliveredTo.length ≥ recipients`, and **sent** otherwise.

```json
{
  "_id": "66f1c0000000000000000009",
  "conversation": "66f1b0000000000000000001",
  "sender": "66f1a2b3...a2b",
  "type": "image",
  "text": "Rooftop install, Gomti Nagar",
  "media": { "url": "https://api.greentikchats.us/uploads/2026-09/3f9c...e1.jpg", "mimeType": "image/jpeg", "fileName": "site.jpg", "size": 482113 },
  "replyTo": null,
  "reactions": [{ "user": "66f1a2b3...a2c", "emoji": "👍" }],
  "deliveredTo": [{ "user": "66f1a2b3...a2c", "at": "2026-09-23T11:45:02Z" }],
  "readBy": [{ "user": "66f1a2b3...a2c", "at": "2026-09-23T11:46:10Z" }],
  "starredBy": [],
  "deletedForEveryone": false,
  "deletedFor": [],
  "clientMsgId": "m1x9k2a7qz"
}
```

## 6. `reports`

| Field | Type | Notes |
|---|---|---|
| `reporter` | ObjectId → users | |
| `targetUser`, `conversation`, `message` | ObjectId | whichever applies |
| `reason` | `spam` \| `abuse` \| `fraud` \| `inappropriate` \| `other` | |
| `details` | String (max 1000) | |
| `status` | `open` \| `reviewing` \| `resolved` \| `dismissed` | for an admin panel later |

Index: `{ status: 1, createdAt: -1 }`.

---

## Growing later

- **Status/Stories (Phase 2):** add a `statuses` collection `{ user, type, text, media, viewers[], expiresAt }` with a TTL index on `expiresAt` (24 hours).
- **Large groups or high volume:** shard `messages` on `{ conversation: 1, createdAt: 1 }`.
- **Media:** move `uploads/` to S3 or Cloudflare R2. Only `media.url` changes.
- **Several API servers:** add the Socket.IO Redis adapter. No database change is needed.