Skip to content

Self-evolving Context Database for AI Agents. Unify Agent Memory, Knowledge RAG and Skills.

28.8 KB
Raw Blame
# Copyright (c) 2026 Beijing Volcano Engine Technology Co., Ltd.
# SPDX-License-Identifier: AGPL-3.0

from __future__ import annotations

import sqlite3
from datetime import datetime, timezone
from zoneinfo import ZoneInfo

import pytest

from openviking.observability.events import ObservabilityEvent
from openviking.observability.usage_audit import sqlite_store as sqlite_store_module
from openviking.observability.usage_audit.sqlite_store import SQLiteUsageAuditStore

UTC = ZoneInfo("UTC")


def _event(
    event_name: str,
    payload: dict,
    *,
    ts: datetime | None = None,
    user_id: str = "user-1",
) -> ObservabilityEvent:
    return ObservabilityEvent(
        event_name=event_name,
        payload=payload,
        timestamp=ts or datetime(2026, 5, 12, 1, 2, 3, tzinfo=timezone.utc),
        request_id=payload.get("request_id"),
        account_id="acct-1",
        user_id=user_id,
    )


def _create_legacy_usage_audit_db(db_path) -> None:
    conn = sqlite3.connect(db_path)
    try:
        conn.executescript(
            """
            CREATE TABLE _schema_meta (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );
            INSERT INTO _schema_meta (key, value) VALUES ('version', '3');
            CREATE TABLE usage_token_daily (
                account_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                date TEXT NOT NULL,
                source TEXT NOT NULL,
                token_type TEXT NOT NULL,
                provider TEXT NOT NULL,
                model_name TEXT NOT NULL,
                token_count INTEGER NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL,
                PRIMARY KEY (
                    account_id, user_id, date, source, token_type, provider, model_name
                )
            );
            CREATE TABLE usage_retrieval_daily (
                account_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                date TEXT NOT NULL,
                operation TEXT NOT NULL,
                status TEXT NOT NULL,
                request_count INTEGER NOT NULL DEFAULT 0,
                result_count INTEGER NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL,
                PRIMARY KEY (account_id, user_id, date, operation, status)
            );
            CREATE TABLE usage_context_write_bucket (
                account_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                date TEXT NOT NULL,
                hour_bucket INTEGER NOT NULL,
                operation TEXT NOT NULL,
                count INTEGER NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL,
                PRIMARY KEY (account_id, user_id, date, hour_bucket, operation)
            );
            CREATE TABLE request_audit (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                request_id TEXT,
                account_id TEXT NOT NULL,
                user_id TEXT,
                method TEXT NOT NULL,
                route TEXT NOT NULL,
                api_type TEXT NOT NULL,
                status_code INTEGER NOT NULL,
                duration_ms REAL NOT NULL,
                created_at TEXT NOT NULL
            );
            INSERT INTO request_audit (
                request_id, account_id, user_id, method, route,
                api_type, status_code, duration_ms, created_at
            )
            VALUES (
                'legacy-req', 'acct-1', 'user-1', 'GET',
                '/api/v1/system/status', 'system', 200, 1.0,
                '2026-05-11T00:00:00+08:00'
            );
            """
        )
        conn.commit()
    finally:
        conn.close()


