From 9fe0998436488537a8a2e8ffeefb0c4424b41c60 Mon Sep 17 00:00:00 2001 From: Peter Stone Date: Mon, 12 Jan 2026 09:27:16 -1000 Subject: 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 --- internal/store/sqlite.go | 484 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 484 insertions(+) create mode 100644 internal/store/sqlite.go (limited to 'internal/store/sqlite.go') 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( + ¬e.Filename, + ¬e.Title, + ¬e.Content, + ¬e.Modified, + ¬e.Path, + &tagsJSON, + ) + if err != nil { + return nil, err + } + + json.Unmarshal([]byte(tagsJSON), ¬e.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() +} -- cgit v1.2.3