Energy co-op automation: reconciling 7,200 meters by 04:00

It is Sunday, 03:47. The board of an Arnhem energiecoöperatie — thirty-five people who own a solar field on a former agricultural plot near the IJssel — is asleep. The treasurer is asleep. The accountant in Zutphen who handles their books is asleep. Nobody is watching except a Postgres database, a Bash cron, and a Python process that has thirteen minutes left to decide whose share of last week's teruglever-saldo goes onto whose jaarafrekening next March.

At 04:00, an EDSN batch lands. EDSN is the national clearinghouse for Dutch energy data, the system every supplier and grid operator exchanges metering through. Their batch is not negotiable. It does not retry on your schedule. If our reconciliation has not produced a clean saldo by 03:59, the wrong number gets posted, and the bestuur gets to spend their March explaining it to the ALV.

This post is the playbook for that thirteen-minute window. Specifically: how we took 7,200 weekly slimme-meter readings, a 13-year-old Stedin allocatiebestand, and a homegrown PostgreSQL teruglever-ledger that grew organically since 2017, and made them agree often enough that the four-eyes review queue only sees the genuinely weird stuff.

Three sources, three truths

A Dutch energy cooperative that produces its own solar power has three datasets that are supposed to mean the same thing.

The first is smart-meter telemetry itself. Each connection behind the co-op's allocation reports a quarter-hourly reading. The co-op only reconciles weekly rollups, so what we actually compare is 7,200 weekly aggregates across about a hundred connection IDs — the field's main meters, the substation meters, and the members' own rooftops. These come from the netbeheerder's data hub.

The second is the Stedin allocatiebestand. Stedin is the regional grid operator for Arnhem. They publish a weekly file that says: of the energy this co-op's field fed back into the net, here is how much we are crediting to which connection. The file is a fixed-width text export whose schema was designed in 2012 and has never been versioned. Its column positions are documented in a single PDF that we keep checked into the repo, scanned from a printed binder the previous bookkeeper handed over.

The third is the co-op's own teruglever-ledger. This is a Postgres table the previous bookkeeper built in 2017 to track what each member is owed for the energy their share of the field produced. Members pay an entry fee for a productie-aandeel that determines their slice of total opbrengst, and shares change hands a few times a year when somebody moves out of the regio.

These three should agree. They do not. Three reasons. Meters can be replaced mid-week, in which case the meter ID changes and Stedin allocates against the new one, but telemetry for the first half of the week is still under the old. The Stedin file rounds to whole kWh per connection; the ledger tracks four decimals because that is what the bookkeeper wrote in 2017. And members sometimes transfer a productie-aandeel halfway through a week — the ledger handles that with two rows, but Stedin allocates the whole week to whoever was on record at file-cut.

The reconciliation agent's job is to make those disagreements visible and decide which ones are routine drift versus which ones need a human.

Parsing the 13-year-old allocation file

The Stedin file is the part nobody wants to touch. It is a UTF-8 (formerly ISO-8859-1, switched silently in 2019) fixed-width text file. Records start with a two-character segment code. Lines that begin with AL are allocation lines; MT are mutation lines; HD is the header; ZZ is the trailer. There are six other segment codes the spec mentions; we have never seen them in this co-op's data, but we still write a parser stub for each that raises if encountered.

There is no formal grammar. There is a PDF.

We use a tiny adapter that turns the PDF's column-position table into a Python dataclass. The relevant detail is that we do not try to be clever. We do not write a generic fixed-width parser. We write one function per segment, and we test each against every historical file we have in the repo as a fixture.

from dataclasses import dataclass
from decimal import Decimal

@dataclass(frozen=True, slots=True)
class AllocationLine:
    ean18: str          # cols 3-20  — the connection's EAN code
    weekstart: str      # cols 21-30 — ISO date YYYY-MM-DD
    kwh_in: Decimal     # cols 31-40 — afname, whole kWh
    kwh_out: Decimal    # cols 41-50 — teruglever, whole kWh
    tariefcode: str     # cols 51-54
    versie: int         # cols 55-57

def parse_al(line: str) -> AllocationLine:
    assert line[:2] == "AL", f"not an AL line: {line[:2]!r}"
    return AllocationLine(
        ean18=line[2:20].strip(),
        weekstart=f"{line[20:24]}-{line[24:26]}-{line[26:28]}",
        kwh_in=Decimal(line[30:40].strip() or "0"),
        kwh_out=Decimal(line[40:50].strip() or "0"),
        tariefcode=line[50:54].strip(),
        versie=int(line[54:57].strip() or "0"),
    )

Three things to notice. We use Decimal, never float. We assert the segment code rather than dispatching inside the parser — the dispatch happens one level up so the type checker sees one return type per function. And we treat empty strings as zero because the older files, the ones from 2013 to 2016, sometimes elided trailing columns and the spec says we should.

One trap that cost us a Sunday morning early on: Stedin's file claims a single encoding in its header byte. In 2019 a batch came through with mixed encodings inside the same file — header in UTF-8, body in Latin-1, trailer in UTF-8 again. The parser now detects encoding per line, not per file. Vendor declarations about encoding are aspirational.

The teruglever-ledger and the floating-point sins of 2017

The co-op's own ledger is a Postgres table that, after our migration, looks like this:

CREATE TABLE teruglever_ledger (
  id              BIGSERIAL PRIMARY KEY,
  member_id       BIGINT NOT NULL REFERENCES members(id),
  ean18           CHAR(18) NOT NULL,
  weekstart       DATE NOT NULL,
  share_fraction  NUMERIC(8,7) NOT NULL,
  kwh_out         NUMERIC(12,4) NOT NULL,
  saldo_eur       NUMERIC(10,4) NOT NULL,
  source_hash     CHAR(64) NOT NULL,
  corrects_id     BIGINT REFERENCES teruglever_ledger(id),
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  posted          BOOLEAN NOT NULL DEFAULT FALSE,
  UNIQUE (member_id, ean18, weekstart, source_hash)
);

