WhatsApp API dengan Database

Database = Memory bot kamu!

Tanpa database, bot tidak ingat apapun. Dengan database, kamu bisa simpan conversation, track customers, dan analyze data.

Kenapa Perlu Database?

📊 USE CASES:

CONVERSATION HISTORY:
- Simpan semua pesan
- Context untuk AI/bot
- Audit trail

CUSTOMER DATA:
- Profile & preferences
- Order history
- Contact info

ANALYTICS:
- Message volume
- Response time
- Popular queries

BOT STATE:
- User session
- Flow progress
- Pending actions

Pilihan Database

🗄️ DATABASE OPTIONS:

MONGODB (Recommended):
✅ Flexible schema
✅ JSON-native
✅ Easy scaling
✅ Good for chat data

POSTGRESQL:
✅ ACID compliant
✅ Complex queries
✅ Relational data
✅ Good for transactions

MYSQL:
✅ Familiar
✅ Widespread hosting
✅ Good documentation

REDIS:
✅ Super fast
✅ Session/cache
✅ Pub/sub
⚠️ In-memory (data loss risk)

Schema Design

MongoDB Schema:

javascript

// Contacts collection
const contactSchema = {
    wa_id: String,           // WhatsApp ID
    phone: String,           // Phone number
    name: String,            // Display name
    tags: [String],          // Custom tags
    metadata: Object,        // Custom data
    firstContact: Date,
    lastContact: Date,
    messageCount: Number,
    isBlocked: Boolean,
    preferences: {
        language: String,
        notifications: Boolean
    }
};

// Messages collection
const messageSchema = {
    messageId: String,       // WA message ID
    conversationId: String,  // Group messages together
    wa_id: String,           // Customer WA ID
    direction: String,       // 'incoming' atau 'outgoing'
    type: String,            // 'text', 'image', 'document', etc
    content: {
        text: String,
        mediaUrl: String,
        caption: String
    },
    status: String,          // 'sent', 'delivered', 'read'
    timestamp: Date,
    metadata: Object
};

// Conversations collection
const conversationSchema = {
    conversationId: String,
    wa_id: String,
    status: String,          // 'active', 'resolved', 'pending'
    assignedTo: String,      // Agent ID
    tags: [String],
    startedAt: Date,
    lastMessageAt: Date,
    messageCount: Number,
    summary: String          // AI-generated summary
};

PostgreSQL Schema:

sql

