OvertimeIQ: Privacy-First Time Tracking
An overtime tracker where the work data lives in a SQLite file the user owns on their own Google Drive — not on my servers. Sign-in is Google OAuth with PKCE. A thin server exists only for the two things that genuinely cannot run in a browser, and finding out exactly what those were is the interesting part.
Overview
Punch in, punch out, or log a shift by hand; the app works out what you are owed using per-job hourly rates with weekend and holiday multipliers. The premise was that a tool which knows your working hours should not need to hold them, so the database is a SQLite file compiled to WebAssembly, running in your browser tab, syncing to a file in your own Google Drive.
Where the data actually lives
In the tab, in a WASM SQLite instance, persisted to local storage between reloads and pushed to Drive on a debounce. The Drive scope is the narrow one — the app can only see the single file it created, not your other documents. Two details cost real time. Writes are split into user data, which arms the upload debounce, and sync bookkeeping, which does not, because otherwise recording the sync triggers another sync. And on a cold start the Drive copy is fetched into local storage before the database is initialised, after a throwaway instance is spun up purely to read the stored refresh token — without that ordering a fresh browser creates an empty database and cheerfully overwrites good data with it.
Where zero-backend hits the wall
Two walls, and neither is the one I expected. The first is Google's: a Web-application OAuth client requires a client secret at the token endpoint even in a PKCE flow, so the code exchange and every hourly refresh have to happen server-side. The client-side refresh function I wrote first is still in the file, defined and never called — I left it there as the fossil of the assumption. The second wall is trust: you cannot gate a paid feature on a database the user owns and can edit. So entitlement is a short-lived signed token minted server-side from subscription state, verified against a public key compiled into the bundle rather than stored in the database, precisely so that swapping the key means editing the app rather than editing your own data.
The parts that were harder than the storage
A shift that crosses midnight has to be priced as two segments, because the multiplier belongs to the calendar day and a Sunday night into a Monday morning is not one rate. Sync compares the Drive modification time against the last local sync with a tolerance for clock skew, because two clocks that disagree by eight seconds will otherwise ping-pong a file between devices forever. And the database schema is re-asserted idempotently on every boot rather than versioned — which is fine while there is one user and will not be later.
Honest scope
A personal project, twenty-seven commits, April to August 2026, no tests. Conflict resolution is whole-file last-writer-wins with no row-level merge, so simultaneous edits on two devices lose one side. Daylight-saving transitions are not handled — the arithmetic is in integer minutes and a DST day is costed as twenty-four hours. Spreadsheet and PDF export are dependencies with no code behind them yet. I wrote the whole thing up publicly on dev.to while building it, including the article about where the strategy stops working, which is the half worth reading.