A backend design for ingesting smart meter interval readings: idempotent batch ingest from the head-end system, actual versus estimated reads, gap detection across daylight saving changes, partitioned Postgres tables and the daily close for billing.

A smart meter backend takes interval readings from the head-end system, stores one row per meter per interval, and turns them into complete days that billing and settlement can trust. The hard parts are resent batches, missing intervals and days that are not 48 half-hours long.
In Great Britain, the MHHS programme is moving every meter to settlement on half-hourly data, so this pipeline now runs for every customer.
The meters themselves usually speak DLMS/COSEM to the head-end system, and the backend sees the output as files or API batches of interval values. In the US, customers download the same data in the Green Button format, which is built on interval readings with a quality flag per value.
Store interval_start in UTC with the meter ID as the primary key, the value in watt-hours as an integer, and a quality column. Half-hourly reads for a million meters come to 48 million rows a day, so the table is partitioned by range on interval_start, one partition per month. Queries for a meter's month touch one partition, and dropping old data is a DROP TABLE instead of a slow DELETE.
The head-end system resends batches after timeouts, and the same interval arrives again with the same or a corrected value. Upsert on (meter_id, interval_start) with ON CONFLICT, and let the quality decide who wins: an actual read replaces an estimate, a later actual replaces an earlier one, and an estimate never overwrites an actual.
A local day in London has 46 half-hours on the last Sunday in March and 50 on the last Sunday in October. Code that checks for 48 reads per day flags a gap every spring and misses one every autumn. Generate the expected intervals from the meter's time zone instead, which Postgres does with generate_series between the local midnight boundaries converted to UTC.
Encore is a backend framework with built-in infrastructure, so the readings database, the topic that triggers gap checks and the nightly estimation job are declared in the TypeScript that uses them. Deploying provisions Postgres, Pub/Sub and the cron schedule in your own AWS or GCP account, and cron jobs run on Encore's managed scheduler with no extra cloud resources.

