Onboarded MCP Server

/** * SQLite-backed state management for persisting created entities. * * Stores entity references (IDs, names, types) created via API calls * so the agent can reference them in follow-up operations. */ import Database, { type Database as DatabaseType } from "better-sqlite3"; import { join } from "node:path"; import { getDataDir } from "./config.js"; const DB_NAME = "state.db"; let db: DatabaseType | null = null; /** * Initialize the database connection and schema. */ export function initDb(): DatabaseType { if (db) { return db; } const dbPath = join(getDataDir(), DB_NAME); db = new Database(dbPath); // Enable WAL mode for better concurrency db.pragma("journal_mode = WAL"); // Create tables db.exec(` CREATE TABLE IF NOT EXISTS entities ( id TEXT PRIMARY KEY, entity_type TEXT NOT NULL, external_id TEXT, name TEXT, data TEXT, api TEXT NOT NULL, env TEXT NOT NULL DEFAULT 'prod', created_at TEXT NOT NULL DEFAULT (datetime('now')), updated_at TEXT NOT NULL DEFAULT (datetime('now')) ); CREATE INDEX IF NOT EXISTS idx_entity_type ON entities(entity_type); CREATE INDEX IF NOT EXISTS idx_external_id ON entities(external_id); CREATE INDEX IF NOT EXISTS idx_name ON entities(name); CREATE INDEX IF NOT EXISTS idx_api_env ON entities(api, env); `); return db; } /** * Close the database connection. */ export function closeDb(): void { if (db) { db.close(); db = null; } } /** * Get the database instance (initializes if needed). */ function getDb(): DatabaseType { if (!db) { return initDb(); } return db; } export interface Entity { id: string; entity_type: string; external_id?: string; name?: string; data?: Record<string, unknown>; api: string; env: string; created_at: string; updated_at: string; } export interface PutEntityInput { entityType: string; externalId?: string; name?: string; data?: Record<string, unknown>; api: string; env?: string; } /** * Store an entity in the database. * * @returns The stored entity with generated ID */ export function putEntity(input: PutEntityInput): Entity { const database = getDb(); const id = crypto.randomUUID(); const now = new Date().toISOString(); const stmt = database.prepare(` INSERT INTO entities (id, entity_type, external_id, name, data, api, env, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) `); stmt.run( id, input.entityType, input.externalId ?? null, input.name ?? null, input.data ? JSON.stringify(input.data) : null, input.api, input.env ?? "prod", now, now ); return { id, entity_type: input.entityType, external_id: input.externalId, name: input.name, data: input.data, api: input.api, env: input.env ?? "prod", created_at: now, updated_at: now, }; } /** * Update an existing entity. */ export function updateEntity( id: string, updates: Partial<Omit<PutEntityInput, "api" | "env">> ): Entity | null { const database = getDb(); const existing = getEntityById(id); if (!existing) { return null; } const now = new Date().toISOString(); const stmt = database.prepare(` UPDATE entities SET external_id = COALESCE(?, external_id), name = COALESCE(?, name), data = COALESCE(?, data), entity_type = COALESCE(?, entity_type), updated_at = ? WHERE id = ? `); stmt.run( updates.externalId ?? null, updates.name ?? null, updates.data ? JSON.stringify(updates.data) : null, updates.entityType ?? null, now, id ); return getEntityById(id); } /** * Get an entity by internal ID. */ export function getEntityById(id: string): Entity | null { const database = getDb(); const stmt = database.prepare(` SELECT * FROM entities WHERE id = ? `); const row = stmt.get(id) as | (Omit<Entity, "data"> & { data?: string }) | undefined; if (!row) { return null; } return { ...row, data: row.data ? JSON.parse(row.data) : undefined, }; } /** * Get an entity by external ID (the ID from the Onboarded API). */ export function getEntityByExternalId( externalId: string, api?: string, env?: string ): Entity | null { const database = getDb(); let sql = "SELECT * FROM entities WHERE external_id = ?"; const params: unknown[] = [externalId]; if (api) { sql += " AND api = ?"; params.push(api); } if (env) { sql += " AND env = ?"; params.push(env); } sql += " LIMIT 1"; const stmt = database.prepare(sql); const row = stmt.get(...params) as | (Omit<Entity, "data"> & { data?: string }) | undefined; if (!row) { return null; } return { ...row, data: row.data ? JSON.parse(row.data) : undefined, }; } export interface QueryEntitiesInput { entityType?: string; api?: string; env?: string; nameContains?: string; limit?: number; offset?: number; orderBy?: "created_at" | "updated_at" | "name"; order?: "asc" | "desc"; } /** * Query entities with filters. */ export function queryEntities(input: QueryEntitiesInput = {}): Entity[] { const database = getDb(); const conditions: string[] = []; const params: unknown[] = []; if (input.entityType) { conditions.push("entity_type = ?"); params.push(input.entityType); } if (input.api) { conditions.push("api = ?"); params.push(input.api); } if (input.env) { conditions.push("env = ?"); params.push(input.env); } if (input.nameContains) { conditions.push("name LIKE ?"); params.push(`%${input.nameContains}%`); } let sql = "SELECT * FROM entities"; if (conditions.length > 0) { sql += " WHERE " + conditions.join(" AND "); } const orderBy = input.orderBy ?? "created_at"; const order = input.order ?? "desc"; sql += ` ORDER BY ${orderBy} ${order.toUpperCase()}`; const limit = input.limit ?? 100; const offset = input.offset ?? 0; sql += ` LIMIT ? OFFSET ?`; params.push(limit, offset); const stmt = database.prepare(sql); const rows = stmt.all(...params) as Array< Omit<Entity, "data"> & { data?: string } >; return rows.map((row) => ({ ...row, data: row.data ? JSON.parse(row.data) : undefined, })); } /** * Delete an entity by ID. */ export function deleteEntity(id: string): boolean { const database = getDb(); const stmt = database.prepare("DELETE FROM entities WHERE id = ?"); const result = stmt.run(id); return result.changes > 0; } /** * Delete all entities (useful for testing). */ export function clearEntities(): void { const database = getDb(); database.exec("DELETE FROM entities"); } /** * Count entities matching a filter. */ export function countEntities( entityType?: string, api?: string, env?: string ): number { const database = getDb(); const conditions: string[] = []; const params: unknown[] = []; if (entityType) { conditions.push("entity_type = ?"); params.push(entityType); } if (api) { conditions.push("api = ?"); params.push(api); } if (env) { conditions.push("env = ?"); params.push(env); } let sql = "SELECT COUNT(*) as count FROM entities"; if (conditions.length > 0) { sql += " WHERE " + conditions.join(" AND "); } const stmt = database.prepare(sql); const row = stmt.get(...params) as { count: number }; return row.count; }