/**
* Self MCP Server - Database Module
* Usando bun:sqlite para acesso ao SQLite
*
* Suporta tanto uso local (singleton) quanto multi-tenant (instância injetada)
*/
import { Database } from 'bun:sqlite'
import { readFileSync, existsSync } from 'fs'
import { join, dirname } from 'path'
import { fileURLToPath } from 'url'
const __dirname = dirname(fileURLToPath(import.meta.url))
/**
* Cria funções de banco de dados usando a instância fornecida
* @param {Database} db - Instância do SQLite database
* @returns {Object} Objeto com todas as funções de banco de dados
*/
export function createDbFunctions(db) {
// ============================================
// FOUNDATIONS (valores, anti-valores, identidade)
// ============================================
function getFoundations() {
return db.query('SELECT * FROM foundations ORDER BY type, id').all()
}
function getFoundationsByType(type) {
return db.query('SELECT * FROM foundations WHERE type = ?').all(type)
}
function addFoundation(type, content) {
const stmt = db.prepare('INSERT INTO foundations (type, content) VALUES (?, ?)')
const result = stmt.run(type, content)
return { id: result.lastInsertRowid, type, content }
}
function updateFoundation(id, content) {
db.prepare('UPDATE foundations SET content = ? WHERE id = ?').run(content, id)
return db.query('SELECT * FROM foundations WHERE id = ?').get(id)
}
function deleteFoundation(id) {
db.prepare('DELETE FROM foundations WHERE id = ?').run(id)
}
// ============================================
// INTENTIONS (problema, curiosidade, identidade, experiência)
// ============================================
function getIntentions() {
return db.query('SELECT * FROM intentions ORDER BY is_primary DESC, id').all()
}
function getIntentionsByType(type) {
return db.query('SELECT * FROM intentions WHERE type = ?').all(type)
}
function getPrimaryIntention() {
return db.query('SELECT * FROM intentions WHERE is_primary = 1').get()
}
function addIntention(type, content, isPrimary = false) {
// Se for primária, remove a flag de outras
if (isPrimary) {
db.prepare('UPDATE intentions SET is_primary = 0').run()
}
const stmt = db.prepare('INSERT INTO intentions (type, content, is_primary) VALUES (?, ?, ?)')
const result = stmt.run(type, content, isPrimary ? 1 : 0)
return { id: result.lastInsertRowid, type, content, is_primary: isPrimary }
}
function updateIntention(id, content, isPrimary) {
if (isPrimary !== undefined) {
if (isPrimary) {
db.prepare('UPDATE intentions SET is_primary = 0').run()
}
db.prepare('UPDATE intentions SET content = ?, is_primary = ? WHERE id = ?').run(content, isPrimary ? 1 : 0, id)
} else {
db.prepare('UPDATE intentions SET content = ? WHERE id = ?').run(content, id)
}
return db.query('SELECT * FROM intentions WHERE id = ?').get(id)
}
function deleteIntention(id) {
db.prepare('DELETE FROM intentions WHERE id = ?').run(id)
}
// ============================================
// MISSIONS (missões de longo prazo)
// ============================================
function getMissions() {
return db
.query(
`
SELECT m.*, i.type as intention_type, i.content as intention_content
FROM missions m
LEFT JOIN intentions i ON m.intention_id = i.id
ORDER BY m.code
`
)
.all()
}
function getMissionByCode(code) {
return db.query('SELECT * FROM missions WHERE code = ?').get(code)
}
function addMission(code, intentionId, content) {
const stmt = db.prepare('INSERT INTO missions (code, intention_id, content) VALUES (?, ?, ?)')
const result = stmt.run(code, intentionId, content)
return {
id: result.lastInsertRowid,
code,
intention_id: intentionId,
content,
}
}
function updateMission(id, content) {
db.prepare('UPDATE missions SET content = ? WHERE id = ?').run(content, id)
return db.query('SELECT * FROM missions WHERE id = ?').get(id)
}
function deleteMission(id) {
db.prepare('DELETE FROM missions WHERE id = ?').run(id)
}
// ============================================
// GOALS (metas anuais)
// ============================================
function getGoals(year = null) {
if (year) {
return db
.query(
`
SELECT g.*, m.code as mission_code, m.content as mission_content
FROM goals g
LEFT JOIN missions m ON g.mission_id = m.id
WHERE g.year = ?
ORDER BY g.code
`
)
.all(year)
}
return db
.query(
`
SELECT g.*, m.code as mission_code, m.content as mission_content
FROM goals g
LEFT JOIN missions m ON g.mission_id = m.id
ORDER BY g.year DESC, g.code
`
)
.all()
}
function getGoalByCode(code) {
return db.query('SELECT * FROM goals WHERE code = ?').get(code)
}
function addGoal(code, missionId, year, content, metric = null) {
const stmt = db.prepare('INSERT INTO goals (code, mission_id, year, content, metric) VALUES (?, ?, ?, ?, ?)')
const result = stmt.run(code, missionId, year, content, metric)
return {
id: result.lastInsertRowid,
code,
mission_id: missionId,
year,
content,
metric,
}
}
function updateGoal(id, content, metric, completed) {
if (completed !== undefined) {
db.prepare('UPDATE goals SET content = ?, metric = ?, completed = ? WHERE id = ?').run(content, metric, completed ? 1 : 0, id)
} else {
db.prepare('UPDATE goals SET content = ?, metric = ? WHERE id = ?').run(content, metric, id)
}
return db.query('SELECT * FROM goals WHERE id = ?').get(id)
}
function deleteGoal(id) {
db.prepare('DELETE FROM goals WHERE id = ?').run(id)
}
// ============================================
// PROJECTS (projetos ativos)
// ============================================
function getProjects(status = null) {
if (status) {
return db
.query(
`
SELECT p.*, g.code as goal_code, g.content as goal_content
FROM projects p
LEFT JOIN goals g ON p.goal_id = g.id
WHERE p.status = ?
ORDER BY p.code
`
)
.all(status)
}
return db
.query(
`
SELECT p.*, g.code as goal_code, g.content as goal_content
FROM projects p
LEFT JOIN goals g ON p.goal_id = g.id
ORDER BY p.status, p.code
`
)
.all()
}
function getProjectByCode(code) {
return db.query('SELECT * FROM projects WHERE code = ?').get(code)
}
function addProject(code, goalId, content) {
const stmt = db.prepare('INSERT INTO projects (code, goal_id, content) VALUES (?, ?, ?)')
const result = stmt.run(code, goalId, content)
return {
id: result.lastInsertRowid,
code,
goal_id: goalId,
content,
status: 'active',
}
}
function updateProject(id, content, status) {
if (status) {
db.prepare('UPDATE projects SET content = ?, status = ? WHERE id = ?').run(content, status, id)
} else {
db.prepare('UPDATE projects SET content = ? WHERE id = ?').run(content, id)
}
return db.query('SELECT * FROM projects WHERE id = ?').get(id)
}
function deleteProject(id) {
db.prepare('DELETE FROM projects WHERE id = ?').run(id)
}
// ============================================
// OBSTACLES (obstáculos e estratégias)
// ============================================
function getObstacles(resolved = null) {
if (resolved !== null) {
return db.query('SELECT * FROM obstacles WHERE resolved = ? ORDER BY code').all(resolved ? 1 : 0)
}
return db.query('SELECT * FROM obstacles ORDER BY resolved, code').all()
}
function getObstacleByCode(code) {
return db.query('SELECT * FROM obstacles WHERE code = ?').get(code)
}
function addObstacle(code, obstacle, strategy = null) {
const stmt = db.prepare('INSERT INTO obstacles (code, obstacle, strategy) VALUES (?, ?, ?)')
const result = stmt.run(code, obstacle, strategy)
return {
id: result.lastInsertRowid,
code,
obstacle,
strategy,
resolved: false,
}
}
function updateObstacle(id, obstacle, strategy, resolved) {
if (resolved !== undefined) {
db.prepare('UPDATE obstacles SET obstacle = ?, strategy = ?, resolved = ? WHERE id = ?').run(obstacle, strategy, resolved ? 1 : 0, id)
} else {
db.prepare('UPDATE obstacles SET obstacle = ?, strategy = ? WHERE id = ?').run(obstacle, strategy, id)
}
return db.query('SELECT * FROM obstacles WHERE id = ?').get(id)
}
function deleteObstacle(id) {
db.prepare('DELETE FROM obstacles WHERE id = ?').run(id)
}
// ============================================
// DAILY (registro diário: vitórias, aprendizados, desvios)
// ============================================
function getDaily(limit = 50) {
return db
.query(
`
SELECT d.*, m.code as mission_code, m.content as mission_content
FROM daily d
LEFT JOIN missions m ON d.mission_id = m.id
ORDER BY d.date DESC, d.id DESC
LIMIT ?
`
)
.all(limit)
}
function getDailyByType(type, limit = 50) {
return db
.query(
`
SELECT d.*, m.code as mission_code
FROM daily d
LEFT JOIN missions m ON d.mission_id = m.id
WHERE d.type = ?
ORDER BY d.date DESC
LIMIT ?
`
)
.all(type, limit)
}
function addDaily(date, type, content, missionId = null) {
const stmt = db.prepare('INSERT INTO daily (date, type, content, mission_id) VALUES (?, ?, ?, ?)')
const result = stmt.run(date, type, content, missionId)
return {
id: result.lastInsertRowid,
date,
type,
content,
mission_id: missionId,
}
}
function deleteDaily(id) {
db.prepare('DELETE FROM daily WHERE id = ?').run(id)
}
// ============================================
// METADATA
// ============================================
function getMetadata(key) {
const row = db.query('SELECT value FROM metadata WHERE key = ?').get(key)
return row ? row.value : null
}
function setMetadata(key, value) {
db.prepare('INSERT OR REPLACE INTO metadata (key, value) VALUES (?, ?)').run(key, value)
}
// ============================================
// UTILITIES
// ============================================
function getFullProfile() {
return {
foundations: getFoundations(),
intentions: getIntentions(),
missions: getMissions(),
goals: getGoals(),
projects: getProjects(),
obstacles: getObstacles(),
daily: getDaily(20),
metadata: {
lastUpdated: getMetadata('last_updated') || new Date().toISOString(),
},
}
}
function clearAll() {
db.exec(`
DELETE FROM daily;
DELETE FROM projects;
DELETE FROM goals;
DELETE FROM missions;
DELETE FROM obstacles;
DELETE FROM intentions;
DELETE FROM foundations;
DELETE FROM metadata;
`)
}
function isEmpty() {
const count = db.query('SELECT COUNT(*) as count FROM foundations').get()
return count.count === 0
}
// Atualiza last_updated ao modificar dados
function touchLastUpdated() {
setMetadata('last_updated', new Date().toISOString())
}
return {
// Foundations
getFoundations,
getFoundationsByType,
addFoundation,
updateFoundation,
deleteFoundation,
// Intentions
getIntentions,
getIntentionsByType,
getPrimaryIntention,
addIntention,
updateIntention,
deleteIntention,
// Missions
getMissions,
getMissionByCode,
addMission,
updateMission,
deleteMission,
// Goals
getGoals,
getGoalByCode,
addGoal,
updateGoal,
deleteGoal,
// Projects
getProjects,
getProjectByCode,
addProject,
updateProject,
deleteProject,
// Obstacles
getObstacles,
getObstacleByCode,
addObstacle,
updateObstacle,
deleteObstacle,
// Daily
getDaily,
getDailyByType,
addDaily,
deleteDaily,
// Metadata
getMetadata,
setMetadata,
// Utilities
getFullProfile,
clearAll,
isEmpty,
touchLastUpdated,
}
}
// ============================================
// Singleton para uso local (stdio server)
// ============================================
const dbPath = join(__dirname, 'self.db')
const schemaPath = join(__dirname, 'schema.sql')
// Cria banco local apenas se não estiver em ambiente de serviço
let localDb = null
let localDbFunctions = null
function getLocalDb() {
if (!localDb) {
localDb = new Database(dbPath)
// Aplica o schema
if (existsSync(schemaPath)) {
const schema = readFileSync(schemaPath, 'utf-8')
localDb.exec(schema)
}
localDbFunctions = createDbFunctions(localDb)
}
return localDbFunctions
}
// Export default é um proxy que usa o singleton local
const defaultDb = new Proxy(
{},
{
get(target, prop) {
const db = getLocalDb()
return db[prop]
},
}
)
export default defaultDb
