Program Phases & Policies

This document defines the domain model EC uses to describe what program a contributor is participating in and what economic and operational rules apply to them. It complements:

• People & Roles (ops guide) — operator and player role catalog
• Playroll App Identity Link — how a contributor is linked to a Playroll app user
• Phase 1 Catalog — the titles enabled for capture in Phase 1

The model is implemented on the playroll_contributor_portal Supabase project (the legal/payments project), with cross-project edge functions that read role state from the playroll_supa_new project (the app/recording project).

Why this model exists

Until now, EC ran a single implicit "phase" — the Phase 1 pilot — with hardcoded payment thresholds ($10 / 60h / $600 in network-crm/client/src/pages/p2e-funnel.tsx) that did not match the operating policy negotiated for the pilot (€500 / 90min / 90min / 10h / 10h, manual approval).

That gap is acceptable while there is only one phase. It stops being acceptable when:

1. The pilot policy must coexist with future phases (retention loops, B2B onboarding waves, partner-sourced cohorts).
2. Operators (Phil, Ale) need a single place to ask "what is the rule for this person, right now?" without reading source code.
3. Compensation history must remain auditable: a payment made under Phase 1 rules must stay legible even after Phase 2 has replaced the active policy.

The model below decouples three concerns that were previously tangled:

• What program is active (program_phases)
• What economic policy applies inside that program (payment_policies and friends, all FK to a phase)
• What operational group a contributor belongs to (cohorts)

Conceptual model

A program phase is an EC-defined period of operations with its own rules. It is a first-class entity. Each phase aggregates a set of phase-scoped configurations, of which the payment policy is one. The configurations describe everything that can vary between phases:

program_phase
  ├── payment_policy            (economic rules: tranches, basis, approval mode)
  ├── eligibility_rules         (which player roles can participate)
  ├── validation_config         (manual vs automated pipeline)
  ├── recorder_config           (minimum app version, identity link required)
  ├── notification_config       (which notification categories are active)
  └── ... (extend by adding a new phase_*_config table — never jsonb)

A cohort is an operational grouping orthogonal to phase. Two contributors in the same cohort may belong to different phases over time, and one phase usually contains multiple cohorts (e.g. pioneers and staff both inside the pilot phase).

A contributor belongs to exactly one program phase at a time (contributors.phase_id) and to zero or more cohorts (contributor_cohorts, M:N).

Three orthogonal identity axes

When reasoning about a person in the EC system, keep these three axes separate. They live in different tables and answer different questions.

Axis	Question	Where it lives	Values today
CRM role	"What can they do in the Control Plane?"	playroll_supa_new.crm_user_roles (email-keyed)	admin, cm, vc, sales
Player role	"What can they do in the Playroll app?"	playroll_supa_new.player_roles (user_id-keyed) and playroll_supa_new.steam_id_allowlist (steam_id-keyed, pre-login)	staff, pioneer, tester
Phase + Cohort	"What program rules apply to them as a contributor?"	playroll_contributor_portal.contributors.phase_id + playroll_contributor_portal.contributor_cohorts	phase: pilot · cohorts: pioneers, staff

A single human will typically have at most one CRM role (if they are EC staff), one player role (if they have a Playroll account), and one phase + one or more cohorts (if they are a contributor). The three sets do not overlap by identity (different keys, different RBAC systems), even when the same person spans all three (e.g. Marco is admin CRM-side, staff player-side, and not a contributor at all).

Naming caveat

The word pioneer appears both as a player role (singular, individual user) and as a cohort code pioneers (plural, group). They are intentionally the same concept seen from two sides — one in the app, one in the portal. Treat singular and plural as a deliberate signal of which system you are looking at.

Schema

All tables live on playroll_contributor_portal (Supabase ref xlsjwlmsqonllvtyplml).

program_phases

The first-class phase entity. One row per program phase EC has run, is running, or has scheduled.

Column	Type	Notes
id	uuid PK
code	text UNIQUE	Semantic identifier: pilot, retention_v1, b2b_pilot_q3. Avoid phase1 / phase2 numeric codes — they age badly.
label	text	Human-readable: "Phase 1 Pilot"
description	text	One-paragraph summary
default_for_new_contributors	boolean	Exactly one row may have this set to true (unique partial index). Drives the auto-assignment of new contributors who pass eligibility checks.
starts_at, ends_at	timestamptz	Optional. Audit only — does not gate eligibility.
notes	text
created_at, updated_at	timestamptz

Constraint: unique partial index ensures at most one default phase at any time:

create unique index one_default_phase
  on program_phases (default_for_new_contributors)
  where default_for_new_contributors = true;

hour_basis_types (catalog)

The canonical glossary of "what does an hour mean". Eliminates the ambiguity between duration_seconds, validated_hours, rewardable_seconds, and the seven other variants found across the codebase.

Column	Type	Notes
code	text PK	recorded_minutes, uploaded_minutes, validated_minutes
label	text	Display name
description	text	What the value actually represents
source_table	text	Where it is computed from
source_column	text	Which column is summed/counted
aggregation	text CHECK	sum, count, max, min, avg
unit	text CHECK	seconds, minutes, hours, count

