System Design

1. Capacity and retention

Assume readings contain an authenticated meter identity, UTC interval_start, flow measurement and payload version. Intervals align to 15-minute boundaries. The logical identity is (meter_id, interval_start), independent of upload attempts.

Steady-state ingestion:

• Readings per meter per day: 24 × 60 / 15 = 96.
• Fleet readings per day: 1,200,000 × 96 = 115,200,000.
• Average readings per second: 1,200,000 / 900 = 1,333.33.
• Raw daily volume: 115,200,000 × 200 = 23,040,000,000 bytes = 23.04 GB.
• Raw storage for 400 days: 23.04 × 400 = 9,216 GB.
• With three replicas: 9,216 × 3 = 27,648 GB = 27.648 TB.

These are payload-only estimates using decimal units. Indexes, record framing, temporary duplicate uploads, operational logs and filesystem overhead require additional capacity. Apply the 400-day lifecycle to canonical raw readings, not every retry copy. Maintain billing ledgers separately according to the utility’s financial retention policy.

2. Reconnection sizing and ingestion

The storm disconnects 1,200,000 × 30% = 360,000 meters. Each buffers 6 × 4 = 24 readings, giving 360,000 × 24 = 8,640,000 backlog readings.

With uploads spread over 600 seconds:

• Backlog rate: 8,640,000 / 600 = 14,400 readings/second.
• Concurrent live rate: 1,333.33 readings/second.
• Peak sustained unique rate: 15,733.33 readings/second.
• Corresponding wire throughput: 15,733.33 × 200 = 3.1467 MB/second.

This is the specified ten-minute sustained peak, not a bound on instantaneous batches or retry traffic. Pace backlog uploads across the reconnection window and enforce per-meter fairness so live readings are not starved.

Use stateless authenticated gateways, a replicated durable log partitioned by meter, independent consumer groups, canonical storage, a dashboard projection and a leak processor. Provision gateways for at least 60,000 upload attempts/second and downstream consumers for 30,000 unique readings/second. These provide retry headroom; they do not assume retries have a mathematically bounded maximum.

Acknowledge each reading only after durable quorum persistence, with replicas across failure domains. Partial batch acknowledgements identify precisely which records are durable. Unacknowledged records remain eligible for retry. Gateways acknowledge well within 30 seconds under the stated load, preventing retry amplification during ordinary operation.

The backlog itself occupies 8,640,000 × 200 = 1.728 GB, or 5.184 GB replicated. Reserve at least 100 GB of replicated log capacity, with additional framing headroom. Never evict unprocessed acknowledged records: extend retention or spill to durable storage if consumers fall behind.

Queues absorb batches and consumer interruptions. Autoscaling, backpressure and prioritized admission protect durability. The six-hour backlog leaves 42 hours before meter overwrite; the designed ingestion capacity drains it within the ten-minute upload window. No system can recover readings already overwritten, or guarantee delivery through an outage exceeding the meter’s buffer limit.

3. Billing and dashboard correctness

The billing database enforces exactly-once counting, not the transport. Its interval ledger has a unique constraint on (meter_id, interval_start).

For each consumed reading, one database transaction inserts the interval and updates its billing-period accumulator only if the insert succeeds. A duplicate becomes a no-op. The consumer commits its log offset only after that transaction commits; crashes therefore cause safe replay. Conflicting payloads for one identity are quarantined rather than silently replacing a billed value. Approved corrections use an explicit adjustment ledger.

Billing closes only after completeness reconciliation. Missing readings remain exceptions requiring utility policy, never invented usage. This guarantees each received valid interval is counted once despite retries.

The dashboard independently upserts the same key. Track arrival-to-visibility latency and provision its normal path for the five-minute 99th-percentile target. During storms, scale consumers and drain backlog within 60 minutes. Display measurement time and freshness so late historical readings do not appear current.

4. Event-time leak detection

Leak processing uses interval_start, never arrival order. Store deduplicated readings in an ordered per-meter interval map. A candidate alert requires 96 adjacent interval starts, spaced exactly 15 minutes apart, each with non-zero flow.

Late readings can connect previously separate fragments, so immediately alerting on every observed fragment could produce multiple notifications for one eventual streak. Instead, issue notifications only from a finalized event-time prefix.

Add a meter upload-completeness protocol: a meter reports a frontier only after every earlier interval is either durably acknowledged or explicitly declared unavailable because it expired. The server advances the processing frontier only after those acknowledged readings have reached canonical storage. Pending retries and in-flight batches prevent premature advancement. Persist these frontiers and gap declarations.

Do not assume that “48 hours old” alone proves completeness: transmission and processing can add delay. Disconnected meters cannot advance their frontier, so their alerts wait for reconciliation. No alert-latency requirement was specified.