Run in your terminal to get started locally.
The ingest endpoint, gap check and estimation job:
// readings/readings.ts
import { api, APIError } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";
import { Topic, Subscription } from "encore.dev/pubsub";
import { CronJob } from "encore.dev/cron";
const db = new SQLDatabase("readings", { migrations: "./migrations" });
interface Reading {
meterId: string;
intervalStart: string; // ISO 8601, UTC
wh: number;
quality: "actual" | "estimated";
}
interface MeterDay {
meterId: string;
day: string; // local date, YYYY-MM-DD
}
const meterDays = new Topic<MeterDay>("meter-days-ingested", {
deliveryGuarantee: "at-least-once",
});
// Called by the service that unpacks head-end batches; resending a batch is safe.
export const ingest = api(
{ expose: false, method: "POST", path: "/readings/ingest" },
async ({ readings }: { readings: Reading[] }): Promise<{ upserted: number }> => {
if (readings.length > 10_000) throw APIError.invalidArgument("batch too large");
const touched = new Map<string, MeterDay>();
let upserted = 0;
for (const r of readings) {
const res = await db.queryRow<{ day: string }>`
INSERT INTO readings (meter_id, interval_start, wh, quality)
VALUES (${r.meterId}, ${r.intervalStart}, ${r.wh}, ${r.quality})
ON CONFLICT (meter_id, interval_start) DO UPDATE
SET wh = EXCLUDED.wh, quality = EXCLUDED.quality, updated_at = now()
-- An estimate never overwrites an actual read.
WHERE readings.quality = 'estimated' OR EXCLUDED.quality = 'actual'
RETURNING to_char((interval_start AT TIME ZONE 'Europe/London')::date, 'YYYY-MM-DD') AS day`;
if (!res) continue; // skipped: an estimate arrived for an actual read
upserted++;
touched.set(`${r.meterId}/${res.day}`, { meterId: r.meterId, day: res.day });
}
for (const md of touched.values()) await meterDays.publish(md);
return { upserted };
},
);
// Counts missing intervals for one local day, so DST days expect 46 or 50.
new Subscription(meterDays, "check-gaps", {
handler: async ({ meterId, day }) => {
const row = await db.queryRow<{ missing: number }>`
SELECT count(*)::int AS missing
FROM generate_series(
(${day}::date)::timestamp AT TIME ZONE 'Europe/London',
((${day}::date + 1)::timestamp AT TIME ZONE 'Europe/London') - interval '30 minutes',
interval '30 minutes') AS t(ts)
WHERE NOT EXISTS (
SELECT 1 FROM readings WHERE meter_id = ${meterId} AND interval_start = t.ts)`;
await db.exec`
INSERT INTO meter_days (meter_id, day, missing) VALUES (${meterId}, ${day}, ${row!.missing})
ON CONFLICT (meter_id, day) DO UPDATE SET missing = EXCLUDED.missing`;
},
});
// Fills yesterday's gaps from the same interval a week earlier, flagged as estimates.
export const estimateMissing = api(
{ expose: false, method: "POST", path: "/readings/estimate-missing" },
async (): Promise<{ estimated: number }> => {
const res = await db.queryRow<{ n: number }>`
WITH gaps AS (
SELECT md.meter_id, t.ts
FROM meter_days md,
generate_series(
md.day::timestamp AT TIME ZONE 'Europe/London',
(md.day + 1)::timestamp AT TIME ZONE 'Europe/London' - interval '30 minutes',
interval '30 minutes') AS t(ts)
WHERE md.day = (now() AT TIME ZONE 'Europe/London')::date - 1 AND md.missing > 0
AND NOT EXISTS (
SELECT 1 FROM readings r WHERE r.meter_id = md.meter_id AND r.interval_start = t.ts)
), ins AS (
INSERT INTO readings (meter_id, interval_start, wh, quality)
SELECT g.meter_id, g.ts, COALESCE(prev.wh, 0), 'estimated'
FROM gaps g
LEFT JOIN readings prev
ON prev.meter_id = g.meter_id AND prev.interval_start = g.ts - interval '7 days'
ON CONFLICT (meter_id, interval_start) DO NOTHING
RETURNING 1
)
SELECT count(*)::int AS n FROM ins`;
return { estimated: res!.n };
},
);
// 04:00 UTC, after the head-end system's overnight batch has landed.
new CronJob("estimate-missing", {
title: "Estimate missing intervals for yesterday",
schedule: "0 4 * * *",
endpoint: estimateMissing,
});
-- readings/migrations/1_init.up.sql
CREATE TABLE readings (
meter_id TEXT NOT NULL,
interval_start TIMESTAMPTZ NOT NULL,
wh INTEGER NOT NULL CHECK (wh >= 0),
quality TEXT NOT NULL CHECK (quality IN ('actual', 'estimated')),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (meter_id, interval_start)
) PARTITION BY RANGE (interval_start);
CREATE TABLE readings_2026_11 PARTITION OF readings
FOR VALUES FROM ('2026-11-01 00:00+00') TO ('2026-12-01 00:00+00');
CREATE TABLE readings_2026_12 PARTITION OF readings
FOR VALUES FROM ('2026-12-01 00:00+00') TO ('2027-01-01 00:00+00');
CREATE TABLE meter_days (
meter_id TEXT NOT NULL,
day DATE NOT NULL,
missing INTEGER NOT NULL,
closed_at TIMESTAMPTZ,
PRIMARY KEY (meter_id, day)
);
The ingest endpoint is private, so only other services in the app, such as the one that unpacks head-end files from a bucket, can call it. The upsert's WHERE clause holds the quality rule, so an estimate sent after an actual read is skipped, and the generated series should produce 50 intervals for October 25, 2026 and 46 for March 29. The gap check is idempotent, so a redelivered Pub/Sub message recomputes the same count. Locally, encore run starts Postgres and the topic for this code, and the estimation endpoint can be called by hand, since cron schedules only run in deployed environments.
When a coding agent adds the next step, such as closing days for billing, it adds the job and its table in the same service and tests the endpoint locally, and the deploy provisions what it declared without Terraform changes.
Best fit: Energy retailers and meter data teams building readings ingest who want Postgres, queues and schedules in their own cloud account with the pipeline logic in one codebase.
It stores one row per meter per interval, which with half-hourly reads is 48 rows per meter per day, so a million meters produce 48 million rows a day and about 17.5 billion a year, which is why the table is usually partitioned by month.
Key each reading on meter ID and interval start and upsert, so a batch the head-end system resends overwrites rather than duplicates. Let an actual read replace an estimated one, but never let an estimate overwrite an actual.
A local day has 46 half-hour intervals when clocks go forward and 50 when they go back. Store interval starts in UTC and generate each local day's expected intervals from the meter's time zone, rather than assuming 48.
VEE stands for validation, estimation and editing: readings are checked for gaps and implausible values, missing intervals are filled with estimates flagged as such, and later actual reads or manual edits replace those estimates before billing.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.