feat: SQL-Views und Grafana-Provisionierung
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
This commit is contained in:
@@ -1,4 +1,5 @@
|
||||
import hmac
|
||||
from contextlib import asynccontextmanager
|
||||
|
||||
from fastapi import FastAPI, Form, Request
|
||||
from fastapi.responses import HTMLResponse, RedirectResponse
|
||||
@@ -6,11 +7,20 @@ from fastapi.staticfiles import StaticFiles
|
||||
|
||||
from app import auth
|
||||
from app.config import get_settings
|
||||
from app.db import get_engine
|
||||
from app.models.views import create_views
|
||||
from app.routers import (accounts, categories, gui, imports, planning,
|
||||
scenarios, transactions)
|
||||
from app.routers.gui import templates
|
||||
|
||||
app = FastAPI(title="Finanzberatungs-Tool")
|
||||
|
||||
@asynccontextmanager
|
||||
async def lifespan(app: FastAPI):
|
||||
create_views(get_engine())
|
||||
yield
|
||||
|
||||
|
||||
app = FastAPI(title="Finanzberatungs-Tool", lifespan=lifespan)
|
||||
|
||||
app.mount("/static", StaticFiles(directory="app/static"), name="static")
|
||||
|
||||
|
||||
33
finance/app/models/views.py
Normal file
33
finance/app/models/views.py
Normal file
@@ -0,0 +1,33 @@
|
||||
from sqlalchemy import text
|
||||
from sqlalchemy.engine import Engine
|
||||
|
||||
VIEWS: dict[str, str] = {
|
||||
"v_balance_history": """
|
||||
SELECT t.booking_date AS day, a.name AS account,
|
||||
SUM(SUM(t.amount)) OVER (PARTITION BY a.id
|
||||
ORDER BY t.booking_date) AS balance
|
||||
FROM transactions t JOIN accounts a ON a.id = t.account_id
|
||||
WHERE t.status = 'confirmed'
|
||||
GROUP BY a.id, a.name, t.booking_date""",
|
||||
"v_balance_total": """
|
||||
SELECT booking_date AS day,
|
||||
SUM(SUM(amount)) OVER (ORDER BY booking_date) AS balance
|
||||
FROM transactions WHERE status = 'confirmed' GROUP BY booking_date""",
|
||||
"v_monthly_by_category": """
|
||||
SELECT date_trunc('month', t.booking_date) AS month,
|
||||
COALESCE(c.name, 'unkategorisiert') AS category,
|
||||
SUM(t.amount) AS total
|
||||
FROM transactions t LEFT JOIN categories c ON c.id = t.category_id
|
||||
WHERE t.status = 'confirmed' GROUP BY 1, 2""",
|
||||
"v_projection": """
|
||||
SELECT s.name AS scenario, p.day, p.balance
|
||||
FROM projection_points p JOIN scenarios s ON s.id = p.scenario_id""",
|
||||
}
|
||||
|
||||
|
||||
def create_views(engine: Engine) -> None:
|
||||
if engine.dialect.name != "postgresql":
|
||||
return
|
||||
with engine.begin() as conn:
|
||||
for name, body in VIEWS.items():
|
||||
conn.execute(text(f"CREATE OR REPLACE VIEW {name} AS {body}"))
|
||||
171
finance/grafana/dashboards/finanzen.json
Normal file
171
finance/grafana/dashboards/finanzen.json
Normal file
@@ -0,0 +1,171 @@
|
||||
{
|
||||
"uid": "finanzen",
|
||||
"title": "Finanzen",
|
||||
"schemaVersion": 39,
|
||||
"version": 1,
|
||||
"editable": true,
|
||||
"timezone": "browser",
|
||||
"tags": ["finanzen"],
|
||||
"time": {
|
||||
"from": "now-1y",
|
||||
"to": "now"
|
||||
},
|
||||
"refresh": "",
|
||||
"panels": [
|
||||
{
|
||||
"id": 1,
|
||||
"type": "timeseries",
|
||||
"title": "Kontostand-Verlauf",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"gridPos": { "h": 8, "w": 12, "x": 0, "y": 0 },
|
||||
"fieldConfig": {
|
||||
"defaults": {
|
||||
"custom": {
|
||||
"drawStyle": "line",
|
||||
"lineWidth": 1,
|
||||
"fillOpacity": 0
|
||||
},
|
||||
"unit": "currencyEUR"
|
||||
},
|
||||
"overrides": []
|
||||
},
|
||||
"options": {
|
||||
"legend": { "displayMode": "list", "placement": "bottom" },
|
||||
"tooltip": { "mode": "multi" }
|
||||
},
|
||||
"targets": [
|
||||
{
|
||||
"refId": "A",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"format": "time_series",
|
||||
"rawSql": "SELECT day AS time, account AS metric, balance AS value FROM v_balance_history ORDER BY day",
|
||||
"rawQuery": true
|
||||
}
|
||||
]
|
||||
},
|
||||
{
|
||||
"id": 2,
|
||||
"type": "timeseries",
|
||||
"title": "Gesamt-Saldo",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"gridPos": { "h": 8, "w": 12, "x": 12, "y": 0 },
|
||||
"fieldConfig": {
|
||||
"defaults": {
|
||||
"custom": {
|
||||
"drawStyle": "line",
|
||||
"lineWidth": 2,
|
||||
"fillOpacity": 10
|
||||
},
|
||||
"unit": "currencyEUR"
|
||||
},
|
||||
"overrides": []
|
||||
},
|
||||
"options": {
|
||||
"legend": { "displayMode": "list", "placement": "bottom" },
|
||||
"tooltip": { "mode": "single" }
|
||||
},
|
||||
"targets": [
|
||||
{
|
||||
"refId": "A",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"format": "time_series",
|
||||
"rawSql": "SELECT day AS time, balance AS value FROM v_balance_total ORDER BY day",
|
||||
"rawQuery": true
|
||||
}
|
||||
]
|
||||
},
|
||||
{
|
||||
"id": 3,
|
||||
"type": "barchart",
|
||||
"title": "Monat nach Kategorie",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"gridPos": { "h": 8, "w": 12, "x": 0, "y": 8 },
|
||||
"fieldConfig": {
|
||||
"defaults": {
|
||||
"custom": {
|
||||
"stacking": { "mode": "normal", "group": "A" }
|
||||
},
|
||||
"unit": "currencyEUR"
|
||||
},
|
||||
"overrides": []
|
||||
},
|
||||
"options": {
|
||||
"legend": { "displayMode": "list", "placement": "bottom" },
|
||||
"stacking": "normal",
|
||||
"xTickLabelRotation": 0
|
||||
},
|
||||
"targets": [
|
||||
{
|
||||
"refId": "A",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"format": "time_series",
|
||||
"rawSql": "SELECT month AS time, category AS metric, total AS value FROM v_monthly_by_category ORDER BY month",
|
||||
"rawQuery": true
|
||||
}
|
||||
]
|
||||
},
|
||||
{
|
||||
"id": 4,
|
||||
"type": "timeseries",
|
||||
"title": "Szenario-Vergleich",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"gridPos": { "h": 8, "w": 12, "x": 12, "y": 8 },
|
||||
"fieldConfig": {
|
||||
"defaults": {
|
||||
"custom": {
|
||||
"drawStyle": "line",
|
||||
"lineWidth": 1,
|
||||
"fillOpacity": 0,
|
||||
"thresholdsStyle": { "mode": "line" }
|
||||
},
|
||||
"unit": "currencyEUR",
|
||||
"thresholds": {
|
||||
"mode": "absolute",
|
||||
"steps": [
|
||||
{ "color": "red", "value": 0 },
|
||||
{ "color": "orange", "value": 500 }
|
||||
]
|
||||
}
|
||||
},
|
||||
"overrides": []
|
||||
},
|
||||
"options": {
|
||||
"legend": { "displayMode": "list", "placement": "bottom" },
|
||||
"tooltip": { "mode": "multi" }
|
||||
},
|
||||
"targets": [
|
||||
{
|
||||
"refId": "A",
|
||||
"datasource": {
|
||||
"type": "postgres",
|
||||
"uid": "FinanzDB"
|
||||
},
|
||||
"format": "time_series",
|
||||
"rawSql": "SELECT day AS time, scenario AS metric, balance AS value FROM v_projection ORDER BY day",
|
||||
"rawQuery": true
|
||||
}
|
||||
]
|
||||
}
|
||||
]
|
||||
}
|
||||
6
finance/grafana/provisioning/dashboards/provider.yaml
Normal file
6
finance/grafana/provisioning/dashboards/provider.yaml
Normal file
@@ -0,0 +1,6 @@
|
||||
apiVersion: 1
|
||||
providers:
|
||||
- name: finanzen
|
||||
type: file
|
||||
options:
|
||||
path: /var/lib/grafana/dashboards
|
||||
12
finance/grafana/provisioning/datasources/postgres.yaml
Normal file
12
finance/grafana/provisioning/datasources/postgres.yaml
Normal file
@@ -0,0 +1,12 @@
|
||||
apiVersion: 1
|
||||
datasources:
|
||||
- name: FinanzDB
|
||||
type: postgres
|
||||
access: proxy
|
||||
url: localhost:5432
|
||||
user: finance_read
|
||||
jsonData:
|
||||
database: finance
|
||||
sslmode: disable
|
||||
secureJsonData:
|
||||
password: ${FINANCE_READ_PASSWORD}
|
||||
Reference in New Issue
Block a user