A missing interval is unknown, not zero and not non-zero. It prevents any 96-interval window crossing it from qualifying. While unresolved, it also prevents finalization. Once explicitly finalized as unavailable, it breaks the verified consecutive streak without asserting that water stopped flowing.

Process finalized intervals chronologically, maintaining consecutive non-zero count and an alerted flag. At count 96, transactionally create one notification-outbox entry keyed by meter and verified streak start. Further non-zero intervals retain the flag. A finalized zero or unavailable interval ends that verified streak. Durable notification identifiers and an idempotent delivery endpoint prevent replay from creating another logical alert.

5. Exactly three trade-offs

1. Chosen: durable-log-first acknowledgement. Rejected: acknowledge before persistence. Quorum writes add latency and infrastructure cost, but retries remain safe and acknowledged readings survive gateway failures.

2. Chosen: transactional interval ledger for billing. Rejected: trusting transport-level exactly-once delivery. Database uniqueness and atomic accumulator updates cost write capacity, but directly protect the financial counting invariant across replay and crashes.

3. Chosen: finalized event-time leak alerts. Rejected: immediate arrival-order alerts with later corrections. Completeness tracking delays notifications, particularly after disconnection, but prevents late buffered data from merging independently notified fragments into one streak.

Design and capacity

Use a durable, replicated ingestion log between meter-facing gateways and downstream services. Gateways authenticate meters, validate the interval timestamp and payload, and publish readings keyed by (meter_id, interval_start). Partition the log by meter ID so one meter’s records are ordered, while spreading meters across partitions. Acknowledge an upload only after the record is durably replicated to the required replicas. If the acknowledgement is lost, the meter may retry; the system accepts the duplicate rather than risking data loss.

The steady-state rate is:

• 1,200,000 meters ÷ 900 seconds per interval = 1,333.3 readings/second.
• 1,200,000 × 96 intervals/day = 115,200,000 readings/day.
• 115,200,000 × 200 bytes = 23,040,000,000 bytes/day = 23.04 GB/day.
• Over 400 days: 23.04 GB × 400 = 9,216 GB = 9.216 TB of raw readings. With three replicas: 9.216 TB × 3 = 27.648 TB.

These figures use the specified on-wire record size and decimal units; they exclude indexes, storage-format overhead, and operational headroom. Store the complete raw history in a replicated, time-partitioned archive, and retain the ingestion log long enough to cover normal consumer recovery rather than using it as the 400-day archive.

For the stated storm, 30% × 1,200,000 = 360,000 meters are affected. Six hours contains 6 × 4 = 24 intervals, so the reconnecting meters upload 360,000 × 24 = 8,640,000 buffered readings. Spread across 10 minutes, that is 8,640,000 ÷ 600 = 14,400 readings/second. Add live traffic from all meters, 1,333.3 readings/second, for a nominal peak of approximately 15,733 readings/second. The 10-minute arrival payload is about 9.44 million × 200 bytes = 1.888 GB, including approximately 800,000 live readings.

Provision gateway and log-write capacity for at least 32,000 readings/second sustained—about twice the calculated peak—and reserve queue capacity for at least the full 9.44-million-record arrival window, with additional space for retries and processing lag. The replicated log absorbs the burst while consumers catch up; independently scalable consumers then write the archive, billing store, dashboard view, and alert state. This capacity absorbs the described burst without dropping accepted readings. The meter’s 48-hour overwrite limit is an upstream limitation: readings overwritten before reconnection cannot be recovered by the backend.

Correctness and serving

Billing uses a canonical reading table with a database-enforced unique constraint on (meter_id, interval_start). Its sink performs an insert-or-conflict operation: the first copy inserts the reading, and retries or repeated uploads with the same key do not create another billable interval. Billing aggregates only this canonical table. The sink commits the reading and its log-consumer progress transactionally, or uses replay-safe writes, so a crash and replay cannot double-count. A duplicate with conflicting values should be flagged for investigation rather than silently counted as another interval.

A separate consumer maintains a customer-facing materialized view. It upserts by the same interval key and updates the displayed latest reading according to interval time, not arrival order. Monitor end-to-end arrival-to-view latency and scale consumers and partitions to meet the normal-operation target that 99% are visible within five minutes. During the specified burst, the view may lag up to 60 minutes; the durable log and archive remain intact while it catches up. Customers can be shown freshness information so a delayed view is not mistaken for a current reading.

