Expression-based index emitted twice under PostgreSQL — fresh scaffold fails first 'make migrate' with DuplicateTable

[antosubash]

Summary

On a freshly scaffolded app using PostgreSQL, the very first make migrate fails:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable)
relation "ix_users_user_email_lower" already exists

[SQL: CREATE INDEX ix_users_user_email_lower ON users_user (lower(email))]

The autogenerated initial migration contains the expression-based index twice:

op.create_index('ix_users_user_email_lower', 'users_user', [sa.literal_column('lower(email)')], unique=False)
op.create_index(op.f('ix_users_user_email'), 'users_user', ['email'], unique=True)
op.create_index('ix_users_user_email_lower', 'users_user', [sa.literal_column('lower(email)')], unique=False)

and symmetrically twice in downgrade().

Root cause

make_process_revision_directives in simple_module_db/migrations.py injects a CreateIndexOp for every expression-based index found in the metadata after each CreateTableOp. Its own docstring explains the workaround is needed specifically because SQLite cannot reflect expression-based indexes:

SQLAlchemy 2.0 can't reflect expression-based indexes (functional indexes like CREATE INDEX ... ON t (lower(email))) under the SQLite dialect. Autogenerate guards against false-positive diffs there by skipping the index entirely […] SQLite dev DBs end up without an index that production Postgres has.

However the injection is unconditional — it does not check the dialect, nor whether the op list already contains that index. Under PostgreSQL autogenerate does emit the index, so the hook adds a second, identical create_index and the migration is invalid on first run.

Reproduction

smpy new demo --db postgres --preset standard --yes --no-install
cd demo
# set SM_DATABASE_URL to a real Postgres DB in .env
uv sync --all-packages
make migration msg="initial schema"
make migrate     # <-- DuplicateTable error

(Requires the fix from #262 so that alembic actually targets Postgres; otherwise it runs against SQLite and the duplicate is masked, because autogenerate skips the index there and only the injected copy remains.)

Suggested fix

Make the injection idempotent — skip when an op for that index name is already present. Roughly, in _inject_create_index_after_create_table:

existing = {
    op.index_name
    for op in ops.ops
    if isinstance(op, CreateIndexOp)
}
for index in expression_indexes.get(table_name, []):
    if index.name in existing:
        continue   # dialect already reflected it (e.g. PostgreSQL)
    ...

Same guard for _inject_drop_index_before_drop_table / DropIndexOp. A name-based check keeps it dialect-agnostic and is robust if SQLAlchemy later gains SQLite reflection for these.

Impact

Blocks the documented smpy new --db postgres quickstart at the first make migrate. Workaround is to hand-edit the generated migration and delete the duplicate lines.

Environment

• framework / simple_module_db: 0.0.30
• SQLAlchemy 2.0.52, alembic (bundled), psycopg2
• PostgreSQL 16, Python 3.14, Linux

---

Related: #262 (that issue masks this one by silently routing migrations to SQLite).