customer-installer/STEP1_BACKEND_API_SUMMARY.md

Schritt 1: Backend-API für Installer-JSON - ABGESCHLOSSEN

Zusammenfassung

Backend-API wurde erfolgreich erstellt, die das Installer-JSON sicher (ohne Secrets) für Frontend-Clients bereitstellt.

---

Erstellte Dateien

1. SQL-Schema: sql/add_installer_json_api.sql

Funktionen:

• Erweitert instances Tabelle um installer_json JSONB-Spalte
• Erstellt api.instance_config View (filtert Secrets automatisch)
• Implementiert Row Level Security (RLS)
• Bietet 5 API-Funktionen:
  • get_public_config() - Öffentliche Konfiguration
  • get_instance_config_by_email(email) - Instanz-Config per E-Mail
  • get_instance_config_by_ctid(ctid) - Instanz-Config per CTID (service_role only)
  • store_installer_json(email, ctid, json) - Speichert Installer-JSON (service_role only)
  • log_config_access(customer_id, type, ip) - Audit-Logging

Sicherheit:

• ✅ Filtert automatisch alle Secrets (postgres.password, service_role_key, jwt_secret, etc.)
• ✅ Row Level Security aktiviert
• ✅ Audit-Logging für alle Zugriffe

---

2. API-Dokumentation: API_DOCUMENTATION.md

Inhalt:

• Vollständige API-Referenz
• Alle Endpunkte mit Beispielen
• Authentifizierungs-Modelle
• CORS-Konfiguration
• Rate-Limiting-Empfehlungen
• Fehlerbehandlung
• Integration mit install.sh
• Test-Szenarien

---

3. Integration-Library: lib_installer_json_api.sh

Funktionen:

• store_installer_json_in_db() - Speichert JSON in DB
• get_installer_json_by_email() - Ruft JSON per E-Mail ab
• get_installer_json_by_ctid() - Ruft JSON per CTID ab
• get_public_config() - Ruft öffentliche Config ab
• apply_installer_json_api_schema() - Wendet SQL-Schema an
• test_api_connectivity() - Testet API-Verbindung
• verify_installer_json_stored() - Verifiziert Speicherung

---

4. Test-Script: test_installer_json_api.sh

Tests:

• API-Konnektivität
• Public Config Endpoint
• Instance Config by Email
• Instance Config by CTID
• Store Installer JSON
• CORS Headers
• Response Format Validation
• Security: Verifiziert, dass keine Secrets exposed werden

Usage:

# Basis-Tests (öffentliche Endpunkte)
bash test_installer_json_api.sh

# Vollständige Tests (mit Service Role Key)
bash test_installer_json_api.sh --service-role-key "eyJhbGc..."

# Spezifische Instanz testen
bash test_installer_json_api.sh \
  --ctid 769697636 \
  --email max@beispiel.de \
  --postgrest-url http://192.168.45.104:3000

---

API-Routen (PostgREST)

1. Public Config (Keine Auth)

URL: POST /rpc/get_public_config

Request:

curl -X POST 'http://192.168.45.104:3000/rpc/get_public_config' \
  -H "Content-Type: application/json" \
  -d '{}'

Response:

{
  "registration_webhook_url": "https://api.botkonzept.de/webhook/botkonzept-registration",
  "api_base_url": "https://api.botkonzept.de"
}

---

2. Instance Config by Email (Öffentlich)

URL: POST /rpc/get_instance_config_by_email

Request:

curl -X POST 'http://192.168.45.104:3000/rpc/get_instance_config_by_email' \
  -H "Content-Type: application/json" \
  -d '{"customer_email_param": "max@beispiel.de"}'

Response:

