Skip to content

CubeLoop — a Pythonic, async-native agent harness core designed for high performance, readability, and production-grade persistence.

10.2 KB
Raw Blame

title: MySQL Checkpointing description: "Use MySQLCheckpointer for persistent agent state in CubeLoop with the MySQL backend."

MySQL Checkpointing

MySQLCheckpointer is a production-grade persistence backend for deployments standardized on MySQL. It mirrors PostgresCheckpointer: aiomysql connection pool, msgpack payloads, a queryable JSON metadata column, and a host-managed schema verified on connect. Per-thread seq allocation is serialized with a SELECT … FOR UPDATE row lock so multiple processes can append to the same thread_id safely.

Requires MySQL 8.0.13+ (for JSON expression defaults) on the InnoDB engine.

Install the extra:

pip install "cubeloop[mysql]"

This pulls in aiomysql, sqlalchemy, and msgpack.

Basic usage

import asyncio
from cubeloop import Agent
from cubeloop.checkpointer import MySQLCheckpointer
from cubeloop.providers.anthropic import AnthropicProvider


async def main():
    provider = AnthropicProvider(provider_id="anthropic", api_key="…")
    async with MySQLCheckpointer("mysql://user:pass@host:3306/dbname") as cp:
        agent = Agent(
            model=provider.model("claude-sonnet-4-6"),
            checkpointer=cp,
            thread_id="user-42",
        )
        await agent.prompt("hello")


asyncio.run(main())

The connection is configured from a mysql://user:pass@host:port/db DSN. Pool sizing:

async with MySQLCheckpointer(
    "mysql://…",
    min_pool_size=2,
    max_pool_size=20,
) as cp:
    …

Schema

The checkpointer expects cubepi_threads, cubepi_messages, cubepi_runs, cubepi_hitl_answers, and cubepi_schema_version. Like Postgres (and unlike SQLite), CubeLoop does not create these for you — it verifies on __aenter__ that they exist with the expected schema_version.

If they're missing, you get CubeloopSchemaUninitialized. If the version doesn't match this CubeLoop release, you get CubeloopSchemaMismatch. A production database belongs to the host application's migration system (Alembic, …), not to a third-party library.

Bootstrapping via Alembic

CubeLoop exposes the SQLAlchemy MetaData so your migrations can adopt the schema:

# alembic/env.py
from cubeloop.checkpointer.mysql import cubeloop_metadata, EXPECTED_SCHEMA_VERSION

target_metadata = [my_app_metadata, cubeloop_metadata]

Then autogenerate a revision:

alembic revision --autogenerate -m "add cubeloop checkpointer"

The cubepi_messages table is KEY-partitioned, which SQLAlchemy's autogenerate cannot express, so append the partition clause and the schema-version write with the provided helpers:

# In a migration's upgrade():
from cubeloop.checkpointer.mysql.alembic_helpers import (
    messages_partition_clause,
    write_schema_version_op,
)

def upgrade():
    op.create_table("cubepi_threads", ...)
    op.create_table(
        "cubepi_messages", ...,
        mysql_engine="InnoDB",
    )
    # KEY(thread_id) partitioning is not autogenerated — apply it explicitly:
    op.execute(
        "ALTER TABLE cubepi_messages " + messages_partition_clause()
    )
    op.create_table("cubepi_schema_version", ...)
    # write_schema_version_op() returns two ';'-separated statements
    # (DELETE then INSERT). MySQL/pymysql runs one statement per execute,
    # so split and execute each:
    for stmt in write_schema_version_op().split(";"):
        if stmt.strip():
            op.execute(stmt)

write_schema_version_op() is the immutable historical v5 writer. It is idempotent, deleting other version rows before inserting 5. A future schema version must ship and use a new version-specific writer; do not reuse this one.

Data model

cubepi_threads
    thread_id (PK)             -- VARCHAR(255) utf8mb4_bin (case-sensitive)
    parent_thread_id           -- self-FK, for forks
    forked_at_seq              -- seq number at fork point
    extra                      -- JSON
    created_at / updated_at

cubepi_messages
    thread_id, seq             -- composite PK; partitioned by KEY(thread_id) into 64
    role                       -- "user" | "assistant" | "tool"
    metadata                   -- JSON (not indexed; see below)
    payload                    -- LONGBLOB (msgpack)
    created_at

cubepi_runs
    thread_id, run_id          -- composite PK
    claimed_at / completed_at
    completion_seq

cubepi_hitl_answers
    thread_id, run_id, question_id -- composite PK
    answer                         -- JSON
    answered_at

cubepi_schema_version
    version (PK)

