analyze-read-replica-risk

Analyze Read Replica Risk

Surge.ReadRepo is a read replica with ~20ms average lag behind the primary Surge.Repo. Moving a query to it (Repo.all/one/get/preload → ReadRepo.*) is safe ONLY if every caller can tolerate reading data that may be up to ~20ms stale. Your job: given a query function, trace its full usage context and check each call path against the red flags below, then report which paths (if any) trip one.

This is a procedural scan, not a data-imagination exercise. Don't enumerate every way a stale field or a missing row could ripple through the system. Instead, walk each path and ask one question: does it match one of the red-flag patterns below? The red flags are the structural signatures of a lag problem - if a path matches none of them and fits a green-light signal, it's safe.

Workflow

1. Locate the query. Find the function definition the user named. Confirm it actually runs DB reads and isn't already on ReadRepo.

2. Trace the full call graph - not just direct callers. This is the core of the task. Lag risk usually lives several layers up, not at the query itself. Be exhaustive: under-counting paths is the most common way this analysis goes wrong.

   • Find direct callers (Grep the function name across lib/; test/ is for context only).
   • For each caller, find ITS callers. Repeat up the chain - parents, grandparents, and beyond - until you reach a stable entry point (controller action, LiveView event, Oban worker perform/1, channel handler, webhook handler, GenServer callback, public context function with many external callers).
   • Don't stop at an Oban worker perform/1 - climb to who enqueues the job. A worker is a fan-in point: Grep for the worker module (.new(, .insert(, Oban.insert) to find every enqueue site. Different entry points (e.g. different provider webhooks) often funnel into one worker, and each is a distinct path. The enqueue-then-job hop is itself a timing gap where the job can start within ms of the enqueuing write.
   • Expand dispatcher/multiplexer functions. A single function (e.g. a controller create/2, a catch-all webhook handler, a handle_event/3) often serves many event types or routes. Treat each meaningfully different trigger as its own path rather than collapsing them.
   • Follow shared helpers and defdelegates. If the query is reached through a wrapper, plug, or delegated function, trace callers of the wrapper too.
   • Stop climbing a branch only when you understand what triggers it. If a branch fans out very widely into clearly equivalent leaves, note the entry-point category once - but only after confirming the leaves really are equivalent.

3. Check each call path against the red flags below. For each path, scan for the red-flag patterns. Track each distinct path separately - one path may trip a flag while another is clean. A single path that trips a "do NOT move" flag means the query stays on primary (unless you split it).

1. Report (see Output format).

Red flags (do NOT move)

Read-after-write — the data this query returns can be written shortly before this read, then read back from the replica within the ~20ms window. This is one risk; the mechanisms below are just where to look for it.

• Synchronous (gap ≈ 0, race effectively guaranteed): data written earlier in the same request/flow and re-read (create-then-reload, insert-then-return); read inside or immediately after the writing transaction; a controller/LiveView re-querying to show the result of an action the user just performed.
• Asynchronous (gap varies — estimate the real write→read gap): an Oban job reading a record enqueued moments earlier (can start within ms of the insert); a webhook/callback looking up a record written in the same request that triggered it; a PubSub/broadcast-triggered read racing the write that fired the broadcast.
• Latest-row decision: a query that fetches the newest row (order_by: desc(:inserted_at), limit: 1) to read current state - latest delivery status, most recent call leg, newest config - and then branches on it. Callers often don't recognize this as read-after-write, but the replica can return an older "latest" if the newest row hasn't replicated.

To tell whether an asynchronous read-after-write actually fires, count physical wall-clock latency between the write and this read, not logical proximity. A record written in an earlier dashboard/API request and then read by a later inbound provider webhook is not a race - the HTTP round trip plus carrier/provider routing dwarfs 20ms, no matter how logically related the two steps are (buy number → receive SMS; verify demo → send test message). Only same-request reads, same-flow Oban enqueue→job hops, PubSub broadcasts, or two genuinely near-simultaneous requests to our app close the gap.

Correctness-critical reads:

• The result guards a write: uniqueness/existence checks before insert, balance/quota/limit checks, idempotency checks, optimistic locking.
• Billing, usage accumulation, or financial math that enforces (not just reports) state.
• Auth, permission, or ownership checks.

Transactional/consistency:

