Team Analytics — Designer Handoff (Cici)¶

This doc is everything you need to query Sync's team data directly from Supabase, without spelunking through the schema. Open the Supabase SQL editor next to this and copy queries in.

0. Prerequisites — do these before running any query¶

The Beacon team doesn't exist in the DB yet, and no campaigns are tagged to a team. Until that changes, every query below returns empty.

Step 1: CoachJ creates the Beacon team¶

Use the UI at /account/teams → "Create team." Name it Beacon. Add: Amin, james, cici, natascha.jasmin (the system will auto-add the creator as owner).

Step 2: Tag the 4 historical multiplayer campaigns¶

Once the team exists, run this once (replace BEACON_TEAM_ID with the real id from SELECT id FROM teams WHERE name='Beacon';):

UPDATE campaigns
SET team_id = 'BEACON_TEAM_ID'
WHERE id IN (
  '3c8b1b61-8dc6-4315-b679-6af0c236bea6',  -- The Brink (Apr 22)
  '06a75aea-243f-46c8-afe8-7679f334a1a4',  -- The Inflection Point (Apr 20)
  '06e2e091-93b3-4a92-b4ed-907f868ce9c4',  -- The Pivot (Apr 14)
  'd38ae62c-b9f0-4ab5-8df0-d0d6dbd8713d'   -- The Kepler Divide (Apr 10, active)
);

Note the dataset is small (4 campaigns, ~4–5 sessions). Patterns will be directional, not statistically significant. Treat the visualizations as design artifacts, not as conclusions about teammates.

1. Data dictionary — where each signal lives¶

What you want to show	Source table	Key columns
Per-player choice on each scenario	`choices`	`session_id`, `user_id`, `chosen_option_id`, `decision_strength` (conviction), `none_of_above`
Which scenario a session was about	`sessions`	`id`, `scenario_id`, `campaign_id`
Scenario text + options	`scenarios`	`id`, `title`, `description`, `options` (jsonb), `category`
Which campaign belongs to a team	`campaigns`	`id`, `team_id`, `name`
Players in a session (with guest flag)	`session_participants`	`session_id`, `user_id`, `is_guest`
Who predicted whom + accuracy	`peer_prediction_edges`	`predictor_id`, `target_id`, `was_correct`, `predictor_confidence`
Per-session readability rollup	`peer_readability_scores`	`user_id`, `session_id`, `readability_score`, `scenario_category`
Stand / Revise / Delegate after discussion	`post_discussion_actions`	`user_id`, `action_type`, `revised_option_id`, `responding_to_user_ids` (uuid array — who moved you), `delegated_to_user_id`
Persistent delegation patterns	`delegation_preferences`	`user_id`, `target_user_id`, `category`, `strength`, `games_together`
Display name → user_id	`profiles`	`id`, `display_name`

Always filter to the team via campaigns.team_id = :TEAM_ID, joined through sessions.campaign_id. Always exclude guests via session_participants.is_guest = false if mixing in non-team players.

2. Starter SQL kit¶

Replace :TEAM_ID with the Beacon team's UUID (from SELECT id FROM teams WHERE name='Beacon'). Each query is independent — copy and run.

2.1 Most-aligned and most-divergent pairs¶

For every pair of teammates, what % of scenarios did they pick the same option?

WITH team_choices AS (
  SELECT c.user_id, c.session_id, c.chosen_option_id, s.scenario_id
  FROM choices c
  JOIN sessions s ON s.id = c.session_id
  JOIN campaigns ca ON ca.id = s.campaign_id
  WHERE ca.team_id = :TEAM_ID
)
SELECT
  p1.display_name AS player_a,
  p2.display_name AS player_b,
  COUNT(*) AS shared_scenarios,
  SUM(CASE WHEN tc1.chosen_option_id = tc2.chosen_option_id THEN 1 ELSE 0 END) AS agreed,
  ROUND(100.0 * SUM(CASE WHEN tc1.chosen_option_id = tc2.chosen_option_id THEN 1 ELSE 0 END) / COUNT(*), 1) AS agreement_pct
FROM team_choices tc1
JOIN team_choices tc2
  ON tc1.session_id = tc2.session_id
  AND tc1.user_id < tc2.user_id  -- each pair once
JOIN profiles p1 ON p1.id = tc1.user_id
JOIN profiles p2 ON p2.id = tc2.user_id
GROUP BY p1.display_name, p2.display_name
HAVING COUNT(*) >= 3
ORDER BY agreement_pct DESC;

Top row = most-aligned pair. Bottom row = most-divergent. The HAVING COUNT(*) >= 3 filters out pairs who barely overlap.

2.2 Voice equity — who gets cited as moving people¶

WITH cited AS (
  SELECT unnest(pda.responding_to_user_ids) AS cited_user_id
  FROM post_discussion_actions pda
  JOIN sessions s ON s.id = pda.session_id
  JOIN campaigns ca ON ca.id = s.campaign_id
  WHERE ca.team_id = :TEAM_ID
    AND pda.action_type = 'revise'
)
SELECT p.display_name, COUNT(*) AS times_cited_as_mover
FROM cited c
JOIN profiles p ON p.id = c.cited_user_id
GROUP BY p.display_name
ORDER BY times_cited_as_mover DESC;

High value = "people change their minds because of this person." Note: this signal is sparse until responding_to_user_ids adoption picks up post-rollout.

2.3 Peer readability — who reads whom best¶

SELECT
  pp.display_name AS predictor,
  pt.display_name AS target,
  COUNT(*) AS predictions,
  ROUND(100.0 * SUM(CASE WHEN ppe.was_correct THEN 1 ELSE 0 END) / COUNT(*), 1) AS accuracy_pct
