1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
|
(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-squee))
#: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-squee guile-json-4 guile-gnutls) ; guile-gnutls needed by (web client)
#~(begin
(use-modules (ice-9 match)
(ice-9 receive)
(srfi srfi-19) ; dates
(web client)
(web response)
(squee)
(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."
(let ((query (format #f "insert into \"~a\" values ($1, $2) on conflict (\"time\") do update set \"value\" = EXCLUDED.\"value\";" table)))
(match-lambda
(($ <data-point> date value)
;; If a value already exists for any given point in time, replace it.
(exec-query db query (list date value))))))
(let ((settings (call-with-input-file #$conso-config-file json->settings))
(db (connect-to-postgres-paramstring "")))
(exec-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")
(conso-request settings "consumption_load_curve"))
(pg-conn-finish 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.")))
|
