Adding a Model

Migration rules (read first)

Before writing any code, understand the rules that govern migrations in Orchid:

1. Only add — never drop or alter in upgrade(). Dropping or altering columns in an upgrade migration can break other running instances during a rolling deployment.
2. downgrade() should correctly reverse what upgrade() added (dropping the table or column that was added is fine here).
3. Keep the migration chain linear — one head, one down_revision chain. No branches.
4. Always review the generated migration file before committing — Alembic sometimes generates unexpected or wrong migrations.

Danger

These rules are enforced by GitHub Actions CI. A migration that creates multiple heads or has an invalid down_revision will fail CI and block the PR.

Step-by-step

1. Choose the right model file

   Models are organized by domain. Add to an existing file when the model clearly belongs to that domain:

   Domain	File
   Cases and cycles	orchid/models/case.py or cycle.py
   Users and admins	orchid/models/user.py
   Agency configuration	orchid/models/agency.py
   Profiles	orchid/models/profile.py
   Forms	orchid/models/form.py
   Workflow / tasks	orchid/models/workflow.py or tood.py
   Contracts	orchid/models/contract.py
   General utility models	orchid/models/general.py

   If none fit, create a new file with a clear, domain-specific name.

2. Write the model

   from orchid.models import BaseModel, db
   
   class MyNewModel(BaseModel):
       __tablename__ = 'my_new_model'
   
       id = db.Column(db.Integer, primary_key=True)
       case_id = db.Column(db.Integer, db.ForeignKey('case.id'), nullable=False)
       name = db.Column(db.String(255), nullable=False)
       notes = db.Column(db.Text, nullable=True)
       created_at = db.Column(db.DateTime, nullable=False)
       is_active = db.Column(db.Boolean, default=True, nullable=False)
   
       # Relationship (optional but useful)
       case = db.relationship('Case', backref='my_new_models')

   Column type tips:

   • String fields: always set an explicit length — db.String(255) not db.String()
   • Text fields: use db.Text for longer content with no predictable max length
   • Nullable: be explicit — set nullable=False for required fields, nullable=True for optional ones
   • Defaults: set default= for columns that always have a value on insert

3. Use mixins for common patterns

   Check orchid/models/mixins.py before adding common column sets:

   from orchid.models.mixins import AddressMixin, DocumentMixin, FlagsMixin
   
   class MyNewModel(BaseModel, AddressMixin):
       # AddressMixin adds: street, city, state, zip, country columns

4. Generate the migration

   Make sure your virtual environment is active and the DB is running:

   flask db migrate -m "JMS-XXXX add my_new_model table"

   Use a ticket number in the message — this makes it easy to trace migrations back to issues.

5. Review the generated migration file

   Open the generated file in migrations/versions/. Check:

   • Does upgrade() create the right table/columns?
   • Does downgrade() correctly reverse it?
   • Are any unexpected changes included (Alembic sometimes picks up unrelated changes)?
   • Is down_revision pointing to the correct parent migration?

   # Expected structure:
   def upgrade():
       op.create_table('my_new_model',
           sa.Column('id', sa.Integer(), nullable=False),
           sa.Column('case_id', sa.Integer(), nullable=False),
           sa.Column('name', sa.String(length=255), nullable=False),
           # ...
           sa.ForeignKeyConstraint(['case_id'], ['case.id'], ),
           sa.PrimaryKeyConstraint('id')
       )
   
   def downgrade():
       op.drop_table('my_new_model')

6. Apply the migration locally

   flask db upgrade

   Verify there are no errors and the table exists in your local DB.

7. Verify single head

   flask db heads

   There should be exactly one head. If there are two, you have a branch — resolve it before committing.

8. Commit both the model and the migration together

   Always commit the model change and the migration file in the same commit. Never commit a model without its migration or a migration without its model.

Adding a column to an existing model

Adding a column follows the same flow but uses op.add_column:

# Generated by flask db migrate:
def upgrade():
    op.add_column('case',
        sa.Column('new_field', sa.String(length=100), nullable=True)
    )

def downgrade():
    op.drop_column('case', 'new_field')

Caution

New columns should almost always be nullable=True or have a server_default. Adding a NOT NULL column without a default to a table with existing rows will fail during migration — MySQL cannot add a non-nullable column with no default to a non-empty table.

The migration lock mechanism

In production, migrations run automatically on EB startup with a locking mechanism (migration_lock table) that prevents concurrent execution when multiple instances start simultaneously. You do not need to manage this manually — it runs automatically via orchid/migrations.py.

Checking migration chain health

flask db current     # Show what revision the DB is currently at
flask db heads       # Show all head revisions (should be exactly 1)
flask db history     # Show full migration history

GitHub Actions also runs scripts/check_alembic_heads.py on every PR to enforce a single head.