34 lines
1.2 KiB
SQL
34 lines
1.2 KiB
SQL
-- 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);
|