max / goingson

2.6 KB · 80 lines History Blame Raw

1	-- Initial schema for GoingsOn (SQLite)
2	-- Creates all 5 core tables: projects, tasks, annotations, events, emails
3
4	-- Projects table
5	CREATE TABLE projects (
6	id TEXT PRIMARY KEY NOT NULL,
7	name TEXT NOT NULL,
8	description TEXT NOT NULL DEFAULT '',
9	project_type TEXT NOT NULL DEFAULT 'Other',
10	status TEXT NOT NULL DEFAULT 'Active',
11	created_at TEXT NOT NULL DEFAULT (datetime('now'))
12	);
13
14	-- Create index for common queries
15	CREATE INDEX idx_projects_status ON projects(status);
16	CREATE INDEX idx_projects_type ON projects(project_type);
17
18	-- Tasks table (TaskWarrior-inspired)
19	CREATE TABLE tasks (
20	id TEXT PRIMARY KEY NOT NULL,
21	project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
22	description TEXT NOT NULL,
23	status TEXT NOT NULL DEFAULT 'Pending',
24	priority TEXT NOT NULL DEFAULT 'Medium',
25	due TEXT,
26	tags TEXT NOT NULL DEFAULT '[]',
27	urgency REAL NOT NULL DEFAULT 0.0,
28	recurrence TEXT NOT NULL DEFAULT 'None',
29	created_at TEXT NOT NULL DEFAULT (datetime('now'))
30	);
31
32	-- Create indexes for common task queries
33	CREATE INDEX idx_tasks_project ON tasks(project_id);
34	CREATE INDEX idx_tasks_status ON tasks(status);
35	CREATE INDEX idx_tasks_priority ON tasks(priority);
36	CREATE INDEX idx_tasks_due ON tasks(due);
37	CREATE INDEX idx_tasks_urgency ON tasks(urgency DESC);
38
39	-- Annotations table (linked to tasks)
40	CREATE TABLE annotations (
41	id TEXT PRIMARY KEY NOT NULL,
42	task_id TEXT NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
43	timestamp TEXT NOT NULL DEFAULT (datetime('now')),
44	note TEXT NOT NULL
45	);
46
47	CREATE INDEX idx_annotations_task ON annotations(task_id);
48
49	-- Events table
50	CREATE TABLE events (
51	id TEXT PRIMARY KEY NOT NULL,
52	project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
53	title TEXT NOT NULL,
54	description TEXT NOT NULL DEFAULT '',
55	start_time TEXT NOT NULL,
56	end_time TEXT,
57	location TEXT
58	);
59
60	-- Create indexes for event queries
61	CREATE INDEX idx_events_project ON events(project_id);
62	CREATE INDEX idx_events_start_time ON events(start_time);
63
64	-- Emails table
65	CREATE TABLE emails (
66	id TEXT PRIMARY KEY NOT NULL,
67	project_id TEXT REFERENCES projects(id) ON DELETE SET NULL,
68	from_address TEXT NOT NULL,
69	to_address TEXT NOT NULL,
70	subject TEXT NOT NULL,
71	body TEXT NOT NULL DEFAULT '',
72	is_read INTEGER NOT NULL DEFAULT 0,
73	received_at TEXT NOT NULL DEFAULT (datetime('now'))
74	);
75
76	-- Create indexes for email queries
77	CREATE INDEX idx_emails_project ON emails(project_id);
78	CREATE INDEX idx_emails_is_read ON emails(is_read);
79	CREATE INDEX idx_emails_received_at ON emails(received_at DESC);
80