def _create_v4_usage_audit_db(db_path) -> None:
    """Create and populate the exact compatible layout shipped by schema v4."""
    conn = sqlite3.connect(db_path)
    try:
        conn.executescript(
            """
            CREATE TABLE _schema_meta (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );
            INSERT INTO _schema_meta (key, value) VALUES ('version', '4');

            CREATE TABLE usage_token_hourly (
                account_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                date_utc TEXT NOT NULL,
                hour_utc INTEGER NOT NULL,
                source TEXT NOT NULL,
                token_type TEXT NOT NULL,
                provider TEXT NOT NULL,
                model_name TEXT NOT NULL,
                token_count INTEGER NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL,
                PRIMARY KEY (
                    account_id, user_id, date_utc, hour_utc,
                    source, token_type, provider, model_name
                )
            );
            CREATE TABLE usage_retrieval_hourly (
                account_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                date_utc TEXT NOT NULL,
                hour_utc INTEGER NOT NULL,
                operation TEXT NOT NULL,
                status TEXT NOT NULL,
                request_count INTEGER NOT NULL DEFAULT 0,
                result_count INTEGER NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL,
                PRIMARY KEY (
                    account_id, user_id, date_utc, hour_utc, operation, status
                )
            );
            CREATE TABLE usage_context_write_bucket (
                account_id TEXT NOT NULL,
                user_id TEXT NOT NULL,
                date_utc TEXT NOT NULL,
                hour_utc INTEGER NOT NULL,
                operation TEXT NOT NULL,
                count INTEGER NOT NULL DEFAULT 0,
                updated_at TEXT NOT NULL,
                PRIMARY KEY (account_id, user_id, date_utc, hour_utc, operation)
            );
            CREATE TABLE request_audit (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                request_id TEXT,
                account_id TEXT NOT NULL,
                user_id TEXT,
                method TEXT NOT NULL,
                route TEXT NOT NULL,
                api_type TEXT NOT NULL,
                status_code INTEGER NOT NULL,
                duration_ms REAL NOT NULL,
                created_at TEXT NOT NULL
            );

            INSERT INTO usage_token_hourly VALUES (
                'acct-1', 'user-1', '2026-05-12', 1,
                'vlm', 'input', 'p', 'm', 11, '2026-05-12T01:02:03+00:00'
            );
            INSERT INTO usage_retrieval_hourly VALUES (
                'acct-1', 'user-1', '2026-05-12', 1,
                'find', 'success', 2, 0, '2026-05-12T01:02:03+00:00'
            );
            INSERT INTO usage_context_write_bucket VALUES (
                'acct-1', 'user-1', '2026-05-12', 1,
                'session.commit', 3, '2026-05-12T01:02:03+00:00'
            );
            INSERT INTO request_audit (
                request_id, account_id, user_id, method, route,
                api_type, status_code, duration_ms, created_at
            ) VALUES (
                'v4-request', 'acct-1', 'user-1', 'GET',
                '/api/v1/system/status', 'system', 200, 1.0,
                '2026-05-12T01:02:03+00:00'
            );
            """
        )
        conn.commit()
    finally:
        conn.close()