Leak detection is an event-time computation keyed by meter and ordered by interval_start. Deduplicate each interval before evaluating it, and retain interval results so a late reading can update state and cause the affected run of intervals to be recomputed. Do not decide that an interval is missing merely because a later interval arrived first. Advance a completeness watermark only after all log records received up to the relevant ingestion time have been processed, and set it at least 48 hours behind ingestion time, with extra operational grace for queue delay. This accounts for the maximum meter buffering period.

A missing interval is treated as not showing non-zero flow: once it is behind that completeness watermark and no reading exists, it breaks a consecutive run. Before then it is unresolved, not a zero. Once 96 consecutive finalized intervals all show non-zero flow, emit one alert for that streak. Persist an alert identity based on the meter and streak, and use an idempotent notification outbox so replay does not send another alert for the same streak. Waiting for completeness can delay an alert by up to the lateness horizon, but prevents a late, out-of-order reading from producing an incorrect result.

Exactly three trade-offs

1. Chosen: replicated log before consumers. Rejected: synchronous writes from gateways to every destination. The log adds infrastructure and some latency, but buffers reconnection bursts and isolates ingestion from slow billing or dashboard services.
2. Chosen: asynchronously updated dashboard materialized views. Rejected: synchronous dashboard fan-out during ingestion. Eventual updates meet the stated five-minute normal and 60-minute burst targets while keeping customer queries fast; synchronous fan-out would couple meter acceptance to dashboard availability and burst load.
3. Chosen: watermark-based, late-data-aware leak alerts. Rejected: immediate alerts from arrival order. Waiting for the bounded lateness window costs alert speed, but supports correct interval-based streaks and explicit handling of missing data.

1. Capacity estimates

Steady-state rate

• 1,200,000 meters ÷ 900 s per interval = 1,333 readings/s
• 1,333 × 200 B ≈ 267 KB/s

Raw volume per day

• Readings per day: 1,200,000 × 96 intervals = 115.2 M
• Bytes per day: 115.2 M × 200 B = 23.04 × 10^9 B = 23.04 GB/day

Raw storage for retention

• One copy: 23.04 GB × 400 days = 9,216 GB = 9.216 TB
• With 3 replicas: 9.216 TB × 3 = 27.65 TB

Indexes and compaction overhead come on top of this. Columnar compression will probably cut it a lot.

2. Reconnection burst

Peak rate

• Offline meters: 30% × 1.2 M = 360,000
• Buffered readings per meter: 6 h × 4 intervals/h = 24
• Total backlog: 360,000 × 24 = 8.64 M readings
• All meters reconnect within 600 s, spread evenly: 8.64 M ÷ 600 = 14,400 readings/s
• Add live traffic: 14,400 + 1,333 = 15,733 readings/s. That is about 11.8× steady state, or roughly 3.15 MB/s.
• If acknowledgements slow down, retries could double this, so I provision for about 32,000 readings/s.

How the design absorbs it

1. Stateless gateways (MQTT/HTTPS behind a load balancer) check meter credentials and the payload schema. They are sized for 32k/s plus N+1 spare capacity.
2. Durable log (Kafka, replication factor 3, acks=all, partitioned by meter_id). The gateway sends the meter its acknowledgement only after the log write is confirmed. Every acknowledged reading is therefore durable, and an unacknowledged reading gets retried by the meter. Either way, nothing is lost. A few MB/s is trivial for Kafka, so writes stay fast and acknowledgements return well inside the 30 s timeout, which keeps retries rare.
3. Two topics. The gateway sends readings with interval_start in the last 30 minutes to live, and everything older to backfill. Consumers drain live first, so the 5-minute dashboard target holds for live data even during a storm.
4. Downstream consumers absorb the backlog. They do not need to keep up with the peak. At 5,000 readings/s, the 8.64 M backlog clears in about 29 minutes, which is inside the relaxed 60-minute dashboard limit. Kafka retention is 7 days, so even a consumer outage loses no data.
5. Meter-side jitter. Firmware adds random backoff on reconnect and on retry, which smooths the burst further.

3. Exactly-once billing

The billing ledger database enforces exactly-once counting. It uses the primary key (meter_id, interval_start).

• The billing consumer writes with INSERT … ON CONFLICT (meter_id, interval_start) DO NOTHING.
• Retried uploads, gateway replays and consumer reprocessing after a crash all just hit the conflict and change nothing.
• Kafka offsets are committed only after the database write succeeds. This gives at-least-once delivery into an idempotent sink, which works out to exactly-once in effect.
• If a duplicate arrives with a different value, the first stored value is kept and the conflict goes to a reconciliation queue. It is never added.
• Billing totals are computed only from this deduplicated table.
• The dashboard store (a time-series database) uses the same key as an upsert, so its charts are also free of duplicates.

4. Leak alerts in event time

A stateful stream processor (for example Flink), keyed by meter_id, reads both topics and works in event time (interval_start), not arrival time.