-- Contacts table
CREATE TABLE contacts (
    id SERIAL PRIMARY KEY,
    wa_id VARCHAR(20) UNIQUE NOT NULL,
    phone VARCHAR(20),
    name VARCHAR(100),
    tags TEXT[],
    metadata JSONB,
    first_contact TIMESTAMP DEFAULT NOW(),
    last_contact TIMESTAMP,
    message_count INT DEFAULT 0,
    is_blocked BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Messages table
CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    message_id VARCHAR(100) UNIQUE,
    conversation_id VARCHAR(50),
    wa_id VARCHAR(20) REFERENCES contacts(wa_id),
    direction VARCHAR(10), -- 'incoming', 'outgoing'
    type VARCHAR(20),
    content JSONB,
    status VARCHAR(20),
    timestamp TIMESTAMP,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Index untuk query cepat
CREATE INDEX idx_messages_wa_id ON messages(wa_id);
CREATE INDEX idx_messages_timestamp ON messages(timestamp);
CREATE INDEX idx_messages_conversation ON messages(conversation_id);

Implementasi MongoDB

Setup Connection:

javascript

const { MongoClient } = require('mongodb');

class Database {
    constructor() {
        this.client = null;
        this.db = null;
    }
    
    async connect() {
        this.client = await MongoClient.connect(process.env.MONGO_URI);
        this.db = this.client.db('whatsapp_bot');
        
        // Create indexes
        await this.db.collection('messages').createIndex({ wa_id: 1 });
        await this.db.collection('messages').createIndex({ timestamp: -1 });
        await this.db.collection('contacts').createIndex({ wa_id: 1 }, { unique: true });
        
        console.log('Database connected');
        return this.db;
    }
    
    getCollection(name) {
        return this.db.collection(name);
    }
}

const db = new Database();
module.exports = db;

Save Incoming Message:

javascript

async function saveIncomingMessage(webhookData) {
    const message = webhookData.messages[0];
    const contact = webhookData.contacts[0];
    
    // Save/update contact
    await db.getCollection('contacts').updateOne(
        { wa_id: contact.wa_id },
        {
            $set: {
                phone: contact.wa_id,
                name: contact.profile?.name,
                lastContact: new Date()
            },
            $inc: { messageCount: 1 },
            $setOnInsert: { firstContact: new Date() }
        },
        { upsert: true }
    );
    
    // Save message
    const messageDoc = {
        messageId: message.id,
        conversationId: generateConversationId(contact.wa_id),
        wa_id: contact.wa_id,
        direction: 'incoming',
        type: message.type,
        content: extractContent(message),
        timestamp: new Date(parseInt(message.timestamp) * 1000),
        metadata: {
            raw: message
        }
    };
    
    await db.getCollection('messages').insertOne(messageDoc);
    
    return messageDoc;
}

function extractContent(message) {
    switch (message.type) {
        case 'text':
            return { text: message.text.body };
        case 'image':
            return { 
                mediaId: message.image.id,
                caption: message.image.caption 
            };
        case 'document':
            return {
                mediaId: message.document.id,
                filename: message.document.filename
            };
        default:
            return { raw: message[message.type] };
    }
}

Save Outgoing Message:

javascript

async function saveOutgoingMessage(to, message, response) {
    const messageDoc = {
        messageId: response.messages[0].id,
        conversationId: generateConversationId(to),
        wa_id: to,
        direction: 'outgoing',
        type: message.type || 'text',
        content: message,
        status: 'sent',
        timestamp: new Date()
    };
    
    await db.getCollection('messages').insertOne(messageDoc);
    
    return messageDoc;
}

// Update status dari webhook
async function updateMessageStatus(messageId, status) {
    await db.getCollection('messages').updateOne(
        { messageId },
        { 
            $set: { 
                status,
                [`statusTimestamps.${status}`]: new Date()
            }
        }
    );
}

Query Conversation History:

javascript

async function getConversationHistory(wa_id, limit = 50) {
    const messages = await db.getCollection('messages')
        .find({ wa_id })
        .sort({ timestamp: -1 })
        .limit(limit)
        .toArray();
    
    return messages.reverse(); // Oldest first
}

// Get context untuk AI
async function getContextForAI(wa_id, messageLimit = 10) {
    const messages = await getConversationHistory(wa_id, messageLimit);
    
    // Format untuk AI prompt
    return messages.map(m => ({
        role: m.direction === 'incoming' ? 'user' : 'assistant',
        content: m.content.text || '[media]'
    }));
}

// Search messages
async function searchMessages(query, wa_id = null) {
    const filter = {
        'content.text': { $regex: query, $options: 'i' }
    };
    
    if (wa_id) {
        filter.wa_id = wa_id;
    }
    
    return await db.getCollection('messages')
        .find(filter)
        .sort({ timestamp: -1 })
        .limit(100)
        .toArray();
}

Analytics Queries

Message Statistics:

javascript

async function getMessageStats(startDate, endDate) {
    const pipeline = [
        {
            $match: {
                timestamp: { $gte: startDate, $lte: endDate }
            }
        },
        {
            $group: {
                _id: {
                    date: { $dateToString: { format: '%Y-%m-%d', date: '$timestamp' } },
                    direction: '$direction'
                },
                count: { $sum: 1 }
            }
        },
        {
            $sort: { '_id.date': 1 }
        }
    ];
    
    const results = await db.getCollection('messages')
        .aggregate(pipeline)
        .toArray();
    
    return results;
}

// Response time analytics
async function getResponseTimeStats() {
    const pipeline = [
        {
            $match: { direction: 'outgoing' }
        },
        {
            $lookup: {
                from: 'messages',
                let: { convId: '$conversationId', ts: '$timestamp' },
                pipeline: [
                    {
                        $match: {
                            $expr: {
                                $and: [
                                    { $eq: ['$conversationId', '$$convId'] },
                                    { $eq: ['$direction', 'incoming'] },
                                    { $lt: ['$timestamp', '$$ts'] }
                                ]
                            }
                        }
                    },
                    { $sort: { timestamp: -1 } },
                    { $limit: 1 }
                ],
                as: 'previousMessage'
            }
        },
        {
            $unwind: '$previousMessage'
        },
        {
            $project: {
                responseTime: {
                    $subtract: ['$timestamp', '$previousMessage.timestamp']
                }
            }
        },
        {
            $group: {
                _id: null,
                avgResponseTime: { $avg: '$responseTime' },
                minResponseTime: { $min: '$responseTime' },
                maxResponseTime: { $max: '$responseTime' }
            }
        }
    ];
    
    const [result] = await db.getCollection('messages')
        .aggregate(pipeline)
        .toArray();
    
    return {
        avg: Math.round(result.avgResponseTime / 1000), // seconds
        min: Math.round(result.minResponseTime / 1000),
        max: Math.round(result.maxResponseTime / 1000)
    };
}

// Top contacts
async function getTopContacts(limit = 10) {
    return await db.getCollection('contacts')
        .find({})
        .sort({ messageCount: -1 })
        .limit(limit)
        .toArray();
}

User State Management

Track Bot Flow State:

javascript

async function getUserState(wa_id) {
    const state = await db.getCollection('userStates').findOne({ wa_id });
    return state || { wa_id, currentFlow: null, data: {} };
}

async function setUserState(wa_id, flow, data = {}) {
    await db.getCollection('userStates').updateOne(
        { wa_id },
        {
            $set: {
                currentFlow: flow,
                data,
                updatedAt: new Date()
            }
        },
        { upsert: true }
    );
}

async function clearUserState(wa_id) {
    await db.getCollection('userStates').deleteOne({ wa_id });
}

// Usage dalam bot flow
async function handleMessage(wa_id, message) {
    const state = await getUserState(wa_id);
    
    if (state.currentFlow === 'order') {
        return await handleOrderFlow(wa_id, message, state.data);
    }
    
    if (state.currentFlow === 'support') {
        return await handleSupportFlow(wa_id, message, state.data);
    }
    
    // No active flow, check intent
    return await handleNewMessage(wa_id, message);
}

Data Cleanup

javascript

// Cleanup old messages (GDPR, storage)
async function cleanupOldMessages(daysToKeep = 90) {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - daysToKeep);
    
    const result = await db.getCollection('messages').deleteMany({
        timestamp: { $lt: cutoffDate }
    });
    
    console.log(`Deleted ${result.deletedCount} old messages`);
    return result.deletedCount;
}

