How I Built a 93-Day Streak Engine in Postgres That Scales to 28K Users

Streaks are the backbone of habit-forming products. Duolingo has them. Strava has them. GitHub has them. And if you're building anything where users show up daily — a fitness tracker, a journal, a learning platform — you'll eventually need a streak system.

I learned this firsthand building Wishyze, an AI-powered daily ritual platform. Our longest active streak is 93 days and counting. Across 28,547 users, we track millions of daily completions — and the streak calculation has to be fast, accurate, and bulletproof.

Here's exactly how we built it, including the schema, the queries, the edge cases, and the mistakes I'd avoid if I were starting over.

The problem, stated simply

A streak is "how many consecutive days the user has completed their ritual." Sounds trivial. It's not.

You need to answer questions like:

• What's the current streak length?
• Did they break their streak yesterday?
• What's their longest-ever streak?
• How do we handle time zones when a user in Tokyo and a user in New York both hit "complete" at different UTC times?
• How do we make this fast when a user has 500+ days of history?

Schema: the foundation

We use Supabase (Postgres) with two core tables:

-- Users table (simplified)
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  timezone TEXT DEFAULT 'UTC',
  longest_streak INT DEFAULT 0,
  current_streak INT DEFAULT 0,
  last_ritual_date DATE
);

-- Ritual completions
CREATE TABLE ritual_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) NOT NULL,
  completed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  ritual_type TEXT NOT NULL, -- 'affirmation', 'visualization', etc.
  -- Computed column for index-friendly date lookups
  completed_date DATE GENERATED ALWAYS AS (completed_at::DATE) STORED
);

-- The index that does the heavy lifting
CREATE INDEX idx_ritual_logs_user_date 
  ON ritual_logs(user_id, completed_date DESC);

A few decisions worth explaining:

completed_date as a generated column — We store the full TIMESTAMPTZ for audit trails but compute completed_date at write time. This lets us run index scans on (user_id, completed_date) without function calls in the WHERE clause.

DESC on the index — Streak queries almost always look at the most recent dates. A descending index puts the newest rows at the front of the B-tree, reducing page reads.

Denormalized streak counters on users — We cache current_streak and longest_streak on the user row. This is a deliberate trade-off: we compute the canonical streak from ritual_logs and update the cache in a transaction, but 99% of reads (dashboard, leaderboard, profile pages) hit the cached value. A single write-time recompute saves thousands of read-time CTEs.

The streak query

Here's the actual query we use to compute a user's current streak:

WITH daily_completions AS (
  SELECT DISTINCT completed_date
  FROM ritual_logs
  WHERE user_id = $1
    AND completed_date >= CURRENT_DATE - INTERVAL '120 days'
  ORDER BY completed_date DESC
),
streak_groups AS (
  SELECT 
    completed_date,
    completed_date - (ROW_NUMBER() OVER (ORDER BY completed_date DESC))::INT AS grp
  FROM daily_completions
),
current_streak AS (
  SELECT COUNT(*) AS streak_length
  FROM streak_groups
  WHERE grp = (
    SELECT grp 
    FROM streak_groups 
    ORDER BY completed_date DESC 
    LIMIT 1
  )
)
SELECT 
  streak_length,
  CASE 
    WHEN (SELECT MAX(completed_date) FROM daily_completions) < CURRENT_DATE - 1 
    THEN TRUE 
    ELSE FALSE 
  END AS streak_broken
FROM current_streak;

Let's unpack what's happening here.

Step 1: daily_completions — deduplicate and scope

We SELECT DISTINCT completed_date because a user might complete multiple rituals in one day (affirmation + visualization + action). We only care about days, not individual completions.

The 120 days window is a practical cap — nobody's current streak exceeds that, and it keeps the working set small.

Step 2: streak_groups — the islands-and-gaps trick

This is the clever part. We use ROW_NUMBER() to number each consecutive day, then subtract that row number from the date. Consecutive dates get the same group identifier:

completed_date	row_number	date - row_number (grp)
2026-07-05	1	2026-07-04
2026-07-04	2	2026-07-02
2026-07-03	3	2026-06-30
2026-07-01	4	2026-06-27
2026-06-30	5	2026-06-25

The gap on July 2nd creates a new group. The most recent group (grp = 2026-07-04) has 3 rows → a 3-day streak.

This is the classic "gaps and islands" SQL pattern, and it's far more efficient than recursive CTEs for this use case.