@pytest.mark.asyncio
async def test_sqlite_usage_audit_store_aggregates_dashboard_data(tmp_path):
    store = SQLiteUsageAuditStore(tmp_path / "usage.sqlite3")
    await store.initialize()
    try:
        await store.record_batch(
            [
                _event(
                    "vlm.call",
                    {
                        "provider": "p",
                        "model_name": "m",
                        "prompt_tokens": 3,
                        "completion_tokens": 2,
                    },
                ),
                _event(
                    "embedding.call",
                    {
                        "provider": "p",
                        "model_name": "e",
                        "prompt_tokens": 5,
                        "completion_tokens": 0,
                    },
                ),
                _event(
                    "embedding.call",
                    {
                        "provider": "p",
                        "model_name": "e",
                        "prompt_tokens": 100,
                        "completion_tokens": 0,
                    },
                    user_id="user-2",
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-find",
                        "method": "POST",
                        "route": "/api/v1/search/find",
                        "status": "200",
                        "duration_seconds": 0.1,
                    },
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-search-user-2",
                        "method": "POST",
                        "route": "/api/v1/search/search",
                        "status": "200",
                        "duration_seconds": 0.1,
                    },
                    user_id="user-2",
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-message",
                        "method": "POST",
                        "route": "/api/v1/sessions/{session_id}/messages",
                        "status": "200",
                        "duration_seconds": 0.1,
                    },
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-message-user-2",
                        "method": "POST",
                        "route": "/api/v1/sessions/{session_id}/messages",
                        "status": "200",
                        "duration_seconds": 0.1,
                    },
                    user_id="user-2",
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-console",
                        "method": "GET",
                        "route": "/api/v1/console/dashboard/summary",
                        "status": "200",
                        "duration_seconds": 0.01,
                    },
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-docs",
                        "method": "GET",
                        "route": "/docs",
                        "status": "200",
                        "duration_seconds": 0.01,
                    },
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-favicon",
                        "method": "GET",
                        "route": "/favicon.ico",
                        "status": "200",
                        "duration_seconds": 0.01,
                    },
                ),
            ]
        )

        assert await store.get_today_tokens(
            account_id="acct-1", user_date="2026-05-12", tz=UTC
        ) == {
            "vlm_input": 3,
            "vlm_output": 2,
            "embedding_input": 105,
            "total": 110,
        }
        assert await store.get_today_tokens(
            account_id="acct-1", user_id="user-1", user_date="2026-05-12", tz=UTC
        ) == {
            "vlm_input": 3,
            "vlm_output": 2,
            "embedding_input": 5,
            "total": 10,
        }
        assert await store.get_today_retrievals(
            account_id="acct-1", user_date="2026-05-12", tz=UTC
        ) == {
            "find": 1,
            "search": 1,
            "total": 2,
        }
        assert await store.get_today_retrievals(
            account_id="acct-1", user_id="user-1", user_date="2026-05-12", tz=UTC
        ) == {
            "find": 1,
            "search": 0,
            "total": 1,
        }
        commits = await store.get_context_commit_heatmap(
            account_id="acct-1",
            start_user_date="2026-05-12",
            end_user_date="2026-05-12",
            bucket="hour",
            tz=UTC,
        )
        account_commits = commits
        commits = await store.get_context_commit_heatmap(
            account_id="acct-1",
            user_id="user-1",
            start_user_date="2026-05-12",
            end_user_date="2026-05-12",
            bucket="hour",
            tz=UTC,
        )
        assert any(
            row["hour"] == 1 and row["session_add_message"] == 2 for row in account_commits
        )
        assert any(row["hour"] == 1 and row["session_add_message"] == 1 for row in commits)
        audit = await store.query_audit_logs(account_id="acct-1")
        assert audit["total"] == 4
        assert audit["success_rate"] == 1.0
        assert {item["api_type"] for item in audit["items"]} == {
            "search.find",
            "search.search",
            "sessions",
        }
        audit = await store.query_audit_logs(account_id="acct-1", user_id="user-1")
        assert audit["total"] == 2
        assert audit["success_rate"] == 1.0
        assert {item["request_id"] for item in audit["items"]} == {"req-find", "req-message"}
    finally:
        await store.close()


@pytest.mark.asyncio
async def test_sqlite_usage_audit_store_resets_incompatible_legacy_schema(tmp_path):
    db_path = tmp_path / "usage.sqlite3"
    _create_legacy_usage_audit_db(db_path)

    store = SQLiteUsageAuditStore(db_path)
    await store.initialize()
    try:
        await store.record_batch(
            [
                _event(
                    "vlm.call",
                    {
                        "provider": "p",
                        "model_name": "m",
                        "prompt_tokens": 8,
                        "completion_tokens": 2,
                    },
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-message",
                        "method": "POST",
                        "route": "/api/v1/sessions/{session_id}/messages",
                        "status": "200",
                        "duration_seconds": 0.1,
                    },
                ),
            ]
        )

        assert await store.get_today_tokens(
            account_id="acct-1", user_date="2026-05-12", tz=UTC
        ) == {
            "vlm_input": 8,
            "vlm_output": 2,
            "embedding_input": 0,
            "total": 10,
        }
        commits = await store.get_context_commit_heatmap(
            account_id="acct-1",
            start_user_date="2026-05-12",
            end_user_date="2026-05-12",
            bucket="hour",
            tz=UTC,
        )
        assert any(row["hour"] == 1 and row["session_add_message"] == 1 for row in commits)
        audit = await store.query_audit_logs(account_id="acct-1", page_size=10)
        assert audit["total"] == 1
        assert audit["items"][0]["request_id"] == "req-message"
    finally:
        await store.close()

    conn = sqlite3.connect(db_path)
    try:
        tables = {
            row[0] for row in conn.execute("SELECT name FROM sqlite_master WHERE type = 'table'")
        }
        assert "usage_token_daily" not in tables
        assert "usage_retrieval_daily" not in tables
        columns = {row[1] for row in conn.execute("PRAGMA table_info(usage_context_write_bucket)")}
        assert "date_utc" in columns
        assert "date" not in columns
        context_columns = {
            row[1] for row in conn.execute("PRAGMA table_info(usage_context_write_bucket)")
        }
        assert "hour_utc" in context_columns
        assert "hour_bucket" not in context_columns
        version = conn.execute("SELECT value FROM _schema_meta WHERE key = 'version'").fetchone()
        assert version == ("5",)
    finally:
        conn.close()


