Offline-first aquarium log in Flutter with Drift and Supabase

This post is about the data model behind my aquarium app: readings where a blank field never turns into a zero, free ammonia worked out from pH and temperature, one stored unit per parameter, and a local Drift database that syncs to Supabase through an outbox. The app is Aquarium - Log Tracker, a free iPhone logbook for water tests, written in Flutter. What it does for fishkeepers is in the user-facing post; this one is the code.

Blank is not zero

A water test is a handful of numbers typed standing at a tank, often with wet hands, and nobody tests everything every time. So the first real decision was what an empty field means. The answer is nothing. There is no nullable value column: a parameter you didn't test produces no row at all.

for (final setting in settings) {
  final text = _controllers[setting.key]?.text.trim() ?? '';
  // An untouched field produces no reading at all. Never zero.
  if (text.isEmpty) continue;

  final value = double.tryParse(text.replaceAll(',', '.'));
  if (value == null) {
    setState(() => _error = '${setting.definition.displayName}: "$text" is not a number');
    return;
  }

  entries.add(ReadingDraft(parameterKey: setting.key, value: value,
      unit: _units[setting.key] ?? ref.read(unitsProvider).unitFor(setting.definition)));
}

The previous value sits in each field as hintText, not as text, so it's a reference you can't save by accident.

This matters most for rate of change. Each saved reading is compared with the latest reading of the same parameter, which is by construction the last time that thing was actually measured. The default reef rules allow alkalinity to move 0.5 dKH a day. Store a skipped test as zero and the next honest 8.4 dKH looks like the tank jumped 8.4 dKH overnight. Two related rules: no daily rate from readings less than 12 hours apart, because over an hour the difference is mostly the test kit, and no pH drift limit for planted tanks, where CO₂ injection swings pH every day on purpose.

Charts follow from the same model. Each parameter gets its own row and scale on a shared time axis, with points spaced by date and a dot on every real measurement. A month without a nitrate test is a long straight segment between two dots, not a dip to zero. That segment is still an interpolation the eye can read as data; making the dots obvious is the best answer I have.

Absence of a measurement is not a measurement

Nulls do appear in one place: when rows are folded into per-session snapshots to work out where a new tank is in its nitrogen cycle. A snapshot holds nullable ammonia, nitrite, nitrate, pH, KH and temperature, and a null never counts as clear or present.

bool get ammoniaClear => ammonia != null && ammonia! <= Cycling.effectivelyZero;
bool get nitriteClear => nitrite != null && nitrite! <= Cycling.effectivelyZero;
bool get nitratePresent => nitrate != null && nitrate! > 0;

bool get passesCompletionTest => ammoniaClear && nitriteClear && nitratePresent;

// In Cycling.assess: the stage comes from the newest session that measured
// ammonia or nitrite, not simply the newest session.
final latest = sorted.lastWhere(
  (s) => s.ammonia != null || s.nitrite != null,
  orElse: () => sorted.last,
);

A tank counts as cycled after two passing sessions in a row, because one clean result can be a misread. Nitrate is required so that "nothing was ever dosed" can't pass for "the bacteria are working". "Clear" means at or below 0.25 ppm, because my research notes flag a common hobby kit as reading about that on clean water.

The lastWhere exists because of a bug I found by looking at the screen, not with a test. After logging a single alkalinity reading, the cycling banner dropped back to "Setting up": the newest snapshot had no ammonia in it, and the code took that as the cycle going backwards. Stall detection had the other half of the bug. KH and pH are often tested on a different day from ammonia, so each check now uses the latest session that measured that particular thing.

Free ammonia from pH and temperature

Kits report total ammonia. The seriously toxic part is un-ionised NH₃, and its share depends on pH and temperature, as the EPA's ammonia page explains. When the user has typed all three into the same session, the rest is arithmetic.

/// pKa = 0.09018 + 2729.92 / T(K). At 25 C this gives ~9.25.
static double pKa(double temperatureC) =>
    0.09018 + 2729.92 / (temperatureC + 273.15);

/// Fraction of total ammonia present as toxic, un-ionised NH3 (0..1).
static double freeFraction({required double ph, required double temperatureC}) =>
    1 / (1 + math.pow(10, pKa(temperatureC) - ph));

static double freeAmmoniaPpm({
  required double totalAmmoniaPpm,
  required double ph,
  required double temperatureC,
}) => totalAmmoniaPpm * freeFraction(ph: ph, temperatureC: temperatureC);

The pKa expression is the same as in the Florida Department of Environmental Protection's procedure for calculating un-ionized ammonia, which writes the constant as 0.0901821, credits Thurston et al., and gives it as valid from 0 to 50 °C and pH 6 to 10. At 25 °C it puts about 0.56% of total ammonia in the free form at pH 7 and 5.4% at pH 8, matching the table in that document. One test pins down why the feature exists: 0.5 ppm total ammonia is 0.0017 ppm free at pH 6.8 and 24 °C, and 0.054 ppm at pH 8.3 and 26 °C. Same kit reading, about 33 times apart.

The result appears only when the saved session has ammonia, pH and temperature together; it doesn't borrow last week's pH. The known limitation: the expression is for fresh water. The same document says the free fraction drops slightly as ionic strength rises and gives a separate correction for saline water, which the app doesn't apply, so in a reef tank it errs high. It calculates; it doesn't diagnose.

One stored unit per parameter

Each reading keeps the value in the parameter's canonical unit and, beside it, exactly what the user typed and in which unit, so a conversion bug can't destroy the original observation. Nitrate is stored as the NO₃ ion, alkalinity in meq/L, salinity in ppt, temperature in °C. The display unit is a device setting, since it's about how this person reads their kit, not a fact about the tank. Grading uses the canonical value, so a reading in range stays in range whatever it's shown as.

