Datenmodell

Datenmodell

Alle Modelle nutzen UUID-Primärschlüssel. Stand: backend/app/models/.

User

User
├── id (UUID)
├── username, email (unique)
├── password_hash (Argon2)
├── full_name, timezone, language
├── notification_preferences (JSON)
├── ui_preferences (JSON)               # u.a. paperless_url, paperless_token
├── fcm_token (String, optional)        # Firebase Push
├── api_key (String, unique, optional)  # permanenter Service-API-Key
├── is_active, is_verified
├── two_factor_enabled, two_factor_secret
└── created_at, updated_at, last_login_at

Document

Document
├── id (UUID)
├── user_id (FK → User)
├── file_id (FK → File, optional)
├── type (String: invoice, reminder, contract, receipt, tax_document,
│         payslip, insurance, bank_statement, letter, identity_document, other)
├── title (String)
├── doc_metadata (JSON)                 # KI-Analyseergebnis: sender, due_date, amount, ...
├── processing_status (pending, processing, done, failed)
├── confidence_score (Float)
├── extracted_text (Text)               # OCR-Ergebnis
└── uploaded_at, processed_at

File

File
├── id (UUID)
├── user_id (FK → User)
├── path, original_filename, size_bytes, mime_type
├── checksum (SHA256)
├── storage_backend (local, s3)
├── thumbnail_path, extracted_text, ocr_language
└── created_at

Task

Task
├── id (UUID)
├── user_id (FK → User)
├── document_id (FK → Document, optional)
├── parent_task_id (FK → Task, optional)   # Subtasks
├── title, description
├── due_date, estimated_duration_minutes
├── status (open, in_progress, done, cancelled)
├── priority (low, medium, high, critical)
├── amount (Numeric), currency (Default EUR)
└── created_at, updated_at, completed_at

Relationen: reminders (1:n, cascade delete), calendar_event (1:1, cascade delete)

Reminder

Reminder
├── id (UUID)
├── task_id (FK → Task)
├── trigger_at (DateTime)
├── severity (String)                   # INFO, WARNING, URGENT, CRITICAL
├── channels (JSON)                     # z.B. ["push"]
├── status (pending, sent, failed, ...)
├── sent_at, error_message
├── acknowledged_at, snoozed_until
└── created_at

Siehe Reminder Engine für die tatsächlichen Eskalationsstufen.

CalendarEvent

CalendarEvent
├── id (UUID)
├── user_id (FK → User)
├── task_id (FK → Task, optional)
├── title, description, location
├── start_time, end_time, all_day
├── external_event_id (String)          # ID im externen Kalender (z.B. Google)
├── external_calendar_id (FK → Integration, optional)
├── sync_status (Enum: pending, synced, conflict, error, ...)
├── last_synced_at, conflict_data (JSON)
└── created_at, updated_at

Integration

Integration
├── id (UUID)
├── user_id (FK → User)
├── name (String)
├── integration_type (Enum: google_calendar, caldav, paperless, ...)
├── enabled (Boolean)
├── config (JSON)                       # URL, Kalender-ID etc.
├── credentials (JSON)                  # OAuth-Tokens / Passwörter
├── sync_direction (Enum: bidirectional, push_only, pull_only)
├── auto_sync (Boolean), sync_interval_minutes (Default 15)
├── sync_status (idle, syncing, success, error)
├── last_sync_at, error_log
└── created_at, updated_at

Session

Session
├── id (UUID)
├── user_id (FK → User)
├── token_hash, refresh_token_hash
├── device_type (web, android, ios), device_name, os, user_agent
├── ip_address, city, country
├── is_suspicious (Boolean)
├── expires_at, last_active
└── created_at

---

← Tech Stack | → Komponenten