Operations & Recovery Runbook Runbook de Operaciones & Recuperacion

How to operate, diagnose, and recover the data ingestion pipeline — the Hetzner Go ingest service, ClickHouse, the R2 write-ahead log, and the AppsFlyer Pull API. Every recovery action here is idempotent. Como operar, diagnosticar y recuperar el pipeline de ingesta de datos — el servicio Go en Hetzner, ClickHouse, el write-ahead log en R2, y la AppsFlyer Pull API. Toda accion de recuperacion aqui es idempotente.

1. Pipeline health check 1. Chequeo de salud del pipeline

Start every investigation here. The Go ingest service exposes a single JSON health endpoint. Hit it through the Cloudflare Tunnel from anywhere, or directly on the box. Empieza cada investigacion aqui. El servicio Go expone un solo endpoint JSON de salud. Llamalo a traves del Cloudflare Tunnel desde cualquier lado, o directo en el servidor.

Copy# Proxied through the Cloudflare Tunnel (anywhere)
curl -s https://ingest.relo.mx/health | jq

# On the Hetzner box (bypasses the tunnel)
ssh root@178.156.195.219
curl -s http://localhost:4080/health | jq

Key fields to watch Campos clave a vigilar

The fields below tell you the health of every stage. Green vs red signals are noted inline. Los campos de abajo te dicen la salud de cada etapa. Las senales verde vs rojo van anotadas en linea.

• buffer_events — in-memory ClickHouse write buffer depth. 0 = healthy (everything flushed). A steadily rising or large number means CH writes are stalling → red.buffer_events — profundidad del buffer de escritura en memoria hacia ClickHouse. 0 = sano (todo descargado). Un numero que sube de forma sostenida o grande significa que las escrituras a CH se estan atorando → rojo.
• dlq_depth {clicks, pixel, s2s} — dead-letter queue depth per ingest path. All zeros = healthy. Non-zero is recovered automatically by the DLQ loop, but a persistently growing value is red.dlq_depth {clicks, pixel, s2s} — profundidad de la dead-letter queue por ruta de ingesta. Todo en cero = sano. Si no es cero, el loop de DLQ lo recupera solo, pero un valor que crece de forma persistente es rojo.
• last_pull_at — timestamp of the last AppsFlyer pull. Should be recent (within the hour) — pulls run hourly. Stale → AF data is going dark, red.last_pull_at — timestamp del ultimo pull de AppsFlyer. Debe ser reciente (dentro de la ultima hora) — los pulls corren cada hora. Si esta viejo → los datos de AF se estan apagando, rojo.
• last_pull_stats {errors, purchases, rows} — result of that last pull. errors = 0 with non-zero purchases/rows is healthy. Errors > 0 or zero purchases during business hours → red.last_pull_stats {errors, purchases, rows} — resultado de ese ultimo pull. errors = 0 con purchases/rows distintos de cero es sano. Errores > 0 o cero purchases en horario habil → rojo.
• failover.active — false = Hetzner primary is up and serving (good). true means the pipeline has failed over → red, investigate the primary.failover.active — false = el primario Hetzner esta arriba y sirviendo (bien). true significa que el pipeline hizo failover → rojo, investiga el primario.
• recovery.health_check {clickhouse_ok, r2_wal_ok, supabase_ok, dragonfly_ok} — the four dependency probes. All true = green. Any false pinpoints the failing dependency.recovery.health_check {clickhouse_ok, r2_wal_ok, supabase_ok, dragonfly_ok} — las cuatro sondas de dependencias. Todas true = verde. Cualquiera en false senala la dependencia que falla.

2. How ingestion works (the safety nets) 2. Como funciona la ingesta (las redes de seguridad)

Three independent safety nets make the pipeline recoverable. Understanding them is what makes every action below safe to re-run. Tres redes de seguridad independientes hacen el pipeline recuperable. Entenderlas es lo que hace que cada accion de abajo sea segura para re-ejecutar.

• Hourly AppsFlyer Pull API. The service pulls AF purchase data every hour. The pull is idempotent — rows are deduped by sale_hash, so re-pulling any window never double-counts.AppsFlyer Pull API cada hora. El servicio jala data de compras de AF cada hora. El pull es idempotente — las filas se deduplican por sale_hash, asi que re-jalar cualquier ventana nunca duplica conteos.
• R2 write-ahead log (WAL). Every event batch is written to the R2 WAL — gzipped NDJSON files under wal/events/YYYY/MM/DD/HH/ — before the ClickHouse insert. If CH ever drops events, they still live in the WAL and can be replayed.Write-ahead log en R2 (WAL). Cada lote de eventos se escribe al WAL de R2 — archivos NDJSON con gzip en wal/events/YYYY/MM/DD/HH/ — antes del insert en ClickHouse. Si CH llega a perder eventos, siguen vivos en el WAL y se pueden re-insertar.
• ReplacingMergeTree dedup. The CH events table is a ReplacingMergeTree ordered by (…event_id). Re-inserting the same event collapses on the next background merge — so all recovery is idempotent at the storage layer too.Dedup por ReplacingMergeTree. La tabla events de CH es un ReplacingMergeTree ordenado por (…event_id). Re-insertar el mismo evento se colapsa en el siguiente merge de fondo — asi que toda recuperacion es idempotente tambien en la capa de almacenamiento.
• DLQ recovery loops. Ingest failures on the live paths (clicks / pixel / s2s) land in a dead-letter queue and are replayed from R2 automatically — no operator action needed in the normal case.Loops de recuperacion DLQ. Las fallas de ingesta en las rutas vivas (clicks / pixel / s2s) caen en una dead-letter queue y se re-procesan desde R2 automaticamente — sin accion del operador en el caso normal.

