yoten.app
Yōten: A social tracker for your language learning journey built on the atproto.
brookjeynes.dev
1-- This script backfills XP for all existing study sessions and updates user
2-- profiles. It's designed to be run once.
3
4-- =============================================================================
5-- Part 1: Create missing xp_events for all completed study sessions. This is
6-- safe to run multiple times, as it will only insert records that don't exist.
7-- =============================================================================
8INSERT INTO xp_events (did, session_rkey, xp_gained)
9SELECT
10 ss.did,
11 ss.rkey,
12 (ss.duration / 60) * 10 -- xp_gained (10 XP per minute)
13FROM
14 study_sessions ss
15LEFT JOIN
16 xp_events x ON ss.did = x.did AND ss.rkey = x.session_rkey
17WHERE
18 x.id IS NULL AND ss.duration >= 60;
19
20
21-- =============================================================================
22-- Part 2: Reset and Recalculate all user XP and Levels. WARNING: This will
23-- reset all users to Level 1 and then recalculate their total XP from scratch
24-- based on ALL their xp_events.
25-- =============================================================================
26
27-- Step 2.1: Reset all profiles to a clean slate.
28UPDATE profiles
29SET
30 xp = 0,
31 level = 0;
32
33-- Step 2.2: Create a temporary table to hold the total XP for each user.
34-- This makes the final update statement cleaner and more compatible across
35-- SQLite versions.
36CREATE TEMP TABLE user_total_xp AS
37SELECT
38 did,
39 SUM(xp_gained) as total_xp
40FROM
41 xp_events
42GROUP BY
43 did;
44
45-- Step 2.3: Update the profiles table with the new total XP from the temp
46-- table.
47UPDATE profiles
48SET
49 xp = (
50 SELECT total_xp
51 FROM user_total_xp
52 WHERE user_total_xp.did = profiles.did
53 )
54WHERE
55 EXISTS (
56 SELECT 1
57 FROM user_total_xp
58 WHERE user_total_xp.did = profiles.did
59 );
60
61-- Clean up the temporary table.
62DROP TABLE user_total_xp;