Scaffolded apps migrate the wrong database: alembic runs from host/ and never reads the repo-root .env

[antosubash]

Summary

An app scaffolded with smpy new <name> --db postgres runs its Alembic migrations against the default SQLite database, not the SM_DATABASE_URL configured in .env. The app itself (via host/main.py) correctly uses Postgres, so the schema is created in one database while the application reads another.

This is silent — nothing warns you. The only visible hint is a stray host/app.db appearing and an easily-missed autogenerate log line reading dialect 'sqlite'.

Root cause

Two coupled defects in the scaffold templates:

1. templates/host/alembic.ini sets script_location = migrations — resolved relative to the invocation cwd, so alembic can only be run from inside host/.
2. templates/workspace/Makefile therefore does cd host && uv run alembic ....

But BootstrapSettings (in simple_module_hosting/bootstrap_settings.py) declares:

model_config = SettingsConfigDict(env_prefix="SM_", env_file=".env", extra="ignore")

env_file=".env" is cwd-relative. The scaffold writes .env at the repo root, so running from host/ never reads it and database_url falls back to its default sqlite+aiosqlite:///./app.db.

Notably, this repo's own Makefile already gets this right and documents exactly why:

# All targets run from the repo root so alembic and `make dev-api` share the
# same cwd (and therefore the same .env, SM_DATABASE_URL, and SQLite path).
migrate:
	uv run --project host alembic -c host/alembic.ini upgrade heads

and host/alembic.ini here uses:

# Resolve script_location relative to this ini file, not the invocation cwd,
script_location = %(here)s/migrations

The scaffold templates simply don't carry these two fixes.

Reproduction

uv tool install simple_module_cli==0.0.30
smpy new demo --db postgres --preset standard --yes --no-install
cd demo
# point SM_DATABASE_URL at a real Postgres DB in .env
uv sync --all-packages
make migration msg="initial schema"

Observe in the output:

dialect 'sqlite' under SQLAlchemy 2.0.52 can't reflect these indexes

and a host/app.db file created. The configured Postgres database stays empty.

Suggested fix

Port the two corrections from this repo into the scaffold templates:

• templates/host/alembic.ini → script_location = %(here)s/migrations
• templates/workspace/Makefile → run from the repo root:

migrate:
	uv run --project host alembic -c host/alembic.ini upgrade heads

migration:
	uv run --project host alembic -c host/alembic.ini revision --autogenerate -m "$(msg)"

Optionally, make the mismatch loud rather than silent — e.g. have env.py log the resolved database URL, or have Settings search parent directories for .env.

Environment

• simple_module_cli / framework: 0.0.30
• Python 3.14, uv 0.11.7, Linux

---

Found while building an app scaffolded from smpy new, using globalcanopyatlas as a reference.