Schema provisioning runs from every process's boot path: create_all races on cold boot and never evolves a table, Alembic runs destructive migrations on first use #1570

What happened

Every SQLAlchemy-backed store provisions its own schema inside startup(), so every server process runs DDL, at a time it does not control, with nothing arbitrating between processes. Three patterns coexist (paths under packages/server/src/memmachine_server, line numbers as of 231ce17).

1. metadata.create_all in startup(), eight sites, unguarded.

• common/episode_store/episode_sqlalchemy_store.py:170
• common/session_manager/session_data_manager_sql_impl.py:144
• common/vector_store/sqlite_vector_store.py:718
• common/vector_store/sqlite_vec_vector_store.py:491
• episodic_memory/event_memory/segment_store/sqlalchemy_segment_store.py:793
• semantic_memory/cluster_store/cluster_store_sqlalchemy.py:101
• semantic_memory/config_store/config_store_sqlalchemy.py:238
• semantic_memory/storage/vector_store_semantic_storage.py:196

create_all is check-then-create: it reflects, then emits CREATE TABLE without IF NOT EXISTS. Two processes booting against a cold database both pass the check and the loser's boot dies. #1174 was this race surfacing on the episode store's enum type (pg_type_typname_nsp_index); it was closed with a per-site guard (episode_sqlalchemy_store.py:155-168, savepoint plus catch), which is right for that one object and absent everywhere else. PostgreSQL's own IF NOT EXISTS is not race-free either (the catalog insert races outside the existence check), so the catch is load-bearing rather than belt-and-braces.

The quieter half: create_all(checkfirst=True) tests table existence only. A table that exists is skipped whole, columns and indexes included. So a column or index added to a model is never applied to an existing database, and the failure arrives later as UndefinedColumn from a query rather than at boot. Probe (SQLite, serialized, no race involved): create a table as (id, x), evolve the model to (id, x, y) plus Index("ix_y"), run create_all again: columns ['id', 'x'], indexes [], SELECT y fails with no such column. None of the eight sites has any other way to change an existing table. The segment store's tables carry named indexes, which is exactly the kind of thing that gets tuned over time.

2. Alembic upgrade head in startup(), on the lazy first-use path.

apply_alembic_migrations (semantic_memory/storage/sqlalchemy_pgvector_semantic.py:172-191) runs command.upgrade(config, "head") in-process. It is called from SqlAlchemyPgVectorSemanticStorage.startup() (:209), which SemanticManager.get_semantic_storage() calls on first use (common/resource_manager/semantic_manager.py:83-99), and it is preceded by an unguarded CREATE EXTENSION IF NOT EXISTS vector (:178). The chain is not additive: op.drop_table("set_ingested_history") (3d6aaebdc526:284), op.drop_column (62dff1150a46:37), and 37 op.alter_column calls across three revisions.

• The race exists here too. env.py reuses the caller's connection (run_migrations_online, env.py:103-108), so the whole chain runs inside one engine.begin() and a losing process rolls back cleanly. But the loser does not fail fast: it blocks behind the winner's ACCESS EXCLUSIVE locks for the migration's duration, which under a readiness or liveness probe becomes a timeout and a restart loop rather than an error.
• The schema moves forward the moment any process running newer code touches semantic memory, for every process, including ones still on the old code and still querying the dropped table. Rolling the code back does not roll the schema back.
• It fires on a warm server carrying traffic, not at deploy time, so DROP TABLE and type-rewriting ALTERs take ACCESS EXCLUSIVE under load.

3. Hand-rolled inspect-then-ALTER in the session manager.

create_tables() (session_data_manager_sql_impl.py:117-145) inspects the sessions table for a pickle-typed param_data column and a missing status column, then runs ALTER TABLE ... RENAME COLUMN / ADD COLUMN / DROP COLUMN plus a row-by-row backfill (:154-212) before create_all. Two processes inspecting concurrently both see the column missing; the loser's ADD COLUMN fails. It is also a third migration mechanism living beside Alembic and create_all, with its own version detection by column-type sniffing.

The only DDL path that was ever made race-free is the segment store's per-session partition creation (LOCK TABLE plus row insert plus DDL in one transaction), and #1545 removes that DDL altogether by moving to shared tables. #1545 also ships a schema layout change with no migration, because there is no infrastructure to carry one. The collection registry in #1524's stack kept bare create_all deliberately, to match the eight sites rather than add a ninth private guard; the design/schema_creation.md note in #1526 records that decision and the conditions under which crash-and-restart convergence is acceptable for boot-path DDL.

