City-scale smart water metering with separated ingest and reads

The problem

Operators need pipeline-level visibility from household water meters, including freezing risk and reverse flow, without dashboards crushing ingest during peak reporting.

City-scale device count with burst ingest by time of day.

Rules such as freezing risk need a window of samples, not one reading.

Per-household flow rate must survive meter reset and bad timestamps.

Multi-tenant operators; each organisation sees only its scope.

System architecture

flowchart LR
  subgraph edge ["Meters"]
    edge_0(["Household water meters"])
  end
  subgraph ingest ["Ingest"]
    ingest_0["Thin validation and timestamping"]
    ingest_1[["Ingest queue"]]
    ingest_2["Batch insert writers"]
  end
  subgraph read ["Read path"]
    read_0[("Operational store")]
    read_1["Pre-aggregated rollups"]
    read_2["Rule engine for freezing and reverse flow"]
    read_3["Operator dashboards"]
  end
  edge_0 --> ingest_0
  ingest_0 --> ingest_1
  ingest_1 --> ingest_2
  ingest_2 --> read_0
  read_0 --> read_1
  read_1 --> read_2
  read_2 --> read_3

Key architecture decisions

Window rules for freezing risk and reverse flow

Evaluate freezing risk and reverse flow over a time window with ordered samples, not on a single packet.

One noisy sample would page operators constantly; the business question is sustained abnormal behaviour.

Rejected Threshold on every raw sample — rejected after false-positive volume in early pilots.

Trade-off Windows must tolerate late-arriving samples without closing alerts too early.

Flow rate as delta volume over delta time

Compute per-household flow from volume deltas with handling for reset, duplicate timestamps, and clock steps backward.

Operational monitoring breaks when resets are treated as negative usage spikes.

Rejected Storing only instantaneous rate from the device without reconciliation — rejected when devices retried duplicate rows.

Trade-off More ingestion logic; cleaner operator trust in charts.

Batch insert and denormalized dashboard reads

Consumers batch insert; dashboards query rollups, not live ingest tables.

Peak-hour telemetry must not compete with ad-hoc GROUP BY on raw history.

Rejected Single PostgreSQL table for both ingest and interactive analytics — rejected under load tests.

Trade-off Maintaining rollup freshness; seconds of chart lag.

What I own, wrote, and delegate

Own

Backend collecting meter telemetry and feeding centralized monitoring
Separation of ingest workload from dashboard query workload

Wrote

Analytics and alerting for freezing-risk and reverse-flow conditions
Per-household flow-rate calculation for network-wide monitoring

Delegate

Billing and customer-facing tariff logic, which sat outside this monitoring backend

Scale

Overseas city-scale deployment
Operational monitoring, so operators can act before pipe damage or a service outage

Boundaries

Household meters across one city. Device count and daily volume stay with the operator.
Meter firmware stays with the hardware vendor. This backend starts at the telemetry.

What we watch in operation

Ingest rate versus consumer lagAlert counts for freezing and reverse-flow rulesLate or out-of-order sample ratio per hour

Rules the design never breaks

Dashboard queries do not run on the ingest connection pool.
Tenant filters apply on every read API.
Meter anomalies are upserted by device and timestamp, not double-counted.

Broader application

Utility-scale telemetry with diurnal bursts.
Networks where reverse flow and freeze risk are early-warning signals.
Platforms moving from ingest-everything to read-model separation.