From ef74672906a8b5918e4c4b0984af77f75cd35267 Mon Sep 17 00:00:00 2001 From: Timo Wilken Date: Sat, 24 Aug 2024 20:27:54 +0200 Subject: 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. --- regenerate-secrets.sh | 7 +- .../files/personal-data-exporter/conso.json | 11 + tw/services/personal-data-exporter.scm | 228 +++++++++++++++++++++ .../files/restic/vin-electricity-conso-db.enc | 7 + tw/system/vin.scm | 51 +---- 5 files changed, 262 insertions(+), 42 deletions(-) create mode 100644 tw/services/files/personal-data-exporter/conso.json create mode 100644 tw/services/personal-data-exporter.scm create mode 100644 tw/system/files/restic/vin-electricity-conso-db.enc 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 (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 (prm) (api-token)) + (define-json-type (date) (value)) + (define-json-type + (points "interval_reading" #())) + + (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 + (($ 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 + (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 + (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 + (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 (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 (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 (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 -- cgit v1.2.3