Data Model

This page is the reference for Clapilot's PostgreSQL schema: the core tables, the constraints worth knowing before writing queries or migrations, and the file-path/sync contracts that connect rows to the shared workspace. It is written for developers touching db/migrations/ or any data-access code.

Canonical schema lives in db/migrations/*.sql and is applied by scripts/db-migrate.mjs (or startup auto-migrate via entrypoint.sh). Native runtime tables (agent_*) are documented in depth in the Clapilot-Agent docs; this page focuses on the app-owned schema.

Product telemetry

product_events stores privacy-aware product usage metadata for pilot reporting. It records event name, route, module, optional internal user/session IDs, coarse platform metadata, duration, success/failure state, error code, and sanitized properties_json.

The table must not store chat, email, document, tenant, phone, token, secret, or sensitive free-text content. Use POST /api/telemetry/events or recordProductEvent() so server-side validation and property filtering are applied consistently.

Core tables

Column-level note: user_profiles.agent_pet_key stores the signed-in user's selected chat activity style. It defaults to bubbles, which renders the compact animated three-dot bubble; users can explicitly select clapilot, pilot_rooster, or a registered custom Pet instead. Web and Apple chat clients share this behavior while generic loading indicators remain unchanged.

High-value constraints

• app_settings.id = TRUE enforces singleton row
• aufgaben.board_id references aufgaben_boards.id (required)
• tasks can be aggregated across all boards in the Aufgaben UI, but each task still belongs to exactly one board row
• aufgaben_boards.visibility_scope separates shared boards from per-user private boards; private rows carry owner_user_id, and task reads/mutations only expose shared boards plus the signed-in user's own private boards. The built-in private board is named Privat.
• aufgaben and calendar_entries share a zero-risk sync model: external_id, sync_source, sync_status, and linked_entities. Local Clapilot records default to sync_source='clapilot' and sync_status='local'; imported Outlook/Microsoft and Google calendar rows are mirrored records, not destructive writes back to the source system.
• aufgaben.source_type includes email, document, manual, and issue_reporter; Issue Reporter tasks use the report or Hub issue as source traceability and are assigned to the agent when they target the Agent Orchestrator board.
• hub_reported_issues.app_key stores the normalized repository basename used by Issue Reporter routing and review filtering. Existing rows and callers without an app parameter default to clapilot; the full repository and task board remain resolved through app_settings.agent_orchestrator_repo_automation_config.
• aufgaben includes optional CRM/follow-up fields for acquisition, partner, talent, and deal workflows: naechster_schritt, kontaktperson, organisation, branche, athlet, projekt, deal_name, kommunikationskanal, letzter_kontakt_at, letzter_kontakt_notiz, wiedervorlage_at, abschlussart, abschlussnotiz, tags, umsatzrelevant, and deal_wert. These fields stay nullable/defaulted so existing simple tasks remain compatible; tags is indexed with GIN, follow-up dates are indexed with task status, and revenue/deal value has a dedicated sort/filter index.
• aufgaben.schedule_mode remains for legacy task-board scheduling rows during migration/import
• scheduled_tasks.provider is openclaw for native-job-backed rows and clapilot for direct event-triggered rows (openclaw is a legacy-named enum value kept for compatibility; those rows execute in the native clapilot-agent job layer)
• scheduled_tasks.trigger_kind in schedule|new_mail|new_document|new_calendar_entry|webhook
• scheduled_tasks.specialized_agent_id optionally references specialized_agents.id; when present, the automation executes inside that specialist's isolated runtime scope instead of the generic main-agent automation path
• scheduled_tasks.created_by records the human creator for audit; scheduled_tasks.service_principal_id independently selects the runtime identity. Team-facing and ownerless automations use the built-in global_team_service principal, while personal automations execute with their creator's user context. notify_target_json remains a separate delivery decision.
• scheduled_tasks.provider_snapshot.mailboxScope stores all|personal|agent for new_mail event automations; missing values are treated as all
• scheduled_tasks.provider_snapshot.payload.specializedAgentId mirrors the current specialist binding into direct/event automation metadata and native job sync snapshots
• scheduled_tasks.provider_snapshot.webhookToken stores the bearer-style token for webhook trigger URLs; idx_scheduled_tasks_webhook_token keeps tokens unique
• scheduled_task_event_queue durably stores webhook request envelopes, delivery/hash dedupe keys, the pre-reserved agent_runs foreign key, retry state, locks, and delivered_at. reserve_scheduled_task_webhook_run(...) creates the run and queue row in one transaction. agent_runs.actor_user_id snapshots the verified initiating user per execution; unlike mutable Team Chat session state, it is safe for audit and personal-export ownership decisions.
• scheduled_tasks.workflow_config_json stores the optional node-editor graph (schema_version, trigger/agent/output nodes, edges, and positions). Compatibility columns still hold the primary trigger/agent/output; additional event/webhook trigger nodes can dispatch the same automation, and output nodes can fan out result delivery to multiple validated targets. Agent node config supports specialized_agent_id, model, prompt (per-step work order, max 8000 chars), and skill_keys (up to 12 installed-skill keys injected additively into that step's run — for default and specialized agents alike). Multiple agent nodes form a linear chain along agent -> agent edges (normalization drops fan-in/fan-out beyond one edge per node and cycle-creating edges); the first chain agent stays mirrored into the flat prompt/specialized_agent_id/model columns, downstream agents are graph-only. Output nodes wired from an intermediate agent deliver that step's result; unwired outputs deliver the final chain result.
• scheduled_tasks.schedule_mode in once|interval|weekdays|cron for schedule-backed rows and NULL for event-triggered rows
• scheduled_tasks.provider_job_id maps to the native job id for schedule-backed rows and to the direct automation session key for event-triggered rows
• webhook runs append :event:<queue-id> to the task session key and disable stored-history replay, preventing context from accumulating between unrelated deliveries while retaining each event's auditable run transcript
• scheduled_tasks.notify_target_json stores the assigned automation target (main_session, optional main_session.sessionId for a concrete web chat session, team_chat for Teamchat #general, optional team_chat.roomId for a selected Teamchat channel/group, or approved channel_approval target). Rows without an explicit target are backfilled to main_session for user-owned automations and team_chat for system-owned automations
• scheduled_tasks.notify_with_result is a legacy compatibility column and is no longer part of the active automation contract
• livestream_assets.status follows draft|generating|review_ready|approved|queued|playing|played|archived|failed; queueing requires approved or queued plus a non-empty media_path
• Live Stream Studio also exposes scanned video files from CLAPILOT_MEDIA_OUTPUT_DIR as a filesystem-backed media library; dropping a file into the queue creates or re-approves an uploaded livestream asset that points at that file. Deleting a media-library file is blocked while it is pending or playing, removes the filesystem file, archives linked assets, and clears their media_path.
• livestream_queue_items.loop_count stores how many times a row should appear in each loop playlist cycle. 0 disables looping and the row is marked played after its normal playback; values 1..100 keep the row active by returning it from playing to pending after each cycle. If the streamer restarts ungracefully and leaves loop rows in playing, the next stale-heartbeat recovery moves them back to pending so the selected loop queue survives restarts until an admin skips/removes it. loop_enabled remains as the UI/tool compatibility boolean derived from loop_count > 0; loop_remaining is retained only for migration compatibility.
• livestream_channels.stream_desired_state is the operator intent (running|stopped), while streamer_status and streamer_heartbeat_at are written by the separate clapilot-streamer process
• livestream_channels.stream_key_* stores the YouTube stream key encrypted with CLAPILOT_LIVESTREAM_SECRET or the normal runtime secret fallback; APIs only return stream_key_hint
• livestream_channels.agent_topup_* controls the seconds-based queue refill loop. The streamer writes wake timing/status and calls /api/internal/livestream/topup when queue buffer is below target.
• livestream_channels.youtube_chat_* controls YouTube chat ingest. The streamer calls /api/internal/livestream/youtube-chat/poll, which uses the linked Google OAuth user to poll the active broadcast chat and stores deduplicated messages plus derived audience requests.
• media_generation_provider_configs.api_key_* stores the shared Kie.ai key encrypted with CLAPILOT_MEDIA_GENERATION_SECRET or the normal runtime secret fallback; Gemini media generation reuses the existing google_gemini Runtime provider key, and xAI Grok Video reuses the existing xai Runtime provider OAuth/API credential.
• video_studio_ai_projects.status follows draft|storyboard_generating|storyboard_ready|generating|concatenating|ready|failed; each project stores the exact provider_slug, model, and the storyboard_model that produced its storyboard.
• video_studio_ai_scenes.kind is ai|html; status follows pending|frame_ready|clip_generating|clip_ready|failed. (project_id, scene_index) is unique, project deletion cascades to scenes, and generated image/video deletion clears the corresponding optional reference instead of deleting the scene.
• video_studio_ai_project_characters has primary key (project_id, character_id) and cascades when either side is deleted.
• app_settings.video_storyboard_model optionally selects the native/compatible text model used for strict Video Studio storyboard JSON; an empty value falls back to agent:main.
• chat_nachrichten.message_origin in user_turn|assistant_automation|assistant_specialist|assistant_specialist_delegated|assistant_async_callback
• chat_rooms stores /team-chat channels, group DMs, user direct rooms, and specialist direct rooms; specialist direct room ids use agent-dm:<user-id>:<specialized-agent-handle> and carry a one-human membership row for access control. Channel rows also store the opt-in agent_to_agent_enabled flag plus main_agent_enabled and main_agent_reply_mode. Existing and new channels default to an invited main agent with all_messages; admins may remove it or require explicit mentions. Direct and group-DM rooms do not use these channel controls.
• chat_room_members is the authoritative room-scoped access list for people. general is backfilled and auto-joined for compatibility; every other channel contains only its creator and explicitly invited people. Leaving/removal retains the row with left_at for safe re-invitation.
• chat_group_room_settings.model stores the room's main-agent runtime model override; team rooms no longer use agent:<specialized-agent-handle> model refs as hidden specialist defaults.
• chat_room_specialized_agents stores room-scoped specialist invitations with reply_mode=mention_only|all_messages; only invited specialists can be mentioned in that room, and all_messages specialists receive unmentioned room turns as detached specialist tasks. The main agent uses the same reply-mode vocabulary through its chat_rooms columns. In an agent-conversation-enabled channel the specialist modes govern agent-authored messages, while lineage, depth, source-deduplication, and the shared reaction count are persisted in chat_group_messages.message_meta.agentConversation* fields.
• specialized_agents.handle is unique and backs the shared @agentHandle mention surface in personal and team chat
• specialized_agents.skill_keys and specialized_agents.allowed_tool_names are persisted separately so skill injection and runtime tool exposure stay independently configurable
• specialized_agents.default_model_ref optionally pins one provider/model for that specialist; when empty, the runtime falls back to the active chat/session model before using the global provider default
• specialized_agent_embed_deployments.specialized_agent_id is unique, so each specialist currently has at most one external/embed deployment
• specialized_agent_embed_deployments.public_slug is unique and is intended to back future public/embed routing for that specialist
• specialized_agent_embed_deployments.runtime_access_mode is forced to public_safe; public website/API-key specialist runs keep public guardrails and cannot expose the normal internal specialist runtime envelope
• specialized_agent_embed_deployments.public_allowed_tool_names is the separate public website/API-key tool allowlist. It is filtered to public-safe tools at save/run time and does not inherit from specialized_agents.allowed_tool_names; an empty array means public runs get zero tools.
• specialized_agent_embed_deployments.service_principal_id references a restricted agent_service_principals row dedicated to that public specialist deployment
• specialized_agent_embed_api_keys.key_hash is unique and only the hash is persisted; raw publishable keys are returned once at creation time and never stored in plaintext
• specialized_agent_embed_api_keys.session_id maps each key to a stable external specialist session fallback used when public/embed or OpenAI-compatible clients omit their own session id
• specialized_agent_embed_api_keys.deployment_id scopes each key to exactly one specialist deployment; keys can be soft-revoked via revoked_at
• specialized_agent_embed_request_logs persists public website-agent request outcomes, including blocked safety/rate-limit decisions, so the public embed can enforce per-IP message/session limits without relying on signed-in chat history
• specialized_agent_tasks persists detached specialist work for both direct @agentHandle mentions and main-agent delegation, including task status, specialist target, visible pending message ids, scoped chat linkage, stored attachments, specialist output/error, and whether an async main-agent callback should be posted after completion
• aufgaben assignment rules:
  • zugewiesen_typ in user|agent or NULL
  • zugewiesen_typ='user' requires zugewiesen_an (users.id)
  • zugewiesen_typ='agent' requires zugewiesen_an IS NULL
• agent_email_jobs.status in pending|processing|processed|failed
• agent_learning_objects.status controls prompt eligibility; only approved/active/promoted objects can be considered, and retrieval still enforces expiry, validity windows, latest approval state, visibility scope, subject/session/channel matching, and a per-run token budget before writing learning.used_in_prompt audit rows. The opt-out activation policy appends auto_approved_by_policy for canonical, evidence-backed facts and preferences. Low-risk procedure proposals and other exceptions start in review states such as needs_evidence. Admin curation and the model-backed Learning Curator append decision rows and audit events rather than overwriting approval history. The curator records a content hash and model provenance so only new or changed facts are checked, while manual decisions remain authoritative.
• Memory v2 separates agent_memory_sources, canonical agent_memory_assertions, exact agent_memory_assertion_evidence, append-only reviews, explicit conflicts, versioned embedding generations/vectors, retrieval feedback, and the idempotent ingestion outbox. Assertion identity/semantics are immutable; corrections create linked revisions and terminal/tombstoned identities cannot be silently recreated. Safe fact/preference assertions can be policy-approved automatically, while conflicts, corrections, policies, procedures, weak evidence, and other exceptions receive Learning review objects. Only approved/current/audience-visible assertions are eligible for prompt retrieval, active durable-memory projections, prompt-eligible Learning state, and the automatically maintained Knowledge Graph. agent_memories and every graph build/entity/claim/edge/evidence row carry instance_key; write guards and read filters prevent cross-instance projection or recall. User profiles are built with the same instance filter; only default uses the legacy persisted profile cache, while other instances are computed on demand. Wiki proposal state remains separately human-reviewed.
• email_drafts.status in draft|sent|discarded
• email_message_cache.detected_language, email_drafts.source_language, email_drafts.reply_language, email_thread_automations.source_language, and email_thread_automations.reply_language use the supported UI language keys de|en|it
• wiki_pages.slug is unique kebab-case and can be used anywhere a Wiki page id is accepted; source_type is manual|agent|dream|imported, status is active|archived, tags is GIN-indexed, and exact source/assertion/evidence refs preserve provenance. semantic_signature_version='wiki-semantic-v1' identifies the shared web/native/backfill dedup contract; migration 196 preserves previous derived values in wiki_semantic_signature_migration_audit for reversible restoration.
• chat_sessions.user_id scopes sessions per user; chat_sessions.is_main marks the single protected main personal session per user; chat_sessions.is_pinned persists user-pinned custom sessions for the web session lists; chat_sessions.title_manually_set prevents first-turn auto-title generation from overwriting manual renames; chat_nachrichten.session_id binds each message pair to a session
• update triggers maintain updated_at
• unified_sync_items.item_kind is task|event; source is clapilot|outlook|clapilot_synced; event rows require start_at, end_at, and end_at > start_at.
• unified_sync_items.external_provider/external_id are either both set or both empty, and (user_id, item_kind, external_provider, external_id) is unique for imported Outlook rows so mirror imports can upsert without duplicates.
• unified_sync_items.linked_entities is a JSON array for references such as source email, document, Mandant, match, task, or event records. Export and future sync flows should read this canonical model rather than reconstructing relationships from UI payloads.
• calendar_entries can store Google sync metadata in google_event_id, google_sync_source, google_calendar_id, google_etag, google_synced_at.
• calendar_entries can also store Microsoft sync metadata in microsoft_event_id, microsoft_sync_source, microsoft_calendar_id, microsoft_etag, and microsoft_synced_at.
• calendar_entries.conference_url and conference_provider store provider-agnostic online meeting links for Kalender detail views; Google Meet rows mirror google_conference_url, and Microsoft Calendar sync stores Teams join URLs from Graph when available.
• calendar_entries.external_id mirrors the active provider event id for imported calendar records, with sync_source='outlook' for Microsoft/Outlook rows and sync_source='google' for Google rows.
• google_user_integrations stores token rows per (user_id, provider) and keeps token state for re-connect and refresh.
• microsoft_user_integrations stores token rows per (user_id, provider) and keeps token state for re-connect and refresh.
• x_user_integrations stores token rows per (user_id, provider) and keeps token state plus X profile identity for re-connect and refresh.
• calendar_entries.google_event_id is unique per user for synced Google event rows (google_sync_source='google').
• calendar_entries.microsoft_event_id is unique per user for synced Microsoft event rows (microsoft_sync_source='microsoft').
• app_settings stores Google OAuth client credentials (google_oauth_client_id, google_oauth_client_secret), Microsoft OAuth app registration values (microsoft_oauth_client_id, microsoft_oauth_client_secret, microsoft_oauth_tenant_id), and X OAuth client credentials (x_oauth_client_id, x_oauth_client_secret); redirect targets are derived from public_base_url as /api/integrations/google/oauth/complete, /api/integrations/microsoft/oauth/complete, and /api/integrations/x/oauth/complete.
• app_settings.mandant_profile_web_crawl_enabled is the admin-owned feature toggle for Mandanten website/logo/profile enrichment.
• Mandanten web-profile enrichment treats a name as only the first identity signal. Automatic master-data writes require an organization plus at least one independent match (non-generic email domain, location, industry/company context, or an already confirmed website). Private-person and otherwise ambiguous results are stored only in mandanten.enrichment_suggestion with confidence, evidence, and source for explicit review; accepting a suggestion fills only empty website_url, logo_url, and profile_image_url fields and never overwrites existing values.
• app_settings.developer_mode_enabled is the admin-owned feature toggle for the hidden root-level Settings -> Developer area and defaults to false. It gates instance API-key management and API-key authentication, in-instance E2E suites that may use live runtime credentials and subscription-backed model access, and developer-only modules such as Agent Orchestrator and the web Terminal.
• instance_api_keys.allowed_repositories stores the immutable full-repository allowlist for isolated issue_reports:write public-client keys. instance_api_key_rate_limit_buckets stores short-lived atomic per-key scope counters for the Memory, Tool Execution, and stateless Inference APIs and per-key/hashed-IP counters for public issue intake; buckets older than two days are pruned during successful requests.
• instance_api_keys stores instance-level external API credentials. key_hash is the SHA-256 digest of a high-entropy clp_live_... secret that is returned only once; key_prefix is safe display metadata. scopes is the explicit permission list, while expires_at, revoked_at, and last_used_at support lifecycle control and operational review. Supported private scopes include subscription_usage:read, the creating user's notifications:read inbox, creator-bound memory:read / memory:write, stateless provider passthrough through inference:execute, and high-privilege creator-bound tools:execute; the isolated public issue_reports:write scope uses clp_public_... keys.
• user_notifications is the durable per-recipient message-notification inbox shared by APNs delivery and the polling API. Rows use the same chat_response / team_chat_message, title/body, message, session/room, sender, and author metadata delivered to Apple devices. The (user_id, clapilot_type, message_id) uniqueness boundary makes producer retries idempotent.
• hub_instance_subscription_usage_snapshots stores the latest normalized subscription-usage snapshot reported for each connected instance host. checked_at comes from the sender snapshot, while Hub-controlled received_at drives the 45-minute stale indicator. The JSON payload contains display-safe provider windows, plan/account labels, errors, and credit summaries; subscription credentials and arbitrary metadata are rejected by both sender and Hub normalization.
• subscription_usage_hub_sync_state is the one-row spoke-side lease and outcome record for Hub usage reporting. It enforces the 15-minute successful-report interval and five-minute failure retry without adding another cache or credential store.
• agent_provider_configs supports the first-class elevenlabs provider type (migration 269) for TTS, STT, and the Video Studio speech-to-speech voice changer; it uses the normal encrypted API-key columns and defaults to https://api.elevenlabs.io/v1.
• agent_provider_configs supports the first-class ollama provider type. Its normal encrypted API-key columns hold a versioned Ollama credential bundle containing the optional Cloud API key and optional ollama.com browser session used for Subscription Usage. Public/admin reads expose only separate presence flags and masked hints; the plaintext browser session is never returned.
• app_settings.document_processing_use_native_pdf_tool may still exist as a transition artifact, but document inbox processing no longer uses a user-facing PDF mode toggle.
• document_folders.name is unique case-insensitively per parent folder, so sibling names cannot collide but the same name can exist in different branches.
• document_folders.parent_id optionally links a folder to another document_folders.id, enabling nested document trees.
• document_folders.visibility_scope and dokumente.visibility_scope separate shared documents from per-user private documents; private rows carry owner_user_id. The built-in personal folder is named Persönlich.
• dokumente.folder_id optionally links a document to document_folders.id; deleting a folder lifts its documents and child folders to the deleted folder's parent instead of deleting them.
• dokumente.original_file_name, dokumente.suggested_file_name, and dokumente.auto_renamed preserve the upload filename, the post-analysis filename suggestion, and whether the stored file path was actually renamed when the document indexer detects a generic name such as Download_1.pdf, scan.pdf, or IMG_1234.jpg. After content extraction, the canonical schema is YYYY-MM-DD_Type_Counterparty_Amount-or-Subject.ext; unsafe characters and umlauts are normalized. High-confidence suggestions rename the stored file and title, while lower-confidence suggestions remain reviewable metadata. Concurrent or pre-existing targets receive deterministic _1, _2, … suffixes under a database advisory lock, so neither archive paths nor files are overwritten.
• dokumente.source_id is optional and unique together with source_type when present. Email attachment imports use source_type='email_attachment' plus a stable mailbox/message/attachment key so opening an email detail can safely create or reuse the linked Dokumente row.
• dokumente.amount_cents, dokumente.currency, and dokumente.datum are canonical document tax fields extracted during processing. Steuer-Manager sources accessible rechnung, beleg, and ust rows from dokumente by selected Mandant and period; uploaded files also become dokumente rows before generation.
• tax_manager_generations stores the owner, optional Mandant, source (mandant|upload), output kind (ustva|euer), normalized period, lifecycle status (draft|extracting|extracted|finalized|failed), agent task, deterministic totals/Kennzahlen/Prüfhinweise JSON, and rendered HTML. UStVA is month/quarter only; EÜR is year only at the API layer.
• tax_manager_generation_documents snapshots each source filename and stores status (pending|classified|unreadable|excluded|duplicate), direction, EÜR category, UStVA Kennzahl, integer-cent amounts, VAT rate, date, counterparty/invoice number, AfA marker, confidence, issues, extraction source, and notes. (generation_id, document_id) is unique while the document link is present; generation deletion cascades, while document deletion retains the snapshot with a null link.
• Tax-manager migrations 001 through 004 and their legacy tables remain for deployed-data compatibility, but the active module handler and UI use only tax_manager_generations and tax_manager_generation_documents.
• document_mandanten stores additional Mandanten related to a document. dokumente.mandant_id remains the primary compatibility link, while document_mandanten supports recipient, sender, issuer, mentioned, and related parties so outgoing invoices can stay primarily assigned to the customer while retaining the sender/issuer context.
• document_workflow_automations (one row per dokument_id) is the document-side analogue of email_thread_automations: after extraction, src/lib/document-auto-flow.ts analyzes the document and persists a summary, context_label, document_kind, matched mandant_id, the executed actions in action_payload (tasks array, calendar, follow_up, highlights), and a content_signature so the model only re-runs (and tasks are only re-created) when extracted content changes. status is analyzed|no_action|executed|failed. Actions are auto-executed, not gated: each detected task is created in aufgaben (source_type='document'), or, when it strongly matches an existing open task for the same client / a task already linked to the document, that task is updated instead of duplicated (action_kind distinguishes created vs updated). A single document can produce multiple tasks (action_payload.tasks[]); task_id holds the primary one, calendar_event_id and follow_up_task_id back-reference the calendar/follow-up rows. Linked aufgaben rows use source_type='document' and linked calendar_entries rows use source_origin='document'.
• demo_seed_runs.status in running|completed|failed
• demo_seed_records stores record_kind, optional record_id, and external_ref metadata so resets can target only tagged demo data instead of wiping unrelated live data

Google Drive sync contract

• Synced Drive files are written under:
  • ${GOOGLE_DRIVE_STORAGE_ROOT || /app/workspace/mandanten}/google/drive/{user_id}/
  • dedicated Agent Google account files use the isolated google/drive/{user_id}/agent/ subtree
• If a targetPath is provided to the sync endpoint, files are placed in the requested subfolder.
• Downloaded files are stored with restricted local permissions (best-effort) so access is limited to the runtime user.
• Background imports also upsert dokumente rows with relative paths like google/drive/{user_id}/..., so the documents UI can expose them in the virtual Google Drive folder.

Microsoft 365 file sync contract

• Synced OneDrive files are written under:
  • ${MICROSOFT_DRIVE_STORAGE_ROOT || /app/workspace/mandanten}/microsoft/onedrive/{user_id}/
• If a targetPath is provided to the sync endpoint, files are placed in the requested subfolder.
• The sync endpoint walks nested OneDrive folders, not only root-level files, until the requested max_files limit is reached.
• Downloaded files are stored with restricted local permissions (best-effort) so access is limited to the runtime user.
• Imports also upsert dokumente rows with relative paths like microsoft/onedrive/{user_id}/..., so the documents UI can expose them in the virtual Microsoft 365 folder.

Background sync (Google)

• Runtime worker: scripts/google-sync-poller.mjs
• Worker runs when GOOGLE_SYNC_ENABLED=true (default in compose).
• Calendar and Drive are enabled per user through google_user_integrations.calendar_enabled / drive_enabled, managed from Settings -> App Verbindungen.
• google_user_integrations.account_type separates the user's Workspace connection from the dedicated agent Workspace identity. The agent row can enable Calendar, Gmail, Drive, Docs/Sheets, and Meet independently; calendar_entries.google_account_type records the credential that owns a Google event so later ETag-protected patches use the same account.
• Docs, Sheets, and Contacts also have per-user service toggles in google_user_integrations, even though the current background worker only acts on Calendar and Drive.
• Scope constraints apply: sync actions only run when the user-granted OAuth scopes cover the required Google API scopes.

Microsoft 365 integration behavior

• Microsoft 365 currently ships as a user-managed OAuth integration under Settings -> App Verbindungen.
• Mail, Calendar, OneDrive, Word, Excel, and Contacts are enabled per user through microsoft_user_integrations.*_enabled; OneDrive defaults to enabled when the user has granted Files.Read.
• Enabled Microsoft Calendar imports events into calendar_entries when the calendar view loads or when the calendar service toggle is enabled.
• The current implementation exposes authenticated HTTP endpoints for connect/list/sync flows, but does not yet ship dedicated live/native Microsoft agent tools; chat receives connection-status context only.

X integration behavior

• X currently ships as a user-managed OAuth integration under Settings -> App Verbindungen.
• The current implementation stores per-user token state and basic profile identity in x_user_integrations.
• The current implementation exposes authenticated HTTP endpoints for connect/disconnect/status flows, but does not yet ship dedicated live/native X agent tools; chat and native context reads receive connection-status context only.

Document path contract

• dokumente.file_path is relative to /app/workspace/mandanten
• valid examples:
  • _inbox/file.pdf
  • <mandant>/dokumente/<typ>/file.pdf
• invalid examples:
  • absolute paths
  • mandanten/... prefixed values

Implementation: src/lib/document-paths.ts and document APIs.