Web process has the same SQLite-fallback problem as #94: background_tasks.sync_db reads SM_DATABASE_URL from os.environ, but uvicorn-loaded .env never gets there

[antosubash]

Summary

Follow-up / regression of #94. That issue fixed the worker entrypoint; the web process has the same fault and isn't covered by it.

background_tasks.sync_db._build_engine() reads os.environ.get(\"SM_DATABASE_URL\", \"sqlite:///./app.db\"). The Celery task_publish signal handler (which writes the background_tasks_task_execution row when the API enqueues a task) calls sync_session(), which calls _build_engine(). If SM_DATABASE_URL isn't in the OS environment, it silently builds a SQLite engine pointing at ./app.db — even when the rest of the app, async Settings() included, is correctly using Postgres via host/.env.

Why it's still broken for the web process

pydantic-settings reads host/.env into the Settings() object — but it doesn't propagate values to os.environ. The README's documented dev command:

uv run uvicorn main:app --reload --port 8000

…doesn't load .env into the environment either. So:

• Settings.database_url → Postgres URL (from .env) ✅
• os.environ[\"SM_DATABASE_URL\"] → unset ❌
• background_tasks.sync_db._build_engine() → falls back to sqlite:///./app.db ❌

First task publish:

ERROR background_tasks.signals: on_task_publish failed for task_id=...
sqlite3.OperationalError: unknown function: now()
[SQL: INSERT INTO background_tasks_task_execution ... VALUES (..., 'queued_at', ...)]

Status of the enqueued work in the UI freezes at the pre-publish state ("Uploading", in this repo's case) because the row never gets written. The task is still pushed onto the broker, but the dashboard can't see it, retries don't show up, and sweep_stuck_tasks has nothing to sweep.

Reproduction

1. sm new --preset full host with SM_DATABASE_URL=postgresql+asyncpg://... in .env (and only .env).
2. make docker-up && uv run alembic upgrade head.
3. uv run uvicorn main:app --port 8000 (the documented dev command).
4. Trigger any code path that enqueues a Celery task (e.g. dataset upload).
5. Watch /tmp/api.log:

   sqlite3.OperationalError: unknown function: now()
   

Expected

background_tasks.sync_db and the framework boot path should agree on which DB URL wins. Two ways:

A. _build_engine() reads from Settings() (or a registered hook) instead of os.environ. The framework already builds Settings() once at boot via simple_module_hosting.create_app; BackgroundTasksModule.on_startup could stash the resolved sync URL on a module attribute and _build_engine could pick it up.

B. The framework's app builder (and scripts/run_worker.py) explicitly load_dotenv() from the host directory before constructing settings, so .env values land in os.environ for any sub-component (including _build_engine) that still reads the env.

(A) is cleaner — os.environ reads in random library code is the antipattern that #94 also flagged.

Workaround

Mirror the scripts/run_worker_dev.py trick for the API:

# scripts/run_api_dev.py
import simple_module_db.base as _smdb_base
_smdb_base._default_provider = lambda: _smdb_base.DatabaseProvider.SQLITE  # match dev migrations

from sqlalchemy import create_engine
import background_tasks.sync_db as _sd

_DEV_URL = \"postgresql+psycopg2://postgres:postgres@localhost:5432/<dbname>\"

def _build_engine_dev():
    return create_engine(_DEV_URL, pool_pre_ping=True, pool_size=2, max_overflow=3)

_sd._build_engine = _build_engine_dev

from main import app  # noqa

Run with uv run uvicorn scripts.run_api_dev:app. That every fresh dev-mode checkout needs two parallel "dev" wrappers — run_worker_dev.py and run_api_dev.py — to compensate for the same root cause is the smell.

Acceptance

• A fresh sm new --preset full host's documented uv run uvicorn main:app command writes background_tasks_task_execution rows to the same Postgres database the rest of the app uses.
• The dev-only _default_provider / _build_engine monkey-patches stop being a mandatory part of the host's scripts/ directory.

Related

• Celery worker entrypoint doesn't load .env; connects to SQLite with module schemas #94 — same root cause, fixed only for the worker.

[antosubash]

Fixed in #130.