@pytest.mark.asyncio
async def test_sqlite_usage_audit_store_migrates_v4_without_losing_rows(tmp_path):
    db_path = tmp_path / "usage.sqlite3"
    _create_v4_usage_audit_db(db_path)

    store = SQLiteUsageAuditStore(db_path)
    await store.initialize()
    try:
        tokens = await store.get_today_tokens(
            account_id="acct-1",
            user_id="user-1",
            user_date="2026-05-12",
            tz=UTC,
        )
        retrievals = await store.get_today_retrievals(
            account_id="acct-1",
            user_id="user-1",
            user_date="2026-05-12",
            tz=UTC,
        )
        commits = await store.get_context_commit_heatmap(
            account_id="acct-1",
            user_id="user-1",
            start_user_date="2026-05-12",
            end_user_date="2026-05-12",
            bucket="hour",
            tz=UTC,
        )
        before = await store.query_audit_logs(account_id="acct-1", page_size=10)

        assert tokens["vlm_input"] == 11
        assert retrievals["find"] == 2
        assert any(row["session_commit"] == 3 for row in commits)
        assert before["items"][0]["request_id"] == "v4-request"
        assert before["items"][0]["error_code"] is None
        assert before["items"][0]["error_message"] is None
        assert before["items"][0]["error_details"] is None

        await store.record_batch(
            [
                _event(
                    "http.request",
                    {
                        "request_id": "new-error",
                        "method": "GET",
                        "route": "/api/v1/tasks/{task_id}",
                        "status": "404",
                        "duration_seconds": 0.01,
                        "error_code": "NOT_FOUND",
                        "error_message": "Task was not found",
                        "error_details": {"task_id": "missing"},
                    },
                )
            ]
        )
        after = await store.query_audit_logs(account_id="acct-1", page_size=10)
        inserted = next(item for item in after["items"] if item["request_id"] == "new-error")
        assert inserted["error_code"] == "NOT_FOUND"
        assert inserted["error_message"] == "Task was not found"
        assert inserted["error_details"] == {"task_id": "missing"}
        assert {item["request_id"] for item in after["items"]} == {
            "v4-request",
            "new-error",
        }
    finally:
        await store.close()

    conn = sqlite3.connect(db_path)
    try:
        columns = {row[1] for row in conn.execute("PRAGMA table_info(request_audit)")}
        assert {"error_code", "error_message", "error_details"} <= columns
        version = conn.execute("SELECT value FROM _schema_meta WHERE key = 'version'").fetchone()
        assert version == ("5",)
    finally:
        conn.close()


@pytest.mark.asyncio
async def test_sqlite_usage_audit_store_rejects_unhandled_future_migration_without_data_loss(
    tmp_path, monkeypatch
):
    db_path = tmp_path / "usage.sqlite3"
    _create_v4_usage_audit_db(db_path)

    store = SQLiteUsageAuditStore(db_path)
    await store.initialize()
    await store.close()

    monkeypatch.setattr(sqlite_store_module, "SCHEMA_VERSION", 6)
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    try:
        with pytest.raises(
            RuntimeError,
            match="No usage/audit schema migration path from version 5 to 6",
        ):
            SQLiteUsageAuditStore._migrate_legacy_sync(conn)

        assert conn.execute("SELECT COUNT(*) FROM request_audit").fetchone()[0] == 1
        assert conn.execute("SELECT COUNT(*) FROM usage_token_hourly").fetchone()[0] == 1
        version = conn.execute("SELECT value FROM _schema_meta WHERE key = 'version'").fetchone()
        assert version[0] == "5"
    finally:
        conn.close()