Per-meter state

• A bitmap over the last 296 intervals (48 h plus 24 h of history plus slack). Each slot is nonzero, zero or unknown.
• The current streaks, each identified by its first interval.
• A flag for each streak recording whether it has already raised an alert.

Rules

• A late reading fills its slot, wherever it falls. Arrival order does not matter, and a duplicate is a no-op.
• An alert fires only when 96 consecutive slots are all present and all nonzero. Because every slot must be filled with confirmed data, a later arrival can never invalidate an alert that has already been raised.
• Missing interval. While a slot is less than 48 h old (plus 1 h grace), it is unknown. It neither extends nor breaks a streak, and the alert simply waits. Once interval_start + 49 h has passed, the reading can never arrive, so the slot is finalised as missing, which breaks the streak. An alert is never raised on unverified data.
• One alert per streak. The alert record is keyed (meter_id, streak_start) in an alerts table with a unique constraint, so a reprocessed event cannot send a second alert. A streak ends at a zero reading or a finalised gap. If a late reading fills a gap and joins two streaks, the merged streak keeps the earliest streak_start and inherits the "already alerted" flag, so it still gets only one alert.
• State is checkpointed to durable storage. After a restart, the processor replays from the committed offsets and rebuilds the same state.

5. Trade-offs

1. When to acknowledge a reading

   • Chosen: acknowledge after the replicated log write. This guarantees no reading is lost and costs a few milliseconds per acknowledgement.
   • Rejected: acknowledge on receipt, from memory. It is faster, but a gateway crash would silently drop readings that the meter has already deleted from its buffer.

2. How to remove duplicates

   • Chosen: idempotent writes with a unique key in the billing ledger. This is simple, easy to check, and survives any replay from any source.
   • Rejected: end-to-end Kafka exactly-once transactions. They do not catch duplicates created before Kafka, such as a meter retrying after a lost acknowledgement. They also add coordination cost and are hard to operate.

3. How to treat a missing interval in leak detection

   • Chosen: a finalised gap breaks the streak. This means no false alerts based on guessed data, and every alert can be backed up to the customer. The cost is that a real leak with a 48-hour connectivity gap may be detected late.
   • Rejected: treat gaps as continuing the streak, or fill them in from neighbouring readings. This catches more leaks, but it raises alerts based on readings that never existed and makes the "one alert per streak" rule unclear.

Summary

• Capacity: 1,333 readings/s steady, 23.04 GB/day, 27.65 TB stored with 3 replicas.
• Burst: peak of 15,733 readings/s, absorbed by a durable log that acknowledges only after a confirmed write and by priority for live data.
• Billing: exactly-once counting is enforced by the ledger's unique key.
• Leak alerts: computed in event time, never raised on unverified data, and limited to one per streak.

1. Capacity estimates

Steady state

• Intervals per day: 24 h × 4 = 96.
• Readings per second: 1,200,000 / 900 s = ≈1,333 readings/s. This is about 267 KB/s on the wire.
• Readings per day: 1,200,000 × 96 = 115.2 M.
• Raw volume per day: 115.2 M × 200 B = 23.04 GB, so ≈23 GB/day.
• Retention: 23.04 GB × 400 days = 9,216 GB ≈ 9.2 TB of one copy.
• With 3 replicas: 9.216 TB × 3 = ≈27.6 TB (27,648 GB).
• This excludes indexes and compression. I would add about 30% headroom, giving a provisioned figure of about 36 TB.

2. Reconnection burst

• Affected meters: 30% × 1.2 M = 360,000.
• Buffered intervals per meter: 6 h × 4 = 24.
• Backlog: 360,000 × 24 = 8.64 M readings, which is 1.73 GB.
• Upload window: 10 min = 600 s, spread evenly, so backlog rate = 8.64 M / 600 = 14,400/s.
• Live traffic continues at 1,333/s.
• Peak = 14,400 + 1,333 ≈ 15,733 readings/s, about 11.8× steady state, or about 3.1 MB/s.

Retries add to this. If acknowledgements are delayed past 30 s, meters resend. I therefore size the ingest path for 2× peak, about 32,000/s. In practice that means 8 stateless ingest nodes at roughly 5,000/s each, with N+2 spare.

How nothing is lost

