crm-system/architecture.md

LeoCRM — Architecture Document

Scope: This document covers both v1 (Core) and v2 (Plugin) architecture. v1 Features: 73 Core features (F-AUTH, F-COMP, F-CONT, F-CORE, F-DATA, F-UI, F-A11Y, F-SEC, F-INFRA, F-INT, F-MIG, F-NAV, F-SET, F-PLUGIN, F-SCHED, F-SEARCH, F-TEST, F-ENV, F-DOC, F-PERF, F-AI, F-WF) v2 Features: 70 Plugin features (F-CAL, F-DMS, F-FILE, F-FILEUI, F-LINK, F-MAIL, F-PERM, F-TAG) v1 Tasks: T01, T02, T03, T07, T09, T10 v2 Tasks: T04, T05, T06, T11, T08a, T08b, T08c

Projekt: leocrm — Greenfield Architekt: Solution Architect (Agent Zero) Datum: 2026-06-28 Status: Draft — ready for review

---

1. System Architecture

Overview

LeoCRM ist ein Multi-Tenant CRM mit Plugin-basiertem Erweiterungs-System. Die Architektur folgt API-First-Prinzip: alle Features sind primär über REST API nutzbar, die React SPA ist ein API-Client.

High-Level Diagramm

┌───────────────────────────────────────────────────────┐
│                    Coolify (Docker)                     │
├───────────────────────────────────────────────────────┤
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐ │
│  │  React SPA    │  │  FastAPI     │  │  ARQ Worker  │ │
│  │  (Vite build) │  │  Backend     │  │  (async jobs)│ │
│  │  :80          │  │  :8000       │  │  (background)│ │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘ │
│         │                  │                  │         │
│         │   ┌──────────────┼──────────────┐   │         │
│         │   │  PostgreSQL 16  │  Redis   │   │         │
│         │   │  (data + FTS)    │ (cache+  │   │         │
│         │   │  :5432           │  queue)  │   │         │
│         │   └─────────────────┴──────────┘   │         │
│         │                                    │         │
│  ┌──────┴─────────────────────────────────────┴──────┐ │
│  │  OnlyOffice Document Server :8080 (optional)      │ │
│  └───────────────────────────────────────────────────┘ │
│  ┌───────────────────────────────────────────────────┐ │
│  │  Storage Volume (/data/leocrm)                    │ │
│  │  S3-kompatibel oder lokales Volume               │ │
│  └───────────────────────────────────────────────────┘ │
└───────────────────────────────────────────────────────┘

Services (Docker Compose)

Service	Container	Port	Purpose
backend	FastAPI + Uvicorn	8000	REST API, Session-Auth, OpenAPI
frontend	Nginx + React SPA (static)	80	React SPA, served as static files
postgres	PostgreSQL 16	5432	Primary database, Full-Text-Search
redis	Redis 7	6379	Cache, Session-Store, Job-Queue
worker	ARQ Worker (Python)	—	Background jobs (export, mail-sync, reminders)
onlyoffice	OnlyOffice Document Server	8080	Inline Office-Editing (optional)

Backend Architecture

backend/
├── app/
│   ├── main.py                 — FastAPI app, lifespan, middleware
│   ├── config.py               — Pydantic Settings (env vars)
│   ├── deps.py                 — Dependency injection (auth, db, tenant)
│   ├── core/                   — Core infrastructure
│   │   ├── db/                 — SQLAlchemy engine, session, base model
│   │   ├── tenant.py           — Tenant-scoping middleware/query filter
│   │   ├── auth.py             — Session auth, password hashing, RBAC
│   │   ├── event_bus.py        — Event publishing/subscribing
│   │   ├── service_container.py — DI container for services
│   │   ├── storage.py          — File storage backend (S3/local)
│   │   ├── cache.py            — Redis cache wrapper
│   │   ├── jobs.py             — ARQ job queue integration
│   │   ├── notifications.py    — Notification service
│   │   └── audit.py            — Audit log middleware
│   ├── models/                 — SQLAlchemy models (one file per module)
│   ├── schemas/                — Pydantic schemas (one file per module)
│   ├── services/               — Business logic (one file per module)
│   ├── routes/                  — FastAPI routers (one file per module)
│   ├── plugins/                — Plugin system
│   │   ├── registry.py         — Plugin discovery, registration
│   │   ├── manifest.py         — Plugin manifest schema
│   │   ├── lifecycle.py        — Install/activate/deactivate/uninstall
│   │   ├── migrations.py       — Plugin DB migration runner
│   │   ├── ui_registry.py      — Plugin UI component registration
│   │   └── builtins/           — Built-in plugins
│   │       ├── dms/            — DMS plugin
│   │       ├── calendar/       — Calendar plugin
│   │       ├── mail/           — Mail plugin
│   │       └── tags/           — Tags plugin
│   ├── workflows/              — Workflow engine
│   │   ├── engine.py           — Workflow execution engine
│   │   ├── code/               — Code-based core workflows
│   │   └── models.py           — Workflow SQLAlchemy models
│   ├── ai/                     — KI-Copilot integration
│   │   ├── copilot.py          — Copilot query/execute logic
│   │   ├── llm_client.py       — LLM API client (OpenAI/Anthropic)
│   │   └── models.py           — ai_conversations model
│   └── utils/                  — Shared utilities (validation, export, import)
├── tests/                     — pytest + httpx
├── alembic/                    — DB migrations
├── pyproject.toml
└── Dockerfile

Frontend Architecture

frontend/
├── src/
│   ├── main.tsx               — React entry point
│   ├── App.tsx                — Root component, router, providers
│   ├── api/                   — API client (axios/fetch), interceptors
│   ├── components/            — Shared UI components
│   │   ├── layout/            — Shell, Sidebar, TopBar, ContentArea
│   │   ├── ui/               — Button, Input, Select, Modal, Toast, Table
│   │   └── shared/           — EmptyState, LoadingState, ConfirmDialog, Pagination
│   ├── features/              — Feature modules
│   │   ├── auth/             — Login, PasswordReset, UserManagement
│   │   ├── companies/        — CompanyList, CompanyDetail, CompanyForm
│   │   ├── contacts/         — ContactList, ContactDetail, ContactForm
│   │   ├── settings/         — SettingsTree, ProfileSettings, RoleEditor
│   │   ├── audit/            — AuditLog
│   │   ├── dashboard/        — Dashboard
│   │   └── search/           — GlobalSearch
│   ├── plugins/              — Plugin UI loading framework
│   │   ├── PluginRegistry.tsx — Plugin UI component registry
│   │   └── PluginLoader.tsx   — Dynamic plugin component loading
│   ├── hooks/                 — Custom React hooks
│   ├── store/                 — Zustand stores
│   ├── i18n/                  — react-i18next setup + locale files
│   ├── styles/                — Global CSS, design tokens, accessibility
│   └── utils/                  — Utilities (format, validation, export)
├── public/
├── index.html
├── vite.config.ts
├── package.json
├── tsconfig.json
└── Dockerfile

---

2. DB Schema

Design Principles (F-DATA-03, F-DATA-04, F-DATA-06)

v1 Core Features:

• F-DATA-03: Daten-Validierung — Pydantic schemas validate all API inputs
• F-DATA-04: PostgreSQL als Datenbank — PostgreSQL 16 with Row-Level Security for tenant isolation
• F-DATA-06: ARIA-Rollen auf DataTable — frontend tables use ARIA roles for accessibility

• Multi-Tenant: Jede Tabelle hat tenant_id (UUID). ORM filtert automatisch.
• Soft-Delete: deleted_at TIMESTAMP NULL auf Companies, Contacts, Folders, Files.
• Audit Trail: created_at, updated_at, created_by, updated_by auf alle Core-Tabellen.
• UUID Primary Keys: Alle IDs sind UUID (gen_random_uuid() default).
• Timestamps: TIMESTAMPTZ für alle Datumsfelder.

Core Tables

tenants

Column	Type	Constraints
id	UUID	PK, default gen_random_uuid()
name	VARCHAR(200)	NOT NULL
slug	VARCHAR(100)	UNIQUE, NOT NULL
created_at	TIMESTAMPTZ	NOT NULL, default NOW()
updated_at	TIMESTAMPTZ	NOT NULL, default NOW()

users

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id, NOT NULL
email	VARCHAR(255)	NOT NULL
name	VARCHAR(200)	NOT NULL
password_hash	VARCHAR(255)	NOT NULL (bcrypt cost=12)
role	VARCHAR(50)	NOT NULL, default 'viewer'
is_active	BOOLEAN	default true
preferences	JSONB	default '{}'
default_calendar_id	UUID	NULL (v2: FK→calendars.id, added in v2 migration)
default_mail_account_id	UUID	NULL (v2: FK→mail_accounts.id, added in v2 migration)
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL

Unique: (tenant_id, email)

user_tenants (N:M — User kann mehreren Tenant angehören)

Column	Type	Constraints
user_id	UUID	FK→users.id
tenant_id	UUID	FK→tenants.id
is_default	BOOLEAN	default false

PK: (user_id, tenant_id)

roles

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(100)	NOT NULL
permissions	JSONB	NOT NULL (module→action→read/write/delete/admin)
field_permissions	JSONB	default '{}' (field→read/write/hidden)
created_at	TIMESTAMPTZ	NOT NULL

sessions (Audit Trail — primary session store is Redis)

Column	Type	Constraints
id	UUID	PK
user_id	UUID	FK→users.id
tenant_id	UUID	FK→tenants.id (active tenant)
csrf_token	VARCHAR(255)	NOT NULL
expires_at	TIMESTAMPTZ	NOT NULL
created_at	TIMESTAMPTZ	NOT NULL

