Session Management and Conversation Context

Session Schema

CREATE TABLE wa_sessions (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  team_id         INT UNSIGNED NOT NULL,
  wa_contact_id   BIGINT UNSIGNED NOT NULL,
  session_key     VARCHAR(40) NOT NULL UNIQUE,
  last_active_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  expires_at      DATETIME NOT NULL,
  context_turns   TINYINT UNSIGNED NOT NULL DEFAULT 0,
  INDEX idx_contact_session (wa_contact_id, expires_at)
);

CREATE TABLE wa_session_messages (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id  BIGINT UNSIGNED NOT NULL,
  role        ENUM('user','assistant') NOT NULL,
  content     TEXT NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (session_id) REFERENCES wa_sessions(id) ON DELETE CASCADE
);

Context Injection

class SessionManager
{
    private const SESSION_TTL_HOURS = 4;
    private const MAX_CONTEXT_TURNS = 10;

    public function getOrCreateSession(int $contactId, int $teamId, PDO $pdo): array
    {
        $stmt = $pdo->prepare(
            'SELECT * FROM wa_sessions WHERE wa_contact_id = ? AND expires_at > NOW()'
        );
        $stmt->execute([$contactId]);
        $session = $stmt->fetch();

        if (!$session) {
            $pdo->prepare(
                'INSERT INTO wa_sessions (team_id, wa_contact_id, session_key, expires_at)
                 VALUES (?, ?, ?, DATE_ADD(NOW(), INTERVAL ? HOUR))'
            )->execute([$teamId, $contactId, bin2hex(random_bytes(20)), self::SESSION_TTL_HOURS]);
            $session = $pdo->query("SELECT * FROM wa_sessions WHERE id = LAST_INSERT_ID()")->fetch();
        }

        return $session;
    }

    public function getContextMessages(int $sessionId, PDO $pdo): array
    {
        $stmt = $pdo->prepare(
            'SELECT role, content FROM wa_session_messages
             WHERE session_id = ?
             ORDER BY created_at DESC LIMIT ' . (self::MAX_CONTEXT_TURNS * 2)
        );
        $stmt->execute([$sessionId]);
        $rows = $stmt->fetchAll();
        return array_reverse($rows);  // Restore chronological order
    }

    public function appendMessage(int $sessionId, string $role, string $content, PDO $pdo): void
    {
        $pdo->prepare(
            'INSERT INTO wa_session_messages (session_id, role, content) VALUES (?,?,?)'
        )->execute([$sessionId, $role, $content]);

        // Keep only the last MAX_CONTEXT_TURNS * 2 messages
        $pdo->prepare(
            'DELETE FROM wa_session_messages WHERE session_id = ? AND id NOT IN (
                SELECT id FROM (
                    SELECT id FROM wa_session_messages WHERE session_id = ?
                    ORDER BY created_at DESC LIMIT ' . (self::MAX_CONTEXT_TURNS * 2) . '
                ) t
             )'
        )->execute([$sessionId, $sessionId]);
    }
}

What to Watch For

• Session expiry on logout — When a user explicitly says "goodbye" or "end session", expire the session immediately rather than waiting for TTL.
• Multi-device scope — A WhatsApp number can be used on multiple devices. Sessions are per wa_contact (phone number), not per device. This is correct — it prevents context fragmentation.
• Context window budget — The session context plus the system prompt plus the current message must fit within the LLM's context window. Always measure the combined token count before the LLM call.