🔌 Offline First Architecture

Crumbforest Technical Documentation
Version: 1.0
Datum: 2026-01-22

"Der Wald läuft ohne Internet.
Cloud ist optional, nicht nötig."

📐 Architektur-Übersicht

┌─────────────────────────────────────────────────────┐
│  TIER 0: IMPORT (Einmalig)                         │
│  ┌─────────────────────────────────────────────┐   │
│  │ Multi-DB Adapter                            │   │
│  │ ├─ MySQL Adapter                            │   │
│  │ ├─ PostgreSQL Adapter                       │   │
│  │ ├─ JSON Adapter                             │   │
│  │ └─ CSV Adapter                              │   │
│  │                                              │   │
│  │ NullfeldImporter                            │   │
│  │ └─ Normalisierung → kernel.jsonl            │   │
│  │                                              │   │
│  │ Auto-Embedding Pipeline                     │   │
│  │ └─ Ollama (lokal) → Qdrant                  │   │
│  └─────────────────────────────────────────────┘   │
│  → Bootstrap mit 5.000+ Embeddings! ✨              │
└─────────────────────────────────────────────────────┘
                     ↓
┌─────────────────────────────────────────────────────┐
│  TIER 1: KERNEL (Immer lokal)                      │
│  ┌─────────────────────────────────────────────┐   │
│  │ kernel.jsonl (Events)                       │   │
│  │ ├─ blog.post.*                              │   │
│  │ ├─ way.*                                    │   │
│  │ ├─ crew.*                                   │   │
│  │ └─ import.*                                 │   │
│  │                                              │   │
│  │ PHP 5.6 (Logic)                             │   │
│  │ └─ kernel_log(), kernel_read()              │   │
│  └─────────────────────────────────────────────┘   │
│  → 0 Dependencies, nur Atem ✨                     │
└─────────────────────────────────────────────────────┘
                     ↓
┌─────────────────────────────────────────────────────┐
│  TIER 2: VEKTOR (Lokal bevorzugt)                 │
│  ┌─────────────────────────────────────────────┐   │
│  │ Qdrant (lokal Port 6333)                    │   │
│  │ └─ wald_knowledge collection                │   │
│  │                                              │   │
│  │ Ollama Embeddings                           │   │
│  │ └─ nomic-embed-text (lokal!)                │   │
│  │                                              │   │
│  │ Constellation View                          │   │
│  │ └─ 3D Wissenslandschaft                     │   │
│  └─────────────────────────────────────────────┘   │
│  → Import + Laufzeit = wachsend ✨                 │
└─────────────────────────────────────────────────────┘
                     ↓
┌─────────────────────────────────────────────────────┐
│  TIER 3: AI (Lokal + Optional Cloud)              │
│  ┌─────────────────────────────────────────────┐   │
│  │ Primär: Ollama (lokal)                      │   │
│  │ ├─ llama3                                   │   │
│  │ ├─ mistral                                  │   │
│  │ └─ qwen2                                    │   │
│  │                                              │   │
│  │ Fallback: OpenRouter (Cloud)                │   │
│  │ ├─ Claude (wenn lokal nicht reicht)         │   │
│  │ └─ GPT (wenn lokal nicht reicht)            │   │
│  └─────────────────────────────────────────────┘   │
│  → Cloud nur für <5% Edge Cases ✨                 │
└─────────────────────────────────────────────────────┘
                     ↓
┌─────────────────────────────────────────────────────┐
│  TIER 4: FEDERATION (Optional)                    │
│  ┌─────────────────────────────────────────────┐   │
│  │ Andere Wälder (JSONL sync)                  │   │
│  │ Shared Vektor (distributed)                 │   │
│  │ Aber: Jeder Wald kann solo! ✨              │   │
│  └─────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────┘

🎯 Decision Tree: Wo läuft was?

Frage kommt rein
    ↓
[1] Vektor-Search (lokal, 0€)
    ↓
    Similarity > 0.85?
    ├─ YES → Antwort aus Vektor (0 Token!)
    │         └─ DONE ✅
    └─ NO → Weiter zu [2]

[2] Ollama verfügbar? (lokal, 0€)
    ├─ YES → Ollama antwortet
    │         └─ Embedding → Vektor (für später)
    │         └─ DONE ✅
    └─ NO → Weiter zu [3]