Why it matters now

Running several server processes against one database is the goal of the registry and segment store work (#1524, #1545, #1549). Those changes turn per-session metadata into row writes, so the only schema left to create is the fixed set created at boot. That is exactly the set these three patterns provision, and no cross-process serialization exists on any of them.

Crash-and-restart convergence, the argument that makes pattern 1 survivable, holds only under three conditions: each create_all emits a single statement or statements with no ordering dependency (a partially applied multi-statement schema is never repaired, because the restart skips the whole table); DDL is transactional (true on PostgreSQL and SQLite); and the table set is fixed by configuration rather than traffic. It does not cover pattern 2 (destructive and version-coupled) or pattern 3, and it never covers evolution.

Expected

• Exactly one actor runs DDL against a given database, serialized.
• A serving process never emits DDL. It verifies that the schema it needs is present and at the version it understands, and fails fast with a message naming the command to run.
• Schema evolution has one mechanism, and traffic does not trigger it.
• The quickstart stays zero-step: a single process against SQLite or a fresh PostgreSQL still auto-provisions.

Suggested direction

Recorded from the discussion that deferred this out of the registry stack; none of it is decided.

1. One chokepoint for DDL. A single module owning ensure_schema(engine, metadata, *, component, mode) and, for the irreducible dynamic cases, ensure_dynamic_object(connection, statement, *, lock_key). Internally pg_advisory_xact_lock on PostgreSQL and BEGIN IMMEDIATE plus busy_timeout on SQLite, then the DDL, then verification. A test that fails when create_all(, command.upgrade( or a raw CREATE TABLE appears outside that module is what actually retires the bug class; everything else is "remember to do it right", which eight unguarded sites show does not hold.
2. Split the lifecycle on the storage ABCs. provision_schema() (DDL, idempotent, may be skipped) next to startup() (open resources, verify schema, never DDL). Verify mode raises a typed error naming the fix. Today there is no way to boot a store without it doing DDL, which is what makes "migrations as a deploy step" impossible rather than merely unconfigured.
3. A per-database switch, schema_management: apply | verify | none, default apply (the shape Chroma uses). Because of (1), apply is concurrency-safe, so the default costs beginners nothing. Multi-process deployments set verify and run provisioning once, from an init step, a job, or a deploy stage.
4. A console script, memmachine-db provision | verify | current, idempotent and safe to run concurrently (it takes the same lock). Table names that come from configuration, such as per-store registries, are provisioned by reading the config, which a static Alembic script cannot express on its own.
5. Converge the eight create_all sites and the session manager's hand-rolled migration onto Alembic, one version table (or branch) per component, so evolution is expressed once. The chain under alembic_pg already does this correctly when serialized; it is wired to the wrong trigger.
6. Centralize engine construction (dialect validation, SQLite pragmas including busy_timeout, which is not set today). This is a precondition for the SQLite half of (1), and it deduplicates the three copies of the engine validator, which share a gap: the ephemeral-SQLite check tests db is None or db == ":memory:", but sqlite+aiosqlite:/// parses to database == "" and passes, giving every pooled connection its own private temporary database. That one is a plain bug rather than a race, and no restart converges out of it.

Scope for a first cut: serialized provisioning plus verify-at-boot. Zero-downtime rolling upgrades (expand/contract migrations, old code tolerating a newer schema) are a later layer on the same mechanism, not a requirement of this issue.

Notes

• Deliberately deferred from the collection registry stack rather than fixed there: it changes how the server is deployed, it touches all eight boot paths plus the in-process Alembic call, and the operator-facing story (what runs the migration, what happens on rollback) has to be decided together with the code. Doing it store by store would leave the other sites on a different contract and make "SQLAlchemy store, startup(), create_all" mean two things.
• [Bug]: UniqueViolationError – duplicate key value violates unique constraint "pg_type_typname_nsp_index" after local Docker build #1174 is the same race observed in the field, closed with a per-site guard.
• Overhaul segment store: shared tables with incarnation-scoped tenant keys (fixes #1544, #1546, #1549) #1545 is the first schema layout change shipped without a migration; this is the infrastructure that would carry the next one.