Note share_fraction. This is the bit that bit us. When the 2017 ledger was built, it used FLOAT8. The migration to NUMERIC(8,7) was the first thing we did, and it produced 614 rows whose value did not survive the cast cleanly — share fractions like 0.0666666666666 that needed to be reconciled with the original membership contracts before we could write them back.

We do not edit historical ledger rows. The migration created adjustment rows with a counter-sign and the original ID in a corrects_id column. Auditability matters more than tidiness. The bestuur of a coöperatie can be asked at any ALV why a member's saldo changed; "we ran a migration in 2026" is not the answer they want, but "here is the adjustment row and the contract it traces to" is.

The four-eyes queue at €240

Reconciliation, in plain prose: for each (ean18, weekstart) pair, compute the delta between the Stedin number and the ledger number, multiply by the week's tarief, bucket the result.

Most deltas are below one kWh. Stedin rounds, the ledger does not, so there is always a small floor of disagreement. We treat anything under €5 absolute as drift and post it to a single weekly afrondingsverschillen account.

Anything between €5 and €240 gets posted to the member's saldo with a flag that says "auto-reconciled, check on jaarafrekening". The accountant in Zutphen sees these in a Monday morning report.

Anything where the absolute delta exceeds €240, in either direction, parks. It goes into a queue that the treasurer and one board member must both approve before the EDSN batch is allowed to consume it. We chose €240 because it is roughly one month of average solar revenue for a single share at this co-op, and because the bestuur picked that number after we showed them a histogram of historical reconciliation deltas from 2018 onward. The fat tail is real: most reviewable events trace back to a meter swap that had not been propagated, or a member who paid in for a second share and got double-allocated for one week.

CREATE TABLE reconciliation_review (
  id              BIGSERIAL PRIMARY KEY,
  ean18           CHAR(18) NOT NULL,
  weekstart       DATE NOT NULL,
  delta_eur       NUMERIC(10,4) NOT NULL,
  reason          TEXT NOT NULL,
  approver_1_id   BIGINT REFERENCES users(id),
  approver_1_at   TIMESTAMPTZ,
  approver_2_id   BIGINT REFERENCES users(id),
  approver_2_at   TIMESTAMPTZ,
  released_at     TIMESTAMPTZ,
  UNIQUE (ean18, weekstart),
  CHECK (
    approver_1_id IS NULL
    OR approver_2_id IS NULL
    OR approver_1_id <> approver_2_id
  )
);

Two distinct approver IDs. A CHECK constraint enforces it. The release only happens when both approvals are present, the approvers are not the same person, and the release timestamp is before 03:59. The EDSN batch step reads only released rows. Everything else either rolls forward to the next week or stays parked until somebody in the bestuur looks at it.

Idempotency, replay, and the Monday-morning audit

The agent runs every Sunday at 03:30 and again as a dry-run every Monday at 09:00, so the bookkeeper can see what would have happened if last week were re-run today. Both runs read the same input files. Both produce the same output. Neither writes to the ledger except through one function:

def post_reconciliation(
    *,
    ean18: str,
    weekstart: date,
    delta_eur: Decimal,
    source: Literal["auto", "review"],
    idempotency_key: str,
) -> None:
    ...

The idempotency key is a SHA-256 of ean18 || weekstart || source_file_hash || policy_version. The UNIQUE constraint on the ledger enforces it. Run the agent twice on the same input and you get the same result. Run it after the input changed — Stedin republishes a corrected weekly file two or three times a year — and you get adjustment rows, not silent overwrites.

Every decision lands in a separate reconciliation_audit table: which inputs were considered, what the computed delta was, which threshold band it fell into, who approved it. The Monday-morning report is a SQL view over that table. The bookkeeper in Zutphen reads it through a read-only Metabase login. We did not give her that login on day one; we gave her PDFs, and she eventually asked us to stop. She was right.

What we would do differently

Three things, in order of regret.

We started by trying to write one big parser for the Stedin file. We rewrote it twice. The version that finally worked is a flat collection of one-function-per-segment-code parsers with a dispatch table. We should have started there. Generic-feeling code is a smell in adapter layers where the schema is not yours and the cost of being wrong is a human reading a misposted invoice.

We let the four-eyes threshold be a hardcoded constant for the first three months. The bestuur wanted to tune it after they saw real Monday reports. Moving it to a row in a policy table was a half-day of work that should have been there from day one — any threshold whose value the business will negotiate belongs in data, not code, and gets its own policy_version column on every dependent row.

We did not initially expose the audit view to the bookkeeper. She was reading PDFs we generated. She is a competent reader of SQL; we should have given her the read-only Metabase connection from the start. Tools the operator can drive themselves beat reports you have to regenerate.

The five-minute audit you can do today

When we built this reconciliation agent for the Arnhem co-op, the part we underestimated was the 13-year-old allocatiebestand — a textbook example of why process automation on older Dutch infrastructure is half about reading the file and half about reading the room. The first parser we shipped assumed the spec was reliable. The second assumed it was not. The second one is still in production.

If you are running a reconciliation against a legacy fixed-width file from any Dutch netbeheerder, here is the audit: open your last four input files in a hex editor and check the first byte of each line. If they disagree across files, you are decoding inconsistently and you do not know it yet. That is your Sunday morning at 03:47.