Consent Versioning

How the Playroll launcher records user consents, why the rotation rule has two flavors, and how the cross-DB UNION with the contributor portal stays sane after identities are linked.

Background: this ships EC-23 (age gate) + EC-25 (DB persistence). The launcher and the contributor portal are different products with different audiences (consumer launcher vs. B2B contributor program), but the two universes overlap on at least one document — the Privacy Policy. The system has to keep one stable view of "did this person accept the current version?" without forcing duplicate prompts when the two identities get linked.

Pipeline

Every acceptance arrives through consents-accept. The renderer never persists or back-fills a row by itself.

Schema

Launcher (playroll_supa_new.playroll_user_consents)

create table public.playroll_user_consents (
    id           uuid primary key default gen_random_uuid(),
    user_id      uuid not null references auth.users(id) on delete cascade,
    consent_type text not null check (consent_type in (
        'age_confirmation', 'tos', 'privacy', 'eula', 'edc_gameplay'
    )),
    version      text not null,
    accepted_at  timestamptz not null default now(),
    country      text,        -- populated only for age_confirmation
    metadata     jsonb not null default '{}'::jsonb,
    ip_address   inet,        -- captured server-side from the edge request
    user_agent   text         -- ditto
);

Append-only. No UPDATE / DELETE policies — the table is a legal audit log. RLS lets users SELECT their own rows (useful for a future "your data" UI); only the service role can INSERT (the consents-accept edge function).

Portal (playroll-contributors.consents + document_versions)

Pre-existing portal table; this work added rotation metadata to document_versions:

Column	Meaning
current_version	The version operators want users to be on right now.
previous_version	What was current immediately before the last rotation. Used together with rotation_kind to grant a free pass.
rotation_kind	material (legally meaningful change → everyone re-accepts) vs cosmetic (typo / formatting only → prior acceptance carries forward). Default material.
consent_type_canonical	Bridge to the launcher vocabulary. Today privacy → privacy; null for portal-only documents (compensation, incarico, isee_notice, package).

Canonical consent vocabulary

Five consent_type values, all enforced by the launcher CHECK constraint:

consent_type	Source of truth for version	Overlap with portal
age_confirmation	launcher constant (playroll-age-confirmation-v1)	—
tos	launcher constant (playroll-tos-v1)	—
privacy	portal document_versions.current_version	yes — same string in both DBs
eula	launcher constant (playroll-eula-v1)	—
edc_gameplay	launcher constant (playroll-edc-gameplay-v1)	—

Launcher constants are baked into the consents-accept and consents-status edge function source. Bumping one of them = rotation of that document; ship the edge function and every client re-prompts at the next boot.

privacy is the only document that overlaps with the contributor portal today. The consents-accept edge resolves its version by fetching document_versions.current_version where document_id = 'privacy' from the portal at request time (cached 60 s inside one warm invocation). That way the launcher record carries the same string the portal record carries — making the cross-DB UNION below well-defined.

The rotation rule

consents-status decides requires_action per consent_type using this rule, applied server-side:

if accepted_version == current_version           -> ok
if rotation_kind == 'cosmetic'
   and accepted_version == previous_version       -> ok  (typo fix preserves prior consent)
otherwise                                         -> requires_action: true

material is the default for new rotations — the safe legal choice. Operators set rotation_kind = 'cosmetic' explicitly on the portal admin path when they want users to skip the prompt.

For launcher-managed consents (tos / eula / edc_gameplay / age_confirmation) we don't track previous_version or rotation_kind in a table. Every bump of a launcher constant = material rotation by definition. If we ever need cosmetic rotations launcher-side we move the four constants into a small mirror table.

Cross-DB UNION (launcher + portal)

consents-status aggregates two logs into one effective view:

1. Launcher acceptances from playroll_user_consents (this DB).
2. Portal acceptances from playroll-contributors.consents (different DB, fetched via CONTRIBUTOR_PORTAL_URL + CONTRIBUTOR_SUPABASE_SERVICE_ROLE_KEY edge secrets).

The portal lookup happens only after the identities are linked. The bridge is contributor_app_links (portal table):