Step 3: Check if the streak is still alive

If the most recent completion isn't yesterday or today, the streak is broken. We return a boolean so the frontend can show encouraging messaging ("Don't lose your streak!") before midnight.

Time zones: the silent streak-killer

This is where most streak implementations fall apart. Here's the scenario:

1. User in Los Angeles completes their ritual at 11 PM local time (7 AM UTC next day)
2. Your server stores completed_at as 2026-07-06 07:00:00 UTC
3. Your streak query uses CURRENT_DATE (server time, UTC)
4. The completion appears to be on July 6th, but the user completed it on July 5th local
5. If they miss July 6th local, the streak looks unbroken when it's actually broken

The fix:

-- Instead of CURRENT_DATE, use the user's timezone
completed_date DATE GENERATED ALWAYS AS (
  (completed_at AT TIME ZONE timezone)::DATE
) STORED

Wait — we can't actually reference the users.timezone column inside a generated column on ritual_logs. So we handle this in application code instead, and our ritual_logs table stores completed_date in the user's local date:

// When inserting a ritual completion
const userTz = user.timezone || 'UTC';
const localDate = new Date().toLocaleDateString('en-CA', { 
  timeZone: userTz 
}); // 'en-CA' gives YYYY-MM-DD format

await supabase.from('ritual_logs').insert({
  user_id: userId,
  completed_at: new Date().toISOString(),
  completed_date: localDate, // explicitly set, not generated
  ritual_type: ritualType,
});

By computing the local date in the application layer and storing it explicitly, our streak query always operates on the user's actual calendar days.

Performance at scale

With 28K users and growing, the ritual_logs table has millions of rows. Here's what keeps things fast:

The partial current-streak query never scans more than 120 rows per user. The index on (user_id, completed_date DESC) combined with LIMIT-style windowing makes this an index-only scan in most cases.

Longest-streak is computed on write, not on read. When a user completes a ritual, we run the streak calculation once and update users.longest_streak in the same transaction:

const { data: streak } = await supabase.rpc('compute_current_streak', {
  p_user_id: userId,
});

await supabase.from('users').update({
  current_streak: streak.streak_length,
  longest_streak: greatest(streak.streak_length, user.longest_streak),
  last_ritual_date: today,
}).eq('id', userId);

The leaderboard query becomes a trivial index scan on users.longest_streak.

The Phase Model: streaks in context

One thing that surprised me: streak data alone isn't that useful without behavioral context. We layer our Phase Model on top of streaks to give users a richer picture:

Phase	Duration	What happens
Spark	Days 1–7	The honeymoon. Streak is exciting, completion feels novel.
Void	Weeks 2–6	73% of users quit here. The novelty wears off; the streak becomes a chore.
Alignment	Weeks 6–12	The habit crystallizes. Missing a day feels wrong.
Manifestation	Week 12+	The ritual is part of identity. Users report measurable life changes.

We compute the phase server-side:

function getPhase(streakLength: number, totalCompletions: number): Phase {
  if (streakLength <= 7) return 'spark';
  if (totalCompletions < 42) return 'void';      // 6 weeks of daily completions
  if (totalCompletions < 84) return 'alignment';  // 12 weeks
  return 'manifestation';
}

This gives us something more useful than a number: we can show different UI, different encouragement copy, and different ritual suggestions based on where the user is in their journey.

What I'd do differently

1. Start with a materialized approach sooner. I spent too long trying to make the pure-SQL streak query work for the leaderboard page. Caching the result on the user row eliminated 90% of the complexity.

2. Store the timezone at signup. We added it later and had to backfill. Ask for it during onboarding (or infer from the browser with Intl.DateTimeFormat().resolvedOptions().timeZone).

3. Track "almost streaks." Users who complete 6 out of 7 days aren't on a streak, but they're building a habit. We missed early signals of engagement by focusing only on perfect streaks.

4. Don't punish missed days. We used to show a broken-streak animation that felt more like a penalty than encouragement. Now we show "Start a new streak today!" — framing matters enormously for retention.

Try it yourself

If you're building streak mechanics into your own product, start with the gaps-and-islands query above and a simple (user_id, local_date) index. You can optimize from there.

And if you're curious to see how it all comes together in a real product, you can try the ritual engine at wishyze.com — it's free for up to 3 daily rituals, and I'd love to hear what you think.

Building something with streaks, Postgres, or behavior-change mechanics? Drop a comment — I'm genuinely curious how others are solving these problems.