Note: Session lookup at runtime uses Redis (session:{id} with TTL=8h). This PostgreSQL table is an immutable audit trail of all sessions ever created, used for security analysis and forensic logging. Session invalidation deletes the Redis key; the PostgreSQL record persists.

companies

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id, NOT NULL
name	VARCHAR(100)	NOT NULL
account_number	VARCHAR(40)	NULL
industry	VARCHAR(50)	NULL (picklist)
account_type	VARCHAR(50)	NULL (picklist)
ownership	VARCHAR(50)	NULL (picklist)
employees	INTEGER	NULL
annual_revenue	DECIMAL(15,2)	NULL
phone	VARCHAR(30)	NULL
fax	VARCHAR(30)	NULL
email	VARCHAR(255)	NULL
website	VARCHAR(500)	NULL
rating	VARCHAR(20)	NULL (Hot/Warm/Cold)
parent_account_id	UUID	FK→companies.id NULL (self-ref)
billing_street	VARCHAR(250)	NULL
billing_city	VARCHAR(100)	NULL
billing_state	VARCHAR(100)	NULL
billing_postal_code	VARCHAR(20)	NULL
billing_country	VARCHAR(100)	NULL
shipping_street	VARCHAR(250)	NULL
shipping_city	VARCHAR(100)	NULL
shipping_state	VARCHAR(100)	NULL
shipping_postal_code	VARCHAR(20)	NULL
shipping_country	VARCHAR(100)	NULL
description	TEXT	NULL
sic_code	VARCHAR(10)	NULL
ticker_symbol	VARCHAR(30)	NULL
account_site	VARCHAR(80)	NULL
deleted_at	TIMESTAMPTZ	NULL (soft-delete)
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL
created_by	UUID	FK→users.id
updated_by	UUID	FK→users.id

Index: (tenant_id), (tenant_id, deleted_at), (tenant_id, name), (tenant_id, industry), (tenant_id, billing_country), (tenant_id, rating)

contacts

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id, NOT NULL
first_name	VARCHAR(50)	NULL
last_name	VARCHAR(50)	NOT NULL
salutation	VARCHAR(20)	NULL
email	VARCHAR(255)	NULL
secondary_email	VARCHAR(255)	NULL
phone	VARCHAR(30)	NULL
mobile	VARCHAR(30)	NULL
home_phone	VARCHAR(30)	NULL
fax	VARCHAR(30)	NULL
title	VARCHAR(100)	NULL
department	VARCHAR(100)	NULL
reports_to	UUID	FK→contacts.id NULL
date_of_birth	DATE	NULL
assistant	VARCHAR(50)	NULL
assistant_phone	VARCHAR(30)	NULL
mailing_street	VARCHAR(250)	NULL
mailing_city	VARCHAR(100)	NULL
mailing_state	VARCHAR(100)	NULL
mailing_postal_code	VARCHAR(20)	NULL
mailing_country	VARCHAR(100)	NULL
other_street	VARCHAR(250)	NULL
other_city	VARCHAR(100)	NULL
other_state	VARCHAR(100)	NULL
other_postal_code	VARCHAR(20)	NULL
other_country	VARCHAR(100)	NULL
skype_id	VARCHAR(50)	NULL
linkedin	VARCHAR(500)	NULL
twitter	VARCHAR(50)	NULL
description	TEXT	NULL
deleted_at	TIMESTAMPTZ	NULL (soft-delete)
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL
created_by	UUID	FK→users.id
updated_by	UUID	FK→users.id

Index: (tenant_id, deleted_at), (tenant_id, last_name), (tenant_id, first_name), (tenant_id, email), GIN(tenant_id, to_tsvector('simple', last_name||' '||first_name||' '||email))

company_contacts

Column	Type	Constraints
company_id	UUID	FK→companies.id
contact_id	UUID	FK→contacts.id
tenant_id	UUID	FK→tenants.id (denormalized for filter)
created_at	TIMESTAMPTZ	NOT NULL

PK: (company_id, contact_id) Index: (tenant_id), (contact_id)

audit_log

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
action	VARCHAR(50)	NOT NULL (create/update/delete/gdpr_delete/login)
entity_type	VARCHAR(50)	NOT NULL
entity_id	UUID	NULL
changes	JSONB	NULL (field→old/new)
timestamp	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, timestamp), (tenant_id, entity_type), (tenant_id, user_id)

deletion_log (unveränderlich)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
entity_type	VARCHAR(50)	NOT NULL
entity_id	UUID	NOT NULL
entity_snapshot	JSONB	NOT NULL (full record before deletion)
deleted_at	TIMESTAMPTZ	NOT NULL

notifications

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
type	VARCHAR(20)	NOT NULL (info/warning/error/success)
title	VARCHAR(200)	NOT NULL
body	TEXT	NULL
read_at	TIMESTAMPTZ	NULL
created_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, user_id, read_at)

password_reset_tokens

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
token_hash	VARCHAR(255)	NOT NULL
expires_at	TIMESTAMPTZ	NOT NULL
used_at	TIMESTAMPTZ	NULL

plugins (Plugin Registry in DB)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(100)	NOT NULL
version	VARCHAR(20)	NOT NULL
status	VARCHAR(20)	NOT NULL (installed/active/inactive/error)
manifest	JSONB	NOT NULL
installed_at	TIMESTAMPTZ	NOT NULL
activated_at	TIMESTAMPTZ	NULL

api_tokens (API Token Auth — post-MVP, architecture ready)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
token_hash	VARCHAR(255)	NOT NULL (SHA-256 hash of token)
name	VARCHAR(200)	NOT NULL (user-provided label)
scopes	JSONB	NOT NULL (array of scope strings, e.g. ["companies:read","contacts:write"])
expires_at	TIMESTAMPTZ	NULL (NULL = no expiry)
last_used_at	TIMESTAMPTZ	NULL
created_at	TIMESTAMPTZ	NOT NULL
revoked_at	TIMESTAMPTZ	NULL

Index: (tenant_id, user_id), (token_hash)

Workflow Engine Tables

workflows (Workflow Definition)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(200)	NOT NULL
description	TEXT	NULL
entity_type	VARCHAR(50)	NOT NULL (company/contact/entry/mail/generic)
trigger_type	VARCHAR(30)	NOT NULL (manual/event/schedule)
trigger_config	JSONB	NULL (event name or cron expression)
steps	JSONB	NOT NULL (ordered array of step definitions)
is_active	BOOLEAN	default true
created_by	UUID	FK→users.id
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, entity_type), (tenant_id, is_active)

workflow_instances (Running Workflow)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
workflow_id	UUID	FK→workflows.id
entity_type	VARCHAR(50)	NOT NULL
entity_id	UUID	NULL
current_step_index	INTEGER	NOT NULL default 0
status	VARCHAR(20)	NOT NULL (pending/in_progress/approved/rejected/cancelled/completed)
context	JSONB	NOT NULL (trigger data + step results)
initiated_by	UUID	FK→users.id
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL
completed_at	TIMESTAMPTZ	NULL

Index: (tenant_id, status), (tenant_id, workflow_id), (tenant_id, initiated_by)

workflow_step_history (Audit Trail per Step)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
instance_id	UUID	FK→workflow_instances.id
step_index	INTEGER	NOT NULL
step_name	VARCHAR(200)	NOT NULL
action	VARCHAR(50)	NOT NULL (advance/approve/reject/cancel/timeout)
actor_id	UUID	FK→users.id NULL (NULL for system)
result	JSONB	NULL (step output data)
created_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, instance_id, step_index)

KI-Copilot Tables

ai_conversations

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
role	VARCHAR(20)	NOT NULL (user/assistant)
content	TEXT	NOT NULL
proposed_actions	JSONB	NULL (array of {method, path, body, description})
executed	BOOLEAN	default false
created_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, user_id, created_at)

DMS Plugin Tables (v2 — Plugin Phase)

dms_folders

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(255)	NOT NULL
parent_id	UUID	FK→dms_folders.id NULL
owner_id	UUID	FK→users.id
path	VARCHAR(1000)	NOT NULL (materialized path)
deleted_at	TIMESTAMPTZ	NULL
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, parent_id), (tenant_id, path)

dms_files

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(255)	NOT NULL
folder_id	UUID	FK→dms_folders.id NULL
size	BIGINT	NOT NULL
mime_type	VARCHAR(100)	NOT NULL
storage_path	VARCHAR(1000)	NOT NULL
uploaded_by	UUID	FK→users.id
uploaded_at	TIMESTAMPTZ	NOT NULL
modified_at	TIMESTAMPTZ	NOT NULL
deleted_at	TIMESTAMPTZ	NULL

Index: (tenant_id, folder_id), (tenant_id, deleted_at)

file_links

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
file_id	UUID	FK→dms_files.id
entity_type	VARCHAR(50)	NOT NULL (company/contact)
entity_id	UUID	NOT NULL

Unique: (file_id, entity_type, entity_id)

tags

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(100)	NOT NULL
color	VARCHAR(7)	default '#808080'
created_at	TIMESTAMPTZ	NOT NULL

Unique: (tenant_id, name)

tag_assignments

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
entity_type	VARCHAR(50)	NOT NULL
entity_id	UUID	NOT NULL
tag_id	UUID	FK→tags.id

Unique: (entity_type, entity_id, tag_id)

folder_permissions

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
folder_id	UUID	FK→dms_folders.id
group_id	UUID	NULL
user_id	UUID	NULL
permission	VARCHAR(10)	NOT NULL (read/write/admin)

file_shares

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
file_id	UUID	FK→dms_files.id
user_id	UUID	NULL
group_id	UUID	NULL
permission	VARCHAR(10)	NOT NULL (read/write)

share_links

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
file_id	UUID	FK→dms_files.id
token	VARCHAR(64)	NOT NULL UNIQUE
password_hash	VARCHAR(255)	NULL
expires_at	TIMESTAMPTZ	NULL
download_only	BOOLEAN	default false

