001_init.sql · rin/polar

Code Issues Pull requests Releases 9 Activity

001_init.sql · 54 lines · 1.9 KB

1	CREATE TABLE IF NOT EXISTS users (
2	id INTEGER PRIMARY KEY AUTOINCREMENT,
3	username TEXT NOT NULL UNIQUE COLLATE NOCASE,
4	email TEXT NOT NULL UNIQUE COLLATE NOCASE,
5	password_hash TEXT NOT NULL,
6	bio TEXT NOT NULL DEFAULT '',
7	is_admin INTEGER NOT NULL DEFAULT 0,
8	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
9	);
10
11	CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
12
13	CREATE TABLE IF NOT EXISTS sessions (
14	id TEXT PRIMARY KEY,
15	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
16	csrf_token TEXT NOT NULL,
17	expires_at DATETIME NOT NULL,
18	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
19	);
20
21	CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON sessions(user_id);
22	CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
23
24	CREATE TABLE IF NOT EXISTS access_tokens (
25	id INTEGER PRIMARY KEY AUTOINCREMENT,
26	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
27	name TEXT NOT NULL,
28	token_prefix TEXT NOT NULL DEFAULT '',
29	token_hash TEXT NOT NULL UNIQUE,
30	last_used_at DATETIME,
31	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
32	);
33
34	CREATE INDEX IF NOT EXISTS idx_access_tokens_user_id ON access_tokens(user_id);
35	CREATE INDEX IF NOT EXISTS idx_access_tokens_hash ON access_tokens(token_hash);
36
37	CREATE TABLE IF NOT EXISTS repositories (
38	id INTEGER PRIMARY KEY AUTOINCREMENT,
39	owner_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
40	name TEXT NOT NULL COLLATE NOCASE,
41	description TEXT NOT NULL DEFAULT '',
42	is_private INTEGER NOT NULL DEFAULT 0,
43	default_branch TEXT NOT NULL DEFAULT 'main',
44	created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
45	updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
46	UNIQUE(owner_id, name)
47	);
48
49	CREATE INDEX IF NOT EXISTS idx_repositories_owner_id ON repositories(owner_id);
50
51	CREATE TABLE IF NOT EXISTS settings (
52	key TEXT PRIMARY KEY,
53	value TEXT NOT NULL
54	);
55