1. The ingest gateway validates a reading and appends it to a durable, partitioned log such as Kafka. It uses acks=all, replication factor 3 and min.insync.replicas=2. It sends the meter its acknowledgement only after that append succeeds.
2. The log has 48 partitions keyed by meter_id and keeps 7 days of data. The burst is about 3 MB/s, so it is trivial for the log. Consumers can fall behind by minutes or hours without loss.
3. If the gateway or log is overloaded, it returns 503 or no acknowledgement. The meter keeps the reading and retries. Its 48-hour buffer is covered against a 6-hour outage plus recovery, which leaves about 42 hours of margin. Back-pressure therefore never drops data.
4. Downstream consumers (billing writer, dashboard projector, leak processor) have separate consumer groups. They commit offsets only after their writes succeed, so a crash causes replay, not loss.
5. Consumers are sized for about 30,000/s. The 8.64 M backlog then drains in about 5 minutes, which is well inside the 60-minute relaxed dashboard budget. In normal operation, lag stays in seconds, which meets the 5-minute target for 99% of readings.

3. Exactly-once billing

Delivery is at-least-once everywhere: meter retries, gateway retries and log replays. Exactly-once effect is enforced by one component: the reading store, a replicated SQL store (3 replicas, sharded by meter_id). It has a primary key (meter_id, interval_start).

• The billing writer runs INSERT … ON CONFLICT DO NOTHING. A duplicate is a no-op, whether it comes from a meter retry, a replay or a consumer restart.
• Billing totals are computed by querying this table. They are never kept as stream counters that could be incremented twice.
• interval_start is set by the meter and aligned to the 15-minute grid. The gateway rejects misaligned values and quarantines them.
• If a duplicate key arrives with a different payload, the first write wins. The conflict is written to a discrepancy table for review, so billing is never silently altered.
• Downstream consumers act only on rows that the insert reports as newly created. The dashboard and leak processor are therefore also deduplicated.

This store is also the raw store. Its 27.6 TB is the replicated figure computed above.

4. Leak alerts under late and out-of-order data

The rule is judged by interval_start, so the processor uses event time and holds no arrival-time windows. It is a stateful stream job keyed by meter_id that consumes only newly inserted readings.

State per meter (about 70 bytes):

• A ring of 2 bits per interval, covering the last 288 intervals (72 hours). The three values are unknown, zero and non-zero.
• alerted_range = [start, end] for the last alerted streak.

On each reading at interval t:

1. Set the slot for t.
2. If it is non-zero, scan left and right from t to find the maximal contiguous run of non-zero intervals that contains t.
3. If the run length is ≥ 96 and it does not overlap alerted_range, emit one alert. Then set alerted_range to the run.
4. If the run overlaps alerted_range, extend alerted_range and emit nothing.

Because the state depends only on the set of readings and not on their order, a batch arriving in reverse order gives the same result as in-order arrival. A late reading that fills a hole can join two shorter runs into a 96-long run, and the alert fires then. A zero reading ends the streak. The next qualifying run is a new streak and may alert again.

Alerts are written to an outbox in the same transaction as alerted_range, and a unique key (meter_id, streak_start) backs this up. Replays and restarts therefore cannot send a second alert.

Missing intervals. A missing interval is unknown. It is neither zero nor non-zero, and it is never interpolated. It breaks contiguity, so no alert is raised across a gap. The streak is re-evaluated automatically if the missing reading arrives later, which can happen up to 48 hours after the interval. After 48 hours it is permanently lost and the streak stays broken. This favours no false alarms, at the cost of a possible delayed or missed alert after a long outage. If it turns out that missed alerts are worse than false ones, a separate "data gap" notice can be added.

State older than 72 hours is evicted. Readings older than the 48-hour limit cannot legitimately arrive, so they are logged and ignored.

5. Trade-offs

1. When to acknowledge the meter.

• Chosen: acknowledge after the durable log append.
• Rejected: acknowledge after the SQL store write.
• Reason: the log write is a fast append that is not affected by database load. A burst therefore cannot stretch acknowledgement latency beyond 30 s and trigger retry storms.
• Cost: the data is not yet queryable when acknowledged. This is acceptable because billing tolerates delay and the dashboard allows 60 minutes during a burst.

2. Where to deduplicate.

• Chosen: a database primary-key constraint with idempotent insert.
• Rejected: stream-level deduplication, meaning Kafka transactions or an in-memory or Bloom-filter "seen" cache.
• Reason: Kafka transactions cover only the log hop, not meter retries that create new messages. A cache is bounded and can be lost on restart, and a Bloom filter has false positives, which would drop a billable reading.
• Cost: every duplicate costs one cheap conflicting insert. At 2× peak this is well within store capacity.

3. How to evaluate leaks.

• Chosen: an event-driven per-meter bitmap, evaluated on every new reading.
• Rejected: a periodic batch scan or watermark-closed windows.
• Reason: the bitmap is order-independent and tolerates 48-hour lateness without any watermark delay, so alerts fire as soon as the last missing interval arrives. Windows would need a 48-hour watermark and would delay every alert, or else would emit wrong results that need later correction.
• Cost: about 85 MB of keyed state (1.2 M × about 70 B) plus careful state recovery from checkpoints.