The ratios are where silent errors live. NO₃ is 4.427 times NO₃-N, so the same water reads 10 on one kit and 44 on another. Nitrite has its own ratio, 3.284. One meq/L is 2.8 dKH. An unknown unit throws instead of passing the number through, because an unconverted alkalinity is off by 2.8 times and still looks plausible on a chart. I caught myself quoting nitrate's 4.4 on the nitrite row, on a screen whose whole job is preventing unit mistakes. Both figures now come from constants pinned by tests.

Drift as the source of truth, with an outbox

The UI reads only from Drift. Every write goes into its table and into an outbox table in the same transaction, so nothing appears on screen without also being queued for the server.

await _db.transaction(() async {
  // ...the session row and its outbox entry first, then for each reading:
  await _db.into(_db.readings).insert(ReadingsCompanion.insert(
    id: id,
    tankId: draft.tankId,
    sessionId: Value(sessionId),
    parameterKey: entry.parameterKey,
    valueCanonical: canonical,
    enteredValue: entry.value, enteredUnit: entry.unit,
    measuredAt: draft.measuredAt,
    updatedAt: stamp,
  ));
  await _outbox.enqueue(entity: SyncEntity.readings, entityId: id, op: SyncOp.upsert,
      payload: {/* the same row as JSON */});
});

The outbox stores the payload rather than re-reading the row later, so an edit can't change what an earlier queued write meant. IDs are UUID v7 made on the device, so a reading can reference a tank that hasn't synced yet. Deletes set deleted_at, because a delta sync can't express a row that stopped existing.

The sync engine is written against a small SyncRemote interface and tested against a fake, on an in-memory SQLite database rather than a mock. It pushes before it pulls, parents before children. The pull asks for rows with updated_at after the stored cursor minus two seconds, because a transaction that began before the cursor can commit after it. The cursor comes from server rows, stamped by a Postgres trigger, never from the device clock, and a pulled row is skipped while the same row has an unpushed local edit. Drift reads DateTimes back in local time by default, so I set store_date_time_values_as_text, which keeps UTC as UTC.

What went wrong:

• Against a project that took fourteen seconds to answer auth, eight changes were dead-lettered and shown as refused by the server. They hadn't been. Each timeout counted as an attempt across launches and resumes, and after five the tank was abandoned; its parameter settings then pointed at a tank the server had never seen, row level security refused them, and they went too. Now being offline never uses up attempts, a pass stops before anything depending on an abandoned row, and dead letters have a "Try again".
• Sync only ran while the account screen was open, because only that screen watched the provider. It's now watched at the app root and invalidated on resume.
• The tank screen kept showing "not tested yet" after a test was logged, until a restart. The query watched only the settings table but attached the last reading from another one; Drift's readsFrom fixed it. The tests missed it because they called .first after writing. The new one subscribes first.

Row level security, briefly

Supabase only holds a copy for people who sign in, and row level security is the real boundary; the publishable key in the app authorises nothing on its own. Tanks carry an owner_id, and a tank_members table that every policy already reads through means sharing can come later without a migration. Every child table carries tank_id, so one pair of policies covers all of them:

do $$
declare t text;
begin
  foreach t in array array[
    'tank_parameter_config','test_sessions','readings','livestock',
    'task_templates','task_events','journal_entries','photos','cycle_logs'
  ] loop
    execute format($f$
      create policy %1$s_select on public.%1$s
      for select to authenticated
      using (tank_id = any (array(select private.accessible_tank_ids())))
    $f$, t);
    -- plus a matching %1$s_write policy using private.can_edit_tank(tank_id)
  end loop;
end $$;

The helpers are security definer functions in a private schema with an empty search_path, revoked from the API roles, which also breaks the infinite recursion of a tank_members policy that queries tank_members. auth.uid() is wrapped as (select auth.uid()), as the Supabase RLS docs recommend. A pgTAP suite simulates two users by setting request.jwt.claims, and an integration test runs the real sync code against a local Supabase stack and checks that a second account sees none of it.

What the app does for the people who use it: A free aquarium water test log app for iPhone.

FAQ

How do you calculate free ammonia from pH and temperature?

Compute pKa = 0.09018 + 2729.92 / T with T in kelvin, then the free fraction = 1 / (1 + 10^(pKa − pH)), and multiply total ammonia by it. At 25 °C pKa is about 9.25, so roughly 0.56% of total ammonia is un-ionised NH₃ at pH 7 and 5.4% at pH 8. The expression is for fresh water; in seawater the real fraction is slightly lower.

Should an untested value be stored as null or zero?

Neither. In Aquarium an untested parameter produces no reading row, and nulls appear only when rows are grouped into per-session views. Storing zero for "not tested" draws dips that never happened and makes rate-of-change checks compare against a value nobody measured.

How do you build offline-first sync with Drift and Supabase?

Make Drift the only thing the UI reads, and write every change to its table and to an outbox table in one Drift transaction. A sync engine pushes the outbox parents-first, then pulls rows whose server-stamped updated_at is newer than the last one it saw minus a short overlap, skipping rows with unpushed local edits. Client-generated UUIDs and deleted_at tombstones let both sides agree on what changed.

How does an app decide an aquarium is cycled?

Aquarium groups readings into test sessions and looks for two sessions in a row where ammonia and nitrite are at or below 0.25 ppm and nitrate is present. One such session is shown as "confirming", because a single clean result can be a misread.

How do you write Supabase RLS policies for tables that belong to a parent row?

Give every child table the parent's id, put the access check in a security definer function in a schema the API can't reach, and generate the same select and write policy for each table in a loop. Wrap auth.uid() as (select auth.uid()) and index the columns the policies filter on.