2 files changed, 87 insertions, 0 deletions
diff --git a/migrations/001_initial_schema.sql b/migrations/001_initial_schema.sql
new file mode 100644
index 0000000..26d1eac
--- /dev/null
+++ b/migrations/001_initial_schema.sql
@@ -0,0 +1,70 @@
+-- Initial schema for Personal Consolidation Dashboard
+
+-- Tasks table (Todoist)
+CREATE TABLE IF NOT EXISTS tasks (
+    id TEXT PRIMARY KEY,
+    content TEXT NOT NULL,
+    description TEXT,
+    project_id TEXT,
+    project_name TEXT,
+    due_date DATETIME,
+    priority INTEGER DEFAULT 1,
+    completed BOOLEAN DEFAULT 0,
+    labels TEXT, -- JSON array
+    url TEXT,
+    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE INDEX IF NOT EXISTS idx_tasks_due_date ON tasks(due_date);
+CREATE INDEX IF NOT EXISTS idx_tasks_completed ON tasks(completed);
+CREATE INDEX IF NOT EXISTS idx_tasks_project_id ON tasks(project_id);
+
+-- Notes table (Obsidian)
+CREATE TABLE IF NOT EXISTS notes (
+    filename TEXT PRIMARY KEY,
+    title TEXT NOT NULL,
+    content TEXT,
+    modified DATETIME NOT NULL,
+    path TEXT NOT NULL,
+    tags TEXT, -- JSON array
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE INDEX IF NOT EXISTS idx_notes_modified ON notes(modified DESC);
+
+-- Meals table (PlanToEat)
+CREATE TABLE IF NOT EXISTS meals (
+    id TEXT PRIMARY KEY,
+    recipe_name TEXT NOT NULL,
+    date DATE NOT NULL,
+    meal_type TEXT CHECK(meal_type IN ('breakfast', 'lunch', 'dinner', 'snack')),
+    recipe_url TEXT,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE INDEX IF NOT EXISTS idx_meals_date ON meals(date);
+CREATE INDEX IF NOT EXISTS idx_meals_type ON meals(meal_type);
+
+-- Boards table (Trello)
+CREATE TABLE IF NOT EXISTS boards (
+    id TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Cards table (Trello)
+CREATE TABLE IF NOT EXISTS cards (
+    id TEXT PRIMARY KEY,
+    name TEXT NOT NULL,
+    board_id TEXT NOT NULL,
+    list_id TEXT,
+    list_name TEXT,
+    due_date DATETIME,
+    url TEXT,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
+    FOREIGN KEY (board_id) REFERENCES boards(id) ON DELETE CASCADE
+);
+
+CREATE INDEX IF NOT EXISTS idx_cards_board_id ON cards(board_id);
+CREATE INDEX IF NOT EXISTS idx_cards_due_date ON cards(due_date);
diff --git a/migrations/002_add_cache_metadata.sql b/migrations/002_add_cache_metadata.sql
new file mode 100644
index 0000000..da6c0ce
--- /dev/null
+++ b/migrations/002_add_cache_metadata.sql
@@ -0,0 +1,17 @@
+-- Cache metadata table for tracking API fetch times and TTL
+
+CREATE TABLE IF NOT EXISTS cache_metadata (
+    key TEXT PRIMARY KEY,
+    last_fetch DATETIME NOT NULL,
+    ttl_minutes INTEGER NOT NULL DEFAULT 5,
+    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
+);
+
+-- Pre-populate with cache keys
+INSERT OR IGNORE INTO cache_metadata (key, last_fetch, ttl_minutes) VALUES
+    ('todoist_tasks', '2000-01-01 00:00:00', 5),
+    ('todoist_projects', '2000-01-01 00:00:00', 5),
+    ('obsidian_notes', '2000-01-01 00:00:00', 5),
+    ('plantoeat_meals', '2000-01-01 00:00:00', 5),
+    ('trello_boards', '2000-01-01 00:00:00', 5),
+    ('trello_cards', '2000-01-01 00:00:00', 5);