Production-Safe Scalable Periodic Data Ingest for Rails

You are a senior backend architect. Design a production-safe way to run a periodic data-ingest job as the system scales. Do not give generic advice like “just use a lock.” Propose a concrete design, failure modes, and what must never happen.

## Context

Rails 6 / Ruby 3 app on **one VM** (4 CPUs, 16 GB RAM). Same host runs:

- Puma web (5 threads, 1 worker) behind nginx
- Puma API
- Sidekiq
- MySQL
- cron every **15 minutes** that boots a **full Rails process** and runs `data_fetch:fetch_and_process`

That rake task:

1. Reads a watermark table `last_end_times` (single row, `end_time` as a string).
2. If watermark is within the last **2 hours**, resume from it. **If it is older than 2 hours, it discards the gap and starts from ****`Time.now`****.** This is a known data-loss bug.
3. Fetches a **20-minute** window from an upstream HTTP API (AQ, life-safety, node state, motion).
4. `upsert_all` into MySQL (`readings` unique on `sensor_id, created_at, agg_type`).
5. **Only then** advances the watermark by +15 minutes.
6. **In the same process**, runs heavy post-processing (high-AQI rollups, LS analytics, notify). That post-work can last **hours**, long after the fetch finished.

There was **no overlap lock**. Cron kept starting a new Rails process every 15 minutes. We saw **50+ concurrent copies**, load \~35, MySQL with 60+ running queries, Puma threads blocked, nginx **504**. Oldest stuck process was \~15 days. The fetch itself was often already done; processes were stuck in post-processing.

We added a temporary `flock -n` in the cron wrapper so a second `data_fetch` exits immediately if one is running. That stops the pile-up. It does **not** fix catch-up or scale.

Puma also crash-looped (`EADDRINUSE` on :3000, 17k restarts) because an orphan held the port and `Restart=always` had no start limit. Every Rails boot also rewrote crontab via an initializer. Treat process supervision as in-scope only if it affects the ingest design.

## Scenario that must be handled

At 10:00 the job starts. Upstream returns a **very large** payload (example: **3 GB** / millions of rows). Saving to MySQL takes **longer than 15 minutes**. At 10:15 cron fires again. Job A is still writing.

We need a design that still works when:

- One window is huge (minutes to hours to persist)
- The host is busy and jobs run longer than the interval
- We scale to **more buildings, more sensors, more frequent pulls, or multiple app servers**
- The process is killed mid-`upsert` (partial batch)
- Upstream is slow or returns 500
- Watermark is hours or days behind
- Post-processing (analytics) is slow or broken
- Two app nodes both have cron/Sidekiq (split-brain)

## Constraints

- Must not lose or silently skip time windows (the 2-hour “jump to now” is unacceptable).
- Must not run two writers for the same job on one host or across hosts.
- Web/API must stay up; ingest must not be allowed to saturate MySQL/CPU the way it did.
- Upserts are idempotent; re-fetching a window is OK.
- A skipped cron tick is better than overlap.
- Catch-up must be **rate-limited** (one window at a time or a low-priority queue), not “replay 3 days in one process.”
- Prefer using existing Sidekiq if that is the right tool; say if cron should only enqueue.
- MySQL is the source of truth for the watermark unless you justify something better (Redis lock + DB watermark, etc.).

## What I want from you

1. **Target architecture** for ingest at scale (who schedules, who locks, who writes, who advances the watermark).
2. **Exact behavior** for the 3 GB / still-writing / next-tick case, step by step, including what the watermark does.
3. **How catch-up works** when we are 2 hours or 2 days behind — without jumping to now and without melting the box.
4. **How to split** “fetch+upsert” from “analytics/notify” so homework cannot block the next fetch forever.
5. **Locking**: local `flock` vs Redis/DB advisory lock vs Sidekiq unique jobs — what survives **multiple servers**.
6. **Timeouts**: what to kill vs what must be allowed to finish (do not kill a healthy large chunked write with a naive 12-minute timeout).
7. **Chunking** the 3 GB write (batch size, watermark granularity, crash recovery).
8. **Observability**: metrics/alerts that would page before 504s.
9. **Migration plan** from current rake+cron+flock with **low risk** to the live site and **no CPU increase** in steady state.
10. **What you would not do** (anti-patterns).

Call out remaining data-loss or dual-writer risks. Prefer a design that stays correct when we add a second worker host.