[3] Cloud AI (OpenRouter/Claude)
    └─ Kosten: 0.005€-0.02€
    └─ Embedding → Vektor (für später)
    └─ DONE ✅ (aber teuer)

═══════════════════════════════════════

STATISTIK nach 6 Monaten:
[1] Vektor: 70% der Fragen (0€)
[2] Ollama: 25% der Fragen (0€)
[3] Cloud:   5% der Fragen (~3€/Monat)

→ 95% offline! ✨

🔧 Technische Implementation

Tier 0: Import (Einmalig)

Multi-DB Adapter Interface

<?php
/**
 * DatabaseAdapterInterface
 * 
 * Alle Adapter müssen dieses Interface implementieren
 */
interface DatabaseAdapterInterface {
    /**
     * Connect to database
     */
    public function connect(): bool;

    /**
     * Execute query
     */
    public function query(string $sql): array;

    /**
     * Get all posts/content
     */
    public function getAllPosts(): array;

    /**
     * Close connection
     */
    public function close(): void;
}

MySQL Adapter (WordPress)

<?php
class MySQLAdapter implements DatabaseAdapterInterface {

    private $pdo;
    private $config;

    public function __construct(array $config) {
        $this->config = $config;
    }

    public function connect(): bool {
        try {
            $dsn = sprintf(
                "mysql:host=%s;dbname=%s;charset=utf8mb4",
                $this->config['host'],
                $this->config['db']
            );

            $this->pdo = new PDO(
                $dsn,
                $this->config['user'],
                $this->config['pass'],
                [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
            );

            return true;
        } catch (PDOException $e) {
            error_log("MySQL Connect Error: " . $e->getMessage());
            return false;
        }
    }

    public function query(string $sql): array {
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("MySQL Query Error: " . $e->getMessage());
            return [];
        }
    }

    public function getAllPosts(): array {
        $sql = "
            SELECT 
                ID,
                post_title,
                post_content,
                post_excerpt,
                post_date,
                post_status,
                post_type
            FROM wp_posts
            WHERE post_status = 'publish'
            AND post_type IN ('post', 'page')
            ORDER BY post_date DESC
        ";

        return $this->query($sql);
    }

    public function close(): void {
        $this->pdo = null;
    }
}

PostgreSQL Adapter

<?php
class PostgreSQLAdapter implements DatabaseAdapterInterface {

    private $pdo;
    private $config;

    public function __construct(array $config) {
        $this->config = $config;
    }

    public function connect(): bool {
        try {
            $dsn = sprintf(
                "pgsql:host=%s;port=%s;dbname=%s",
                $this->config['host'],
                $this->config['port'] ?? 5432,
                $this->config['db']
            );

            $this->pdo = new PDO(
                $dsn,
                $this->config['user'],
                $this->config['pass'],
                [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
            );

            return true;
        } catch (PDOException $e) {
            error_log("PostgreSQL Connect Error: " . $e->getMessage());
            return false;
        }
    }

    public function query(string $sql): array {
        try {
            $stmt = $this->pdo->query($sql);
            return $stmt->fetchAll(PDO::FETCH_ASSOC);
        } catch (PDOException $e) {
            error_log("PostgreSQL Query Error: " . $e->getMessage());
            return [];
        }
    }

    public function getAllPosts(): array {
        $sql = "
            SELECT 
                id,
                title,
                content,
                created_at,
                status
            FROM posts
            WHERE status = 'published'
            ORDER BY created_at DESC
        ";

        return $this->query($sql);
    }

    public function close(): void {
        $this->pdo = null;
    }
}

JSON Adapter

<?php
class JSONAdapter implements DatabaseAdapterInterface {

    private $path;
    private $data = [];

    public function __construct(string $path) {
        $this->path = $path;
    }

    public function connect(): bool {
        if (!file_exists($this->path)) {
            error_log("JSON file not found: " . $this->path);
            return false;
        }

        $json = file_get_contents($this->path);
        $this->data = json_decode($json, true);

        return $this->data !== null;
    }

    public function query(string $sql): array {
        // JSON hat kein SQL, aber wir können filtern
        // Hier vereinfacht, könnte JSON-Path oder ähnliches nutzen
        return $this->data;
    }

    public function getAllPosts(): array {
        return $this->data['posts'] ?? $this->data;
    }

