CubeLoop — a Pythonic, async-native agent harness core designed for high performance, readability, and production-grade persistence.
MySQLCheckpointer Implementation Plan
For agentic workers: REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (
- [ ]) syntax for tracking.
Goal: Add a production-grade MySQLCheckpointer implementing the cubepi
Checkpointer protocol against MySQL, mirroring PostgresCheckpointer.
Architecture: New package cubepi/checkpointer/mysql/ (checkpointer / models
/ alembic_helpers / exceptions / init) following the Postgres package layout.
Connection pool via aiomysql, msgpack message payloads, JSON metadata/extra
columns, KEY(thread_id) partitioned messages table, host-managed schema
verified on entry. Schema exceptions are re-exported from the Postgres module
(single source of truth). Ships as optional mysql extra.
Tech Stack: Python 3.11+, aiomysql, SQLAlchemy 2.0 (table defs only), msgpack, pymysql (error codes, transitive via aiomysql), pytest/pytest-asyncio.
Spec: dev/specs/2026-05-27-mysql-checkpointer.md.
File Structure
- Create
cubepi/checkpointer/mysql/__init__.py— public exports + lazy nothing (eager, since importing it already implies the extra is installed). - Create
cubepi/checkpointer/mysql/exceptions.py— re-export schema exceptions fromcubepi.checkpointer.postgres.exceptions. - Create
cubepi/checkpointer/mysql/models.py— SQLAlchemy table defs on a privatecubepi_metadata; constantsEXPECTED_SCHEMA_VERSION,PARTITION_COUNT. - Create
cubepi/checkpointer/mysql/alembic_helpers.py—messages_partition_clause(),write_schema_version_op(). - Create
cubepi/checkpointer/mysql/checkpointer.py—MySQLCheckpointer+_parse_dsn,_role_of,_ROLE_TO_CLS,_decode_json. - Modify
cubepi/checkpointer/__init__.py— addMySQLCheckpointerto__getattr__and__all__. - Modify
pyproject.toml— addmysqloptional-dependency extra + dev group dep. - Create
tests/checkpointer/test_mysql.py— unit + E2E tests. - Modify
tests/checkpointer/conftest.py— add MySQL fixtures alongside PG ones. - Create/modify docs page under
website/docs/— MySQL checkpointer section.
Task 1: Dependency + package skeleton + exceptions
Files:
Modify:
pyproject.tomlCreate:
cubepi/checkpointer/mysql/__init__.pyCreate:
cubepi/checkpointer/mysql/exceptions.pyTest:
tests/checkpointer/test_mysql.pyStep 0: Add the
mysqlextra and dev dep (must precede any aiomysql import)
In pyproject.toml, under [project.optional-dependencies] add after the
postgres block:
mysql = [
"sqlalchemy>=2.0",
"aiomysql>=0.2",
"msgpack>=1.0",
]
In [dependency-groups] dev = [ ... ], add near the other DB drivers:
"aiomysql>=0.2",
Then run: uv sync --all-extras --dev
Expected: resolves and installs aiomysql (which pulls in PyMySQL).
- Step 1: Write the failing test
# tests/checkpointer/test_mysql.py
"""MySQLCheckpointer tests — mirrors test_postgres.py."""
import pytest
def test_exceptions_are_postgres_aliases() -> None:
from cubepi.checkpointer.mysql.exceptions import (
CubepiSchemaError,
CubepiSchemaMismatch,
CubepiSchemaUninitialized,
)
from cubepi.checkpointer.postgres import exceptions as pg_exc
assert CubepiSchemaError is pg_exc.CubepiSchemaError
assert CubepiSchemaMismatch is pg_exc.CubepiSchemaMismatch
assert CubepiSchemaUninitialized is pg_exc.CubepiSchemaUninitialized
- Step 2: Run test to verify it fails
Run: uv run pytest tests/checkpointer/test_mysql.py::test_exceptions_are_postgres_aliases -v
Expected: FAIL — ModuleNotFoundError: cubepi.checkpointer.mysql
- Step 3: Write minimal implementation
# cubepi/checkpointer/mysql/exceptions.py
"""Schema exceptions for MySQLCheckpointer.
These are backend-agnostic schema errors; re-exported from the Postgres
module so there is a single source of truth.
"""
from cubepi.checkpointer.postgres.exceptions import (
CubepiSchemaError,
CubepiSchemaMismatch,
CubepiSchemaUninitialized,
)
__all__ = [
"CubepiSchemaError",
"CubepiSchemaMismatch",
"CubepiSchemaUninitialized",
]
# cubepi/checkpointer/mysql/__init__.py
"""MySQLCheckpointer package.
Exports are built up incrementally across the plan. Exceptions are available
immediately; MySQLCheckpointer and model symbols are added once their modules
exist (Task 5), to avoid importing not-yet-written submodules.
"""
from cubepi.checkpointer.mysql.exceptions import (
CubepiSchemaError,
CubepiSchemaMismatch,
CubepiSchemaUninitialized,
)
__all__ = [
"CubepiSchemaError",
"CubepiSchemaMismatch",
"CubepiSchemaUninitialized",
]
Critical: importing the package runs __init__.py first, so it must NOT
import checkpointer/models until those modules exist. The full export list
(adding MySQLCheckpointer, EXPECTED_SCHEMA_VERSION, PARTITION_COUNT,
cubepi_metadata) is written in Task 5 Step 2, after all submodules exist.
- Step 4: Run test to verify it passes
Run: uv run pytest tests/checkpointer/test_mysql.py::test_exceptions_are_postgres_aliases -v
Expected: PASS
- Step 5: Commit
git add pyproject.toml uv.lock cubepi/checkpointer/mysql/__init__.py cubepi/checkpointer/mysql/exceptions.py tests/checkpointer/test_mysql.py
git commit -m "feat(checkpointer): mysql package skeleton, exception aliases, mysql extra"
Task 2: SQLAlchemy models
Files:
Create:
cubepi/checkpointer/mysql/models.pyTest:
tests/checkpointer/test_mysql.pyStep 1: Write the failing tests
# append to tests/checkpointer/test_mysql.py
def test_models_import() -> None:
from cubepi.checkpointer.mysql.models import (
EXPECTED_SCHEMA_VERSION,
PARTITION_COUNT,
CubepiMessage,
CubepiSchemaVersion,
CubepiThread,
cubepi_metadata,
)
assert EXPECTED_SCHEMA_VERSION == 1
assert PARTITION_COUNT == 64
assert CubepiThread.__tablename__ == "cubepi_threads"
assert CubepiMessage.__tablename__ == "cubepi_messages"
assert CubepiSchemaVersion.__tablename__ == "cubepi_schema_version"
assert "cubepi_threads" in cubepi_metadata.tables
assert "cubepi_messages" in cubepi_metadata.tables
assert "cubepi_schema_version" in cubepi_metadata.tables
def test_threads_parent_self_fk_present() -> None:
"""parent_thread_id keeps a self-FK (threads table is not partitioned)."""
from cubepi.checkpointer.mysql.models import cubepi_metadata
threads = cubepi_metadata.tables["cubepi_threads"]
fk_targets = {
fk.column.table.name
for col in threads.columns
for fk in col.foreign_keys
}
# parent_thread_id references cubepi_threads
assert "cubepi_threads" in fk_targets
def test_messages_has_no_foreign_keys() -> None:
"""messages table is partitioned, so no FK is allowed."""
from cubepi.checkpointer.mysql.models import cubepi_metadata
msgs = cubepi_metadata.tables["cubepi_messages"]
assert msgs.foreign_keys == set()
def test_messages_has_no_metadata_index() -> None:
from cubepi.checkpointer.mysql.models import cubepi_metadata
msgs = cubepi_metadata.tables["cubepi_messages"]
assert msgs.indexes == set()
- Step 2: Run tests to verify they fail
Run: uv run pytest tests/checkpointer/test_mysql.py -k "models_import or self_fk or foreign_keys or metadata_index" -v
Expected: FAIL — ModuleNotFoundError / attribute errors.
- Step 3: Write minimal implementation
# cubepi/checkpointer/mysql/models.py
"""SQLAlchemy table definitions for cubepi MySQLCheckpointer.
Mirrors the Postgres models with MySQL adaptations: VARCHAR(255) utf8mb4_bin
thread ids, JSON columns, no messages->threads FK (the messages table is
KEY-partitioned and MySQL forbids FKs on partitioned tables), self-FK on
parent_thread_id kept. KEY partitioning is NOT expressible in SQLAlchemy
declarative, so it lives only in alembic_helpers.messages_partition_clause().
"""
from __future__ import annotations
import datetime as _dt
from typing import Any
import sqlalchemy as sa
from sqlalchemy.dialects.mysql import JSON, LONGBLOB, VARCHAR
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
EXPECTED_SCHEMA_VERSION = 1
PARTITION_COUNT = 64
cubepi_metadata = sa.MetaData()
_TID = VARCHAR(255, collation="utf8mb4_bin")
class CubepiBase(DeclarativeBase):
metadata = cubepi_metadata
class CubepiThread(CubepiBase):
__tablename__ = "cubepi_threads"
__table_args__ = {"mysql_engine": "InnoDB"}
thread_id: Mapped[str] = mapped_column(_TID, primary_key=True)
parent_thread_id: Mapped[str | None] = mapped_column(
_TID,
sa.ForeignKey("cubepi_threads.thread_id"),
nullable=True,
)
forked_at_seq: Mapped[int | None] = mapped_column(sa.BigInteger, nullable=True)
extra: Mapped[dict[str, Any]] = mapped_column(
JSON,
nullable=False,
server_default=sa.text("(JSON_OBJECT())"),
)
created_at: Mapped[_dt.datetime] = mapped_column(
sa.TIMESTAMP,
nullable=False,
server_default=sa.text("CURRENT_TIMESTAMP"),
)
updated_at: Mapped[_dt.datetime] = mapped_column(
sa.TIMESTAMP,
nullable=False,
server_default=sa.text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"),
)
class CubepiMessage(CubepiBase):
__tablename__ = "cubepi_messages"
__table_args__ = {"mysql_engine": "InnoDB"}
thread_id: Mapped[str] = mapped_column(_TID, primary_key=True)
seq: Mapped[int] = mapped_column(sa.BigInteger, primary_key=True)
role: Mapped[str] = mapped_column(sa.String(32), nullable=False)
# Python attribute renamed to avoid DeclarativeBase's reserved `metadata`
# ClassVar; DB column stays `metadata`.
msg_metadata: Mapped[dict[str, Any]] = mapped_column(
"metadata",
JSON,
nullable=False,
server_default=sa.text("(JSON_OBJECT())"),
)
payload: Mapped[bytes] = mapped_column(LONGBLOB, nullable=False)
created_at: Mapped[_dt.datetime] = mapped_column(
sa.TIMESTAMP,
nullable=False,
server_default=sa.text("CURRENT_TIMESTAMP"),
)
class CubepiSchemaVersion(CubepiBase):
__tablename__ = "cubepi_schema_version"
__table_args__ = {"mysql_engine": "InnoDB"}
version: Mapped[int] = mapped_column(sa.Integer, primary_key=True)
- Step 4: Run tests to verify they pass
Run: uv run pytest tests/checkpointer/test_mysql.py -k "models_import or self_fk or foreign_keys or metadata_index" -v
Expected: PASS (4 tests)
- Step 5: Commit
git add cubepi/checkpointer/mysql/models.py tests/checkpointer/test_mysql.py
git commit -m "feat(checkpointer): mysql SQLAlchemy models"
Task 3: alembic helpers
Files:
Create:
cubepi/checkpointer/mysql/alembic_helpers.pyTest:
tests/checkpointer/test_mysql.pyStep 1: Write the failing tests
# append to tests/checkpointer/test_mysql.py
def test_messages_partition_clause() -> None:
from cubepi.checkpointer.mysql.alembic_helpers import messages_partition_clause
clause = messages_partition_clause()
assert clause == "PARTITION BY KEY (thread_id) PARTITIONS 64"
def test_write_schema_version_op_clears_stale_then_inserts() -> None:
from cubepi.checkpointer.mysql.alembic_helpers import write_schema_version_op
sql = write_schema_version_op()
assert "DELETE FROM cubepi_schema_version" in sql
assert "WHERE version <> 1" in sql
assert "INSERT IGNORE INTO cubepi_schema_version" in sql
assert "VALUES (1)" in sql
assert sql.index("DELETE") < sql.index("INSERT")
- Step 2: Run tests to verify they fail
Run: uv run pytest tests/checkpointer/test_mysql.py -k "partition_clause or schema_version_op" -v
Expected: FAIL — ModuleNotFoundError.
- Step 3: Write minimal implementation
# cubepi/checkpointer/mysql/alembic_helpers.py
"""SQL helpers for host application alembic migrations (MySQL)."""
from cubepi.checkpointer.mysql.models import (
EXPECTED_SCHEMA_VERSION,
PARTITION_COUNT,
)
def messages_partition_clause() -> str:
"""Return the KEY-partition clause for the cubepi_messages CREATE TABLE.
Unlike Postgres (one child partition per modulus), MySQL declares all
partitions inline. Append this to the messages table DDL, e.g.::
op.execute("CREATE TABLE cubepi_messages (...) " + messages_partition_clause())
KEY (not HASH) is required because thread_id is a VARCHAR; MySQL HASH
partitioning only accepts integer expressions.
"""
return f"PARTITION BY KEY (thread_id) PARTITIONS {PARTITION_COUNT}"
def write_schema_version_op() -> str:
"""Return SQL setting cubepi_schema_version to the current version.
Clears any stale rows from prior cubepi versions then inserts the current
one. Idempotent. Call inside alembic upgrade() after CREATE TABLE
cubepi_schema_version.
"""
return (
f"DELETE FROM cubepi_schema_version "
f"WHERE version <> {EXPECTED_SCHEMA_VERSION}; "
f"INSERT IGNORE INTO cubepi_schema_version (version) "
f"VALUES ({EXPECTED_SCHEMA_VERSION});"
)
- Step 4: Run tests to verify they pass
Run: uv run pytest tests/checkpointer/test_mysql.py -k "partition_clause or schema_version_op" -v
Expected: PASS
- Step 5: Commit
git add cubepi/checkpointer/mysql/alembic_helpers.py tests/checkpointer/test_mysql.py
git commit -m "feat(checkpointer): mysql alembic helpers"
Task 4: Checkpointer — helpers, DSN, role mapping, empty-append no-op
Files:
Create:
cubepi/checkpointer/mysql/checkpointer.pyTest:
tests/checkpointer/test_mysql.pyStep 1: Write the failing tests
# append to tests/checkpointer/test_mysql.py
def test_role_of_known_message_types() -> None:
from cubepi.checkpointer.mysql.checkpointer import _role_of
from cubepi.providers.base import (
AssistantMessage,
TextContent,
ToolResultMessage,
Usage,
UserMessage,
)
assert _role_of(UserMessage(content=[TextContent(text="x")])) == "user"
assert (
_role_of(AssistantMessage(content=[TextContent(text="x")], usage=Usage()))
== "assistant"
)
tr = ToolResultMessage(
tool_call_id="tc-1", tool_name="t", content=[TextContent(text="ok")]
)
assert _role_of(tr) == "tool"
def test_role_of_rejects_unknown_message_type() -> None:
from cubepi.checkpointer.mysql.checkpointer import _role_of
class FakeMessage:
pass
with pytest.raises(TypeError, match="unknown Message type"):
_role_of(FakeMessage()) # type: ignore[arg-type]
def test_parse_dsn_full() -> None:
from cubepi.checkpointer.mysql.checkpointer import _parse_dsn
cfg = _parse_dsn("mysql://user:[email protected]:3307/mydb")
assert cfg == {
"host": "db.example.com",
"port": 3307,
"user": "user",
"password": "pw",
"db": "mydb",
}
def test_parse_dsn_defaults_port_3306() -> None:
from cubepi.checkpointer.mysql.checkpointer import _parse_dsn
cfg = _parse_dsn("mysql://root@localhost/x")
assert cfg["port"] == 3306
assert cfg["password"] == ""
def test_decode_json_handles_str_and_dict() -> None:
from cubepi.checkpointer.mysql.checkpointer import _decode_json
assert _decode_json('{"a": 1}') == {"a": 1}
assert _decode_json({"a": 1}) == {"a": 1}
assert _decode_json(None) == {}
@pytest.mark.asyncio
async def test_append_empty_messages_is_noop() -> None:
# Import from the submodule, not the package: the package __init__ does not
# export MySQLCheckpointer until Task 5.
from cubepi.checkpointer.mysql.checkpointer import MySQLCheckpointer
cp = MySQLCheckpointer("mysql://root@unreachable-host/none")
assert cp._pool is None
await cp.append("thread-x", [])
assert cp._pool is None
- Step 2: Run tests to verify they fail
Run: uv run pytest tests/checkpointer/test_mysql.py -k "role_of or parse_dsn or decode_json or append_empty" -v
Expected: FAIL — module/attr missing.
- Step 3: Write minimal implementation
# cubepi/checkpointer/mysql/checkpointer.py
"""MySQLCheckpointer — Checkpointer protocol against MySQL.
Append-only message log + per-thread KV (extra). aiomysql pool + msgpack
payloads. Schema version verified on context entry. Mirrors
PostgresCheckpointer; see dev/specs/2026-05-27-mysql-checkpointer.md for the
list of deliberate MySQL divergences.
"""
from __future__ import annotations
import json
from typing import Any
from urllib.parse import unquote, urlparse
import aiomysql
import msgpack
import pymysql
from cubepi.checkpointer.base import CheckpointData
from cubepi.checkpointer.mysql.exceptions import (
CubepiSchemaMismatch,
CubepiSchemaUninitialized,
)
from cubepi.checkpointer.mysql.models import EXPECTED_SCHEMA_VERSION
from cubepi.providers.base import (
AssistantMessage,
Message,
ToolResultMessage,
UserMessage,
)
_ER_NO_SUCH_TABLE = 1146
_ER_BAD_FIELD_ERROR = 1054
def _role_of(msg: Message) -> str:
if isinstance(msg, UserMessage):
return "user"
if isinstance(msg, AssistantMessage):
return "assistant"
if isinstance(msg, ToolResultMessage):
return "tool"
raise TypeError(f"unknown Message type: {type(msg).__name__}")
_ROLE_TO_CLS: dict[str, type[Message]] = {
"user": UserMessage,
"assistant": AssistantMessage,
"tool": ToolResultMessage,
}
def _parse_dsn(dsn: str) -> dict[str, Any]:
"""Parse a mysql:// URL into aiomysql.create_pool kwargs."""
u = urlparse(dsn)
db = u.path.lstrip("/")
return {
"host": u.hostname or "localhost",
"port": u.port or 3306,
"user": unquote(u.username) if u.username else "",
"password": unquote(u.password) if u.password else "",
"db": db,
}
def _decode_json(value: Any) -> dict[str, Any]:
"""aiomysql returns JSON columns as str; tolerate already-parsed dicts."""
if value is None:
return {}
if isinstance(value, str):
return json.loads(value)
return value
class MySQLCheckpointer:
"""Checkpointer backed by MySQL (8.0.13+, InnoDB).
Usage:
cp = MySQLCheckpointer("mysql://user:pw@host:3306/db")
async with cp:
await cp.append(thread_id, [msg1, msg2])
data = await cp.load(thread_id)
await cp.save_extra(thread_id, {"k": "v"})
Raises CubepiSchemaUninitialized / CubepiSchemaMismatch at __aenter__ if the
DB schema isn't compatible with this cubepi version.
"""
def __init__(
self,
dsn: str,
*,
min_pool_size: int = 1,
max_pool_size: int = 10,
) -> None:
self._cfg = _parse_dsn(dsn)
self._min = min_pool_size
self._max = max_pool_size
self._pool: aiomysql.Pool | None = None
async def append(self, thread_id: str, messages: list[Message]) -> None:
if not messages:
return
raise NotImplementedError # completed in Task 6
- Step 4: Run tests to verify they pass
Run: uv run pytest tests/checkpointer/test_mysql.py -k "role_of or parse_dsn or decode_json or append_empty" -v
Expected: PASS
- Step 5: Commit
git add cubepi/checkpointer/mysql/checkpointer.py tests/checkpointer/test_mysql.py
git commit -m "feat(checkpointer): mysql checkpointer helpers + dsn parsing"
Task 5: Pool lifecycle + schema verification + package wiring
Files:
- Modify:
cubepi/checkpointer/mysql/checkpointer.py - Modify:
cubepi/checkpointer/mysql/__init__.py - Modify:
cubepi/checkpointer/__init__.py - Test:
tests/checkpointer/test_mysql.py
(The mysql extra and dev dep were already added in Task 1 Step 0.)
- Step 1: Complete the mysql package
__init__.py
Now that all submodules exist, replace cubepi/checkpointer/mysql/__init__.py
with the full export surface:
# cubepi/checkpointer/mysql/__init__.py
from cubepi.checkpointer.mysql.checkpointer import MySQLCheckpointer
from cubepi.checkpointer.mysql.exceptions import (
CubepiSchemaError,
CubepiSchemaMismatch,
CubepiSchemaUninitialized,
)
from cubepi.checkpointer.mysql.models import (
EXPECTED_SCHEMA_VERSION,
PARTITION_COUNT,
cubepi_metadata,
)
__all__ = [
"MySQLCheckpointer",
"CubepiSchemaError",
"CubepiSchemaMismatch",
"CubepiSchemaUninitialized",
"EXPECTED_SCHEMA_VERSION",
"PARTITION_COUNT",
"cubepi_metadata",
]
- Step 2: Wire lazy import in the checkpointer package
Edit cubepi/checkpointer/__init__.py __getattr__ to add, before the final
raise AttributeError:
if name == "MySQLCheckpointer":
from cubepi.checkpointer.mysql.checkpointer import MySQLCheckpointer
return MySQLCheckpointer
And add "MySQLCheckpointer" to __all__.
- Step 3: Write the test for the top-level lazy import
# append to tests/checkpointer/test_mysql.py
def test_top_level_lazy_import() -> None:
import cubepi.checkpointer as cp_pkg
assert cp_pkg.MySQLCheckpointer is not None
assert "MySQLCheckpointer" in cp_pkg.__all__
- Step 4: Run it (verifies the wiring; the class exists from Task 4)
Run: uv run pytest tests/checkpointer/test_mysql.py::test_top_level_lazy_import -v
Expected: PASS. A failure here means an import error in the wiring — fix it
before continuing. (This step verifies wiring rather than following strict
red→green, because the class already exists from Task 4.)
- Step 5: Implement
__aenter__/__aexit__/_verify_schema
Add these methods to MySQLCheckpointer (after __init__, before append):
async def __aenter__(self) -> "MySQLCheckpointer":
self._pool = await aiomysql.create_pool(
minsize=self._min,
maxsize=self._max,
autocommit=True,
**self._cfg,
)
await self._verify_schema()
return self
async def __aexit__(self, *args: Any) -> None:
if self._pool is not None:
self._pool.close()
await self._pool.wait_closed()
self._pool = None
async def _verify_schema(self) -> None:
assert self._pool is not None
async with self._pool.acquire() as conn:
async with conn.cursor() as cur:
try:
await cur.execute(
"SELECT version FROM cubepi_schema_version LIMIT 1"
)
row = await cur.fetchone()
except pymysql.err.Error as e:
# PyMySQL maps 1146 (ER_NO_SUCH_TABLE) to ProgrammingError but
# 1054 (ER_BAD_FIELD_ERROR) to OperationalError, so catch the
# common base and branch on the errno.
code = e.args[0] if e.args else None
if code in (_ER_NO_SUCH_TABLE, _ER_BAD_FIELD_ERROR):
raise CubepiSchemaUninitialized(
"cubepi tables not found or malformed. Run host "
"application's alembic upgrade."
) from e
raise
if row is None:
raise CubepiSchemaUninitialized(
"cubepi_schema_version table is empty. Host alembic migration "
"must INSERT the current version (use write_schema_version_op())."
)
actual = row[0]
if actual != EXPECTED_SCHEMA_VERSION:
raise CubepiSchemaMismatch(
expected=EXPECTED_SCHEMA_VERSION,
actual=actual,
hint="cubepi was upgraded but host alembic is behind. "
"Generate a new revision and apply.",
)
- Step 6: Run the full unit-test subset to verify nothing broke
Run: uv run pytest tests/checkpointer/test_mysql.py -v
Expected: PASS for all non-E2E tests (E2E tests not written yet).
- Step 7: Commit
git add cubepi/checkpointer/__init__.py cubepi/checkpointer/mysql/__init__.py cubepi/checkpointer/mysql/checkpointer.py tests/checkpointer/test_mysql.py
git commit -m "feat(checkpointer): mysql pool lifecycle + schema verification + package wiring"
Task 6: load / append / save_extra
Files:
- Modify:
cubepi/checkpointer/mysql/checkpointer.py - Test:
tests/checkpointer/test_mysql.py(E2E, added in Task 7)
This task has no standalone unit test (the logic needs a DB; E2E tests in Task 7 cover it). Implement, then run the existing unit subset to confirm no import or syntax regressions.
- Step 1: Implement
load
Replace the append stub region by first adding load above it:
async def load(self, thread_id: str) -> CheckpointData | None:
assert self._pool is not None
async with self._pool.acquire() as conn:
async with conn.cursor() as cur:
await cur.execute(
"SELECT seq, role, metadata, payload FROM cubepi_messages "
"WHERE thread_id = %s ORDER BY seq",
(thread_id,),
)
msg_rows = await cur.fetchall()
await cur.execute(
"SELECT extra FROM cubepi_threads WHERE thread_id = %s",
(thread_id,),
)
extra_row = await cur.fetchone()
if not msg_rows and extra_row is None:
return None
messages: list[Message] = []
for _seq, role, metadata, payload in msg_rows:
cls = _ROLE_TO_CLS.get(role)
if cls is None:
raise ValueError(f"unknown role in DB: {role!r}")
data = msgpack.unpackb(bytes(payload), raw=False)
data["metadata"] = _decode_json(metadata)
messages.append(cls.model_validate(data))
extra = _decode_json(extra_row[0]) if extra_row is not None else {}
return CheckpointData(messages=messages, extra=extra)
- Step 2: Implement
append(replace theNotImplementedErrorstub)
async def append(self, thread_id: str, messages: list[Message]) -> None:
if not messages:
return
assert self._pool is not None
async with self._pool.acquire() as conn:
await conn.begin()
try:
async with conn.cursor() as cur:
await cur.execute(
"INSERT IGNORE INTO cubepi_threads (thread_id) "
"VALUES (%s)",
(thread_id,),
)
await cur.execute(
"SELECT thread_id FROM cubepi_threads "
"WHERE thread_id = %s FOR UPDATE",
(thread_id,),
)
await cur.execute(
"SELECT COALESCE(MAX(seq), 0) FROM cubepi_messages "
"WHERE thread_id = %s",
(thread_id,),
)
(last_seq,) = await cur.fetchone()
rows = []
for i, m in enumerate(messages):
seq = last_seq + i + 1
payload = msgpack.packb(
m.model_dump(mode="json"), use_bin_type=True
)
rows.append(
(
thread_id,
seq,
_role_of(m),
json.dumps(m.metadata),
payload,
)
)
await cur.executemany(
"INSERT INTO cubepi_messages "
"(thread_id, seq, role, metadata, payload) "
"VALUES (%s, %s, %s, %s, %s)",
rows,
)
await conn.commit()
except BaseException:
await conn.rollback()
raise
- Step 3: Implement
save_extra(read-modify-write shallow merge)
async def save_extra(self, thread_id: str, extra: dict[str, Any]) -> None:
assert self._pool is not None
async with self._pool.acquire() as conn:
await conn.begin()
try:
async with conn.cursor() as cur:
await cur.execute(
"INSERT IGNORE INTO cubepi_threads (thread_id) "
"VALUES (%s)",
(thread_id,),
)
await cur.execute(
"SELECT extra FROM cubepi_threads "
"WHERE thread_id = %s FOR UPDATE",
(thread_id,),
)
row = await cur.fetchone()
current = _decode_json(row[0]) if row is not None else {}
merged = {**current, **extra}
await cur.execute(
"UPDATE cubepi_threads "
"SET extra = %s, updated_at = CURRENT_TIMESTAMP "
"WHERE thread_id = %s",
(json.dumps(merged), thread_id),
)
await conn.commit()
except BaseException:
await conn.rollback()
raise
- Step 4: Run the unit subset to verify no regressions
Run: uv run pytest tests/checkpointer/test_mysql.py -v
Expected: PASS (unit tests; E2E added next task).
Also: uv run ruff check cubepi/ tests/ and uv run ruff format --check cubepi/ tests/
Expected: clean.
- Step 5: Commit
git add cubepi/checkpointer/mysql/checkpointer.py
git commit -m "feat(checkpointer): mysql load/append/save_extra"
Task 7: E2E tests + conftest fixtures
Files:
- Modify:
tests/checkpointer/conftest.py - Modify:
tests/checkpointer/test_mysql.py
E2E tests skip automatically when no MySQL is reachable (mirrors the Postgres
ones). They run when CUBEPI_TEST_MYSQL_DSN points at a live server.
- Step 1: Add MySQL fixtures to conftest
Append to tests/checkpointer/conftest.py:
import aiomysql # add near the asyncpg import at top of file
@pytest.fixture(scope="session")
def mysql_dsn() -> str:
"""MySQL DSN for tests. Override via CUBEPI_TEST_MYSQL_DSN env."""
return os.environ.get(
"CUBEPI_TEST_MYSQL_DSN",
"mysql://root:root@localhost:3306/mysql",
)
def _mysql_cfg(dsn: str) -> dict:
from cubepi.checkpointer.mysql.checkpointer import _parse_dsn
return _parse_dsn(dsn)
@pytest_asyncio.fixture(scope="session")
async def _mysql_available(mysql_dsn: str) -> bool:
"""Quick probe — connection refused → skip MySQL tests."""
try:
conn = await aiomysql.connect(connect_timeout=2, **_mysql_cfg(mysql_dsn))
conn.close()
return True
except (aiomysql.Error, OSError, ConnectionError):
return False
@pytest_asyncio.fixture
async def clean_mysql_db(mysql_dsn: str, _mysql_available: bool):
"""Create a fresh database for each test; drop after."""
if not _mysql_available:
pytest.skip(
"MySQL not available for E2E tests; set CUBEPI_TEST_MYSQL_DSN "
"to a working DSN to enable."
)
db_name = f"cubepi_test_{secrets.token_hex(6)}"
admin_cfg = _mysql_cfg(mysql_dsn)
admin = await aiomysql.connect(autocommit=True, **admin_cfg)
try:
async with admin.cursor() as cur:
await cur.execute(f"CREATE DATABASE `{db_name}`")
finally:
admin.close()
base = mysql_dsn.rsplit("/", 1)[0]
test_dsn = f"{base}/{db_name}"
yield test_dsn
admin = await aiomysql.connect(autocommit=True, **admin_cfg)
try:
async with admin.cursor() as cur:
await cur.execute(f"DROP DATABASE IF EXISTS `{db_name}`")
finally:
admin.close()
(os, secrets, pytest, pytest_asyncio are already imported in this file.)
- Step 2: Add
_setup_schema+ E2E tests to test_mysql.py
# append to tests/checkpointer/test_mysql.py
import aiomysql
from cubepi.checkpointer.mysql.alembic_helpers import (
messages_partition_clause,
write_schema_version_op,
)
async def _setup_schema(dsn: str) -> None:
"""Build the cubepi schema (matching what host alembic would generate)."""
from cubepi.checkpointer.mysql.checkpointer import _parse_dsn
conn = await aiomysql.connect(autocommit=True, **_parse_dsn(dsn))
try:
async with conn.cursor() as cur:
await cur.execute("""
CREATE TABLE cubepi_threads (
thread_id VARCHAR(255) COLLATE utf8mb4_bin PRIMARY KEY,
parent_thread_id VARCHAR(255) COLLATE utf8mb4_bin NULL,
forked_at_seq BIGINT NULL,
extra JSON NOT NULL DEFAULT (JSON_OBJECT()),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT fk_parent FOREIGN KEY (parent_thread_id)
REFERENCES cubepi_threads (thread_id)
) ENGINE=InnoDB
""")
await cur.execute(
"""
CREATE TABLE cubepi_messages (
thread_id VARCHAR(255) COLLATE utf8mb4_bin NOT NULL,
seq BIGINT NOT NULL,
role VARCHAR(32) NOT NULL,
metadata JSON NOT NULL DEFAULT (JSON_OBJECT()),
payload LONGBLOB NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (thread_id, seq)
) ENGINE=InnoDB """
+ messages_partition_clause()
)
await cur.execute("""
CREATE TABLE cubepi_schema_version (
version INT PRIMARY KEY
) ENGINE=InnoDB
""")
for stmt in write_schema_version_op().split(";"):
if stmt.strip():
await cur.execute(stmt)
finally:
conn.close()
@pytest.mark.asyncio
async def test_mysql_checkpointer_round_trip(clean_mysql_db) -> None:
from cubepi.checkpointer.mysql import MySQLCheckpointer
from cubepi.providers.base import (
AssistantMessage,
TextContent,
Usage,
UserMessage,
)
await _setup_schema(clean_mysql_db)
async with MySQLCheckpointer(clean_mysql_db) as cp:
msg1 = UserMessage(
content=[TextContent(text="hello")],
metadata={"memory_snapshot": {"id": "m1"}},
)
msg2 = AssistantMessage(
content=[TextContent(text="hi back")],
usage=Usage(),
metadata={"cost_cents": 5},
)
await cp.append("t-1", [msg1, msg2])
data = await cp.load("t-1")
assert data is not None
assert len(data.messages) == 2
assert isinstance(data.messages[0], UserMessage)
assert isinstance(data.messages[1], AssistantMessage)
assert data.messages[0].metadata == {"memory_snapshot": {"id": "m1"}}
assert data.messages[1].metadata == {"cost_cents": 5}
assert data.messages[0].content[0].text == "hello"
assert data.messages[1].content[0].text == "hi back"
@pytest.mark.asyncio
async def test_mysql_checkpointer_save_extra_merges(clean_mysql_db) -> None:
from cubepi.checkpointer.mysql import MySQLCheckpointer
from cubepi.providers.base import TextContent, UserMessage
await _setup_schema(clean_mysql_db)
async with MySQLCheckpointer(clean_mysql_db) as cp:
await cp.append("t-2", [UserMessage(content=[TextContent(text="x")])])
await cp.save_extra("t-2", {"a": 1})
await cp.save_extra("t-2", {"b": 2})
data = await cp.load("t-2")
assert data is not None
assert data.extra == {"a": 1, "b": 2}
@pytest.mark.asyncio
async def test_mysql_checkpointer_seq_monotonic(clean_mysql_db) -> None:
from cubepi.checkpointer.mysql import MySQLCheckpointer
from cubepi.providers.base import TextContent, UserMessage
await _setup_schema(clean_mysql_db)
async with MySQLCheckpointer(clean_mysql_db) as cp:
msgs1 = [UserMessage(content=[TextContent(text=str(i))]) for i in range(5)]
await cp.append("t-3", msgs1)
msgs2 = [
UserMessage(content=[TextContent(text=str(i))]) for i in range(5, 10)
]
await cp.append("t-3", msgs2)
data = await cp.load("t-3")
assert data is not None
assert len(data.messages) == 10
texts = [m.content[0].text for m in data.messages]
assert texts == [str(i) for i in range(10)]
@pytest.mark.asyncio
async def test_uninitialized_schema_raises(clean_mysql_db) -> None:
from cubepi.checkpointer.mysql import (
CubepiSchemaUninitialized,
MySQLCheckpointer,
)
with pytest.raises(CubepiSchemaUninitialized):
async with MySQLCheckpointer(clean_mysql_db):
pass
@pytest.mark.asyncio
async def test_version_mismatch_raises(clean_mysql_db) -> None:
from cubepi.checkpointer.mysql import CubepiSchemaMismatch, MySQLCheckpointer
from cubepi.checkpointer.mysql.checkpointer import _parse_dsn
await _setup_schema(clean_mysql_db)
conn = await aiomysql.connect(autocommit=True, **_parse_dsn(clean_mysql_db))
try:
async with conn.cursor() as cur:
await cur.execute("UPDATE cubepi_schema_version SET version = 999")
finally:
conn.close()
with pytest.raises(CubepiSchemaMismatch) as exc_info:
async with MySQLCheckpointer(clean_mysql_db):
pass
assert exc_info.value.expected == 1
assert exc_info.value.actual == 999
@pytest.mark.asyncio
async def test_empty_thread_load_returns_none(clean_mysql_db) -> None:
from cubepi.checkpointer.mysql import MySQLCheckpointer
await _setup_schema(clean_mysql_db)
async with MySQLCheckpointer(clean_mysql_db) as cp:
data = await cp.load("nonexistent-thread")
assert data is None
- Step 3: Run the full test file
Run: uv run pytest tests/checkpointer/test_mysql.py -v
Expected: unit tests PASS; E2E tests PASS if CUBEPI_TEST_MYSQL_DSN is set to a
live 8.0.13+ server, otherwise SKIPPED. Run with a real DB before considering
the feature done:
CUBEPI_TEST_MYSQL_DSN=mysql://root:root@localhost:3306/mysql uv run pytest tests/checkpointer/test_mysql.py -v
- Step 4: Lint
Run: uv run ruff check cubepi/ tests/ && uv run ruff format --check cubepi/ tests/
Expected: clean.
- Step 5: Commit
git add tests/checkpointer/conftest.py tests/checkpointer/test_mysql.py
git commit -m "test(checkpointer): mysql unit + E2E tests"
Task 8: Documentation
Files:
Modify: the checkpointer docs page under
website/docs/(locate the page that documentsPostgresCheckpointer; add a MySQL section there). If Postgres is documented in e.g.website/docs/guides/checkpointing.mdor similar, edit that file. Usegrep -rl PostgresCheckpointer website/docsto find it.Step 1: Locate the existing checkpointer doc
Run: grep -rl "PostgresCheckpointer" website/docs
Use the returned path as the doc to extend.
- Step 2: Add a MySQL section
Mirror the Postgres section. Content must cover:
- Install:
pip install "cubepi[mysql]"(driver: aiomysql). - Minimum MySQL 8.0.13+, InnoDB engine.
- Host-managed schema: cubepi ships SQLAlchemy models
(
cubepi.checkpointer.mysql.models.cubepi_metadata) and alembic helpers (messages_partition_clause(),write_schema_version_op()). The host's alembic migration creates the tables; the checkpointer verifies the schema version on connect and raisesCubepiSchemaUninitialized/CubepiSchemaMismatch. - Usage example:
from cubepi.checkpointer import MySQLCheckpointer
cp = MySQLCheckpointer("mysql://user:pw@localhost:3306/mydb")
async with cp:
await cp.append(thread_id, [msg])
data = await cp.load(thread_id)
Divergences worth flagging to users: messages table is
KEY(thread_id)partitioned with no FK (integrity from lazy thread insert);metadataJSON column is not indexed (add a generated-column index host-side if you need to query by metadata); thread IDs are case-sensitive (utf8mb4_bin).Step 3: Build/verify docs (if a build step exists)
Run (if present): grep -q "\"build\"" website/package.json && (cd website && npm run build) — otherwise skip; verify the markdown renders by eye.
- Step 4: Commit
git add website/docs
git commit -m "docs: document MySQLCheckpointer"
Final verification before PR
-
uv run pytest tests/ -v(whole suite; MySQL E2E skipped without DB). - With a live MySQL 8.0.13+:
CUBEPI_TEST_MYSQL_DSN=... uv run pytest tests/checkpointer/test_mysql.py -v— all E2E pass. -
uv run ruff check cubepi/ tests/clean. -
uv run ruff format --check cubepi/ tests/clean. - Spec divergences all reflected in code.
- Docs page updated.