Site Systems Inventory — web app

A Flask app over a SQLite database describing each site's vendors.

Run as a container (podman/docker)

A prebuilt image is published at forgejo.vosjes.cloud/yarrith/site-vendor-inventory-demo. Pull and run it:

With podman:

podman pull forgejo.vosjes.cloud/yarrith/site-vendor-inventory-demo:latest
podman run -d --name svi-demo -p 3000:3000 \
  forgejo.vosjes.cloud/yarrith/site-vendor-inventory-demo:latest

With docker:

docker pull forgejo.vosjes.cloud/yarrith/site-vendor-inventory-demo:latest
docker run -d --name svi-demo -p 3000:3000 \
  forgejo.vosjes.cloud/yarrith/site-vendor-inventory-demo:latest

Open http://localhost:3000/. The demo SQLite database is baked into the image, so edits made through the UI live only in that container's writable layer and are lost when the container is removed. To persist them across container recreation, mount a volume over the db instead:

podman run -d --name svi-demo -p 3000:3000 \
  -v svi-demo-db:/app/inventory.db \
  forgejo.vosjes.cloud/yarrith/site-vendor-inventory-demo:latest

# or with docker:
docker run -d --name svi-demo -p 3000:3000 \
  -v svi-demo-db:/app/inventory.db \
  forgejo.vosjes.cloud/yarrith/site-vendor-inventory-demo:latest

Data model

Table	Purpose
sites	One row per site — identity, address, and site-level fields (region, stray notes).
vendors	One row per vendor company (unique name).
categories	One row per category (i, tf, tc, nc, ac, …), with optional parent for sub-categories.
site_vendors	Junction: one row per (site, category, slot) holding the vendor + relationship fields (contract number, support contacts, brand/model, …).

The contract number, support lines, etc. live on site_vendors because they belong to the relationship, not to the vendor or site alone (the same vendor can have a different contract at each site). Categories with two vendors (printers; badging clock + badge supplier) use two rows distinguished by slot.

schema.py holds one field-mapping that drives migration (import), on-screen rendering, and export together, so the flat workbook layout and the relational tables never drift.

Read mode vs. edit mode

Viewing a site (select it on the home page) is read-only — site info plus one card per system showing its vendor and details, and a single Edit button. All editing lives behind that button:

• The edit and New Site forms have, for each system, a Vendor box backed by a datalist of existing vendors — pick an existing one or type a new name (a new vendor row is created automatically; vendors left unused after an edit are cleaned up).
• Linking an existing vendor pre-fills its shared details. Choosing a vendor fills that system's universal fields — product/brand-model, service level, and support phone numbers (technical support, critical / non-critical lines) — copied from the most complete site already using that vendor. Fields that are unique per site — contract number, customer number, DSID/GDN reference, support-contract reference, purchase year — aren't touched. Universal fields are tagged shared on the form.
• Delete lives on the edit form, not the read view.
• Nothing is persisted until you click Save Changes / Create Site.

Find a site

The Sites page (/sites) searches by name and works as a browser search provider, e.g. /sites?q=damogran:

• A unique match (exact, or a single partial match) redirects straight to that site's read-only overview.
• Multiple matches show a table to pick from; no query lists all sites.
• Case-insensitive and partial.

Vendor lookup

The Vendors page (/vendors) lists all vendors (with usage counts); searching shows each matching vendor and a table of every site/category that uses it, with contract and support columns. Site names link straight to the site.

Rename a vendor everywhere

A vendor is a single row, so renaming (the ✎ action) updates all of its site/category links at once. If the new name already matches another vendor, the two are merged (links repointed, duplicate removed).

Edit a vendor's phone / support numbers

The ☎ Edit support numbers button auto-detects the phone numbers in a vendor's records (Belgian formats and +32 …, ignoring bare contract/customer numbers) and shows each with its global reach. Editing one replaces it everywhere it appears across all site_vendors fields. A manual find/replace box handles the rest.

Export

Both exports rebuild the original flat 89-column workbook layout from the normalized data:

• GET /export/xlsx → site_inventory.xlsx (sheet "Low Current", bold frozen header).
• GET /export/csv → site_inventory.csv (;-separated, UTF-8 with BOM).

Backup, restore & import

The Backup page (/backup) manages database backups and bulk data loads. Every snapshot uses SQLite's online backup API, so it is a consistent copy even while the app is writing. Backup files are named inventory-YYYYMMDD-HHMMSS.db.

• Scheduled / manual backup — enable a daily automatic backup at a chosen time, set a retention count (keep the N newest, older ones pruned locally and on SFTP), or use Back up now / Test connection for on-demand runs.

• Destinations — a local folder (which may be an sshfs / network mount), or SFTP via paramiko with username+password or an SSH private key.

• Restore (POST /backup/restore) — from a listed local backup or an uploaded .db. The source is validated (must be a SQLite DB with the sites / vendors / categories / site_vendors tables) and the current database is snapshotted to a pre-restore-*.db first. Filename restores are constrained to the backup folder (no path traversal).

• Import from Excel (POST /backup/import) — upload a workbook shaped like the original "Low Current" sheet (or the app's own export). Columns are matched by header label; rows are matched to sites by name (existing updated, unknown added). A pre-import-*.db snapshot is taken first.

  Import updates matched sites from the whole row, so a column missing from the uploaded file blanks that field. Use a complete workbook.

Running in a container

Everything the feature writes at runtime lives under one folder, BACKUP_HOME — backup_config.json, backup_state.json, the backup files, and the pre-restore / pre-import safety snapshots. It defaults to backups/ next to app.py; set SVI_BACKUP_DIR to relocate it onto a mounted volume (the standalone app keeps config/state next to app.py, so this env var is a demo-repo addition). The deployed instance bind-mounts a host directory as BACKUP_HOME, so config, credentials, run history, and backups all survive a rebuild. The in-process daily scheduler starts at import so it also runs under gunicorn; both workers start a thread, but the first to fire records the run in backup_state.json and the other's next poll skips, so a schedule fires once per day.

Files

File	Purpose
app.py	Flask routes (read / create / update / delete / vendor / search / export / backup).
schema.py	Normalized schema + the field-mapping (migration/render/export).
store.py	Data-access layer: schema DDL and all queries/CRUD.
backup.py	Backup engine (local + SFTP), daily scheduler, restore, validation.
importer.py	Excel → database import (header-matched, upsert by site name).
migrate.py	One-time migration sites.db (JSON) → inventory.db + validation.
templates/	Jinja2 templates.
static/style.css	Styling.
inventory.db	The live normalized database (created by migrate.py).
fields.py	Flat 89-column order + labels; used for export and import matching.
generate_fields.py	Regenerates fields.py if the workbook column layout changes.
backup_config.json	Backup settings — contains secrets. In BACKUP_HOME (see above); not committed.
backup_state.json	Backup/restore run history. In BACKUP_HOME; not committed.