3. Symptom: ClickHouse write outage / event gap 3. Sintoma: outage de escritura a ClickHouse / hueco de eventos

The signature of an outage is a stretch of time where events were accepted but never landed in ClickHouse. Detect it two ways: La firma de un outage es un periodo donde los eventos fueron aceptados pero nunca llegaron a ClickHouse. Detectalo de dos maneras:

• Daily-count dip. Group relo.events by day on event_time and compare against neighboring days. A dip (or a flat spot) that isn't a weekend/holiday is the gap.Caida en el conteo diario. Agrupa relo.events por dia usando event_time y compara contra los dias vecinos. Una caida (o un valle plano) que no sea fin de semana/feriado es el hueco.
• Service logs. Look for WAL replay needed or buffer overflow in journalctl -u relo-ingest. Those lines are emitted exactly when CH drops re-queued events that are still in the WAL.Logs del servicio. Busca WAL replay needed o buffer overflow en journalctl -u relo-ingest. Esas lineas se emiten justo cuando CH descarta eventos re-encolados que siguen en el WAL.

Copy# 1) Daily event counts — look for a dip vs neighbors
ssh root@178.156.195.219 "clickhouse-client -d relo -q \
  \"SELECT toDate(event_time) AS d, count() AS n
    FROM relo.events
    WHERE event_time >= now() - INTERVAL 14 DAY
    GROUP BY d ORDER BY d\""

# 2) Scan the logs for the overflow / replay signal
ssh root@178.156.195.219 "journalctl -u relo-ingest --since '2 days ago' \
  | grep -iE 'buffer overflow|WAL replay needed'"

4. Recovery A — re-pull AppsFlyer (MMP / AF data gaps) 4. Recuperacion A — re-jalar AppsFlyer (huecos de data MMP / AF)

Use this when the gap is in AppsFlyer-sourced data (purchases pulled from AF). Because the pull dedups by sale_hash, it is safe to re-run any window — you can't double-count. Usa esto cuando el hueco es de data originada en AppsFlyer (compras jaladas de AF). Como el pull deduplica por sale_hash, es seguro re-ejecutar cualquier ventana — no puedes duplicar conteos.

Trigger a manual pull with POST /pull/trigger and an admin Bearer token (ADMIN_TOKEN). The JSON body sets the window in RFC3339 (from / to); omit the body to run the default hourly window. The pull runs in the background and returns 202 immediately — watch last_pull_at on /health for progress. Dispara un pull manual con POST /pull/trigger y un Bearer token de admin (ADMIN_TOKEN). El body JSON define la ventana en RFC3339 (from / to); omite el body para correr la ventana default por hora. El pull corre en segundo plano y regresa 202 de inmediato — vigila last_pull_at en /health para ver el progreso.

Copy# Re-pull a narrow window (proxied through the tunnel)
curl -X POST https://ingest.relo.mx/pull/trigger \
  -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"from":"2026-05-28T00:00:00Z","to":"2026-05-30T00:00:00Z"}'

# Response (202 Accepted):
# {"ok":true,"status":"started","note":"pull running in background — watch /health last_pull_at"}

On the Hetzner box, read ADMIN_TOKEN straight from the systemd environment so you never paste the secret: En el servidor Hetzner, lee ADMIN_TOKEN directo del environment de systemd para nunca pegar el secreto:

Copyssh root@178.156.195.219

# Pull ADMIN_TOKEN from the running unit's environment
ADMIN_TOKEN=$(systemctl show relo-ingest -p Environment \
  | tr ' ' '\n' | grep '^ADMIN_TOKEN=' | cut -d= -f2-)

curl -X POST http://localhost:4080/pull/trigger \
  -H "Authorization: Bearer $ADMIN_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"from":"2026-05-28T00:00:00Z","to":"2026-05-30T00:00:00Z"}'

5. Recovery B — WAL replay (pixel / click events) 5. Recuperacion B — replay del WAL (eventos de pixel / click)

