"""Reproducible, benchmark-informed synthetic histories, never real player data. See SAMPLE_DATA.md for public references, assumptions, and validation commands. All writes belong to the caller's transaction. Existing game IDs are preserved. """ from collections import Counter, defaultdict from datetime import datetime, timedelta, timezone import math from pathlib import Path import random from psycopg import sql from psycopg.types.json import Jsonb SAMPLE_KEYS = ('willow-v1', 'orbit-v1', 'dungeon-v1') DAY = timedelta(days=1) def copy_rows(conn, table, columns, records): if not records: return query = sql.SQL('COPY {} ({}) FROM STDIN').format( sql.Identifier(table), sql.SQL(',').join(map(sql.Identifier, columns))) with conn.cursor().copy(query) as stream: for row in records: stream.write_row(tuple(Jsonb(v) if isinstance(v, (dict, list)) else v for v in row)) def reset_sample(conn): """Remove only the bundled samples; keep games and unrelated projects intact.""" gids = [r['id'] for r in conn.execute('SELECT id FROM games WHERE sample_key=ANY(%s)', (list(SAMPLE_KEYS),))] for table in ('daily_health_reports', 'interaction_events', 'gamepass_ownership', 'product_sales', 'activity_runs', 'events', 'player_snapshots', 'sessions', 'game_metrics', 'players', 'gamepasses', 'products', 'activity_definitions', 'column_templates', 'player_fields'): conn.execute(sql.SQL('DELETE FROM {} WHERE game_id=ANY(%s)').format(sql.Identifier(table)), (gids,)) def populate(conn, players_per_game=6000): rng = random.Random(20261103) now = datetime.now(timezone.utc) midnight = now.replace(hour=0, minute=0, second=0, microsecond=0) first_day = midnight - 89 * DAY conn.execute((Path(__file__).parent / 'db' / 'seed.sql').read_text(encoding='utf-8')) games = conn.execute('SELECT * FROM games WHERE sample_key=ANY(%s) ORDER BY id', (list(SAMPLE_KEYS),)).fetchall() for game in games: gid = game['id'] # Different genres, not three copies of the same graph. scale, session_scale = {'willow-v1': (1., 1.), 'orbit-v1': (.78, .78), 'dungeon-v1': (1.35, 1.35)}[game['sample_key']] defs = {r['key']: r for r in conn.execute('SELECT * FROM activity_definitions WHERE game_id=%s', (gid,))} products = conn.execute('SELECT * FROM products WHERE game_id=%s ORDER BY id', (gid,)).fetchall() weights = [] for day in range(90): date = first_day + day * DAY # Gradual growth, weekend lift, acquisition campaign, and update bump. weight = (.7 + day / 140) * (1.28 if date.weekday() >= 5 else 1) weight *= 1 + .75 * math.exp(-((day-61)/3)**2) + .4 * math.exp(-((day-79)/2)**2) if day == 89: weight *= max(.02, (now-midnight).total_seconds()/86400) weights.append(weight) players = [] for index in range(players_per_game): cohort = rng.choices(range(90), weights=weights)[0] date = first_day + cohort * DAY # Global audience with an evening peak, not a uniform hourly trace. hour = rng.gauss(18, 4) % 24 if rng.random() < .7 else rng.uniform(0, 24) if cohort == 89: hour = min(hour, max(0, (now-midnight).total_seconds()/3600-.02)) * rng.uniform(.1, 1) start = date + timedelta(hours=hour) kind = rng.choices(['bounce', 'casual', 'regular', 'whale'], [.55, .32, .127, .003])[0] name = rng.choice(['Pixel','Cloud','Nova','Maple','Moss','Luna','Echo','Aero','Fern','Snow']) name += rng.choice(['Fox','Builder','Runner','Cat','Plays','_']) + str(rng.randrange(10, 9999)) players.append((gid, 1000000000+gid*100000+index, name if rng.random()>.008 else None, start, start, {'seedVersion': 2, 'seedIndex': index, 'simulationGroup': kind})) copy_rows(conn, 'players', ['game_id','roblox_user_id','display_name','first_seen','last_seen','metadata'], players) stored = conn.execute('SELECT * FROM players WHERE game_id=%s ORDER BY id', (gid,)).fetchall() session_rows, traits = [], {} for player in stored: pid, start = player['id'], player['first_seen'] kind = player['metadata']['simulationGroup'] affinity = rng.uniform(.7, 1.3) payer = rng.random() < {'bounce': .0015, 'casual': .035, 'regular': .18, 'whale': 1}[kind] traits[pid] = {'kind':kind, 'payer':payer, 'device':rng.choices(['phone','desktop','tablet'],[.65,.28,.07])[0], 'spendIntensity':min(12, rng.paretovariate(1.7)) if kind=='whale' else 1} dates = [start] age = (midnight - start.replace(hour=0, minute=0, second=0, microsecond=0)).days for offset in range(1, age+1): if kind == 'bounce': break if kind == 'casual': probability = .20 * math.exp(-(offset-1)/1.8) elif kind == 'regular': probability = .40*math.exp(-(offset-1)/2.7) + .10*math.exp(-(offset-1)/15) else: probability = .85*math.exp(-(offset-1)/70) date = start.replace(hour=0, minute=0, second=0, microsecond=0) + offset * DAY weekend = 1.12 if date.weekday() >= 5 else .96 if rng.random() < min(.96, probability * affinity * scale * weekend): dates.append(date + timedelta(hours=rng.gauss(18,4)%24)) last_end = None for day_index, date in enumerate(dates): count = 1 if kind != 'bounce' and rng.random() < (.65 if kind=='whale' else .18): count += rng.choices([1,2], [.85,.15])[0] previous_end = None for number in range(count): current = date if previous_end is None else previous_end+timedelta(minutes=rng.uniform(25,180)) if last_end is not None and current < last_end: current = last_end + timedelta(minutes=20) if current > now or current.date() != date.date(): continue median = {'bounce':2.5,'casual':6,'regular':11,'whale':28}[kind] if day_index: median *= 1.2 duration = max(15, min(3*3600, rng.lognormvariate(math.log(median*60*session_scale), .85))) end = current+timedelta(seconds=duration) # Preserve genuinely open sessions, plus rare lost end reports. recorded_end = None if end>now or rng.random()<.003 else end session_rows.append([gid,pid,current,recorded_end, rng.choice(['Main Island','Forest Arena','Trading Hub']), {'device':traits[pid]['device'], 'source':'synthetic-v2'}]) previous_end = end last_end = end session_rows.sort(key=lambda row: row[2]) ids = [r['id'] for r in conn.execute("SELECT nextval(pg_get_serial_sequence('sessions','id')) id FROM generate_series(1,%s)", (len(session_rows),))] copy_rows(conn, 'sessions', ['id','game_id','player_id','started_at','ended_at','place_name','stats'], [[sid]+row for sid,row in zip(ids,session_rows)]) events, sales, snapshots, runs = [], [], [], [] balances, spent, units, playtime, levels, visits = defaultdict(int), defaultdict(int), defaultdict(int), defaultdict(float), defaultdict(lambda:1), Counter() cash_earned, cash_spent = defaultdict(int), defaultdict(int) intervals = [] def event(name, pid, sid, at, props=None, category='events'): events.append((gid,category,name,pid,sid,at,props or {})) def activity(key, pid, sid, start, end, outcome, progress, captured, prompts=0): definition=defs[key] runs.append((gid,definition['id'],pid,sid,start,end,outcome, sum(e in definition['configuration']['steps'] for e in progress),prompts,captured,progress)) # The ordered observations also appear as individually inspectable events. for i, name in enumerate(progress): at=start+(end-start)*(i/max(1,len(progress)-1)) event(name,pid,sid,at,captured,'activities') for sid, row in zip(ids, session_rows): _,pid,start,end,place,stats=row trait=traits[pid] visits[start.replace(hour=0,minute=0,second=0,microsecond=0)] += 1 intervals.append((start,end or min(now,start+timedelta(minutes=10)))) event('session_start',pid,sid,start,{'place':place,'device':trait['device']}) if end is None: continue seconds=(end-start).total_seconds() playtime[pid] += seconds earned=round(seconds*rng.uniform(1.2,3.5)) cash_earned[pid] += earned balances[pid] += earned expense=round(balances[pid]*rng.uniform(0,.25)) cash_spent[pid] += expense balances[pid] -= expense before=levels[pid] levels[pid]=1+int((playtime[pid]/240)**.72) if levels[pid]>before: event('level_up',pid,sid,start+(end-start)*.6,{'level':levels[pid],'levelsGained':levels[pid]-before}) if rng.random()<.18 and seconds>300: event('daily_reward_claimed',pid,sid,start+timedelta(seconds=50),{'reward':100}) buys=[] if trait['payer'] and seconds>90 and rng.random()<(.82 if trait['kind']=='whale' else .35): n=rng.randint(1,max(2,round(2*trait['spendIntensity']))) if trait['kind']=='whale' else 1 for j in range(n): product=rng.choices(products,weights=[.03,.12,.3,.55] if trait['kind']=='whale' else [.52,.27,.16,.05])[0] quantity=rng.randint(1,4) if trait['kind']=='whale' else 1 # A temporary promotion changes historical sale prices. price=round(product['current_price_robux']*(.8 if (start-first_day).days in range(60,64) else 1)) at=start+(end-start)*(.2+.5*(j+1)/(n+1)) sales.append((gid,product['id'],pid,sid,f'synthetic-v2-{sid}-{j}',at,price,quantity,{'source':'synthetic_receipt'})) spent[pid] += price*quantity units[pid] += quantity buys.append((product,quantity,price,at)) if seconds>60 and (buys or rng.random()<.23): definition=defs['shop'] progress=[definition['start_event']] if buys or rng.random()<.85: progress.append('product_viewed') activity('shop',pid,sid,start+(end-start)*.15,start+(end-start)*.3,'closed', progress+[definition['end_event']],{'currency_balance':balances[pid]}) for key, chance in [('minigame',.6),('quest',.35)]: if seconds<180 or rng.random()>chance: continue definition=defs[key] maximum=len(definition['configuration']['steps']) count=maximum if rng.random()<(.7 if seconds>600 else .4) else rng.randrange(maximum) progress=[definition['start_event']]+definition['configuration']['steps'][:count]+[definition['end_event']] outcome=('won' if rng.random()<.4 else 'lost') if key=='minigame' and count==maximum else 'completed' if count==maximum else 'abandoned' captured={'score':round(seconds*rng.uniform(.3,2)),'rounds':1} if key=='minigame' else {'quest_id':f'quest_{rng.randrange(12)}','reward':100 if count==maximum else 0} activity(key,pid,sid,start+(end-start)*.35,start+(end-start)*.9,outcome,progress,captured) event('session_end',pid,sid,end,{'playtime':round(seconds),'level':levels[pid]}) data={'level':levels[pid],'cash':balances[pid], 'cashEarned':cash_earned[pid], 'cashSpent':cash_spent[pid], 'robux':spent[pid],'purchases':units[pid], 'levelsGained':levels[pid]-1,'quests':int(playtime[pid]/1800),'achievements':int(math.sqrt(playtime[pid]/600))} if game['sample_key']=='orbit-v1': data['roundWins']=int(playtime[pid]/1200) # Realistic partial payloads: a small stable set lacks a field or all snapshots. if pid%47==0: data.pop('cash') if pid%61: snapshots.append((gid,pid,end,data)) copy_rows(conn,'events',['game_id','category','name','player_id','session_id','occurred_at','properties'],events) copy_rows(conn,'product_sales',['game_id','product_id','player_id','session_id','receipt_id','purchased_at','unit_price_robux','quantity','properties'],sales) copy_rows(conn,'player_snapshots',['game_id','player_id','captured_at','data'],snapshots) copy_rows(conn,'activity_runs',['game_id','definition_id','player_id','session_id','started_at','ended_at','outcome','step_count','prompt_count','captured_values','stage_progress'],runs) conn.execute('''UPDATE players p SET first_seen=s.first_seen,last_seen=s.last_seen FROM (SELECT player_id,min(started_at) first_seen,max(coalesce(ended_at,started_at)) last_seen FROM sessions WHERE game_id=%s GROUP BY player_id) s WHERE p.game_id=%s AND p.id=s.player_id''',(gid,gid)) # CCU and visits come from these same sessions, instead of unrelated sine-wave values. starts=sorted(s for s,e in intervals) ends=sorted(e for s,e in intervals) metrics=[] from bisect import bisect_right for hour in range(90*24): at=first_day+timedelta(hours=hour) if at>now: break ccu=bisect_right(starts,at)-bisect_right(ends,at) metrics.append((gid,'ccu','concurrent_players',at,None if rng.random()<.004 else ccu,{'source':'synthetic_session_intervals'})) if hour%3==0: metrics.append((gid,'performance','server_fps',at,round(max(35,min(60,59-ccu*.12+rng.gauss(0,1.1))),1),{})) metrics.extend((gid,'visits','daily_visits',day,count,{'source':'session_starts'}) for day,count in visits.items()) copy_rows(conn,'game_metrics',['game_id','category','name','recorded_at','value','properties'],metrics) root_events=[] for day in range(90): at=first_day+day*DAY+timedelta(hours=17) if at<=now: root_events.append((gid,'server','weather_changed',at,{'weather':rng.choice(['clear','rain','fog'])})) if day%7==4: root_events.append((gid,'events','global_announcement',at,{'message':'Weekend event'})) copy_rows(conn,'events',['game_id','category','name','occurred_at','properties'],root_events) conn.execute('''INSERT INTO gamepasses(game_id,roblox_gamepass_id,name) SELECT %s,9000000+%s*100+n,(ARRAY['VIP','Double cash','Extra inventory','Early access'])[n] FROM generate_series(1,4) n''',(gid,gid)) passes=conn.execute('SELECT * FROM gamepasses WHERE game_id=%s ORDER BY id',(gid,)).fetchall() ownership=[] for player in stored: pid=player['id'] for index,gp in enumerate(passes): # Previously owned / gifted passes do not require a receipt in this 90-day window. probability=([.82,.92,.78,.6] if traits[pid]['kind']=='whale' else [.25,.4,.2,.08] if spent[pid] else [.012,.018,.009,.003])[index] if rng.random() < (.04 if index==3 else 0): continue ownership.append((gid,gp['id'],pid,rng.random()