/**
* Prompteka Database Accessor
*
* Provides full read-write access to Prompteka's SQLite database.
* Uses WAL mode for safe concurrent access with Prompteka app.
*
* WAL Safety Model:
* - One write connection (MCP server)
* - Prompteka app may have separate connections
* - WAL ensures no locks, no conflicts, no corruption
* - Multiple readers can access simultaneously
* - Writers automatically serialize (SQLite handles locking)
*
* All write operations include:
* - Retry logic for SQLITE_BUSY (up to 3 attempts)
* - Atomic transactions where needed
* - Prepared statements (prevent injection)
* - Timestamp generation (Unix seconds, matching Prompteka database schema)
*/
import Database from "better-sqlite3";
import path from "path";
import fs from "fs";
import os from "os";
import { v4 as uuidv4 } from "uuid";
import { Folder, Prompt, UUID, Emoji, PromptColor } from "./types.js";
import { PromptekaMCPError, ErrorCodes } from "../validation/error-taxonomy.js";
import { getLogger } from "../observability/logger.js";
/**
* Expected Prompteka database schema version.
* If the database schema differs from this, writes are blocked to prevent data corruption.
* CRITICAL: This must match the actual Prompteka database schema version.
* Verify with: sqlite3 ~/Library/Application\ Support/prompteka/prompts.db "PRAGMA schema_version;"
*/
const PROMPTEKA_SCHEMA_VERSION = 10; // Current Prompteka schema version
/**
* Prompteka bundle identifier for App Store (sandboxed) version
*/
const PROMPTEKA_BUNDLE_ID = "com.robertgrow.prompteka";
/**
* Get the sandboxed database path (App Store version)
*/
function getSandboxedDbPath(): string {
return path.join(
os.homedir(),
`Library/Containers/${PROMPTEKA_BUNDLE_ID}/Data/Library/Application Support/prompteka/prompts.db`
);
}
/**
* Get the non-sandboxed database path (dev/direct distribution)
*/
function getNonSandboxedDbPath(): string {
return path.join(
os.homedir(),
"Library/Application Support/prompteka/prompts.db"
);
}
/**
* Map color names to hex codes used in Prompteka database
*/
const COLOR_NAME_TO_HEX: Record<PromptColor, string> = {
red: "#EF4444",
orange: "#F97316",
yellow: "#EAB308",
green: "#22C55E",
blue: "#3B82F6",
purple: "#A855F7",
};
/**
* Map hex codes back to color names
*/
const HEX_TO_COLOR_NAME: Record<string, PromptColor> = {
"#EF4444": "red",
"#F97316": "orange",
"#EAB308": "yellow",
"#22C55E": "green",
"#3B82F6": "blue",
"#A855F7": "purple",
};
/**
* Convert color name to hex code for database storage
*/
function colorNameToHex(colorName: PromptColor | null | undefined): string {
if (!colorName) return COLOR_NAME_TO_HEX.blue; // default to blue
return COLOR_NAME_TO_HEX[colorName] || COLOR_NAME_TO_HEX.blue;
}
/**
* Convert hex code to color name for API response
*/
function hexToColorName(hexCode: string | null | undefined): PromptColor {
if (!hexCode) return "blue";
return HEX_TO_COLOR_NAME[hexCode] || "blue";
}
// Note: hexToColorName will be used when reading prompts from database
/**
* Validates that a path is safe (no symlinks, no traversal)
* Accepts both sandboxed (App Store) and non-sandboxed paths
*/
function validatePath(filePath: string): void {
if (filePath.includes("..")) {
throw new PromptekaMCPError(
ErrorCodes.PERMISSION_DENIED,
"Path traversal detected"
);
}
const realPath = fs.realpathSync(filePath);
// Allowed base paths: sandboxed (App Store) and non-sandboxed (dev)
const sandboxedBase = path.join(
os.homedir(),
`Library/Containers/${PROMPTEKA_BUNDLE_ID}/Data/Library/Application Support/prompteka`
);
const nonSandboxedBase = path.join(
os.homedir(),
"Library/Application Support/prompteka"
);
const isInSandboxed = realPath.startsWith(sandboxedBase);
const isInNonSandboxed = realPath.startsWith(nonSandboxedBase);
if (!isInSandboxed && !isInNonSandboxed) {
throw new PromptekaMCPError(
ErrorCodes.PERMISSION_DENIED,
`Path must be under ${sandboxedBase} or ${nonSandboxedBase}`
);
}
const stats = fs.lstatSync(filePath);
if (stats.isSymbolicLink()) {
throw new PromptekaMCPError(
ErrorCodes.PERMISSION_DENIED,
"Symlinks are not allowed"
);
}
}
/**
* Database accessor for full read-write access to Prompteka SQLite database
*/
export class PromptekaDatabaseAccessor {
private db: Database.Database | null = null;
private dbPath: string;
private isOpen = false;
private readonly maxRetries = 3;
private readonly retryBackoffMs = 50;
constructor(dbPath?: string) {
// Determine database path with priority:
// 1. Explicit dbPath parameter
// 2. PROMPTEKA_DB_PATH environment variable
// 3. Sandboxed path (App Store version)
// 4. Non-sandboxed path (dev/direct distribution)
if (dbPath) {
validatePath(dbPath);
this.dbPath = dbPath;
} else if (process.env.PROMPTEKA_DB_PATH) {
const envPath = process.env.PROMPTEKA_DB_PATH.replace(/^~/, os.homedir());
if (!fs.existsSync(envPath)) {
throw new PromptekaMCPError(
ErrorCodes.INTERNAL_ERROR,
`Prompteka database not found at ${envPath} (from PROMPTEKA_DB_PATH). Verify the path is correct.`
);
}
validatePath(envPath);
this.dbPath = envPath;
} else {
// Auto-detect: check sandboxed (App Store) first, then non-sandboxed (dev)
const sandboxedPath = getSandboxedDbPath();
const nonSandboxedPath = getNonSandboxedDbPath();
if (fs.existsSync(sandboxedPath)) {
this.dbPath = sandboxedPath;
getLogger().logDebug("database-accessor", "Using App Store (sandboxed) database", {
path: sandboxedPath,
});
} else if (fs.existsSync(nonSandboxedPath)) {
this.dbPath = nonSandboxedPath;
getLogger().logDebug("database-accessor", "Using non-sandboxed database", {
path: nonSandboxedPath,
});
} else {
throw new PromptekaMCPError(
ErrorCodes.INTERNAL_ERROR,
`Prompteka database not found. Checked:\n` +
` - App Store: ${sandboxedPath}\n` +
` - Dev/Direct: ${nonSandboxedPath}\n` +
`Make sure Prompteka is installed and has been opened at least once.`
);
}
}
}
/**
* Connect to database
*/
connect(): void {
if (this.isOpen) {
return;
}
try {
// Open database with write access
this.db = new Database(this.dbPath);
// Enable WAL mode (for concurrent read/write)
this.db.pragma("journal_mode = WAL");
// Enable foreign keys
this.db.pragma("foreign_keys = ON");
// Verify database schema version matches expectations
const schemaVersionResult = this.db.pragma("schema_version", { simple: true });
const schemaVersion = schemaVersionResult as number;
if (schemaVersion !== PROMPTEKA_SCHEMA_VERSION) {
this.db.close();
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Database schema mismatch: expected version ${PROMPTEKA_SCHEMA_VERSION}, got ${schemaVersion}. ` +
`This may indicate an incompatible Prompteka version. Please verify Prompteka is up to date. ` +
`Write operations are blocked to prevent data corruption.`
);
}
this.isOpen = true;
getLogger().logDebug("database-accessor", "Connected to database", {
dbPath: this.dbPath,
schemaVersion,
});
} catch (error) {
const message =
error instanceof Error ? error.message : "Unknown error";
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to connect to database: ${message}`
);
}
}
/**
* Close database connection
*/
close(): void {
if (this.db) {
this.db.close();
this.db = null;
this.isOpen = false;
}
}
/**
* Health check
*/
async healthCheck(): Promise<boolean> {
try {
if (!this.isOpen || !this.db) {
return false;
}
const result = this.db.prepare("SELECT 1").get();
return result !== undefined;
} catch {
return false;
}
}
/**
* Execute with retry logic for SQLITE_BUSY
* Note: Uses busy-sleep instead of busy-wait to reduce CPU spinning
*/
private executeWithRetry<T>(
fn: () => T,
operationName: string
): T {
let lastError: Error | null = null;
for (let attempt = 0; attempt < this.maxRetries; attempt++) {
try {
return fn();
} catch (error) {
lastError = error as Error;
// Check if this is a SQLITE_BUSY error
if (
lastError.message.includes("SQLITE_BUSY") &&
attempt < this.maxRetries - 1
) {
// Exponential backoff with busy-sleep
const backoff = this.retryBackoffMs * Math.pow(2, attempt);
this.busySleep(backoff);
continue;
}
throw error;
}
}
throw lastError || new Error(`Failed to ${operationName} after retries`);
}
/**
* Synchronous sleep using busy-wait with minimal CPU spinning
* SQLite operations are very fast, so delays are typically <50ms
*/
private busySleep(ms: number): void {
const start = Date.now();
while (Date.now() - start < ms) {
// Minimal CPU work - just check time repeatedly
// For better performance, would need async/await but better-sqlite3 is sync-only
}
}
/**
* Checkpoint the WAL to ensure writes are visible to other connections
* Uses PASSIVE mode for non-blocking, best-effort checkpoint
*
* WAL Safety: When MCP writes data, changes go to the WAL file first. This checkpoint
* merges those changes into the main database file, ensuring the Prompteka app sees
* the updates immediately without requiring a restart.
*/
private checkpoint(): void {
if (!this.db || !this.isOpen) return;
try {
this.db.pragma("wal_checkpoint(PASSIVE)");
} catch (error) {
// Log but don't throw - checkpoint is best-effort, write succeeded
const message = error instanceof Error ? error.message : "Unknown error";
getLogger().logDebug("database-accessor", "WAL checkpoint failed", { error: message });
}
}
/**
* Check if a folder exists
*/
private folderExists(folderId: UUID): boolean {
const result = this.db!.prepare("SELECT 1 FROM folders WHERE id = ?").get(
folderId
);
return result !== undefined;
}
/**
* Check if a prompt exists
*/
private promptExists(promptId: UUID): boolean {
const result = this.db!.prepare("SELECT 1 FROM prompts WHERE id = ?").get(
promptId
);
return result !== undefined;
}
/**
* Detect cycles in folder hierarchy to prevent a folder from being ancestor of itself
*/
private wouldCreateCycle(folderId: UUID, potentialParentId: UUID | null): boolean {
if (potentialParentId === null) {
return false; // Moving to root can't create a cycle
}
if (folderId === potentialParentId) {
return true; // Folder can't be its own parent
}
// Check if potentialParentId is a descendant of folderId
// (which would create a cycle if we make it a parent)
let currentId: UUID | null = potentialParentId;
const visited = new Set<UUID>();
while (currentId !== null && !visited.has(currentId)) {
visited.add(currentId);
if (currentId === folderId) {
return true; // Found a cycle
}
const folder = this.db!
.prepare("SELECT parent_id FROM folders WHERE id = ?")
.get(currentId) as { parent_id: UUID | null } | undefined;
currentId = folder?.parent_id || null;
}
return false;
}
/**
* Find folder by name and parent ID
*/
private findFolderByNameAndParent(
name: string,
parentId: UUID | null | undefined
): Folder | null {
// Properly handle NULL comparisons: use COALESCE to compare NULL-safe
const result = this.db!
.prepare(
"SELECT id, name, parent_id as parentId, created_at as createdAt, updated_at as updatedAt FROM folders " +
"WHERE name = ? AND COALESCE(parent_id, '') = COALESCE(?, '')"
)
.get(name, parentId || null) as {
id: UUID;
name: string;
parentId: UUID | null;
createdAt: string;
updatedAt: string;
} | undefined;
if (!result) {
return null;
}
return {
id: result.id as UUID,
name: result.name,
parentId: result.parentId as UUID | null | undefined,
createdAt: result.createdAt,
updatedAt: result.updatedAt,
};
}
/**
* Find prompt by title (simple match)
*/
private findPromptByTitle(title: string): Prompt | null {
const result = this.db!
.prepare("SELECT id, title, content, folder_id as folderId, emoji, color, url, created_at as createdAt, updated_at as updatedAt FROM prompts WHERE title = ?")
.get(title) as {
id: UUID;
title: string;
content: string;
folderId: UUID | null;
emoji?: string;
color?: string;
url?: string;
createdAt: string;
updatedAt: string;
} | undefined;
if (!result) {
return null;
}
return {
id: result.id as UUID,
title: result.title,
content: result.content,
folderId: result.folderId as UUID | null | undefined,
emoji: result.emoji as Emoji | null | undefined,
color: result.color as PromptColor | null | undefined,
url: result.url,
createdAt: result.createdAt,
updatedAt: result.updatedAt,
};
}
/**
* Get folder depth in hierarchy (for sorting parents before children)
* Returns 0 for root folders, 1 for children of root, etc.
*/
private getFolderDepth(folder: any, allFolders: any[]): number {
let depth = 0;
let currentParentId = folder.parent_id;
while (currentParentId) {
depth++;
const parent = allFolders.find((f) => f.id === currentParentId);
if (!parent) break;
currentParentId = parent.parent_id;
}
return depth;
}
/**
* Recursively delete folder and all its nested descendants
* This is called within a transaction, so it will roll back if any error occurs
*/
private recursivelyDeleteFolder(folderId: UUID): void {
// Find all immediate children
const children = this.db!
.prepare("SELECT id FROM folders WHERE parent_id = ?")
.all(folderId) as Array<{ id: UUID }>;
// Recursively delete all children first (bottom-up)
for (const child of children) {
this.recursivelyDeleteFolder(child.id);
}
// Delete all prompts in this folder
this.db!.prepare("DELETE FROM prompts WHERE folder_id = ?").run(folderId);
// Delete the folder itself (now that all children and prompts are gone)
this.db!.prepare("DELETE FROM folders WHERE id = ?").run(folderId);
}
/**
* List all folders
*/
listFolders(): Folder[] {
this.ensureConnected();
try {
const query = `
SELECT
id,
name,
parent_id as parentId,
created_at as createdAt,
updated_at as updatedAt
FROM folders
ORDER BY name ASC
`;
const folders = this.db!.prepare(query).all() as Array<{
id: UUID;
name: string;
parentId: UUID | null;
createdAt: string;
updatedAt: string;
}>;
return folders.map((f) => ({
id: f.id as UUID,
name: f.name,
parentId: f.parentId as UUID | null | undefined,
createdAt: f.createdAt,
updatedAt: f.updatedAt,
}));
} catch (error) {
const message =
error instanceof Error ? error.message : "Unknown error";
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to list folders: ${message}`
);
}
}
/**
* List prompts with optional folder filter
*/
listPrompts(
folderId?: string | null,
limit: number = 100,
offset: number = 0
): { prompts: Prompt[]; total: number } {
this.ensureConnected();
try {
let query = "FROM prompts WHERE 1=1";
const params: unknown[] = [];
if (folderId !== undefined && folderId !== null) {
query += " AND folder_id = ?";
params.push(folderId);
} else if (folderId === null) {
query += " AND folder_id IS NULL";
}
const countResult = this.db!.prepare(`SELECT COUNT(*) as count ${query}`).get(
...params
) as { count: number };
const total = countResult.count;
const selectQuery = `
SELECT
id,
title,
body,
folder_id as folderId,
icon,
color,
url,
created_at as createdAt,
updated_at as updatedAt
${query}
ORDER BY created_at DESC
LIMIT ? OFFSET ?
`;
const prompts = this.db!.prepare(selectQuery).all(
...params,
limit,
offset
) as Array<{
id: UUID;
title: string;
body: string;
folderId: UUID | null;
icon?: string;
color?: string;
url?: string;
createdAt: number;
updatedAt: number;
}>;
return {
prompts: prompts.map((p) => ({
id: p.id as UUID,
title: p.title,
content: p.body,
folderId: p.folderId as UUID | null | undefined,
emoji: p.icon as Emoji | null | undefined,
color: hexToColorName(p.color),
url: p.url,
createdAt: new Date(p.createdAt * 1000).toISOString(),
updatedAt: new Date(p.updatedAt * 1000).toISOString(),
})),
total,
};
} catch (error) {
const message =
error instanceof Error ? error.message : "Unknown error";
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to list prompts: ${message}`
);
}
}
/**
* Get a single prompt by ID
*/
getPrompt(id: string): Prompt | null {
this.ensureConnected();
try {
const query = `
SELECT
id,
title,
body,
folder_id as folderId,
icon,
color,
url,
created_at as createdAt,
updated_at as updatedAt
FROM prompts
WHERE id = ?
`;
const prompt = this.db!.prepare(query).get(id) as {
id: UUID;
title: string;
body: string;
folderId: UUID | null;
icon?: string;
color?: string;
url?: string;
createdAt: number;
updatedAt: number;
} | undefined;
if (!prompt) {
return null;
}
return {
id: prompt.id as UUID,
title: prompt.title,
content: prompt.body,
folderId: prompt.folderId as UUID | null | undefined,
emoji: prompt.icon as Emoji | null | undefined,
color: hexToColorName(prompt.color),
url: prompt.url,
createdAt: new Date(prompt.createdAt * 1000).toISOString(),
updatedAt: new Date(prompt.updatedAt * 1000).toISOString(),
};
} catch (error) {
const message =
error instanceof Error ? error.message : "Unknown error";
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to get prompt: ${message}`
);
}
}
/**
* Search prompts
*/
searchPrompts(
query: string,
limit: number = 100,
offset: number = 0
): { prompts: Prompt[]; total: number } {
this.ensureConnected();
try {
const likeQuery = `%${query.replace(/"/g, '""')}%`;
const countQuery = `
SELECT COUNT(*) as count FROM prompts
WHERE title LIKE ? OR body LIKE ?
`;
const selectQuery = `
SELECT
id,
title,
body,
folder_id as folderId,
icon,
color,
url,
created_at as createdAt,
updated_at as updatedAt
FROM prompts
WHERE title LIKE ? OR body LIKE ?
ORDER BY created_at DESC
LIMIT ? OFFSET ?
`;
const countResult = this.db!.prepare(countQuery).get(
likeQuery,
likeQuery
) as { count: number };
const total = countResult.count;
const prompts = this.db!.prepare(selectQuery).all(
likeQuery,
likeQuery,
limit,
offset
) as Array<{
id: UUID;
title: string;
body: string;
folderId: UUID | null;
icon?: string;
color?: string;
url?: string;
createdAt: number;
updatedAt: number;
}>;
return {
prompts: prompts.map((p) => ({
id: p.id as UUID,
title: p.title,
content: p.body,
folderId: p.folderId as UUID | null | undefined,
emoji: p.icon as Emoji | null | undefined,
color: hexToColorName(p.color),
url: p.url,
createdAt: new Date(p.createdAt * 1000).toISOString(),
updatedAt: new Date(p.updatedAt * 1000).toISOString(),
})),
total,
};
} catch (error) {
const message =
error instanceof Error ? error.message : "Unknown error";
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to search prompts: ${message}`
);
}
}
/**
* Create prompt
*/
createPrompt(data: {
title: string;
content: string;
folderId: UUID | null;
emoji: Emoji | null | undefined;
color: PromptColor | null | undefined;
url: string | null | undefined;
}): UUID {
this.ensureConnected();
// Validate folder exists if specified
if (data.folderId !== null && !this.folderExists(data.folderId)) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Folder '${data.folderId}' does not exist`
);
}
const id = this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
const id = uuidv4() as UUID;
// Map API fields to database schema:
// - content -> body
// - emoji -> icon
// - color name -> hex code
const stmt = this.db!.prepare(`
INSERT INTO prompts (id, title, body, folder_id, icon, color, url, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`);
stmt.run(
id,
data.title,
data.content,
data.folderId || null,
data.emoji || "📝",
colorNameToHex(data.color),
data.url || null,
Math.floor(Date.now() / 1000), // Unix timestamp in seconds
Math.floor(Date.now() / 1000)
);
// Verify write was successful
const verify = this.db!.prepare("SELECT 1 FROM prompts WHERE id = ?").get(id);
if (!verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
"Failed to verify prompt was created (read-back check failed)"
);
}
return id;
});
return transaction();
}, "create prompt");
this.checkpoint(); // Ensure write is visible to other connections
return id;
}
/**
* Update prompt
*/
updatePrompt(
id: string,
data: Partial<{
title: string;
content: string;
folderId: UUID | null;
emoji: Emoji | null;
color: PromptColor | null;
url: string | null;
}>
): void {
this.ensureConnected();
// Validate prompt exists
if (!this.promptExists(id as UUID)) {
throw new PromptekaMCPError(
ErrorCodes.PROMPT_NOT_FOUND,
`Prompt '${id}' does not exist`
);
}
// Validate folder exists if changing
if ("folderId" in data && data.folderId && !this.folderExists(data.folderId)) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Folder '${data.folderId}' does not exist`
);
}
this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
const now = Math.floor(Date.now() / 1000); // Unix timestamp in seconds
const updates: string[] = [];
const params: unknown[] = [];
if ("title" in data) {
updates.push("title = ?");
params.push(data.title);
}
if ("content" in data) {
// Map API field 'content' to database column 'body'
updates.push("body = ?");
params.push(data.content);
}
if ("folderId" in data) {
updates.push("folder_id = ?");
params.push(data.folderId || null);
}
if ("emoji" in data) {
// Map API field 'emoji' to database column 'icon'
updates.push("icon = ?");
params.push(data.emoji || "📝");
}
if ("color" in data) {
// Map color name to hex code
updates.push("color = ?");
params.push(colorNameToHex(data.color));
}
if ("url" in data) {
updates.push("url = ?");
params.push(data.url || null);
}
if (updates.length === 0) {
return; // Nothing to update
}
updates.push("updated_at = ?");
params.push(now);
params.push(id);
const stmt = this.db!.prepare(`
UPDATE prompts
SET ${updates.join(", ")}
WHERE id = ?
`);
stmt.run(...params);
// Verify update was successful
const verify = this.db!.prepare("SELECT 1 FROM prompts WHERE id = ?").get(id);
if (!verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
"Failed to verify prompt was updated (read-back check failed)"
);
}
});
return transaction();
}, "update prompt");
this.checkpoint(); // Ensure write is visible to other connections
}
/**
* Delete prompt
*/
deletePrompt(id: string): void {
this.ensureConnected();
// Validate prompt exists
if (!this.promptExists(id as UUID)) {
throw new PromptekaMCPError(
ErrorCodes.PROMPT_NOT_FOUND,
`Prompt '${id}' does not exist`
);
}
this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
const stmt = this.db!.prepare("DELETE FROM prompts WHERE id = ?");
stmt.run(id);
// Verify deletion was successful
const verify = this.db!.prepare("SELECT 1 FROM prompts WHERE id = ?").get(id);
if (verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
"Failed to verify prompt was deleted (read-back check failed)"
);
}
});
return transaction();
}, "delete prompt");
this.checkpoint(); // Ensure write is visible to other connections
}
/**
* Create folder
*/
createFolder(data: {
name: string;
parentId: UUID | null | undefined;
}): UUID {
this.ensureConnected();
// Validate parent folder exists if specified
if (data.parentId && !this.folderExists(data.parentId)) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Parent folder '${data.parentId}' does not exist`
);
}
// Check for duplicate folder name within the same parent
const existingFolder = this.findFolderByNameAndParent(
data.name,
data.parentId as UUID | null | undefined
);
if (existingFolder) {
throw new PromptekaMCPError(
ErrorCodes.INVALID_INPUT,
`A folder named '${data.name}' already exists in this location`
);
}
const id = this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
const id = uuidv4() as UUID;
const now = Math.floor(Date.now() / 1000); // Unix seconds
// Get the next position for this folder (max position + 1)
// Use COALESCE for NULL-safe comparison
const maxPositionResult = this.db!
.prepare("SELECT MAX(position) as maxPos FROM folders WHERE COALESCE(parent_id, '') = COALESCE(?, '')")
.get(data.parentId || null) as { maxPos: number | null };
const nextPosition = (maxPositionResult.maxPos ?? -1) + 1;
const stmt = this.db!.prepare(`
INSERT INTO folders (id, name, parent_id, position, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
`);
stmt.run(
id,
data.name,
data.parentId || null,
nextPosition,
now,
now
);
// Verify insertion was successful
const verify = this.db!.prepare("SELECT 1 FROM folders WHERE id = ?").get(id);
if (!verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
"Failed to verify folder was created (read-back check failed)"
);
}
return id;
});
return transaction();
}, "create folder");
this.checkpoint(); // Ensure write is visible to other connections
return id;
}
/**
* Update folder
*/
updateFolder(
id: string,
data: Partial<{
name: string;
parentId: UUID | null;
}>
): void {
this.ensureConnected();
// Validate folder exists
const folderId = id as UUID;
if (!this.folderExists(folderId)) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Folder '${id}' does not exist`
);
}
// Validate parent folder exists if changing
if ("parentId" in data && data.parentId && !this.folderExists(data.parentId)) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Parent folder '${data.parentId}' does not exist`
);
}
// Check for cycles if changing parent
if ("parentId" in data && this.wouldCreateCycle(folderId, data.parentId || null)) {
throw new PromptekaMCPError(
ErrorCodes.INVALID_INPUT,
"Cannot move folder - would create a circular hierarchy"
);
}
// Check for duplicate folder name if name or parent is being changed
if ("name" in data || "parentId" in data) {
// Get current folder to determine the parent (if not changing)
const currentFolder = this.getFolder(id);
if (!currentFolder) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Folder '${id}' not found`
);
}
const newName = "name" in data ? (data.name as string) : currentFolder.name;
const newParentId = "parentId" in data ? (data.parentId || null) : (currentFolder.parentId || null);
// Check if another folder with the same name exists in the target parent
const existingFolder = this.findFolderByNameAndParent(newName, newParentId as UUID | null | undefined);
if (existingFolder && existingFolder.id !== folderId) {
throw new PromptekaMCPError(
ErrorCodes.INVALID_INPUT,
`A folder named '${newName}' already exists in that location`
);
}
}
this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
const now = Math.floor(Date.now() / 1000); // Unix seconds
const updates: string[] = [];
const params: unknown[] = [];
if ("name" in data) {
updates.push("name = ?");
params.push(data.name);
}
if ("parentId" in data) {
updates.push("parent_id = ?");
params.push(data.parentId || null);
}
if (updates.length === 0) {
return;
}
updates.push("updated_at = ?");
params.push(now);
params.push(id);
const stmt = this.db!.prepare(`
UPDATE folders
SET ${updates.join(", ")}
WHERE id = ?
`);
stmt.run(...params);
// Verify update was successful
const verify = this.db!.prepare("SELECT 1 FROM folders WHERE id = ?").get(id);
if (!verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
"Failed to verify folder was updated (read-back check failed)"
);
}
});
return transaction();
}, "update folder");
this.checkpoint(); // Ensure write is visible to other connections
}
/**
* Delete folder
*/
deleteFolder(id: string, recursive: boolean = false): void {
this.ensureConnected();
// Validate folder exists
if (!this.folderExists(id as UUID)) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Folder '${id}' does not exist`
);
}
this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
if (!recursive) {
// Check if folder has children
const result = this.db!
.prepare("SELECT COUNT(*) as count FROM prompts WHERE folder_id = ?")
.get(id) as { count: number };
if (result.count > 0) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_EMPTY,
`Folder has ${result.count} prompts. Use recursive=true to delete with contents.`
);
}
const subfolders = this.db!
.prepare("SELECT COUNT(*) as count FROM folders WHERE parent_id = ?")
.get(id) as { count: number };
if (subfolders.count > 0) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_EMPTY,
`Folder has ${subfolders.count} subfolders. Use recursive=true to delete with contents.`
);
}
} else {
// Recursively delete all nested folders and prompts
this.recursivelyDeleteFolder(id as UUID);
}
// Delete the folder itself
const stmt = this.db!.prepare("DELETE FROM folders WHERE id = ?");
stmt.run(id);
// Verify deletion was successful
const verify = this.db!.prepare("SELECT 1 FROM folders WHERE id = ?").get(id);
if (verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
"Failed to verify folder was deleted (read-back check failed)"
);
}
});
return transaction();
}, "delete folder");
this.checkpoint(); // Ensure write is visible to other connections
}
/**
* Move prompt to different folder
*/
movePrompt(promptId: string, targetFolderId: UUID | null): void {
this.ensureConnected();
// Validate prompt exists
if (!this.promptExists(promptId as UUID)) {
throw new PromptekaMCPError(
ErrorCodes.PROMPT_NOT_FOUND,
`Prompt '${promptId}' does not exist`
);
}
// Validate target folder exists if specified
if (targetFolderId !== null && !this.folderExists(targetFolderId)) {
throw new PromptekaMCPError(
ErrorCodes.FOLDER_NOT_FOUND,
`Target folder '${targetFolderId}' does not exist`
);
}
this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
const now = Math.floor(Date.now() / 1000); // Unix seconds
const stmt = this.db!.prepare(`
UPDATE prompts
SET folder_id = ?, updated_at = ?
WHERE id = ?
`);
stmt.run(targetFolderId || null, now, promptId);
// Verify move was successful
const verify = this.db!
.prepare("SELECT folder_id FROM prompts WHERE id = ?")
.get(promptId) as { folder_id: UUID | null } | undefined;
if (!verify || (targetFolderId !== null && verify.folder_id !== targetFolderId)) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
"Failed to verify prompt was moved (read-back check failed)"
);
}
});
return transaction();
}, "move prompt");
this.checkpoint(); // Ensure write is visible to other connections
}
/**
* Restore backup - atomic operation with full transaction
* Handles folder hierarchy, ID mapping, and overwrite semantics
*/
restoreBackup(
backupFolders: any[],
backupPrompts: any[],
overwrite: boolean
): { importedFolders: number; importedPrompts: number } {
this.ensureConnected();
return this.executeWithRetry(() => {
const transaction = this.db!.transaction(() => {
const folderIdMap = new Map<string, string>();
let importedFolders = 0;
let importedPrompts = 0;
// Sort folders by hierarchy - parents before children
// This ensures parent_id references exist when creating children
const sortedFolders = [...backupFolders].sort((a, b) => {
const aParentDepth = this.getFolderDepth(a, backupFolders);
const bParentDepth = this.getFolderDepth(b, backupFolders);
return aParentDepth - bParentDepth;
});
// Phase 1: Import folders
for (const folder of sortedFolders) {
const oldId = folder.id as string;
const name = folder.name as string;
const parentId = folder.parent_id as string | null | undefined;
// Note: Prompteka database doesn't store emoji/color for folders, only for prompts
// Map parent folder ID if it exists in backup
let mappedParentId: string | null = null;
if (parentId) {
mappedParentId = folderIdMap.get(parentId) || null;
if (!mappedParentId) {
throw new PromptekaMCPError(
ErrorCodes.INVALID_INPUT,
`Folder '${name}' references parent folder '${parentId}' which was not found in backup`
);
}
}
// Check if folder with same name already exists in parent
const existingFolder = this.findFolderByNameAndParent(name, mappedParentId as UUID | null | undefined);
if (existingFolder) {
if (!overwrite) {
// Skip this folder on duplicate when overwrite=false
// Still map old ID to existing folder ID for prompts that reference it
folderIdMap.set(oldId, existingFolder.id);
continue;
} else {
// Overwrite: delete existing folder and its contents, then recreate
// Use cascade delete to handle children
this.db!.prepare("DELETE FROM prompts WHERE folder_id = ?").run(existingFolder.id);
this.db!.prepare("DELETE FROM folders WHERE id = ?").run(existingFolder.id);
}
}
// Create new folder
const newId = uuidv4() as UUID;
const now = Math.floor(Date.now() / 1000); // Unix seconds
// Get the next position for this folder (max position + 1)
// Use COALESCE for NULL-safe comparison
const maxPositionResult = this.db!
.prepare("SELECT MAX(position) as maxPos FROM folders WHERE COALESCE(parent_id, '') = COALESCE(?, '')")
.get(mappedParentId || null) as { maxPos: number | null };
const nextPosition = (maxPositionResult.maxPos ?? -1) + 1;
this.db!.prepare(`
INSERT INTO folders (id, name, parent_id, position, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
`).run(
newId,
name,
mappedParentId || null,
nextPosition,
now,
now
);
// Verify folder was created
const verify = this.db!.prepare("SELECT 1 FROM folders WHERE id = ?").get(newId);
if (!verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to create folder '${name}' in backup restore`
);
}
folderIdMap.set(oldId, newId);
importedFolders++;
}
// Phase 2: Import prompts
for (const prompt of backupPrompts) {
const title = prompt.title as string;
const content = prompt.content || "";
const folderId = prompt.folder_id as string | null | undefined;
const emoji = prompt.emoji || null;
const color = prompt.color || null;
const url = prompt.url || null;
// Map folder ID if it exists in backup
let mappedFolderId: string | null = null;
if (folderId) {
mappedFolderId = folderIdMap.get(folderId) || null;
if (!mappedFolderId) {
throw new PromptekaMCPError(
ErrorCodes.INVALID_INPUT,
`Prompt '${title}' references folder '${folderId}' which was not found in backup`
);
}
}
// Check if prompt with same title already exists
const existingPrompt = this.findPromptByTitle(title);
if (existingPrompt) {
if (!overwrite) {
// Skip duplicate when overwrite=false
continue;
} else {
// Overwrite: delete existing prompt and recreate
this.db!.prepare("DELETE FROM prompts WHERE id = ?").run(existingPrompt.id);
}
}
// Create new prompt
const newId = uuidv4() as UUID;
const now = Math.floor(Date.now() / 1000); // Unix timestamp in seconds
this.db!.prepare(`
INSERT INTO prompts (id, title, body, folder_id, icon, color, url, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`).run(
newId,
title,
content,
mappedFolderId || null,
emoji || "📝",
colorNameToHex(color as PromptColor | null | undefined),
url,
now,
now
);
// Verify prompt was created
const verify = this.db!.prepare("SELECT 1 FROM prompts WHERE id = ?").get(newId);
if (!verify) {
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to create prompt '${title}' in backup restore`
);
}
importedPrompts++;
}
return { importedFolders, importedPrompts };
});
return transaction();
}, "restore backup");
this.checkpoint(); // Ensure write is visible to other connections
}
/**
* Get folder by ID
*/
getFolder(id: string): Folder | null {
this.ensureConnected();
try {
const query = `
SELECT
id,
name,
parent_id as parentId,
created_at as createdAt,
updated_at as updatedAt
FROM folders
WHERE id = ?
`;
const folder = this.db!.prepare(query).get(id) as {
id: UUID;
name: string;
parentId: UUID | null;
createdAt: string;
updatedAt: string;
} | undefined;
if (!folder) {
return null;
}
return {
id: folder.id as UUID,
name: folder.name,
parentId: folder.parentId as UUID | null | undefined,
createdAt: folder.createdAt,
updatedAt: folder.updatedAt,
};
} catch (error) {
const message =
error instanceof Error ? error.message : "Unknown error";
throw new PromptekaMCPError(
ErrorCodes.DATABASE_ERROR,
`Failed to get folder: ${message}`
);
}
}
/**
* Verify schema compatibility
*/
verifySchema(): { compatible: boolean; version?: string } {
this.ensureConnected();
try {
const tables = this.db!
.prepare(
"SELECT name FROM sqlite_master WHERE type='table' AND name IN ('folders', 'prompts')"
)
.all() as Array<{ name: string }>;
if (tables.length !== 2) {
return { compatible: false };
}
return { compatible: true };
} catch {
return { compatible: false };
}
}
/**
* Ensure connection is open
*/
private ensureConnected(): void {
if (!this.isOpen || !this.db) {
this.connect();
}
}
}
/**
* Singleton instance
*/
let accessorInstance: PromptekaDatabaseAccessor | null = null;
export function initializeDatabaseAccessor(
dbPath?: string
): PromptekaDatabaseAccessor {
if (accessorInstance) {
return accessorInstance;
}
accessorInstance = new PromptekaDatabaseAccessor(dbPath);
accessorInstance.connect();
return accessorInstance;
}
export function getDatabaseAccessor(): PromptekaDatabaseAccessor {
if (!accessorInstance) {
accessorInstance = new PromptekaDatabaseAccessor();
accessorInstance.connect();
}
return accessorInstance;
}