@pytest.mark.asyncio
async def test_sqlite_usage_audit_store_handles_malformed_error_details(tmp_path):
    db_path = tmp_path / "usage.sqlite3"
    store = SQLiteUsageAuditStore(db_path)
    await store.initialize()
    try:
        assert store._conn is not None
        store._conn.execute(
            """
            INSERT INTO request_audit (
                request_id, account_id, user_id, method, route, api_type,
                status_code, duration_ms, error_code, error_message,
                error_details, created_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            """,
            (
                "malformed",
                "acct-1",
                "user-1",
                "GET",
                "/api/v1/tasks/{task_id}",
                "tasks",
                404,
                1.0,
                "NOT_FOUND",
                "Task was not found",
                "not-json",
                "2026-05-12T01:02:03+00:00",
            ),
        )

        audit = await store.query_audit_logs(account_id="acct-1")
        assert audit["items"][0]["error_details"] is None
    finally:
        await store.close()


@pytest.mark.asyncio
async def test_sqlite_usage_audit_store_trims_audit_per_account(tmp_path):
    store = SQLiteUsageAuditStore(
        tmp_path / "usage.sqlite3",
        audit_retention_per_account=2,
    )
    await store.initialize()
    try:
        await store.record_batch(
            [
                _event(
                    "http.request",
                    {
                        "request_id": f"req-{idx}",
                        "method": "GET",
                        "route": "/api/v1/system/status",
                        "status": "200",
                        "duration_seconds": 0.01,
                    },
                )
                for idx in range(3)
            ]
        )
        audit = await store.query_audit_logs(account_id="acct-1", page_size=10)
        assert audit["total"] == 2
        assert [item["request_id"] for item in audit["items"]] == ["req-2", "req-1"]
    finally:
        await store.close()


@pytest.mark.asyncio
async def test_sqlite_usage_audit_store_success_filter_includes_3xx(tmp_path):
    store = SQLiteUsageAuditStore(tmp_path / "usage.sqlite3")
    await store.initialize()
    try:
        await store.record_batch(
            [
                _event(
                    "http.request",
                    {
                        "request_id": "req-200",
                        "method": "GET",
                        "route": "/api/v1/system/status",
                        "status": "200",
                        "duration_seconds": 0.01,
                    },
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-302",
                        "method": "GET",
                        "route": "/api/v1/system/status",
                        "status": "302",
                        "duration_seconds": 0.01,
                    },
                ),
                _event(
                    "http.request",
                    {
                        "request_id": "req-500",
                        "method": "GET",
                        "route": "/api/v1/system/status",
                        "status": "500",
                        "duration_seconds": 0.01,
                    },
                ),
            ]
        )

        success = await store.query_audit_logs(
            account_id="acct-1",
            statuses=["success"],
            page_size=10,
        )
        explicit_2xx = await store.query_audit_logs(
            account_id="acct-1",
            statuses=["2xx"],
            page_size=10,
        )

        assert success["total"] == 2
        assert {item["request_id"] for item in success["items"]} == {"req-200", "req-302"}
        assert explicit_2xx["total"] == 1
        assert explicit_2xx["items"][0]["request_id"] == "req-200"
    finally:
        await store.close()


