Leon4gr45/builder
0
1/**2 * System Database3 *4 * Manages the shared system.sqlite database for user accounts,5 * workspaces, workspace access, and deployment routing.6 * Workspaces are the unit of data isolation and quota enforcement.7 * Users get granted access to workspaces with roles (owner/editor/viewer).8 */9 10import Database from 'better-sqlite3';11import path from 'path';12import fs from 'fs';13import { randomUUID } from 'crypto';14import { enqueueEvent } from '../webhooks/outbox';15import { startDeliveryLoop } from '../webhooks/delivery';16 17let systemDb: Database.Database | null = null;18 19function getDataDir(): string {20 return process.env.DATA_DIR || path.join(process.cwd(), 'data');21}22 23function ensureDir(dirPath: string): void {24 if (!fs.existsSync(dirPath)) {25 fs.mkdirSync(dirPath, { recursive: true });26 }27}28 29// ---------------------------------------------------------------------------30// Types31// ---------------------------------------------------------------------------32 33export interface SystemUser {34 id: string;35 email: string;36 password_hash: string;37 display_name: string | null;38 is_admin: number;39 active: number;40 default_workspace_id: string | null;41 created_at: string;42 updated_at: string;43}44 45export interface SystemWorkspace {46 id: string;47 name: string;48 owner_id: string;49 max_projects: number;50 max_deployments: number;51 max_storage_mb: number;52 created_at: string;53 updated_at: string;54}55 56export interface WorkspaceAccess {57 user_id: string;58 workspace_id: string;59 role: 'owner' | 'editor' | 'viewer';60 created_at: string;61}62 63// ---------------------------------------------------------------------------64// Role hierarchy65// ---------------------------------------------------------------------------66 67const ROLE_LEVELS: Record<string, number> = { viewer: 1, editor: 2, owner: 3 };68 69// ---------------------------------------------------------------------------70// Database init71// ---------------------------------------------------------------------------72 73/**74 * Get the system database connection (singleton)75 */76export function getSystemDatabase(): Database.Database {77 if (systemDb) return systemDb;78 79 const dataDir = getDataDir();80 ensureDir(dataDir);81 82 const dbPath = path.join(dataDir, 'system.sqlite');83 systemDb = new Database(dbPath);84 const encryptionKey = process.env.DB_ENCRYPTION_KEY;85 if (encryptionKey) {86 systemDb.pragma(`key='${encryptionKey}'`);87 }88 systemDb.pragma('journal_mode = WAL');89 systemDb.pragma('foreign_keys = ON');90 systemDb.pragma('synchronous = NORMAL');91 92 initSystemSchema(systemDb);93 94 startDeliveryLoop();95 96 return systemDb;97}98 99function initSystemSchema(db: Database.Database): void {100 db.exec(`101 CREATE TABLE IF NOT EXISTS users (102 id TEXT PRIMARY KEY,103 email TEXT UNIQUE NOT NULL,104 password_hash TEXT NOT NULL,105 display_name TEXT,106 is_admin INTEGER NOT NULL DEFAULT 0,107 active INTEGER NOT NULL DEFAULT 1,108 default_workspace_id TEXT,109 created_at TEXT NOT NULL DEFAULT (datetime('now')),110 updated_at TEXT NOT NULL DEFAULT (datetime('now'))111 );112 113 CREATE TABLE IF NOT EXISTS workspaces (114 id TEXT PRIMARY KEY,115 name TEXT NOT NULL,116 owner_id TEXT NOT NULL,117 max_projects INTEGER NOT NULL DEFAULT 3,118 max_deployments INTEGER NOT NULL DEFAULT 1,119 max_storage_mb INTEGER NOT NULL DEFAULT 100,120 created_at TEXT NOT NULL DEFAULT (datetime('now')),121 updated_at TEXT NOT NULL DEFAULT (datetime('now')),122 FOREIGN KEY (owner_id) REFERENCES users(id)123 );124 125 CREATE TABLE IF NOT EXISTS workspace_access (126 user_id TEXT NOT NULL,127 workspace_id TEXT NOT NULL,128 role TEXT NOT NULL DEFAULT 'editor',129 created_at TEXT NOT NULL DEFAULT (datetime('now')),130 PRIMARY KEY (user_id, workspace_id),131 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,132 FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE133 );134 135 CREATE TABLE IF NOT EXISTS deployment_routing (136 deployment_id TEXT PRIMARY KEY,137 workspace_id TEXT NOT NULL,138 slug TEXT UNIQUE,139 custom_domain TEXT UNIQUE,140 created_at TEXT NOT NULL DEFAULT (datetime('now')),141 FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE142 );143 144 CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);145 CREATE INDEX IF NOT EXISTS idx_workspaces_owner ON workspaces(owner_id);146 CREATE INDEX IF NOT EXISTS idx_workspace_access_user ON workspace_access(user_id);147 CREATE INDEX IF NOT EXISTS idx_workspace_access_workspace ON workspace_access(workspace_id);148 CREATE INDEX IF NOT EXISTS idx_deployment_routing_workspace ON deployment_routing(workspace_id);149 CREATE INDEX IF NOT EXISTS idx_deployment_routing_slug ON deployment_routing(slug);150 151 CREATE TABLE IF NOT EXISTS webhook_outbox (152 id INTEGER PRIMARY KEY AUTOINCREMENT,153 event_type TEXT NOT NULL,154 payload TEXT NOT NULL,155 created_at TEXT NOT NULL DEFAULT (datetime('now')),156 delivered INTEGER NOT NULL DEFAULT 0,157 delivered_at TEXT,158 attempts INTEGER NOT NULL DEFAULT 0,159 last_attempted_at TEXT160 );161 `);162 163 // Migration: add custom_domain column if missing (existing databases)164 try {165 db.prepare('SELECT custom_domain FROM deployment_routing LIMIT 0').get();166 } catch {167 db.prepare('ALTER TABLE deployment_routing ADD COLUMN custom_domain TEXT').run();168 db.prepare('CREATE UNIQUE INDEX IF NOT EXISTS idx_deployment_routing_domain ON deployment_routing(custom_domain)').run();169 }170}171 172// ---------------------------------------------------------------------------173// User functions174// ---------------------------------------------------------------------------175 176/**177 * Create a new user. Returns the user ID.178 */179export function createUser(email: string, passwordHash: string, displayName?: string): string {180 const db = getSystemDatabase();181 const id = randomUUID();182 db.prepare(`183 INSERT INTO users (id, email, password_hash, display_name)184 VALUES (?, ?, ?, ?)185 `).run(id, email.toLowerCase().trim(), passwordHash, displayName || null);186 187 enqueueEvent('user.created', { userId: id, email: email.toLowerCase().trim(), displayName: displayName || null });188 189 return id;190}191 192/**193 * Find a user by email194 */195export function getUserByEmail(email: string): SystemUser | undefined {196 const db = getSystemDatabase();197 return db.prepare('SELECT * FROM users WHERE email = ? AND active = 1')198 .get(email.toLowerCase().trim()) as SystemUser | undefined;199}200 201/**202 * Find a user by ID203 */204export function getUserById(id: string): SystemUser | undefined {205 const db = getSystemDatabase();206 return db.prepare('SELECT * FROM users WHERE id = ? AND active = 1')207 .get(id) as SystemUser | undefined;208}209 210/**211 * Get the number of user accounts (for bootstrap detection)212 */213export function getUserCount(): number {214 const db = getSystemDatabase();215 const row = db.prepare('SELECT COUNT(*) as count FROM users').get() as { count: number };216 return row.count;217}218 219/**220 * Deactivate a user (soft delete)221 */222export function deactivateUser(id: string): void {223 const db = getSystemDatabase();224 db.prepare("UPDATE users SET active = 0, updated_at = datetime('now') WHERE id = ?").run(id);225 enqueueEvent('user.deactivated', { userId: id });226}227 228/**229 * List all users (for admin). Excludes password_hash.230 */231export function listUsers(): Omit<SystemUser, 'password_hash'>[] {232 const db = getSystemDatabase();233 return db.prepare(`234 SELECT id, email, display_name, is_admin, active,235 default_workspace_id, created_at, updated_at236 FROM users ORDER BY created_at DESC237 `).all() as Omit<SystemUser, 'password_hash'>[];238}239 240/**241 * Update user properties242 */243export function updateUser(id: string, updates: { display_name?: string; active?: number }): void {244 const db = getSystemDatabase();245 const setClauses: string[] = ["updated_at = datetime('now')"];246 const values: (string | number)[] = [];247 248 if (updates.display_name !== undefined) { setClauses.push('display_name = ?'); values.push(updates.display_name); }249 if (updates.active !== undefined) { setClauses.push('active = ?'); values.push(updates.active); }250 251 values.push(id);252 db.prepare(`UPDATE users SET ${setClauses.join(', ')} WHERE id = ?`).run(...values);253 254 const updated = getUserById(id);255 if (updated) {256 enqueueEvent('user.updated', { userId: id, email: updated.email, displayName: updated.display_name });257 }258}259 260// ---------------------------------------------------------------------------261// Workspace functions262// ---------------------------------------------------------------------------263 264/**265 * Create a workspace, create its data directory, return workspace ID.266 */267export function createWorkspace(name: string, ownerId: string): string {268 const db = getSystemDatabase();269 const id = randomUUID();270 271 db.prepare(`INSERT INTO workspaces (id, name, owner_id) VALUES (?, ?, ?)`).run(id, name, ownerId);272 273 // Grant owner access274 db.prepare(`275 INSERT INTO workspace_access (user_id, workspace_id, role)276 VALUES (?, ?, 'owner')277 `).run(ownerId, id);278 279 // Create workspace data directory280 const workspaceDir = path.join(getDataDir(), 'workspaces', id);281 ensureDir(workspaceDir);282 283 enqueueEvent('workspace.created', { workspaceId: id, name, ownerId });284 285 return id;286}287 288/**289 * Get workspace by ID290 */291export function getWorkspaceById(id: string): SystemWorkspace | undefined {292 const db = getSystemDatabase();293 return db.prepare('SELECT * FROM workspaces WHERE id = ?')294 .get(id) as SystemWorkspace | undefined;295}296 297/**298 * List all workspaces (admin)299 */300export function listWorkspaces(): SystemWorkspace[] {301 const db = getSystemDatabase();302 return db.prepare('SELECT * FROM workspaces ORDER BY created_at DESC')303 .all() as SystemWorkspace[];304}305 306/**307 * List workspaces a user has access to (with their role)308 */309export function listUserWorkspaces(userId: string): (SystemWorkspace & { role: string })[] {310 const db = getSystemDatabase();311 return db.prepare(`312 SELECT w.*, wa.role313 FROM workspaces w314 JOIN workspace_access wa ON wa.workspace_id = w.id315 WHERE wa.user_id = ?316 ORDER BY w.created_at DESC317 `).all(userId) as (SystemWorkspace & { role: string })[];318}319 320/**321 * Update workspace properties322 */323export function updateWorkspace(id: string, updates: {324 name?: string;325 max_projects?: number;326 max_deployments?: number;327 max_storage_mb?: number;328}): void {329 const db = getSystemDatabase();330 const setClauses: string[] = ["updated_at = datetime('now')"];331 const values: (string | number)[] = [];332 333 if (updates.name !== undefined) { setClauses.push('name = ?'); values.push(updates.name); }334 if (updates.max_projects !== undefined) { setClauses.push('max_projects = ?'); values.push(updates.max_projects); }335 if (updates.max_deployments !== undefined) { setClauses.push('max_deployments = ?'); values.push(updates.max_deployments); }336 if (updates.max_storage_mb !== undefined) { setClauses.push('max_storage_mb = ?'); values.push(updates.max_storage_mb); }337 338 values.push(id);339 db.prepare(`UPDATE workspaces SET ${setClauses.join(', ')} WHERE id = ?`).run(...values);340 341 if (updates.name !== undefined) {342 enqueueEvent('workspace.updated', { workspaceId: id, name: updates.name });343 }344}345 346/**347 * Delete workspace (removes from DB, caller handles filesystem cleanup)348 */349export function deleteWorkspace(id: string): void {350 const db = getSystemDatabase();351 db.prepare('DELETE FROM workspaces WHERE id = ?').run(id);352 enqueueEvent('workspace.deleted', { workspaceId: id });353}354 355// ---------------------------------------------------------------------------356// Workspace access functions357// ---------------------------------------------------------------------------358 359/**360 * Grant a user access to a workspace361 */362export function grantWorkspaceAccess(userId: string, workspaceId: string, role: 'owner' | 'editor' | 'viewer'): void {363 const db = getSystemDatabase();364 db.prepare(`365 INSERT OR REPLACE INTO workspace_access (user_id, workspace_id, role)366 VALUES (?, ?, ?)367 `).run(userId, workspaceId, role);368 369 const grantedUser = getUserById(userId);370 if (grantedUser) {371 enqueueEvent('workspace.access_granted', { workspaceId, email: grantedUser.email, role });372 }373}374 375/**376 * Revoke a user's access to a workspace377 */378export function revokeWorkspaceAccess(userId: string, workspaceId: string): void {379 const db = getSystemDatabase();380 const revokedUser = getUserById(userId);381 db.prepare('DELETE FROM workspace_access WHERE user_id = ? AND workspace_id = ?')382 .run(userId, workspaceId);383 384 if (revokedUser) {385 enqueueEvent('workspace.access_revoked', { workspaceId, email: revokedUser.email });386 }387}388 389/**390 * Get a user's access to a specific workspace391 */392export function getWorkspaceAccess(userId: string, workspaceId: string): WorkspaceAccess | undefined {393 const db = getSystemDatabase();394 return db.prepare('SELECT * FROM workspace_access WHERE user_id = ? AND workspace_id = ?')395 .get(userId, workspaceId) as WorkspaceAccess | undefined;396}397 398/**399 * Verify user has access to workspace, throws Error if not.400 * Admin users (is_admin=1) always have access.401 * Legacy admin/desktop/instance-api users always have access.402 */403export function verifyWorkspaceAccess(404 userId: string,405 workspaceId: string,406 requiredRole: 'owner' | 'editor' | 'viewer' = 'viewer'407): void {408 // Admin users always have access409 const user = getUserById(userId);410 if (user?.is_admin) return;411 412 // Also allow legacy admin and desktop users413 if (userId === 'admin' || userId === 'desktop' || userId === 'instance-api') return;414 415 const access = getWorkspaceAccess(userId, workspaceId);416 if (!access) throw new Error('Workspace access denied');417 418 const userLevel = ROLE_LEVELS[access.role] || 0;419 const requiredLevel = ROLE_LEVELS[requiredRole] || 0;420 if (userLevel < requiredLevel) throw new Error('Insufficient workspace permissions');421}422 423/**424 * Set user's default workspace425 */426export function setDefaultWorkspace(userId: string, workspaceId: string): void {427 const db = getSystemDatabase();428 db.prepare("UPDATE users SET default_workspace_id = ?, updated_at = datetime('now') WHERE id = ?")429 .run(workspaceId, userId);430}431 432/**433 * Get user's default workspace ID434 */435export function getUserDefaultWorkspace(userId: string): string | undefined {436 const db = getSystemDatabase();437 const row = db.prepare('SELECT default_workspace_id FROM users WHERE id = ?')438 .get(userId) as { default_workspace_id: string | null } | undefined;439 return row?.default_workspace_id ?? undefined;440}441 442// ---------------------------------------------------------------------------443// Deployment routing functions444// ---------------------------------------------------------------------------445 446/**447 * Register a deployment for routing448 */449export function registerDeploymentRoute(deploymentId: string, workspaceId: string, slug?: string, customDomain?: string): void {450 const db = getSystemDatabase();451 452 // Check if another workspace already owns this deployment453 const existing = db.prepare('SELECT workspace_id FROM deployment_routing WHERE deployment_id = ?')454 .get(deploymentId) as { workspace_id: string } | undefined;455 if (existing && existing.workspace_id !== workspaceId) {456 throw new Error('Deployment is owned by another workspace');457 }458 459 if (customDomain) {460 const domainOwner = db.prepare(461 'SELECT deployment_id FROM deployment_routing WHERE custom_domain = ? AND deployment_id != ?'462 ).get(customDomain, deploymentId) as { deployment_id: string } | undefined;463 if (domainOwner) {464 throw new Error('Domain is already registered to another deployment');465 }466 }467 468 db.prepare(`469 INSERT OR REPLACE INTO deployment_routing (deployment_id, workspace_id, slug, custom_domain)470 VALUES (?, ?, ?, ?)471 `).run(deploymentId, workspaceId, slug || null, customDomain || null);472}473 474/**475 * Remove a deployment route476 */477export function removeDeploymentRoute(deploymentId: string): void {478 const db = getSystemDatabase();479 db.prepare('DELETE FROM deployment_routing WHERE deployment_id = ?').run(deploymentId);480}481 482/**483 * Get the full routing record for a deployment484 */485export function getDeploymentRoute(deploymentId: string): { deployment_id: string; workspace_id: string; slug: string | null; custom_domain: string | null } | undefined {486 const db = getSystemDatabase();487 return db.prepare('SELECT deployment_id, workspace_id, slug, custom_domain FROM deployment_routing WHERE deployment_id = ?')488 .get(deploymentId) as { deployment_id: string; workspace_id: string; slug: string | null; custom_domain: string | null } | undefined;489}490 491/**492 * Look up which workspace owns a deployment493 */494export function getDeploymentWorkspace(deploymentId: string): string | undefined {495 const db = getSystemDatabase();496 const row = db.prepare('SELECT workspace_id FROM deployment_routing WHERE deployment_id = ?')497 .get(deploymentId) as { workspace_id: string } | undefined;498 return row?.workspace_id;499}500 501/**502 * Look up a deployment by subdomain slug503 */504export function getDeploymentBySlug(slug: string): { deployment_id: string; workspace_id: string } | undefined {505 const db = getSystemDatabase();506 return db.prepare('SELECT deployment_id, workspace_id FROM deployment_routing WHERE slug = ?')507 .get(slug) as { deployment_id: string; workspace_id: string } | undefined;508}509 510/**511 * Look up a deployment by custom domain512 */513export function getDeploymentByDomain(domain: string): { deployment_id: string; workspace_id: string } | undefined {514 const db = getSystemDatabase();515 return db.prepare(516 'SELECT deployment_id, workspace_id FROM deployment_routing WHERE custom_domain = ?'517 ).get(domain) as { deployment_id: string; workspace_id: string } | undefined;518}519 520/**521 * Get all deployments with custom domains (for Caddy config generation)522 */523export function getAllDomainRoutes(): { deployment_id: string; workspace_id: string; custom_domain: string }[] {524 const db = getSystemDatabase();525 return db.prepare(526 'SELECT deployment_id, workspace_id, custom_domain FROM deployment_routing WHERE custom_domain IS NOT NULL ORDER BY custom_domain'527 ).all() as { deployment_id: string; workspace_id: string; custom_domain: string }[];528}529 530/**531 * Get all deployments with slugs (for Caddy subdomain config generation)532 */533export function getAllSlugRoutes(): { deployment_id: string; slug: string }[] {534 const db = getSystemDatabase();535 return db.prepare(536 'SELECT deployment_id, slug FROM deployment_routing WHERE slug IS NOT NULL ORDER BY slug'537 ).all() as { deployment_id: string; slug: string }[];538}539 540/**541 * Get workspace's deployment count (for quota enforcement)542 */543export function getWorkspaceDeploymentCount(workspaceId: string): number {544 const db = getSystemDatabase();545 const row = db.prepare('SELECT COUNT(*) as count FROM deployment_routing WHERE workspace_id = ?')546 .get(workspaceId) as { count: number };547 return row.count;548}549 550// ---------------------------------------------------------------------------551// Workspace stats (used by admin APIs)552// ---------------------------------------------------------------------------553 554/**555 * Get the project count for a workspace by reading its database directly.556 * Opens the workspace DB read-only, does not use the adapter cache.557 */558export function getWorkspaceProjectCount(workspaceId: string): number {559 const dbPath = path.join(getDataDir(), 'workspaces', workspaceId, 'osws.sqlite');560 if (!fs.existsSync(dbPath)) return 0;561 try {562 const db = new Database(dbPath, { readonly: true });563 const tableExists = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='projects'").get();564 if (!tableExists) { db.close(); return 0; }565 const count = (db.prepare('SELECT COUNT(*) as count FROM projects').get() as { count: number }).count;566 db.close();567 return count;568 } catch { return 0; }569}570 571// ---------------------------------------------------------------------------572// Close573// ---------------------------------------------------------------------------574 575/**576 * Close system database connection577 */578export function closeSystemDatabase(): void {579 if (systemDb) {580 try { systemDb.close(); } catch {}581 systemDb = null;582 }583}584