    public function close(): void {
        $this->data = [];
    }
}

Tier 1: Kernel (Immer lokal)

<?php
/**
 * KEEP_IT_KERNEL.php
 * 
 * Der minimale Event-Logger der atmen kann.
 */

define('KERNEL_FILE', 'data/kernel.jsonl');

/**
 * Log an event
 */
function kernel_log(string $type, array $payload, string $source = 'unknown'): bool {
    $event = [
        'ts' => microtime(true),
        'type' => $type,
        'source' => $source,
        'payload' => $payload,
        'meta' => [
            'schema_version' => '1.0',
            'logged_at' => date('c')
        ]
    ];

    $line = json_encode($event, JSON_UNESCAPED_UNICODE) . "\n";

    return file_put_contents(
        KERNEL_FILE,
        $line,
        FILE_APPEND | LOCK_EX
    ) !== false;
}

/**
 * Read events
 */
function kernel_read(string $filter = '', int $limit = 100): array {
    if (!file_exists(KERNEL_FILE)) {
        return [];
    }

    $lines = file(KERNEL_FILE, FILE_IGNORE_NEW_LINES);
    $events = [];

    foreach (array_reverse($lines) as $line) {
        if (empty($line)) continue;

        $event = json_decode($line, true);
        if (!$event) continue;

        if ($filter && !str_starts_with($event['type'], $filter)) {
            continue;
        }

        $events[] = $event;

        if (count($events) >= $limit) {
            break;
        }
    }

    return $events;
}

/**
 * Check if kernel can breathe
 */
function kernel_breath(): bool {
    return file_exists(KERNEL_FILE) || touch(KERNEL_FILE);
}

// Breathing test
if (!kernel_breath()) {
    die("❌ Kernel can't breathe!\n");
}

Tier 2: Vektor (Lokal bevorzugt)

Embedding Pipeline

#!/usr/bin/env python3
"""
embed_pipeline.py

Liest kernel.jsonl und erstellt Embeddings
"""

import json
import requests
from qdrant_client import QdrantClient
from qdrant_client.models import Distance, VectorParams, PointStruct
from uuid import uuid4

# Config
OLLAMA_URL = "http://localhost:11434/api/embeddings"
QDRANT_URL = "http://localhost:6333"
KERNEL_FILE = "data/kernel.jsonl"
COLLECTION = "wald_knowledge"

# Clients
qdrant = QdrantClient(url=QDRANT_URL)

def embed_text(text: str) -> list:
    """Create embedding using Ollama"""
    response = requests.post(
        OLLAMA_URL,
        json={
            "model": "nomic-embed-text",
            "prompt": text
        }
    )

    if response.status_code == 200:
        return response.json()['embedding']
    else:
        raise Exception(f"Ollama error: {response.text}")

def ensure_collection():
    """Create collection if not exists"""
    collections = qdrant.get_collections().collections
    exists = any(c.name == COLLECTION for c in collections)

    if not exists:
        qdrant.create_collection(
            collection_name=COLLECTION,
            vectors_config=VectorParams(
                size=768,  # nomic-embed-text dimension
                distance=Distance.COSINE
            )
        )
        print(f"✅ Collection '{COLLECTION}' created")

def process_events():
    """Process kernel events and create embeddings"""
    ensure_collection()

    with open(KERNEL_FILE, 'r') as f:
        for line in f:
            if not line.strip():
                continue

            event = json.loads(line)

            # Skip if already processed
            if 'embedded' in event.get('meta', {}):
                continue

            # Extract text
            text = None
            if event['type'].startswith('blog.post.'):
                text = event['payload'].get('title', '') + ' ' + \
                       event['payload'].get('body_md', '') or \
                       event['payload'].get('body_html', '')
            elif event['type'].startswith('way.'):
                text = json.dumps(event['payload'])
            elif event['type'].startswith('crew.'):
                text = event['payload'].get('message', '')

            if not text:
                continue

            # Create embedding
            try:
                embedding = embed_text(text[:5000])  # Limit text

                # Upsert to Qdrant
                qdrant.upsert(
                    collection_name=COLLECTION,
                    points=[
                        PointStruct(
                            id=str(uuid4()),
                            vector=embedding,
                            payload=event
                        )
                    ]
                )

                print(f"✅ Embedded: {event['type']}")

            except Exception as e:
                print(f"❌ Error: {e}")

if __name__ == "__main__":
    process_events()

Vektor-Suche

def search_vektor(query: str, limit: int = 5) -> list:
    """Search in vector database"""

