3.9 KB119 lines
Blame
1import Database from "better-sqlite3";
2import { mkdirSync } from "fs";
3import { dirname } from "path";
4
5export function initDatabase(dbPath: string): Database.Database {
6 mkdirSync(dirname(dbPath), { recursive: true });
7 const db = new Database(dbPath);
8
9 db.pragma("journal_mode = WAL");
10 db.pragma("foreign_keys = ON");
11
12 migrate(db);
13
14 return db;
15}
16
17function migrate(db: Database.Database) {
18 db.exec(`
19 CREATE TABLE IF NOT EXISTS users (
20 id INTEGER PRIMARY KEY AUTOINCREMENT,
21 username TEXT UNIQUE NOT NULL,
22 display_name TEXT,
23 created_at TEXT DEFAULT (datetime('now'))
24 );
25
26 CREATE TABLE IF NOT EXISTS credentials (
27 id TEXT PRIMARY KEY,
28 user_id INTEGER NOT NULL REFERENCES users(id),
29 public_key BLOB NOT NULL,
30 counter INTEGER NOT NULL DEFAULT 0,
31 transports TEXT,
32 device_type TEXT,
33 backed_up INTEGER DEFAULT 0,
34 created_at TEXT DEFAULT (datetime('now'))
35 );
36
37 CREATE INDEX IF NOT EXISTS idx_credentials_user ON credentials(user_id);
38
39 CREATE TABLE IF NOT EXISTS instances (
40 id INTEGER PRIMARY KEY AUTOINCREMENT,
41 user_id INTEGER NOT NULL REFERENCES users(id),
42 name TEXT NOT NULL DEFAULT 'grove',
43 ip TEXT,
44 domain TEXT,
45 region TEXT NOT NULL DEFAULT 'self-hosted',
46 size TEXT NOT NULL DEFAULT 'custom',
47 jwt_secret TEXT,
48 status TEXT DEFAULT 'creating'
49 CHECK(status IN ('creating', 'active', 'error', 'destroyed')),
50 created_at TEXT DEFAULT (datetime('now')),
51 updated_at TEXT DEFAULT (datetime('now'))
52 );
53
54 CREATE INDEX IF NOT EXISTS idx_instances_user ON instances(user_id);
55
56 CREATE TABLE IF NOT EXISTS repos (
57 id INTEGER PRIMARY KEY AUTOINCREMENT,
58 owner_id INTEGER NOT NULL REFERENCES users(id),
59 instance_id INTEGER REFERENCES instances(id),
60 name TEXT NOT NULL,
61 description TEXT,
62 default_branch TEXT DEFAULT 'main',
63 is_private INTEGER DEFAULT 0,
64 created_at TEXT DEFAULT (datetime('now')),
65 updated_at TEXT DEFAULT (datetime('now')),
66 UNIQUE(owner_id, name)
67 );
68
69 CREATE INDEX IF NOT EXISTS idx_repos_owner ON repos(owner_id);
70
71 CREATE TABLE IF NOT EXISTS api_tokens (
72 id INTEGER PRIMARY KEY AUTOINCREMENT,
73 user_id INTEGER NOT NULL REFERENCES users(id),
74 name TEXT NOT NULL,
75 token_hash TEXT UNIQUE NOT NULL,
76 expires_at TEXT NOT NULL,
77 last_used_at TEXT,
78 created_at TEXT DEFAULT (datetime('now'))
79 );
80
81 CREATE INDEX IF NOT EXISTS idx_api_tokens_user ON api_tokens(user_id);
82 CREATE INDEX IF NOT EXISTS idx_api_tokens_hash ON api_tokens(token_hash);
83
84 CREATE TABLE IF NOT EXISTS orgs (
85 id INTEGER PRIMARY KEY AUTOINCREMENT,
86 name TEXT UNIQUE NOT NULL,
87 display_name TEXT,
88 created_by INTEGER NOT NULL REFERENCES users(id),
89 created_at TEXT DEFAULT (datetime('now')),
90 updated_at TEXT DEFAULT (datetime('now'))
91 );
92
93 CREATE TABLE IF NOT EXISTS org_members (
94 org_id INTEGER NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
95 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
96 created_at TEXT DEFAULT (datetime('now')),
97 PRIMARY KEY (org_id, user_id)
98 );
99
100 CREATE INDEX IF NOT EXISTS idx_org_members_user ON org_members(user_id);
101 `);
102
103 // Migrations for existing databases
104 const cols = db.prepare("PRAGMA table_info(instances)").all() as { name: string }[];
105 const colNames = cols.map((c) => c.name);
106 if (!colNames.includes("jwt_secret")) {
107 db.exec("ALTER TABLE instances ADD COLUMN jwt_secret TEXT");
108 }
109 if (colNames.includes("droplet_id")) {
110 // droplet_id is no longer used — leave it for backwards compat, just ignore it
111 }
112
113 // Add owner_type to repos
114 const repoCols = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
115 if (!repoCols.map((c) => c.name).includes("owner_type")) {
116 db.exec("ALTER TABLE repos ADD COLUMN owner_type TEXT NOT NULL DEFAULT 'user'");
117 }
118}
119