Daily Streak State Machine

ELI5

Each user has one row in streaks with current_streak, longest_streak, and last_activity_date. A daily cron at 00:01 UTC walks every row: if the user was active yesterday, increment; if they skipped a day, either consume a freeze (JET/ORBIT only) or reset to zero. Multiple activities on the same day are no-ops.

Technical Deep Dive

Schema

CREATE TABLE streaks (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  current_streak INT NOT NULL DEFAULT 0,
  longest_streak INT NOT NULL DEFAULT 0,
  last_activity_date DATE,
  streak_start_date DATE,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

PK is user_id (not surrogate), so there is exactly one row per user.

update_user_streak(p_user_id)

Stored function in 002_gamification_schema.sql:

1. SELECT current state.
2. If no row → INSERT (1, 1, today, today).
3. If last_activity_date = today → no-op.
4. If last_activity_date = today - 1 → current_streak++, longest_streak = GREATEST(...).
5. If last_activity_date < today - 1 → current_streak = 1.
6. UPDATE.

This function is the application-side “any activity” entry point. The daily cron in the Gamification service spec (§ 8.2) layers freeze logic on top.

Daily Cron Logic

stateDiagram-v2
    [*] --> Eval: cron 00:01 UTC
    Eval --> Active: was active today
    Eval --> MissedYesterday: last_activity = today-1\n(no activity today yet)
    Eval --> Reset: last_activity < today-1
    Eval --> NoOp: last_activity = today (already counted)
    Active --> Increment: current_streak += 1
    Increment --> AwardBonus: +5 × current_streak XP
    AwardBonus --> [*]
    MissedYesterday --> CheckTier
    CheckTier --> ConsumeFreeze: tier ≥ JET\n& freeze_count > 0
    CheckTier --> Break: otherwise
    ConsumeFreeze --> [*]: freeze_count--
    Break --> [*]: current_streak = 0\nemit events.streak.broken
    Reset --> [*]: current_streak = 0
    NoOp --> [*]

Freeze Grants

flowchart LR
    F["1st of month cron"] -->|"tier IN (JET, ORBIT)"| G["freeze_count = 3"]

Note: the streaks table in 002_gamification_schema.sql does not declare a freeze_count column; the Gamification service spec adds one in its CREATE TABLE example. Treat freeze-count storage as service-owned and confirm against the live migration before relying on it.

XP Coupling

Continued streaks award 5 × current_streak XP under event_type='daily_streak' and trigger the streak_starter (7d), streak_warrior (30d) and streak_legend (100d) achievements via skyflow-009.

Key Terms

• last_activity_date → DATE (no time), so “activity” means “any XP-earning event today” regardless of hour
• Streak freeze → JET/ORBIT perk granting up to 3 missed-day passes per calendar month
• events.streak.broken → emitted only on actual break, not on freeze consumption
• update_user_streak → idempotent within a UTC day

Q&A

Q: What if a user is active twice on the same UTC day? A: The function early-returns when last_activity_date = today. No bonus, no double-increment.

Q: When does the streak roll over for a user in UTC-12? A: All comparisons use server-side CURRENT_DATE (UTC). Users in extreme timezones may perceive the cutoff at unexpected local times — this is a documented design choice.

Q: What stops a user from manipulating freeze_count? A: It is mutated only by Gamification (cron) and the daily streak handler — there is no public API to set it. The 1st-of-month grant is idempotent (UPDATE … SET freeze_count = 3).

Q: Are streak achievements awarded retroactively? A: They are evaluated by the standard 5-minute achievement sweep against streaks.current_streak, so a streak that crosses a threshold will unlock the achievement on the next sweep, not at the cron tick itself.

Examples

Like a habit tracker app: one box per day. Tick today’s box → streak +1. Skip a day → streak resets to zero. The premium plan gives you three “vacation tickets” per month that paste a sticker over a missed box without breaking the chain.