# Railway Deployment Guide v5 (Raw SQL / Psycopg2)

This guide provides the **standard deployment pattern** for Flask applications that use **raw SQL with `psycopg2`** instead of an ORM like SQLAlchemy/Alembic.

This pattern allows you to run raw `.sql` migration files automatically during deployment, keeping your database in sync with your code, exactly like the Alembic workflow in `railwaydeploy2v2.md`.

---

## 1. Project Structure for Deployment

Your repository must contain these critical files in the root directory:

```text
your-flask-app/
├── app.py                  # Main application factory
├── migrations/             # Directory containing your raw .sql files
│   ├── 001_initial.sql
│   └── 002_add_users.sql
├── migrate.py              # Custom Python script to run .sql migrations
├── requirements.txt        # Python dependencies
├── Dockerfile              # Instructions to build the container
├── start.sh                # The command that runs when the server starts
├── railway.json            # Railway-specific configuration
└── Procfile                # Process definition (Railway fallback)
```

---

## 2. Essential Files Configuration

### `requirements.txt`
Ensure you are using `psycopg2-binary` for PostgreSQL compatibility.
```text
Flask
psycopg2-binary
gunicorn
python-dotenv
requests
...
```

### `migrate.py` (The Custom Migrator)
This script is the engine that replaces `flask db upgrade`. It tracks which `.sql` files have been run and executes any new ones. Place this in the root of your project.

```python
import os
import sys
import psycopg2

DATABASE_URL = os.environ.get('DATABASE_URL')
if not DATABASE_URL:
    print("DATABASE_URL is not set. Skipping migrations.")
    sys.exit(0)

def run_migrations():
    print("Connecting to database for migrations...")
    conn = psycopg2.connect(DATABASE_URL)
    cur = conn.cursor()

    # Create migrations tracking table if it doesn't exist
    cur.execute("""
        CREATE TABLE IF NOT EXISTS schema_migrations (
            version VARCHAR(255) PRIMARY KEY,
            applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    """)
    conn.commit()

    # Get already applied migrations
    cur.execute("SELECT version FROM schema_migrations")
    applied = {row[0] for row in cur.fetchall()}

    # CRITICAL FIX FOR EXISTING DATABASES
    # If live DB exists but schema_migrations is empty, mark legacy migrations as done
    if not applied:
        cur.execute("SELECT EXISTS (SELECT FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'storefronts')")
        has_storefronts = cur.fetchone()[0]
        
        if has_storefronts:
            legacy_migrations = [
                '001_initial_schema.sql',
                '002_checkout_best_integration.sql',
                '003_checkout_events.sql',
                '004_storefront_types.sql',
                '005_team_members.sql',
                '006_product_cache.sql'
            ]
            for lm in legacy_migrations:
                cur.execute("INSERT INTO schema_migrations (version) VALUES (%s) ON CONFLICT DO NOTHING", (lm,))
            conn.commit()
            
            cur.execute("SELECT version FROM schema_migrations")
            applied = {row[0] for row in cur.fetchall()}

    migrations_dir = 'migrations'
    if not os.path.exists(migrations_dir):
        print(f"Migrations directory '{migrations_dir}' not found. Skipping.")
        return

    # Get all .sql files and sort them alphabetically (e.g., 001_..., 002_...)
    files = sorted([f for f in os.listdir(migrations_dir) if f.endswith('.sql')])
    
    for file in files:
        if file not in applied:
            print(f"Applying migration: {file}...")
            filepath = os.path.join(migrations_dir, file)
            with open(filepath, 'r') as f:
                sql = f.read()
            
            try:
                cur.execute(sql)
                cur.execute(
                    "INSERT INTO schema_migrations (version) VALUES (%s)",
                    (file,)
                )
                conn.commit()
                print(f"Successfully applied {file}")
            except Exception as e:
                conn.rollback()
                print(f"Error applying {file}: {e}")
                sys.exit(1)
        else:
            print(f"Migration {file} already applied. Skipping.")

    cur.close()
    conn.close()
    print("All migrations applied successfully.")

if __name__ == '__main__':
    run_migrations()
```

### `start.sh` (The Deployment Hook)
This script handles the database migration **before** the app starts, using the `migrate.py` script we just created.

```bash
#!/bin/bash
set -e

echo "Starting Deployment Process..."

# 1. Run Database Migrations (Custom Raw SQL Migrator)
echo "Running Database Migrations..."
python migrate.py

# 2. Start the Gunicorn Server
# We use 'exec' to replace the shell process with the server.
echo "Starting Gunicorn Server..."
exec gunicorn app:app \
    --bind "0.0.0.0:${PORT:-8000}" \
    --workers 2 \
    --timeout 120 \
    --log-level info
```

### `Dockerfile`
This tells Railway how to build your Python environment.
```dockerfile
FROM python:3.9-slim

WORKDIR /app

# Install system dependencies (needed for psycopg2)
RUN apt-get update && apt-get install -y \
    gcc \
    libpq-dev \
    && rm -rf /var/lib/apt/lists/*

# Install Python dependencies
COPY requirements.txt .
RUN pip install --no-cache-dir -r requirements.txt

# Copy application code
COPY . .

# Make start script executable
RUN chmod +x start.sh

# Expose port
EXPOSE 8000

# Run the start script
CMD ["./start.sh"]
```

### `Procfile`
Ensure Railway falls back to your startup script.
```text
web: ./start.sh
```

---

## 3. Database Migration Workflow

### Local Development (When you need a database change)
1. Create a new `.sql` file in the `migrations/` directory.
2. Name it sequentially. For example, if the last file was `005_users.sql`, name the new one `006_add_status.sql`.
3. Write your raw `ALTER TABLE` or `CREATE TABLE` commands inside it.
4. Commit the `.sql` file to Git.

### Deployment (Automatic)
When you push to Railway:
1. Railway builds the container.
2. It runs `./start.sh`.
3. `python migrate.py` runs automatically. It checks the `schema_migrations` table, finds your new `006_add_status.sql` file, applies it, and records it.
4. The server starts via Gunicorn with the updated database schema.

---

## 4. Persistent Storage (Volumes)
If your app allows file uploads, you **MUST** configure a Volume in Railway to prevent files from deleting on every deploy.

1. Go to Railway Dashboard > Your Project > Settings.
2. Click **"Volumes"** (or "Mounts").
3. Add a new volume:
   * **Mount Path:** `/app/uploads` (or whatever your `UPLOAD_FOLDER` is).
4. Redeploy.

---

## 5. Environment Variables
Ensure these are set in Railway:
* `DATABASE_URL`: (Auto-set by Railway Postgres plugin)
* `SECRET_KEY`: A long random string.
* `FLASK_APP`: `app.py`