Energiecoöperatie automatiseren: 7.200 meterstanden vóór 04:00

Het is zondag, 03:47. Het bestuur van een Arnhemse energiecoöperatie — vijfendertig mensen die samen een zonneveld bezitten op een voormalig agrarisch perceel bij de IJssel — slaapt. De penningmeester slaapt. De accountant in Zutphen die hun boekhouding doet, slaapt. Niemand kijkt mee, behalve een Postgres-database, een Bash-cron en een Python-proces dat nog dertien minuten heeft om te bepalen wiens deel van het teruglever-saldo van afgelopen week op wiens jaarafrekening van komende maart komt te staan.

Om 04:00 valt er een EDSN-batch binnen. EDSN is het landelijke clearinghouse voor Nederlandse energiedata, het systeem waar elke leverancier en netbeheerder hun metering doorheen uitwisselt. Hun batch is niet onderhandelbaar. Hij herstart niet op jouw schema. Heeft onze reconciliatie om 03:59 geen schoon saldo opgeleverd, dan gaat het verkeerde getal de boeken in en mag het bestuur in maart op de ALV uitleggen hoe dat zit.

Deze post is het draaiboek voor dat venster van dertien minuten. Concreet: hoe we 7.200 wekelijkse slimme-meterstanden, een 13 jaar oud Stedin-allocatiebestand en een zelfgebouwde PostgreSQL-teruglever-ledger die sinds 2017 organisch is gegroeid, zo op één lijn kregen dat de vierogen-reviewwachtrij alleen het écht vreemde spul te zien krijgt.

Drie bronnen, drie waarheden

Een Nederlandse energiecoöperatie die haar eigen zonnestroom opwekt, heeft drie datasets die hetzelfde zouden moeten betekenen.

De eerste is de telemetrie van de slimme meters zelf. Elke aansluiting achter de allocatie van de coöperatie levert een kwartierwaarde aan. De coöperatie reconciliëert alleen wekelijkse totalen, dus wat we daadwerkelijk vergelijken zijn 7.200 weekaggregaten verdeeld over ongeveer honderd aansluit-ID's — de hoofdmeters van het veld, de meters van het onderstation en de daken van de leden zelf. Die komen uit de datahub van de netbeheerder.

De tweede is het Stedin-allocatiebestand. Stedin is de regionale netbeheerder voor Arnhem. Zij publiceren wekelijks een bestand dat zegt: van de energie die het veld van deze coöperatie heeft teruggeleverd, dit is hoeveel we aan welke aansluiting toerekenen. Het bestand is een fixed-width tekst-export waarvan het schema in 2012 is ontworpen en nooit is geversioneerd. De kolomposities zijn gedocumenteerd in één PDF die we in de repo hebben gezet, gescand uit een ringband die de vorige boekhouder ons overhandigde.

De derde is de eigen teruglever-ledger van de coöperatie. Dit is een Postgres-tabel die de vorige boekhouder in 2017 heeft opgezet om bij te houden wat elk lid tegoed heeft voor de energie die zijn aandeel in het veld heeft opgewekt. Leden betalen entreegeld voor een productie-aandeel dat hun deel van de totale opbrengst bepaalt, en aandelen wisselen een paar keer per jaar van eigenaar als iemand uit de regio verhuist.

Deze drie zouden moeten kloppen. Dat doen ze niet. Drie redenen. Meters kunnen midden in de week worden vervangen; het meter-ID verandert dan en Stedin alloceert op de nieuwe, terwijl de telemetrie voor de eerste helft van de week nog op de oude staat. Het Stedin-bestand rondt af op hele kWh per aansluiting; de ledger houdt vier decimalen aan, omdat de boekhouder dat in 2017 zo heeft ingericht. En leden dragen soms halverwege de week een productie-aandeel over — de ledger vangt dat op met twee rijen, maar Stedin alloceert de hele week aan degene die bij de bestandscut geregistreerd stond.

De taak van de reconciliatie-agent is die verschillen zichtbaar te maken en te bepalen welke routine-drift zijn en welke een mens nodig hebben.

Het 13 jaar oude allocatiebestand parsen