// Archive instead of delete
async function archiveOldMessages(daysToKeep = 90) {
    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - daysToKeep);
    
    const oldMessages = await db.getCollection('messages')
        .find({ timestamp: { $lt: cutoffDate } })
        .toArray();
    
    if (oldMessages.length > 0) {
        // Move ke archive collection
        await db.getCollection('messages_archive').insertMany(oldMessages);
        
        // Delete dari main collection
        await db.getCollection('messages').deleteMany({
            timestamp: { $lt: cutoffDate }
        });
    }
    
    return oldMessages.length;
}

// Schedule cleanup
const cron = require('node-cron');

cron.schedule('0 2 * * *', async () => {
    // Run setiap hari jam 2 pagi
    await cleanupOldMessages(90);
});

Best Practices

DO ✅

- Index field yang sering di-query
- Gunakan connection pooling
- Implement data retention policy
- Encrypt sensitive data
- Regular backup
- Monitor query performance

DON'T ❌

- Query tanpa index
- Open new connection tiap request
- Simpan data selamanya
- Store password plain text
- Skip backup
- Ignore slow queries

FAQ

Database mana yang terbaik?

MongoDB untuk flexibility dan chat data. PostgreSQL jika butuh relational dan transactions.

Berapa lama simpan data?

Tergantung regulasi dan kebutuhan. GDPR: minimal retention. Bisnis: 1-2 tahun typical.

Perlu backup?

WAJIB! Automated daily backup, test restore regularly.

Kesimpulan

Database = Bot yang ingat!

Setup Database Sekarang →

Artikel Terkait

• WhatsApp API untuk Node.js
• WhatsApp API Session Management
• WhatsApp API Deployment