fix: Final-Review-Findings (GUI-Login, FK-Kaskaden, Validierung, Duplikat-Recheck, Views)

F1: GUI-Login-Hash gegen $-Korruption gehaertet. .env-Werte einfach-gequotet
    geschrieben + Sanity-Check nach Sourcing. Zusaetzlich systemd-Ebene:
    podman escapt $->$$ in Environment=, systemd 252 kollabiert das nicht ->
    sed-Post-Processing der Unit-Dateien. Live-.env: GUI-Passwort rotiert.
F2: Explizite Dependent-Deletes gegen FK-500 (delete_scenario/loan), 409 bei
    bestaetigtem Import-Delete. Test-Engine erzwingt PRAGMA foreign_keys=ON.
F3: Enum/Range-Validierung (Literal + Field ge/le) fuer Recurring, Loan,
    Modifier inkl. PATCH-Pfade.
F4: Confirm nur fuer draft-Statements (sonst 409) + Dedup-Re-Check gegen
    bestaetigte Buchungen vor dem Promoten.
F5: v_balance_history/v_balance_total addieren pro Konto den opening_balance
    des fruehesten bestaetigten Statements (approved deviation vom Plan-SQL).
F6: patch_scenario Namenskollision -> 409; create_transaction unbekanntes
    Konto -> 404 (statt 500).
F7: Generierte systemd-Unit-Dateien chmod 600 (enthalten Env-Secrets).
F8: Globaler htmx:responseError-Handler in base.html.

Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>

finance/app/models/views.py

@@ -1,17 +1,33 @@
 from sqlalchemy import text
 from sqlalchemy.engine import Engine
 
+# Per-account starting balance: the opening_balance of each account's earliest
+# confirmed statement (if any). Used to seed the cumulative sums below so the
+# Grafana balance charts show the real account balance, not just the running
+# sum of imported transactions (which would start at zero and ignore the
+# balance that existed before the first imported statement).
+_ACCOUNT_BASE = """
+    SELECT DISTINCT ON (account_id) account_id, opening_balance
+    FROM statements
+    WHERE status = 'confirmed' AND opening_balance IS NOT NULL
+    ORDER BY account_id, period_start NULLS LAST, id
+"""
+
 VIEWS: dict[str, str] = {
-    "v_balance_history": """
+    "v_balance_history": f"""
         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
+               COALESCE(b.opening_balance, 0)
+                 + 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
+        LEFT JOIN ({_ACCOUNT_BASE}) b ON b.account_id = a.id
         WHERE t.status = 'confirmed'
-        GROUP BY a.id, a.name, t.booking_date""",
-    "v_balance_total": """
+        GROUP BY a.id, a.name, t.booking_date, b.opening_balance""",
+    "v_balance_total": f"""
         SELECT booking_date AS day,
-               SUM(SUM(amount)) OVER (ORDER BY booking_date) AS balance
+               (SELECT COALESCE(SUM(opening_balance), 0)
+                FROM ({_ACCOUNT_BASE}) s)
+                 + 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,
@@ -30,4 +46,13 @@ def create_views(engine: Engine) -> None:
         return
     with engine.begin() as conn:
         for name, body in VIEWS.items():
-            conn.execute(text(f"CREATE OR REPLACE VIEW {name} AS {body}"))
+            # CREATE OR REPLACE VIEW fails if the output column set changed
+            # (name/type/order). Fall back to DROP + CREATE in that case so a
+            # redeploy always applies the current definition.
+            try:
+                with conn.begin_nested():
+                    conn.execute(
+                        text(f"CREATE OR REPLACE VIEW {name} AS {body}"))
+            except Exception:
+                conn.execute(text(f"DROP VIEW IF EXISTS {name} CASCADE"))
+                conn.execute(text(f"CREATE VIEW {name} AS {body}"))