-- Timestamps are stored as UTC text, always formatted as "YYYY-MM-DDTHH:MM:SSZ" -- so that lexicographic comparison matches chronological order. CREATE TABLE clients ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, archived INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL ); CREATE TABLE projects ( id INTEGER PRIMARY KEY, client_id INTEGER NOT NULL REFERENCES clients (id) ON DELETE CASCADE, name TEXT NOT NULL, archived INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL, UNIQUE (client_id, name) ); CREATE TABLE time_entries ( id INTEGER PRIMARY KEY, project_id INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE, started_at TEXT NOT NULL, ended_at TEXT, note TEXT NOT NULL DEFAULT '', CHECK (ended_at IS NULL OR ended_at > started_at) ); -- At most one running timer: every running entry indexes the same value (1). CREATE UNIQUE INDEX one_running_timer ON time_entries ((ended_at IS NULL)) WHERE ended_at IS NULL; CREATE INDEX idx_entries_started ON time_entries (started_at); CREATE INDEX idx_entries_project ON time_entries (project_id); CREATE INDEX idx_projects_client ON projects (client_id);