Calendar Plugin Tables (v2 — Plugin Phase)

calendars

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(200)	NOT NULL
color	VARCHAR(7)	default '#3B82F6'
type	VARCHAR(20)	NOT NULL (personal/team/project/company)
owner_id	UUID	FK→users.id
created_at	TIMESTAMPTZ	NOT NULL

calendar_entries

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
calendar_id	UUID	FK→calendars.id
entry_type	VARCHAR(15)	NOT NULL (appointment/task)
subtype	VARCHAR(20)	default 'normal' (normal/follow_up/private)
title	VARCHAR(500)	NOT NULL
description	TEXT	NULL
start_at	TIMESTAMPTZ	NULL (appointment)
end_at	TIMESTAMPTZ	NULL (appointment)
all_day	BOOLEAN	default false
location	VARCHAR(500)	NULL
due_date	TIMESTAMPTZ	NULL (task)
priority	VARCHAR(10)	NULL (high/medium/low) (task)
status	VARCHAR(20)	default 'open' (open/in_progress/done/cancelled)
assigned_to	UUID	FK→users.id NULL (task)
reminder	JSONB	NULL ({value, unit, channel})
recurrence	JSONB	NULL ({pattern, custom_rule, end_date, exceptions})
source_mail_id	UUID	NULL (mail integration)
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL
deleted_at	TIMESTAMPTZ	NULL

Index: (tenant_id, calendar_id), (tenant_id, start_at), (tenant_id, due_date), (tenant_id, assigned_to, status)

calendar_entry_links

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
entry_id	UUID	FK→calendar_entries.id
entity_type	VARCHAR(50)	NOT NULL (company/contact)
entity_id	UUID	NOT NULL

calendar_shares

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
calendar_id	UUID	FK→calendars.id
user_id	UUID	NULL
group_id	UUID	NULL
permission	VARCHAR(10)	NOT NULL (read/write)

user_calendar_visibility

Column	Type	Constraints
user_id	UUID	FK→users.id
calendar_id	UUID	FK→calendars.id
tenant_id	UUID	FK→tenants.id
visible	BOOLEAN	default true

PK: (user_id, calendar_id)

subtasks

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
entry_id	UUID	FK→calendar_entries.id
title	VARCHAR(500)	NOT NULL
completed	BOOLEAN	default false
created_at	TIMESTAMPTZ	NOT NULL

resources

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(200)	NOT NULL
type	VARCHAR(50)	NOT NULL (room/equipment)

resource_bookings

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
resource_id	UUID	FK→resources.id
entry_id	UUID	FK→calendar_entries.id
start_at	TIMESTAMPTZ	NOT NULL
end_at	TIMESTAMPTZ	NOT NULL

Mail Plugin Tables (v2 — Plugin Phase)

mail_accounts

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
name	VARCHAR(200)	NOT NULL
type	VARCHAR(20)	default 'personal' (personal/shared)
imap_host	VARCHAR(255)	NOT NULL
imap_port	INTEGER	NOT NULL
imap_ssl	BOOLEAN	default true
smtp_host	VARCHAR(255)	NOT NULL
smtp_port	INTEGER	NOT NULL
smtp_starttls	BOOLEAN	default true
username	VARCHAR(255)	NOT NULL
password_encrypted	BYTEA	NOT NULL (AES-256)
default_signature_id	UUID	NULL

mail_folders

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
account_id	UUID	FK→mail_accounts.id
name	VARCHAR(200)	NOT NULL
type	VARCHAR(20)	NOT NULL (inbox/sent/drafts/spam/custom)
parent_id	UUID	FK→mail_folders.id NULL
unread_count	INTEGER	default 0
total_count	INTEGER	default 0

mails

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
account_id	UUID	FK→mail_accounts.id
folder_id	UUID	FK→mail_folders.id
subject	TEXT	NULL
body_html	TEXT	NULL (sanitized with DOMPurify)
body_text	TEXT	NULL
from_addr	VARCHAR(255)	NOT NULL
to_addrs	JSONB	NOT NULL (array)
cc_addrs	JSONB	NULL
bcc_addrs	JSONB	NULL
date	TIMESTAMPTZ	NOT NULL
in_reply_to	VARCHAR(255)	NULL
references	TEXT	NULL
thread_id	VARCHAR(255)	NULL
seen	BOOLEAN	default false
flagged	BOOLEAN	default false
has_attachments	BOOLEAN	default false
body_tsv	TSVECTOR	NULL (full-text search)

Index: GIN(body_tsv), (tenant_id, account_id, folder_id), (tenant_id, thread_id)

mail_attachments

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
mail_id	UUID	FK→mails.id
filename	VARCHAR(255)	NOT NULL
mime_type	VARCHAR(100)	NOT NULL
size	BIGINT	NOT NULL
dms_file_id	UUID	FK→dms_files.id NULL
content_id	VARCHAR(255)	NULL (inline images)

mail_labels

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(100)	NOT NULL
color	VARCHAR(7)	NOT NULL

mail_label_assignments

Column	Type	Constraints
mail_id	UUID	FK→mails.id
label_id	UUID	FK→mail_labels.id
tenant_id	UUID	FK→tenants.id

PK: (mail_id, label_id)

mail_rules

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
account_id	UUID	FK→mail_accounts.id
name	VARCHAR(200)	NOT NULL
conditions	JSONB	NOT NULL
actions	JSONB	NOT NULL
priority	INTEGER	default 0

mail_templates

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id NULL (NULL=shared)
name	VARCHAR(200)	NOT NULL
subject	VARCHAR(500)	NULL
body_html	TEXT	NOT NULL

mail_signatures

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
name	VARCHAR(200)	NOT NULL
body_html	TEXT	NOT NULL

vacation_sent_log

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
account_id	UUID	FK→mail_accounts.id
sender_address	VARCHAR(255)	NOT NULL
sent_at	TIMESTAMPTZ	NOT NULL

mail_seen_by

Column	Type	Constraints
mail_id	UUID	FK→mails.id
user_id	UUID	FK→users.id
seen_at	TIMESTAMPTZ	NOT NULL

PK: (mail_id, user_id)

mail_account_delegates

Column	Type	Constraints
id	UUID	PK
account_id	UUID	FK→mail_accounts.id
delegate_user_id	UUID	FK→users.id
permission	VARCHAR(10)	NOT NULL (read/full)

mail_account_send_permissions

Column	Type	Constraints
account_id	UUID	FK→mail_accounts.id
user_id	UUID	FK→users.id

PK: (account_id, user_id)

pgp_keys

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
private_key_encrypted	BYTEA	NULL
public_key	TEXT	NOT NULL

contact_pgp_keys

Column	Type	Constraints
contact_id	UUID	FK→contacts.id
public_key	TEXT	NOT NULL
tenant_id	UUID	FK→tenants.id

PK: (contact_id)

Full-Text Search

PostgreSQL tsvector with GIN index on:

• contacts: to_tsvector('simple', last_name || ' ' || first_name || ' ' || COALESCE(email, ''))
• companies: to_tsvector('simple', name || ' ' || COALESCE(description, '') || ' ' || COALESCE(billing_city, ''))
• mails: to_tsvector('simple', subject || ' ' || body_text) stored in body_tsv column

---

3. API Design

Conventions

