| 1 | PRAGMA foreign_keys = ON; |
| 2 | |
| 3 | CREATE TABLE users ( |
| 4 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 5 | username TEXT NOT NULL UNIQUE, |
| 6 | public_key TEXT NOT NULL, |
| 7 | created_at TEXT NOT NULL |
| 8 | ); |
| 9 | |
| 10 | CREATE TABLE tokens ( |
| 11 | token_hash TEXT PRIMARY KEY, -- SHA256 of token |
| 12 | user_id INTEGER NOT NULL, |
| 13 | created_at TEXT NOT NULL, |
| 14 | FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE |
| 15 | ); |
| 16 | |
| 17 | CREATE TABLE repos ( |
| 18 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 19 | owner_id INTEGER NOT NULL, |
| 20 | name TEXT NOT NULL, |
| 21 | created_at TEXT NOT NULL, |
| 22 | UNIQUE(owner_id, name), |
| 23 | FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE |
| 24 | ); |
| 25 | |
| 26 | CREATE TABLE invites ( |
| 27 | code TEXT PRIMARY KEY, |
| 28 | created_by INTEGER, -- null = admin-created |
| 29 | used_by INTEGER, |
| 30 | created_at TEXT NOT NULL, |
| 31 | used_at TEXT, |
| 32 | FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL, |
| 33 | FOREIGN KEY(used_by) REFERENCES users(id) ON DELETE SET NULL |
| 34 | ); |
| 35 | |
| 36 | CREATE TABLE challenges ( |
| 37 | id INTEGER PRIMARY KEY AUTOINCREMENT, |
| 38 | username TEXT NOT NULL, |
| 39 | nonce TEXT NOT NULL UNIQUE, |
| 40 | created_at TEXT NOT NULL |
| 41 | ); |
| 42 | |
| 43 | CREATE INDEX idx_repos_owner_id ON repos(owner_id); |
| 44 | CREATE INDEX idx_tokens_user_id ON tokens(user_id); |
| 45 | CREATE INDEX idx_challenges_username ON challenges(username); |