🚀 BSN-Chatsystem v2.1 — Implementierungsplan (extern geprüft)

Ziel: Das BSN-Chatsystem (chat.datenhimmel.work) 1:1 funktional auf dedizierten dogado-Server migrieren, Architektur produktionsreif.

┌──────────────────────────────────────────────────┐
│              🌍 Cloudflare                        │
│  Cache Rules: / + /beitrag/* → 5min              │
│  Bypass: /api/*, /webhook, /admin, /telegram     │
│  Access (OTP): /admin/*                          │
│  DDoS, WAF, weltweit Edge                        │
└────────┬─────────────────────────────────────────┘
         │ cloudflared Tunnel → localhost:80
┌────────▼─────────────────────────────────────────┐
│  nginx Reverse-Proxy :80                         │
│  limit_req_zone im HTTP-Kontext                  │
│  /static/* → direkt von Disk                     │
│  /media/*  → direkt von Disk                     │
│  proxy_pass → gunicorn :8000                     │
│  KEIN nginx-Cache (macht Cloudflare)             │
└────────┬─────────────────────────────────────────┘
         │ HTTP :8000
┌────────▼──────────┐    ┌─────────────────────────┐
│  bsn-chatbot      │    │  bsn-worker             │
│  gunicorn 4w×2t   │    │  eigener systemd-       │
│  wsgi:app         │    │  Service                │
│  User: bsn        │    │  pollt submission_queue │
│  Flask-Routen     │    │  Whisper 1× geladen     │
└────────┬──────────┘    │  kein Recycling mid-    │
         │               │  Verarbeitung           │
         │               └───────────┬─────────────┘
         │                           │
┌────────▼───────────────────────────▼─────────────┐
│              SQLite (WAL-Mode)                   │
│  bsn_intake.db                                   │
└──────────────────────────────────────────────────┘

Extern: OpenRouter API (Mistral 4), Meta Cloud API (WhatsApp send)

READ (~95%)

User → Cloudflare Edge (Cache HIT, 85%) → <50ms → FERTIG
User → Cloudflare (Cache MISS) → nginx → gunicorn → SQLite → HTML
  → Cloudflare cached für 5min → nächster Request = HIT
⚠️ Flask DARF KEIN Set-Cookie auf / + /beitrag/* senden!

WRITE (~5%)

WhatsApp → Meta → POST /whatsapp/webhook → nginx → gunicorn:
  1. Signatur-Prüfung (HMAC) → 403 bei Fehler
  2. INSERT INTO submission_queue → 200 OK in <10ms

bsn-worker (eigener Prozess, pollt alle 2s):
  1. SELECT pending LIMIT 3
  2. UPDATE status=processing
  3. Media-Download + Transkription (Whisper) + LLM-Triage
  4. INSERT INTO submissions
  5. UPDATE status=completed

submissions (Haupttabelle)

CREATE TABLE submissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    channel TEXT NOT NULL,                -- whatsapp, telegram, web
    sender_id TEXT NOT NULL,
    sender_name TEXT,
    type TEXT NOT NULL,                   -- text, image, audio, video
    content TEXT,
    media_path TEXT,
    media_publish_flags TEXT,
    status TEXT DEFAULT 'new',            -- new, done, published, flagged, gdpr_deleted
    category TEXT,
    summary TEXT,
    publish_name TEXT,
    halle TEXT,
    spiel_titel TEXT,
    display_text TEXT,
    triage_tier INTEGER,                  -- 1=🟢, 2=🟡, 3=🔴
    triage_reasons TEXT,                  -- JSON-Array
    triage_at TEXT,
    auto_response_sent INTEGER DEFAULT 0,
    public INTEGER DEFAULT 0,
    published_at TEXT,
    deleted_at TEXT,                      -- Soft-Delete für DSGVO-Löschroutine
    created_at TEXT DEFAULT (datetime('now','localtime')),
    -- weitere Felder: email, phone, likes, views
);

submission_queue (Worker-Queue)

CREATE TABLE submission_queue (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    channel TEXT NOT NULL,
    message_id TEXT UNIQUE NOT NULL,       -- WhatsApp msg ID / Telegram update_id
    payload TEXT NOT NULL,                 -- JSON: Webhook-Body
    status TEXT DEFAULT 'pending',         -- pending, processing, completed, failed
    attempt_count INTEGER DEFAULT 0,
    last_error TEXT,
    created_at TEXT DEFAULT (datetime('now','localtime')),
    updated_at TEXT DEFAULT (datetime('now','localtime'))
);
CREATE INDEX idx_queue_status ON submission_queue(status);

Queue-Flow

POST /whatsapp/webhook → HMAC-Prüfung → INSERT submission_queue → 200 OK (<10ms)

bsn-worker (eigener systemd-Prozess):
  1. Whisper-Modell laden (1× beim Start)
  2. WHILE True:
     SELECT ... WHERE status='pending' LIMIT 3
     FOR EACH:
       UPDATE status='processing'
       → Media-Download
       → Transkription (faster-whisper, Modell bereits geladen)
       → LLM-Triage (OpenRouter)
       → INSERT INTO submissions
       → UPDATE status='completed'
     SLEEP 2

Idempotenz

message_id UNIQUE verhindert Doppelverarbeitung. Meta wiederholt Webhooks? → INSERT OR IGNORE.

Fehlertoleranz

# .env:
OPENROUTER_API_KEY=sk-or-v1-...n
# API: POST https://openrouter.ai/api/v1/chat/completions
# Modell: "mistralai/mistral-large"

Kosten: ~$0.26/h bei Volllast.

10.1 Cache Rules (Cloudflare Dashboard → Caching → Cache Rules)

10.2 Kein Set-Cookie auf öffentlichen Routen

10.3 Cloudflare Access für /admin/*

Cloudflare Dashboard → Zero Trust → Access → Applications → Add:
  Name: "BSN Admin"
  Domain: chat.datenhimmel.work
  Path: /admin/*
  Policy: Allow, One-time PIN → E-Mail an daniel@brettspiel-news.de

So ist /admin/* ab Go-Live durch OTP geschützt. Kein Passwort im Flask nötig.

Schritt 1: OS

# dogado-Kundencenter → Neuinstallation → Ubuntu 24.04 LTS minimal
# KEIN Plesk. Root-Passwort selbst setzen.

Schritt 2: Basis

apt update && apt upgrade -y
apt install -y python3.13 python3.13-venv python3.13-dev \
    git nginx ufw fail2ban curl unzip build-essential restic

Schritt 3: Service-User

useradd --system --home /opt/bsn-chatbot --shell /bin/bash bsn
mkdir -p /opt/bsn-chatbot/{src,data,logs,media,backups}
chown -R bsn:bsn /opt/bsn-chatbot

Schritt 4: Firewall

ufw default deny incoming
ufw allow 22/tcp
ufw allow 80/tcp
ufw allow 443/tcp
ufw enable

Schritt 5: venv

su - bsn
python3.13 -m venv /opt/bsn-chatbot/venv
source /opt/bsn-chatbot/venv/bin/activate
pip install --upgrade pip wheel

Schritt 6: Repo

cd /opt/bsn-chatbot/src
git clone git@git.datenhimmel.work:danielkrause/BSN-Chatsystem.git .
pip install -r requirements.txt
pip install faster-whisper gunicorn

Schritt 7: .env

cat > /opt/bsn-chatbot/src/.env << 'EOF'
DATABASE_PATH=/opt/bsn-chatbot/data/bsn_intake.db
MEDIA_DIR=/opt/bsn-chatbot/data/media
META_TOKEN=EAAx.....n
OPENR....I_KEY=sk-or-v1-...n
QUEUE_POLL_INTERVAL=2
QUEUE_BATCH_SIZE=3
TRIAGE_DELAY_SECONDS=360
EOF
chmod 600 /opt/bsn-chatbot/src/.env

Schritt 8: Daten migrieren

scp hermes@185.162.249.159:~/workspace/bsn-chatbot/bsn_intake.db /opt/bsn-chatbot/data/
scp -r hermes@185.162.249.159:~/workspace/bsn-chatbot/media/* /opt/bsn-chatbot/data/media/
chown -R bsn:bsn /opt/bsn-chatbot/data

Schritt 9: cloudflared (EINMAL)

wget -q https://github.com/cloudflare/cloudflared/releases/latest/download/cloudflared-linux-amd64
chmod +x cloudflared-linux-amd64
mv cloudflared-linux-amd64 /usr/local/bin/cloudflared

# Login (einmalig, Browser):
cloudflared tunnel login

# Tunnel ERSTELLEN:
cloudflared tunnel create bsn-chat

# DNS-Route (CNAME):
cloudflared tunnel route dns bsn-chat chat.datenhimmel.work

# Tunnel-ID notieren (z.B. e84dedca-...)

Schritt 10: cloudflared Config

cat > ~/.cloudflared/config.yml << 'CFEOF'
tunnel: <TUNNEL_ID>
credentials-file: ~/.cloudflared/<TUNNEL_ID>.json
ingress:
  - hostname: chat.datenhimmel.work
    service: http://localhost:80
  - service: http_status:404
CFEOF

cloudflared service install
systemctl enable --now cloudflared

Schritt 11: Tunnel-Credentials auf Ketchup kopieren

# Auf dogado:
scp ~/.cloudflared/<TUNNEL_ID>.json hermes@185.162.249.159:~/.cloudflared/
scp ~/.cloudflared/cert.pem hermes@185.162.249.159:~/.cloudflared/

# Auf Ketchup:
cp <TUNNEL_ID>.json ~/workspace/bsn-chatbot/
# config.yml auf Ketchup auf localhost:5002 zeigen lassen

Schritt 12: Cloudflare Cache Rules

Cloudflare Dashboard → Caching → Cache Rules → Create Rule:
  Field: URI Path, Operator: contains, Value: /beitrag/
  (plus separate rule for /static/*)
  → Eligible for Cache, Edge TTL: 5 min / 365 days

Schritt 13: Cloudflare Access

Cloudflare Dashboard → Zero Trust → Access → Applications → Add:
  Application: BSN Admin
  Domain: chat.datenhimmel.work
  Path: /admin/*
  → Policy: Allow, One-time PIN → daniel@brettspiel-news.de

Schritt 14: Flask prüfen — kein Set-Cookie auf /

# In app.py prüfen: after_request setzt keine Cookies auf öffentlichen Routen
grep -n "set_cookie\|Set-Cookie\|session\[" /opt/bsn-chatbot/src/app.py
# Falls Cookies gesetzt werden → auf /admin/* beschränken

12.1 nginx: /etc/nginx/sites-available/bsn-chatbot

# /etc/nginx/nginx.conf (HTTP-Kontext):
http {
    limit_req_zone $binary_remote_addr zone=api:10m rate=30r/s;
    limit_req_zone $binary_remote_addr zone=webhook:10m rate=10r/s;
    # ...
}

# /etc/nginx/sites-available/bsn-chatbot:
server {
    listen 80;
    server_name _;   # cloudflared verbindet per IP

    client_max_body_size 50M;

    # Static Files
    location /static/ {
        alias /opt/bsn-chatbot/src/static/;
        expires 365d;
        add_header Cache-Control "public, immutable";
    }

    # Media Files
    location /media/ {
        alias /opt/bsn-chatbot/data/media/;
        expires 7d;
    }

    # Webhooks — exakte Pfade
    location ~ ^/(whatsapp|telegram)/webhook {
        proxy_pass http://127.0.0.1:8000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        limit_req zone=webhook burst=20 nodelay;
    }

    # Admin
    location /admin {
        proxy_pass http://127.0.0.1:8000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_buffering off;
    }

    # API
    location /api/ {
        proxy_pass http://127.0.0.1:8000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        limit_req zone=api burst=20 nodelay;
    }

    # Frontend (KEIN nginx-Cache, macht Cloudflare)
    location / {
        proxy_pass http://127.0.0.1:8000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

12.2 gunicorn: /opt/bsn-chatbot/gunicorn.conf.py

bind = "127.0.0.1:8000"
workers = 4
threads = 2
worker_class = "gthread"
timeout = 120
graceful_timeout = 30
keepalive = 5
user = "bsn"
group = "bsn"
accesslog = "/opt/bsn-chatbot/logs/gunicorn-access.log"
errorlog = "/opt/bsn-chatbot/logs/gunicorn-error.log"
loglevel = "info"
proc_name = "bsn-chatbot"
max_requests = 1000
max_requests_jitter = 100

13.1 bsn-chatbot.service (gunicorn)

[Unit]
Description=BSN Chatbot — gunicorn WSGI
After=network.target

[Service]
Type=notify
User=bsn
Group=bsn
WorkingDirectory=/opt/bsn-chatbot/src
EnvironmentFile=/opt/bsn-chatbot/src/.env
ExecStart=/opt/bsn-chatbot/venv/bin/gunicorn --config /opt/bsn-chatbot/gunicorn.conf.py app:app
ExecReload=/bin/kill -s HUP $MAINPID
Restart=always
RestartSec=5
PrivateTmp=true
NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/opt/bsn-chatbot/data /opt/bsn-chatbot/logs /opt/bsn-chatbot/backups
ReadOnlyPaths=/opt/bsn-chatbot/src

[Install]
WantedBy=multi-user.target

13.2 bsn-worker.service (Queue)

[Unit]
Description=BSN Queue Worker — Submission-Verarbeitung
After=bsn-chatbot.service
Wants=bsn-chatbot.service

[Service]
Type=simple
User=bsn
Group=bsn
WorkingDirectory=/opt/bsn-chatbot/src
EnvironmentFile=/opt/bsn-chatbot/src/.env
ExecStart=/opt/bsn-chatbot/venv/bin/python worker.py
Restart=always
RestartSec=10
PrivateTmp=true
NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/opt/bsn-chatbot/data /opt/bsn-chatbot/logs
ReadOnlyPaths=/opt/bsn-chatbot/src

[Install]
WantedBy=multi-user.target

13.3 worker.py (neue Datei)

#!/usr/bin/env python3
"""Standalone Queue-Worker — eigener Prozess, nicht unter gunicorn."""
import os, sys, time, json, sqlite3
sys.path.insert(0, '/opt/bsn-chatbot/src')

from dotenv import load_dotenv
load_dotenv()

DB = os.environ['DATABASE_PATH']
POLL = int(os.environ.get('QUEUE_POLL_INTERVAL', 2))
BATCH = int(os.environ.get('QUEUE_BATCH_SIZE', 3))

# Whisper 1× laden (kein Recycling mitten in der Verarbeitung)
from faster_whisper import WhisperModel
model = WhisperModel("base", device="cpu", compute_type="int8")

def get_db():
    db = sqlite3.connect(DB)
    db.row_factory = sqlite3.Row
    db.execute("PRAGMA journal_mode=WAL")
    db.execute("PRAGMA busy_timeout=5000")
    return db

def process_one(db, row):
    # → Media-Download, Transkription, LLM-Triage, DB-Insert
    # (Implementierung aus queue_worker.py übernehmen)
    pass

def main():
    print("[worker] Gestartet, Whisper-Modell geladen.", flush=True)
    db = get_db()
    while True:
        try:
            rows = db.execute(
                "SELECT * FROM submission_queue WHERE status='pending' ORDER BY created_at LIMIT ?",
                (BATCH,)).fetchall()
            for row in rows:
                db.execute("UPDATE submission_queue SET status='processing' WHERE id=?", (row['id'],))
                db.commit()
                try:
                    process_one(db, row)
                    db.execute("UPDATE submission_queue SET status='completed' WHERE id=?", (row['id'],))
                except Exception as e:
                    db.execute(
                        "UPDATE submission_queue SET status='failed', last_error=?, attempt_count=attempt_count+1 WHERE id=?",
                        (str(e)[:500], row['id']))
                db.commit()
        except Exception as e:
            print(f"[worker] Fehler: {e}", flush=True)
        time.sleep(POLL)

if __name__ == '__main__':
    main()

13.4 Aktivierung

systemctl daemon-reload
systemctl enable --now bsn-chatbot bsn-worker nginx
# cloudflared ist bereits aktiv (Schritt 10)

/health (abgespeckt)

GET /health → {"status":"ok","db":"bsn_intake.db","whatsapp_configured":true}

Watchdog (Hermes-Server)

# Cronjob alle 5min:
curl -s -o /dev/null -w "%{http_code}" https://chat.datenhimmel.work/health | grep -q 200
# Bei Fehler → Telegram an Daniel

16.1 Backup-Script (/opt/bsn-chatbot/backup.sh)

#!/bin/bash
set -e
BACKUP_DIR=/opt/bsn-chatbot/backups
HERMES="hermes@185.162.249.159:/home/hermes/backups/bsn-chatbot"
DATE=$(date +%Y%m%d-%H%M)

mkdir -p $BACKUP_DIR

# Lokales DB-Backup
sqlite3 /opt/bsn-chatbot/data/bsn_intake.db \
    ".backup $BACKUP_DIR/bsn_intake_$DATE.db"

# Offsite (zum Hermes-Server)
rsync -avz --delete $BACKUP_DIR/ $HERMES/db/
rsync -avz /opt/bsn-chatbot/data/media/ $HERMES/media/

# Rotation: 72 lokale Backups behalten
ls -t $BACKUP_DIR/bsn_intake_*.db | tail -n +73 | xargs rm -f

16.2 Cron für Backup

# /etc/cron.d/bsn-backup
0 * * * * bsn /opt/bsn-chatbot/backup.sh

16.3 Recovery

LAST=$(ls -t /opt/bsn-chatbot/backups/bsn_intake_*.db | head -1)
systemctl stop bsn-chatbot bsn-worker
cp $LAST /opt/bsn-chatbot/data/bsn_intake.db
systemctl start bsn-chatbot bsn-worker

17.1 WhatsApp: „Meine Daten löschen"

# Bereits implementiert in app.py:
# Nutzer sendet "Meine Daten löschen" → alle Submissions dieser Nummer:
# UPDATE submissions SET deleted_at=CURRENT_TIMESTAMP, status='gdpr_deleted'
# Medien von Platte löschen
# Auto-Reply: Bestätigung

17.2 Web: Referenz-ID

# Bereits implementiert:
# Nach Web-Submit: Referenz-ID anzeigen + Kopier-Button
# Löschung aktuell via Kontaktaufnahme Redaktion
# ⚠️ Selbstbedienungs-Löschformular: jetzt in v1 umsetzen
# → /loeschen Route: ID eingeben → DELETE

17.3 Löschroutine (Cron)

# /opt/bsn-chatbot/purge.py (täglich via Cron):
# SELECT * FROM submissions WHERE deleted_at IS NOT NULL
#   AND deleted_at < date('now', '-30 days')
# → Medien von Platte löschen, DB-Eintrag hart löschen
# → DSGVO-Vermerk bleibt in separater Tabelle

1. Ketchup cloudflared STOPPEN: systemctl stop cloudflared
2. Dogado cloudflared STARTEN: systemctl start cloudflared
3. DNS prüfen: CNAME chat.datenhimmel.work → Tunnel-ID (Cloudflare Dashboard)
4. Warten: 2-5 Min DNS-Propagation
5. Verifikation: curl https://chat.datenhimmel.work/health → 200
6. 10-Punkte-Checkliste durchgehen
7. 72h warten bevor alter Server endgültig abgeschaltet wird

Rollback (innerhalb 72h)

# Auf Ketchup:
systemctl start cloudflared
# Auf dogado:
systemctl stop cloudflared
# Fertig. Cloudflare sieht den aktiven Connector auf Ketchup.

Nach 72 Stunden

# Auf Ketchup:
systemctl stop bsn-chatbot
rm ~/.cloudflared/<TUNNEL_ID>.json   # Credentials entfernen

Gesamt: ~2,5 Stunden

Server (12 GB, 6 Cores, NVMe) ist für 1000/h komfortabel überdimensioniert.

Ende des Plans v5.1
Cody (Hermes Agent) + externes Review — 14.07.2026
Status: ✅ Umsetzungsreif