ADR-0045: Sync one site per Cloud Tasks task — decouple per-site sync budget from fleet size

Status: Accepted
Date: 2026-08-05
Deciders: Seth (accountable, Lead Architect — reassigned this issue to himself on 2026-08-05 specifically because the remedy shape is his call) — APPROVED 2026-08-05, after the ai-engineer review below and its required changes were folded into this text; ai-engineer review: APPROVE-WITH-CHANGES (docs/superpowers/reviews/2026-08-05-ai-engineer-adr-0045-per-site-sync-tasks.md) — no dollar cost, so nothing here needed ceo-blair per that review’s own read of the material-choice gate
Issue: caseproof/memberintel#852 (parent #784; relabelled v1-blocking 2026-08-05 per the CEO priority decision recorded in #852’s comments — priority only, remedy shape explicitly left open for this ADR)

Context

Sites sync today as one HTTP request draining a queue of claimed sites serially in-process:

src/memberintel/api/sites/service.py
  SWEEP_SOFT_DEADLINE_SECONDS  = 480                      # raw Cloud Run request timeout is 540s (infra/main.tf:535)
  SWEEP_ROUNDS_PER_TICK        = 15
  PER_SITE_SYNC_BUDGET_SECONDS = 480 / 15 = 32s            # added in #851
  SWEEP_DUE_LIMIT              = SYNC_ADMISSION_SLOTS * SWEEP_ROUNDS_PER_TICK

Cloud Scheduler POSTs to /internal/sites-sweep (OIDC-authenticated per ADR-0022,
src/memberintel/api/_internal_auth.py); sweep_due_sites claims due sites via
find_and_claim_due_sites’s FOR UPDATE SKIP LOCKED query, then
asyncio.gathers their syncs inside that one request, bounded by the 480s soft
deadline.

The structural problem. Every site’s time allowance is a function of how
many other sites share its request, not of what that site actually needs.
PER_SITE_SYNC_BUDGET_SECONDS is a flat 32s at any fleet size — it doesn’t
shrink automatically as the fleet grows, but fleet growth is exactly the
pressure that pushes SWEEP_ROUNDS_PER_TICK up (the only other lever besides
DB-pool-bounded slots, ADR-0040), which does shrink the per-site share. So
the ceiling is a constant someone picked, and it moves down as MemberIntel
adds customers — never up.

#851 (merged) bounded each site to its 32s share and gave overruns their
own over_budget outcome, deliberately not a sync failure (a site being big
isn’t a site being broken, so it must not feed the 7-consecutive-failure
circuit breaker). That closed the fairness bug — one oversized tenant can no
longer starve the sites queued behind it — and made the ceiling visible via
the #820 alert. It did not raise the ceiling.

#784 measured the actual cost: a full-module-set site’s /members fetch
runs 388s against a 32s share (loopback, warm cache, no HTTP round trip —
production, with its real per-page round trips, is slower still). Break-even
at the live 32s budget is ~308 members/platform, not the ~360 quoted
throughout #784’s thread (that number was 540/15-era, before #819
introduced the 480s soft deadline).

Why this is now v1-blocking, not post-launch. The CEO priority decision
recorded in #852 (2026-08-05) corrects the original framing: this was
reasoned about as a Free-tier capacity question, but the defect is
tier-independent — a Pro site over the same ~308-member threshold stops
syncing too, and syncs daily rather than monthly, so it fails ~7x more often
than a Free one. A Pro customer whose data quietly goes stale is a churn
event, and V1 economics rest on 5–10% Free→Pro conversion inside 60 days.
Seth countersigned as accountable exec
(https://github.com/caseproof/memberintel/pull/1094#issuecomment-5193730148).
That decision called priority only — this ADR is where the remedy shape
gets decided.

Why #818 (incremental sync) doesn’t substitute for this. #818 reduces
work per cadence, buying a larger break-even N — it leaves the coupling in
place, and any design where one request drains a queue of sites serially
hits this failure at some fleet size. Worse, per Seth’s own analysis on
#852: keeping sync_site as one full authoritative snapshot per call (which
this ADR’s design preserves) is what lets engagement-clear
(sync.py:806 — an absent engagement object means tracked-and-zero when a
platform is active, preserve-last-known when it isn’t) and content
pruning (sync.py:969, :993 — posts untouched by the call get deleted)
stay correct. A chunk-a-site-across-ticks design (#818’s shape, or any
partial-fetch scheme) breaks that invariant: absence acquires a third
meaning (“unchanged, not fetched this chunk”) the logic can’t distinguish
from “genuinely gone.” #852 is a strictly cheaper change on the correctness
axis, not just the coupling axis — an independent reason to sequence it
first, on top of #818 remaining worth doing later on its own merits (less
load on the customer’s host, cheaper syncs).