[
  {
    "id": "550e8400-e29b-41d4-a716-446655440000",
    "customer_id": "123e4567-e89b-12d3-a456-426614174000",
    "ctid": 769697636,
    "hostname": "sb-1769697636",
    "fqdn": "sb-1769697636.userman.de",
    "ip": "192.168.45.104",
    "vlan": 90,
    "status": "active",
    "created_at": "2025-01-15T10:30:00Z",
    "urls": {
      "n8n_internal": "http://192.168.45.104:5678/",
      "n8n_external": "https://sb-1769697636.userman.de",
      "postgrest": "http://192.168.45.104:3000",
      "chat_webhook": "https://sb-1769697636.userman.de/webhook/rag-chat-webhook/chat",
      "chat_internal": "http://192.168.45.104:5678/webhook/rag-chat-webhook/chat",
      "upload_form": "https://sb-1769697636.userman.de/form/rag-upload-form",
      "upload_form_internal": "http://192.168.45.104:5678/form/rag-upload-form"
    },
    "supabase": {
      "url_external": "http://192.168.45.104:3000",
      "anon_key": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
    },
    "ollama": {
      "url": "http://192.168.45.3:11434",
      "model": "ministral-3:3b",
      "embedding_model": "nomic-embed-text:latest"
    },
    "customer_email": "max@beispiel.de",
    "first_name": "Max",
    "last_name": "Mustermann",
    "company": "Muster GmbH",
    "customer_status": "trial"
  }
]

Wichtig: Keine Secrets (passwords, service_role_key, jwt_secret) im Response!

---

3. Store Installer JSON (Service Role Only)

URL: POST /rpc/store_installer_json

Request:

curl -X POST 'http://192.168.45.104:3000/rpc/store_installer_json' \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer <SERVICE_ROLE_KEY>" \
  -d '{
    "customer_email_param": "max@beispiel.de",
    "lxc_id_param": 769697636,
    "installer_json_param": {...}
  }'

Response:

{
  "success": true,
  "instance_id": "550e8400-e29b-41d4-a716-446655440000",
  "customer_id": "123e4567-e89b-12d3-a456-426614174000",
  "message": "Installer JSON stored successfully"
}

---

Sicherheits-Whitelist

✅ Erlaubt (Frontend-sicher)

{
  "ctid": 769697636,
  "hostname": "sb-1769697636",
  "fqdn": "sb-1769697636.userman.de",
  "ip": "192.168.45.104",
  "vlan": 90,
  "urls": {
    "n8n_internal": "http://192.168.45.104:5678/",
    "n8n_external": "https://sb-1769697636.userman.de",
    "postgrest": "http://192.168.45.104:3000",
    "chat_webhook": "https://sb-1769697636.userman.de/webhook/rag-chat-webhook/chat",
    "chat_internal": "http://192.168.45.104:5678/webhook/rag-chat-webhook/chat",
    "upload_form": "https://sb-1769697636.userman.de/form/rag-upload-form",
    "upload_form_internal": "http://192.168.45.104:5678/form/rag-upload-form"
  },
  "supabase": {
    "url_external": "http://192.168.45.104:3000",
    "anon_key": "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."
  },
  "ollama": {
    "url": "http://192.168.45.3:11434",
    "model": "ministral-3:3b",
    "embedding_model": "nomic-embed-text:latest"
  }
}

❌ Verboten (Secrets)

{
  "postgres": {
    "password": "NEVER_EXPOSE"
  },
  "supabase": {
    "service_role_key": "NEVER_EXPOSE",
    "jwt_secret": "NEVER_EXPOSE"
  },
  "n8n": {
    "owner_password": "NEVER_EXPOSE",
    "encryption_key": "NEVER_EXPOSE"
  }
}

---

Authentifizierung

1. Keine Authentifizierung (Public)

• /rpc/get_public_config
• /rpc/get_instance_config_by_email

Empfehlung: Rate Limiting aktivieren!

2. Service Role Key (Backend-to-Backend)

Header:

Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoic2VydmljZV9yb2xlIiwiaXNzIjoic3VwYWJhc2UiLCJpYXQiOjE3MDAwMDAwMDAsImV4cCI6MjAwMDAwMDAwMH0...

Verwendung:

• /rpc/get_instance_config_by_ctid
• /rpc/store_installer_json

---

Deployment-Schritte

Schritt 1: SQL-Schema anwenden

# Auf bestehendem Container
CTID=769697636

pct exec ${CTID} -- bash -c "
  docker exec customer-postgres psql -U customer -d customer < /opt/customer-stack/sql/add_installer_json_api.sql
"

Schritt 2: Test ausführen

# Basis-Test
bash customer-installer/test_installer_json_api.sh \
  --postgrest-url http://192.168.45.104:3000

