User data stored in ephemeral per-instance /tmp SQLite on Vercel: accounts/progress lost, no shared DB

[ionfwsrijan]

Description

On the deployed platform (Vercel), all user data is stored in a local SQLite file on an ephemeral, per-instance filesystem.

src/config.py:

if os.getenv("VERCEL") == "1" or os.getenv("VERCEL_ENV") is not None:
    SQLALCHEMY_DATABASE_URI = os.getenv("DATABASE_URL", "sqlite:////tmp/devpath.db")

Vercel serverless functions have a writable but non-persistent /tmp that is not shared between instances and is frequently recycled. src/app.py runs db.create_all() at import time and auto-seeds from data/projects.json on every cold start, guarded only by Project.query.count() == 0.

Consequences:

• User accounts, ProjectProgress (roadmap progress), and UserGameProgress rows are permanently lost whenever the instance that wrote them is recycled.
• Two concurrent instances hold separate SQLite files, so data written on one instance is invisible on another (a user "logged in" on one instance is unknown to the next).
• Concurrent cold starts can both observe count() == 0 and double-seed, hitting primary-key/unique integrity errors during import — a crash at module import time.

Expected Behaviour

Production user data is durable and shared across instances.

Actual Behaviour

All user data lives in throwaway /tmp files; data is lost and inconsistent across serverless instances. Verified from the code path: without DATABASE_URL, the app has no persistent backing store on Vercel.

Proposed Fix

• Require DATABASE_URL to point at a managed persistent database (e.g. Postgres) for any production/Vercel deployment; fail fast if it is missing on Vercel instead of silently using /tmp SQLite.
• Move seeding out of the import-time app_context into an idempotent, transaction-guarded upsert (and skip when a data version marker exists) to avoid double-seed races.
• Document the production database requirement and provide a migration path for existing deployments.
• Add a config test asserting a Vercel-configured app without DATABASE_URL raises instead of using /tmp.