@pytest.mark.asyncio
async def test_today_tokens_rebuckets_for_utc_plus_8(tmp_path):
    """A UTC+8 user querying "today" must include UTC events that fall inside
    their local day, even when they span two UTC dates.

    Two events bracket a UTC day boundary at 16:00 UTC = 00:00 next day
    in Asia/Shanghai. From the SH viewer's perspective on 2026-05-13 both
    events belong to "today"; from a UTC viewer they straddle two days.
    """
    store = SQLiteUsageAuditStore(tmp_path / "usage.sqlite3")
    await store.initialize()
    try:
        await store.record_batch(
            [
                _event(  # 2026-05-12 23:30 UTC == 2026-05-13 07:30 SH
                    "vlm.call",
                    {
                        "provider": "p",
                        "model_name": "m",
                        "prompt_tokens": 11,
                        "completion_tokens": 0,
                    },
                    ts=datetime(2026, 5, 12, 23, 30, tzinfo=timezone.utc),
                ),
                _event(  # 2026-05-13 04:30 UTC == 2026-05-13 12:30 SH
                    "vlm.call",
                    {
                        "provider": "p",
                        "model_name": "m",
                        "prompt_tokens": 22,
                        "completion_tokens": 0,
                    },
                    ts=datetime(2026, 5, 13, 4, 30, tzinfo=timezone.utc),
                ),
                _event(  # 2026-05-13 16:30 UTC == 2026-05-14 00:30 SH (next day)
                    "vlm.call",
                    {
                        "provider": "p",
                        "model_name": "m",
                        "prompt_tokens": 9999,
                        "completion_tokens": 0,
                    },
                    ts=datetime(2026, 5, 13, 16, 30, tzinfo=timezone.utc),
                ),
            ]
        )

        shanghai = ZoneInfo("Asia/Shanghai")
        sh_today = await store.get_today_tokens(
            account_id="acct-1", user_date="2026-05-13", tz=shanghai
        )
        # Includes the 23:30 UTC event but excludes the 16:30 UTC event that
        # already rolled into the next SH day.
        assert sh_today["vlm_input"] == 33
        assert sh_today["total"] == 33

        utc_today = await store.get_today_tokens(
            account_id="acct-1", user_date="2026-05-13", tz=UTC
        )
        # UTC viewer's day starts at 00:00 UTC, so misses the 23:30 prior-day
        # event but includes the 16:30 event.
        assert utc_today["vlm_input"] == 22 + 9999
    finally:
        await store.close()


@pytest.mark.asyncio
async def test_token_series_rebuckets_to_user_tz_days(tmp_path):
    """An event at 03:00 UTC must land in `2026-05-11` for an America/New_York
    viewer (UTC-4 with DST in May = 23:00 of the previous day local)."""
    store = SQLiteUsageAuditStore(tmp_path / "usage.sqlite3")
    await store.initialize()
    try:
        await store.record_batch(
            [
                _event(
                    "vlm.call",
                    {
                        "provider": "p",
                        "model_name": "m",
                        "prompt_tokens": 7,
                        "completion_tokens": 0,
                    },
                    ts=datetime(2026, 5, 12, 3, 0, tzinfo=timezone.utc),
                ),
            ]
        )
        ny = ZoneInfo("America/New_York")
        series = await store.get_token_series(
            account_id="acct-1",
            start_user_date="2026-05-10",
            end_user_date="2026-05-12",
            bucket="day",
            tz=ny,
        )
        by_date = {row["date"]: row for row in series}
        # 03:00 UTC on 2026-05-12 falls on 2026-05-11 23:00 in New York.
        assert by_date["2026-05-11"]["vlm_input"] == 7
        assert by_date["2026-05-12"]["vlm_input"] == 0
    finally:
        await store.close()


@pytest.mark.asyncio
async def test_context_heatmap_rebuckets_hour_for_shanghai(tmp_path):
    """A context commit at 23:30 UTC must show up at hour 7 of the next day in
    Asia/Shanghai (UTC+8)."""
    store = SQLiteUsageAuditStore(tmp_path / "usage.sqlite3")
    await store.initialize()
    try:
        await store.record_batch(
            [
                _event(
                    "http.request",
                    {
                        "request_id": "req-late",
                        "method": "POST",
                        "route": "/api/v1/sessions/{session_id}/messages",
                        "status": "200",
                        "duration_seconds": 0.01,
                    },
                    ts=datetime(2026, 5, 12, 23, 30, tzinfo=timezone.utc),
                ),
            ]
        )
        shanghai = ZoneInfo("Asia/Shanghai")
        rows = await store.get_context_commit_heatmap(
            account_id="acct-1",
            start_user_date="2026-05-13",
            end_user_date="2026-05-13",
            bucket="hour",
            tz=shanghai,
        )
        match = next(
            (row for row in rows if row["date"] == "2026-05-13" and row["hour"] == 7),
            None,
        )
        assert match is not None
        assert match["session_add_message"] == 1
        assert match["total"] == 1
    finally:
        await store.close()