Subtraction is half the work

Every time the Library tab opened, a brief spinner appeared. Templates aren’t data that changes often, yet the first paint had to wait for an API round-trip every time.

The record side already had the SQLite cache + incremental sync pattern in place. Apply the same thing to templates and the spinner goes away. The opening question was simple: can I just copy it?

Two policies first

Two things had to be decided before any copying. Photos and ownership.

Photos aren’t cached. Material/Tool/Venue photos are coming to templates soon. Records have few photos so it wasn’t an issue there; templates are different. Pulling photo binaries into the sync response would inflate the payload and fill mobile disk fast. And photos are only needed on the detail screen — invisible in list view, only touched on the occasional detail open. Caching binaries that are rarely used is a loss.

Decision: cache metadata only; photos stay lazy from the server, fetched on detail open.

Owner-only cache. Records can be owner OR assigned_to (you cache records others assigned to you). Templates are different — assigned_to isn’t really a thing, and more importantly the community Overview lets users browse other people’s templates. Caching those browsed templates would pollute the cache — the word “my library” stops meaning anything when foreign data leaks in.

Decision: cache holds only what I own. Shared / community templates hit the API directly, no cache layer.

Once those two were settled, what remained was a subtraction job — bring the record pattern over, then carefully take out what doesn’t apply.

Subtracting on the server

The record /sync endpoint has several merge steps:

1. Filter by owner OR assigned_to + parent_task_id=None + revised_at
2. Merge preferences (is_focused, is_bucketed, is_pinned)
3. Merge project response
4. Merge assignee — the nickname of the person it’s assigned to
5. Merge current_sub_task — info about the sub-task currently in progress
6. Fallback — if current_sub_task isn’t computed yet, find the next PENDING/STARTED in sub_task_links
7. Tombstone-based deleted_ids

In the template version, steps 4, 5, 6 all drop out.

• Assignee — templates don’t have assignment
• current_sub_task — templates don’t carry execution state; there’s no notion of “which step are we on”
• Fallback — no execution state, no fallback to do

What remains: owner filter + parent_task_id=None + preferences + project + tombstone. Code shrinks by roughly half.

@router.get("/sync")
async def sync_task_templates(...):
    conditions = [
        {"access.owner": achiever.id},   # owner only — no assigned_to
        {"parent_task_id": None},
    ]
    # cursor pagination — same as record
    ...
    # preferences + project merge
    # tombstone-based deleted_ids
    return {"templates": [...], "deleted_ids": [...], ...}

Tombstones carry over directly. On template delete, task_template_tombstone_collection gets owner_id + task_id + deleted_at. A delete done on another device flows back through sync and disappears from this device’s cache.

Subtracting on the schema

The mobile record cache table has 15 columns:

id, json, state, parent_task_id, start_date, end_date, created_at,
is_focused, is_bucketed, project_id, title, point_color,
priority, difficulty, satisfaction

The template version wasn’t a question of what to keep — it was what to drop.

• state — templates have no execution state. Drop.
• start_date / end_date — templates have them, but the library tab doesn’t query by date range. Drop.
• is_bucketed — templates don’t go to a bucket list. Drop.
• priority / difficulty / satisfaction — record-only evaluation fields. Drop.

What stays — 9 columns:

id, json, parent_task_id, project_id, title, point_color,
is_focused, is_pinned, created_at

Indexes follow the same logic — only what’s actually queried: parent, project, pinned, created.

Cache as source of truth — not yet

The natural next step from here: move the Library tab’s filter / sort / pagination on top of the cache too. Then the client only ever looks at the cache (kept fresh by sync) and the API gather call disappears entirely. A clean end-state.

But I held back. That transition needs a fair amount more:

• Move filter logic to the client (keyword search, point colors, project filter)
• Move sort logic to the client (created_at, generated_count, pinned-first)
• Convert pagination to SQL OFFSET / LIMIT
• Auto-refresh the displayed list when dbVersionAtom changes — so a sync that brings in a new template while the Library tab is open shows up in place, with proper reactivity
• The last one especially — when you’ve already scrolled 50 items in and a sync adds/removes some, what should appear and what should hide and how to reconcile is not a small problem

So this PR is narrowed to warm-start only:

1. Sync runs in the background and keeps the cache as the full owner-template set
2. On Library tab cold start, hydrate the first 12 items from the cache instantly — no spinner
3. In parallel, fire the API gather for fresh data — when it returns, swap the atom

Cold-start first paint goes to zero. From the user’s view, the library is already there when they tap, and a moment later it smoothly updates with fresh data. The spinner is gone.

When a filter is active the cache hydrate is skipped and only the API fires. The cache holds the full owner set — it doesn’t match a filtered subset cleanly, so for this round the filter path stays as it was. Filter-aware cache reads come in a later PR.

CRUD mutations don’t write to the cache directly either. The current flow: user creates a template → API succeeds → SSE event fires → sync triggers → cache updates. Long path, but SSE moves fast, so cache catches up within a few hundred milliseconds. If that ever becomes noticeable, direct cache mutations come in.

Closing thoughts

Borrowing a pattern is not copy. The value of borrowing comes from deciding what to remove and what to redraw.

The record cache shape was already a good, battle-tested one. SQLite + syncAt watermark + tombstones + cursor-paginated increments + preferences/project merge. Bringing it to templates kept that skeleton intact while the flesh had to come out entirely — assignee, current_sub_task, fallback, state, start / end date, is_bucketed, priority / difficulty / satisfaction. Seven lines of subtraction.

Each line removed is a small declaration: a template is not a record. The two domains look similar but they aren’t. The same abstraction fitting both is the lucky case; usually, if you don’t re-examine where they differ at borrowing time, the wrong pattern flows through unchanged.

And the scope call. Cache as source of truth is a clean end-state, but it’s a big step — reactivity, filter, pagination all have to come together for consistency to hold. Cramming it into a single PR inflates the diff and the risk. Warm-start only captures the value the user actually sees (instant first paint), and the rest is its own work.

Today was a reminder that subtraction takes as much care as addition, and that a clean end-state doesn’t have to be reached in one step.