Sizing at today’s fleet (production, queried 2026-07-29, per #852): 8
sites, 587 member rows total, largest single site 391 rows, max 2 platforms
emitting rows on any one site (edd + memberpress, 42 rows), 0 consecutive
failures fleet-wide. The queue costs approximately nothing at today’s scale,
and this is pre-customer work on a coupling no current site is anywhere near
— the argument for doing it now, with no live tenants to migrate across an
execution-model change.

Decision

Move to one Cloud Tasks task per claimed site, keeping the existing claim
mechanism and sync_site call shape unchanged underneath:

1. sweep_due_sites keeps find_and_claim_due_sites (the
   FOR UPDATE SKIP LOCKED claim is still the right cross-instance dedup —
   nothing about the claim query changes) but stops asyncio.gather-ing the
   claimed sites in-process. Instead it enqueues one Cloud Tasks task per
   claimed site and returns in about a second — sweep_due_sites becomes
   claim-and-enqueue, not claim-and-run.

2. New POST /internal/sites/{id}/sync endpoint on the existing API
   service, OIDC-authenticated the same way every other /internal/*
   endpoint already is (_internal_auth.py, ADR-0022) — the allowed_sas
   set for this endpoint is the new Cloud Tasks queue’s own service account,
   not the scheduler’s. sync_site_with_failure_tracking moves behind this
   endpoint largely unchanged in its own logic, but the endpoint must
   explicitly pass a new PER_TASK_SYNC_BUDGET_SECONDS constant (see
   Decision §3) as its budget_s argument. The function’s existing default,
   budget_s: float | None = PER_SITE_SYNC_BUDGET_SECONDS, is the exact 32s
   ceiling this ADR exists to remove — if the new endpoint calls it without
   overriding that default, every task silently inherits the old cap and
   the entire point of this ADR evaporates on day one, invisibly, since the
   call remains syntactically valid. This is not a code-review nit; it is
   part of the decision and must be named here so an implementing PR can’t
   miss it. The endpoint also must map every SiteSweepResult the
   wrapper returns — synced, failed, vanished, over_budget, all four
   — to an HTTP 2xx response. Non-2xx is reserved for exceptions that occur
   outside or before the wrapper (the wrapper itself never raises, by
   design). See Decision §5 for why this status-code contract is load-bearing
   for the retry split, not a formality.

3. Per-task ceiling: a new soft deadline, PER_TASK_SYNC_BUDGET_SECONDS ≈ 500s, passed as budget_s — not the raw 540s Cloud Run hard timeout, and
   not None/unbounded. Mirrors today’s pattern of a soft deadline
   (480s) sitting under a hard one (540s): sync_site_with_failure_tracking
   enforces this via asyncio.wait_for, which raises a catchable
   TimeoutError that becomes a real, logged over_budget outcome with time
   left to roll back and commit before Cloud Run’s own hard kill. Passing
   budget_s=None would remove the wait_for entirely, leaving Cloud Run’s
   SIGKILL as the only remaining ceiling — a SIGKILL produces no
   over_budget log line, no #820 alert, no signal at all, reproducing at
   the single-task level exactly the “claimed but never reached, no signal”
   bug class #784→#819→#821→#851→#852 exists to close at the fleet level.
   500s under 540s gives ~15.6x the headroom of today’s 32s share, in the
   same neighborhood as the issue’s own “~15x” estimate. Do not tune this
   further until Seth’s offered single-platform bench band exists — the
   issue itself flags that the only measured number (388s) is a
   ten-platform-fit extrapolation that disagrees by ~8x with the other
   available extrapolation, and neither describes the 1–2-platform shape
   every real site actually has. 500s is a reasonable ceiling to ship with;
   revisit only if that bench band shows it’s wrong.

   Coupling to note, not hide: Cloud Run’s request timeout is a
   whole-service setting (the timeout attribute inside infra/main.tf’s
   template { ... } block for google_cloud_run_v2_service.memberintel_api),
   not per-route. If the bench band later shows sites genuinely need longer than 540s, raising
   the service-wide timeout to accommodate them raises it for every other
   endpoint on the same service (chat, login, everything) — a customer-facing
   latency/isolation tradeoff, not a free knob. The escalation path if that
   happens is to split /internal/sites/{id}/sync onto its own Cloud Run
   service with its own timeout, not to quietly bump the shared service’s
   limit. Not needed now; naming it so it isn’t rediscovered under pressure.

4. SYNC_ADMISSION_SLOTS (ADR-0040) is unchanged — it is still the
   per-instance DB-pool guard (a different job: protecting the 47-connection
   Cloud SQL budget from oversubscription, per ADR-0040 §2) and stays exactly
   as-is. The Cloud Tasks queue’s own max_concurrent_dispatches becomes the
   new fleet-level throttle — the two are independent knobs, not a rename of
   one into the other. Starting value: SYNC_ADMISSION_SLOTS * max_instances
   (today: 3 × 3 = 9), so a single Cloud Run instance’s own connection budget
   is never the limiting bottleneck below what the fleet-level queue already
   permits.

5. Retry ladder splits by failure class, not collapsed into one — and the
   split only holds if the HTTP-status contract below is followed exactly:

   • Transport-level retry (a single HTTP call to /internal/sites/{id}/sync
     times out, 5xx’s, or the instance recycles mid-request) moves to Cloud
     Tasks’ native queue config (max_attempts, min_backoff,
     max_backoff, dead-lettering). This is what today’s hand-rolled retry
     inside sweep_due_sites/sync_site_with_failure_tracking was standing
     in for, and the queue does it better (backoff, dead-lettering, and
     per-task deadlines are its actual job).
   • Product-level cadence policy (the 7-consecutive-failure circuit
     breaker that decides a site’s connector is broken, not merely
     transiently slow, and should stop being retried until reconnected) stays
     exactly where it is today: a DB-persisted, site-level counter in
     find_and_claim_due_sites’s claim logic. This is a business decision
     about tenant health, not a transport concern, and collapsing it into the
     queue’s retry config would conflate “this network call flaked” with
     “this tenant is broken” — two different signals with two different
     correct responses. Leave this tier alone.
   • The contract that keeps these two tiers from colliding:
     sync_site_with_failure_tracking already catches everything and returns
     a SiteSweepResult rather than raising — that design is correct and
     unchanged. The new endpoint must map every result it returns
     (synced, failed, vanished, over_budget) to HTTP 2xx, with
     non-2xx reserved strictly for exceptions that occur outside or before
     the wrapper (e.g., the OIDC check itself, a bug that raises before the
     try). If a status="failed" result is instead re-raised as a non-2xx
     — a very natural-looking mistake, since “the sync failed” reads like
     “the request failed” — the same real failure gets double-counted: once
     inside the wrapper’s own consecutive_sync_failures counter, and again
     as a Cloud Tasks retry that re-invokes the endpoint, hits the wrapper
     again, and increments the counter a second (and third) time. A typical
     max_attempts of 3 against one real connector outage would then trip
     the 7-failure circuit breaker in under an hour instead of over a week,
     firing the stall-email path on a timeline nobody designed for. This
     status-code mapping is part of the decision, not an implementation
     detail left to the PR that builds it.

6. last_sync_attempted_at is still written at claim time, as today —
   but claim and enqueue are two separate steps now (a DB commit, then a
   later CreateTask call), not one atomic unit. If the process dies, or
   CreateTask itself fails (network blip, IAM misconfig, queue quota)
   between them, the claim is real but its task never gets created — the
   same shape as a mutation committing without its audit row.
   last_sync_attempted_at alone can’t detect this: claim time and
   sync_site’s own entry-stamp write both set the SAME column, so a
   “claimed but the task never ran” site is indistinguishable from a
   “claimed and the task ran fine” site by that column alone. A new
   Site.sync_task_enqueued_at column carries the signal instead: set by
   sweep_due_sites on a successful CreateTask call, and cleared by
   sync_site’s own entry-stamp write (which runs unconditionally as its
   first statement, regardless of caller) — so a non-null value that
   survives past when the task should have run is real proof the task never
   reached sync_site. Add a lightweight reconciliation check (not a
   sync-runner) that:

   • Detects sites where sync_task_enqueued_at is non-null and older
     than a concrete cutoff — a real query condition, not a phrase to
     re-derive later. Sized against the sweep’s own polling cadence, not the
     per-task budget: reconciliation only gets a chance to run once per
     sites-sweep tick (infra/main.tf’s sites_sweep_tick, hourly,
     "0 * * * *"). A cutoff shorter than that hourly interval would
     misclassify ordinary queue backlog or Cloud Tasks’ own retry backoff as
     “stuck” on literally the very next tick. 2 hours (one full missed
     tick of slack) gives real backlog room to clear before a task is
     treated as genuinely stuck.
   • Remediates by re-enqueueing automatically. sync_site is safe to
     re-run (idempotent upserts; the only two commits in the sync path are
     the entry-stamp commit and one final commit at the end, everything
     between is db.flush() only, so a partial run leaves no partial state
     to double-apply), so alert-only would leave a claimed site sitting dead
     until a human acts — a worse silent-failure mode than today’s, where a
     stuck site is at least visible inside one failed sweep request. Auto-repair
     closes the gap instead of just reporting it; log the re-enqueue event so
     a repeatedly-re-enqueued site is still visible as a pattern, even though
     no single occurrence pages anyone.

7. skipped_over_budget becomes structurally impossible; over_budget
   keeps its current meaning. There is no shared tick deadline once every
   claimed site gets its own full-timeout task, so the “tick ran out before
   this site was reached” failure mode this ADR exists to remove goes away
   by construction. A single site’s own sync can still exceed its own 540s
   task timeout (over_budget, unchanged semantics) — that’s a per-site
   problem, not a fleet-fairness one, and stays out of the circuit breaker
   for the same reason #851 excluded it. Per the issue: the #820 alert
   (sites_sweep_over_budget) should go quiet as a consequence of this
   landing. Leave the alert in place rather than removing it — a silent
   alert here is evidence the fix is holding, and it’s the regression guard
   against some future change re-introducing shared-tick fan-out.

Consequences

Positive:

• Per-site sync budget stops shrinking as the fleet grows — it is a function
  of the Cloud Run request timeout, a constant, not of SWEEP_ROUNDS_PER_TICK.
• Sites stop sharing a fate: today a single slow tenant is a multi-tenant
  event (everyone behind it in the tick loses their share); after this, each
  site’s sync lives or dies on its own.
• Retry/backoff/dead-lettering come from infrastructure the queue already
  does well, instead of three hand-rolled failure tiers spread across
  find_and_claim_due_sites and sync_site_with_failure_tracking.
• The whole “claimed but never reached” bug class that #784, #819, #821, and
  #851 have each chipped at goes away by construction, not by further tuning.
• sync_site’s one-fetch-is-one-authoritative-snapshot invariant is
  preserved unchanged, so engagement-clear and content-pruning correctness is
  untouched — unlike #818’s shape, which would need a delta path plus a
  periodic full reconcile to keep those correct.

Negative / costs:

• A new infra component (Cloud Tasks queue + its own service account + IAM
  bindings) — this repo has no existing Cloud Tasks usage to build on, so
  this is genuinely new surface, not an extension of a proven pattern the
  way the /internal/* OIDC auth reuse is.
• A new failure mode to reason about: claimed-but-never-delivered (queue
  purge, IAM misconfig, endpoint total outage) needs the reconciliation
  check in Decision §6 — without it, a systemic delivery failure would be
  invisible where today’s inline fan-out fails loudly inside one request.
• Rollout risk: this changes the sweep’s execution model for every site,
  including the 8 that exist today. Needs a staging-first validation that
  directly compares outcomes (synced/failed/vanished counts) against the
  current inline fan-out before cutting production over — not just “it
  deployed,” per this repo’s actual e2e-verification bar.
• Accepted tradeoff: at-least-once delivery, not exactly-once. Cloud
  Tasks can redeliver a task even after the handler completed, if the
  response is delayed past the dispatch deadline. sync_site itself is safe
  to re-run from a data-correctness standpoint (idempotent upserts, one
  atomic commit at the end of the sync path), but a duplicate delivery also
  re-runs refresh_presentation_summary’s LLM call and re-fetches the
  customer’s entire site over the network — real duplicate cost and load,
  not a correctness bug. At today’s 8-site scale this is noise; naming it
  here so it isn’t silently absent from the record as the fleet grows.

Mitigations:

• Ship behind the existing sweep endpoint’s own control (e.g., dual-run on
  staging — enqueue via Cloud Tasks but leave the old inline path reachable
  for one cycle — before removing the old path), so a staging comparison
  exists before production ever depends on the new path exclusively.
• The reconciliation check in Decision §6 closes the new blind spot rather
  than accepting it.

Alternatives considered

• Cloud Run Jobs (the issue’s title mentions both). Rejected as the
  mechanism, though not the concept — Jobs are the right shape for “run this
  script to completion” batch work (this is also the shape used for the
  parallel CI-runner migration plan for #1041), but a poor mechanical fit for
  “fan out N independent per-item HTTP calls, each with its own retry/backoff.”
  Jobs’ task-array model needs the container to self-select work by
  CLOUD_RUN_TASK_INDEX, with no native way to hand N distinct payloads
  (site IDs) to N tasks in one execution — that means building a separate
  index→site-id lookup (a DB table or blob the container reads) just to
  reproduce what Cloud Tasks already does natively as “one task, one
  payload.” Jobs also has no backoff/dead-letter concept — a task failed
  after max-retries just marks the execution Failed, versus Cloud Tasks’
  configurable min_backoff/max_backoff/dead-lettering. And each Job
  execution spins up a new container (a cold start) versus Cloud Tasks
  pushing an HTTP request into the service’s already-running, already-warm
  instances. Cloud Tasks’ native payload-per-task model, backoff/dead-letter
  config, and reuse of an already-warm service are the actual reasons it
  fits this problem better — not a difference in retry granularity, which is
  comparable between the two mechanisms.
• Keep serial fan-out, just raise SWEEP_ROUNDS_PER_TICK and/or
  SYNC_ADMISSION_SLOTS further. Rejected per the issue’s own core
  argument: this doesn’t remove the coupling, it only moves the break-even N
  — and the ceiling still moves the wrong direction as the fleet grows,
  since fleet growth is exactly the pressure toward raising
  SWEEP_ROUNDS_PER_TICK, which shrinks the per-site share. Slots are also
  DB-pool-bounded (ADR-0040), so this lever runs out regardless.
• #818 (incremental sync) as a substitute rather than a complement.
  Rejected as a substitute — it reduces work per cadence without removing the
  structural coupling, and (per the Context section above) it breaks the
  one-fetch-one-snapshot invariant that engagement-clear and content-pruning
  correctness depend on. Still worth doing later, on its own merits (less
  load on the customer’s host, cheaper syncs), on top of this ADR rather than
  instead of it.
• Do nothing pre-launch, revisit post-launch. Rejected per the CEO
  priority decision on #852: the defect is tier-independent (a paying Pro
  customer hits the same ceiling, more often), making this a data-integrity
  question for paying customers rather than a Free-tier capacity question,
  and #852’s own sizing shows the queue costs approximately nothing to build
  now against today’s 8-site fleet with no live tenants to migrate across an
  execution-model change later.

Open questions carried forward (not blocking this ADR, but unresolved)

• The single-platform bench band Seth offered to run on #852 (today’s only
  measured number, 388s, is a ten-platform-fit extrapolation that
  disagrees ~8x with the other available extrapolation for a single-platform
  site — neither is measured). This ADR ships with PER_TASK_SYNC_BUDGET_SECONDS ≈ 500s (Decision §3) as the operative per-task ceiling regardless, sitting
  under the 540s Cloud Run hard timeout the same way today’s 480s soft
  deadline sits under it; the bench band would only inform whether either
  number needs to become something other than what’s already the
  service’s real limit today.
• Exact max_concurrent_dispatches tuning beyond the starting value in
  Decision §4 — revisit once real multi-tenant volume exists to tune against.
• sites_sweep_tick’s Cloud Scheduler retry_config (currently count=1,
  30s backoff, sized for “the whole fan-out failed”) becomes close to
  vestigial once sweep_due_sites is a ~1s claim-and-enqueue — a fast op
  failing and retrying once at 30s is a much lower-stakes event than today.
  Not a blocker; leave as-is unless it proves noisy in practice.
• Whether SWEEP_DUE_LIMIT/SWEEP_ROUNDS_PER_TICK still gates the claim
  batch size per tick once “per-site time budget” stops being why they
  exist. If kept, they need a new justification (e.g., bounding how many
  Cloud Tasks CreateTask calls one sweep request makes) rather than
  carrying forward a derivation comment that would otherwise go silently
  stale.