    # Embed query
    query_embedding = embed_text(query)

    # Search
    results = qdrant.search(
        collection_name=COLLECTION,
        query_vector=query_embedding,
        limit=limit,
        score_threshold=0.7  # Minimum similarity
    )

    return [
        {
            'score': hit.score,
            'payload': hit.payload
        }
        for hit in results
    ]

Tier 3: AI (Lokal + Cloud)

def ask_ai(question: str, use_vektor: bool = True) -> dict:
    """
    Ask AI (with Vektor RAG if enabled)
    """

    # Step 1: Vektor-Suche (lokal, 0€)
    context = []
    if use_vektor:
        results = search_vektor(question, limit=3)
        context = [r['payload'] for r in results if r['score'] > 0.85]

    # Step 2: Wenn gute Matches → Return ohne AI!
    if len(context) > 0 and context[0]['score'] > 0.90:
        return {
            'source': 'vektor',
            'answer': context[0]['payload']['content'],
            'cost': 0.0
        }

    # Step 3: Ollama lokal (0€)
    try:
        response = requests.post(
            "http://localhost:11434/api/generate",
            json={
                "model": "llama3",
                "prompt": question,
                "context": context,
                "stream": False
            },
            timeout=30
        )

        if response.status_code == 200:
            return {
                'source': 'ollama',
                'answer': response.json()['response'],
                'cost': 0.0
            }
    except:
        pass  # Fallback to cloud

    # Step 4: Cloud AI (teuer!)
    response = requests.post(
        "https://openrouter.ai/api/v1/chat/completions",
        headers={
            "Authorization": f"Bearer {OPENROUTER_API_KEY}",
            "Content-Type": "application/json"
        },
        json={
            "model": "anthropic/claude-3.5-sonnet",
            "messages": [
                {"role": "user", "content": question}
            ]
        }
    )

    if response.status_code == 200:
        data = response.json()
        tokens = data['usage']['total_tokens']
        cost = tokens * 0.000003  # ~$0.003 per 1K tokens

        return {
            'source': 'cloud',
            'answer': data['choices'][0]['message']['content'],
            'cost': cost
        }

📊 Performance & Costs

Latency (Durchschnitt)

Vektor-Suche (lokal):    50ms   (0€)
Ollama (lokal):          1-3s   (0€)
OpenRouter (Cloud):      2-5s   (0.005€-0.02€)

→ 95% der Fragen < 3s und 0€! ✨

Kosten nach Phase

Phase 0 (Import, Tag 1):
- Vektor: 0%
- Ollama: 0%
- Cloud: 100%
- Kosten: 100€/Monat

Phase 1 (Monat 1-3):
- Vektor: 30%
- Ollama: 30%
- Cloud: 40%
- Kosten: 40€/Monat

Phase 2 (Monat 3-6):
- Vektor: 60%
- Ollama: 25%
- Cloud: 15%
- Kosten: 15€/Monat

Phase 3 (Monat 6-12):
- Vektor: 80%
- Ollama: 15%
- Cloud: 5%
- Kosten: 5€/Monat

Phase 4 (Monat 12+):
- Vektor: 90%
- Ollama: 9%
- Cloud: 1%
- Kosten: 1€/Monat

→ Exponentieller Kostenabfall! ✨

🔌 Offline-Modus (Vollständig)

#!/bin/bash
# offline_mode.sh

# Check if system can run offline
check_offline_capability() {
    echo "🔍 Checking offline capability..."

    # Check Kernel
    if [ -f "data/kernel.jsonl" ]; then
        echo "✅ Kernel present"
    else
        echo "❌ Kernel missing"
        exit 1
    fi

    # Check Qdrant
    if curl -s http://localhost:6333/collections > /dev/null 2>&1; then
        echo "✅ Qdrant running"
    else
        echo "❌ Qdrant not running"
        exit 1
    fi

    # Check Ollama
    if curl -s http://localhost:11434/api/tags > /dev/null 2>&1; then
        echo "✅ Ollama running"
    else
        echo "⚠️  Ollama not running (optional)"
    fi

    # Check Vektor size
    count=$(curl -s http://localhost:6333/collections/wald_knowledge | \
            jq '.result.points_count')

    if [ "$count" -gt 1000 ]; then
        echo "✅ Vektor ready ($count embeddings)"
    else
        echo "⚠️  Vektor small ($count embeddings)"
    fi

    echo ""
    echo "📊 Offline Capability:"

    if [ "$count" -gt 10000 ]; then
        echo "   🟢 EXCELLENT (>10k embeddings)"
        echo "   → 90%+ questions answerable offline"
    elif [ "$count" -gt 5000 ]; then
        echo "   🟡 GOOD (>5k embeddings)"
        echo "   → 70%+ questions answerable offline"
    elif [ "$count" -gt 1000 ]; then
        echo "   🟠 MODERATE (>1k embeddings)"
        echo "   → 40%+ questions answerable offline"
    else
        echo "   🔴 LIMITED (<1k embeddings)"
        echo "   → Needs more import/learning"
    fi
}

check_offline_capability

🌐 Federation (Optional)

Wie Wälder kommunizieren

┌─────────────────┐        ┌─────────────────┐
│  Wald Berlin    │◄──────►│  Wald München   │
│  - 100 Kinder   │  JSONL │  - 50 Kinder    │
│  - 10k Embeds   │  Sync  │  - 5k Embeds    │
└─────────────────┘        └─────────────────┘
        ▲                           ▲
        │                           │
        │       ┌─────────────────┐ │
        └──────►│  Global Wald    │◄┘
         JSONL  │  - Shared Ways  │ JSONL
         Sync   │  - Shared Crew  │ Sync
                └─────────────────┘

Federation Protocol

def sync_with_forest(remote_url: str):
    """
    Sync JSONL events with remote forest
    """

