Datenbank-Schema¶
CC-Sprint verwendet SQLite als Cache- und Index-Schicht für schnelle Queries und Filterung.
Speicherort¶
Pro Projekt: .backlog-admin/backlog.db
Beispiel:
my-project/
├── docs/
│ └── Backlog.md
├── .backlog-admin/
│ ├── backlog.db # SQLite Database
│ └── backups/ # Backup-Dateien
└── src/
Schema-Übersicht¶
Tabellen-Details¶
projects¶
Projekt-Metadaten.
CREATE TABLE projects (
id TEXT PRIMARY KEY,
root_path TEXT NOT NULL UNIQUE,
backlog_file TEXT NOT NULL,
name TEXT NOT NULL,
last_hash TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
last_opened_at TEXT
);
Felder:
- id: UUID v4
- root_path: Absoluter Pfad zum Projekt-Verzeichnis
- backlog_file: Relativer Pfad zu Backlog.md (meist docs/Backlog.md)
- last_hash: SHA-256 Hash des letzten Backlog.md Inhalts (für Change Detection)
id_sequences¶
Auto-Increment pro Item-Typ.
CREATE TABLE id_sequences (
project_id TEXT NOT NULL,
type TEXT NOT NULL,
next_number INTEGER NOT NULL DEFAULT 1,
PRIMARY KEY (project_id, type),
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
Beispiel-Daten:
project_id | type | next_number
-----------|------|------------
abc-123 | F | 48 -- Nächste Feature-ID: F-0048
abc-123 | T | 25 -- Nächste Task-ID: T-0025
abc-123 | B | 12 -- Nächste Bug-ID: B-0012
abc-123 | C | 5 -- Nächste Chore-ID: C-0005
backlog_items¶
Alle Items (Features, Tasks, Bugs, Chores).
CREATE TABLE backlog_items (
id TEXT NOT NULL,
project_id TEXT NOT NULL,
title TEXT NOT NULL,
type TEXT NOT NULL CHECK(type IN ('F', 'T', 'B', 'C')),
status TEXT NOT NULL CHECK(status IN ('Offen', 'In Arbeit', 'Blockiert', 'Erledigt')),
priority TEXT NOT NULL CHECK(priority IN ('Hoch', 'Mittel', 'Niedrig')),
phase TEXT CHECK(phase IN ('Idee', 'Analyse', 'Umsetzung', 'Test', 'Done', '-')),
area TEXT CHECK(area IN ('funktional', 'technisch', 'design', 'sonstiges', '-')),
description_md TEXT,
technical_notes_md TEXT,
position INTEGER NOT NULL DEFAULT 0,
source_hash TEXT,
modified_in_app BOOLEAN NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
PRIMARY KEY (project_id, id),
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
CREATE INDEX idx_items_status ON backlog_items(project_id, status);
CREATE INDEX idx_items_phase ON backlog_items(project_id, phase);
CREATE INDEX idx_items_type ON backlog_items(project_id, type);
CREATE INDEX idx_items_position ON backlog_items(project_id, position);
CREATE INDEX idx_items_modified ON backlog_items(project_id, modified_in_app);
Wichtige Felder:
- position: Sortierreihenfolge (innerhalb Sektion)
- source_hash: Hash des Item-Blocks in Backlog.md
- modified_in_app: Flag für Konflikt-Erkennung (true = ungespeichert)
dependencies¶
Gerichteter Graph von Abhängigkeiten.
CREATE TABLE dependencies (
project_id TEXT NOT NULL,
from_item_id TEXT NOT NULL,
to_item_id TEXT NOT NULL,
relation TEXT NOT NULL DEFAULT 'depends_on',
note TEXT,
UNIQUE(project_id, from_item_id, to_item_id, relation),
FOREIGN KEY (project_id, from_item_id) REFERENCES backlog_items(project_id, id) ON DELETE CASCADE,
FOREIGN KEY (project_id, to_item_id) REFERENCES backlog_items(project_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_deps_from ON dependencies(project_id, from_item_id);
CREATE INDEX idx_deps_to ON dependencies(project_id, to_item_id);
Beispiel:
from_item_id | to_item_id | relation | note
-------------|------------|-------------|---------------------
F-0010 | T-0005 | depends_on | "Braucht SQLite-Setup"
F-0012 | F-0010 | depends_on | NULL
Semantik: F-0010 depends_on T-0005 = "F-0010 kann erst starten, wenn T-0005 erledigt ist"
acceptance_criteria¶
Checkboxen für Akzeptanzkriterien.
CREATE TABLE acceptance_criteria (
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_id TEXT NOT NULL,
item_id TEXT NOT NULL,
text TEXT NOT NULL,
is_done BOOLEAN NOT NULL DEFAULT 0,
position INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (project_id, item_id) REFERENCES backlog_items(project_id, id) ON DELETE CASCADE
);
CREATE INDEX idx_criteria_item ON acceptance_criteria(project_id, item_id);
Beispiel:
Wird zu:
id | item_id | text | is_done | position
---|---------|------------------------------|---------|----------
1 | F-0001 | Parser kann Items lesen | 1 | 0
2 | F-0001 | Renderer kann Items schreiben| 0 | 1
3 | F-0001 | Roundtrip-Tests bestehen | 0 | 2
tags¶
Tag-Definitionen.
CREATE TABLE tags (
id INTEGER PRIMARY KEY AUTOINCREMENT,
project_id TEXT NOT NULL,
name TEXT NOT NULL,
color TEXT,
UNIQUE(project_id, name),
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);
Beispiel:
id | project_id | name | color
---|------------|-------------|--------
1 | abc-123 | ui | #3B82F6
2 | abc-123 | backend | #10B981
3 | abc-123 | critical | #EF4444
item_tags¶
Many-to-Many Zuordnung Items ↔ Tags.
CREATE TABLE item_tags (
project_id TEXT NOT NULL,
item_id TEXT NOT NULL,
tag_id INTEGER NOT NULL,
PRIMARY KEY (project_id, item_id, tag_id),
FOREIGN KEY (project_id, item_id) REFERENCES backlog_items(project_id, id) ON DELETE CASCADE,
FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);
Migrations¶
System: SQL-Dateien in src-tauri/src/repository/migrations/
Tracking-Tabelle:
CREATE TABLE schema_migrations (
version INTEGER PRIMARY KEY,
applied_at TEXT NOT NULL DEFAULT (datetime('now'))
);
Beispiel-Migration (001_initial_schema.sql):
-- Migration: Initial schema
-- Version: 1
CREATE TABLE projects (...);
CREATE TABLE backlog_items (...);
-- ... etc
INSERT INTO schema_migrations (version) VALUES (1);
Anwendung:
1. Lese alle .sql Dateien aus migrations/
2. Sortiere nach Version (Dateiname: 001_, 002_, etc.)
3. Für jede Migration:
- Check: Ist Version bereits in schema_migrations?
- Wenn nein: Führe SQL aus, insert Version
Query-Beispiele¶
Alle offenen Features¶
SELECT id, title, priority, phase
FROM backlog_items
WHERE project_id = ? AND type = 'F' AND status = 'Offen'
ORDER BY position;
Items mit hoher Priorität und Blockiert¶
SELECT id, title, type, phase
FROM backlog_items
WHERE project_id = ?
AND priority = 'Hoch'
AND status = 'Blockiert'
ORDER BY type, position;
Dependency-Graph (alle Kanten)¶
Items mit unerledigten Akzeptanzkriterien¶
SELECT bi.id, bi.title, COUNT(ac.id) as pending_count
FROM backlog_items bi
JOIN acceptance_criteria ac ON bi.id = ac.item_id AND bi.project_id = ac.project_id
WHERE bi.project_id = ?
AND ac.is_done = 0
GROUP BY bi.id, bi.title
ORDER BY pending_count DESC;
Performance¶
Indexes¶
- Status/Phase/Type: Häufigste Filter → Composite Indexes
- Dependencies: Beide Richtungen (from/to) indexiert für Graph-Queries
- Position: Für effizientes Sortieren
Benchmark (1000 Items)¶
| Query | Dauer | Mit Index | Ohne Index |
|---|---|---|---|
| Filter by status | 2ms | ✅ | ~50ms |
| Filter by phase | 2ms | ✅ | ~50ms |
| Get dependencies | 5ms | ✅ | ~80ms |
| Full-text search | 10ms | ⚠️ Ohne FTS | ~200ms |
Full-Text Search (Future): - SQLite FTS5 für Beschreibung + Titel - Aktivieren bei >1000 Items