A push pipeline written to assume failure — in nothing but SQL

Pet-record apps exist, but most assume a public feed. The people an owner actually wants to show things to are not followers — they are the handful of people who know that particular animal.

The other motivation was to never hold records hostage. I didn't want a design where you hand over photos and logs and then can't get them back if you cancel. Making full export a free, independent subsystem rather than a paid feature was decided first.

Keeping a health log going (weight, vet visits, medication) requires notifications. But notifications are the area where outsourcing makes failures impossible to diagnose, and writing it yourself makes it easy to forget the failure paths.

The first implementation was, honestly, a queue that could only succeed. No retry interval when delivery failed. A worker dying mid-flight left the row claimed forever. Nothing collapsed repeated notifications of the same kind.

The other difficulty was photos. Pet photos carry the place they were taken.

I decided to close the delivery layer inside SQL. No external worker infrastructure at all — the queue, retries, the dead-letter state and reaping all live as Postgres tables and functions, driven by pg_cron.

The reason is that there is then one place to look during an incident. When someone says a notification didn't arrive, the queue rows show exactly where it stopped.

For photos, the decision was to strip EXIF completely on-device before upload, reading only the capture timestamp in memory first so the record still carries an honest date.

What I gave up was throughput. This will not reach the parallelism of dedicated worker infrastructure. For a handful of quiet notifications a day, it is enough.

The queue carries a scheduled time, an attempt count, a next-attempt time, a claim time and a receipt-checked time, with the backoff curve collapsed into a single function. Rows left claimed by a dispatcher that died are picked back up by a reaper. Anything that exhausts its retries falls to dead.

Notification kinds are held as a catalogue table, each row declaring whether the user may change it and whether it may cross quiet hours. Only medication doses and reminders are allowed to break quiet hours.

Delivery time is nudged toward each person's own hours, derived by smoothing and clamping a histogram of when they open the app. Not machine learning — a method that can be explained in one sentence and costs one nightly query.

Images are converted on-device to JPEGs with a 2048px long edge, and those converted JPEGs carry no EXIF at all, so GPS coordinates never leave the phone. Video is not transcoded: a poster frame pulled on-device is written to the same naming scheme photo thumbnails use, so the app and the app-less viewer render it through the same path.

28 commits between 2026-07-28 and 07-30. The database defines 100 RLS policies and enables force row level security on 58 tables. The force is the point: even the table owner cannot bypass a policy.

RLS is verified by tests that mint real users and JWTs and check allow/deny one policy at a time, run in CI against a locally started Supabase. 91 test files.

14 pg_cron jobs drive push production, delivery, receipt checking, reaping of orphaned rows, retention sweeps, partition maintenance and nightly rollups.

Analytics load only inside the site route group, so the screens that render actual pet photos and live tokens — the shared viewer, invitations, pairing — cannot inherit them. Not an omission that was remembered; a structure that cannot include them.

Booking reminders, payment chasers, stock alerts, shift notifications — notifications are part of the business in a lot of projects, and in all of them "what happens when it fails" gets deferred. memofu's delivery layer is a worked example of designing those failure paths first: retry intervals, a dead-letter state, and reaping work nobody finished. It also adds no external service, so it adds no new place to look.

Writing 100 RLS policies and forcing them is directly applicable to design review for B2B SaaS with tenant isolation in scope. For projects handling location or personal data — medical, children, real estate, security — the strip-before-send pattern is already implemented.

I take on this kind of work as SaaS / industry DX development.