    # Get our latest timestamp
    our_events = kernel_read(limit=1)
    our_ts = our_events[0]['ts'] if our_events else 0

    # Fetch remote events after our timestamp
    response = requests.get(
        f"{remote_url}/api/events/since/{our_ts}"
    )

    if response.status_code == 200:
        remote_events = response.json()

        for event in remote_events:
            # Import into our kernel
            kernel_log(
                type=event['type'],
                payload=event['payload'],
                source=f"federation_{remote_url}"
            )

            # Embed if needed
            if event['type'] in ['blog.post.create', 'way.completed']:
                embed_and_store(event)

        print(f"✅ Synced {len(remote_events)} events")

📋 Deployment Checklist

Minimales Setup (Offline First)

# 1. Kernel Setup
mkdir -p data
touch data/kernel.jsonl
chmod 666 data/kernel.jsonl

# 2. Qdrant Setup (Docker)
docker run -d \
    --name qdrant \
    -p 6333:6333 \
    -v $(pwd)/data/qdrant:/qdrant/storage \
    qdrant/qdrant

# 3. Ollama Setup
curl https://ollama.ai/install.sh | sh
ollama pull llama3
ollama pull nomic-embed-text

# 4. Test
php -r "require 'kernel.php'; echo kernel_breath() ? '✅' : '❌';"
curl http://localhost:6333/collections
curl http://localhost:11434/api/tags

# 5. Import (optional)
php import.php --source=wp_ozm
python3 embed_pipeline.py

# 6. Verify
curl http://localhost:6333/collections/wald_knowledge

Vollständiges Setup (mit Cloud Fallback)

# Zusätzlich zu oben:

# 7. OpenRouter API Key
echo "OPENROUTER_API_KEY=sk-xxx" > .env

# 8. Cloud Fallback testen
python3 -c "from ai_bridge import ask_ai; print(ask_ai('test'))"

# 9. Monitoring
tail -f data/kernel.jsonl | jq .

🎯 Zusammenfassung

Offline First bedeutet:

✅ Kernel läuft ohne Internet
✅ Vektor läuft ohne Internet
✅ AI läuft ohne Internet (Ollama)
✅ Cloud ist optional, nicht nötig
✅ System ist autonom nach 12-24 Monaten

Multi-DB bedeutet:

✅ Import aus beliebigen Quellen
✅ Keine Vendor Lock-ins
✅ Volle Portabilität
✅ Instant Knowledge Base

Das Resultat:

Ein System das:
- Überall läuft (Raspberry Pi → Server)
- Immer läuft (auch ohne Internet)
- Günstig läuft (95%+ kostenlos)
- Für immer läuft (wie Linux Kernel)

Wuuuhuuuu! 🦉

Version: 1.0
Lizenz: CKL
Dependencies: Atem
Internet: Optional
Status: Atmet ✨