Growth & Network

Plain-language summary. This domain is about acquisition through the players we already have. Every Playroll user has a Steam friend list; those friends are leads. We mirror that network into the shadow graph, score it, and surface the most valuable not-yet-recruited friends in the headhunter view. Two smaller mechanisms — invite codes and referrals — gate and reward the actual sign-ups.

Map

shadow_graph — the friend network

The recruitment engine. Each row is a directed edge from a registered Playroll user (source_user_id / source_steam_id) to one of their Steam friends (target_steam_id) who may or may not be a Playroll user yet.

Each edge is unique on (source_user_id, target_steam_id). The "target" side carries a full enrichment snapshot of that friend so the headhunter view can rank them without a second API call: target_name, target_avatar_url, target_profile_url, target_hours, target_games_owned, target_level, target_profile_public, plus two scoring columns — target_tier (whale | regular | casual, default casual, assigned by calculate_shadow_tier) and target_yearly_value (the estimated worth of recruiting them).

The recruitment funnel is tracked inline. recruitment_status is a closed enum:

Column	Meaning
recruitment_status	none (default) → invited → converted.
invited_at	When we reached out.
converted_at	When the friend became a Playroll user.
converted_user_id	Nullable FK to the playroll_users row they became — closing the loop from lead to member.
friend_since	Steam-reported friendship age (a Unix timestamp).

The graph aggregates onto the source user as playroll_users.network_value, network_whale_count, and steam_friend_count — but this is not automatic: it's recomputed by the update_network_stats(p_user_id) SECURITY DEFINER RPC (no trigger fires it), and it counts only rows where recruitment_status = 'none' (still-open leads) — so invited/converted friends drop out of network_value.

This is the "headhunter" data

The friend-network / headhunter view in the dashboards is a projection of shadow_graph. When someone asks "who should we recruit next?", the answer is a query over this table ordered by target_yearly_value where recruitment_status = 'none'.

Invite codes — Phase-0 gating

A fixed pool of codes that gate access on playroll.gg during invite-only phases.

• invite_codes — the pool. Each code belongs to a pool (default phase_0), allows max_uses redemptions (CHECK max_uses > 0, tracking uses within 0..max_uses), can be deactivated (active), optionally expires_at, and records who minted it (created_by → a user).
• invite_code_consumptions — composite PK (code, user_id), so a user can consume a given code at most once. It's the success log: the consume_invite_code RPC checks if exists(...) return 'already' before inserting, so a repeat success is impossible and never produces a second row. Captures consumed_at and abuse-context (ip_hash, user_agent). Note the invite flow does not log misses anywhere — consume_invite_code just returns sentinel strings ('already', 'expired', …) without writing.

consume_invite_code is a SECURITY DEFINER RPC: it enforces auth.uid() = p_user_id (else error 42501), normalizes the code, takes a SELECT ... FOR UPDATE row lock, checks active/expiry/duplicate/uses < max_uses, then atomically inserts the consumption and increments invite_codes.uses under that lock.

redeem_attempt_log is not part of the invite flow

Despite the name's resemblance, redeem_attempt_log backs the contributor activation-code flow (redeem-activation-code edge function, EC-227), not invite codes. It is the rate-limiter log for that flow (per app_user_id, per ip, per code_hash). It is documented with the contributor/activation domain, not here. (app_user_id is an unconstrained uuid — a soft reference, deliberately not an FK.)

Access model

RLS is on all the growth tables and they are service-role-only: shadow_graph, invite_codes, and invite_code_consumptions carry deny-anon + deny-authenticated policies with service-role full access. User access happens only through the consume_invite_code RPC (above) — never direct table reads.

Referrals

The referral loop lives as columns on playroll_users rather than its own table:

• referral_code — the player's own shareable code.
• referred_by — the code (or user) that brought them in.
• referral_count — how many sign-ups they've driven.

Combined with the referrer column and the utm_* attribution columns (see Identity & Access), this is the full picture of how a given player arrived.

---

See also: full column reference for these tables.