Seed:

code	source_table	source_column	aggregation	unit
recorded_minutes	recording_sessions (app project)	duration_seconds	sum	seconds
uploaded_minutes	recording_sessions (app project, filtered to sessions with ≥1 successful upload)	duration_seconds	sum	seconds
validated_minutes	session_validation_results (portal project)	validated_seconds	sum	seconds

payment_policies.basis_code FKs into this table.

payment_policies

The economic policy for a phase. One row per phase (1:1 today; the unique constraint can be relaxed if A/B testing is later needed).

Column	Type	Notes
id	uuid PK
phase_id	uuid FK UNIQUE → program_phases(id) ON DELETE CASCADE	1:1 with the phase
total_eur	numeric	Per-contributor cap, e.g. 500 for Phase 1
basis_code	text FK → hour_basis_types(code)	Source of truth for which "hour" is being measured
approval_mode	text CHECK (manual, auto, hybrid)	Pilot is manual. Allows future expansion without dropping the boolean.
capacity_eur	numeric NULL	Optional program-wide budget cap (separate from per-contributor cap)
notes	text

payment_policy_tranches

The reward steps inside a policy. Stored incrementally (threshold_delta_minutes) to mirror the way the PM describes them ("first 90 minutes... second 90 minutes... next 10 hours"). A view exposes cumulative thresholds for query convenience.

Column	Type	Notes
id	uuid PK
policy_id	uuid FK → payment_policies(id) ON DELETE CASCADE
idx	int	Order within the policy (UNIQUE with policy_id)
label	text	"Trust 1", "Block 1"
threshold_delta_minutes	int	Increment from the previous tranche
eur	numeric	Amount unlocked when this tranche is reached

Companion view:

create view payment_policy_tranches_cumulative_v as
select
  t.*,
  sum(t.threshold_delta_minutes) over (
    partition by t.policy_id
    order by t.idx
    rows between unbounded preceding and current row
  ) as cumulative_threshold_minutes
from payment_policy_tranches t;

cohorts (catalog)

The catalog of named cohorts. Prevents typo-driven cohort proliferation (pioneer vs pioneers vs Pioneers).

Column	Type	Notes
code	text PK	pioneers, staff, future: b2b_recruit_q3, ...
label	text	Display name
description	text	What this cohort represents
created_at	timestamptz

Seed: pioneers ("First wave of hand-picked players for the pilot launch") and staff ("Internal EC team members participating as contributors for dogfooding").

contributor_cohorts

M:N between contributors and cohorts. A contributor can belong to multiple cohorts over time.

Column	Type	Notes
contributor_id	uuid FK → contributors(id) ON DELETE CASCADE
cohort_code	text FK → cohorts(code)
joined_at	timestamptz default now()
left_at	timestamptz NULL	NULL = still in the cohort
PK	(contributor_id, cohort_code)

phase_eligibility_rules

Which player_role values are admitted into a phase. OR semantics: a contributor is eligible if their player role matches any row for the phase.

Column	Type	Notes
id	uuid PK
phase_id	uuid FK → program_phases(id) ON DELETE CASCADE
allowed_player_role	text	Matches playroll_supa_new.player_role_catalog.role
max_contributors	int NULL	NULL = unlimited
UNIQUE	(phase_id, allowed_player_role)

For Phase 1 pilot: two rows, (pilot, pioneer, 15) and (pilot, staff, NULL).

phase_validation_config

How session validation works inside a phase. One row per phase.

Column	Type	Notes
phase_id	uuid UNIQUE FK → program_phases(id) ON DELETE CASCADE
pipeline	text CHECK (manual, auto_v1, auto_v2)	Pilot uses manual until the validation pipeline ships
validation_pct_required	int NULL	Threshold for the QA pipeline when active
notes	text

phase_recorder_config

App-side requirements for participating in a phase.

Column	Type	Notes
phase_id	uuid UNIQUE FK → program_phases(id) ON DELETE CASCADE
min_recorder_version	text NULL	Block recorder versions below this
requires_steam_link	boolean default true	Whether the contributor must have a verified contributor_app_links row to count
notes	text

phase_notification_config

Which notification categories are enabled for a phase. M:N (one row per category/subtype).

Column	Type	Notes
id	uuid PK
phase_id	uuid FK → program_phases(id) ON DELETE CASCADE
category	text	e.g. cohort_announcement, payment_readiness
subtype	text NULL
t1_throttle, t2_throttle	int NULL	7-day window caps
UNIQUE	(phase_id, category, subtype)

Empty in Phase 1 (no per-phase throttling yet) — the table is added now so future phases can attach configuration without a schema change.

Contributor wiring

alter table contributors add column phase_id uuid references program_phases(id);

A contributor with phase_id IS NULL is registered but not yet enabled for any phase — typical for someone who completed the portal onboarding but whose Steam identity does not yet match any phase's eligibility rules. They cannot record and they cannot earn until an admin (or the auto-assignment in the edge function) sets the phase.

