SQLite stays the default (fine for a small single-cafe deployment); this
makes Postgres a supported alternative via DATABASE_URL for clients who
outgrow SQLite's single-writer lock (multiple concurrent admin users,
high write volume).
- requirements.txt: add psycopg2-binary (Postgres driver for SQLAlchemy).
- bober_bbq/migrate.py: the admin_users.created_at/last_login_at ALTER
TABLE DDL used the type name DATETIME, which SQLite accepts (it only
has type affinity) but Postgres doesn't recognize as a real type — use
TIMESTAMP instead, valid on both.
- bober_bbq/admin/inventory.py: the ingredient-name duplicate check used
db.func.lower() in SQL, which is ASCII-only on SQLite and silently
fails to match Cyrillic names (same bug already fixed this session in
admin/suppliers.py's supplier-name dedup) — compare in Python instead.
Not a Postgres crash risk (Postgres's LOWER() is Unicode-aware), but
worth fixing for consistency since moving to Postgres would otherwise
silently change this check's behavior.
- bober_bbq/utils/gdrive_backup.py and admin/system.py were audited for
SQLite-specific behavior (PRAGMA integrity_check, direct file
copy/restore) but already guard every such path behind
`uri.startswith("sqlite:///")` / `_sqlite_path()` checks that
gracefully no-op with a clear message on Postgres — no changes needed.
- scripts/migrate_sqlite_to_postgres.py (new): standalone, manually-run,
one-off tool for actually moving a specific client's data off SQLite.
Reuses the app's own SQLAlchemy models (db.metadata) rather than raw
table introspection, copies rows in FK-safe order, refuses to run
against a non-empty target unless --truncate is passed, and resets
Postgres auto-increment sequences after copying explicit ids. Not
wired into run_web.py or any automatic startup path.
- docs/DEPLOYMENT.md: new section on when/how to use Postgres instead of
SQLite, standing up a Postgres instance, the DATABASE_URL format, and
when/how to run the migration script.
Verified: create_app() + db.create_all() + migrate() + seed() (including
the ALTER-TABLE-ADD-COLUMN code path exercised against a DB missing
those columns) still work cleanly against a fresh temp SQLite DB. The
migration script's row-copy/FK-ordering/truncate/non-empty-guard logic
was verified end-to-end using SQLite as a stand-in target, since no
Postgres server is available in this environment — the Postgres-specific
pieces (actual connection, pg_get_serial_sequence/setval) are
unverified beyond code review.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
