Ship the shape, not the answer

cookd gives you access to a phone app in proportion to how much of your Claude usage window you have burned through on your laptop. Burn more, get more. It is a joke about developer self-destruction that happens to need a real sync system.

A companion CLI reads Claude Code's local transcripts, works out how much of the rolling five-hour window you have used, and pushes that percentage to an Edge Function. The function turns it into minutes of access:

// supabase/functions/usage-ingest/pure.ts
export function accessMinutesFor(pct: number): number {
  const p = Math.min(100, Math.max(0, pct));
  if (p <= 10)  return MIN_GRANT_MINUTES;   // 5
  if (p >= 100) return MAX_GRANT_MINUTES;   // 60
  return MIN_GRANT_MINUTES + ((p - 10) / 90) * (MAX_GRANT_MINUTES - MIN_GRANT_MINUTES);
}

Below 100% the largest grant is about 54 minutes. The expiry it produces has exactly one writer, and that writer runs only when the companion speaks.

The companion spoke twice per Claude session: on SessionStart and on SessionEnd. Claude Code sessions are not short. One measured session ran 240 hours. It would have pushed once, at minute zero, and then said nothing for ten days.

So a quiet companion did not just leave a stale number on a screen. Within the hour it locked its owner out of the app while they sat there using Claude Code, producing the exact usage that was supposed to keep them in.

Before changing anything we measured how stale things were. The most recent update to any usage row, across every user, was 13 days old. The ADR opening this work had guessed "hours or days". The cause was ordinary: the setup command synced once and exited, the session hook meant to push afterwards had never shipped, and the flag recording whether it was installed was null on every device.

The hook was the bug we could see. The bug underneath it was that we were sending an answer, and answers go stale.

Three ways an answer goes bad

The first design is the one most people build first. The client knows the answer, sends it, and the server stores it:

companion → { pctUsed: 47, usedTokens: …, resetsAt: … } → server → limit_states

One row per user. It is cheap and obvious, and it fails in three ways that only show up when you look at what the payload is rather than what it says.

Upward decay. The five-hour window is defined relative to now. A value computed at push time keeps counting tokens that should have aged out, for as long as nobody pushes again. An idle user does not drift to zero. The migration that fixed it puts it this way:

a value computed at push time DECAYS UPWARD: buckets that should have aged out stay counted for as long as nobody pushes. An idle user would read as permanently at peak.

Frozen weights. The usage number weights five token counters by relative cost, because that is the only unit in which Anthropic's limit holds still. The weights are a guess. Storing the weighted total throws away the counters you would need to re-derive it, so a bad guess is permanent.

Only the laptop can refresh it. If the number changes only when the laptop speaks, access changes only when the laptop speaks. That is the lockout.

There is no line to fix in any of these. The payload did not carry enough information for what the server had to do with it.

Send observations, derive conclusions

The companion now reports what it saw, in 15-minute buckets of raw, unweighted counters:

-- supabase/migrations/0022_usage_buckets.sql
primary key (user_id, device_id, source, bucket_start)

input       bigint  -- the five raw counters, unweighted
output      bigint
cache_5m    bigint
cache_1h    bigint
cache_read  bigint

captured_at timestamptz  -- the laptop's clock
received_at timestamptz  -- the server's clock

Twenty buckets cover a five-hour window. Every push re-sends the whole window, at most 20 rows and roughly 3 KB, and the server derives the window when someone reads it.

That dissolves all three failure modes. The weights become configuration: re-tune them and every stored bucket re-derives, history included. That paid off within two months, when a paired reading showed the app at 26% while Claude reported 56% for the same window. The inferred limit was about 2.15× too high. Under the old design that would have been unrecoverable history. Under this one it is a constant change. And because the window is computed at read time, it is correct whenever someone looks, whether or not the laptop has said anything since.

A merge with no clock in it

The first version of the design guarded the upsert with captured_at: newest push wins. Our design reviews are AI-assisted: an architect role proposes, a critic role attacks, and the team decides. The critic's objection here is the sharpest exchange in either of our repositories:

Your idempotence is built on the laptop's clock, which is exactly what risk R3 says you cannot trust. […] If a machine's clock jumps backwards — NTP correction, timezone bug, dual-boot, a VM resuming from snapshot — then usage_buckets.captured_at < excluded.captured_at is false for every subsequent push, and that device is wedged permanently: it keeps pushing, the server keeps silently rejecting, and nothing surfaces the failure because the upsert reports success. You have designed a silent, per-device, permanent data-loss mode and called it idempotence.

The fix was simpler than the guard. Within one (device, bucket) pair the totals only go up. A 15-minute slot gains tokens as work lands in it and never loses any. So the merge does not need to know which push is newer:

