A second is a small unit until you write one down every second of a working day. Eight hours of sampling at 1 Hz is 28,800 rows. A full year of a person at a desk is north of seven million rows, every one small, boring, and exactly like its neighbour. The interesting question for an architect is not how to store seven million rows — any database does that in its sleep — but how to store them on one machine, owned by one person, with no server in the loop, such that the file is still openable, queryable, and trustworthy in 2050.
That's the design problem behind Timex, our automatic time tracker for Mac. Timex is a closed-source product — the app you buy at gettimex.app — but the architecture underneath is the kind of thing every engineer building a desktop tool eventually has to reason about, and none of it is secret. So this post is the design guide, not the source dump. I'll talk about the pattern: local-first, one embedded SQLite file, 1 Hz sampling, and the rollup from raw seconds into the sessions a human actually recognises. The schema and queries below are ones I wrote for this post to illustrate the shape, not a paste of the product.
The Muvon developer stack — Octomind, Octocode, the rest — is open source under Apache-2.0. Timex is the one we keep closed. The architecture is worth telling honestly anyway.
Local-First Is a Decision, Not a Vibe
"Local-first" gets used as a marketing word, which is a shame, because it's a load-bearing architectural decision you commit to on day one.
The decision is this: the source of truth lives on the user's device, and the app is fully functional with the network unplugged. Not "works offline and syncs later" — that's offline-first, a sync architecture with a local cache bolted on. Local-first means there is no later. No server holds the canonical copy. The file on the disk is the product's memory, and everything else is a view of it.
For a single-user desktop time tracker, this is not a hard call. Walk the alternatives:
- A cloud database. Now you operate a server, with a network round-trip on the write path for data generated once per second, forever, by every user. You have an outage budget, a backup strategy, a GDPR surface, a breach risk, and a recurring bill you pass to the user as a subscription. For a tool whose entire job is to record what one person did on one Mac, the server is pure liability.
- A custom binary log. Tempting — append-only, fast, compact. But now you own the format forever: the reader, the migration tool, the compaction logic, the crash-recovery code. The day a user wants to "just query my data," you have to ship a query engine. You've reinvented a database, badly, and locked the user into a format only your code understands.
- An embedded SQL database in a single file. One file. No server. ACID transactions. A query language everyone already knows. A file format stable for over two decades and an official US Library of Congress recommended storage format for datasets. Readable by hundreds of tools you didn't write.
The third option is SQLite, and for a single-writer desktop app it is not a compromise — it's the correct answer. The privacy story people like ("no cloud, no telemetry, your data stays on your Mac") is downstream of this. It isn't a policy we wrote. It's the architecture: there is no endpoint your activity data could leak from, because your activity data never leaves the file. (The one network call Timex makes is license activation — your tracking history plays no part in it.)
Why SQLite Specifically
SQLite is the most deployed database in the world — it's in your phone, your browser, your car — and the reasons it fits a desktop app are worth saying out loud, because they're the same reasons it gets dismissed by people who think only in client-server terms.
It's embedded, not a server. No process to manage, no port to bind, no connection pool, no pg_hba.conf. The database is a library linked into your app; the "connection" is a function call. For a single-user app this removes an entire category of operational concern.
It's one file. Your backup story is cp. Your "export" story is "here is the file." Your sync story, if you ever want one, is "move the file" — with caveats I'll get to. The user can find it, copy it to a USB stick, drop it in a folder that iCloud or Dropbox happens to watch, and nothing in your app breaks. Compare that to explaining to a non-technical user how to export their data from a cloud account.
It's queryable by everyone. This is what makes "own your data" true rather than a slogan. The user's data is in a format the sqlite3 CLI, DB Browser for SQLite, Python's sqlite3 module, DuckDB, Datasette, and roughly every analytics tool on earth can read directly. You're not the gatekeeper of your own users' data; they can answer questions you never built a screen for.
It's durable and transactional. SQLite is ACID. A COMMIT either fully happens or doesn't, even if the power dies mid-write. For a tool recording a continuous stream, "what happens to the database if the lid slams shut at the wrong microsecond" has a boring, correct answer instead of a corrupted file.
The objection you'll hear is "SQLite doesn't scale / doesn't do concurrent writers." Both true and both irrelevant here. A single-user desktop app has exactly one writer: itself. The thing SQLite is bad at — many machines hammering writes over a network — this architecture never does. The access pattern here is one writer, append-mostly, occasional analytical reads, which is SQLite's home turf.
The 1 Hz Sampling Loop
The tracker's job is to answer "what was the user doing" continuously. The approach is a sampler: once per second, ask the OS three questions.
- What application is frontmost?
- What is the title of its focused window?
- (With permission, for browsers) what is the active tab's URL?
On macOS those answers come from the frontmost-application API and the Accessibility (AXUIElement) tree — the same permission that exposes window titles also exposes the browser's address bar, which is why Timex reads tab URLs without a separate Automation prompt for most browsers. None of that is proprietary; it's the documented macOS surface any tracker uses. What you do with the samples is where the design lives.
The naive implementation writes one row per sample: one second, one row. It's correct and wasteful — 28,800 identical-ish rows for an 8-hour day where you spent two hours in one editor window. Storing "editor, editor, editor, …" 7,200 times is not data, it's a stuck pixel.
The better model treats the second-by-second stream as the input and a segment as the output. A segment is a contiguous run where the answer to all three questions stayed the same. You keep the currently-open segment in memory — (bundleId, title, url, startAt) — and on each tick compare the new sample to it:
- Same context? Extend the open segment. Nothing hits the disk.
- Context changed? Close the open segment (stamp its
endAt, computeduration), write it, and open a new one.
So a two-hour editor session is one row with a two-hour duration, not 7,200 rows. The disk only sees a write when something actually changes — which, for real human work, is far less often than once a second. The 1 Hz loop gives you resolution; the segment model gives you sane storage.
tick (1 Hz) ─▶ sample(app, title, url)
│
├─ equals open segment? ──▶ extend in memory, no write
│
└─ differs? ──▶ close + flush open segment ──▶ open new
Idle Is Not a Context
A sampler that runs blind will happily record eight hours of "editor" because the editor was frontmost while you were at lunch. That data is a lie, and a time tracker that lies is worse than no time tracker.
So you need an idle gate: track input activity, and after some threshold of no keyboard or mouse (Timex uses two minutes), stop attributing time to the foreground app. The honest way to model this is to treat idle as a state transition that closes the active segment, exactly like a context switch. When input resumes, a fresh segment opens. The gap between them is real — time the machine was on and the human wasn't — and you can keep that gap as a first-class fact in the schema or infer it from the hole between segments — Timex does the latter.
There's a judgment call buried here: the seconds just before you go idle still get attributed to whatever was frontmost. There's no perfect answer — you can't know the user got up until they don't come back — so the design picks a threshold and documents it. Honesty about the fuzzy edge beats a false precision that pretends sampling can read minds.
A Schema for Time Intervals
Here's an illustrative schema — mine, written for this post — that captures the segment model. It's the shape, not the product's actual DDL, but it's a perfectly reasonable thing to build.
-- Closed activity segments: the rolled-up unit, not raw samples.
CREATE TABLE activity (
id INTEGER PRIMARY KEY,
bundle_id TEXT NOT NULL, -- e.g. com.apple.dt.Xcode
app_name TEXT NOT NULL,
title TEXT, -- focused window title (nullable)
url TEXT, -- active tab, browsers only
domain TEXT, -- extracted host, for fast grouping
started_at INTEGER NOT NULL, -- unix epoch seconds
ended_at INTEGER NOT NULL,
duration INTEGER NOT NULL, -- ended_at - started_at, denormalised
category_id INTEGER REFERENCES category(id)
);
CREATE INDEX idx_activity_started ON activity(started_at);
CREATE INDEX idx_activity_bundle ON activity(bundle_id);
CREATE INDEX idx_activity_domain ON activity(domain) WHERE domain IS NOT NULL;
CREATE TABLE category (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
color TEXT NOT NULL, -- hex
score INTEGER NOT NULL -- productivity weight, -100..100
);
-- Map an app to a default category. User-overridable.
CREATE TABLE app_category (
bundle_id TEXT PRIMARY KEY,
category_id INTEGER NOT NULL REFERENCES category(id)
);
Three design choices in there are worth defending:
Store closed intervals, not raw ticks. The activity table is segments. Per-second samples never get persisted — they live for one tick in memory and fold into the open segment. That's the difference between a database growing ~7M rows a year and one growing by however many times you actually switched windows, which for most people is one or two orders of magnitude smaller.
Denormalise duration. It's derivable from ended_at - started_at, and storing it anyway is a deliberate violation of "don't store what you can compute." The justification: every query the UI runs sums durations, and a stored, indexed integer beats recomputing a subtraction across millions of rows on every "where did my week go" question. Denormalise what you read constantly; derive what you read rarely.
Half-open intervals [started_at, ended_at). Start inclusive, end exclusive — the boring-but-correct convention for time ranges. Two adjacent segments share a boundary timestamp with no overlap and no gap, and "does this segment fall in this day" is a clean started_at < day_end AND ended_at > day_start with no off-by-one at midnight. Get this wrong and every aggregation is subtly haunted.
What I'm not showing is the product's real migration history, its exact indexes, or its category-seeding logic — that's proprietary, and frankly the boring part. The pattern above is the transferable knowledge.
Queries You Can Run on Your Own File
This is where "own your data" stops being a slogan. Because the store is a plain SQLite file, the user — not just the app — can ask it questions. Here are queries against the illustrative schema above, runnable with the sqlite3 CLI on your own database.
Where did today go, by app:
SELECT app_name, SUM(duration) / 3600.0 AS hours
FROM activity
WHERE started_at >= strftime('%s', 'now', 'start of day')
GROUP BY bundle_id
ORDER BY hours DESC;
Top websites this week:
SELECT domain, SUM(duration) / 60 AS minutes
FROM activity
WHERE domain IS NOT NULL
AND started_at >= strftime('%s', 'now', '-7 days')
GROUP BY domain
ORDER BY minutes DESC
LIMIT 20;
Focused vs. distracted time, using the category weights:
SELECT c.name,
SUM(a.duration) / 3600.0 AS hours
FROM activity a
JOIN category c ON c.id = a.category_id
WHERE a.started_at >= strftime('%s', 'now', '-30 days')
GROUP BY c.id
ORDER BY hours DESC;
None of these required a feature request, a CSV export, or an API. Pipe the file into DuckDB for window functions, Datasette for a web UI, a pandas notebook for charts. The product shipped a Today view; the data shipped the ability to answer everything else. That's the dividend of choosing a format the whole world can read.
Durability: WAL, Crashes, and File Growth
A continuously-writing app on a laptop that gets its lid slammed shut needs an answer to "what happens to the file when the process dies mid-write." SQLite's answer is good, but you have to opt into the good version.
Use WAL mode. The default rollback journal serialises readers against the writer and does more fsync work. Write-Ahead Logging (PRAGMA journal_mode=WAL) writes changes to a -wal sidecar file and lets reads proceed concurrently with the single writer — exactly the shape of a tracker appending segments while the UI reads them to draw the timeline. This is why you'll see three files on disk: timex.sqlite, timex.sqlite-wal, and timex.sqlite-shm. That's not corruption; that's WAL doing its job, periodically checkpointing back into the main file.
Crash safety is real, but tune the sync. With synchronous=NORMAL under WAL, a power loss can cost you the last un-checkpointed transaction but cannot corrupt the database — a trade most desktop apps should take, because losing the last few seconds of "you were in your editor" is a rounding error. synchronous=FULL is safer and slower; for second-resolution activity data, NORMAL is the sane default. What you must not do is run with synchronisation off to chase benchmark numbers and then act surprised when a kernel panic eats the file.
Plan for growth and retention. Segments grow slowly, but they grow forever, and "forever" is a long time on a 256 GB laptop. The honest design gives the user a retention knob — keep raw segments for N months, optionally roll older data into coarser daily aggregates, and VACUUM occasionally to reclaim space. You don't have to build it all on day one, but you should know where it goes, because "the database that only grows" is a slow-motion bug.
The Honest Tradeoffs of Local-First
I'd be selling the same fairy tale I'm criticising if I stopped at the upside. Local-first buys you ownership and privacy by spending two things, and you should know what they cost.
No built-in sync. If a person uses a desktop and a laptop, each machine has its own file and its own truth. There's no server reconciling them, because the absence of that server is the whole point. The bring-your-own answer is to put the file in a folder that iCloud or Dropbox syncs — but that's file sync, not database sync, and SQLite explicitly warns that a WAL database does not play nicely with naive file-sync tools that copy the .sqlite while the -wal is mid-flight. It works if the app is closed when the file moves; it corrupts if two machines write the same folder at once. So the honest position is: local-first gives you one perfect copy per machine, and multi-device is a separate, opt-in feature you build deliberately or not at all.
Backup is the user's job — so make it trivial. With no cloud, nobody backs up the user's history but the user. The mitigation is that the backup is one file you can copy, the easiest backup story in software. But the product has to say so plainly instead of pretending the cloud has their back, because it doesn't, by design.
And here's the part worth being blunt about: the moment you want real multi-device sync, you have signed up for one of the genuinely hard problems in computing. Syncing a database across devices that edit independently and go offline is the domain of conflict resolution, CRDTs, vector clocks, and last-writer-wins regret. It is not "just rsync the file." A lot of products that start local-first and then bolt on sync end up rebuilding the cloud database they were proud of avoiding — now with a distributed-systems bug surface on top. The defensible move is to ship local-first as the honest default and keep the door open for opt-in sync as a deliberate, separately-engineered feature. The day the canonical copy lives somewhere other than the user's disk, you're not local-first — you're a cloud app with a nice offline cache, which is a fine thing to be, just not the thing you said you were.
What This Buys the User
Strip it all down and the architecture makes exactly one promise: the file is yours. It survives the company getting acquired, the wifi being down on a flight, the next breach headline — because your tracking history isn't sitting on anyone's server to be breached. It's readable by tools that won't exist for another decade, because the format predates most of the ones that exist now. No account to lock you out, no subscription whose lapse erases your history, no export wizard to beg your own data through.
That's the entire value proposition of local-first, and it's why we built Timex on one SQLite file instead of a backend we'd have to host, secure, and eventually shut down. The same instinct runs through the open-source Muvon stack — your code, your tools, your repo — and the one closed product we ship: your time, your file, your machine.
A second is a small unit. Seven million of them, on a file you own and can query with any tool on earth, turns out to be the difference between "where did my year go" being a rhetorical question and an answerable one.
— Vladimir
FAQ
Why SQLite instead of a cloud database for a desktop app?
Because the access pattern is one writer on one machine, append-mostly, with occasional analytical reads — which is precisely where an embedded single-file database shines and a client-server database is pure operational overhead. No server for your data means no outage budget, no recurring bill, no breach surface holding your history, and a backup story that's a file copy.
Can I really query my own data?
Yes — that's the point of choosing SQLite. The file opens in the sqlite3 CLI, DB Browser, DuckDB, Datasette, a Python notebook, or anything that reads SQLite. The illustrative queries in this post run against the schema shown; adapt them to your file.
Is local-first the same as offline-first?
No. Offline-first is a cloud app with a local cache that syncs when it can — the server still holds the canonical copy. Local-first means the device holds the source of truth and there is no canonical server copy at all. Different architecture, different guarantees.
What's the catch with local-first?
No built-in multi-device sync, and backup is on you. Both are real costs. The mitigation is that "your data" is a single file you can copy anywhere — but if you need devices to merge edits automatically, that's a separate hard problem (conflict resolution, CRDTs) that no amount of file-copying solves.
Is Timex open source?
No. Timex is a closed-source product. Muvon's developer tooling — Octomind, Octocode, and the rest — is open source under Apache-2.0. This post is about the architecture and patterns, which are general engineering knowledge, not the product's source.
Timex is a closed-source, local-first automatic time tracker for Mac: 1 Hz sampling into one local SQLite file you own — no cloud storage of your activity, no telemetry on what you do; the only network call is license activation. The schema and queries here are illustrative and written for this post, not the product's source. Muvon's developer stack — Octomind and friends — is open source under Apache-2.0.
