sandrodach
50b017f387
MindSift CI & Docker Release / Build, Test in Container & Push (push) Successful in 1m0s
374 lines
17 KiB
Markdown
374 lines
17 KiB
Markdown
# 🗄️ Datenbank-Design & SQLite Schema
|
||
|
||
MindSift verwendet als persistente Datenspeicherung eine lokale SQLite-Datenbank (`/data/mindsift.db`). Dieses Dokument beschreibt das relationale Datenmodell, Tabellenstrukturen, Indizes, Fremdschlüsselbeziehungen und das Migrationskonzept.
|
||
|
||
---
|
||
|
||
## 📊 1. Entity-Relationship-Diagramm (ERD)
|
||
|
||
```mermaid
|
||
erDiagram
|
||
users ||--o{ thoughts : "erstellt"
|
||
users ||--o{ documents : "besitzt"
|
||
users ||--o{ lists : "besitzt"
|
||
users ||--o{ task_lists : "besitzt"
|
||
users ||--o{ api_keys : "besitzt"
|
||
users ||--o{ team_members : "ist Mitglied in"
|
||
|
||
teams ||--o{ team_members : "besteht aus"
|
||
teams ||--o{ task_list_shares : "erhält Zugriff"
|
||
teams ||--o{ list_shares : "erhält Zugriff"
|
||
teams ||--o{ doc_team_shares : "erhält Zugriff"
|
||
teams ||--o{ workspace_folder_shares : "erhält Zugriff"
|
||
|
||
task_lists ||--o{ thoughts : "gruppiert"
|
||
task_lists ||--o{ task_list_shares : "wird geteilt mit"
|
||
|
||
lists ||--o{ list_items : "enthält"
|
||
lists ||--o{ list_shares : "wird geteilt mit"
|
||
|
||
documents ||--o{ blocks : "besteht aus"
|
||
documents ||--o{ block_tags : "indiziert"
|
||
blocks ||--o{ block_tags : "verknüpft"
|
||
documents ||--o{ doc_shares : "geteilt mit Benutzern"
|
||
documents ||--o{ doc_team_shares : "geteilt mit Teams"
|
||
documents ||--o{ doc_user_folders : "eigene Platzierung"
|
||
documents ||--o{ doc_pins : "Favorit von"
|
||
|
||
workspace_shared_folders ||--o{ workspace_folder_shares : "wird geteilt mit"
|
||
workspace_shared_folders ||--o{ workspace_folder_mounts : "eingehängt bei"
|
||
workspace_shared_folders ||--o{ documents : "enthält"
|
||
```
|
||
|
||
---
|
||
|
||
## 📑 2. Detaillierte Tabellen-Spezifikation
|
||
|
||
### 2.1 Benutzerverwaltung, System & Sicherheit
|
||
|
||
#### `users`
|
||
Verwaltet Konten, Rollen, Multi-Channel Alarmziele und Benutzerpräferenzen.
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id TEXT PRIMARY KEY, -- Präfix "usr_..."
|
||
email TEXT UNIQUE NOT NULL, -- E-Mail-Adresse
|
||
username TEXT NOT NULL DEFAULT '', -- Eindeutiger Login-Name
|
||
password_hash TEXT NOT NULL, -- bcrypt Hash
|
||
display_name TEXT NOT NULL, -- Anzeigename
|
||
avatar_url TEXT NOT NULL DEFAULT '', -- Profilbild URL oder leer
|
||
role TEXT NOT NULL DEFAULT 'member', -- 'admin' | 'member'
|
||
is_active INTEGER NOT NULL DEFAULT 1, -- 1 = aktiv, 0 = gesperrt
|
||
token_version INTEGER NOT NULL DEFAULT 1, -- Erhöht bei Passwortwechsel/Rechteentzug zur Token-Invalidierung
|
||
alarm_channels TEXT NOT NULL DEFAULT 'discord', -- Kommagetrennt: 'email', 'discord', 'webhook'
|
||
alarm_email TEXT NOT NULL DEFAULT '', -- Ziel-E-Mail für Alarme
|
||
discord_webhook_url TEXT NOT NULL DEFAULT '', -- Discord Webhook URL
|
||
custom_webhook_url TEXT NOT NULL DEFAULT '', -- HTTP POST JSON Webhook URL
|
||
notify_before_minutes INTEGER NOT NULL DEFAULT 30, -- Vorwarnzeit in Minuten
|
||
created_at TEXT NOT NULL, -- ISO 8601 String
|
||
updated_at TEXT NOT NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
||
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
||
```
|
||
|
||
#### `system_settings`
|
||
Globale Instanz-Konfiguration (Key-Value-Tabelle).
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS system_settings (
|
||
key TEXT PRIMARY KEY, -- 'sharing_mode', 'allow_registration', 'instance_name', 'smtp_...'
|
||
value TEXT NOT NULL
|
||
);
|
||
```
|
||
|
||
#### `user_invites`
|
||
Einladungscodes für kontrollierte Benutzer-Registrierungen.
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS user_invites (
|
||
id TEXT PRIMARY KEY, -- Präfix "inv_..."
|
||
code TEXT UNIQUE NOT NULL, -- Format "MS-XXXXXXXX"
|
||
email TEXT DEFAULT '', -- Optional: an bestimmte E-Mail gebunden
|
||
role TEXT NOT NULL DEFAULT 'member', -- Rolle des neuen Kontos ('member', 'admin')
|
||
created_by TEXT NOT NULL, -- Ersteller User-ID
|
||
used_by TEXT DEFAULT '', -- Eingelöste User-ID
|
||
expires_at TEXT NOT NULL, -- Gültig bis (ISO 8601)
|
||
created_at TEXT NOT NULL
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_user_invites_code ON user_invites(code);
|
||
```
|
||
|
||
#### `api_keys`
|
||
Persönliche API-Schlüssel für externe Skripte, Smarthome und Ingestion.
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS api_keys (
|
||
id TEXT PRIMARY KEY, -- Präfix "key_..."
|
||
user_id TEXT NOT NULL,
|
||
name TEXT NOT NULL, -- Bezeichnung (z.B. "Home Assistant")
|
||
key_prefix TEXT NOT NULL, -- Erste 12 Zeichen im Klartext für Anzeige
|
||
key_hash TEXT UNIQUE NOT NULL, -- SHA-256 Hash des kompletten Schlüssels
|
||
scopes TEXT NOT NULL DEFAULT 'ingest,read',
|
||
last_used_at TEXT NOT NULL DEFAULT '',
|
||
created_at TEXT NOT NULL,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
```
|
||
|
||
#### `revoked_tokens`
|
||
Sperrliste für explizit abgemeldete JWTs (Blacklist bis zum regulären Token-Ablaufdatum).
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS revoked_tokens (
|
||
token_hash TEXT PRIMARY KEY, -- SHA-256 Hash des Tokens
|
||
user_id TEXT NOT NULL, -- Benutzer-ID
|
||
expires_at TEXT NOT NULL, -- ISO 8601 Ablaufdatum
|
||
revoked_at TEXT NOT NULL -- ISO 8601 Sperrzeitpunkt
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_expires ON revoked_tokens(expires_at);
|
||
CREATE INDEX IF NOT EXISTS idx_revoked_tokens_user ON revoked_tokens(user_id);
|
||
```
|
||
|
||
---
|
||
|
||
### 2.2 Gruppen & Organisations-Teams
|
||
|
||
#### `organizations` & `teams` & `team_members`
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS organizations (
|
||
id TEXT PRIMARY KEY, -- Präfix "org_..."
|
||
name TEXT NOT NULL,
|
||
owner_id TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS teams (
|
||
id TEXT PRIMARY KEY, -- Präfix "tm_..."
|
||
org_id TEXT NOT NULL DEFAULT 'default',
|
||
name TEXT NOT NULL,
|
||
description TEXT NOT NULL DEFAULT '',
|
||
created_at TEXT NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS team_members (
|
||
team_id TEXT NOT NULL,
|
||
user_id TEXT NOT NULL,
|
||
role TEXT NOT NULL DEFAULT 'member', -- 'admin' | 'member'
|
||
joined_at TEXT NOT NULL,
|
||
PRIMARY KEY (team_id, user_id),
|
||
FOREIGN KEY(team_id) REFERENCES teams(id) ON DELETE CASCADE,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
### 2.3 Gedanken, Triage & Aufgaben
|
||
|
||
In MindSift nutzen Quick Dumps (Triage) und Aufgaben dieselbe Basistabelle **`thoughts`**. Ein Quick Dump ist ein Gedanke mit `status = 'inbox'`, der durch Triage in `status = 'kept'` oder `status = 'converted'` übergeht.
|
||
|
||
#### `thoughts`
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS thoughts (
|
||
id TEXT PRIMARY KEY, -- Präfix "th_..."
|
||
user_id TEXT NOT NULL,
|
||
content TEXT NOT NULL, -- Textinhalt inkl. Hashtags
|
||
tags TEXT NOT NULL DEFAULT '[]', -- JSON Array: ["smarthome", "keller"]
|
||
priority TEXT NOT NULL DEFAULT 'normal', -- 'low' | 'normal' | 'high' | 'urgent'
|
||
source TEXT NOT NULL DEFAULT 'manual', -- 'manual' | 'voice' | 'webhook'
|
||
status TEXT NOT NULL DEFAULT 'inbox', -- 'inbox' | 'kept' | 'discarded' | 'converted'
|
||
is_completed INTEGER NOT NULL DEFAULT 0, -- 0 = offen, 1 = erledigt
|
||
converted_type TEXT DEFAULT '', -- 'task' | 'document' | 'list_item'
|
||
converted_ref_id TEXT DEFAULT '', -- Verknüpfte ID (z.B. doc_id oder list_item_id)
|
||
due_date TEXT DEFAULT '', -- Fälligkeitsdatum "YYYY-MM-DD"
|
||
task_list_id TEXT NOT NULL DEFAULT '', -- Zugeordnete Aufgabenliste ("" = Standard)
|
||
kanban_status TEXT NOT NULL DEFAULT 'todo',-- 'todo' | 'in_progress' | 'done' | column.id
|
||
notes TEXT NOT NULL DEFAULT '', -- Zusätzliche Notizen / Beschreibung
|
||
subtasks TEXT NOT NULL DEFAULT '[]', -- JSON Array von SubTask: [{id, title, isCompleted}]
|
||
alarm_sent INTEGER NOT NULL DEFAULT 0, -- 1 = Alarm wurde bereits versendet
|
||
sort_order INTEGER NOT NULL DEFAULT 0, -- Manuelle Reorder-Sortierung
|
||
created_at TEXT NOT NULL,
|
||
triaged_at TEXT DEFAULT '',
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_thoughts_user_status ON thoughts(user_id, status, created_at DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_thoughts_list_sort ON thoughts(task_list_id, sort_order ASC, created_at DESC);
|
||
```
|
||
|
||
#### `task_lists` & `task_list_shares`
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS task_lists (
|
||
id TEXT PRIMARY KEY, -- Präfix "tl_..."
|
||
user_id TEXT NOT NULL,
|
||
title TEXT NOT NULL,
|
||
icon TEXT NOT NULL DEFAULT '📋',
|
||
color TEXT NOT NULL DEFAULT '#3b82f6',
|
||
team_id TEXT NOT NULL DEFAULT '', -- Optional: an Team gebunden
|
||
columns TEXT NOT NULL DEFAULT '[]', -- JSON Array von Kanban-Spalten
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS task_list_shares (
|
||
id TEXT PRIMARY KEY,
|
||
task_list_id TEXT NOT NULL,
|
||
target_type TEXT NOT NULL CHECK(target_type IN ('team', 'user')),
|
||
target_id TEXT NOT NULL, -- Team-ID oder User-ID
|
||
created_at TEXT NOT NULL,
|
||
created_by TEXT NOT NULL,
|
||
UNIQUE(task_list_id, target_type, target_id),
|
||
FOREIGN KEY(task_list_id) REFERENCES task_lists(id) ON DELETE CASCADE
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
### 2.4 Workspace: Dokumente, Blöcke & Geteilte Ordner
|
||
|
||
#### `documents`, `blocks` & `block_tags` (Database-First Outliner Schema)
|
||
Dokumente werden als Kopfdatensatz in `documents` gespeichert, während der Inhalt als feingranulare Blöcke in `blocks` verwaltet wird. Hashtags (#tags) werden per Regex analysiert und in `block_tags` abgelegt. MindSift speichert alle Notizen zu 100% in SQLite (keine physischen `.md`-Dateien auf Festplatte).
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS documents (
|
||
id TEXT PRIMARY KEY, -- Präfix "doc_..."
|
||
user_id TEXT NOT NULL DEFAULT '', -- Eigentümer (usr_...)
|
||
space_id TEXT DEFAULT '',
|
||
title TEXT NOT NULL DEFAULT '', -- Dokumenttitel
|
||
folder_id TEXT NOT NULL DEFAULT '', -- Ordner-Zuordnung
|
||
markdown_cache TEXT NOT NULL DEFAULT '', -- Kompiliertes Standard-Markdown (automatischer Cache)
|
||
icon TEXT NOT NULL DEFAULT '📄', -- Emoji-Icon
|
||
content TEXT NOT NULL DEFAULT '', -- Plaintext / Text-Repräsentation
|
||
blocks_json TEXT NOT NULL DEFAULT '[]', -- Legacy/Zusatz-Cache
|
||
tags TEXT NOT NULL DEFAULT '[]', -- JSON Array von Tags
|
||
folder_path TEXT NOT NULL DEFAULT '', -- Relativer Ordnerpfad des Eigentümers
|
||
is_pinned INTEGER NOT NULL DEFAULT 0, -- Globaler Pin
|
||
is_archived INTEGER NOT NULL DEFAULT 0,
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_documents_user ON documents(user_id, is_archived, updated_at DESC);
|
||
|
||
CREATE TABLE IF NOT EXISTS blocks (
|
||
id TEXT PRIMARY KEY, -- Block-ID (z.B. "blk_..." oder "docId-b0")
|
||
document_id TEXT NOT NULL, -- Zugehöriges Dokument (doc_...)
|
||
content TEXT NOT NULL DEFAULT '', -- Textinhalt des Blocks
|
||
level INT NOT NULL DEFAULT 0, -- Hierarchieebene (0 = Root, 1 = Kind...)
|
||
position REAL NOT NULL DEFAULT 0, -- Fließkommazahl für konfliktfreies Einfügen/Sortieren
|
||
type TEXT NOT NULL DEFAULT 'bullet', -- 'bullet', 'todo', 'h1', 'h2', 'h3', 'code', 'quote', 'callout', 'divider'
|
||
checked INTEGER NOT NULL DEFAULT 0, -- Für Checklisten (0 = offen, 1 = abgehakt)
|
||
collapsed INTEGER NOT NULL DEFAULT 0, -- Für eingeklappte Unterbäume (1 = zugeklappt)
|
||
lang TEXT NOT NULL DEFAULT '', -- Programmiersprache für Codeblöcke
|
||
FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_blocks_doc_pos ON blocks(document_id, position ASC);
|
||
|
||
CREATE TABLE IF NOT EXISTS block_tags (
|
||
tag TEXT NOT NULL, -- Hashtag (z.B. "meeting", ohne führendes '#')
|
||
document_id TEXT NOT NULL, -- Zugehöriges Dokument
|
||
block_id TEXT NOT NULL, -- Zugehöriger Block
|
||
PRIMARY KEY(tag, block_id),
|
||
FOREIGN KEY(document_id) REFERENCES documents(id) ON DELETE CASCADE,
|
||
FOREIGN KEY(block_id) REFERENCES blocks(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_block_tags_tag ON block_tags(tag);
|
||
CREATE INDEX IF NOT EXISTS idx_block_tags_doc ON block_tags(document_id);
|
||
```
|
||
|
||
#### Entkoppelte Ordner-Freigabe (`workspace_shared_folders` & `workspace_folder_mounts`)
|
||
Ermöglicht das Teilen kompletter Ordnerstrukturen. Verschiebt ein Empfänger den geteilten Ordner in seiner Hierarchie, wird **nur sein Mount-Pfad** aktualisiert – die Quellstruktur beim Eigentümer und anderen Empfängern bleibt unberührt!
|
||
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS workspace_shared_folders (
|
||
id TEXT PRIMARY KEY, -- Präfix "wsf_..."
|
||
owner_id TEXT NOT NULL,
|
||
name TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS workspace_folder_shares (
|
||
id TEXT PRIMARY KEY,
|
||
folder_id TEXT NOT NULL,
|
||
target_type TEXT NOT NULL CHECK(target_type IN ('team', 'user')),
|
||
target_id TEXT NOT NULL,
|
||
permission TEXT NOT NULL CHECK(permission IN ('read', 'write')),
|
||
created_by TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
UNIQUE(folder_id, target_type, target_id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS workspace_folder_mounts (
|
||
user_id TEXT NOT NULL,
|
||
folder_id TEXT NOT NULL,
|
||
mount_path TEXT NOT NULL, -- Eigener lokaler Pfad des Benutzers (z.B. "Arbeit/Projekte/Team-Docs")
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL,
|
||
PRIMARY KEY(user_id, folder_id)
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS doc_user_folders (
|
||
user_id TEXT NOT NULL,
|
||
doc_id TEXT NOT NULL,
|
||
folder_path TEXT NOT NULL DEFAULT '', -- Individuelle Ordnerplatzierung für geteilte Einzelnotizen
|
||
created_at TEXT NOT NULL,
|
||
updated_at TEXT NOT NULL,
|
||
PRIMARY KEY(user_id, doc_id)
|
||
);
|
||
```
|
||
|
||
---
|
||
|
||
### 2.5 Geteilte Listen & Listen-Einträge
|
||
|
||
#### `lists`, `list_items` & `list_shares`
|
||
```sql
|
||
CREATE TABLE IF NOT EXISTS lists (
|
||
id TEXT PRIMARY KEY, -- Präfix "lst_..."
|
||
user_id TEXT NOT NULL,
|
||
title TEXT NOT NULL,
|
||
icon TEXT NOT NULL DEFAULT '🛒',
|
||
list_type TEXT NOT NULL DEFAULT 'shopping',-- 'shopping', 'packing', 'inventory', 'todo'
|
||
updated_at TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_lists_user ON lists(user_id, updated_at DESC);
|
||
|
||
CREATE TABLE IF NOT EXISTS list_shares (
|
||
id TEXT PRIMARY KEY,
|
||
list_id TEXT NOT NULL,
|
||
target_type TEXT NOT NULL CHECK(target_type IN ('team', 'user')),
|
||
target_id TEXT NOT NULL,
|
||
role TEXT NOT NULL DEFAULT 'editor' CHECK(role IN ('editor', 'viewer')),
|
||
created_by TEXT NOT NULL,
|
||
created_at TEXT NOT NULL,
|
||
UNIQUE(list_id, target_type, target_id),
|
||
FOREIGN KEY(list_id) REFERENCES lists(id) ON DELETE CASCADE
|
||
);
|
||
|
||
CREATE TABLE IF NOT EXISTS list_items (
|
||
id TEXT PRIMARY KEY, -- Präfix "itm_..."
|
||
list_id TEXT NOT NULL,
|
||
content TEXT NOT NULL, -- Bezeichnung des Eintrags
|
||
category TEXT NOT NULL DEFAULT '', -- Regal/Kategorie (z.B. "Kühlregal")
|
||
quantity TEXT NOT NULL DEFAULT '', -- Menge (z.B. "2x", "500g")
|
||
is_completed INTEGER NOT NULL DEFAULT 0, -- 1 = abgehakt
|
||
completed_by TEXT NOT NULL DEFAULT '', -- User-ID des Abhakers
|
||
completed_by_name TEXT NOT NULL DEFAULT '',-- Name des Abhakers für Live-Badge
|
||
sort_order INTEGER NOT NULL DEFAULT 0,
|
||
created_at TEXT NOT NULL,
|
||
completed_at TEXT NOT NULL DEFAULT '',
|
||
FOREIGN KEY(list_id) REFERENCES lists(id) ON DELETE CASCADE
|
||
);
|
||
CREATE INDEX IF NOT EXISTS idx_list_items_sync ON list_items(list_id, is_completed, sort_order ASC);
|
||
```
|
||
|
||
---
|
||
|
||
## 🔄 3. Migrationsstrategie
|
||
|
||
MindSift setzt auf **idempotente automatische In-Process-Migrationen** beim Serverstart ([`internal/storage/sqlite.go`](../internal/storage/sqlite.go)):
|
||
1. **Schema-Bootstrap:** Ausführung des Basis-Schemas via `CREATE TABLE IF NOT EXISTS`.
|
||
2. **Column & Index Migration Array:** Ein vordefiniertes Array idempotenter DDL-Befehle (z.B. `ALTER TABLE thoughts ADD COLUMN ...`, `CREATE TABLE IF NOT EXISTS doc_user_folders ...`).
|
||
3. **Fehlerbehandlung:** Wenn Spalten bereits existieren (z.B. `"duplicate column name"`), wird der Fehler ignoriert und der Start fortgesetzt.
|
||
4. **Vorteil:** Keine manuellen Migrations-Skripte nötig; Container-Updates können jederzeit ohne Downtime oder Vorbereitung über bestehende Datenbanken ausgerollt werden.
|