Regional Smart Water-Meter Ingestion Architecture

1. Capacity Estimates and Arithmetic

Steady-State Readings per Second

• Meter fleet: $1,200,000$ meters.
• Interval: Every 15 minutes ($15 \times 60 = 900$ seconds).
• Calculations:
  $$\text{Steady-State Throughput} = \frac{1,200,000\text{ readings}}{900\text{ seconds}} = 1,333.33\text{ readings/second}$$

Raw Data Volume per Day

• Readings per meter per day: $\frac{24\text{ hours} \times 60\text{ minutes}}{15\text{ minutes}} = 96\text{ readings/day}$.
• Total daily readings: $1,200,000 \times 96 = 115,200,000\text{ readings/day}$.
• Payload size: $200\text{ bytes/reading}$ on the wire.
• Calculations (using decimal units where $1\text{ GB} = 10^9\text{ bytes}$):
  $$\text{Daily Ingestion Volume} = 115,200,000 \times 200\text{ bytes} = 23,040,000,000\text{ bytes} = 23.04\text{ GB/day}$$

Raw Storage for 400-Day Retention (with 3 Replicas)

• Single-copy 400-day storage:
  $$400\text{ days} \times 23.04\text{ GB/day} = 9,216\text{ GB} = 9.216\text{ TB}$$
• Storage with 3-way replication:
  $$9,216\text{ GB} \times 3 = 27,648\text{ GB} = 27.648\text{ TB}$$

2. Reconnection Burst Sizing and Absorption

Burst Peak Calculations

• Affected meters: $30% \text{ of } 1,200,000 = 360,000\text{ meters}$.
• Outage duration: 6 hours = $6 \times 4 = 24\text{ intervals}$ accumulated per meter.
• Total buffered readings: $360,000 \times 24 = 8,640,000\text{ readings}$.
• Reconnect window: 10 minutes = $600\text{ seconds}$ (spread uniformly).
• Burst arrival rate from backlog:
  $$\text{Burst Rate} = \frac{8,640,000\text{ readings}}{600\text{ seconds}} = 14,400\text{ readings/second}$$
• Concurrent live steady-state traffic: $1,333.33\text{ readings/second}$.
• Combined peak throughput:
  $$\text{Peak Rate} = 14,400 + 1,333.33 = 15,733.33\text{ readings/second} \approx 15,734\text{ readings/second}$$
• Peak ingress bandwidth:
  $$15,734\text{ readings/s} \times 200\text{ bytes} = 3,146,800\text{ bytes/s} \approx 3.15\text{ MB/s (25.2 Mbps)}$$

Absorption Strategy

1. Edge Termination & Fast ACK: A fleet of horizontally scaled stateless API Gateways fronts the ingestion endpoint. Because meters retry after 30 seconds without an ACK, the gateway verifies batch structure and immediately appends the payload into a distributed commit log (Apache Kafka) before acknowledging HTTP 202/200 within under 50 milliseconds. This eliminates unacknowledged client timeouts and cascading retries.
2. Queue Decoupling: Kafka acts as a durable shock absorber. Partitions are partitioned strictly by meter_id hash (using 64 partitions to comfortably handle up to $50,000\text{ msgs/sec}$). Even though downstream consumer processing slows down during the burst, the ingress path persists the 3.15 MB/s data stream directly to disk without backpressuring the edge gateways.
3. Dashboard Consumer Throttling: The customer dashboard projection workers throttle ingestion consumption, easily meeting the relaxed 60-minute burst SLO while allowing critical billing and alert stream processors dedicated consumption capacity.

3. Exactly-Once Billing Ingestion

Network retries inevitably produce duplicate messages with identical (meter_id, interval_start). The design enforces exactly-once counting in the Billing Storage Loading Layer (Billing Ingestion Pipeline) through a combination of idempotent upserts and relational primary key constraints.

Mechanism

• Natural Key: The primary key for the billing staging ledger is (meter_id, interval_start).
• Deduplication Boundary: A transactional batch loader consumes events from Kafka and writes them to an immutable append-only billing ledger database using deterministic upsert semantics (INSERT ... ON CONFLICT (meter_id, interval_start) DO NOTHING).
• Two-Phase Idempotency:
  1. Before staging, a stateful stream processor (e.g., Apache Flink) uses a RocksDB-backed state table holding a sliding 48-hour deduplication filter (matching the maximum 48-hour meter buffer). Duplicate network retries occurring within this window are dropped immediately.
  2. Any retries that bypass or reload after worker restarts hit the database unique index constraint on (meter_id, interval_start). Billing aggregation queries sum solely over this deduplicated ledger, making over-counting mathematically impossible.