on conflict (user_id, device_id, source, bucket_start) do update set
  input      = greatest(ub.input,      excluded.input),
  output     = greatest(ub.output,     excluded.output),
  cache_5m   = greatest(ub.cache_5m,   excluded.cache_5m),
  cache_1h   = greatest(ub.cache_1h,   excluded.cache_1h),
  cache_read = greatest(ub.cache_read, excluded.cache_read),

greatest() is idempotent, commutative and associative. A replayed push changes nothing. An out-of-order push changes nothing. A clock jumping backwards changes nothing, because no comparison reads a clock. captured_at is still stored for observability, and the migration says in capitals that it is not a merge guard.

This mattered for a reason we had measured. Subagents keep running after the turn that spawned them returns. One kept burning tokens for 76.9 minutes after its parent turn ended, writing into buckets that had already been pushed. Under newest-wins we would have had to reason about how long a background agent can run. Under max-merge we re-send the window and the question never comes up.

Getting greatest() into production meant breaking a rule the same ADR had just written. It listed "a SQL function taking a jsonb array" as rejected and told the implementation to use the client library's upsert. The migration explains why that could not work:

PostgREST's .upsert(onConflict) compiles to on conflict … do update set col = excluded.col — last write wins, per column, unconditionally. There is no way to express greatest(existing, incoming) through it. […] Part 2 is the load-bearing one […] so the convention gives way.

The convention existed to stop a security boundary moving into untested hand-written SQL. The function meets that concern directly. It re-checks device ownership itself, is revoked from every client-reachable role, and has both properties, including the negative case, asserted in a test file CI runs.

Prorating the edge

The view derives the window by weighting each bucket by how much of it overlaps the last five hours:

select greatest(0.0, least(1.0,
  extract(epoch from (
    least(p_start + interval '15 minutes', p_now)
    - greatest(p_start, p_now - interval '5 hours')
  )) / 900.0
))::numeric;

A bucket straddling the trailing edge is prorated rather than counted whole or dropped whole. Either of those would step the displayed number by up to a full bucket every fifteen minutes. Proration lets it move smoothly, which is what lets the phone tick the number every second between fetches.

It assumes usage is spread evenly inside a slot. It is not. The error is bounded by one bucket at the window edge and sits below display resolution, which is why the slot is fifteen minutes and not sixty.

The cleanup that would have unlocked the paywall

With buckets as the source of truth, the obvious follow-up is to delete the old scalar table. Reading the callers first is what stopped us.

limit_states had four live readers: the access gate, a percentile function on the profile screen, the social rail on the feed, and public profiles. Retiring it was a four-surface change dressed as a cleanup. Worse, one surface would have inverted. The app decides whether to show the lock screen with:

locked = me.authed && me.limit != null && accessExpired

That is safe only because a limit_states row, once created, persists forever. In a bucket table, no rows is the normal state for an idle user. A read path returning nothing makes limit null, makes locked false, and unlocks the app for anyone who has not used Claude in five hours. We would have shipped the opposite of the product while believing we were fixing freshness.

So the table stayed, demoted to a projection, with a view taking its name:

alter table public.limit_states rename to limit_states_base;
alter table public.limit_states_base rename column used_tokens to used_tokens_cached;
alter table public.limit_states_base rename column pct_used    to pct_used_cached;
alter table public.limit_states_base rename column status      to status_cached;

Renaming instead of drop-and-recreate carries the RLS policy, check constraints, primary key and grants across untouched, because they are bound to the object and not its name. All four readers kept working with no query changes. The gate expression is not merely safe; nobody touched it.

Three words that keep row-level security on

create view public.limit_states with (security_invoker = true) as

A Postgres view runs as its owner unless told otherwise, so row-level security on the base table is evaluated as the owner and is effectively off. Leave out security_invoker and every user's row is readable by every other user, through the view. Nothing errors. The app works.

The migration's own comment calls this the file's highest-risk line, and its test file leads with a negative case: an assertion, run under a real authenticated role, that a user cannot read a row they should not see. A happy-path test passes identically with the flag missing. Running that test needed something the repository did not have yet, a way to execute its own SQL before shipping it. That is its own article.

Security review caught a second instance of the same problem. The first draft of the bucket table's policy copied the scalar table's self-or-following shape. The review rejected it:

limit_states is one row carrying a current-window SCALAR; this table is a 15-minute- resolution TIMELINE, 48 hours deep, with per-slot token volumes, model mix, device id and agent counts. Under the self-or-following shape any account holder could reconstruct a named person's working hours, sleep schedule and session intensity — and follows_insert_self needs no approval from the person being followed, so the whole timeline sat one unapproved INSERT away from every user on the platform.

