DB migrations and schema changes (letta-cloud core)

Use this skill whenever you need to change the database schema or debug Alembic migrations in apps/core of the letta-cloud repo.

This skill assumes:

• Working directory: apps/core
• Migrations: Alembic in apps/core/alembic
• Python runner: uv
• Helper: just ready for environment + DB setup

Quick start

1. Ensure environment is ready:
   • just ready
2. For Postgres migrations, set:
   • export LETTA_PG_URI=postgresql+pg8000://postgres:postgres@localhost:5432/letta-core
3. Make your ORM/schema change.
4. Autogenerate migration:
   • uv run alembic revision --autogenerate -m "<short_message>"
5. Apply migration:
   • uv run alembic upgrade head

See references/migration-commands.md for exact commands and variants.

Standard workflows

1. Add or modify a column (ORM-first)

1. Identify the ORM model and table.
2. Update the SQLAlchemy model in letta/orm/...:
   • Prefer using mixins (e.g. ProjectMixin) when available instead of duplicating columns.
3. Run just ready if dependencies or environment may have changed.
4. Ensure LETTA_PG_URI is set if you want the migration to target Postgres.
5. Autogenerate Alembic revision with uv.
6. Inspect the generated file under alembic/versions/:
   • Confirm op.add_column / op.alter_column match expectations.
7. Apply migrations with uv run alembic upgrade head.

Use this pattern for changes like adding project_id columns via ProjectMixin.

2. Data backfill / one-off data migration

1. Make sure the schema change (if any) is already represented in ORM + Alembic.
2. Create a new Alembic revision without autogenerate (or edit an autogen file) and add Python logic in upgrade() that:
   • Uses op.get_bind() and SQLAlchemy Core/SQL to backfill data.
3. Keep downgrade() simple and safe (ideally reversible).
4. Run against Postgres with LETTA_PG_URI set, using uv run alembic upgrade head.

3. Fixing a bad migration

Typical cases:

• Migration fails only on SQLite (ALTER constraint limitations).
• Migration was generated while pointing at SQLite instead of Postgres.

Workflow:

1. Identify the failing revision in alembic/versions/.
2. If failure is SQLite-specific, prefer running migrations against Postgres by exporting LETTA_PG_URI and re-running upgrade.
3. If logic is wrong, create a new migration that fixes the problem rather than editing an applied revision (especially in shared environments).
4. For purely local/dev history, you can delete and regenerate migrations but only if no one else depends on them.

See references/sqlite-vs-postgres-gotchas.md for SQLite-specific issues.

4. Switching between SQLite and Postgres

Alembic picks the engine based on letta.settings.DatabaseChoice and environment variables.

General rules:

• For local dev stateful runs, just ready handles baseline migrations.
• For schema design and production-like migrations, prefer Postgres and set LETTA_PG_URI.

Workflow for Postgres-targeted migration:

1. export LETTA_PG_URI=postgresql+pg8000://postgres:postgres@localhost:5432/letta-core
2. From apps/core:
   • uv run alembic upgrade head
   • uv run alembic revision --autogenerate -m "..."

Troubleshooting

• "Target database is not up to date" when autogenerating
  • First run uv run alembic upgrade head (with appropriate engine/URI).
• SQLite NotImplementedError about ALTER CONSTRAINT
  • Switch to Postgres by setting LETTA_PG_URI and rerun.
• Autogenerated migration missing expected changes
  • Ensure ORM imports and metadata (Base.metadata) are correct and that the changed model is imported in Alembic env context.
• Autogenerated migration has unexpected drops/renames
  • Review model changes; consider explicit operations instead of relying on autogenerate.

References

• references/migration-commands.md — canonical commands for uv, Alembic, and just.
• references/sqlite-vs-postgres-gotchas.md — engine-specific pitfalls and how to avoid them.