-- Self MCP Server - Database Schema
-- Usando SQLite com Bun
-- Fundamentos: valores e identidade
CREATE TABLE IF NOT EXISTS foundations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT NOT NULL CHECK(type IN ('value', 'anti_value', 'identity')),
content TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Intenções: o que te move
CREATE TABLE IF NOT EXISTS intentions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
type TEXT NOT NULL CHECK(type IN ('problem', 'curiosity', 'identity', 'experience')),
content TEXT NOT NULL,
is_primary INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Missões: ações de longo prazo
CREATE TABLE IF NOT EXISTS missions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT UNIQUE NOT NULL,
intention_id INTEGER,
content TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (intention_id) REFERENCES intentions(id)
);
-- Metas: objetivos anuais
CREATE TABLE IF NOT EXISTS goals (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT UNIQUE NOT NULL,
mission_id INTEGER,
year INTEGER NOT NULL,
content TEXT NOT NULL,
metric TEXT,
completed INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (mission_id) REFERENCES missions(id)
);
-- Projetos: trabalho ativo
CREATE TABLE IF NOT EXISTS projects (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT UNIQUE NOT NULL,
goal_id INTEGER,
content TEXT NOT NULL,
status TEXT DEFAULT 'active' CHECK(status IN ('active', 'paused', 'done')),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (goal_id) REFERENCES goals(id)
);
-- Obstáculos e estratégias
CREATE TABLE IF NOT EXISTS obstacles (
id INTEGER PRIMARY KEY AUTOINCREMENT,
code TEXT UNIQUE NOT NULL,
obstacle TEXT NOT NULL,
strategy TEXT,
resolved INTEGER DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
-- Registro diário (vitórias, aprendizados, desvios)
CREATE TABLE IF NOT EXISTS daily (
id INTEGER PRIMARY KEY AUTOINCREMENT,
date DATE NOT NULL,
type TEXT NOT NULL CHECK(type IN ('victory', 'learning', 'deviation')),
content TEXT NOT NULL,
mission_id INTEGER,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (mission_id) REFERENCES missions(id)
);
-- Metadados do Self
CREATE TABLE IF NOT EXISTS metadata (
key TEXT PRIMARY KEY,
value TEXT
);
-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_foundations_type ON foundations(type);
CREATE INDEX IF NOT EXISTS idx_intentions_type ON intentions(type);
CREATE INDEX IF NOT EXISTS idx_goals_year ON goals(year);
CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
CREATE INDEX IF NOT EXISTS idx_daily_date ON daily(date);
