How to Build a Habit Tracker with Replit

1// Prompt to type into Replit Agent:
2// Build a habit tracker app with Express and PostgreSQL using Drizzle ORM.
3// Create these tables in shared/schema.ts:
4// - habits: id serial pk, user_id text not null, name text not null,
5//   description text, color text not null default '#3B82F6',
6//   frequency text default 'daily' (daily/weekdays/weekends/weekly),
7//   target_count integer default 1,
8//   reminder_time text (format 'HH:MM', nullable),
9//   is_archived boolean default false, created_at timestamp
10// - completions: id serial pk, habit_id integer references habits not null,
11//   completed_date date not null, count integer default 1,
12//   notes text, created_at timestamp,
13//   UNIQUE constraint on (habit_id, completed_date)
14// - streak_cache: id serial pk, habit_id integer references habits not null unique,
15//   current_streak integer default 0, longest_streak integer default 0,
16//   last_completed date, total_completions integer default 0,
17//   updated_at timestamp
18// Create a PostgreSQL function calculate_streak(p_habit_id integer)
19// that computes current_streak from completions table and upserts into streak_cache.
20// Set up Replit Auth. Bind server to 0.0.0.0.

1const { db } = require('../db');
2const { habits, completions, streakCache } = require('../../shared/schema');
3const { eq, and, sql } = require('drizzle-orm');
4
5router.get('/api/habits', async (req, res) => {
6  if (!req.user) return res.status(401).json({ error: 'Auth required' });
7
8  const today = new Date().toISOString().split('T')[0];
9
10  const result = await db.execute(
11    sql`SELECT h.id, h.name, h.description, h.color, h.frequency, h.target_count, h.reminder_time,
12              sc.current_streak, sc.longest_streak, sc.total_completions, sc.last_completed,
13              CASE WHEN c.habit_id IS NOT NULL THEN true ELSE false END AS completed_today
14        FROM habits h
15        LEFT JOIN streak_cache sc ON sc.habit_id = h.id
16        LEFT JOIN completions c ON c.habit_id = h.id AND c.completed_date = ${today}
17        WHERE h.user_id = ${req.user.id} AND h.is_archived = false
18        ORDER BY h.created_at ASC`
19  );
20
21  res.json(result.rows);
22});
23
24router.post('/api/habits/:id/complete', async (req, res) => {
25  if (!req.user) return res.status(401).json({ error: 'Auth required' });
26
27  const habitId = Number(req.params.id);
28  const dateStr = req.body.date || new Date().toISOString().split('T')[0];
29
30  // Verify ownership
31  const habit = await db.query.habits.findFirst({
32    where: and(eq(habits.id, habitId), eq(habits.userId, req.user.id))
33  });
34  if (!habit) return res.status(404).json({ error: 'Habit not found' });
35
36  const existing = await db.query.completions.findFirst({
37    where: and(eq(completions.habitId, habitId), eq(completions.completedDate, dateStr))
38  });
39
40  let completed;
41  if (existing) {
42    await db.delete(completions).where(eq(completions.id, existing.id));
43    completed = false;
44  } else {
45    await db.insert(completions).values({
46      habitId, completedDate: dateStr, notes: req.body.notes || null
47    });
48    completed = true;
49  }
50
51  // Recalculate streak and update cache
52  await db.execute(sql`SELECT calculate_streak(${habitId})`);
53
54  const [cache] = await db.select().from(streakCache).where(eq(streakCache.habitId, habitId));
55
56  res.json({ completed, currentStreak: cache?.currentStreak || 0, longestStreak: cache?.longestStreak || 0 });
57});

