DAU Went Up but Retention Didn't — Rebuilding Gamification That Actually Sticks in Rork Apps

-- Streak update function called on login (with grace days)
create table public.user_streaks (
  user_id uuid primary key references auth.users(id) on delete cascade,
  current_streak integer not null default 0,
  best_streak integer not null default 0,
  freezes_available integer not null default 2, -- topped up at the start of each month
  last_active_date date
);
 
create or replace function public.touch_streak()
returns integer language plpgsql security definer as $$
declare
  v_user uuid := auth.uid();
  v_today date := (now() at time zone 'Asia/Tokyo')::date;
  r public.user_streaks;
  v_gap integer;
begin
  select * into r from public.user_streaks where user_id = v_user;
  if not found then
    insert into public.user_streaks(user_id, current_streak, best_streak, last_active_date)
    values (v_user, 1, 1, v_today);
    return 1;
  end if;
 
  if r.last_active_date = v_today then
    return r.current_streak; -- another login the same day does nothing
  end if;
 
  v_gap := v_today - r.last_active_date;
 
  if v_gap = 1 then
    -- consecutive: extend
    update public.user_streaks
      set current_streak = r.current_streak + 1,
          best_streak = greatest(r.best_streak, r.current_streak + 1),
          last_active_date = v_today
      where user_id = v_user
      returning current_streak into v_gap;
    return v_gap;
  elsif v_gap = 2 and r.freezes_available > 0 then
    -- exactly one day missed: spend a grace day to protect the record
    update public.user_streaks
      set current_streak = r.current_streak + 1,
          best_streak = greatest(r.best_streak, r.current_streak + 1),
          freezes_available = r.freezes_available - 1,
          last_active_date = v_today
      where user_id = v_user
      returning current_streak into v_gap;
    return v_gap;
  else
    -- two or more days missed, or out of insurance: reset (but no penalty)
    update public.user_streaks
      set current_streak = 1, last_active_date = v_today
      where user_id = v_user;
    return 1;
  end if;
end;
$$;

-- Return only the five people above and below your own rank (around-me window)
create or replace function public.leaderboard_around_me()
returns table(rnk bigint, display_name text, total_points integer, is_me boolean)
language sql security definer as $$
  with ranked as (
    select user_id, display_name, total_points,
           rank() over (order by total_points desc) as rnk
    from public.user_scores
  ),
  me as (select rnk from ranked where user_id = auth.uid())
  select r.rnk, r.display_name, r.total_points, (r.user_id = auth.uid()) as is_me
  from ranked r, me
  where r.rnk between me.rnk - 5 and me.rnk + 5
  order by r.rnk;
$$;

// components/AroundMeBoard.tsx — show only the ranks around you
import { useEffect, useState } from 'react';
import { View, Text, FlatList, StyleSheet } from 'react-native';
import { supabase } from '../lib/supabase';
 
type Row = { rnk: number; display_name: string; total_points: number; is_me: boolean };
 
export default function AroundMeBoard() {
  const [rows, setRows] = useState<Row[]>([]);
 
  useEffect(() => {
    supabase.rpc('leaderboard_around_me').then(({ data }) => {
      if (data) setRows(data as Row[]);
    });
  }, []);
 
  return (
    <FlatList
      data={rows}
      keyExtractor={(r) => String(r.rnk)}
      renderItem={({ item }) => (
        <View style={[styles.row, item.is_me && styles.me]}>
          <Text style={styles.rank}>{item.rnk}</Text>
          <Text style={styles.name}>{item.display_name}</Text>
          <Text style={styles.pt}>{item.total_points} pt</Text>
        </View>
      )}
    />
  );
}
 
const styles = StyleSheet.create({
  row: { flexDirection: 'row', alignItems: 'center', paddingVertical: 12, paddingHorizontal: 8 },
  me: { backgroundColor: '#eef2ff', borderRadius: 12 },
  rank: { width: 44, fontWeight: '700', color: '#6366f1' },
  name: { flex: 1, fontSize: 15 },
  pt: { fontWeight: '700' },
});