MongoDB schema & indexes for messages and conversations

[mehalyna]

Task 2 — MongoDB schema & indexes for messages and conversations

Title: db: design and implement MongoDB collections for conversations & messages

Deliverables

• Collections and example schema:

  • conversations document:

    {
      "_id": ObjectId,
      "conversation_id": "uuid",     // also used by app
      "participants": [user_id,...],
      "project_id": uuid|null,      // optional link
      "startup_id": uuid|null,
      "created_at": ISODate,
      "last_message_at": ISODate,
      "meta": {...}
    }

  • messages document:

    {
      "_id": ObjectId,
      "conversation_id": "uuid",
      "sender_id": user_id,
      "body": "string",
      "attachments": [{ "upload_id": "...", "url":"...", "type":"image" }],
      "status": "sent|delivered|read",
      "created_at": ISODate,
      "meta": {...}
    }

• Create indexes:

  • messages index on conversation_id, created_at (query by conversation).
  • TTL index for retention if required (created_at with TTL).
  • conversations index on participants and last_message_at.

• Migration script / instructions for creating collections and indexes; include a one-time migration if moving from SQL.

Acceptance

• Index creation scripts committed and can be run locally (mongo script or management command).
• Unit tests that connect to test Mongo instance and create a conversation + message.

Dependencies

• MongoDB available for dev/test environments.

Notes

• Use pymongo or mongoengine for access. For Django integration prefer a thin service layer around pymongo so core app logic is DB-agnostic.