1// Prompt to type into Replit Agent:
2// Add this PostgreSQL function to the database migration.
3// Create or replace the calculate_streak function:
4//
5// CREATE OR REPLACE FUNCTION calculate_streak(p_habit_id INTEGER)
6// RETURNS VOID AS $$
7// DECLARE
8//   v_current_streak INTEGER := 0;
9//   v_longest_streak INTEGER := 0;
10//   v_temp_streak INTEGER := 0;
11//   v_last_date DATE := NULL;
12//   v_check_date DATE;
13//   v_total INTEGER;
14//   v_row RECORD;
15// BEGIN
16//   -- Count total completions
17//   SELECT COUNT(*) INTO v_total FROM completions WHERE habit_id = p_habit_id;
18//
19//   -- Calculate current streak (consecutive days ending today or yesterday)
20//   v_check_date := CURRENT_DATE;
21//   LOOP
22//     IF EXISTS (SELECT 1 FROM completions WHERE habit_id = p_habit_id AND completed_date = v_check_date) THEN
23//       v_current_streak := v_current_streak + 1;
24//       v_check_date := v_check_date - INTERVAL '1 day';
25//     ELSE
26//       EXIT;
27//     END IF;
28//   END LOOP;
29//
30//   -- If today not completed, check if streak continues from yesterday
31//   IF v_current_streak = 0 THEN
32//     v_check_date := CURRENT_DATE - INTERVAL '1 day';
33//     LOOP
34//       IF EXISTS (SELECT 1 FROM completions WHERE habit_id = p_habit_id AND completed_date = v_check_date) THEN
35//         v_current_streak := v_current_streak + 1;
36//         v_check_date := v_check_date - INTERVAL '1 day';
37//       ELSE
38//         EXIT;
39//       END IF;
40//     END LOOP;
41//   END IF;
42//
43//   -- Calculate longest streak from all completions
44//   v_temp_streak := 0;
45//   v_last_date := NULL;
46//   FOR v_row IN SELECT completed_date FROM completions WHERE habit_id = p_habit_id ORDER BY completed_date ASC
47//   LOOP
48//     IF v_last_date IS NULL OR v_row.completed_date = v_last_date + INTERVAL '1 day' THEN
49//       v_temp_streak := v_temp_streak + 1;
50//       v_longest_streak := GREATEST(v_longest_streak, v_temp_streak);
51//     ELSE
52//       v_temp_streak := 1;
53//     END IF;
54//     v_last_date := v_row.completed_date;
55//   END LOOP;
56//
57//   -- Upsert streak_cache
58//   INSERT INTO streak_cache (habit_id, current_streak, longest_streak, total_completions, last_completed, updated_at)
59//   VALUES (p_habit_id, v_current_streak, v_longest_streak, v_total,
60//           (SELECT MAX(completed_date) FROM completions WHERE habit_id = p_habit_id), NOW())
61//   ON CONFLICT (habit_id) DO UPDATE SET
62//     current_streak = EXCLUDED.current_streak,
63//     longest_streak = EXCLUDED.longest_streak,
64//     total_completions = EXCLUDED.total_completions,
65//     last_completed = EXCLUDED.last_completed,
66//     updated_at = NOW();
67// END;
68// $$ LANGUAGE plpgsql;
69//
70// Run this function in the database migration script.

1// Prompt to type into Replit Agent:
2// Add these routes to server/routes/habits.js:
3//
4// GET /api/habits/:id/history — heatmap data for last 90 days
5//   Generate a series of 90 dates (today going back) and LEFT JOIN with completions
6//   Return array of {date: 'YYYY-MM-DD', count: number} for all 90 days
7//   Use PostgreSQL: SELECT d.date, COALESCE(c.count, 0) AS count
8//     FROM generate_series(CURRENT_DATE - 89, CURRENT_DATE, '1 day') AS d(date)
9//     LEFT JOIN completions c ON c.completed_date = d.date AND c.habit_id = :id
10//     ORDER BY d.date ASC
11//
12// GET /api/habits/:id/stats — detailed stats
13//   Return: current_streak, longest_streak, total_completions, last_completed,
14//   completion_rate_30d (completions in last 30 days / 30 * 100),
15//   completion_rate_90d (same for 90 days)
16//
17// GET /api/dashboard — summary for homepage
18//   Return: total_habits (non-archived), completed_today (habits with completion today),
19//   longest_active_streak (max current_streak across all user's habits)
20//
21// React frontend components:
22// 1. HabitCard: shows habit name, colored circle complete button (filled when done today),
23//    streak fire icon with number, target_count progress (e.g. '1/1 today')
24//    Animate the complete button: scale-up + color fill on click
25// 2. HeatmapGrid: 90 squares (10 rows x 9 cols or 13 weeks x 7 days)
26//    Color intensity based on count: 0=gray, 1=light, 2+=dark (using habit.color)
27//    Tooltip on hover showing date and count
28// 3. Dashboard: greeting, summary stats cards, list of all HabitCards

1// Prompt to type into Replit Agent:
2// Finalize the app for deployment:
3// 1. Add SESSION_SECRET to Replit Secrets (lock icon in sidebar)
4//    Value: any 32-character random string
5// 2. Ensure server/index.js binds to 0.0.0.0:
6//    app.listen(process.env.PORT || 3000, '0.0.0.0', ...)
7// 3. Add a PostgreSQL retry wrapper to server/db.js:
8//    const pool = new Pool({ connectionString: process.env.DATABASE_URL,
9//      connectionTimeoutMillis: 5000, idleTimeoutMillis: 30000 })
10// 4. Add a POST /api/habits route for creating habits:
11//    Body: {name, description, color, frequency, targetCount, reminderTime}
12//    INSERT into habits with user_id = req.user.id
13//    Also create an empty streak_cache row: INSERT INTO streak_cache (habit_id) VALUES (:id) ON CONFLICT DO NOTHING
14// 5. Add PATCH /api/habits/:id/archive for soft-archiving a habit
15// 6. Deploy → Autoscale (button in top-right Deploy menu)