FROM peer_prediction_edges ppe
JOIN sessions s ON s.id = ppe.session_id
JOIN campaigns ca ON ca.id = s.campaign_id
JOIN profiles pp ON pp.id = ppe.predictor_id
JOIN profiles pt ON pt.id = ppe.target_id
WHERE ca.team_id = :TEAM_ID
  AND ppe.skipped = false
GROUP BY pp.display_name, pt.display_name
HAVING COUNT(*) >= 3
ORDER BY accuracy_pct DESC;

Asymmetric on purpose: A reads B well doesn't mean B reads A well.

2.4 Delegation network — who trusts whom on what¶

SELECT
  p1.display_name AS delegator,
  p2.display_name AS delegate,
  dp.category,
  dp.strength,
  dp.games_together
FROM delegation_preferences dp
JOIN profiles p1 ON p1.id = dp.user_id
JOIN profiles p2 ON p2.id = dp.target_user_id
WHERE dp.user_id IN (SELECT user_id FROM team_members WHERE team_id = :TEAM_ID)
  AND dp.target_user_id IN (SELECT user_id FROM team_members WHERE team_id = :TEAM_ID)
ORDER BY dp.strength DESC;

Edges in the trust graph, with the category dimension (governance, resources, etc).

2.5 Conviction-shift map — who tends to change their mind¶

SELECT
  p.display_name,
  COUNT(*) FILTER (WHERE pda.action_type = 'stand') AS stood,
  COUNT(*) FILTER (WHERE pda.action_type = 'revise') AS revised,
  COUNT(*) FILTER (WHERE pda.action_type = 'delegate') AS delegated,
  ROUND(100.0 * COUNT(*) FILTER (WHERE pda.action_type = 'revise') / NULLIF(COUNT(*), 0), 1) AS revise_pct
FROM post_discussion_actions pda
JOIN sessions s ON s.id = pda.session_id
JOIN campaigns ca ON ca.id = s.campaign_id
JOIN profiles p ON p.id = pda.user_id
WHERE ca.team_id = :TEAM_ID
GROUP BY p.display_name
ORDER BY revise_pct DESC;

High revise_pct = persuadable / open. Pair this with 2.2 (who moved them) to see the influence flow.

2.6 Where the team splits — fault-line scenarios¶

WITH team_choices AS (
  SELECT c.user_id, c.session_id, c.chosen_option_id, s.scenario_id
  FROM choices c
  JOIN sessions s ON s.id = c.session_id
  JOIN campaigns ca ON ca.id = s.campaign_id
  WHERE ca.team_id = :TEAM_ID
)
SELECT
  sc.title,
  sc.category,
  COUNT(DISTINCT tc.chosen_option_id) AS distinct_options_picked,
  COUNT(*) AS total_picks
FROM team_choices tc
JOIN scenarios sc ON sc.id = tc.scenario_id
GROUP BY sc.id, sc.title, sc.category
HAVING COUNT(DISTINCT tc.chosen_option_id) >= 2
ORDER BY distinct_options_picked DESC, total_picks DESC;

Scenarios where the team genuinely split. Useful for surfacing "what kinds of decisions divide us?" by looking at category patterns.

2.7 Low-additive-voice pairs — who's redundant in the room¶

Cici's insight from the May 5 playtest: if A almost always changes their mind to match B (and never the other way around), then having both A and B in the same group adds little — A's vote effectively becomes B's vote. This query surfaces directional revision pairs.

WITH directional_revisions AS (
  SELECT
    pda.user_id AS reviser,
    unnest(pda.responding_to_user_ids) AS mover
  FROM post_discussion_actions pda
  JOIN sessions s ON s.id = pda.session_id
  JOIN campaigns ca ON ca.id = s.campaign_id
  WHERE ca.team_id = :TEAM_ID
    AND pda.action_type = 'revise'
    AND pda.responding_to_user_ids IS NOT NULL
)
SELECT
  pr.display_name AS reviser,
  pm.display_name AS mover,
  COUNT(*) AS times_reviser_changed_to_match_mover
FROM directional_revisions dr
JOIN profiles pr ON pr.id = dr.reviser
JOIN profiles pm ON pm.id = dr.mover
GROUP BY pr.display_name, pm.display_name
HAVING COUNT(*) >= 2
ORDER BY times_reviser_changed_to_match_mover DESC;

Compare directionally: a high reviser → mover count with a low mover → reviser count = lopsided. Useful for "should we put both of them on this working group, or just one?" Caveat: at small N, one or two revisions look lopsided by chance. Wait for ≥4–5 revisions in a direction before drawing conclusions.

3. Caveats¶

Small N. With 4 campaigns and 4–5 sessions, every metric is suggestive, not conclusive. Design the surface to communicate this — confidence intervals or "pattern emerging from N=12 scenarios" copy.
Symmetry. Alignment is symmetric (A↔B). Readability and delegation are not (A→B differs from B→A). The visualizations should reflect that — a directed graph for readability/delegation, an undirected list for alignment.
Guest exclusion. None of the queries above currently filter is_guest = false because the Beacon team didn't have guests. Add AND sp.is_guest = false if mixing in pickup play.
Identity vs process. Per the project's identity-vs-process philosophy, frame surfaces as per-decision context ("for this kind of question, X tends to be the most-cited mover") not persistent role labels on people.
Stuck? Ping CoachJ in Slack with the query and the result. If a metric looks wrong, it's usually a join error, not the data.