61 lines
2.3 KiB
SQL
61 lines
2.3 KiB
SQL
-- Bowler Chart schema. Applied idempotently on startup.
|
|
PRAGMA journal_mode = WAL;
|
|
PRAGMA foreign_keys = ON;
|
|
|
|
CREATE TABLE IF NOT EXISTS color_schemes (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
seed_color TEXT NOT NULL, -- #rrggbb
|
|
mode TEXT NOT NULL DEFAULT 'light', -- 'light' | 'dark'
|
|
is_default INTEGER NOT NULL DEFAULT 0, -- 0/1; exactly one row should be 1
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT,
|
|
password_hash TEXT NOT NULL,
|
|
role TEXT NOT NULL DEFAULT 'user', -- 'admin' | 'user'
|
|
preferred_scheme_id INTEGER REFERENCES color_schemes(id) ON DELETE SET NULL,
|
|
active INTEGER NOT NULL DEFAULT 1,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS kpis (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
unit TEXT,
|
|
goal_text TEXT,
|
|
sort_order INTEGER NOT NULL DEFAULT 0,
|
|
created_at TEXT NOT NULL DEFAULT (datetime('now'))
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS entries (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
kpi_id INTEGER NOT NULL REFERENCES kpis(id) ON DELETE CASCADE,
|
|
year INTEGER NOT NULL,
|
|
month INTEGER NOT NULL CHECK (month BETWEEN 1 AND 12),
|
|
value TEXT,
|
|
status TEXT NOT NULL DEFAULT 'none', -- 'green' | 'amber' | 'red' | 'none'
|
|
note TEXT,
|
|
updated_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
|
|
updated_at TEXT NOT NULL DEFAULT (datetime('now')),
|
|
UNIQUE (kpi_id, year, month)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_projects_owner ON projects(owner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_kpis_project ON kpis(project_id);
|
|
CREATE INDEX IF NOT EXISTS idx_entries_kpi ON entries(kpi_id, year);
|