# Railway Deployment Standards Comparison

For internal knowledge and documentation, here is a summary of all the Railway deployment patterns (SOPs) that have evolved over time across the projects.

## v2: The "Manual `run.py` SQL" Strategy
*   **Stack:** Python, Flask, raw SQL (`psycopg2`)
*   **Method:** Migrations were written explicitly as Python strings inside a `try/except` block inside `run.py` (e.g. `db.session.execute(text("ALTER TABLE..."))`).
*   **Pros:** Fast, no extra scripts needed.
*   **Cons:** Very messy as the app grows. Hard to track which migrations ran and which didn't. Codebase becomes cluttered with old `ALTER TABLE` statements.

## v2v2: The "Flask-Migrate / Alembic" Strategy
*   **Stack:** Python, Flask, SQLAlchemy, Flask-Migrate (Alembic)
*   **Method:** The `start.sh` file runs `flask db upgrade` before starting Gunicorn.
*   **Pros:** Highly robust. Alembic auto-generates migration files from models, and automatically tracks applied migrations in an `alembic_version` table.
*   **Cons:** Requires rewriting the app's database layer to use SQLAlchemy. Does not work if the app is already written using raw `psycopg2` strings.

## v3: The "Node.js Drizzle ORM" Strategy
*   **Stack:** Node.js, TypeScript, Express, Drizzle ORM
*   **Method:** Because `drizzle-kit push` is interactive and fails on CI/CD (Railway), a specific `manual-migrate.js` script is run as a pre-start command in `package.json`. The script uses `IF NOT EXISTS` logic.
*   **Pros:** Fixes Drizzle's interactive prompt issue on Railway.
*   **Cons:** Only applies to Node.js / JavaScript projects.

## v4: The "Pure Python `pg8000`" Strategy
*   **Stack:** Python, Flask, `pg8000` + `scramp`
*   **Method:** Replaces the C-based `psycopg2` driver with the pure Python `pg8000` to avoid Linux system library compilation issues on Railway (`libpq-dev`). Relies on executing specific migration scripts manually (e.g., `python procedures/migrate_trust_circles.py`) inside `start.sh`.
*   **Pros:** Solves system-level dependency errors on Nixpacks builders.
*   **Cons:** Doesn't track migration state automatically. Relies on the developer manually uncommenting scripts in `start.sh` on each deploy.

## v5: The "Auto-Migrator for Raw SQL" Strategy *(The Current BMOS Standard)*
*   **Stack:** Python, Flask, raw SQL (`psycopg2` or `pg8000`)
*   **Method:** Brings the best of `v2v2` (Alembic) to raw SQL apps. A custom `migrate.py` script automatically creates a `schema_migrations` table, scans the `migrations/` directory, and runs any `.sql` file that hasn't been executed yet. This runs automatically via `start.sh` on every deploy.
*   **Pros:** Zero ORM rewrite required. Automatic state tracking. Clean `migrations/` folder. Completely hands-off deployment.
*   **Cons:** Requires maintaining raw `.sql` files instead of auto-generating them from Python models.