DEV Community

Phinn
Phinn

Posted on

I built a task app that stores everything in one plain SQLite file — and I don't regret skipping Core Data

#swift #ios #sqlite #showdev

This is the engineering story behind KinetTask, a Mac/iOS task app I shipped last week. It's a Show HN-adjacent story but dev.to gets the parts HN would skewer me for. Numbers and regrets included.

The decision everyone told me was wrong

When I started building a local-first task app, every senior Swift dev I asked said the same thing: "Use GRDB. Or SwiftData. Writing SQLite by hand is masochism."

I did it anyway: the SQLite3 C API, directly, in Swift 6.1. Not because I'm tough — because I wanted exactly three things and none of them needed an ORM:

  1. The database file is the export format. Copy tasks.sqlite3 to another machine, it just opens. No "export" feature needed.
  2. Every agent action lands as an append-only journal row — so when the AI planner retries a task, I can diff what actually changed instead of replaying blindly.
  3. Checkpoints store a hash of the normalized artifact. Hash the normalized artifact, not the raw output — timestamps will gaslight you into thinking two identical results differ.

The actual surface area (it's small) 

// The whole "ORM":
// - open with WAL + busy_timeout
// - one prepared statement per query
// - one user_version pragma per schema migration step
Enter fullscreen mode Exit fullscreen mode

That's it. A few tables, WAL mode, prepared statements, PRAGMA user_version for migrations. The entire persistence layer is ~600 lines including the journal and checkpoint logic. GRDB would have been 150 lines of my code plus a dependency I'd have to reason about during every weird concurrency bug.

The honest cost: I wrote two use-after-free bugs against sqlite3_stmt in the first week. C API gives you exactly enough rope.

The bug that justified the whole approach (v1.2.1)

My iOS widget was silently losing every checkbox tap. Root cause, the fun kind:

  • Main app's DB lived in its sandbox Application Support
  • The widget wrote to the App Group container — a different physical file
  • Both files happily existed forever. No crash. No error. Just… widget taps vanishing.

Fix: unify the data root (App Group on iOS), migrate legacy DBs, turn on WAL + busy_timeout(2s) because now two processes share one file, and add a snapshot-reconciliation pass when the main app becomes active — because the widget writing to SQLite doesn't magically invalidate the main app's in-memory cache, and its next UPDATE would happily overwrite the tap with stale state.

None of this was exotic. All of it was invisible until a real user compared widget state with list state. With Core Data this specific bug would've been harder to create (shared store coordinator handles cross-process hints) — that's the fair counterargument, and I'd still make the same trade, because I can sqlite3 tasks.sqlite3 on a user's machine during support and see everything.

What on-device LLM actually feels like

The AI features (natural-language capture, "what should I focus on") run on-device: Apple's foundation model where available, a GGUF model via llama.cpp in-process otherwise.

The real numbers nobody puts in marketing:

  • Cold inference on a 3B model: noticeably dumber than any cloud model. For "parse this sentence into a task with due date" it's fine. For "reorganize my projects" it's… enthusiastic.
  • The failure mode isn't hallucination, it's over-triggering: the model wants to help when it shouldn't. Fix wasn't a better model — it was an anti-filler gate: if the parse adds no fields beyond what you typed, don't create filler structure.

What I'd do differently

  • Ship the widget after verifying both processes write the same file. Obvious in hindsight; it always is.
  • The journal + hash checkpoint design came from my agent-engine side project, and it's the only reason I could debug the retry-drift cases at all. If your app has any automated writes, an append-only log is cheap insurance.
  • I still don't have sync. The file is yours — iCloud Drive it if you want. v1.2 added a .kinettask backup format for machine moves, which covers 90% of what people actually ask for.

Free for 50 tasks, one-time $4.99, no subscription, no account, no telemetry: App Store

Ask me anything about the SQLite internals in the comments — especially if you think skipping GRDB was stupid, I genuinely want that thread.

Top comments (0)