Het Stedin-bestand is het stuk waar niemand aan wil komen. Het is een UTF-8 (voorheen ISO-8859-1, in 2019 stilletjes omgezet) fixed-width tekstbestand. Records beginnen met een tweeletterige segmentcode. Regels die met AL beginnen zijn allocatieregels; MT zijn mutatieregels; HD is de header; ZZ is de trailer. De specificatie noemt nog zes andere segmentcodes; in de data van deze coöperatie hebben we ze nooit gezien, maar we schrijven voor elk toch een parser-stub die een fout opwerpt zodra hij wordt aangeroepen.

Er is geen formele grammatica. Er is een PDF.

We gebruiken een kleine adapter die de kolompositie-tabel uit de PDF omzet naar een Python-dataclass. Het relevante detail is dat we niet proberen slim te zijn. We schrijven geen generieke fixed-width-parser. We schrijven één functie per segment en we testen elke functie als fixture tegen elk historisch bestand dat we in de repo hebben staan.

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"),
    )

Drie dingen om op te merken. We gebruiken Decimal, nooit float. We asserten de segmentcode in plaats van binnen de parser te dispatchen — het dispatchen gebeurt één niveau hoger, zodat de type checker per functie één return-type ziet. En we behandelen lege strings als nul, omdat de oudere bestanden, die uit 2013 tot 2016, soms achterliggende kolommen weglieten en de spec zegt dat we dat zo moeten doen.

Eén valkuil die ons in het begin een zondagochtend heeft gekost: het Stedin-bestand claimt in zijn header-byte één enkele encoding. In 2019 kwam er een batch binnen met gemengde encodings in hetzelfde bestand — header in UTF-8, body in Latin-1, trailer weer in UTF-8. De parser detecteert nu encoding per regel, niet per bestand. Encoding-verklaringen van leveranciers zijn aspiratief.

De teruglever-ledger en de floating-point-zonden van 2017

De eigen ledger van de coöperatie is een Postgres-tabel die er na onze migratie zo uitziet:

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)
);

Let op share_fraction. Dat is het stuk dat ons opbrak. Toen de ledger in 2017 werd opgezet, gebruikte hij FLOAT8. De migratie naar NUMERIC(8,7) was het eerste wat we hebben gedaan, en die leverde 614 rijen op waarvan de waarde de cast niet schoon overleefde — aandelen-fracties zoals 0.0666666666666 die met de oorspronkelijke lidmaatschapscontracten moesten worden afgestemd voordat we ze konden terugschrijven.

We bewerken geen historische ledger-rijen. De migratie heeft correctie-rijen aangemaakt met een tegengesteld teken en het oorspronkelijke ID in een corrects_id-kolom. Auditbaarheid telt zwaarder dan netheid. Het bestuur van een coöperatie kan op elke ALV worden gevraagd waarom het saldo van een lid is veranderd; "we hebben in 2026 een migratie gedraaid" is niet het antwoord dat ze willen, maar "hier is de correctie-rij en het contract waar hij naar verwijst" wel.

De vierogen-wachtrij vanaf €240

Reconciliatie, in gewone taal: bereken voor elke (ean18, weekstart)-combinatie het verschil tussen het Stedin-getal en het ledger-getal, vermenigvuldig met het tarief van die week en zet het resultaat in een bucket.

De meeste delta's zijn kleiner dan één kWh. Stedin rondt af, de ledger niet, dus er is altijd een kleine ondergrens aan verschillen. Alles onder de €5 in absolute waarde behandelen we als drift en boeken we weg op één wekelijkse afrondingsverschillen-rekening.

Alles tussen de €5 en €240 wordt op het saldo van het lid geboekt met een vlag die zegt "automatisch gereconciliëerd, check op jaarafrekening". De accountant in Zutphen ziet die in een maandagochtend-rapport.

Alles waar het absolute verschil de €240 overschrijdt, in beide richtingen, gaat in de wacht. Het komt in een wachtrij die de penningmeester én één bestuurslid samen moeten goedkeuren voordat de EDSN-batch hem mag verwerken. We kozen €240 omdat dat ongeveer één maand gemiddelde zonneopbrengst per aandeel is bij deze coöperatie, en omdat het bestuur dat getal heeft gekozen nadat we ze een histogram lieten zien van historische reconciliatie-delta's vanaf 2018. De staart is echt: bijna alle te beoordelen events zijn terug te voeren op een meterwissel die niet was doorgegeven, of een lid dat een tweede aandeel had ingelegd en daarvoor één week dubbel was gealloceerd.

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
  )
);

