Initial commit: Personal Consolidation Dashboard (Phase 1 Complete)

Implemented a unified web dashboard aggregating tasks, notes, and meal planning:

Core Features:
- Trello integration (PRIMARY feature - boards, cards, lists)
- Todoist integration (tasks and projects)
- Obsidian integration (20 most recent notes)
- PlanToEat integration (optional - 7-day meal planning)
- Mobile-responsive web UI with auto-refresh (5 min)
- SQLite caching with 5-minute TTL
- AI agent endpoint with Bearer token authentication

Technical Implementation:
- Go 1.21+ backend with chi router
- Interface-based API client design for testability
- Parallel data fetching with goroutines
- Graceful degradation (partial data on API failures)
- .env file loading with godotenv
- Comprehensive test coverage (9/9 tests passing)

Bug Fixes:
- Fixed .env file not being loaded at startup
- Fixed nil pointer dereference with optional API clients (typed nil interface gotcha)

Documentation:
- START_HERE.md - Quick 5-minute setup guide
- QUICKSTART.md - Fast track setup
- SETUP_GUIDE.md - Detailed step-by-step instructions
- PROJECT_SUMMARY.md - Complete project overview
- CLAUDE.md - Guide for Claude Code instances
- AI_AGENT_ACCESS.md - AI agent design document
- AI_AGENT_SETUP.md - Claude.ai integration guide
- TRELLO_AUTH_UPDATE.md - New Power-Up auth process

Statistics:
- Binary: 17MB
- Code: 2,667 lines
- Tests: 5 unit + 4 acceptance tests (all passing)
- Dependencies: chi, sqlite3, godotenv

Co-Authored-By: Claude Sonnet 4.5 <noreply@anthropic.com>

1 files changed, 484 insertions, 0 deletions