-- looking up the portal contributor_id from the launcher user
select contributor_id
from contributor_app_links
where app_user_id = <launcher auth.uid()>
  and app_project = 'playroll'
  and status      = 'linked'
order by linked_at desc
limit 1;

Until the Steam handshake completes the link, contributor_id resolves to null and the portal side of the UNION is empty — consents-status just returns the launcher view.

After linking, for each consent_type the edge picks the most recent acceptance across both logs, then runs the rotation rule. So if Marco accepted playroll-privacy-ack-v1 via the launcher on May 17 and the portal already had playroll-privacy-ack-v2 on file from May 20, the launcher won't ask him to re-accept the v1→v2 jump — the portal row carries the newer version with the same canonical string.

API surface

POST /functions/v1/consents-accept

// Authorization: Bearer <user JWT>
{
  "acceptances": [
    { "consent_type": "age_confirmation", "accepted_at": "2026-05-17T15:09:01Z", "country": "IT" },
    { "consent_type": "tos",              "accepted_at": "2026-05-17T15:09:30Z" },
    { "consent_type": "privacy",          "accepted_at": "2026-05-17T15:09:30Z" },
    { "consent_type": "eula",             "accepted_at": "2026-05-17T15:09:30Z" },
    { "consent_type": "edc_gameplay",     "accepted_at": "2026-05-17T15:10:00Z" }
  ]
}

Server-side resolution:

• version is set by the edge — the renderer cannot record an outdated or arbitrary version.
• country for age_confirmation falls back to playroll_users.country when the renderer omits it (the reacceptance overlay does). When neither source has a country the row is still inserted with country = NULL and the metadata flag age_country_fell_through: true (see Country handling below). playroll_user_consents.country is nullable on purpose — the column is GDPR audit metadata, not a precondition for recording the "I am 18+" boolean.
• ip_address + user_agent are captured from the request, never from the body.

Rate limit: 5 batches per minute per user.

Response: 201 { "inserted": <n>, "rows": [...] }.

GET /functions/v1/consents-status

// Authorization: Bearer <user JWT>
{
  "user_id": "<auth.uid>",
  "consents": [
    {
      "consent_type": "privacy",
      "current_version": "playroll-privacy-ack-v1",
      "accepted_version": "playroll-privacy-ack-v1",
      "accepted_at": "2026-05-17T15:09:30Z",
      "source": "launcher",        // or "portal" or null
      "requires_action": false
    },
    { ... }
  ]
}

One entry per known consent_type. The renderer treats any requires_action: true entry as a blocker — ConsentReacceptanceOverlay lists the pending ones in a non-dismissible modal.

UI integration

Surface	When	What it does
OnboardingFlow	At end of onboarding (after Steam login)	Builds the consent batch from the reducer state, calls submitOnboardingConsents fire-and-forget, then closes the onboarding gate. Failures are not blocking — the boot-time status check is the safety net.
Dashboard	Post-auth, after onboarding gate is closed	Polls getConsentsStatus. Filters requires_action: true and feeds the array to ConsentReacceptanceOverlay.
ConsentReacceptanceOverlay	When requires_action.length > 0	Non-dismissible modal listing the pending consents. The user must tick each one. On submit it calls submitOnboardingConsents with a partial batch (only the consents being re-accepted), then re-runs the status check to dismiss itself.

Reading the data

"Did Marco accept the current Privacy Policy?"

-- launcher side
with current as (
  select current_version from portal.document_versions where document_id = 'privacy'
)
select 1
from public.playroll_user_consents c, current
where c.user_id = $marco
  and c.consent_type = 'privacy'
  and c.version = current.current_version
union all
-- portal side, only if linked
select 1
from portal.consents c, current,
     portal.contributor_app_links link
where link.app_user_id = $marco
  and link.app_project = 'playroll'
  and link.status      = 'linked'
  and c.contributor_id = link.contributor_id
  and c.consent_type   = 'privacy'
  and c.version        = current.current_version
limit 1;

