AquaLog: An aquarium logbook that works offline

Reminders converted every log with a fixed UTC offset, so a keeper outside UTC could log a water change on the wrong day, and a monthly cadence stored as 30 days drifted five days off the calendar in a year.

One notion of a day

Each keeper now carries a time zone, and the trigger that schedules the next reminder uses it. Cadence is stored as a number and a unit, so monthly means a real calendar month, and the old count of days is derived from that pair, so the two can never disagree.

Costs booked outside the transactions table had no currency at all, a half-typed currency could break a row's formatting for good, and four ways of writing money disagreed: a filter's price never reached the budget.

One model for money

Every amount now has a currency beside it, checked for shape. A costed record owns at most one linked expense, kept in step by a single database function, while expenses a keeper enters by hand are never touched by it. Stock carries its price per unit, so the store room can be valued.

Postgres grants every new function to the public by default. One function took any keeper's id and returned their time zone, a genuine read across accounts.

Privileges closed at the database

Every table in the first schema has row-level security switched on, and the default public grant on database functions is revoked. The function that read across accounts is no longer callable by clients at all.

The browser is not a trust boundary. A hand-written request with a valid session could store a fish count of a billion trillion and poison every total on the dashboard.

Limits the database enforces

Every numeric limit the forms check is mirrored as a constraint in the database, which holds the authoritative copy, so an impossible value is refused whichever way it arrives.

Running a product means acting on people's accounts, and an action nobody can trace, or a log that can be edited afterwards, is a trust problem.

An admin console with a record

The admin console writes every action to an audit log that is append-only three times over: no grant to change it, no policy to change it, and a trigger that refuses any change. An account switch can suspend a user, and every admin request re-checks the session and the role in the database.

Fish rooms and store rooms are not known for good reception, and a change that fails silently is a log that is quietly wrong.

Readable with no signal

AquaLog installs as an app, keeps tanks, logs and stock readable offline from a cache scoped to the signed-in keeper, and holds a change made offline until there is a connection instead of dropping it.