diff --git a/internal/store/sqlite.go b/internal/store/sqlite.go
new file mode 100644
index 0000000..45d7746
--- /dev/null
+++ b/internal/store/sqlite.go
@@ -0,0 +1,484 @@
+package store
+
+import (
+	"database/sql"
+	"encoding/json"
+	"fmt"
+	"os"
+	"path/filepath"
+	"sort"
+	"time"
+
+	_ "github.com/mattn/go-sqlite3"
+	"task-dashboard/internal/models"
+)
+
+type Store struct {
+	db *sql.DB
+}
+
+// New creates a new Store instance and runs migrations
+func New(dbPath string) (*Store, error) {
+	db, err := sql.Open("sqlite3", dbPath)
+	if err != nil {
+		return nil, fmt.Errorf("failed to open database: %w", err)
+	}
+
+	// Enable foreign keys
+	if _, err := db.Exec("PRAGMA foreign_keys = ON"); err != nil {
+		return nil, fmt.Errorf("failed to enable foreign keys: %w", err)
+	}
+
+	store := &Store{db: db}
+
+	// Run migrations
+	if err := store.runMigrations(); err != nil {
+		return nil, fmt.Errorf("failed to run migrations: %w", err)
+	}
+
+	return store, nil
+}
+
+// Close closes the database connection
+func (s *Store) Close() error {
+	return s.db.Close()
+}
+
+// runMigrations executes all migration files in order
+func (s *Store) runMigrations() error {
+	// Get migration files
+	migrationFiles, err := filepath.Glob("migrations/*.sql")
+	if err != nil {
+		return fmt.Errorf("failed to read migration files: %w", err)
+	}
+
+	// Sort migrations by filename
+	sort.Strings(migrationFiles)
+
+	// Execute each migration
+	for _, file := range migrationFiles {
+		content, err := os.ReadFile(file)
+		if err != nil {
+			return fmt.Errorf("failed to read migration %s: %w", file, err)
+		}
+
+		if _, err := s.db.Exec(string(content)); err != nil {
+			return fmt.Errorf("failed to execute migration %s: %w", file, err)
+		}
+	}
+
+	return nil
+}
+
+// Tasks operations
+
+// SaveTasks saves multiple tasks to the database
+func (s *Store) SaveTasks(tasks []models.Task) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	stmt, err := tx.Prepare(`
+		INSERT OR REPLACE INTO tasks
+		(id, content, description, project_id, project_name, due_date, priority, completed, labels, url, created_at, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
+	`)
+	if err != nil {
+		return err
+	}
+	defer stmt.Close()
+
+	for _, task := range tasks {
+		labelsJSON, _ := json.Marshal(task.Labels)
+		_, err := stmt.Exec(
+			task.ID,
+			task.Content,
+			task.Description,
+			task.ProjectID,
+			task.ProjectName,
+			task.DueDate,
+			task.Priority,
+			task.Completed,
+			string(labelsJSON),
+			task.URL,
+			task.CreatedAt,
+		)
+		if err != nil {
+			return err
+		}
+	}
+
+	return tx.Commit()
+}
+
+// GetTasks retrieves all tasks from the database
+func (s *Store) GetTasks() ([]models.Task, error) {
+	rows, err := s.db.Query(`
+		SELECT id, content, description, project_id, project_name, due_date, priority, completed, labels, url, created_at
+		FROM tasks
+		ORDER BY completed ASC, due_date ASC, priority DESC
+	`)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	var tasks []models.Task
+	for rows.Next() {
+		var task models.Task
+		var labelsJSON string
+		var dueDate sql.NullTime
+
+		err := rows.Scan(
+			&task.ID,
+			&task.Content,
+			&task.Description,
+			&task.ProjectID,
+			&task.ProjectName,
+			&dueDate,
+			&task.Priority,
+			&task.Completed,
+			&labelsJSON,
+			&task.URL,
+			&task.CreatedAt,
+		)
+		if err != nil {
+			return nil, err
+		}
+
+		if dueDate.Valid {
+			task.DueDate = &dueDate.Time
+		}
+
+		json.Unmarshal([]byte(labelsJSON), &task.Labels)
+		tasks = append(tasks, task)
+	}
+
+	return tasks, rows.Err()
+}
+
+// Notes operations
+
+// SaveNotes saves multiple notes to the database
+func (s *Store) SaveNotes(notes []models.Note) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	stmt, err := tx.Prepare(`
+		INSERT OR REPLACE INTO notes
+		(filename, title, content, modified, path, tags, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
+	`)
+	if err != nil {
+		return err
+	}
+	defer stmt.Close()
+
+	for _, note := range notes {
+		tagsJSON, _ := json.Marshal(note.Tags)
+		_, err := stmt.Exec(
+			note.Filename,
+			note.Title,
+			note.Content,
+			note.Modified,
+			note.Path,
+			string(tagsJSON),
+		)
+		if err != nil {
+			return err
+		}
+	}
+
+	return tx.Commit()
+}
+
+// GetNotes retrieves all notes from the database
+func (s *Store) GetNotes(limit int) ([]models.Note, error) {
+	query := `
+		SELECT filename, title, content, modified, path, tags
+		FROM notes
+		ORDER BY modified DESC
+	`
+	if limit > 0 {
+		query += fmt.Sprintf(" LIMIT %d", limit)
+	}
+
+	rows, err := s.db.Query(query)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	var notes []models.Note
+	for rows.Next() {
+		var note models.Note
+		var tagsJSON string
+
+		err := rows.Scan(
+			&note.Filename,
+			&note.Title,
+			&note.Content,
+			&note.Modified,
+			&note.Path,
+			&tagsJSON,
+		)
+		if err != nil {
+			return nil, err
+		}
+
+		json.Unmarshal([]byte(tagsJSON), &note.Tags)
+		notes = append(notes, note)
+	}
+
+	return notes, rows.Err()
+}
+
+// Meals operations
+
+// SaveMeals saves multiple meals to the database
+func (s *Store) SaveMeals(meals []models.Meal) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	stmt, err := tx.Prepare(`
+		INSERT OR REPLACE INTO meals
+		(id, recipe_name, date, meal_type, recipe_url, updated_at)
+		VALUES (?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
+	`)
+	if err != nil {
+		return err
+	}
+	defer stmt.Close()
+
+	for _, meal := range meals {
+		_, err := stmt.Exec(
+			meal.ID,
+			meal.RecipeName,
+			meal.Date,
+			meal.MealType,
+			meal.RecipeURL,
+		)
+		if err != nil {
+			return err
+		}
+	}
+
+	return tx.Commit()
+}
+
+// GetMeals retrieves meals from the database
+func (s *Store) GetMeals(startDate, endDate time.Time) ([]models.Meal, error) {
+	rows, err := s.db.Query(`
+		SELECT id, recipe_name, date, meal_type, recipe_url
+		FROM meals
+		WHERE date BETWEEN ? AND ?
+		ORDER BY date ASC,
+			CASE meal_type
+				WHEN 'breakfast' THEN 1
+				WHEN 'lunch' THEN 2
+				WHEN 'dinner' THEN 3
+				ELSE 4
+			END
+	`, startDate, endDate)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	var meals []models.Meal
+	for rows.Next() {
+		var meal models.Meal
+		err := rows.Scan(
+			&meal.ID,
+			&meal.RecipeName,
+			&meal.Date,
+			&meal.MealType,
+			&meal.RecipeURL,
+		)
+		if err != nil {
+			return nil, err
+		}
+		meals = append(meals, meal)
+	}
+
+	return meals, rows.Err()
+}
+
+// Cache metadata operations
+
+// GetCacheMetadata retrieves cache metadata for a key
+func (s *Store) GetCacheMetadata(key string) (*models.CacheMetadata, error) {
+	var cm models.CacheMetadata
+	err := s.db.QueryRow(`
+		SELECT key, last_fetch, ttl_minutes
+		FROM cache_metadata
+		WHERE key = ?
+	`, key).Scan(&cm.Key, &cm.LastFetch, &cm.TTLMinutes)
+
+	if err == sql.ErrNoRows {
+		return nil, nil
+	}
+	if err != nil {
+		return nil, err
+	}
+
+	return &cm, nil
+}
+
+// UpdateCacheMetadata updates the last fetch time for a cache key
+func (s *Store) UpdateCacheMetadata(key string, ttlMinutes int) error {
+	_, err := s.db.Exec(`
+		INSERT OR REPLACE INTO cache_metadata (key, last_fetch, ttl_minutes, updated_at)
+		VALUES (?, CURRENT_TIMESTAMP, ?, CURRENT_TIMESTAMP)
+	`, key, ttlMinutes)
+	return err
+}
+
+// IsCacheValid checks if the cache for a given key is still valid
+func (s *Store) IsCacheValid(key string) (bool, error) {
+	cm, err := s.GetCacheMetadata(key)
+	if err != nil {
+		return false, err
+	}
+	if cm == nil {
+		return false, nil
+	}
+
+	return cm.IsCacheValid(), nil
+}
+
+// Boards operations
+
+// SaveBoards saves multiple boards to the database
+func (s *Store) SaveBoards(boards []models.Board) error {
+	tx, err := s.db.Begin()
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	// Save boards
+	boardStmt, err := tx.Prepare(`
+		INSERT OR REPLACE INTO boards (id, name, updated_at)
+		VALUES (?, ?, CURRENT_TIMESTAMP)
+	`)
+	if err != nil {
+		return err
+	}
+	defer boardStmt.Close()
+
+	// Save cards
+	cardStmt, err := tx.Prepare(`
+		INSERT OR REPLACE INTO cards
+		(id, name, board_id, list_id, list_name, due_date, url, updated_at)
+		VALUES (?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
+	`)
+	if err != nil {
+		return err
+	}
+	defer cardStmt.Close()
+
+	for _, board := range boards {
+		_, err := boardStmt.Exec(board.ID, board.Name)
+		if err != nil {
+			return err
+		}
+
+		// Save all cards for this board
+		for _, card := range board.Cards {
+			_, err := cardStmt.Exec(
+				card.ID,
+				card.Name,
+				board.ID,
+				card.ListID,
+				card.ListName,
+				card.DueDate,
+				card.URL,
+			)
+			if err != nil {
+				return err
+			}
+		}
+	}
+
+	return tx.Commit()
+}
+
+// GetBoards retrieves all boards with their cards from the database
+func (s *Store) GetBoards() ([]models.Board, error) {
+	// Fetch boards
+	boardRows, err := s.db.Query(`
+		SELECT id, name FROM boards ORDER BY name
+	`)
+	if err != nil {
+		return nil, err
+	}
+	defer boardRows.Close()
+
+	var boards []models.Board
+	boardMap := make(map[string]*models.Board)
+
+	for boardRows.Next() {
+		var board models.Board
+		err := boardRows.Scan(&board.ID, &board.Name)
+		if err != nil {
+			return nil, err
+		}
+		board.Cards = []models.Card{}
+		boards = append(boards, board)
+		boardMap[board.ID] = &boards[len(boards)-1]
+	}
+
+	if err := boardRows.Err(); err != nil {
+		return nil, err
+	}
+
+	// Fetch cards
+	cardRows, err := s.db.Query(`
+		SELECT id, name, board_id, list_id, list_name, due_date, url
+		FROM cards
+		ORDER BY board_id, list_name, name
+	`)
+	if err != nil {
+		return nil, err
+	}
+	defer cardRows.Close()
+
+	for cardRows.Next() {
+		var card models.Card
+		var boardID string
+		var dueDate sql.NullTime
+
+		err := cardRows.Scan(
+			&card.ID,
+			&card.Name,
+			&boardID,
+			&card.ListID,
+			&card.ListName,
+			&dueDate,
+			&card.URL,
+		)
+		if err != nil {
+			return nil, err
+		}
+
+		if dueDate.Valid {
+			card.DueDate = &dueDate.Time
+		}
+
+		// Add card to the appropriate board
+		if board, ok := boardMap[boardID]; ok {
+			board.Cards = append(board.Cards, card)
+		}
+	}
+
+	return boards, cardRows.Err()
+}