# Mit Service Role Key
bash customer-installer/test_installer_json_api.sh \
  --postgrest-url http://192.168.45.104:3000 \
  --service-role-key "eyJhbGc..."

Schritt 3: install.sh erweitern (nächster Schritt)

Am Ende von install.sh hinzufügen:

# Source API library
source "${SCRIPT_DIR}/lib_installer_json_api.sh"

# Apply SQL schema
apply_installer_json_api_schema "${CTID}"

# Store installer JSON in database
store_installer_json_in_db \
  "${CTID}" \
  "${N8N_OWNER_EMAIL}" \
  "${SUPABASE_URL_EXTERNAL}" \
  "${SERVICE_ROLE_KEY}" \
  "${JSON_OUTPUT}"

# Verify storage
verify_installer_json_stored \
  "${CTID}" \
  "${N8N_OWNER_EMAIL}" \
  "${SUPABASE_URL_EXTERNAL}"

---

Curl-Tests

Test 1: Public Config

curl -X POST 'http://192.168.45.104:3000/rpc/get_public_config' \
  -H "Content-Type: application/json" \
  -d '{}'

# Erwartete Antwort:
# {"registration_webhook_url":"https://api.botkonzept.de/webhook/botkonzept-registration","api_base_url":"https://api.botkonzept.de"}

Test 2: Instance Config by Email

curl -X POST 'http://192.168.45.104:3000/rpc/get_instance_config_by_email' \
  -H "Content-Type: application/json" \
  -d '{"customer_email_param": "max@beispiel.de"}'

# Erwartete Antwort: Array mit Instanz-Config (siehe oben)

Test 3: Verify No Secrets

curl -X POST 'http://192.168.45.104:3000/rpc/get_instance_config_by_email' \
  -H "Content-Type: application/json" \
  -d '{"customer_email_param": "max@beispiel.de"}' | jq .

# Prüfe: Response enthält KEINE der folgenden Strings:
# - "password"
# - "service_role_key"
# - "jwt_secret"
# - "encryption_key"
# - "owner_password"

Test 4: Store Installer JSON (mit Service Role Key)

SERVICE_ROLE_KEY="eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9..."

curl -X POST 'http://192.168.45.104:3000/rpc/store_installer_json' \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer ${SERVICE_ROLE_KEY}" \
  -d '{
    "customer_email_param": "max@beispiel.de",
    "lxc_id_param": 769697636,
    "installer_json_param": {
      "ctid": 769697636,
      "urls": {...},
      "postgres": {"password": "secret"},
      "supabase": {"service_role_key": "secret"}
    }
  }'

# Erwartete Antwort:
# {"success":true,"instance_id":"...","customer_id":"...","message":"Installer JSON stored successfully"}

---

Nächste Schritte (Schritt 2)

1. Frontend-Integration:

   • customer-frontend/js/main.js anpassen
   • customer-frontend/js/dashboard.js anpassen
   • Dynamisches Laden der URLs aus API

2. install.sh erweitern:

   • SQL-Schema automatisch anwenden
   • Installer-JSON automatisch speichern
   • Verifizierung nach Speicherung

3. CORS konfigurieren:

   • PostgREST CORS Headers setzen
   • Nginx Reverse Proxy CORS konfigurieren

4. Rate Limiting:

   • Nginx Rate Limiting für öffentliche Endpunkte
   • Oder API Gateway (Kong, Tyk) verwenden

---

Status

✅ Schritt 1 ABGESCHLOSSEN

Erstellt:

• ✅ SQL-Schema mit sicherer API-View
• ✅ API-Dokumentation
• ✅ Integration-Library
• ✅ Test-Script

Bereit für:

• ⏭️ Schritt 2: Frontend-Integration
• ⏭️ Schritt 3: install.sh erweitern
• ⏭️ Schritt 4: E2E-Tests

---

Support

• API-Dokumentation: customer-installer/API_DOCUMENTATION.md
• Test-Script: customer-installer/test_installer_json_api.sh
• Integration-Library: customer-installer/lib_installer_json_api.sh
• SQL-Schema: customer-installer/sql/add_installer_json_api.sql