Database Migrations
====================

The platform uses a **safe-only migration** strategy with automatic schema
management on startup, plus Alembic for controlled production migrations.

Auto-Migration (Development)
-----------------------------

On startup, the backend applies **safe-only** changes automatically:

- Adding new tables
- Adding new columns (with defaults)
- Adding new indexes
- Adding new constraints

This is handled in the lifespan function of the FastAPI application.

.. warning::

   Setting ``RESET_DB_ON_STARTUP=true`` drops ALL tables and recreates them.
   Never use this in production.

Alembic (Production)
----------------------

For production deployments, use Alembic for controlled migrations:

.. code-block:: bash

   cd backend

   # Generate a new migration
   alembic revision --autogenerate -m "Add new column to projects"

   # Review the generated migration
   cat migrations/versions/xxx_add_new_column.py

   # Apply migrations
   alembic upgrade head

   # Rollback one step
   alembic downgrade -1

   # Check current version
   alembic current

Migration Best Practices
~~~~~~~~~~~~~~~~~~~~~~~~~

1. **Always review auto-generated migrations** before applying
2. **Backfill data** for new NOT NULL columns before adding the constraint
3. **Use savepoints** for constraint violations (the platform handles this)
4. **Test migrations** on a copy of production data first
5. **Never drop columns** without deprecation period

Schema Management Rules
------------------------

**Safe operations (auto-applied):**

- ``CREATE TABLE``
- ``ALTER TABLE ADD COLUMN`` (with default or nullable)
- ``CREATE INDEX``
- ``ALTER TABLE ADD CONSTRAINT``

**Unsafe operations (require manual Alembic migration):**

- ``DROP TABLE``
- ``DROP COLUMN``
- ``ALTER COLUMN TYPE``
- ``ALTER COLUMN SET NOT NULL`` (on populated tables)
- Data migrations

LangGraph Checkpoint Tables
-----------------------------

The following tables are managed by LangGraph and excluded from auto-migration:

- ``checkpoint_writes``
- ``checkpoint_blobs``
- ``checkpoints``
- ``checkpoint_migrations``

These are initialized by the LangGraph PostgreSQL checkpointer on first use.

UUID Generation
----------------

All models use ``uuid4()`` as callable defaults, ensuring unique IDs are
generated per-row at insert time (not at class definition time).

Connection Configuration
-------------------------

.. code-block:: python

   # Async engine (for application)
   engine = create_async_engine(
       POSTGRES_URL,  # postgresql+asyncpg://user:pass@host:5432/db
       echo=DEBUG,
   )

   # Session factory
   async_session = sessionmaker(
       engine,
       class_=AsyncSession,
       expire_on_commit=False,
   )