Compensation history: freeze at creation

alter table compensation_items add column policy_id uuid references payment_policies(id);

Every compensation_items row records which policy was in effect when it was created. This makes payment history audit-stable: even after a contributor is promoted to a later phase, the old items remain attributed to the policy that produced them. No retroactive recalculation.

The immutability is enforced by trigger, not by convention:

create or replace function prevent_compensation_policy_change()
returns trigger language plpgsql as $$
begin
  if old.policy_id is not null and new.policy_id is distinct from old.policy_id then
    raise exception 'compensation_items.policy_id is immutable after creation (item_id=%)', old.id;
  end if;
  return new;
end $$;

create trigger compensation_items_policy_immutable
  before update on compensation_items
  for each row execute function prevent_compensation_policy_change();

Phase assignment at link time

When the contributor↔app link is established (either flow described in identity-link.md), the edge function runs the assignment routine below.

Assignment rules (Phase 1)

steam_id_allowlist.role	phase_id	contributor_cohorts.code	Outcome
pioneer	pilot	pioneers	Link succeeds, contributor active
staff	pilot	staff	Link succeeds, contributor active
tester	—	—	Link rejected — wave 2 not yet activated for the pilot
not in allowlist	—	—	Link rejected — steam_id_not_allowlisted

The allowlist is the gate for Phase 1. It will be dismissed in a later phase once eligibility is determined by other criteria (e.g. application acceptance, partner-sourced cohorts). When that happens, the assignment routine will switch from "read allowlist" to "evaluate phase_eligibility_rules against the contributor's player_role" — same shape, different source.

Transitions between phases

Phase is per-contributor, not global. Promoting a contributor from pilot to a future phase is an explicit admin action:

1. Update contributors.phase_id to the new phase.
2. Insert contributor_cohorts row(s) for any new cohort assignments. The old cohort row should set left_at.
3. The promotion writes to audit_events.
4. Compensation items already created stay attached to their original policy_id (immutable, see above).

Automated promotion rules (e.g. "if all 4 tranches of pilot are unlocked, promote to retention") may be added later as a scheduled function. For Phase 1 the promotion is manual.

Decision summary

Decision	Rationale
program_phases as first-class entity	Avoids overloading "phase" with contributor lifecycle or marketing waves. The phase is the program.
Phase configurations as separate tables keyed by phase_id, not jsonb columns	Adding a new aspect (missions, leaderboards) means adding a new phase_*_config table. No jsonb that grows untyped.
Cohort + phase as distinct axes	A cohort is operational (grouping); a phase is contractual (rules). Conflating them locks the model.
cohorts as catalog table with FK	Eliminates typo-driven cohort string proliferation.
hour_basis_types as catalog	The 9 hour-type concepts in the codebase collapse to 3 canonical types with explicit source_table / source_column.
Tranches stored incrementally + cumulative view	Matches how the business describes them; cumulative form is one window function away.
default_for_new_contributors boolean with partial unique index	"Active" is ambiguous (two phases can be live in parallel for different cohorts). "Default for new contributors" is the question the assignment routine actually needs to answer.
Phase assignment via edge function, not DB trigger	Visible in logs, debuggable, testable, lives next to the existing link flows. Triggers hidden in the DB are easy to forget.
compensation_items.policy_id immutable via trigger	Legal-grade history. The trigger is defensive; convention alone is not enough at this level of audit risk.
approval_mode text (not boolean)	Future hybrid modes ("auto for amounts under €X, manual above") fit without dropping the column.
Allowlist reads stay in the assignment routine	The allowlist will be dismissed after Phase 1. Centralizing the allowlist read in one place makes the future migration to phase_eligibility_rules-only assignment a one-file change.

Open questions deferred to a later phase

These do not block Phase 1 and are explicitly out of scope for the initial migration:

• Automated phase promotion (cron edge function that promotes contributors who complete all tranches).
• Per-phase notification throttling (table exists, no rows seeded in Phase 1).
• A/B testing two payment policies inside the same phase (would relax the payment_policies.phase_id UNIQUE constraint).
• Cohort hierarchy (parent/child cohorts). Today cohorts are flat.

Related migrations

The model is delivered in four migrations under playroll-contributor-portal/supabase/migrations/:

• M1 — program_phases, hour_basis_types, payment_policies, payment_policy_tranches + cumulative view. Seeds only hour_basis_types.
• M2 — cohorts (catalog with pioneers and staff seed), phase_eligibility_rules, phase_validation_config, phase_recorder_config, phase_notification_config.
• M3 — contributors.phase_id, contributor_cohorts, compensation_items.policy_id with immutability trigger.
• M4 — Seed the pilot phase: program_phase row, payment_policy with €500 cap and recorded_minutes basis, 4 tranches (90/90/600/600 delta minutes, 50/50/200/200 EUR), eligibility rules for pioneer and staff, manual validation, recorder requires Steam link. Backfill the 5 existing contributors to pilot phase and staff cohort.

Edge function update (activate-contributor-app-link and redeem-activation-code) follows in a separate change to implement the assignment routine described above.