4. Leak Alert Engine with Out-of-Order / Missing Readings

Detection Logic

A continuous leak is defined as 96 consecutive 15-minute intervals (24 continuous hours) of non-zero flow, judged strictly by interval_start.

Stream Processing Pipeline

1. Watermarking and Allowed Lateness: A streaming topology (Apache Flink) maintains a per-meter keyed state tracking contiguous intervals. Because meters can buffer up to 48 hours, the stream job configures an allowed lateness threshold of 48 hours. Late-arriving readings are routed back into the keyed meter state rather than discarded.
2. Contiguity Bitset / Range Tree: Each meter maintains an interval state structure containing the latest evaluated intervals sorted by interval_start. When an out-of-order burst arrives, the intervals fill their corresponding slots in the chronological timeline.
3. Treatment of Missing Intervals:
   • If an interval is missing due to packet loss, clock skew, or exceeding the 48-hour meter buffer overwrite limit, it is treated as an unknown/broken contiguous flow.
   • Because the specification mandates 96 consecutive intervals of confirmed non-zero flow, a missing interval resets the active streak to zero. The engine never assumes flow or zero-flow for missing data.
4. Single Alert Guarantee:
   • The state machine tracks streak_count and a boolean flag alert_emitted_for_current_streak.
   • When streak_count reaches 96, the engine evaluates whether alert_emitted_for_current_streak is false. If false, it emits the leak alert and sets the flag to true.
   • Subsequent consecutive non-zero intervals increment streak_count but suppress new alerts. Only when a zero-reading (or missing interval) breaks the streak is streak_count cleared and alert_emitted_for_current_streak reset to false.

5. Architectural Trade-offs

Trade-off 1: Ingestion Acknowledgement Timing

• Option Chosen: Acknowledge receipts as soon as raw readings are committed to the distributed message queue (Kafka).
• Option Rejected: Acknowledge receipts only after writes are committed to the long-term historical database and billing tables.
• Rationale: A full end-to-end ACK during a 15,734 rps reconnection storm risks hitting database I/O bottlenecks. If responses exceed the 30-second meter timeout, meters retry, compounding ingress load exponentially. Queue-level ACK guarantees persistence in milliseconds and quenches retries immediately.

Trade-off 2: Leak Detection Computation Architecture

• Option Chosen: Stateful event-time streaming processor (Apache Flink with RocksDB state backend).
• Option Rejected: Periodic batch SQL queries querying analytical database tables (e.g., running every 15 minutes).
• Rationale: Batch SQL scans over millions of meters with out-of-order late arrivals require querying massive multi-day sliding windows over billions of rows to reconstruct gaps and streaks. The stateful stream engine evaluates sliding state incrementally on arrival, keeping latency minimal and eliminating repeated table scans.

Trade-off 3: Customer Dashboard Data Storage

• Option Chosen: Pre-aggregated read-optimized time-series/NoSQL store (e.g., Apache Cassandra / ScyllaDB).
• Option Rejected: On-demand queries directly against the normalized billing relational database.
• Rationale: Dashboard queries exhibit high read concurrency and loose consistency requirements (permitting up to 60 minutes delay during bursts). Decoupling dashboard reads from the billing store protects financial transactions from customer query spikes and supports horizontal scaling at 23+ GB per day.

Smart Water-Meter Ingestion Architecture

1. Capacity Estimates and Arithmetic

To build a robust back-end for 1.2 million smart meters, we begin with deterministic capacity and storage sizing.

• Steady-state readings per second: Each meter transmits one reading every 15 minutes (900 seconds). For 1,200,000 meters, the average ingestion rate is $1,200,000 / 900 = 1,333.33$ readings per second.
• Raw data volume per day: At 1,333.33 readings per second, the total number of daily readings is $1,333.33 \times 86,400 = 115,200,000$ readings/day. With each reading sized at 200 bytes on the wire, the raw daily data volume is $115,200,000 \times 200 \text{ bytes} = 23,040,000,000 \text{ bytes/day}$ (or 23.04 GB/day).
• Raw storage for the full retention period including replication: The data must be retained for 400 days with 3 replicas. The total storage calculation is $23.04 \text{ GB/day} \times 400 \text{ days} \times 3 \text{ replicas} = 27,648 \text{ GB}$ (or 27.64 TB).

2. Ingestion Path and Reconnection Burst Sizing

When a major storm occurs, 30% of the meters ($360,000$ meters) lose connectivity for 6 hours. Over 6 hours, each disconnected meter buffers $6 \times 4 = 24$ readings. When connectivity returns, all 360,000 meters upload their 24 buffered readings evenly spread across a 10-minute (600-second) window.