Important properties:

  • (thread_id, seq) is the message identity. seq is monotonic per thread, allocated under a SELECT … FOR UPDATE row lock on the thread row. Two concurrent writers on the same thread serialize cleanly.
  • payload is msgpack-encoded model.model_dump(mode="json"). CubeLoop reconstructs the Pydantic model on read.
  • metadata is JSON, queryable via MySQL JSON functions. It is not indexed — MySQL can't index a JSON column directly. If you need to query messages by metadata, add a generated-column index host-side.
  • Messages are partitioned by KEY(thread_id) into 64 partitions. Even distribution, no per-thread bottleneck. The table has no foreign key to cubepi_threads (MySQL forbids FKs on partitioned tables); integrity is preserved by the lazy thread-row insert on append.

Differences from the Postgres backend

Postgres MySQL
Driver asyncpg aiomysql
seq lock pg_advisory_xact_lock SELECT … FOR UPDATE
Messages partitioning HASH(thread_id) + FK KEY(thread_id), no FK
metadata index GIN none (add a generated column)
Thread IDs TEXT VARCHAR(255) utf8mb4_bin
Min version — MySQL 8.0.13+, InnoDB

save_extra semantics

save_extra does a shallow top-level merge, not a replace — the same behavior as Postgres and SQLite. Writing {"foo": 1} then {"bar": 2} leaves {"foo": 1, "bar": 2}. (Internally it reads the current extra under a row lock and writes the merged dict, rather than using JSON_MERGE_PATCH, whose null-deletion and deep-merge semantics differ from dict.update.)

Conditional pending cleanup

Hosts that coordinate HITL resumes across workers can use clear_pending_request_if_matches(thread_id, question_id=..., run_id=...). It clears the pending request and owning run ID only if both stored identifiers still match. The comparison and clear are one MySQL update, so stale cleanup cannot erase a newer request, even if another run reuses the question ID.

Forks

MySQLCheckpointer implements the v4 snapshot / fork / claim_run / mark_run_complete / load_pending Protocol methods, so it supports both Agent.fork(...) and Agent.fork_once(...). The parent_thread_id / forked_at_seq columns on cubepi_threads record fork lineage; cubepi_runs (added in v4) tracks per-run claim/completion state.

See the Conversation Forking guide for the user-facing API and semantics.

Schema v3 → v4 migration

The fork feature bumps EXPECTED_SCHEMA_VERSION from 3 to 4. The upgrade adds the run_id column + composite index to cubepi_messages and creates the cubepi_runs table. Use the provided alembic helper — MySQL/pymysql executes one statement per call, so split before executing:

# In a migration's upgrade():
from cubeloop.checkpointer.mysql.alembic_helpers import (
    upgrade_v3_to_v4_op,
    write_schema_version_op,
)

def upgrade():
    for stmt in upgrade_v3_to_v4_op().split(";"):
        if stmt.strip():
            op.execute(stmt)
    for stmt in write_schema_version_op().split(";"):
        if stmt.strip():
            op.execute(stmt)

upgrade_v3_to_v4_op() is idempotent under repeated execution. Pre-feature messages keep run_id = NULL and remain readable; see Legacy data behaviour for the fork-eligibility rules on mixed threads.

Schema v4 -> v5 migration

Durable HITL answer replay bumps EXPECTED_SCHEMA_VERSION from 4 to 5. The upgrade creates cubepi_hitl_answers, which stores answered HITL requests by (thread_id, run_id, question_id) until the suspended tool cycle completes.

# In a migration's upgrade():
from cubeloop.checkpointer.mysql.alembic_helpers import (
    upgrade_v4_to_v5_op,
    write_schema_version_op,
)

def upgrade():
    for stmt in upgrade_v4_to_v5_op().split(";"):
        if stmt.strip():
            op.execute(stmt)
    for stmt in write_schema_version_op().split(";"):
        if stmt.strip():
            op.execute(stmt)

CubeLoop rename

The package rename does not change the persistence schema: physical names stay cubepi_* and EXPECTED_SCHEMA_VERSION stays 5. Existing 0.13.6 databases need no migration. See the migration guide.

Common pitfalls

  • CubeloopSchemaUninitialized — Your DB is empty, your migrations didn't run, or the cubepi_schema_version table is malformed. Apply the host alembic upgrade first.
  • CubeloopSchemaMismatch — You upgraded CubeLoop but didn't generate a new migration. Generate one, apply it, and CubeLoop will start.
  • JSON default errors on older MySQL — JSON columns use DEFAULT (JSON_OBJECT()), which requires MySQL 8.0.13+.
  • Case-insensitive thread-ID collisions — Thread IDs use the utf8mb4_bin collation so UserA and usera stay distinct. If you hand-roll the DDL, keep that collation.
  • CheckpointCorruptionError on load() — A persisted message row failed to deserialize (bad msgpack payload, schema-invalid data, or an unknown role). The error's row_ref (e.g. cubepi_messages.seq=42) locates the bad row for inspection or surgical repair; thread_id and __cause__ carry the rest. CubeLoop never skips corrupt rows silently.

See also