Database Schemas

Complete reference for all MongoDB collections used in Hexabot + Prophunt.

Conversation Schema

Collection: conversations

Stores user conversation sessions with agents.

Fields

Field	Type	Description
_id	ObjectId	Unique conversation identifier
sender	ObjectId (ref: Subscriber)	User who initiated conversation
active	Boolean	Whether conversation is ongoing
context	Object (Context)	Conversation context and state
current	ObjectId (ref: Block)	Current conversation block (Hexabot flow)
next	ArrayObjectId (ref: Block)	Next blocks in flow
createdAt	Date	When conversation started
updatedAt	Date	Last update timestamp

Context Structure

The context field contains both Hexabot standard fields and our custom agent data:

{
  // Hexabot standard fields
  channel?: string;              // Channel name (not used in multi-agent)
  text?: string;                 // Last user text
  payload?: string | object;     // Last user payload
  nlp?: object;                  // NLP parsing results
  user_location: {               // User location data
    lat: number;
    lon: number;
    address?: object;
  };
  user?: Subscriber;             // User object (populated)
  skip: {                        // Skip tracking for flows
    [key: string]: number;
  };
  attempt: number;               // Retry attempts

  // Custom multi-agent fields (stored in vars)
  vars: {
    // Agent identification
    channelId: string;           // 'prophunt_bgv', 'prophunt_loan', etc.
    agentId: string;             // 'bgv', 'loan', 'listing'
    taskId: string;              // Unique task ID: 'bgv_user123_1234567890'
    
    // External linking
    verificationId?: string;     // External system verification ID
    
    // State management
    state: string;               // Current agent state: 'AWAITING_PAYMENT', etc.
    active_agent: string;        // Currently active agent ID
    
    // Workflow-specific data (example for BGV)
    panCardUploaded?: boolean;
    panNumber?: string;
    paymentAmount?: number;
    paymentStatus?: string;
    documentUrls?: string[];
    
    // Tracking
    lastMessageAt?: Date;
    lastMessageHandler?: string; // 'bgv-agent', 'loan-agent', etc.
    
    // Custom agent data (flexible)
    [key: string]: any;
  };
}

Example Document

{
  "_id": ObjectId("6751a2b3c4d5e6f7a8b9c0d1"),
  "sender": ObjectId("user123abc"),
  "active": true,
  "context": {
    "user_location": {
      "lat": 19.0760,
      "lon": 72.8777
    },
    "skip": {},
    "attempt": 0,
    "vars": {
      "channelId": "prophunt_bgv",
      "agentId": "bgv",
      "taskId": "bgv_user123_1733395200000",
      "verificationId": "VER_PROPHUNT_12345",
      "state": "AWAITING_PAYMENT",
      "active_agent": "bgv",
      "panCardUploaded": true,
      "panNumber": "ABCDE1234F",
      "paymentAmount": 499,
      "paymentStatus": "pending",
      "lastMessageAt": "2025-12-05T10:30:00.000Z",
      "lastMessageHandler": "bgv-agent"
    }
  },
  "current": null,
  "next": [],
  "createdAt": "2025-12-05T10:00:00.000Z",
  "updatedAt": "2025-12-05T10:30:00.000Z"
}

Relationships

Conversation
   ├─ sender → Subscriber (1:1)
   ├─ current → Block (1:1, nullable)
   └─ next → Block[] (1:N)

Indexes

db.conversations.createIndex({ sender: 1, active: 1 });
db.conversations.createIndex({ "context.vars.taskId": 1 }, { unique: true });
db.conversations.createIndex({ "context.vars.verificationId": 1 });
db.conversations.createIndex({ "context.vars.channelId": 1 });
db.conversations.createIndex({ createdAt: -1 });

---

Message Schema

Collection: messages

Stores all conversation messages (user & bot).

Fields

Field	Type	Description
_id	ObjectId	Unique message identifier
mid	String	Message ID (optional, from channels)
sender	ObjectId (ref: Subscriber)	Who sent the message
recipient	ObjectId (ref: Subscriber)	Who receives the message
sentBy	ObjectId (ref: User)	Agent/admin who sent (if bot message)
message	Object	Message content (StdOutgoingMessage | StdIncomingMessage)
read	Boolean	Whether message was read
delivery	Boolean	Whether message was delivered
handover	Boolean	Whether transferred to human agent
createdAt	Date	When message was sent
updatedAt	Date	Last update

Message Object Structure

// Text message
{
  text: string;
}

// Message with buttons
{
  text: string;
  buttons: [
    {
      type: 'web_url' | 'postback',
      title: string;
      url?: string;      // For web_url
      payload?: string;  // For postback
    }
  ];
}

// Message with quick replies
{
  text: string;
  quickReplies: [
    {
      title: string;
      payload: string;
    }
  ];
}

// Custom payload
{
  type: 'OPEN_MODAL' | 'PAYMENT_LINK' | any;
  modal_id?: string;
  data?: any;
}

Example Document

{
  "_id": ObjectId("6751a2b3c4d5e6f7a8b9c0d2"),
  "mid": null,
  "sender": ObjectId("user123abc"),
  "recipient": null,
  "sentBy": null,
  "message": {
    "text": "Please upload your PAN card",
    "buttons": [
      {
        "type": "postback",
        "title": "Upload PAN",
        "payload": "UPLOAD_PAN"
      }
    ]
  },
  "read": true,
  "delivery": true,
  "handover": false,
  "createdAt": "2025-12-05T10:01:00.000Z",
  "updatedAt": "2025-12-05T10:01:30.000Z"
}

Relationships

Message
   ├─ sender → Subscriber (1:1)
   ├─ recipient → Subscriber (1:1, nullable)
   └─ sentBy → User (1:1, nullable)

