Schema-per-module triggered by SM_DATABASE_URL env var creates unsolvable worker config

[antosubash]

Summary

simple_module_db.base._default_provider() reads SM_DATABASE_URL at model-import time to decide whether each module's tables get a dedicated Postgres schema. This decision sticks for the process lifetime and conflicts with background_tasks.sync_db._build_engine(), which also reads SM_DATABASE_URL (at engine-build time) to know which DB to connect to.

The two consumers create an unsolvable choice for Celery workers connecting to the dev DB:

SM_DATABASE_URL set?	Models register with…	Worker sync_db connects to…
Yes (Postgres URL)	per-module schemas (legends.legends_legend, validation.validation_session, …)	the right Postgres DB
No	flat public schema (matches what autogenerated migrations produce)	falls back to sqlite:///./app.db

Autogenerated Alembic migrations land in public (because env.py reads Settings.database_url from .env, not from os.environ, so _default_provider defaults to SQLite at autogen time). Real-app workers running with SM_DATABASE_URL set then hit:

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedTable)
  relation "background_tasks.background_tasks_task_execution" does not exist

— the schema-prefixed lookup goes to a schema that was never created.

Reproduction

1. sm new --preset full --db postgres.
2. Set SM_DATABASE_URL=postgresql+asyncpg://… in .env and os.environ.
3. alembic upgrade head succeeds (all tables in public).
4. celery -A scripts.run_worker:celery worker and trigger any task.
5. Worker fails with UndefinedTable on the per-module-schema lookup.

Workarounds I'm using

For tests, host_conftest.py patches _build_engine AND deliberately does NOT set SM_DATABASE_URL in os.environ, routing the URL through Settings.database_url only.

For the dev worker, a wrapper script:

import simple_module_db.base as _smdb_base
_smdb_base._default_provider = lambda: _smdb_base.DatabaseProvider.SQLITE
import background_tasks.sync_db as _sd
_sd._build_engine = lambda: create_engine(SYNC_URL, …)
from background_tasks.celery_app import build_celery
celery = build_celery(BackgroundTasksSettings())

Suggested fix

The provider decision should not live in module-level state read from a process env var. Options:

1. Make schema layout an explicit per-host setting (Settings.schema_per_module: bool = False) instead of a side-effect of SM_DATABASE_URL. Migrations and runtime read from the same source.
2. Make create_module_base() default to provider=DatabaseProvider.SQLITE (flat layout) and require explicit opt-in via provider=DatabaseProvider.POSTGRESQL per module.
3. Drop the per-Postgres-schema feature entirely — the <module>_<table> prefix already provides namespacing.

Whichever path, the os.environ['SM_DATABASE_URL'] read in _default_provider should go.

Impact

Every host using Postgres + Celery workers needs a custom bootstrap script. Test infrastructure carries _patch_sync_engine hacks. Documentation has to warn users to pop("SM_DATABASE_URL", None) in test conftests.

Environment

• simple_module_core==0.0.3, simple_module_db==0.0.3, simple_module_hosting==0.0.3, simple_module_background_tasks==0.0.3
• Postgres 16 + PostGIS 3.4
• Discovered while building laco_wiki_python rewrite

[antosubash]

Closing — addressed by commit ade3e24 (PR #98). Schema-per-module is now controlled by an explicit SM_SCHEMA_PER_MODULE env var (with the historical SM_DATABASE_URL fallback kept for back-compat), so the worker and migration tooling read the same source of truth. Combined with #94's load_dotenv_into_environ change in scripts/run_worker.py, the wrapper-script workaround is no longer needed.

---

Generated by Claude Code