max / goingson

1.1 KB · 30 lines History Blame Raw

1	-- Users and authentication (SQLite)
2	-- Adds users table and user_id foreign keys to all data tables
3
4	-- Users table
5	CREATE TABLE users (
6	id TEXT PRIMARY KEY NOT NULL,
7	email TEXT NOT NULL UNIQUE,
8	password_hash TEXT NOT NULL,
9	display_name TEXT NOT NULL,
10	created_at TEXT NOT NULL DEFAULT (datetime('now')),
11	last_login_at TEXT
12	);
13
14	CREATE INDEX idx_users_email ON users(email);
15
16	-- Note: SQLite doesn't use separate sessions table like PostgreSQL
17	-- Tower-sessions with SQLite uses its own management
18
19	-- Add user_id to all data tables
20	ALTER TABLE projects ADD COLUMN user_id TEXT REFERENCES users(id) ON DELETE CASCADE;
21	ALTER TABLE tasks ADD COLUMN user_id TEXT REFERENCES users(id) ON DELETE CASCADE;
22	ALTER TABLE events ADD COLUMN user_id TEXT REFERENCES users(id) ON DELETE CASCADE;
23	ALTER TABLE emails ADD COLUMN user_id TEXT REFERENCES users(id) ON DELETE CASCADE;
24
25	-- Create indexes for user filtering
26	CREATE INDEX idx_projects_user ON projects(user_id);
27	CREATE INDEX idx_tasks_user ON tasks(user_id);
28	CREATE INDEX idx_events_user ON events(user_id);
29	CREATE INDEX idx_emails_user ON emails(user_id);
30