The first draft was a faithful copy of a correct policy. Turning a scalar into a timeline changed what the same policy meant, and nothing in the types or the copy told anyone.

Making the buckets self-only created a quieter trap. Derive a stranger's row from buckets you cannot see and you get zero rows, which derives to 0 tokens / 0% / idle. That is not an error. It is a confident wrong number, waiting for the first reader who points the view at a followee. So the view stopped trusting its caller:

select (auth.uid() is null or b.user_id = auth.uid()) as can_derive

Your own row is derived live. Anyone else's falls back to the cached scalars, and freshness comes back NULL rather than a guess that would paint a "repair your setup" banner over a healthy account. A convention every reader had to remember became a property of the view.

The number that disagrees

The first bucket-era push in production, from one cookd sync:

usage_buckets        5 rows, 15-minute aligned
limit_states_base    synced_at set
limit_states (view)  freshness = live

And the measurement that matters:

companion computed   exact sum, per event
server derived       prorated at the window edges
difference           −0.7%

If the server stored what the client sent, those two numbers would match. They differ because the view really is re-deriving the window from buckets, and prorating the one at the trailing edge. We went looking for that gap on purpose. Zero would have meant no evidence the view was doing anything.

Then something happened that nobody planned. Mid-session, an npx call pulled the published pre-bucket companion and ran it against the new schema. It wrote the cached scalars, sent no buckets, correctly failed to advance the freshness marker, and the view kept serving the live derivation from the newer buckets already there. Backward compatibility, shown by a real old client instead of argued.

What it cost

About 3.7× more writes, from roughly 136 to roughly 500 per user per month, gated on the window growing by at least 5% of the limit. At the 500-user envelope the design was sized for, not observed traffic, that is about 250,000 Edge Function invocations a month, well inside the plan's quota, and a number we track.

Up to twenty rows per active window per device, where there used to be one per user. Retention tiers roll 15-minute slots to hourly after 48 hours and daily after seven days, so storage grows with active users rather than with history.

A merged row nobody sent. If transcript compaction makes a later push report lower values, per-column greatest() keeps the higher ones and can compose a row no single push contained. It is bounded by one bucket and errs toward overstating usage, which here grants more access, not less.

No cross-device dedup. Buckets discard the message ID that made per-event dedup work, and the cross-device aggregate is a SUM. A mirrored home directory double-counts. MAX is the documented fallback and a one-function change, which is the only reason SUM was acceptable.

Everyone's numbers rose about 8% overnight, and 21% in agent-heavy sessions, because the new companion finally read subagent transcripts. That needed a product answer as well as a technical one.

Every user had to upgrade. A pre-bucket companion sends no buckets, so the transition only works if upgrading works, and at first it did not: the installer skipped the download whenever a binary existed, and the hook ran the pinned binary rather than the new package. Re-running the install upgraded nothing. The companion now records its installed version next to the binary and replaces it when the release moves on.

Found on the way: a branch that never ran

The ingest function's timestamp helper accepted only numbers. The companion had always sent ISO strings. So resets_at and window_start were written NULL on every sync, from the first ingest commit on 10 June until this work landed on 29 July. The branch meaning "a new Claude window starts a fresh grant" compared two nulls and never executed. A burn-rate percentile on the profile screen was dead in production the whole time it was shown.

The type fix was two lines. What to do with the revived column was not. Under a rolling window, window_start is always five hours ago, so burn rate becomes used tokens divided by five for everyone, a constant multiple of a stat already on the screen. Repopulating the column quietly merges two statistics into one. That is a product decision wearing a type error's clothes, and we kept the two apart.

What we would do differently

• Measure staleness on day one. The 13-day figure took one query. Had it existed in June, the missing hook would have been a June bug, not a July discovery.
• Grep for readers before designing the replacement. The four-reader inventory changed the design from "retire the table" to "demote it to a projection". It should have been an input, not a late save.
• Treat "no rows" as a value in every review. The lock expression was safe by accident of a table that never deletes. Every != null gate deserves the question "what happens when the row is missing?" before it is merged, not after.
• Write the negative test first for any view. The security_invoker test was written because someone knew to be afraid of that line. It should not depend on that.

The payload is a schema decision

Every problem in the first design came from sending a conclusion instead of the observations behind it: the upward decay, the frozen weights, the lockout. A percentage relative to a rolling window is only true at the instant it is computed, and storing it in a column is a type error Postgres cannot catch for you.

When a value has a moving reference point, send the parts and let the reader do the sum.