• Buffered upload rate: The rate of historical readings during the burst is $(360,000 \text{ meters} \times 24 \text{ readings}) / 600 \text{ seconds} = 8,640,000 / 600 = 14,400$ readings per second.
• Concurrent live rate: Simultaneously, all 1,200,000 meters continue sending their normal live readings at $1,333.33$ readings per second.
• Peak ingestion rate: The total peak rate is $14,400 + 1,333.33 = 15,733.33$ readings per second (roughly 15.7k rps).

To absorb this surge without dropping any readings, the ingestion layer uses a horizontally scalable, distributed message broker (such as Apache Kafka) partitioned into 64 partitions. An API Gateway fleet fronted by a load balancer terminates TLS, performs fast schema validation, and writes directly to the message broker. Because Kafka partitions decouple ingestion from downstream processing, the surge is safely buffered in disk-backed commit logs. The consumer workers scale dynamically based on consumer lag, handling the temporary surge without data loss.

3. Idempotency and Exactly-Once Billing

Meters retry uploads if they do not receive an acknowledgement within 30 seconds, which means duplicates are guaranteed to arrive. To guarantee that billing counts each (meter_id, interval_start) exactly once, we enforce idempotency within the Billing Stream Processor.

Each incoming reading payload contains a compound natural key: meter_id and interval_start. The billing stream processor consumes readings from the message broker and uses an atomic upsert operation against a distributed columnar data store or a deduplication state store (such as RocksDB integrated into Apache Flink).

When a reading arrives, the processor checks if a record for (meter_id, interval_start) has already been processed and billed. If the key exists, the duplicate payload is safely dropped or marked as a duplicate, ensuring zero double-counting. If the key does not exist, the billing table is updated and an acknowledgement is sent back to the ingestion broker.

4. Late-Arriving Data and Leak Alerts

Leak detection requires identifying when a meter shows non-zero flow in every one of 96 consecutive 15-minute intervals (covering a full 24-hour period), evaluated strictly by interval_start rather than arrival time. To ensure leak alerts remain correct when buffered readings arrive late and out of order, we employ Event-Time Stream Processing with Watermarking.

• Handling late data: The stream processor maintains state windows keyed by meter_id. Because meters can buffer data for up to 48 hours, the stream processing windows are configured with a 48-hour allowed lateness threshold. This ensures that when delayed or reconnected batch uploads arrive, they are retroactively slotted into their correct chronological 15-minute bins based on their interval_start timestamps.
• Missing intervals: If a 15-minute interval is entirely missing from the stream and its 48-hour lateness window closes without receiving data, that interval is explicitly treated as zero flow (or missing data that breaks a contiguous streak). A missing reading shatters the streak of non-zero flow, resetting the 96-interval consecutive counter for that meter.
• Firing alerts: Once a meter accumulates 96 contiguous non-zero intervals, the system triggers exactly one alert per streak. Subsequent non-zero readings in the same continuous run do not generate duplicate alerts due to stateful deduplication flags stored alongside the active streak counter.

5. Architectural Trade-Offs

To finalize our system design, we evaluate three critical architectural choices:

1. Ingestion Protocol: Chosen Option (HTTP/REST with JSON over TLS) vs. Rejected Option (MQTT / Custom TCP Sockets). While custom TCP sockets or MQTT provide lighter payload overhead, HTTP/REST with JSON is universally supported by cellular and IoT gateway modules, simplifies debugging, integrates natively with existing API management firewalls, and easily accommodates the 200-byte wire size requirement without specialized firmware protocol stacks.
2. Storage Paradigm: Chosen Option (Object Storage Parquet Files for Cold Data + Distributed Log for Hot) vs. Rejected Option (Traditional Distributed Relational SQL Cluster). Maintaining 400 days of historical time-series data plus 3 replicas in a traditional relational database cluster introduces prohibitive infrastructure costs and complex horizontal sharding limits. By dumping compacted raw readings into columnar Parquet files on object storage (e.g., AWS S3) queried via serverless query engines, we achieve cost-effective long-term retention while keeping the active operational path fast.
3. Dashboard Consistency: Chosen Option (Eventual Consistency via Read-Model Projections) vs. Rejected Option (Synchronous Read-Through Database Queries). To guarantee that dashboard queries remain responsive during massive reconnection bursts (15.7k rps), we chose asynchronous read-model projections that update customer dashboards within 5 minutes under normal loads (and relax to 60 minutes during storms). We rejected synchronous read-through queries because locking or querying the primary transactional store under peak ingestion bursts would cause cascading timeouts, database connection pool exhaustion, and potential ingestion failure.