hub-api/src/services/database.tsblame
View source
135dfe51import Database from "better-sqlite3";
135dfe52import { mkdirSync } from "fs";
135dfe53import { dirname } from "path";
135dfe54
135dfe55export function initDatabase(dbPath: string): Database.Database {
135dfe56 mkdirSync(dirname(dbPath), { recursive: true });
135dfe57 const db = new Database(dbPath);
135dfe58
135dfe59 db.pragma("journal_mode = WAL");
135dfe510 db.pragma("foreign_keys = ON");
135dfe511
135dfe512 migrate(db);
135dfe513
135dfe514 return db;
135dfe515}
135dfe516
135dfe517function migrate(db: Database.Database) {
135dfe518 db.exec(`
135dfe519 CREATE TABLE IF NOT EXISTS users (
135dfe520 id INTEGER PRIMARY KEY AUTOINCREMENT,
135dfe521 username TEXT UNIQUE NOT NULL,
135dfe522 display_name TEXT,
135dfe523 created_at TEXT DEFAULT (datetime('now'))
135dfe524 );
135dfe525
135dfe526 CREATE TABLE IF NOT EXISTS credentials (
135dfe527 id TEXT PRIMARY KEY,
135dfe528 user_id INTEGER NOT NULL REFERENCES users(id),
135dfe529 public_key BLOB NOT NULL,
135dfe530 counter INTEGER NOT NULL DEFAULT 0,
135dfe531 transports TEXT,
135dfe532 device_type TEXT,
135dfe533 backed_up INTEGER DEFAULT 0,
135dfe534 created_at TEXT DEFAULT (datetime('now'))
135dfe535 );
135dfe536
135dfe537 CREATE INDEX IF NOT EXISTS idx_credentials_user ON credentials(user_id);
135dfe538
135dfe539 CREATE TABLE IF NOT EXISTS instances (
135dfe540 id INTEGER PRIMARY KEY AUTOINCREMENT,
135dfe541 user_id INTEGER NOT NULL REFERENCES users(id),
135dfe542 name TEXT NOT NULL DEFAULT 'grove',
135dfe543 ip TEXT,
135dfe544 domain TEXT,
966d71f45 region TEXT NOT NULL DEFAULT 'self-hosted',
966d71f46 size TEXT NOT NULL DEFAULT 'custom',
966d71f47 jwt_secret TEXT,
135dfe548 status TEXT DEFAULT 'creating'
135dfe549 CHECK(status IN ('creating', 'active', 'error', 'destroyed')),
135dfe550 created_at TEXT DEFAULT (datetime('now')),
135dfe551 updated_at TEXT DEFAULT (datetime('now'))
135dfe552 );
135dfe553
135dfe554 CREATE INDEX IF NOT EXISTS idx_instances_user ON instances(user_id);
4a006da55
4a006da56 CREATE TABLE IF NOT EXISTS repos (
4a006da57 id INTEGER PRIMARY KEY AUTOINCREMENT,
4a006da58 owner_id INTEGER NOT NULL REFERENCES users(id),
4a006da59 instance_id INTEGER REFERENCES instances(id),
4a006da60 name TEXT NOT NULL,
4a006da61 description TEXT,
4a006da62 default_branch TEXT DEFAULT 'main',
4a006da63 is_private INTEGER DEFAULT 0,
4a006da64 created_at TEXT DEFAULT (datetime('now')),
4a006da65 updated_at TEXT DEFAULT (datetime('now')),
4a006da66 UNIQUE(owner_id, name)
4a006da67 );
4a006da68
4a006da69 CREATE INDEX IF NOT EXISTS idx_repos_owner ON repos(owner_id);
4a006da70
3c994d371 CREATE TABLE IF NOT EXISTS api_tokens (
3c994d372 id INTEGER PRIMARY KEY AUTOINCREMENT,
3c994d373 user_id INTEGER NOT NULL REFERENCES users(id),
3c994d374 name TEXT NOT NULL,
3c994d375 token_hash TEXT UNIQUE NOT NULL,
3c994d376 expires_at TEXT NOT NULL,
3c994d377 last_used_at TEXT,
3c994d378 created_at TEXT DEFAULT (datetime('now'))
3c994d379 );
3c994d380
3c994d381 CREATE INDEX IF NOT EXISTS idx_api_tokens_user ON api_tokens(user_id);
3c994d382 CREATE INDEX IF NOT EXISTS idx_api_tokens_hash ON api_tokens(token_hash);
79efd4183
79efd4184 CREATE TABLE IF NOT EXISTS orgs (
79efd4185 id INTEGER PRIMARY KEY AUTOINCREMENT,
79efd4186 name TEXT UNIQUE NOT NULL,
79efd4187 display_name TEXT,
79efd4188 created_by INTEGER NOT NULL REFERENCES users(id),
79efd4189 created_at TEXT DEFAULT (datetime('now')),
79efd4190 updated_at TEXT DEFAULT (datetime('now'))
79efd4191 );
79efd4192
79efd4193 CREATE TABLE IF NOT EXISTS org_members (
79efd4194 org_id INTEGER NOT NULL REFERENCES orgs(id) ON DELETE CASCADE,
79efd4195 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
79efd4196 created_at TEXT DEFAULT (datetime('now')),
79efd4197 PRIMARY KEY (org_id, user_id)
79efd4198 );
79efd4199
79efd41100 CREATE INDEX IF NOT EXISTS idx_org_members_user ON org_members(user_id);
135dfe5101 `);
966d71f102
966d71f103 // Migrations for existing databases
966d71f104 const cols = db.prepare("PRAGMA table_info(instances)").all() as { name: string }[];
966d71f105 const colNames = cols.map((c) => c.name);
966d71f106 if (!colNames.includes("jwt_secret")) {
966d71f107 db.exec("ALTER TABLE instances ADD COLUMN jwt_secret TEXT");
966d71f108 }
966d71f109 if (colNames.includes("droplet_id")) {
966d71f110 // droplet_id is no longer used — leave it for backwards compat, just ignore it
966d71f111 }
79efd41112
79efd41113 // Add owner_type to repos
79efd41114 const repoCols = db.prepare("PRAGMA table_info(repos)").all() as { name: string }[];
79efd41115 if (!repoCols.map((c) => c.name).includes("owner_type")) {
79efd41116 db.exec("ALTER TABLE repos ADD COLUMN owner_type TEXT NOT NULL DEFAULT 'user'");
79efd41117 }
135dfe5118}