Archive daily electricity consumption data from Enedis

Extract personal data collection programs into a separate service, and add one
that fetches electricity consumption data daily.

5 files changed, 262 insertions, 42 deletions

diff --git a/regenerate-secrets.sh b/regenerate-secrets.sh
index fbb79883..a93b4488 100755
--- a/regenerate-secrets.sh
+++ b/regenerate-secrets.sh
@@ -5,6 +5,7 @@ encto () {
   # its decrypted content does not change, so leave it alone.
   [ -e "$2" ] && return 0
   hostkey=$(awk '$1 ~ /^\[?'"$1"'\.(twilken\.net|local|fritz\.box)\]?(:[0-9]+)?$/ && $2 == "ssh-ed25519" { print $2, $3 }' ~/.ssh/known_hosts)
+  mkdir -p "$(dirname "$2")"
   age -e -a -r "${hostkey:?No hostkey found for $1}" -o "$2"
 }
 
@@ -30,7 +31,7 @@ for host in lap lud vin frm btl; do
 done
 
 # Restic repository passwords.
-for repo in vin-grafana timo-laptop timo-framework timo-battleship timo-phone timo-sync; do
+for repo in vin-grafana vin-electricity-conso-db timo-laptop timo-framework timo-battleship timo-phone timo-sync; do
   store_restic vin $repo
 done
 
@@ -54,3 +55,7 @@ EOF
 encto lud tw/system/files/paperless-secret-key.enc << EOF
 PAPERLESS_SECRET_KEY=$(pass show computers/lud/paperless/secret-key | head -1)
 EOF