Twee verschillende goedkeurder-ID's. Een CHECK-constraint dwingt het af. De vrijgave gebeurt pas wanneer beide goedkeuringen aanwezig zijn, de goedkeurders niet dezelfde persoon zijn en de vrijgave-timestamp vóór 03:59 ligt. De EDSN-batchstap leest alleen vrijgegeven rijen. Al het andere rolt door naar de volgende week of blijft in de wacht totdat iemand uit het bestuur ernaar kijkt.

Idempotentie, replay en de maandagochtend-audit

De agent draait elke zondag om 03:30 en daarnaast als dry-run elke maandag om 09:00, zodat de boekhouder kan zien wat er zou zijn gebeurd als afgelopen week vandaag opnieuw zou draaien. Beide runs lezen dezelfde invoerbestanden. Beide produceren dezelfde uitvoer. Geen van beide schrijft naar de ledger, behalve via één functie:

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

De idempotency key is een SHA-256 van ean18 || weekstart || source_file_hash || policy_version. De UNIQUE-constraint op de ledger dwingt dat af. Draai de agent tweemaal op dezelfde input en je krijgt hetzelfde resultaat. Draai hem nadat de input is gewijzigd — Stedin publiceert twee of drie keer per jaar een gecorrigeerd weekbestand — en je krijgt correctie-rijen, geen stille overschrijvingen.

Elke beslissing landt in een aparte reconciliation_audit-tabel: welke inputs zijn meegenomen, wat de berekende delta was, in welke drempel-band hij viel en wie hem heeft goedgekeurd. Het maandagochtend-rapport is een SQL-view op die tabel. De boekhouder in Zutphen leest hem via een read-only Metabase-login. Die login hebben we haar niet op dag één gegeven; we gaven haar PDF's, en uiteindelijk vroeg ze ons om daarmee te stoppen. Ze had gelijk.

Wat we anders zouden doen

Drie dingen, in volgorde van spijt.

We begonnen met één grote parser voor het Stedin-bestand. We hebben hem twee keer herschreven. De versie die uiteindelijk werkte, is een platte verzameling parsers met één functie per segmentcode en een dispatch-tabel. Daar hadden we moeten beginnen. Generiek aanvoelende code is een red flag in adapter-lagen waar het schema niet van jou is en de prijs van een fout een mens is die een verkeerd geboekte factuur leest.

We hebben de vierogen-drempel de eerste drie maanden als hardcoded constante laten staan. Het bestuur wilde 'm bijstellen nadat ze de echte maandagrapporten hadden gezien. Hem verplaatsen naar een rij in een policy-tabel was een halve dag werk die er vanaf dag één in had moeten zitten — elke drempel waarover de business gaat onderhandelen, hoort in data thuis, niet in code, en krijgt zijn eigen policy_version-kolom op elke afhankelijke rij.

We hebben de audit-view aanvankelijk niet voor de boekhouder ontsloten. Ze las PDF's die wij genereerden. Ze kan prima SQL lezen; we hadden haar vanaf het begin de read-only Metabase-connectie moeten geven. Tools die de operator zelf kan bedienen, winnen het van rapporten die jij steeds opnieuw moet draaien.

De vijfminutenaudit die je vandaag kunt doen

Toen wij deze reconciliatie-agent voor de Arnhemse coöperatie bouwden, was het stuk dat we onderschatten het 13 jaar oude allocatiebestand — een schoolvoorbeeld waarom procesautomatisering bovenop oudere Nederlandse infrastructuur half om het lezen van het bestand draait en half om het lezen van de kamer. De eerste parser die we hebben opgeleverd ging ervan uit dat de spec betrouwbaar was. De tweede ging ervan uit dat dat niet zo was. Die tweede draait nog steeds in productie.

Draai jij een reconciliatie tegen een legacy fixed-width-bestand van een Nederlandse netbeheerder? Dan is dit de audit: open je laatste vier invoerbestanden in een hex-editor en check de eerste byte van elke regel. Wijken die tussen bestanden af, dan decodeer je inconsistent en heb je het nog niet door. Dat is jouw zondagochtend om 03:47.