munin/internal/store/migrations/sqlite/001_init.sql

-- SQLite-Fassung. Gegenstück: ../postgres/001_init.sql. Eine neue Migration
-- gehört immer in BEIDE Ordner (siehe dort und AUSBAUSTUFEN.md 4.10).

-- Erste Fassung des Schemas.
--
-- Konventionen, die für alle Migrationen gelten:
--
--   * IDs sind TEXT und werden in Go erzeugt (siehe ids.go), nicht von der
--     Datenbank. Damit gibt es keine Sequenzen, die bei einem Umzug nach
--     PostgreSQL nachgezogen werden müssten.
--   * Zeitstempel sind TEXT im Format RFC3339 in UTC. SQLite kennt keinen
--     echten Datumstyp, und Textvergleiche sortieren bei diesem Format korrekt.
--   * Wahrheitswerte sind INTEGER 0/1 — funktioniert in SQLite wie in PostgreSQL.
--   * Kein SQLite-spezifisches SQL (kein AUTOINCREMENT, kein INSERT OR REPLACE,
--     keine datetime()-Funktionen), damit der Umstieg offen bleibt.

-- Diese Datei enthält genau einen Workspace (eine Abteilung). Die
-- Workspace-Verwaltung selbst (Tabelle "workspaces") lebt in einer eigenen,
-- zentralen Kontroll-Datenbank — siehe internal/store/migrations_control und
-- AUSBAUSTUFEN.md 4.4. "workspace_id" steht hier trotzdem an jeder Zeile, damit
-- der Bezug beim Lesen einer einzelnen Datei erhalten bleibt, auch wenn er
-- innerhalb der Datei immer denselben Wert hat. Es gibt bewusst keinen
-- Fremdschlüssel auf eine "workspaces"-Tabelle — die existiert in dieser Datei
-- nicht.

-- Ein Projekt ist die Einheit der Abschottung: eigene Einträge, eigene Keys,
-- eigener MCP-Server. Ein Agent, der an Projekt A arbeitet, sieht Projekt B nicht.
CREATE TABLE projects (
    id           TEXT PRIMARY KEY,
    workspace_id TEXT NOT NULL,
    slug         TEXT NOT NULL,
    name         TEXT NOT NULL,
    description  TEXT NOT NULL DEFAULT '',
    created_at   TEXT NOT NULL,
    UNIQUE (workspace_id, slug)
);

-- API-Keys gehören zu genau einem Projekt.
--
-- Gespeichert wird nur der SHA-256-Hash (siehe auth.HashKey) — der Klartext wird
-- genau einmal beim Anlegen angezeigt und danach nie wieder. Der Hash ist UNIQUE
-- und damit indiziert: die Auflösung eines Keys ist ein Lookup, keine Schleife.
CREATE TABLE api_keys (
    id           TEXT PRIMARY KEY,
    project_id   TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name         TEXT NOT NULL,
    hash         TEXT NOT NULL UNIQUE,
    role         TEXT NOT NULL,
    created_at   TEXT NOT NULL,
    last_used_at TEXT NOT NULL DEFAULT '',
    revoked_at   TEXT NOT NULL DEFAULT ''
);

CREATE INDEX idx_api_keys_project ON api_keys (project_id, created_at);

-- Ein Eintrag ist die einzige Wissenseinheit — egal ob ein Mensch ihn über die
-- Weboberfläche geschrieben hat oder ein Agent über MCP. Der Unterschied steckt
-- in "source" und "curated", nicht in einer zweiten Tabelle.
--
-- "slug" ist der stabile Bezeichner innerhalb eines Projekts. Er ist zugleich der
-- Schlüssel für die Dublettenvermeidung: schreibt ein Agent zweimal unter
-- demselben Slug, wird aktualisiert statt ein zweiter Eintrag angelegt.
--
-- "version" zählt bei jeder Änderung hoch und dient dem optimistischen Sperren:
-- ein Schreiber kann angeben, welchen Stand er gelesen hat, und bekommt einen
-- Konflikt statt eines stillen Überschreibens.
CREATE TABLE entries (
    id         TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    slug       TEXT NOT NULL,
    title      TEXT NOT NULL,
    content    TEXT NOT NULL,
    source     TEXT NOT NULL,
    author     TEXT NOT NULL DEFAULT '',
    curated    INTEGER NOT NULL DEFAULT 0,
    version    INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL,
    updated_at TEXT NOT NULL,
    UNIQUE (project_id, slug)
);

-- Für die Übersichtsliste "zuletzt geändert zuerst".
CREATE INDEX idx_entries_project_updated ON entries (project_id, updated_at);

-- Schlagworte in einer eigenen Tabelle statt als kommagetrennte Spalte:
-- so lässt sich ohne LIKE-Gebastel nach ihnen filtern, und es gibt keine
-- doppelte Wahrheit über denselben Wert.
CREATE TABLE entry_tags (
    entry_id TEXT NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    tag      TEXT NOT NULL,
    PRIMARY KEY (entry_id, tag)
);

CREATE INDEX idx_entry_tags_tag ON entry_tags (tag);

-- Verweise zwischen Einträgen, geparst aus [[slug]] im Inhalt.
--
-- Das ersetzt die frühere Ordnerhierarchie: Struktur entsteht durch Verlinkung,
-- nicht durch einen Baum. Gespeichert wird der Ziel-Slug als Text, nicht als
-- Fremdschlüssel — ein Verweis darf auch auf einen noch nicht existierenden
-- Eintrag zeigen (und bleibt gültig, wenn dieser später angelegt wird).
CREATE TABLE entry_links (
    from_entry_id TEXT NOT NULL REFERENCES entries(id) ON DELETE CASCADE,
    to_slug       TEXT NOT NULL,
    PRIMARY KEY (from_entry_id, to_slug)
);

CREATE INDEX idx_entry_links_to ON entry_links (to_slug);

-- Nachvollziehbarkeitsprotokoll.
--
-- Bewusst mit api_key_id statt nur einem Namen: kommt später eine
-- Nutzerverwaltung dazu, lässt sich eine user_id danebenstellen und die
-- Historie bleibt auswertbar.
CREATE TABLE audit_events (
    id         TEXT PRIMARY KEY,
    ts         TEXT NOT NULL,
    project_id TEXT NOT NULL DEFAULT '',
    api_key_id TEXT NOT NULL DEFAULT '',
    actor      TEXT NOT NULL,
    action     TEXT NOT NULL,
    entry_slug TEXT NOT NULL DEFAULT '',
    source     TEXT NOT NULL,
    ip         TEXT NOT NULL DEFAULT '',
    detail     TEXT NOT NULL DEFAULT ''
);

CREATE INDEX idx_audit_ts ON audit_events (ts);
CREATE INDEX idx_audit_project ON audit_events (project_id, ts);