(In practice you don't write this by hand — call consents-status.)

"How many users still owe me v2 of the Privacy Policy?"

with target as (select 'playroll-privacy-ack-v2'::text as v),
     accepted as (
       select user_id
       from public.playroll_user_consents, target
       where consent_type = 'privacy' and version = target.v
     )
select count(*) as users_pending
from public.playroll_users u
where u.user_id not in (select user_id from accepted);

(Excludes the portal side of the UNION — a more accurate count would also subtract users with portal acceptance of playroll-privacy-ack-v2 via contributor_app_links. For pilot-scale ops the launcher-only approximation is usually enough.)

Operational notes

• Append-only is non-negotiable. Never UPDATE an existing row to bump its version. Every acceptance is a separate row; the latest (user_id, consent_type) row wins. This is how the legal audit log stays defensible.
• Cosmetic rotations need explicit operator action. Default is material. Setting rotation_kind = 'cosmetic' is a deliberate "this change does not invalidate prior consent" decision; document why in the operator runbook when you do it.
• The 60s cache on portal lookups (inside consents-accept) means a rotation of privacy on the portal can take up to ~1 minute to show up on the launcher. Acceptable for typical use; not a hot path. Bumping a launcher-side version is instant after edge redeploy.
• Country fallback for age_confirmation — see the dedicated section below.

Country handling for age_confirmation

The country column on playroll_user_consents is nullable by design. It carries GDPR jurisdiction audit metadata for the age-of-consent acceptance, but it is not a legal precondition for recording the user's "I am 18+" boolean. The edge function does its best to resolve a country, but a missing country never blocks the acceptance.

Resolution order

consents-accept walks three sources in order, taking the first non-null:

1. Client payload: acceptances[i].country. OnboardingFlow passes the value picked in AgeStep (default "US"); the boot-time ConsentReacceptanceOverlay passes null on purpose — it does not re-prompt for a country.
2. playroll_users.country: populated server-side from Steam profile fetch (GetPlayerSummaries) at signup. Steam returns this only when the user's profile is public and a location is set. ~85% of users have it; ~15% do not.
3. Fallthrough: insert with country = NULL and metadata.age_country_fell_through = true.

The third path was the source of bug-16 (2026-05-19) — the edge used to reject the entire batch with HTTP 400 in this case, leaving ~15% of users stuck behind a non-dismissible re-acceptance overlay. The current behavior records the acceptance and tags it for later backfill.

Backfill query

Find rows that ended up with country = NULL because of the fallthrough:

select id, user_id, accepted_at
from public.playroll_user_consents
where consent_type = 'age_confirmation'
  and country is null
  and (metadata->>'age_country_fell_through')::boolean = true
order by accepted_at desc;

These are the targets if a future country picker is added to the re-acceptance overlay, or if the operator wants to surface a one-off "set your country" prompt to the affected users.

When country is required

Never, today. The only operational consequence of a NULL country is that the GDPR audit trail for that single acceptance row is less specific. The user is still legally on record as having confirmed they are 18+.

If a future regulation requires a non-null country, the move is:

1. Add a country picker to the re-acceptance overlay so the client always has a value.
2. Backfill the existing NULL rows using the query above + the picker prompt.
3. Then add a CHECK constraint to the table.

Don't add the CHECK constraint before steps 1 and 2 — it would re-create the bug-16 scenario.

References

• Launcher table: playroll-cpp/supabase/migrations/20260518181200_playroll_user_consents.sql
• Portal extension: playroll-contributor-portal/supabase/migrations/20260518181300_document_versions_rotation_metadata.sql
• Edge functions: playroll-cpp/supabase/functions/consents-accept/ and consents-status/
• UI: playroll-ui/src/renderer/features/consents/ConsentReacceptanceOverlay.tsx, OnboardingFlow.completeOnce, Dashboard.onComplete
• bug-16 country fallthrough fix: playroll-cpp commit 87c3ad5 (edge consents-accept v4 → v5), playroll-ui commit 1c36ffc (FunctionsHttpError context unwrap), root-cause writeup playroll-analytics/diagnostics/bug-16-consents-edge-functions-100pct-failure.md
• Linear: EC-23, EC-25
• Related: Onboarding Gate Flow