max / goingson

1.2 KB · 33 lines History Blame Raw

1	-- Monthly goals and reflections for the Month view.
2	-- Goals are free-text items (up to 3) set at the start of each month.
3	-- Reflections capture highlight and change-text at month end.
4
5	CREATE TABLE IF NOT EXISTS monthly_goals (
6	id TEXT PRIMARY KEY NOT NULL,
7	user_id TEXT NOT NULL,
8	month TEXT NOT NULL, -- YYYY-MM format
9	text TEXT NOT NULL,
10	status TEXT NOT NULL DEFAULT 'active', -- active, done, abandoned
11	position INTEGER NOT NULL, -- 1-3
12	created_at TEXT NOT NULL DEFAULT (datetime('now')),
13	updated_at TEXT NOT NULL DEFAULT (datetime('now')),
14	FOREIGN KEY (user_id) REFERENCES users(id)
15	);
16
17	CREATE INDEX IF NOT EXISTS idx_monthly_goals_user_month
18	ON monthly_goals(user_id, month);
19
20	CREATE TABLE IF NOT EXISTS monthly_reflections (
21	id TEXT PRIMARY KEY NOT NULL,
22	user_id TEXT NOT NULL,
23	month TEXT NOT NULL, -- YYYY-MM format
24	highlight_text TEXT NOT NULL DEFAULT '',
25	change_text TEXT NOT NULL DEFAULT '',
26	completed_at TEXT NOT NULL DEFAULT (datetime('now')),
27	UNIQUE(user_id, month),
28	FOREIGN KEY (user_id) REFERENCES users(id)
29	);
30
31	CREATE INDEX IF NOT EXISTS idx_monthly_reflections_user_month
32	ON monthly_reflections(user_id, month);
33