feat(memory): add MySQL checkpoint backend

[luigisaetta]

Summary

Add a simple MySQL-backed checkpoint backend for Locus agent state persistence.

Locus currently supports several checkpoint backends, including in-memory, file, HTTP, Redis, PostgreSQL, OpenSearch, OCI Object Storage, and Oracle Database. MySQL does not appear to be supported yet.

This issue proposes adding a focused MySQL-only checkpoint backend.

Motivation

MySQL is easy to run in Docker Compose based deployments. For local, self-hosted, or small production setups, a Compose stack with an app service and a MySQL service is a common and simple operational model.

A MySQL checkpointer would let users persist Locus agent threads in that setup without adding Redis, PostgreSQL, Oracle Database, OCI Object Storage, or a separate checkpoint service.

Proposed Scope

Add a new MySQL storage backend following the existing storage-backend-plus-adapter pattern used by the current SQL-style backends.

The initial implementation should include:

• MySQLBackend
• MySQLConfig
• mysql_checkpointer(...) convenience factory
• Registry support for get_checkpointer("mysql")
• Public exports from locus.memory.backends
• Optional dependency extra, for example locus-sdk[mysql]
• Unit tests
• Basic integration test coverage gated behind a MySQL-specific marker
• Documentation updates for the checkpoint backend list and usage example

This proposal is explicitly scoped to MySQL, not MariaDB.

Expected Behavior

The backend should support the basic checkpoint operations already expected by Locus:

• save and load checkpoint state
• delete checkpoints
• check whether a thread exists
• list stored threads
• store and retrieve metadata
• query by metadata
• remove old checkpoints through vacuum-style cleanup

The first version does not need advanced features such as TTL support, full-text search, custom migrations, or broader MySQL-compatible database support.

Testing and Documentation

A future PR should include unit tests, gated integration tests, and documentation updates.

Before opening the PR, the usual project checks should pass:

hatch run test
hatch run lint
hatch run format-check

[luigisaetta]

@fede-kamel I'd welcome your comments.

[fede-kamel]

Hey @luigisaetta — really like this proposal. A MySQL checkpoint backend genuinely fills a gap for the Docker-Compose-style deployments you describe, and the scope you've drawn is exactly the right shape for a first PR. A few thoughts on how to slot it in cleanly and where it could go next.

Pattern fit

The existing SQL-ish backends already give you a template — src/locus/memory/backends/postgresql.py is the closest analogue:

• PostgreSQLConfig(BaseModel) with SecretStr for the password, explicit _validate_sql_identifier() for table_name / schema_name, pool size knobs, and an optional dsn: override.
• PostgreSQLBackend(BaseModel) implementing the checkpoint contract.
• postgresql_checkpointer(...) convenience factory exported from locus.memory.backends.__init__ and registered for get_checkpointer("postgresql").

Mirroring that 1:1 (MySQLConfig / MySQLBackend / mysql_checkpointer / get_checkpointer("mysql")) keeps the surface area predictable for users who already know one of the SQL backends.

Optional dependency

Yes, this should be optional — same pattern as the other backends in pyproject.toml:

[project.optional-dependencies]
redis = ["redis>=5.0"]
postgresql = ["asyncpg>=0.29"]
oracledb = ["oracledb>=2.2.0"]
mysql = ["asyncmy>=0.2.10"]   # proposed

Import inside the backend should be guarded so the core install stays driver-free; mirror what postgresql.py does with asyncpg under TYPE_CHECKING.

On the driver: I'd suggest asyncmy over aiomysql for a new backend. Compatible API, Cython-accelerated protocol, more active upstream — SQLAlchemy's own docs note aiomysql has been effectively unmaintained since 2021. mysql-connector-python is the Oracle-official one and a fine alternative if you want vendor-supported drivers; the trade-off is performance.

Where MySQL AI features could plug in later

Worth noting (and explicitly out of scope for this PR): MySQL's AI surface is moving fast, and locus could integrate it in a second step.

• MySQL 9.0 community introduces a VECTOR storage type (docs) — but in plain community MySQL there's no DISTANCE() function and no vector index, so ANN search is full-scan + app-side distance. Not a credible vector store yet on community-only.
• HeatWave GenAI ships an in-database vector store with automatic HNSW index creation on frequently-queried VECTOR columns (HeatWave 9.5.0, GA October 2025 — release notes), plus in-database LLMs, automated embedding generation, RAG, and natural-language query via HeatWave Chat (product page).

That suggests a clean two-step path:

1. This issue / PR — a tight MySQL checkpoint backend on community MySQL, durable agent threads via JSON/LONGBLOB. Doesn't depend on any AI feature.
2. Follow-up issue — a HeatWaveVectorStore retriever that mirrors OracleVectorStore, targeting HeatWave's vector store + auto HNSW. That's a natural complement for users running on MySQL HeatWave and would give locus parity for "AI on MySQL" without bundling it into the checkpoint backend.

There's also no first-party MySQL + agent-framework package out there today (Oracle's own langchain-oracle is Oracle Database / OCI GenAI only), so locus would be filling real whitespace.

Suggested test split

• Unit tests: in-memory / mocked, run by default.
• Integration tests: gated behind a mysql pytest marker, opt-in via env var; cover save/load, list, delete, vacuum, and concurrent writes.
• A small docker-compose.yml snippet under tests/integration/ to make the integration suite one-command runnable.

Happy to review the PR when you open it. Thanks for putting this together — it's a useful contribution.

[luigisaetta]

Hi @fede-kamel
Checked and updated.

I have decided to use the mysql-connector-python.

The MySQL backend keeps the core install driver-free: mysql-connector-python is only declared under the mysql optional extra, and the backend mirrors exactly the PostgreSQL pattern by importing MySQL types under TYPE_CHECKING and lazy-importing the runtime driver only when opening a connection.

I also updated the test split as suggested: unit tests remain mocked and run by default, while MySQL integration tests are now gated behind requires_mysql plus explicit LOCUS_MYSQL_INTEGRATION=1.
The integration coverage now includes save/load, list, delete, vacuum, and concurrent writes, and I added a small tests/integration/docker-compose.mysql.yml so the MySQL integration suite can be run locally with one compose command.

I'll do more integration tests and then submit the PR, hopefully for Monday.

4:10 PM