Continuation of the previous commit (gdrive_backup.py removal landed
separately by accident) — adds the new module itself, the admin
routes/template using it, generalized Setting keys, updated docs/
provisioning notes, and tests.
Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com>
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>
Two gaps: an unpaid delivery had no way to later be marked paid (or
corrected at all) short of delete-and-recreate, losing history; and
there was no way to hand a supplier or an accountant a statement of
account.
- New GET+POST /suppliers/<id>/deliveries/<id>/edit
(supplier_delivery_edit.html) — edits every field including
paid_amount/payment_date. Deliberately a pure ledger correction: like
the existing delete button already warns, this never touches
current_stock/StockMovement even if quantity or the ingredient link
changes — stock only moves through the ingredient's own actions.
- New bober_bbq/utils/supplier_reports.py generates both a per-supplier
detailed statement and an all-suppliers summary, each in Excel
(openpyxl, already a dependency) and PDF (new: fpdf2), optionally
scoped to a delivery-date range. Download buttons added to the
supplier detail page and the suppliers list.
- PDF needs a real Cyrillic TTF; added fpdf2 to requirements.txt and
documented (and will install) fonts-dejavu-core on the server rather
than bundling a font in the repo — DejaVu is freely embeddable,
Windows fonts are not. A missing font produces a clear flash message,
not a 500.
Verified with a 33-check smoke test across both features (edit
correctness + stock isolation, date-range filtering on both statement
shapes, real route responses reopened/parsed, and the missing-font
fallback path) plus a full regression rerun of every supplier/inventory
smoke suite from this session.
- Reads .xlsx/.xlsm (openpyxl), legacy .xls (xlrd, with native Excel
date-cell conversion), and .csv (delimiter/encoding auto-detected —
handles ';'/',' delimiters and utf-8/cp1251).
- Multi-sheet workbooks: auto-picks the sheet that looks most like a
data table (scored the same way as header-row detection), with a
sheet picker to override and re-preview without re-uploading.
- Broader Ukrainian+Russian header synonyms per field, plus a per-word
fuzzy fallback so typo'd/unusual headers still get a starting guess
the admin can correct.
- Smarter number parsing (handles both "1.234,56" and "1,234.56"
thousands/decimal conventions).
- New optional "№ накладної/рахунку" field, mappable on import and
enterable on the manual-add form.
Verified against CSV, a deliberately multi-sheet workbook (data on a
non-first sheet with a decoy sheet in front), a synthetic legacy .xls,
and a regression pass confirming the original real file still imports
identically.
New Supplier/SupplierDelivery models track what was delivered, paid,
and owed per supplier. Add/edit/deactivate/delete suppliers manually,
or import an Excel ledger of any layout — the admin maps which column
is which (with an auto-guess, including a fuzzy fallback for typo'd
headers) rather than relying on a fixed format, since real supplier
files vary. Rows with no product name (empty template/totals rows)
are skipped automatically. A delivery can optionally be linked to an
existing Ingredient and top up its stock on entry, opt-in per action.
Admin always has access; managers only if "suppliers_manager_access"
is turned on in Settings — hard delete stays admin-only regardless,
matching the order-delete precedent. Verified end-to-end against a
real supplier ledger file, including its date-format quirk and a
typo'd column header.
Opt-in via "gdrive_backup_enabled" — off by default, no effect until
configured. Uses a Google service account (no interactive OAuth, so it
runs unattended from a headless server forever).
- bober_bbq/utils/gdrive_backup.py: uploads a fresh local SQLite backup
on a configurable interval, then deletes the oldest Drive files once
the folder holds more than gdrive_retention_count backups. The
scheduled job (hourly, in run_web.py) re-checks the interval itself
each tick rather than being tied to a fixed APScheduler schedule, so
changing the interval in the admin panel takes effect without a
restart. Failures notify the owner in Telegram, matching the
existing monobank-token-health pattern.
- Admin Backups page gets a new section: paste the service account's
JSON key + target Drive folder ID, set interval/retention, "Перевірити
підключення" to verify without uploading, "Backup зараз" to trigger
one immediately, plus inline step-by-step setup instructions (Cloud
Console → enable Drive API → service account → JSON key → share the
folder with its email → paste both here).
- gdrive_last_backup_at/gdrive_last_backup_status are intentionally
NOT in DEFAULT_SETTINGS (job-written only) — putting them there would
make the generic settings-form save loop blank them out on every
unrelated settings save, since there's no form field for them.
None of the 25 POST forms in the admin panel (delete backup, clear
database, change order status, update settings, etc.) had a CSRF
token — a logged-in admin visiting a malicious page could have
destructive actions silently triggered on their behalf. Add
Flask-WTF's CSRFProtect app-wide and a hidden csrf_token field to
every form. The public API (Telegram webapp + monobank webhook) is
exempted since it authenticates via a custom header, not session
cookies, so it was never CSRF-vulnerable in the first place — exempted
each API sub-blueprint explicitly rather than just the parent, since
CSRFProtect doesn't reliably cascade exemptions through nested
blueprints.
Previously the PWA/home-screen icons (manifest.json) were static PNGs
generated once from whatever logo happened to be set at the time. Add a
/webapp/icons/icon-<size>.png route that renders the current admin logo
(Setting logo_url) on the fly via Pillow, cached by file mtime, so the
icon always matches the company logo without needing a rebuild. Falls
back to the bundled default icon if no logo is set or it's an SVG.
Flask API/admin backend, aiogram bot with delivery/pickup FSM flows,
monobank payment integration, and a Vite/React Telegram Mini App for
menu browsing and cart management.
