基于NodeJS+PostgreSQL的信使聊天机器人超时机制开发方案咨询

Hey there! Let's work through the best approach for your Node.js + PostgreSQL messenger bot—sounds like you've got a solid start with tracking last message times, so let's refine that into a robust, scalable solution that meets all your requirements.

First, let's align on what we need to deliver:

• Users must send a message at least every minute; trigger a 10-second warning if they're approaching the timeout
• End the chat if no response comes after the full minute
• Preserve timeout state across bot restarts
• Support multiple Node.js instances behind load balancing without conflicts

Your initial idea to store last message records is spot-on—we'll expand this into a user_sessions table that tracks all critical state for each user. This ensures state persists across restarts and is shared across all instances.

-- Create a status enum to track session state
CREATE TYPE session_status AS ENUM ('active', 'warning', 'ended');

-- Session table
CREATE TABLE user_sessions (
    user_id VARCHAR(255) PRIMARY KEY, -- Unique identifier for your user
    last_message_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status session_status NOT NULL DEFAULT 'active',
    warning_sent_at TIMESTAMP -- Tracks when we sent the 10-second warning
);

-- Add indexes for fast querying (critical for large user bases)
CREATE INDEX idx_sessions_status_last_msg ON user_sessions (status, last_message_at);

Instead of per-user timers (which break with multiple instances), we'll use a scheduled batch scan of the database. This avoids race conditions and works seamlessly with load balancing.

Key Rules for the Batch Job #

We'll run this job every 5 seconds (adjustable based on your needs):

• Trigger 10-second warning: Find active users where 50 seconds have passed since their last message, and no warning has been sent yet.
• End the chat: Find users in the "warning" state where 60 seconds have passed since their last message.

The critical detail here is using SELECT ... FOR UPDATE SKIP LOCKED—this ensures multiple instances don't process the same user at the same time.

Node.js Implementation Example #

const { Pool } = require('pg');

// Initialize PostgreSQL connection pool
const pool = new Pool({
    host: 'your-db-host',
    database: 'your-db-name',
    user: 'your-db-user',
    password: 'your-db-pass',
    max: 10 // Adjust based on your instance count and DB capacity
});

// Handle incoming user messages (resets the timeout clock)
async function handleIncomingMessage(userId) {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');
        
        // Update existing session to active state
        const updateResult = await client.query(`
            UPDATE user_sessions
            SET last_message_at = CURRENT_TIMESTAMP,
                status = 'active',
                warning_sent_at = NULL
            WHERE user_id = $1;
        `, [userId]);

        // Insert new session if user doesn't exist
        if (updateResult.rowCount === 0) {
            await client.query(`
                INSERT INTO user_sessions (user_id) VALUES ($1);
            `, [userId]);
        }

        await client.query('COMMIT');
    } catch (err) {
        await client.query('ROLLBACK');
        console.error('Failed to update user session:', err);
        throw err;
    } finally {
        client.release();
    }
}

// Scheduled batch job to check timeouts
setInterval(async () => {
    const client = await pool.connect();
    try {
        await client.query('BEGIN');

        // Step 1: Send 10-second warning to eligible users
        const warningCandidates = await client.query(`
            SELECT user_id FROM user_sessions
            WHERE status = 'active'
              AND last_message_at < CURRENT_TIMESTAMP - INTERVAL '50 seconds'
              AND warning_sent_at IS NULL
            FOR UPDATE SKIP LOCKED;
        `);

        for (const { user_id } of warningCandidates.rows) {
            // Replace with your actual message-sending logic
            await sendBotMessage(user_id, "⚠️ 还有10秒聊天将结束，请回复！");
            
            // Update session to warning state
            await client.query(`
                UPDATE user_sessions
                SET status = 'warning', warning_sent_at = CURRENT_TIMESTAMP
                WHERE user_id = $1;
            `, [user_id]);
        }

        // Step 2: End sessions that timed out completely
        const endCandidates = await client.query(`
            SELECT user_id FROM user_sessions
            WHERE status = 'warning'
              AND last_message_at < CURRENT_TIMESTAMP - INTERVAL '60 seconds'
            FOR UPDATE SKIP LOCKED;
        `);

        for (const { user_id } of endCandidates.rows) {
            // Replace with your actual message-sending logic
            await sendBotMessage(user_id, "🔚 聊天已结束，如需继续请重新发起对话。");
            
            // Update session to ended state
            await client.query(`
                UPDATE user_sessions
                SET status = 'ended'
                WHERE user_id = $1;
            `, [user_id]);
        }

        await client.query('COMMIT');
    } catch (err) {
        await client.query('ROLLBACK');
        console.error('Timeout check job failed:', err);
    } finally {
        client.release();
    }
}, 5000); // Run every 5 seconds

// Placeholder for your bot's message-sending function
async function sendBotMessage(userId, message) {
    // Implement integration with your messenger platform here
    console.log(`Sent to ${userId}: ${message}`);
}

• Connection Pooling: Configure the PostgreSQL pool size to match the number of Node.js instances (e.g., 10 connections for 2 instances) to avoid connection exhaustion.
• Batch Size Limits: If you have a large user base, add LIMIT 100 to your batch queries to prevent overwhelming the database.
• Error Handling: Add retries for failed message sends (e.g., using a queue like BullMQ) to ensure users don't miss critical notifications.
• Session Cleanup: Add a weekly cron job to archive or delete ended sessions to keep the table performant.

内容的提问来源于stack exchange，提问作者fstn