Flashback Challenges Debugging

Use Flashback MCP for live checks when available. Prefer read-only Supabase queries on production.

Fast triage questions

1. Is the user connected to FACEIT in Clerk metadata?
2. Is the user registered in user_challenges for the challenge?
3. Is the challenge currently active according to start_date/end_date?
4. Did faceit-match-sync discover rows in challenge_faceit_matches?
5. Are demo_id and pipeline_id linked on those tracking rows?
6. Is the pipeline stuck/failed in demo_pipeline?
7. Does analyzed_demos exist for the demo_id?
8. Does demo_player_match_stats contain the player, matched by SteamID or normalized FACEIT name?
9. Are analyzed demo stats present and matched to the player? Scoreboard no longer falls back to user_games.
10. Are placement matches intentionally excluded/altering Elo baseline?

Useful live SQL

Replace placeholders before running.

Challenge overview

SELECT challenge_id, name, start_date, end_date, registration_start_date, registration_end_date,
       visibility, allowed_clerk_org_id, scoring_config
FROM challenges
WHERE challenge_id = <challenge_id>;

Participants

SELECT clerk_user_id, challenge_id, enrollment_date, faceit,
       elo_at_start, elo_at_end, elo_after_placements,
       placement_matches_played, placement_matches_required
FROM user_challenges
WHERE challenge_id = <challenge_id>
ORDER BY enrollment_date DESC;

One user’s challenge tracking rows

SELECT id, challenge_id, clerk_user_id, faceit_player_id, faceit_nickname,
       faceit_match_id, match_started_at, demo_id, pipeline_id,
       faceit_season_id, is_placement_match, placement_index,
       player_won, player_round_wins, player_round_losses, player_outcome_source,
       source, discovered_at, updated_at
FROM challenge_faceit_matches
WHERE challenge_id = <challenge_id>
  AND clerk_user_id = '<clerk_user_id>'
ORDER BY match_started_at DESC NULLS LAST;

Pipeline state for a challenge

SELECT cfm.faceit_match_id, cfm.faceit_nickname, cfm.match_started_at,
       cfm.demo_id, cfm.pipeline_id,
       dp.status, dp.current_stage, dp.retry_count, dp.error_code, dp.error_message,
       dp.started_at, dp.completed_at, dp.updated_at
FROM challenge_faceit_matches cfm
LEFT JOIN demo_pipeline dp ON dp.id = cfm.pipeline_id OR dp.demo_id = cfm.demo_id
WHERE cfm.challenge_id = <challenge_id>
ORDER BY cfm.match_started_at DESC NULLS LAST;

Analyzed stats linkage

SELECT cfm.faceit_match_id, cfm.faceit_nickname, cfm.demo_id,
       ad.id AS analyzed_demo_id, ad.map_name, ad.total_rounds, ad.analyzed_at,
       pms.steam_id, pms.player_name, pms.kills, pms.deaths, pms.adr, pms.headshot_percentage
FROM challenge_faceit_matches cfm
LEFT JOIN analyzed_demos ad ON ad.demo_id = cfm.demo_id
LEFT JOIN demo_player_match_stats pms ON pms.analyzed_demo_id = ad.id
WHERE cfm.challenge_id = <challenge_id>
  AND cfm.clerk_user_id = '<clerk_user_id>'
ORDER BY cfm.match_started_at DESC NULLS LAST;

Queue state

SELECT * FROM pgmq.q_demo_download ORDER BY enqueued_at LIMIT 50;
SELECT * FROM pgmq.q_demo_analyze ORDER BY enqueued_at LIMIT 50;
SELECT * FROM pgmq.a_demo_analyze ORDER BY archived_at DESC LIMIT 20;

For q_demo_analyze, check vt before assuming the queue is empty. Messages can be present but invisible until the visibility timeout expires.

Common failures

User cannot register

Likely causes:

• Not signed in.
• No publicMetadata.faceit.guid on Clerk user.
• Challenge registration window not open.
• Challenge ended.
• Password challenge missing/wrong password.
• Challenge visibility blocks user/org.

Files:

• src/app/api/v1/challenges/[challengeId]/registrations/route.ts
• src/lib/challenges/challenge-access.server.ts
• src/lib/challenges/challenge-password.server.ts

Registered user has no scoreboard movement

Likely causes:

• faceit-match-sync did not run or skipped participant.
• Clerk FACEIT metadata missing player id.
• Challenge not active, so cron did not include it.
• FACEIT API returned no eligible finished matches in window.
• Rows exist in challenge_faceit_matches, but no demo URL was available.
• Pipeline failed or is still processing.
• Analyzed demo exists but no matching player stats row.

Start with:

• challenge_faceit_matches rows for the user.
• demo_pipeline status/error.
• analyzed_demos + demo_player_match_stats linkage.

Demo queued but never analyzed

Likely causes:

• demo_download queue not being polled.
• faceit-downloader Cloud Run error.
• Download service uploaded demo but did not advance stage.
• demo_analyze queue not being polled.
• analyst failed parsing demo.
• analyst exceeded its Cloud Run memory limit and the container was killed.

Inspect:

• demo_pipeline.status/current_stage/error_message
• pgmq.q_demo_download
• pgmq.q_demo_analyze
• Cloud Run logs for demo-downloader and analyst

If Cloud Run logs show Memory limit ... exceeded for analyst, verify the current CPU/memory limits. In May 2026 the production service was raised from 2 vCPU/8Gi to 4 vCPU/16Gi because several CS2 demos peaked just over 8Gi.

no_player_stats in analytics

Meaning:

• analyzed_demos exists, but getChallengeAnalyticsForUser could not match the player row.

Likely causes:

• Clerk Steam ID missing or different from demo stats steam_id.
• FACEIT nickname changed or differs from demo player name.
• Demo parser produced player names differently than expected.

File:

• src/lib/challenges/challenge-analytics.server.ts

Scoreboard wrong ordering/points

Likely causes:

• challenges.scoring_config changed or invalid.
• Participant below minAnalyzedMatches.
• Placement match exclusion/baseline rules.
• Analyzed stats absent or player stats failed to match; scoreboard intentionally does not fall back to user_games.
• If an audit shows more counted matches than the scoreboard for the same user, check for unpaginated all-participant scoreboard queries. Supabase/PostgREST defaults to 1000 rows, so challenge_faceit_matches and other high-volume challenge queries must be chunked/paginated.

File:

• src/app/api/v1/challenges/[challengeId]/scoreboard/route.ts

Logs to inspect

Use MCP or gcloud logs for:

• Next/Vercel app logs for API route failures.
• Cloud Run demo-downloader logs.
• Cloud Run analyst logs.
• Cloud Run/Vercel scheduler route logs for faceit-match-sync.

Useful search terms:

• [FACEIT Match Sync]
• job-create-failed
• tracking-upserted
• job-skipped-no-demo-url
• create_pipeline_job
• advance_demo_pipeline_stage
• Queue Demos