DEV Community

Cover image for Money in SQLite: building an offline-first expense tracker with Expo
Kasim Rangwala for Spendwise

Posted on

Money in SQLite: building an offline-first expense tracker with Expo

Most expense trackers treat your spending history as a server-side asset. You sign up, the app syncs, and the ledger lives somewhere you cannot see. I wanted the opposite: the ledger is a SQLite file on the phone, and the app is a reader and writer for it.

That constraint changes the design. This post covers the four decisions that mattered most while building Spendwise, a local-first expense tracker for Android: how money is stored, how the schema evolves, how reads stay scoped, and how exports work without a spreadsheet dependency.

1. Money never touches a float

The single rule that prevents most finance bugs: money is an integer in minor units. A coffee is 350, not 3.5. Currency decimals come from a table, because not every currency has two:

export function parseAmountToMinor(amount: string, currencyCode: CurrencyCode): number {
  if (amount.includes('-')) {
    throw new AppError('VALIDATION_ERROR', 'errors.invalidAmount');
  }
  const decimals = getCurrencyDecimals(currencyCode);
  const value = Number.parseFloat(normalizeAmountInput(amount, decimals));

  if (!Number.isFinite(value) || value < 0) {
    throw new AppError('VALIDATION_ERROR', 'errors.invalidAmount');
  }

  return Math.round(value * 10 ** decimals);
}
Enter fullscreen mode Exit fullscreen mode

Number.parseFloat appears here exactly once, at the boundary where text becomes a number, and the result is immediately rounded to an integer. Every sum, budget comparison and balance after that is integer arithmetic. 0.1 + 0.2 never enters the ledger.

The other half is input normalization. The decimal keypad follows the device locale, so 12,50 arrives as often as 12.50, and an Arabic or Persian keypad types its own digits and separators. Stripping commas — the obvious fix — turned 12,50 into 1250, a 100× error. The parser caches the locale's grouping and decimal separators from Intl.NumberFormat().formatToParts() and strips only the grouping character.

Rejecting the minus sign before cleaning is deliberate too. A negative amount is not a valid transaction in this app; if the sign is silently dropped, a refund becomes an expense.

2. Migrations are a list, not a diff

expo-sqlite gives you openDatabaseAsync, execAsync and prepared statements. It does not give you a migration framework. The whole mechanism is a version table and an ordered array:

async function runMigrations(db: SQLite.SQLiteDatabase): Promise<void> {
  await db.execAsync(`
    CREATE TABLE IF NOT EXISTS schema_migrations (
      version INTEGER PRIMARY KEY NOT NULL
    );
  `);

  const row = await db.getFirstAsync<{ version: number }>(
    'SELECT MAX(version) as version FROM schema_migrations',
  );
  const currentVersion = row?.version ?? 0;

  for (const migration of migrations) {
    if (migration.version > currentVersion) {
      await migration.up(db);
      await db.runAsync('INSERT INTO schema_migrations (version) VALUES (?)', migration.version);
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

Each migration is a module exporting version and up. They are imported and listed in client.ts in order — the list is the source of truth, not the filesystem. That catches the class of bug where a bundled app cannot enumerate its own assets and silently skips a migration.

Two rules keep this safe:

  • Never edit a released migration. Add a new numbered one. An app on someone's phone already ran the old body; changing it means two users have two different schemas with the same version number.
  • One database handle. A module-level singleton with an initPromise guard means concurrent callers during startup wait for the same migration run instead of racing it.

The schema is ordinary SQLite: transactions, categories, budgets, recurring rules, people, groups, attachments. Nothing exotic, which is the point — the queries are portable, and the data can be read with any SQLite browser.

3. Scoping needs one predicate, not one per query

Once groups exist — a trip, a shared household — every scoped table has a nullable group_id. The app-level context is NULL, meaning "not in a group". The trap follows immediately:

SELECT * FROM transactions WHERE group_id = ?; -- never matches NULL
Enter fullscreen mode Exit fullscreen mode

= NULL is never true in SQL, so a scoped read written with = silently returns nothing for the app-level ledger, and a read that forgets the predicate silently returns every group's rows. Both failures are quiet, which is the worst kind.

The fix is a single helper that every repository uses:

export function scopeFilter(column: string, groupId: GroupScope = null): ScopeFilter {
  return groupId === null
    ? { sql: `${column} IS NULL`, params: [] }
    : { sql: `${column} = ?`, params: [groupId] };
}
Enter fullscreen mode Exit fullscreen mode

It returns SQL plus the params to bind, in order, so callers compose rather than concatenate. The column name is always a literal written in the repository layer — never user input. Reads take groupId: GroupScope = null, so an existing app-level call keeps its meaning and a group read is opt-in at the call site.

The rule that makes this stick: no hand-written scope predicates. If a repository needs one, it calls the helper. Reviewing a diff for group_id used without it is mechanical, which is exactly what you want in the one place a mistake leaks another context's data.

4. Exports: self-contained PDF, hand-written XLSX

Two export paths, both built on the same data model.

PDF goes through expo-print, which renders a self-contained HTML document with the platform's own engine. Every currency symbol the app supports prints correctly without embedding a font, and the file needs no network. The charts are drawn inline in HTML rather than reusing the on-screen react-native-svg charts, because a print document cannot share a native view tree.

XLSX was the interesting one. An .xlsx is a ZIP of XML parts. A spreadsheet library is roughly a megabyte of dependency for one export button, so the app builds the parts it needs — workbook, styles, one sheet per report table — and writes the ZIP itself with stored (uncompressed) entries.

Two details that decide whether Excel opens the result:

  • Control characters are not legal in XML 1.0. Notes are free text and can carry anything, so they are stripped from every cell rather than trusted.
  • Money is written as a plain decimal string from the formatter (minorToDisplayAmount), flagged numeric, never as a float computed on the way out. The formatting layer is the only place integer minor units become 12.50.

The result is a few hundred lines, no native module, and the same content as the PDF.

What this buys, and what it costs

Buys: the app works in airplane mode, the first launch has no signup, and backing up means keeping a file. There is no server to breach and no sync conflict to resolve.

Costs: multi-device sync is a feature you have to build deliberately, not something the database gives you. Device loss is real unless the user exports or backs up. And every schema change needs a migration that runs correctly on an old database — the cost is paid per release, not once.

For a personal finance app, the trade is worth it. The ledger is the product; it should live where the user can see it.

If you want to see the result, Spendwise is free on Google Play. I write about the internals when something is worth sharing; the local-first article on the site goes deeper on the data model.

Questions about the migration runner or the scope helper are welcome in the comments — especially if you have hit the = NULL bug in your own schema.

Top comments (0)