• Versioning: URL-based: /api/v1/...
• Auth: Session cookie (HttpOnly, Secure, SameSite=Strict). All endpoints except /api/v1/health and /api/v1/auth/* require auth.
• Tenant Context: Active tenant stored in session. All API responses are tenant-scoped.
• Error Format: {"detail": "message", "code": "error_code", "fields": {"field": "msg"}}
• Pagination: ?page=1&page_size=25 → {items: [...], total: N, page: P, page_size: S}
• Sort: ?sort_by=name&sort_order=asc|desc
• Search: ?search=text → full-text search
• Filter: ?industry=IT&country=Germany → structured filters
• Export: ?format=csv|xlsx on list endpoints
• CSRF: SameSite=Strict + Origin-Header-Validierung (no double-submit token)

Auth Endpoints

Method	Path	Feature	Description
POST	/api/v1/auth/login	F-AUTH-01	Login with email+password, sets session cookie
POST	/api/v1/auth/logout	F-AUTH-02	Invalidate session, clear cookie
GET	/api/v1/auth/me	F-AUTH-01	Get current user + active tenant
POST	/api/v1/auth/switch-tenant	F-AUTH-07	Switch active tenant
POST	/api/v1/auth/password-reset/request	F-AUTH-05	Request password reset email
POST	/api/v1/auth/password-reset/confirm	F-AUTH-05	Reset password with token

User Management Endpoints

Method	Path	Feature	Description
GET	/api/v1/users	F-AUTH-03	List users (admin only, paginated)
POST	/api/v1/users	F-AUTH-03	Create user (admin only)
GET	/api/v1/users/{id}	F-AUTH-03	Get user details
PATCH	/api/v1/users/{id}	F-AUTH-03	Update user
DELETE	/api/v1/users/{id}	F-AUTH-03	Delete user
GET	/api/v1/users/me/settings	F-CORE-09	Get current user preferences
PATCH	/api/v1/users/me/settings	F-CORE-09	Update preferences (language, theme, etc.)

Role Management Endpoints

Method	Path	Feature	Description
GET	/api/v1/roles	F-AUTH-06	List roles
POST	/api/v1/roles	F-AUTH-06	Create role
PATCH	/api/v1/roles/{id}	F-AUTH-06	Update role (incl. field permissions)
DELETE	/api/v1/roles/{id}	F-AUTH-06	Delete role

Tenant Management Endpoints

Method	Path	Feature	Description
GET	/api/v1/tenants	F-AUTH-07	List tenants for current user
POST	/api/v1/tenants	F-AUTH-07	Create tenant (admin)
GET	/api/v1/tenants/{id}/users	F-AUTH-07	List users in tenant
POST	/api/v1/tenants/{id}/users	F-AUTH-07	Assign user to tenant

Company Endpoints (F-DATA-01, F-DATA-02)

Method	Path	Feature	Description
GET	/api/v1/companies	F-COMP-05,06	List with search, filter, pagination, sort
POST	/api/v1/companies	F-COMP-01	Create company
GET	/api/v1/companies/{id}	F-COMP-02	Get company detail (incl. contacts)
PUT	/api/v1/companies/{id}	F-COMP-03	Update company
DELETE	/api/v1/companies/{id}	F-COMP-04	Soft-delete (?cascade=true)
POST	/api/v1/companies/{id}/contacts/{contact_id}	F-CONT-07	Add N:M contact link
DELETE	/api/v1/companies/{id}/contacts/{contact_id}	F-CONT-07	Remove N:M contact link
GET	/api/v1/companies/export	F-DATA-01,02	Export (?format=csv
GET	/api/v1/companies/{id}/emails	F-MAIL-10	Company email history

Contact Endpoints

Method	Path	Feature	Description
GET	/api/v1/contacts	F-CONT-05,06	List with search, filter, pagination
POST	/api/v1/contacts	F-CONT-01	Create contact (with company_ids array)
GET	/api/v1/contacts/{id}	F-CONT-02	Get contact detail (incl. companies)
PUT	/api/v1/contacts/{id}	F-CONT-03	Update contact
DELETE	/api/v1/contacts/{id}	F-CONT-04	Soft-delete
DELETE	/api/v1/contacts/{id}?gdpr=true	F-COMP-08	Hard delete (DSGVO)
GET	/api/v1/contacts/export	F-DATA-01,02	Export (?format=csv
GET	/api/v1/contacts/{id}/emails	F-MAIL-10	Contact email history

Import Endpoints

Method	Path	Feature	Description
POST	/api/v1/import	F-MIG-01	CSV import (entity_type + file)
POST	/api/v1/import/preview	F-CORE-11	Dry-run import preview

Audit Log Endpoints

Method	Path	Feature	Description
GET	/api/v1/audit-log	F-COMP-07	List audit log (admin, paginated)

Notification Endpoints

Method	Path	Feature	Description
GET	/api/v1/notifications	F-CORE-13	List notifications (unread first)
PATCH	/api/v1/notifications/{id}/read	F-CORE-13	Mark as read
GET	/api/v1/notifications/unread-count	F-CORE-13	Unread badge count
PATCH	/api/v1/users/me/notification-prefs	F-CORE-13	Update notification preferences

Global Search Endpoint (F-COMP-06, F-CONT-06, F-SEARCH-01)

Method	Path	Feature	Description
GET	/api/v1/search?q=text	F-SEARCH-01	Search across all entities

Health & Infrastructure

Method	Path	Feature	Description
GET	/api/v1/health	F-INFRA-01	Health check (no auth)
GET	/api/v1/jobs/{id}/status	F-SCHED-01	Background job status

Plugin System Endpoints

Method	Path	Feature	Description
GET	/api/v1/plugins	F-PLUGIN-01	List plugins (status)
POST	/api/v1/plugins/{name}/install	F-PLUGIN-02	Install plugin
POST	/api/v1/plugins/{name}/activate	F-PLUGIN-02	Activate plugin
POST	/api/v1/plugins/{name}/deactivate	F-PLUGIN-02	Deactivate plugin
DELETE	/api/v1/plugins/{name}	F-PLUGIN-02	Uninstall (?remove_data=true)
GET	/api/v1/plugins/manifest	F-PLUGIN-02	Plugin manifest schema (for developers)

DMS Plugin Endpoints (v2 — Plugin Phase)

v2 Plugin Features — These endpoints are implemented in T04 (DMS Backend) and T08a (DMS Frontend). Not available in v1.

Method	Path	Feature	Description
GET	/api/v1/dms/folders	F-FILEUI-01	List folders (tree)
POST	/api/v1/dms/folders	F-DMS-01	Create folder
PATCH	/api/v1/dms/folders/{id}	F-DMS-01	Rename/move folder
DELETE	/api/v1/dms/folders/{id}	F-DMS-01	Soft-delete folder
POST	/api/v1/dms/files/upload	F-DMS-02	Upload file (multipart)
GET	/api/v1/dms/files/{id}	F-DMS-06	File metadata
PATCH	/api/v1/dms/files/{id}	F-DMS-03	Rename/move file
DELETE	/api/v1/dms/files/{id}	F-DMS-03	Soft-delete file
POST	/api/v1/dms/files/{id}/restore	F-DMS-03	Restore from trash
GET	/api/v1/dms/files/{id}/preview	F-DMS-04	PDF preview stream
POST	/api/v1/dms/files/{id}/edit-session	F-DMS-05	OnlyOffice edit session
GET	/api/v1/dms/files/{id}/permissions	F-PERM-06	Permission overview
POST	/api/v1/dms/files/{id}/link	F-LINK-01,02	Link to entity
DELETE	/api/v1/dms/files/{id}/link	F-LINK-05	Remove entity link
POST	/api/v1/dms/files/{id}/share	F-PERM-03,04	Share with user/group
DELETE	/api/v1/dms/files/{id}/share	F-PERM-03,04	Remove share
POST	/api/v1/dms/files/{id}/share-link	F-PERM-05	Create public share link
GET	/api/v1/dms/search	F-DMS-07	Search files (?folder_id)
GET	/api/v1/dms/shared-with-me	F-PERM-03	Shared files list
POST	/api/v1/dms/files/bulk-move	F-FILEUI-04	Bulk move
POST	/api/v1/dms/files/bulk-delete	F-FILEUI-04	Bulk delete

Tag Plugin Endpoints

Method	Path	Feature	Description
GET	/api/v1/tags	F-TAG-04	List tags (with counts)
POST	/api/v1/tags	F-TAG-02	Create tag (admin)
PATCH	/api/v1/tags/{id}	F-TAG-02	Update tag
DELETE	/api/v1/tags/{id}	F-TAG-02	Delete tag (cascade)
POST	/api/v1/tags/assign	F-TAG-01	Assign tag to entity
DELETE	/api/v1/tags/assign	F-TAG-01	Remove tag from entity
POST	/api/v1/tags/bulk-assign	F-FILEUI-04	Bulk tag assign

Calendar Plugin Endpoints

Method	Path	Feature	Description
GET	/api/v1/calendars	F-CAL-11	List calendars
POST	/api/v1/calendars	F-CAL-11	Create calendar
PATCH	/api/v1/calendars/{id}	F-CAL-11	Update calendar
DELETE	/api/v1/calendars/{id}	F-CAL-11	Delete calendar (cascade)
POST	/api/v1/calendars/{id}/share	F-CAL-13	Share calendar
GET	/api/v1/calendars/{id}/permissions	F-CAL-13	Calendar permissions
GET	/api/v1/calendar/entries	F-CAL-01,17	List entries (filter, paginate)
POST	/api/v1/calendar/entries	F-CAL-03	Create entry (appointment/task)
GET	/api/v1/calendar/entries/{id}	F-CAL-03	Get entry detail
PATCH	/api/v1/calendar/entries/{id}	F-CAL-05	Update entry (drag&drop, status)
DELETE	/api/v1/calendar/entries/{id}	—	Delete entry
POST	/api/v1/calendar/entries/{id}/link	F-CAL-04	Link to entity
POST	/api/v1/calendar/entries/{id}/subtasks	F-CAL-16	Create subtask
PATCH	/api/v1/calendar/entries/{id}/subtasks/{sub_id}	F-CAL-16	Toggle subtask
POST	/api/v1/calendar/entries/bulk	F-CAL-18	Bulk actions
GET	/api/v1/calendar/kanban	F-CAL-02	Kanban board view
GET	/api/v1/calendar/entries/export	F-CAL-17	Export tasks CSV
GET	/api/v1/calendar/{calendar_id}/ics-feed	F-CAL-09	ICS feed export
POST	/api/v1/calendar/import	F-CAL-09	ICS import
POST	/api/v1/resources	F-CAL-10	Create resource (admin)
POST	/api/v1/calendar/entries/{id}/book-resource	F-CAL-10	Book resource

Mail Plugin Endpoints

Method	Path	Feature	Description
GET	/api/v1/mail/accounts	F-MAIL-14	List mail accounts
POST	/api/v1/mail/accounts	F-MAIL-18	Create mail account
PATCH	/api/v1/mail/accounts/{id}	F-MAIL-14	Update account
GET	/api/v1/mail/accounts/shared	F-MAIL-15	Shared mailboxes
POST	/api/v1/mail/accounts/{id}/users	F-MAIL-15	Assign shared mailbox users
POST	/api/v1/mail/accounts/{id}/delegates	F-MAIL-16	Delegate access
POST	/api/v1/mail/accounts/{id}/send-permissions	F-MAIL-17	Grant send permission
GET	/api/v1/mail/folders	F-MAIL-01,19	List mail folders
POST	/api/v1/mail/folders	F-MAIL-19	Create folder
PATCH	/api/v1/mail/folders/{id}	F-MAIL-19	Rename folder
DELETE	/api/v1/mail/folders/{id}	F-MAIL-19	Delete folder
GET	/api/v1/mail	F-MAIL-03	List mails (filter, search, paginate)
GET	/api/v1/mail/{id}	F-MAIL-02	Get mail detail
POST	/api/v1/mail/send	F-MAIL-02	Send mail
POST	/api/v1/mail/{id}/reply	F-MAIL-02	Reply to mail
POST	/api/v1/mail/{id}/forward	F-MAIL-02	Forward mail
PATCH	/api/v1/mail/{id}/flags	F-MAIL-09	Update flags (seen/flagged)
GET	/api/v1/mail/{id}/attachments/{att_id}	F-MAIL-04	Download attachment
POST	/api/mail/{id}/link	F-MAIL-10	Manual contact/company link
POST	/api/v1/mail/{id}/create-event	F-MAIL-11	Create calendar event from mail
GET	/api/v1/mail/search	F-MAIL-03	Full-text mail search
GET	/api/v1/mail/threads	F-MAIL-05	Threaded view
POST	/api/v1/mail/templates	F-MAIL-06	Create template
GET	/api/v1/mail/templates	F-MAIL-06	List templates
POST	/api/v1/mail/signatures	F-MAIL-13	Create signature
GET	/api/v1/mail/signatures	F-MAIL-13	List signatures
POST	/api/v1/mail/rules	F-MAIL-07	Create mail rule
GET	/api/v1/mail/rules	F-MAIL-07	List rules
DELETE	/api/v1/mail/rules/{id}	F-MAIL-07	Delete rule
POST	/api/v1/mail/vacation	F-MAIL-08	Set vacation auto-reply
POST	/api/v1/mail/pgp/keys	F-MAIL-12	Import PGP private key
POST	/api/v1/contacts/{id}/pgp-key	F-MAIL-12	Import contact public key
POST	/api/v1/mail/labels	F-MAIL-09	Create label
POST	/api/v1/mail/{id}/labels	F-MAIL-09	Assign label to mail

Public Endpoints (no auth)

Method	Path	Feature	Description
GET	/api/v1/health	F-INFRA-01	Health check
GET	/api/public/share/{token}	F-PERM-05	Public share link access
GET	/api/v1/calendar/{calendar_id}/ics-feed?token=...	F-CAL-09	ICS feed (token auth)

KI-Copilot Endpoints

Method	Path	Feature	Description
POST	/api/v1/ai/copilot/query	F-AI-01	Natural language → proposed API calls
GET	/api/v1/ai/copilot/history	F-AI-01	Conversation history (per user, paginated)
POST	/api/v1/ai/copilot/execute	F-AI-01	Execute proposed API action (with user confirmation)

Workflow Engine Endpoints

Method	Path	Feature	Description
GET	/api/v1/workflows	F-WF-01	List workflow definitions (paginated, filter by entity_type)
POST	/api/v1/workflows	F-WF-01	Create workflow definition (admin only)
GET	/api/v1/workflows/{id}	F-WF-01	Get workflow definition detail
PATCH	/api/v1/workflows/{id}	F-WF-01	Update workflow definition
DELETE	/api/v1/workflows/{id}	F-WF-01	Delete workflow definition
POST	/api/v1/workflows/{id}/instances	F-WF-01	Start workflow instance (trigger)
GET	/api/v1/workflows/instances	F-WF-01	List workflow instances (filter by status, paginated)
GET	/api/v1/workflows/instances/{id}	F-WF-01	Get instance detail (current step, history)
POST	/api/v1/workflows/instances/{id}/advance	F-WF-01	Advance to next step (approve/reject)
POST	/api/v1/workflows/instances/{id}/cancel	F-WF-01	Cancel workflow instance

Monitoring Endpoints

Method	Path	Feature	Description
GET	/api/v1/health	F-INFRA-01,F-INFRA-04	Extended health check (DB, Redis, storage, worker)
GET	/api/v1/metrics	F-INFRA-04	Prometheus metrics (admin only)

---

4. Plugin Architecture

Plugin Manifest Format

{
  "name": "dms",
  "version": "1.0.0",
  "display_name": "Dateien (DMS)",
  "description": "Datei-Management-System mit Ordnerstruktur, Upload, OnlyOffice",
  "author": "LeoCRM Team",
  "dependencies": [],
  "min_core_version": "1.0.0",
  "services": ["db", "cache", "event_bus", "storage", "notifications"],
  "endpoints": {
    "router": "app.plugins.builtins.dms.router:router"
  },
  "migrations": {
    "package": "app.plugins.builtins.dms.migrations",
    "versions_dir": "migrations"
  },
  "ui": {
    "routes": [{"path": "/dms", "component": "DmsView"}],
    "menu_items": [{"label": "Dateien", "icon": "folder", "route": "/dms", "order": 40}],
    "detail_tabs": [
      {"entity": "company", "tab_id": "files", "label": "Dateien", "component": "CompanyFilesTab"},
      {"entity": "contact", "tab_id": "files", "label": "Dateien", "component": "ContactFilesTab"}
    ],
    "settings_pages": [{"id": "dms-settings", "label": "DMS-Einstellungen", "component": "DmsSettings"}],
    "dashboard_widgets": []
  },
  "events": {
    "listens_to": ["company.deleted", "contact.deleted"],
    "emits": ["dms.file.uploaded", "dms.file.shared"]
  },
  "preferences": [
    {"key": "default_folder_view", "type": "string", "default": "grid", "options": ["grid", "list"]}
  ],
  "notification_types": ["dms.share_received", "dms.upload_complete"]
}

Lifecycle Hooks

install()   → Run DB migrations, register plugin in DB (status=installed)
activate()  → Register routes, event listeners, UI components (status=active)
deactivate()→ Unregister routes, event listeners, UI components (status=inactive)
uninstall() → Optional: drop plugin tables (?remove_data=true, status=removed)

Event Bus (F-CORE-01)

v1 Core Feature — Event Bus is part of T01 (Core Infrastructure).

# Publishing events
await event_bus.publish("company.created", payload={"company_id": uuid, "tenant_id": uuid})
await event_bus.publish("contact.deleted", payload={"contact_id": uuid, "tenant_id": uuid})

# Subscribing (plugins register during activate())
@event_bus.subscribe("company.created")
async def on_company_created(event):
    # e.g., create default folder for company
    ...
``

**Implementation:** Async in-process event bus. Events are dispatched to registered handlers. For persistence/retry, events are also pushed to Redis job queue for reliable processing.

### Service Container / DI (F-CORE-05, F-CORE-07, F-CORE-08, F-CORE-10, F-CORE-12)

> **v1 Core Features:**
> - F-CORE-07: Async Job Queue (ARQ) — background jobs for export, backup, mail sync
> - F-CORE-08: Caching-Strategie — Redis-based caching for sessions, query results, plugin metadata
> - F-CORE-10: Storage-Backend — abstract storage interface (local filesystem, S3-compatible)
> - F-CORE-12: PDF/Document Generation Service — WeasyPrint for PDF generation (export, reports)

```python
# Core services registered in container
class ServiceContainer:
    db: SessionManager
    cache: RedisCache
    event_bus: EventBus
    storage: StorageBackend
    notifications: NotificationService
    audit: AuditLogger
    jobs: JobQueue

# Plugin requests services via manifest
def activate(container: ServiceContainer):
    db = container.db
    event_bus = container.event_bus
    ...

Plugin DB Migration (F-CORE-03)

Each plugin has its own migrations/ directory with versioned SQL/Python files. Migrations are tracked in a plugin_migrations table:

Column	Type
plugin_name	VARCHAR
migration_id	VARCHAR
executed_at	TIMESTAMPTZ

All plugin tables include tenant_id column for isolation.

UI Plugin Framework (F-CORE-04)

The backend plugin manifest declares UI components. The frontend PluginRegistry.tsx fetches active plugin manifests on startup and dynamically renders:

• Routes (React Router)
• Menu items (Sidebar)
• Detail tabs (Company/Contact detail views)
• Settings pages (Settings tree)
• Dashboard widgets

Plugin UI components are lazy-loaded via React.lazy() with Suspense boundaries.

---

5. Multi-Tenant Architecture

Implementation (F-CORE-02)

1. Session Context: Active tenant_id stored in session. Set at login (default tenant) or via switch-tenant.

2. ORM Auto-Filtering: SQLAlchemy before_query event listener automatically injects tenant_id filter on all models that have a tenant_id column. This prevents cross-tenant data access at the ORM level.

@event.listens_for(Session, "do_orm_execute")
def auto_filter_tenant(execute_state):
    if not _tenant_filter_disabled:
        execute_state.statement = execute_state.statement.where(
            TenantMixin.tenant_id == current_tenant_id()
        )

3. TenantMixin: All models with tenant isolation inherit from a TenantMixin base class that adds tenant_id column.

4. Tenant Switch: POST /api/v1/auth/switch-tenant updates session. All subsequent queries use new tenant_id.

5. Cross-Tenant Protection: If a user tries to access a resource not in their active tenant → 404 (not 403, to prevent information leakage).

6. Plugin Tables: All plugin-created tables MUST include tenant_id. The plugin migration validator checks this.

---

6. Auth Architecture

Session-Based Auth (F-AUTH-01, F-INT-02)

Login Flow:
1. POST /api/v1/auth/login {email, password}
2. Backend validates → bcrypt.check_password(password, user.password_hash)
3. Create session in Redis (key: `session:{session_id}`, value: {user_id, tenant_id, csrf_token}, TTL=8h)
4. Write session record to PostgreSQL `sessions` table for audit trail (id, user_id, tenant_id, csrf_token, expires_at, created_at)
5. Set cookie: leocrm_session=<session_id>; HttpOnly; Secure; SameSite=Strict; Path=/
6. Return {user, tenant} info

RBAC (F-AUTH-04, F-AUTH-06, F-AUTH-08)

Role → Permissions Structure:
{
  "companies": {"read": true, "write": true, "delete": true, "admin": false},
  "contacts": {"read": true, "write": true, "delete": true, "admin": false},
  "users": {"read": false, "write": false, "delete": false, "admin": false},
  ...
}

Field-Level Permissions:
{
  "companies.annual_revenue": "hidden",  // viewer can't see
  "companies.phone": "read_only",       // editor can't edit
  "contacts.email": "read_write"         // full access
}

Default roles: admin (all permissions), editor (CRUD on companies/contacts, no user management), viewer (read-only).

Custom roles can be created via POST /api/v1/roles with custom permissions.

API Tokens (F-INT-02)

API tokens for external integrations (post-MVP, but architecture supports it):

• Tokens stored in api_tokens table (user_id, token_hash, name, scopes, expires_at)
• Token auth via Authorization: Bearer <token> header
• Token respects RBAC and tenant isolation

CSRF Protection (F-SEC-01, F-SEC-03)

v1 Core Feature — CSRF protection via SameSite=Strict + Origin validation.

• SameSite=Strict cookie (browser blocks cross-site requests)
• Origin-Header-Validierung middleware (server-side check)
• Only GET/HEAD/OPTIONS are exempt

Content-Security-Policy (F-SEC-02)

• CSP-Header gesetzt im Nginx-Reverse-Proxy für alle Responses: Content-Security-Policy: default-src 'self'; script-src 'self'; style-src 'self' 'unsafe-inline'; img-src 'self' data: blob:; font-src 'self' data:; connect-src 'self'; frame-src 'self' https://onlyoffice:8080; object-src 'none'; base-uri 'self'; form-action 'self'
• X-Content-Type-Options: nosniff
• X-Frame-Options: SAMEORIGIN (nur OnlyOffice iframe erlaubt)
• X-XSS-Protection: 1; mode=block
• Strict-Transport-Security: max-age=31536000; includeSubDomains
• Referrer-Policy: strict-origin-when-cross-origin
• HTML in User-Eingaben wird serverseitig via Pydantic validiert und im Frontend via DOMPurify/escaped rendering sanitized

CORS Configuration

v1 Core Feature — Cross-Origin Resource Sharing policy for API security.

• Production: Same-origin only (Access-Control-Allow-Origin: <self>). Frontend and API served from same domain via Nginx reverse proxy.
• Development: Explicit origins allowed: http://localhost:5173 (Vite dev), http://localhost:3000 (alt dev). No wildcard *.
• Methods: GET, POST, PATCH, PUT, DELETE, OPTIONS
• Headers: Authorization, Content-Type, X-CSRF-Token, X-Tenant-ID
• Credentials: true (cookies allowed for session auth)
• Max-Age: 3600 (preflight cache)
• Implementation: FastAPI CORSMiddleware with explicit origin list, not wildcard.

Auth Rate Limiting (Brute-Force Protection)

v1 Core Feature — Rate limiting for authentication endpoints to prevent brute-force attacks.

• Login endpoint (POST /api/v1/auth/login):
  • Redis-based counter: auth:login:{ip}:{email} with TTL=15min
  • Max 5 failed attempts per 15min window → 429 Too Many Requests
  • On success: counter reset
  • On failure: counter incremented, response includes Retry-After header
• Password reset request (POST /api/v1/auth/password-reset/request):
  • Redis-based counter: auth:reset:{ip} with TTL=1h
  • Max 3 requests per hour → 429 (prevents email enumeration via timing)
  • Always returns 200 (no user enumeration) but blocks after limit
• Password reset confirm (POST /api/v1/auth/password-reset/confirm):
  • Redis-based counter: auth:reset_confirm:{ip} with TTL=1h
  • Max 5 attempts per hour → 429
• General API rate limiting (all endpoints):
  • Redis-based sliding window: rate:{ip}:{endpoint} with TTL=1min
  • Default: 60 requests/min per IP per endpoint
  • Auth endpoints: 10 requests/min per IP (stricter)
• Implementation: FastAPI middleware using Redis INCR + EXPIRE

PostgreSQL Row-Level Security (RLS) — Defense-in-Depth

v1 Core Feature — Database-level tenant isolation as defense-in-depth alongside ORM-level tenant filtering.

Strategy: ORM-level tenant filtering (SQLAlchemy event listener) is the primary isolation mechanism. PostgreSQL RLS policies provide a secondary defense layer to prevent data leaks if ORM filtering is bypassed (raw SQL, ARQ worker jobs, admin queries).

Implementation:

-- Enable RLS on all tenant-scoped tables
ALTER TABLE companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE api_tokens ENABLE ROW LEVEL SECURITY;
-- (v2: dms_folders, dms_files, calendars, mail_accounts, etc.)

-- Create policy: users can only see rows in their current tenant
CREATE POLICY tenant_isolation ON companies
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Same pattern for all tenant-scoped tables
CREATE POLICY tenant_isolation ON contacts
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- ... (repeat for all tenant-scoped tables)

-- Application sets tenant context per request:
-- SET LOCAL app.current_tenant_id = '<uuid>';
-- This is set in SQLAlchemy session event listener after tenant resolution

Tenant Context Propagation:

• API requests: SQLAlchemy before_request event sets app.current_tenant_id session variable
• ARQ worker jobs: Job payload includes tenant_id; worker sets session variable before processing
• Raw SQL queries: Must explicitly set tenant context or use SET LOCAL
• Admin/superuser: Can bypass RLS via SET row_security = off (logged in audit_log)

Testing:

• Integration test: create 2 tenants, verify tenant A cannot read tenant B's data even with raw SQL
• Test: ARQ worker processes job with correct tenant context
• Test: RLS bypass attempt is logged in audit_log

Password Reset (F-AUTH-05, F-INT-01)

v1 Core Feature — F-INT-01: E-Mail-Integration for password reset via SMTP.

1. POST /api/v1/auth/password-reset/request {email}
   → Generate token, store hash in `password_reset_tokens` (24h expiry)
   → Send email with reset link
   → Always return 200 (no user enumeration)

2. POST /api/v1/auth/password-reset/confirm {token, new_password}
   → Verify token hash + expiry
   → Update password_hash
   → Mark token as used
   → Invalidate all sessions for user

---

7. Frontend Architecture

Stack (F-UI-01, F-UI-02, F-UI-03, F-UI-04, F-UI-05, F-UI-06, F-UI-08)

Component	Technology
Framework	React 18
Build Tool	Vite
Router	React Router v6
State (Server)	TanStack Query (React Query v5)
State (Client)	Zustand
i18n	react-i18next
Forms	React Hook Form + Zod
Styling	Tailwind CSS
Icons	lucide-react
Tables	TanStack Table
Calendar	Custom (FullCalendar or custom)
Rich Text	TipTap (mail composer)
PDF Viewer	PDF.js
Testing	Vitest + @testing-library/react

Routing (F-NAV-01, F-SET-01)

/                 → Dashboard
/login            → Login page
/password-reset   → Password reset flow
/companies        → Company list
/companies/:id    → Company detail
/contacts         → Contact list
/contacts/:id     → Contact detail
/calendar         → Calendar (plugin)
/dms              → DMS file browser (plugin)
/mail             → Mail (plugin)
/users            → User management (admin)
/audit-log        → Audit log (admin)
/settings         → Settings tree
/settings/*       → Settings sub-pages
/search           → Global search results
/*                → Plugin routes (dynamic)

State Management

• TanStack Query: Server state (API data, caching, optimistic updates, loading/error states).
• Zustand: Client state (active tenant, sidebar collapsed, theme, language, UI toggles).
• URL State: Filters, pagination, sort via URL search params (shareable URLs).

i18n

• Locale files: src/i18n/locales/de.json, src/i18n/locales/en.json
• Default: German. Fallback: German.
• Date/time format via date-fns with locale.
• Language stored in user preferences (synced to backend).

Accessibility (F-A11Y-01, F-A11Y-02, F-A11Y-03)

• Semantic HTML, ARIA roles on all interactive elements.
• prefers-reduced-motion media query in global CSS.
• 44px touch targets via Tailwind min-h-[44px] min-w-[44px] on buttons/links.
• .sr-only class for screen-reader-only text.
• Keyboard navigation: logical tab order, focus visible.
• WCAG 2.1 AA contrast (>4.5:1).

Design System

Based on the approved prototype (leocrm-prototype-x7k2p9):

• Color tokens via Tailwind config
• Component library: Button, Input, Select, Modal, Toast, Table, Card, Badge, Avatar, Pagination, EmptyState, Skeleton
• Responsive breakpoints: sm: 375px, md: 768px, lg: 1024px, xl: 1280px

---

8. Deployment Architecture

Docker Compose

services:
  backend:
    build: ./backend
    ports: ["8000:8000"]
    env_file: .env
    depends_on: [postgres, redis]
    healthcheck:
      test: curl -f http://localhost:8000/api/v1/health || exit 1
      interval: 30s
      timeout: 10s
      retries: 3

  frontend:
    build: ./frontend
    ports: ["80:80"]
    depends_on: [backend]

  postgres:
    image: postgres:16-alpine
    volumes: ["pgdata:/var/lib/postgresql/data"]
    env_file: .env

  redis:
    image: redis:7-alpine
    volumes: ["redisdata:/data"]

  worker:
    build: ./backend
    command: arq app.core.jobs.WorkerSettings
    env_file: .env
    depends_on: [postgres, redis]

  onlyoffice:
    image: onlyoffice/documentserver:latest
    ports: ["8080:80"]
    volumes: ["onlyoffice_data:/var/www/onlyoffice/Data"]

volumes:
  pgdata:
  redisdata:
  onlyoffice_data:
  storage_data:

Environment Variables (F-ENV-01)

v1 Core Feature — F-ENV-01: Environments & Secrets — dev/test/prod profiles with .env.example.

# Database
POSTGRES_HOST=postgres
POSTGRES_PORT=5432
POSTGRES_DB=leocrm
POSTGRES_USER=leocrm
POSTGRES_PASSWORD=<secret>

# Redis
REDIS_URL=redis://redis:6379/0

# Session
LEOCRM_SECRET_KEY=<min-32-chars>
SESSION_TIMEOUT_HOURS=8

# SMTP (for password reset + mail plugin)
SMTP_HOST=
SMTP_PORT=587
SMTP_USER=
SMTP_PASS=

# Mail encryption key
MAIL_ENCRYPTION_KEY=<32-byte-hex>

# Storage
STORAGE_BACKEND=local  # or s3
STORAGE_PATH=/data/leocrm/storage
S3_ENDPOINT=
S3_BUCKET=
S3_ACCESS_KEY=
S3_SECRET_KEY=

# OnlyOffice
ONLYOFFICE_URL=http://onlyoffice:80

# Logging
LOG_LEVEL=INFO

Backup (F-INFRA-02)

v1 Core Feature — F-INFRA-02: Backup & Restore — pg_dump + file storage backup.

• pg_dump daily cron job → backup volume or S3
• Storage volume backup (files)
• Restore documented in docs/admin-guide.md

---

8b. KI-Integration (F-AI-01)

Architektur-Grundlage

LeoCRM ist API-First (F-CORE-06): alle Features sind über die REST API nutzbar. Der KI-Copilot nutzt dieselbe API wie das Frontend — er ist ein API-Client mit eigener Authentifizierung.

KI-Copilot API-Endpoint

Method	Path	Feature	Description
POST	/api/v1/ai/copilot/query	F-AI-01	Natural language → API call translation
GET	/api/v1/ai/copilot/history	F-AI-01	Conversation history (per user)
POST	/api/v1/ai/copilot/execute	F-AI-01	Execute proposed API action (with user confirmation)

RBAC-Durchsetzung

Der KI-Copilot respektiert das Rollen-/Rechte-System des zugehörigen Users:

1. Authentifizierung: Copilot-Requests verwenden die Session des Users (gleicher Cookie). Keine separate Copilot-Auth.
2. RBAC: Copilot-API-Calls gehen durch dieselbe RBAC-Middleware wie Frontend-Calls. Ein Viewer-Copilot kann keine Daten löschen.
3. Field-Level Permissions: Copilot sieht nur Felder, die die User-Rolle erlaubt (hidden fields werden aus Responses gefiltert).
4. Tenant-Isolation: Copilot ist an den tenant_id der aktiven Session gebunden. Cross-Tenant → 404.
5. Audit Log: Alle Copilot-Aktionen werden im Audit-Log als entity_type=ai_copilot protokolliert.

Implementation-Modell (v1)

v1 implementiert die API-Schnittstelle (/api/v1/ai/copilot/*) mit:

• Query-Endpoint: Nimmt Natural-Language-Input → returns vorgeschlagene API-Calls (Method, Path, Body) + Erklärung
• Execute-Endpoint: Führt den vorgeschlagenen API-Call aus (mit User-Bestätigung im Frontend)
• History: Speichert Konversationsverlauf pro User
• Die eigentliche LLM-Integration (z.B. OpenAI/Anthropic) ist konfigurierbar via Env-Vars (AI_MODEL, AI_API_KEY)

Frontend-Integration

• Sidebar-Eintrag „KI-Copilot" (sichtbar wenn AI_ENABLED=true)
• Chat-Interface mit Vorschlags-Karten (zeigt geplante API-Calls vor Ausführung)
• User muss jede destruktive Aktion bestätigen (Confirm-Dialog)

DB-Tabelle: ai_conversations

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
user_id	UUID	FK→users.id
role	VARCHAR(20)	NOT NULL (user/assistant)
content	TEXT	NOT NULL
proposed_actions	JSONB	NULL (array of {method, path, body, description})
executed	BOOLEAN	default false
created_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, user_id, created_at)

---

8c. Workflow Engine (F-WF-01)

Hybrid-Ansatz

Zwei Engine-Typen:

1. Code-Engine (Kern-Workflows): Hartkodierte Python-Workflows für System-Prozesse (z.B. User-Onboarding, Plugin-Installation-Sequence, Mail-Sync-Trigger). Definiert als async def Funktionen im app/workflows/ Modul. Nicht vom User konfigurierbar.

2. Configurable Workflow-Engine (User-Workflows): User-definierte Prozesse via Admin-UI. Mehrstufige Prozesse mit Bedingungen, Genehmigungs-Ketten, Notifications.

Workflow API Endpoints

Method	Path	Feature	Description
GET	/api/v1/workflows	F-WF-01	List workflow definitions
POST	/api/v1/workflows	F-WF-01	Create workflow definition (admin)
GET	/api/v1/workflows/{id}	F-WF-01	Get workflow definition
PATCH	/api/v1/workflows/{id}	F-WF-01	Update workflow definition
DELETE	/api/v1/workflows/{id}	F-WF-01	Delete workflow definition
POST	/api/v1/workflows/{id}/instances	F-WF-01	Start workflow instance (trigger)
GET	/api/v1/workflows/instances	F-WF-01	List workflow instances (filter by status)
GET	/api/v1/workflows/instances/{id}	F-WF-01	Get instance detail (current step, history)
POST	/api/v1/workflows/instances/{id}/advance	F-WF-01	Advance to next step (approve/reject)
POST	/api/v1/workflows/instances/{id}/cancel	F-WF-01	Cancel workflow instance

DB-Tabellen

workflows (Workflow Definition)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
name	VARCHAR(200)	NOT NULL
description	TEXT	NULL
entity_type	VARCHAR(50)	NOT NULL (company/contact/entry/mail/generic)
trigger_type	VARCHAR(30)	NOT NULL (manual/event/schedule)
trigger_config	JSONB	NULL (event name or cron expression)
steps	JSONB	NOT NULL (ordered array of step definitions)
is_active	BOOLEAN	default true
created_by	UUID	FK→users.id
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, entity_type), (tenant_id, is_active)

steps JSONB Structure:

[
  {
    "id": "step_1",
    "name": "Angebot erstellen",
    "type": "action",
    "action": "create_entity",
    "entity": "company",
    "fields": {"name": "${trigger.company_name}", "industry": "${trigger.industry}"}
  },
  {
    "id": "step_2",
    "name": "Genehmigung durch Manager",
    "type": "approval",
    "approver_role": "admin",
    "timeout_hours": 48,
    "on_reject": "notify_initiator"
  },
  {
    "id": "step_3",
    "name": "Benachrichtigung senden",
    "type": "notification",
    "template": "offer_approved",
    "recipients": ["initiator"]
  }
]

workflow_instances (Running Workflow)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
workflow_id	UUID	FK→workflows.id
entity_type	VARCHAR(50)	NOT NULL
entity_id	UUID	NULL
current_step_index	INTEGER	NOT NULL default 0
status	VARCHAR(20)	NOT NULL (pending/in_progress/approved/rejected/cancelled/completed)
context	JSONB	NOT NULL (trigger data + step results)
initiated_by	UUID	FK→users.id
created_at	TIMESTAMPTZ	NOT NULL
updated_at	TIMESTAMPTZ	NOT NULL
completed_at	TIMESTAMPTZ	NULL

Index: (tenant_id, status), (tenant_id, workflow_id), (tenant_id, initiated_by)

workflow_step_history (Audit Trail per Step)

Column	Type	Constraints
id	UUID	PK
tenant_id	UUID	FK→tenants.id
instance_id	UUID	FK→workflow_instances.id
step_index	INTEGER	NOT NULL
step_name	VARCHAR(200)	NOT NULL
action	VARCHAR(50)	NOT NULL (advance/approve/reject/cancel/timeout)
actor_id	UUID	FK→users.id NULL (NULL for system)
result	JSONB	NULL (step output data)
created_at	TIMESTAMPTZ	NOT NULL

Index: (tenant_id, instance_id, step_index)

Event Bus Integration

• Workflows mit trigger_type=event werden automatisch gestartet, wenn das konfigurierte Event auf dem Event Bus veröffentlicht wird.
• Workflow-Schritte können Events emitten (z.B. workflow.step_completed, workflow.approved).
• Der Event Bus dispatcht Events an registrierte Workflow-Handler.

Code-Engine Workflows

Hartkodierte Workflows leben in app/workflows/code/:

• onboarding.py — User-Onboarding-Sequence (Default-Rolle, Default-Kalender, Welcome-Mail)
• plugin_sequence.py — Plugin-Activation-Sequenz (migrations → activate → notify)
• mail_sync_trigger.py — Trigger IMAP-Sync beim Account-Setup

Code-Workflows verwenden denselben workflow_instances + workflow_step_history tables für Audit-Trail.

---

8d. Monitoring & Alerting (F-INFRA-04)

Health Endpoint (erweitert)

GET /api/v1/health gibt strukturierten Status zurück:

{
  "status": "healthy",
  "checks": {
    "database": {"status": "up", "latency_ms": 2.3},
    "redis": {"status": "up", "latency_ms": 0.8},
    "storage": {"status": "up"},
    "worker": {"status": "up", "queue_depth": 3}
  },
  "version": "1.0.0",
  "uptime_seconds": 86400
}
}

Bei Degradation: "status": "degraded" mit betroffenen Checks markiert als "down".

Prometheus Metrics Endpoint

GET /api/v1/metrics (Prometheus format, admin-only auth):

• leocrm_http_requests_total{method, path, status} — Request counter
• leocrm_http_request_duration_seconds{method, path} — Histogram
• leocrm_db_pool_connections{state} — DB pool gauge (idle/used/overflow)
• leocrm_redis_pool_connections{state} — Redis pool gauge
• leocrm_arq_jobs_total{queue, status} — Job counter
• leocrm_tenant_active_sessions{tenant_id} — Active session gauge

Alerting

• DB-Connection-Pool erschöpft: Warning-Alert via structured log (ALERT: db_pool_exhausted)
• App down: Coolify healthcheck erkennt status=unhealthy → Auto-Restart
• Backup-Fehler: ARQ job failure → Notification an Admin-User + Error-Log
• Worker-Queue-Depth > 100: Warning-Log ALERT: worker_queue_backlog
• Response-Zeit > 2s: Slow-request-log mit Request-Details

Structured Logging (F-INFRA-03)

Alle Logs sind JSON-formatiert (structlog):

{"timestamp": "2026-06-28T13:00:00Z", "level": "INFO", "event": "http_request", "method": "GET", "path": "/api/v1/companies", "status": 200, "duration_ms": 45, "tenant_id": "...", "user_id": "..."}

Log-Level via LOG_LEVEL env var (DEBUG/INFO/WARNING/ERROR).

---

8e. Performance (F-PERF-01)

Requirements

• API-Response <500ms für List-Endpunkte bei 200k Datensätzen
• Full-Text-Search <500ms bei 200k Datensätzen
• Frontend: Lazy Loading, Code-Splitting, minimal bundle size

DB Indexing Strategy

Alle Such- und Filter-Felder haben PostgreSQL-Indizes:

Table	Index	Type
companies	(tenant_id, name)	B-Tree
companies	(tenant_id, industry)	B-Tree
companies	(tenant_id, billing_country)	B-Tree
companies	(tenant_id, rating)	B-Tree
companies	(tenant_id, deleted_at)	B-Tree (partial: WHERE deleted_at IS NULL)
contacts	(tenant_id, last_name)	B-Tree
contacts	(tenant_id, first_name)	B-Tree
contacts	(tenant_id, email)	B-Tree
contacts	(tenant_id, deleted_at)	B-Tree (partial)
contacts	GIN(tenant_id, body_tsv)	GIN (FTS)
mails	GIN(body_tsv)	GIN (FTS)
mails	(tenant_id, thread_id)	B-Tree
calendar_entries	(tenant_id, start_at)	B-Tree
calendar_entries	(tenant_id, due_date)	B-Tree
audit_log	(tenant_id, timestamp)	B-Tree
audit_log	(tenant_id, entity_type)	B-Tree

Query Optimization

• Pagination: Default page_size=25, max page_size=100. Keyset pagination für >10k Ergebnisse (?cursor=... statt OFFSET).
• Eager Loading: SQLAlchemy selectinload() für N:M Beziehungen (vermeidet N+1 Queries).
• Query Limits: Alle List-Queries haben LIMIT (max 100 rows per page). Full-Table-Scans verboten.
• Export als Background-Job: Export >1000 Datensätze → ARQ background job → Notification bei Fertigstellung (F-SCHED-01).
• Streaming-Response: CSV-Export streamed via StreamingResponse (kein full in-memory load).

Frontend Performance

• Code-Splitting: Route-level React.lazy() + Suspense (minimal initial bundle).
• Plugin Lazy-Load: Plugin UI components lazy-loaded.
• TanStack Query Caching: Stale time 60s, Garbage collection 5min, Background refetch on focus.
• Virtual Scrolling: Tabellen mit >1000 rows verwenden virtual scrolling (TanStack Virtual).
• Image Optimization: User-Avatar via srcset + lazy loading.

Performance Tests

# Seed 200k contacts
python scripts/seed_perf_data.py --count 200000

# Benchmark list endpoint
curl -w '%{time_total}' 'http://localhost:8000/api/v1/contacts?page=1&page_size=25' -b cookie.txt

# Benchmark FTS search
curl -w '%{time_total}' 'http://localhost:8000/api/v1/contacts?search=Mueller' -b cookie.txt
# Expected: <500ms

---

9. Test Strategy (F-TEST-01)

v1 Core Feature — F-TEST-01: Testing-Strategie — pytest + httpx (backend), Vitest + Testing Library (frontend), Playwright (E2E).

Backend (pytest + httpx)

tests/
├── conftest.py              — Fixtures: test client, test DB, auth helpers, seed data
├── test_auth.py            — Login, logout, RBAC, password reset, tenant switch
├── test_companies.py       — CRUD, search, filter, pagination, soft-delete, N:M, audit
├── test_contacts.py        — CRUD, search, filter, N:M, DSGVO delete
├── test_tenant.py          — Tenant isolation, cross-tenant access
├── test_plugins.py         — Plugin install/activate/deactivate, event bus, migrations
├── test_dms.py             — Folders, files, upload, search, links, permissions, shares
├── test_calendar.py       — Entries, appointments, tasks, kanban, recurrence, ICS
├── test_mail.py            — Accounts, IMAP mock, send, search, threading, templates
├── test_import_export.py  — CSV import/export, Excel export
├── test_notifications.py   — Create, read, unread count
├── test_health.py          — Health endpoint
├── test_ai_copilot.py     — KI-Copilot API (query, execute, RBAC enforcement, history)
├── test_workflows.py       — Workflow CRUD, instances, steps, approval/rejection, event triggers
├── test_monitoring.py     — Extended health check, Prometheus metrics, alerting
└── test_performance.py    — 200k seed + list <500ms, FTS <500ms, export streaming

Coverage Target: >80% backend (measured via pytest-cov).

Frontend (Vitest + Testing Library)

frontend/src/__tests__/
├── components/             — UI component tests
├── features/               — Feature integration tests
└── hooks/                  — Custom hook tests

E2E (Playwright)

e2e/
├── auth.spec.ts            — Login → logout
├── company-crud.spec.ts    — Create → edit → delete company
├── contact-crud.spec.ts    — Create → link to company → delete
├── search.spec.ts          — Global search
└── plugin-toggle.spec.ts   — Activate/deactivate plugin

---

10. ADRs (Architecture Decision Records)

ADR-01: PostgreSQL 16 instead of SQLite

Context: Original requirements specified SQLite. System needs 200k records + multi-user concurrent writes.

Decision: PostgreSQL 16.

Rationale: SQLite has file-level locking (~15 updates/sec under concurrency). PostgreSQL uses MVCC (~1500 updates/sec). Full-Text-Search via tsvector + GIN indexes. JSONB support for flexible fields.

Alternatives: SQLite+WAL (insufficient concurrency), MySQL (no native FTS in same way), MongoDB (no relational integrity).

ADR-02: ARQ instead of Celery for Background Jobs

Context: Background jobs needed for exports, mail-sync, reminders, backups.

Decision: ARQ (async Redis-based job queue for Python).

Rationale: Native async (works with FastAPI/asyncio). Simpler than Celery. Redis already in stack for caching. No separate broker needed. Lightweight.

Alternatives: Celery (heavier, needs RabbitMQ or Redis as broker), FastAPI BackgroundTasks (no retry, no status tracking, no persistence).

ADR-03: Plugin System via Python Entry Points + Manifest

Context: Module system needed for Mail, Calendar, DMS, Tags as plugins.

Decision: Built-in plugins in app/plugins/builtins/ with manifest-driven registration. No dynamic external plugin loading in v1.

Rationale: All v1 plugins are built-in (shipped with the code). External plugin installation is post-MVP. Manifest provides metadata for UI registration and lifecycle hooks. Simpler and safer than dynamic imports.

Alternatives: Dynamic pip-installable plugins (complex, security risk for v1), Microservices (overkill for small team).

ADR-04: TanStack Query for Server State

Context: Frontend needs to manage API data with caching, loading states, error handling.

Decision: TanStack Query (React Query v5).

Rationale: Built-in caching, optimistic updates, background refetching, request deduplication. Eliminates need for global state manager for API data. Works with React 18 Suspense.

Alternatives: Redux Toolkit Query (heavier), SWR (less features), manual fetch+useEffect (no caching).

ADR-05: Session-based Auth instead of JWT

Context: Requirements specify session-based auth with HttpOnly cookies.

Decision: Server-side sessions in Redis (primary store for fast lookup), session ID in HttpOnly+Secure+SameSite=Strict cookie. PostgreSQL sessions table retains session records as an audit trail (created_at, expires_at, user_id, tenant_id).

Rationale: No JWT complexity (refresh tokens, rotation). Server can invalidate sessions immediately (delete Redis key). CSRF protection via SameSite=Strict + Origin validation. 8h timeout (Redis TTL). Redis for fast session lookup on every request; PostgreSQL sessions table as immutable audit trail for security analysis. Session validation flow: check Redis key → if expired/missing, check PostgreSQL audit record for forensic logging → deny request.

Alternatives: JWT (harder to invalidate, token leakage risk), OAuth2 (overkill for internal CRM).

ADR-06: Soft-Delete with deleted_at Column

Context: Companies and contacts need recoverable deletion.

Decision: deleted_at TIMESTAMPTZ NULL column. NULL = active, NOT NULL = soft-deleted.

Rationale: Simple, queryable, recoverable. ORM auto-filters deleted_at IS NULL on list endpoints. DSGVO hard-delete removes row entirely and logs to deletion_log.

Alternatives: Separate trash table (more complex), status column (less clear semantics).

---

Open Questions

1. SMTP Provider (Production): Welcher Provider? → Deployment-Phase.
2. Backup Strategy: pg_dump + S3 or Coolify volume backup? → Deployment-Phase.
3. OnlyOffice: Separate container or embedded? → Separate container confirmed in docker-compose.
4. Frontend Calendar Library: FullCalendar (heavy) or custom React implementation? → Implementation-Phase.

---

Documentation (F-DOC-01)

v1 Core Feature — F-DOC-01: Dokumentation — README.md, docs/admin-guide.md, docs/api-overview.md, Swagger UI at /api/v1/docs.

Handoff

• Architecture status: COMPLETE
• Task graph status: PENDING (see task_graph.json)
• AGENTS.md status: PENDING
• Open questions: 4 (listed above, non-blocking for implementation)
• Ready for implementation: NO (pending quality_reviewer review + task graph + AGENTS.md)