• Runs inside Repo.transaction / Ecto.Multi (can't span primary + replica).
• Read-modify-write sequences assuming a consistent snapshot.
• Takes a row lock (lock: "FOR UPDATE" / lock: "FOR SHARE"). A replica is a read-only standby - a locking read can't function there, and it's almost always the read half of a read-modify-write meant to serialize concurrent writers.

Green-light signals (safe to move)

• Analytics, reporting, dashboards, exports, list/search views, aggregations.
• Reads where ~20ms staleness is invisible to the user and never feeds a write decision.
• No path writes the same data within ~20ms before the read (see the read-after-write flag).

Verdict levels

• ✅ Safe — no path trips any red flag; every path matches a green-light signal.
• ⚠️ Caution — at least one path trips an asynchronous read-after-write where the estimated gap could plausibly land within ~20ms (Oban ms after an enqueuing write in the same flow, PubSub broadcast, two near-simultaneous app requests), but it isn't expected routinely.
• ❌ Not safe — at least one path trips a synchronous read-after-write, a read inside/after the writing transaction, a correctness-critical read, or a transactional/consistency flag.
• Safe only on some paths — a mix where a separate ReadRepo variant for the clean paths is viable; say which paths go where.

Output format

Concise. Do all of your analysis internally, then output the report with no preamble and no postscript — no "I'll analyze...", no "Let me know if...", nothing before or after the report.

Start the report with the overall verdict as a title, then a short risk summary, then the per-path traces. Wrap the entire report in an outer <details> tag (see file structure below).

Overall verdict

A title line: ### Overall verdict: <emoji> Safe / ### Overall verdict: <emoji> Not safe (use ✅ for Safe, ⚠️/❌ for Not safe — pick the emoji matching the verdict level).

Immediately under the title, a short bulleted list of the red flags you scanned for: which ones fired and on which path, and which ones you checked for and ruled out (one line each, including the ones that didn't survive the physical-latency test). If unsafe on only some paths, note here whether splitting the function (a separate ReadRepo variant for the clean paths) is viable.

Call paths

The <h3>Call paths</h3> inside the inner <details> summary (see below) is the only heading for this section - do not add a separate markdown ## Call paths title. For EACH traced call path, output:

• The path itself (entry point → … → query).
• A 1-2 sentence, high-level, natural-language description of the steps from the call site to the query. Use an active voice ("we look up…", "an inbound webhook fires and we…", "we insert the record, then re-read…"). Describe what happens; don't restate a verdict or analyze the lag risk here - that lives in the risk summary above.

Keep function heads/clauses of the same function in a single entry (see the tracing step): when several heads of the same function, or several entry points that funnel into one shared downstream path, reach the query, list them as one call path and name the individual triggers parenthetically in the heading rather than repeating an entry for each. If some of those heads behave differently in a way that matters to the analysis, keep them in the same entry and note the difference in the description.

Write the report to a file

Write the entire report to a markdown file in the tmp/ directory under the repo root (create tmp/ if it doesn't exist), e.g. tmp/read-replica-risk-<query-name>.md.

Once the file is written, your entire chat response to the user is that the analysis is complete and the report has been written (with the file path). Nothing more - do not restate the verdict, summarize the risks, reproduce any part of the report, ask a follow-up question, or add a code block.

In the file version, wrap the entire report in an outer <details> tag with <summary><h2>AI Analysis</h2></summary>. Inside that, the overall verdict and risk summary come first, then a nested inner <details> wraps only the call-paths section. The inner summary is the call-paths section's only heading - put it in an <h3> inside <summary>, with no markdown ## Call paths header. The file structure looks like:

<details>
<summary><h2>AI Analysis</h2></summary>

### Overall verdict: ✅ Safe

- ...risk summary bullets...

<details>
<summary><h3>Call paths</h3></summary>

...all per-path traces...

</details>

</details>

Example call path traces

### AcmeWeb.StripeWebhookController.create/2 (checkout.session.completed)

`StripeWebhookController.create/2 → Billing.fulfill_checkout/1 → get_active_subscription/1`

Stripe's checkout webhook fires, we insert the subscription row, then immediately re-read it in the same handler to gate the feature unlock.

### AcmeWeb.AnalyticsLive.mount/3 (dashboard subscriber count)

`AnalyticsLive.mount/3 → Subscriptions.count_active/0`

When the dashboard mounts we count active subscriptions to render a summary tile - a pure aggregate that never feeds a write.

### AcmeWeb.InventoryWebhookController.create/2 (warehouse scan)

`InventoryWebhookController.create/2 → Oban.insert(ProcessScan) → perform/1 → get_sku_by_barcode/1`

A warehouse scan webhook enqueues a job, and moments later the job runs and looks up the SKU by barcode to file the scan.