Use this when the gap is in pixel or click events that can't be re-pulled from AF. When the ClickHouse buffer overflows it drops events that are already in the R2 WAL — this endpoint reads them back from the WAL and re-inserts them into CH. Usa esto cuando el hueco es de eventos de pixel o click que no se pueden re-jalar de AF. Cuando el buffer de ClickHouse se desborda, descarta eventos que ya estan en el WAL de R2 — este endpoint los lee del WAL y los re-inserta en CH.

POST /admin/wal-replay?hours=N with an admin Bearer token. hours defaults to 6 and is capped at 48 (so a single call never floods the live buffer). It returns {files_scanned, events_replayed, errors}. Replay is idempotent — already-present events collapse via the ReplacingMergeTree, so over-shooting the window is harmless. POST /admin/wal-replay?hours=N con un Bearer token de admin. hours default es 6 y esta limitado a 48 (asi una sola llamada nunca inunda el buffer vivo). Regresa {files_scanned, events_replayed, errors}. El replay es idempotente — los eventos ya presentes se colapsan via el ReplacingMergeTree, asi que pasarte de la ventana es inofensivo.

Copy# Replay the last 12 hours of the events WAL
curl -X POST "https://ingest.relo.mx/admin/wal-replay?hours=12" \
  -H "Authorization: Bearer $ADMIN_TOKEN"

# Response:
# {"ok":true,"hours":12,"files_scanned":48,"events_replayed":91204,"errors":[]}

6. Verify recovery 6. Verifica la recuperacion

Confirm the gap is filled and the pipeline is back to baseline. Re-run the daily-count query and check that the dipped day now matches its neighbors. Then re-check /health. Confirma que el hueco se lleno y que el pipeline volvio al baseline. Re-ejecuta la consulta de conteo diario y verifica que el dia que cayo ahora coincide con sus vecinos. Luego re-revisa /health.

Copy# Re-query daily counts — the previously-dipped day should now match
ssh root@178.156.195.219 "clickhouse-client -d relo -q \
  \"SELECT toDate(event_time) AS d, count() AS n
    FROM relo.events
    WHERE event_time >= now() - INTERVAL 14 DAY
    GROUP BY d ORDER BY d\""

# Confirm the pipeline is back to baseline
curl -s https://ingest.relo.mx/health | jq '{buffer_events, dlq_depth, last_pull_at, last_pull_stats}'

Recovery is complete when buffer_events is back to 0, all dlq_depth entries are zero, and the daily counts have no remaining dip. La recuperacion termina cuando buffer_events volvio a 0, todas las entradas de dlq_depth estan en cero, y los conteos diarios ya no tienen ningun hueco.

7. Backbone deploy 7. Deploy del backbone

Build a Linux binary, copy it up, swap it in with a .bak backup, restart, and verify health. The backup makes a rollback a one-line copy if anything looks wrong after restart. Compila un binario Linux, subelo, intercambialo con un respaldo .bak, reinicia, y verifica la salud. El respaldo hace que un rollback sea una copia de una linea si algo se ve mal despues del reinicio.

Copy# 1) Build the linux/amd64 binary (from the repo root)
cd backbone && GOOS=linux GOARCH=amd64 go build -o relo-ingest-new ./cmd/ingest/

# 2) Copy it to the box
scp relo-ingest-new root@178.156.195.219:/opt/relo/relo-ingest-new

# 3) Swap (with a .bak backup), restart, then verify
ssh root@178.156.195.219 "systemctl stop relo-ingest \
  && cp /opt/relo-ingest/relo-ingest /opt/relo-ingest/relo-ingest.bak \
  && cp /opt/relo/relo-ingest-new /opt/relo-ingest/relo-ingest \
  && chmod +x /opt/relo-ingest/relo-ingest \
  && systemctl start relo-ingest"

# 4) Verify it came back up
curl -s https://ingest.relo.mx/health | jq '.status'
ssh root@178.156.195.219 "journalctl -u relo-ingest -f"

8. Escalation note 8. Nota de escalacion

For AppsFlyer-sourced gaps, also reconcile against the AppsFlyer dashboard as the external source of truth. The Pull API has a limited lookback window — very old gaps may not be fully re-servable by the API even though a re-pull runs cleanly. If a re-pull reports zero rows for a date you know had sales, the data has aged out of AF's servable range; export the window from the AppsFlyer dashboard and reconcile manually instead of assuming the pull failed. Para huecos originados en AppsFlyer, reconcilia tambien contra el dashboard de AppsFlyer como fuente de verdad externa. La Pull API tiene una ventana de lookback limitada — huecos muy viejos pueden no ser re-servibles por completo via la API aunque el re-pull corra limpio. Si un re-pull reporta cero filas para una fecha que sabes que tuvo ventas, la data ya salio del rango servible de AF; exporta la ventana desde el dashboard de AppsFlyer y reconcilia manualmente en lugar de asumir que el pull fallo.