+
+encto vin tw/services/files/personal-data-exporter/conso.json << EOF
+{"prm": "$(pass www/conso-api | sed -rn '/^prm: /s///p')", "api-token": "$(pass www/conso-api | head -1)"}
+EOF
diff --git a/tw/services/files/personal-data-exporter/conso.json b/tw/services/files/personal-data-exporter/conso.json
new file mode 100644
index 00000000..2cea0d1d
--- /dev/null
+++ b/tw/services/files/personal-data-exporter/conso.json
@@ -0,0 +1,11 @@
+-----BEGIN AGE ENCRYPTED FILE-----
+YWdlLWVuY3J5cHRpb24ub3JnL3YxCi0+IHNzaC1lZDI1NTE5IC9TV0hVQSB2QTh3
+VGluVyt6SFYvdWhTSkJHSnREUlU0ZTBqVVp5Rk92UVdSaXpLN0FNCktQNkl6QzBv
+ZnEyS0J0S29Ba0h1Y2RnUTZJTmlzYnUrd2NBWFJRcVVMRGcKLS0tIFdoUXVoSVph
+Y1BnV1RPVEpCYkd4T3FlMHkrUFJlb0xIVTFGVnB5OXV5Zk0KOnuG9XxgARaxFwxV
+GF2rudga2yIMNgbLOr+CXzmvodPAAwqhM5P/S/5+bADbDyfxwYhPz/SvvRoYlWIM
+o+Tta8SzUHUq3etKc6HIj7qqV1oITjZi0X/b9uOggX9sgT4r3+6mKRmkXh9MsLG9
+M3F/YvjddD6Bp3iW716bw3PILAFgqVSIpAfBI1BhUE6pb7vatbmZTucaV3Uqu0w/
+3NVhABunqhzdaeB9e6zdazG4n06xy9fOd8C8iHutKzu/JJnG082eKWCOrkw26L3b
+ID9nLD2Ox8SIUunuqZZxCA==
+-----END AGE ENCRYPTED FILE-----
diff --git a/tw/services/personal-data-exporter.scm b/tw/services/personal-data-exporter.scm
new file mode 100644
index 00000000..fb998459
--- /dev/null
+++ b/tw/services/personal-data-exporter.scm
@@ -0,0 +1,228 @@
+(define-module (tw services personal-data-exporter)
+  #:use-module (gnu)
+  #:use-module ((gnu packages backup)
+                #:select (restic))
+  #:use-module ((gnu packages guile)
+                #:select (guile-json-4))
+  #:use-module ((gnu packages guile-xyz)
+                #:select (guile-dbi guile-dbd-postgresql))
+  #:use-module ((gnu packages tls)
+                #:select (guile-gnutls))
+  #:use-module (gnu services)
+  #:use-module (gnu services configuration)
+  #:use-module (gnu services databases)
+  #:use-module (gnu services mcron)
+  #:use-module (gnu services shepherd)
+  #:use-module (guix gexp)
+  #:use-module (guix packages)
+  #:use-module ((guix records)
+                #:select (match-record))
+  #:use-module (tw packages finance)
+  #:use-module (tw services restic)
+  #:use-module (tw services secrets)
+  #:export (personal-data-exporter-configuration
+            personal-data-exporter-service-type))
+
+(define-configuration/no-serialization personal-data-exporter-configuration
+  (user string "The UNIX user name to run as locally.  The database user and
+database itself will be named after this user.")
+  (group string "The UNIX group of the @code{user} configured here.  Used to
+run daemons.")
+  (postgresql package "The PostgresQL package to use.  This must match the
+database server configured on the system as it is used to dump and backup the
+existing database.")
+  (ledger-file string "The location on disks where @code{ledgerplot} can
+expect the main ledger file.  It is expected that this file is synced
+externally, for example using Syncthing.")
+  (ledger-locale (string "en_US.utf8") "A locale definition present on the
+system, passed to @code{ledgerplot} so that it can read a UTF-8 ledger
+file.")
+  (conso-config-file (string "/etc/conso.json") "Configuration file for the
+electricity consumption fetcher, which stores the required secrets.  This file
+is completely managed by @code{personal-data-exporter-service-type}.")
+  (conso-backup-repo (string "/var/backups/electricty-conso-db") "Location of
+the backup repository where electricity consumption data will be archived.
+The repository must already exist and be owned by the configured user.")
+  (conso-backup-password (string "/etc/restic/vin-electricity-conso-db") "The
+file storing the password for the @code{conso-backup-repo}.  This file is
+completely managed by @code{personal-data-exporter-service-type}."))
+
+(define (conso-fetch-command config)
+  (match-record config <personal-data-exporter-configuration> (conso-config-file)
+    (program-file "conso-fetch-command"
+      (with-extensions (list guile-dbi guile-dbd-postgresql
+                             guile-json-4 guile-gnutls)   ; guile-gnutls needed by (web client)
+        #~(begin
+            (eval-when (expand load eval)
+              ;; guile-dbi and guile-dbd-postgresql are written for guile 2.2, but work fine on 3.0.
+              ;; Guix doesn't know this, so we need to add them to the load paths manually.
+              (set! %load-path
+                    (cons* #$(file-append guile-dbi "/share/guile/site/2.2")
+                           #$(file-append guile-dbd-postgresql "/share/guile/site/2.2")
+                           %load-path))
+              (set! %load-compiled-path
+                    (cons* #$(file-append guile-dbi "/lib/guile/2.2/site-ccache")
+                           #$(file-append guile-dbd-postgresql "/lib/guile/2.2/site-ccache")
+                           %load-compiled-path)))
+
+            (use-modules (ice-9 match)
+                         (ice-9 receive)
+                         (srfi srfi-19)             ; dates
+                         (web client)
+                         (web response)
+                         (dbi dbi)
+                         (json))
+
+            (define-json-type <settings> (prm) (api-token))
+            (define-json-type <data-point> (date) (value))
+            (define-json-type <data>
+              (points "interval_reading" #(<data-point>)))
+
+            (define (conso-request settings endpoint)
+              "Fetch data from the given conso.boris.sh API ENDPOINT with secrets from SETTINGS."
+              (let* ((today (current-date))
+                     (yesterday (julian-day->date (1- (date->julian-day today)))))
+                (receive (response body)
+                    (http-request
+                     (string-append "https://conso.boris.sh/api/" endpoint
+                                    "?prm=" (settings-prm settings)
+                                    "&start=" (date->string yesterday "~1")
+                                    "&end=" (date->string today "~1"))
+                     #:headers `((authorization . (basic . ,(settings-api-token settings)))
+                                 (user-agent . "conso.scm/0.1") (from . "abuse@twilken.net"))
+                     #:streaming? #t)
+                  (match (response-code response)
+                    (200 (data-points (json->data body)))
+                    (err (error "Got error response from server:" err (response-reason-phrase response)))))))
+
+            (define (insert-statement db table)
+              "Generate a function that inserts the data point given to it into TABLE in DB."
+              (match-lambda
+                (($ <data-point> date value)
+                 (let ((date-type (if (string-contains date " ") "timestamp" "date")))
+                   (dbi-query db (format #f "insert into \"~a\" values (~a '~a', ~a);"
+                                         table date-type date value))))))
+
+            (let ((settings (call-with-input-file #$conso-config-file json->settings))
+                  (db (dbi-open "postgresql" ":::socket:")))
+              (dbi-query db "
+create table if not exists \"conso_daily\" (\"time\" date primary key, \"value\" real not null);
+create table if not exists \"conso_load\" (\"time\" timestamp primary key, \"value\" real not null);
+create table if not exists \"conso_max_power\" (\"time\" timestamp primary key, \"value\" real not null);
+")
+              (for-each (insert-statement db "conso_daily")
+                        (conso-request settings "daily_consumption"))
+              (for-each (insert-statement db "conso_max_power")
+                        (conso-request settings "consumption_max_power"))
+              (for-each (insert-statement db "conso_load_curve")
+                        (conso-request settings "consumption_load_curve"))
+              (dbi-close db)))))))
+
+(define (conso-backup-command config)
+  (match-record config <personal-data-exporter-configuration>
+                (postgresql user conso-backup-repo conso-backup-password)
+    (program-file "conso-backup-command"
+      #~(begin
+          (use-modules (srfi srfi-1) (ice-9 popen) (ice-9 receive))
+
+          (setenv "RESTIC_REPOSITORY" #$conso-backup-repo)
+          (setenv "RESTIC_PASSWORD_FILE" #$conso-backup-password)
+
+          (receive (from to pids)
+              (pipeline
+               ;; Match postgres version of the running server here.
+               '((#$(file-append postgresql "/bin/pg_dump")
+                  "-wU" #$user "-t" "conso_*" #$user)
+                 (#$(file-append restic "/bin/restic") "backup"
+                  "--no-cache" "--stdin" "--stdin-filename=conso.sql")))
+
+            (close to)
+            (do ((char (read-char from) (read-char from)))
+                ((eof-object? char))
+              (display char))
+            (close from)
+            (exit (every (compose (lambda (ev) (and ev (zero? ev)))
+                                  status:exit-val cdr waitpid)
+                         pids)))))))
+
+(define (conso-backup-cleanup config)
+  (match-record config <personal-data-exporter-configuration>
+                (user conso-backup-repo conso-backup-password)
+    (list (restic-scheduled-cleanup
+           (schedule #~"0 10 * * *")
+           (repo (restic-local-repository (path conso-backup-repo)))
+           (password (restic-password-source (type 'file) (name conso-backup-password)))
+           (user user)
+           (keep-daily 14)
+           (keep-monthly -1)))))
+
+(define (conso-secrets config)
+  (match-record config <personal-data-exporter-configuration>
+                (user group conso-config-file conso-backup-password)
+    (list (secret
+           (encrypted-file (local-file "files/personal-data-exporter/conso.json"))
+           (destination conso-config-file)
+           (user user) (group group))
+          (secret
+           (encrypted-file (local-file "../system/files/restic/vin-electricity-conso-db.enc"))
+           (destination conso-backup-password)
+           (user user) (group group)))))
+
+(define (personal-data-cronjobs config)
+  (match-record config <personal-data-exporter-configuration> (user ledger-file)
+    ;; Ledgerplot uses the Boerse Frankfurt API, so run after markets close there.
+    ;; According to https://www.boerse.de/handelszeiten/, it's 22:00 CET/CEST.
+    (list #~(job "5 22 * * mon-fri"   ; weekdays after market close
+                 #$(program-file "ledgerplot-exchange-rates-command"
+                     #~(begin
+                         (setenv "LEDGER_FILE" #$ledger-file)
+                         (execl #$(file-append ledgerplot "/bin/ledgerplot")
+                                "ledgerplot" "-em")))
+                 #:user #$user)
+
+          ;; Process the previous day's data during the night.
+          #~(job "0 4 * * *" #$(conso-fetch-command config) #:user #$user)
+
+          ;; Back up electricity consumption database.  The other data can
+          ;; be easily recreated from the source data, but this data
+          ;; becomes inaccessible after 2 years via the API.
+          #~(job "0 5 * * *" #$(conso-backup-command config) #:user #$user))))
+
+(define (personal-data-shepherd-services config)
+  (match-record config <personal-data-exporter-configuration> (user group ledger-file ledger-locale)
+    (list (shepherd-service
+           (provision '(ledgerplot))
+           (requirement (list 'postgresql (string->symbol (string-append "syncthing-" user))))
+           (documentation
+            "Monitor a ledger file and keep a database in sync with it.")
+           (start #~(make-forkexec-constructor
+                     (list #$(file-append ledgerplot "/bin/ledgerplot")
+                           ;; Use local socket auth so that we don't have to supply a password.
+                           "-wd" #$user "-U" #$user "-H" "/var/run/postgresql")
+                     #:user #$user #:group #$group
+                     #:environment-variables
+                     (cons*
+                      (string-append "LEDGER_FILE=" #$ledger-file)
+                      ;; Use an appropriate locale so that ledgerplot
+                      ;; can read the UTF-8 ledger file.
+                      (string-append "LC_ALL=" #$ledger-locale)
+                      (default-environment-variables))))
+           (stop #~(make-kill-destructor))))))
+
+(define (personal-data-db-roles config)
+  (match-record config <personal-data-exporter-configuration> (user)
+    (list (postgresql-role
+           (name user)
+           (create-database? #t)
+           (permissions '(login))))))
+
+(define personal-data-exporter-service-type
+  (service-type
+   (name 'personal-data)
+   (extensions
+    (list (service-extension shepherd-root-service-type personal-data-shepherd-services)
+          (service-extension postgresql-role-service-type personal-data-db-roles)
+          (service-extension mcron-service-type personal-data-cronjobs)
+          (service-extension restic-cleanup-service-type conso-backup-cleanup)
+          (service-extension secrets-service-type conso-secrets)))
+   (description "Sync various personal data to a database, for displaying in Grafana.")))
diff --git a/tw/system/files/restic/vin-electricity-conso-db.enc b/tw/system/files/restic/vin-electricity-conso-db.enc
new file mode 100644
index 00000000..eaf10064
--- /dev/null
+++ b/tw/system/files/restic/vin-electricity-conso-db.enc
@@ -0,0 +1,7 @@
+-----BEGIN AGE ENCRYPTED FILE-----
+YWdlLWVuY3J5cHRpb24ub3JnL3YxCi0+IHNzaC1lZDI1NTE5IC9TV0hVQSBMMkRC
+L3ZXSUZrS011cndFR0NaaW5TNS9LOVNaUjJ5NXBJemJJMGNrcGpZCmY2TkIwakRk
+SDVjZmcvMkhhWGVJOEpJTWNwWVNlQ2FRalhhZldsdE5maHMKLS0tIExZV0Z6NWtM
+K3RvN2NKdm0ycFp0TlN5QUMvUzB4U05ZVjZGZDFBTUI2N0UKscV1whui8YRflvSt
+xYCv4MG9WV3ZNIA7hrhSaXbyAqOfnvrGS6vlwjTP19xPoie5sg==
+-----END AGE ENCRYPTED FILE-----
diff --git a/tw/system/vin.scm b/tw/system/vin.scm
index e7f4bb4b..772098a0 100644
--- a/tw/system/vin.scm
+++ b/tw/system/vin.scm
@@ -8,16 +8,14 @@
   #:use-module (gnu services docker)
   #:use-module (gnu services dbus)
   #:use-module (gnu services desktop)  ; elogind-service-type
-  #:use-module (gnu services mcron)
-  #:use-module (gnu services shepherd)
   #:use-module (gnu services syncthing)
   #:use-module (gnu system locale)
   #:use-module (gnu system nss)
   #:use-module (guix gexp)
   #:use-module (tw channels)
-  #:use-module (tw packages finance)
   #:use-module (tw services dns)
   #:use-module (tw services grafana)
+  #:use-module (tw services personal-data-exporter)
   #:use-module (tw services restic)
   #:use-module (tw services secrets)
   #:use-module (tw system))
@@ -208,49 +206,20 @@
         (certbot-configuration
          (email "letsencrypt@twilken.net")))
 
-      ;; Personal statistics exporter: stores hledger data (and soon location
-      ;; data?) in Postgres for Grafana to read.
+      ;; Personal statistics exporter: stores electricity and hledger data
+      ;; (and soon location data?) in Postgres for Grafana to read.
       (service syncthing-service-type
         (syncthing-configuration (user "timo")))
       (service postgresql-service-type
         (postgresql-configuration
          (postgresql postgresql-15)))
-      (simple-service 'ledgerplot-roles postgresql-role-service-type
-        (list (postgresql-role
-               (name "timo")
-               (create-database? #t)
-               (permissions '(login)))))
-      (simple-service 'ledgerplot-file-watcher shepherd-root-service-type
-        (list (shepherd-service
-               (provision '(ledgerplot))
-               (requirement '(postgresql syncthing-timo))
-               (documentation
-                "Monitor a ledger file and keep a database in sync with it.")
-               (start #~(make-forkexec-constructor
-                         (list #$(file-append ledgerplot "/bin/ledgerplot")
-                               "-wd" "timo" "-U" "timo" "-H" "/var/run/postgresql")
-                         #:user "timo" #:group "users"
-                         #:environment-variables
-                         (cons*
-                          ;; This is where Syncthing puts the ledger file.
-                          "LEDGER_FILE=/home/timo/sync/ledger/ledger.journal"
-                          ;; Use an appropriate locale so that ledgerplot
-                          ;; can read the UTF-8 ledger file.
-                          (string-append
-                           "LC_ALL=" #$(locale-definition-name
-                                        (car locale-definitions)))
-                          (default-environment-variables))))
-               (stop #~(make-kill-destructor)))))
-      (simple-service 'ledgerplot-exchange-rates mcron-service-type
-        ;; Ledgerplot uses the Boerse Frankfurt API, so run after markets close there.
-        ;; According to https://www.boerse.de/handelszeiten/, it's 22:00 CET/CEST.
-        (list #~(job "5 22 * * mon-fri"   ; weekdays after market close
-                     #$(program-file "ledgerplot-exchange-rates-command"
-                         #~(begin
-                             (setenv "LEDGER_FILE" "/home/timo/sync/ledger/ledger.journal")
-                             (execl #$(file-append ledgerplot "/bin/ledgerplot")
-                                    "ledgerplot" "-em")))
-                     #:user "timo")))
+      (service personal-data-exporter-service-type
+        (personal-data-exporter-configuration
+         (user "timo") (group "users")
+         ;; This is where Syncthing puts the ledger file.
+         (ledger-file "/home/timo/sync/ledger/ledger.journal")
+         ;; Match the Postgres version configured for the server.
+         (postgresql postgresql-15)))
 
       (service unattended-upgrade-service-type
         (unattended-upgrade-configuration