Indexes

db.messages.createIndex({ sender: 1, createdAt: -1 });
db.messages.createIndex({ recipient: 1, read: 1 });
db.messages.createIndex({ createdAt: -1 });

---

Subscriber Schema

Collection: subscribers

Stores user information.

Fields

Field	Type	Description
_id	ObjectId	Unique user identifier
foreign_id	String	External user ID (from your system)
first_name	String	User's first name
last_name	String	User's last name
language	String	Preferred language code
gender	String	User's gender
country	String	User's country
labels	ArrayObjectId	User labels/tags
assignedTo	ObjectId (ref: User)	Assigned agent/admin
lastvisit	Date	Last activity timestamp
retainedFrom	Date	When user was acquired
createdAt	Date	Account creation date
updatedAt	Date	Last update

Example Document

{
  "_id": ObjectId("user123abc"),
  "foreign_id": "prophunt_user_456",
  "first_name": "John",
  "last_name": "Doe",
  "language": "en",
  "gender": "male",
  "country": "IN",
  "labels": [],
  "assignedTo": null,
  "lastvisit": "2025-12-05T10:30:00.000Z",
  "retainedFrom": "2025-12-01T00:00:00.000Z",
  "createdAt": "2025-12-01T00:00:00.000Z",
  "updatedAt": "2025-12-05T10:30:00.000Z"
}

---

Agent Session (Virtual)

While there's no dedicated agent_sessions collection, agent session state is managed through:

1. Conversation.context.vars - Stores active agent and state
2. In-memory ChannelManager - Maps channels to conversations
3. Socket.io rooms - Manages real-time connections

Virtual Agent Session Structure

Conceptually, an agent session consists of:

{
  sessionId: string;              // = conversation._id
  userId: string;                 // = conversation.sender
  agentId: string;                // = context.vars.agentId
  channelId: string;              // = context.vars.channelId
  taskId: string;                 // = context.vars.taskId
  state: string;                  // = context.vars.state
  isActive: boolean;              // = conversation.active
  socketId?: string;              // In-memory only (SocketAdapter)
  connectedAt?: Date;             // In-memory only
  lastActivity: Date;             // = context.vars.lastMessageAt
}

---

Query Examples

Get active BGV conversations

db.conversations.find({
  active: true,
  "context.vars.agentId": "bgv"
});

Find conversation by taskId

db.conversations.findOne({
  "context.vars.taskId": "bgv_user123_1733395200000"
});

Get conversation history with messages

const conversation = db.conversations.findOne({
  "context.vars.taskId": "bgv_user123_1733395200000"
});

const messages = db.messages.find({
  sender: conversation.sender
}).sort({ createdAt: 1 });

Find unread messages for user

db.messages.find({
  recipient: ObjectId("user123abc"),
  read: false
}).sort({ createdAt: -1 });

Get all conversations for a user

db.conversations.find({
  sender: ObjectId("user123abc")
}).sort({ createdAt: -1 });

Link external verification ID

db.conversations.updateOne(
  { "context.vars.taskId": "bgv_user123_1733395200000" },
  { $set: { "context.vars.verificationId":  "VER_PROPHUNT_12345" } }
);

---

Data Lifecycle

Creating a Conversation

// ConversationManager.getOrCreateConversation()
const conversation = await conversationRepo.create({
  sender: userId,
  active: true,
  context: {
    vars: {
      channelId: 'prophunt_bgv',
      agentId: 'bgv',
      taskId: 'bgv_user123_1733395200000',
      state: 'INIT',
      active_agent: 'bgv'
    },
    user_location: { lat: 0, lon: 0 },
    skip: {},
    attempt: 0
  }
});

Persisting Messages

// ConversationManager.sendMessage()
const message = await messageRepo.create({
  sender: userId,
  message: {
    text: 'Hello, how can I help?'
  },
  read: false,
  delivery: false,
  handover: false
});

Updating Context

// ConversationManager.updateContext()
await conversationRepo.updateOne(
  { _id: conversationId },
  {
    $set: {
      'context.vars.state': 'PAYMENT_COMPLETED',
      'context.vars.paymentStatus': 'paid',
      'context.vars.lastMessageAt': new Date()
    }
  }
);

Closing Conversation

// ConversationManager.closeConversation()
await conversationRepo.updateOne(
  { _id: conversationId },
  {
    $set: {
      active: false,
      'context.vars.state': 'COMPLETED',
      'context.vars.completedAt': new Date()
    }
  }
);

---

Migration Commands

If you need to migrate existing data to the new multi-agent structure:

// Add taskId to existing conversations
db.conversations.find({ "context.vars.taskId": { $exists: false } }).forEach(doc => {
  const taskId = `${doc.context.vars.agentId || 'unknown'}_${doc.sender}_${doc.createdAt.getTime()}`;
  db.conversations.updateOne(
    { _id: doc._id },
    { $set: { "context.vars.taskId": taskId } }
  );
});

// Add channelId to existing conversations
db.conversations.updateMany(
  { "context.vars.channelId": { $exists: false } },
  { $set: { "context.vars.channelId": "prophunt_bgv" } }
);

---

Backup & Restore

Backup

# Backup conversations
mongodump --db hexabot --collection conversations --out /backup

# Backup messages
mongodump --db hexabot --collection messages --out /backup

# Backup subscribers
mongodump --db hexabot --collection subscribers --out /backup

Restore

mongorestore --db hexabot --collection conversations /backup/hexabot/conversations.bson
mongorestore --db hexabot --collection messages /backup/hexabot/messages.bson
mongorestore --db hexabot --collection subscribers /backup/hexabot/subscribers.bson

---

Next Steps

• Optimize Database Performance
• Implement Data Archival
• Monitor Database Health