Tech
How the data storage works
The usual blueprint for an application like this is a server with a database and an account per user. I wanted the opposite: the data belongs to the business and lives as a single file on its own computer. For that, the database has to go where the data is — into the browser.
1. A file instead of a server
The app is nothing but static HTML, CSS and JavaScript, served from GitHub Pages. There is no server that ever sees the records. The database is SQLite compiled to a WebAssembly module (@sqlite.org/sqlite-wasm), bundled locally with the app, hashed and versioned at build time like every other file. Nothing is fetched from a third-party CDN at runtime — which is why the database works offline as well.
One detail that is easy to miss: the file on the user's disk is a real SQLite database, not a home-made export format. It opens in any SQLite tool, and it can be backed up, copied and taken to another machine.
2. Three layers
The database never runs in the browser's main thread. It lives in its own Web Worker so the interface stays responsive even during a large import. Between the two there are only messages.
Interface · main thread
Astro, TypeScript, Bootstrap
- Forms, calendar, lists, export (PDF, CSV)
- State and event bus
- Talks to the database only through messages
- Holds the file handle and writes the file
⇄postMessage
ID, timeout,
transfer of large buffers
Database · Web Worker
SQLite 3.54 as WebAssembly
- Schema with migrations (
user_version 1 to 11)
- WAL, foreign keys, 20 MB page cache
- All queries, paged lists, BVL import
- Exports the whole database as bytes
⇄bytes
export · import
Storage · with the user
One .sqlite file
- File System Access API, handle kept in IndexedDB for auto-start
- OPFS mode when the browser allows it
- Fallbacks: JSON file, localStorage, memory
3. Saving, step by step
- An input, say a new application, is sent to the worker as a message. Every message carries an ID and a timeout; the reply comes back asynchronously.
- The worker writes to its SQLite database. That database lives in the worker's memory or, if the browser allows it, in the Origin Private File System (OPFS).
- The worker then exports the complete database as a byte sequence (
sqlite3_js_db_export) and hands the buffer to the main thread without copying it (Transferable).
- The main thread writes the bytes through the file handle into the user's .sqlite file (
createWritable, write, close). The file on disk is therefore always a complete, consistent database.
- Changes in quick succession are batched: if a write is already running, the app only notes that it has to write once more afterwards. One write per burst of changes instead of one per keystroke.
4. Opening: content, not file extension
When opening, the app reads the file as bytes and checks the signature at the start: SQLite format 3. The content decides, not the extension. A file may be called .json and still be a binary database — earlier versions saved as JSON, and exactly this mix once made an intact database look “empty”. Since then the signature rules.
If it is SQLite, the worker turns the file into the active database via sqlite3_deserialize; in OPFS mode OpfsDb.importDb imports it into the persistent file. If it is JSON, the data is imported as a snapshot. Both routes end in the same database, and the migrations run either way.
5. The BVL data: a pipeline of its own
The official approval database of the BVL (Germany's Federal Office of Consumer Protection and Food Safety) is reachable through a public API, but not made for the browser: many endpoints, many requests, no offline use. So the preparation happens up front in a pipeline of its own, the public repository pflanzenschutz-db:
- Every two days a GitHub Actions run fetches the data from all BVL endpoints, validates it and enriches it — among other things with organic-use flags, active-substance names and hazard statements.
- The result is one SQLite file, compressed with Brotli and as ZIP, published through GitHub Pages together with a manifest listing size and SHA-256 checksum per file.
- The app loads the manifest, picks the smallest format the browser can decompress, shows progress and hands the bytes to the worker.
- The worker opens the download as a temporary in-memory database and copies its tables into the user's database inside one transaction — in dependency order, so the foreign keys hold.
- After that the complete catalogue sits inside the user's own .sqlite file and can be searched offline. A log inside the database records which release was imported.
| As of 25 Oct 2026 | Value |
|---|
| Products in the catalogue | 2,165 |
| Applications (product per crop and pest) | 30,836 |
| Active substances | 482 |
| Download, Brotli / ZIP | 6.8 MB / 12.5 MB |
| Unpacked (SQLite) | 113 MB |
6. Offline and as an app
A service worker precaches all hashed build files on install, WebAssembly module included. After that the app starts without a network. Every build stamps a new cache version into the service worker; without that step, installed users would be stuck on old code, because a browser never reinstalls a byte-identical service worker.
As an installed desktop app, Digitale PSM registers through the File Handling API for .sqlite, .db and .json: a double-click in the file explorer opens the database straight in the app. The last file handle is kept in IndexedDB; on the next start the app only asks for permission and reopens the same file.
7. Performance and robustness
- No copying of large buffers. Database exports and the BVL import move between threads by transfer, not as structured clones. At 100 MB that is the difference between smooth and frozen.
- Timeouts per action. Normal calls get 30 seconds, the BVL import up to eight minutes, because browsers throttle timers in background tabs. If the worker crashes, every pending call is rejected immediately instead of hanging forever.
- Pages instead of everything. History, products and GPS points load page by page with a cursor; the interface renders only the visible rows.
- One data-loss trap, closed. In SQLite mode the worker maintains the history directly, and it is no longer sent along in the state snapshot. Otherwise saving a setting would have written back the stale list and deleted every entry recorded since the file was opened.
8. Limits, honestly
- Desktop first. File access through the File System Access API exists only in Chromium browsers on the desktop (Chrome, Edge). Phones lack it, Safari on the iPhone as much as Chrome on Android: no file can be opened there, and a newly created database lives only until the page is reloaded. A phone version needs a different storage path (OPFS or IndexedDB) and has not been built yet.
- OPFS needs isolation. Persistent storage inside the worker via OPFS requires cross-origin isolation (COOP/COEP headers), which GitHub Pages cannot set. In production the database therefore runs in the worker's memory; the truth lives in the file on disk.
- Whole file, no delta. Every save rewrites the complete file. For the business records themselves that is harmless; with the BVL catalogue imported, however, the file is over 100 MB, and then every save takes noticeable time. This is deliberately kept simple; the next step would be a page-level delta.
9. Fact sheet
| App | digitale-psm.de — free, no ads, no account |
| Language | German (the app follows German approval law and the BVL database) |
| Since | April 2025, currently version 2.0 |
| Licence & code | MIT, source code on GitHub |
| Stack | Astro 5, TypeScript, Bootstrap 5 (bundled locally), SQLite 3.54 as WebAssembly, Web Worker, service worker, web app manifest |
| Storage | SQLite file with the user via the File System Access API; OPFS when available; fallbacks JSON file and localStorage |
| Approval data | BVL API, prepared in pflanzenschutz-db (GitHub Actions, every two days), delivered as Brotli/ZIP with manifest and SHA-256 |
| Hosting | Static via GitHub Pages, build and deploy through GitHub Actions |
| Related | st.digitale-psm.de: statistics dashboard for the BVL database, SQL queries right in the browser |
