Referral ID (referreid) — Design Decisions

• Status: Draft — for review, no code changes made yet
• Date: 2026-10-12
• Scope: How the per-user referral identifier (referreid) is generated, stored, and made unique in the UserPoints Joomla component (com_userpoints).

1. Background and terminology

UserPoints assigns each registered user a referral ID, stored in the referreid column
(spelled referreid in most code, occasionally referrerid/referrerID). It is the value
users put in their referral links (?referrer=<referreid>) so that new sign-ups can be
attributed to them.

Three configuration options govern it (admin/config.xml, lines 100–105):

2. Constraints

1. Widely distributed software. This component ships to many independent sites; changes
   must be safe, backward-compatible, and must not break existing deployments or their data.
2. Database assumption A: the overwhelming majority of hosting uses MariaDB.
3. Database assumption B: deployments on anything other than MySQL/MariaDB are
   negligible. (The component already relies on MySQL/MariaDB-specific SQL — TO_DAYS,
   IFNULL, ON DUPLICATE KEY, backtick quoting — so this is a statement of fact, not a new
   restriction.)
4. Referral links are long-lived and immutable. Users hand links to strangers and they
   persist for an arbitrary time. Therefore an existing referreid value must never be
   changed after creation; re-keying existing IDs would break points data, the referral
   chain, and every link already in the wild.

3. Current state (findings)

3.1 There are two divergent generators

• Plugin path — admin/install/plugins/user-up_newregistered/up_newregistered.php,
  makeNewReffererid() (lines 58–71). Runs on onUserAfterSave. Reads both prefixes and
  chooses between them based on whether the user has a referrer.
• Batch path — site/src/Problems/PointsUser.php, makeNewReferrerID() (lines 251–258)
  and recordNewUsers() (lines 260–282). Back-fills users who exist in #__users but have
  no referreid (the admin "Users sync" action).

The two implementations have already drifted:

• The plugin sanitises the username (str_replace([' ', ',', "'"], '-', ...)) in username
  mode; the batch path does not.
• The batch path only ever uses prefix_selfregister, never prefix_referralregister
  (acceptable, since the sync has no referrer context — but it is a silent divergence).

3.2 The prefix is cosmetic, not functional

The two prefix options are write-only: they are read once at generation time and baked
into the referreid string, then never read back. No code parses, matches, filters, or
branches on the prefix. Its only effect is human readability (distinguishing self-registered
vs referred users in admin lists, exports, and URLs). The referraluser column already
records whether a user was referred, so the prefix duplicates that signal in a non-queryable
form.

3.3 Username mode (referralIDtype = 'u') leaks the login identifier

In 'u' mode the ID is strtoupper(prefix . username). The Joomla username is half of the
login credential, and the referreid is public in multiple places:

• Referral links (?referrer=<referreid>).
• The public site Users list — show_referreid_cols defaults to 1 (site/views/Users/tmpl/default.xml, line 35).
• The REST user-search endpoint, which echoes username - referreid (admin/install/plugins/restful-userpoints/userpoints.php, findUsers(), lines 204–219).
• CSV exports.

So a username-based referral link literally contains the user's login username in the URL.
This is both a privacy issue (a stable personal identifier tied to a public token) and a
security issue (free account enumeration / credential-harvesting aid).

3.4 There is no uniqueness guarantee

The random generator uses uniqid($prefix, true):

• uniqid() is time-based plus a per-process LCG suffix — not a CSPRNG, and PHP's own
  documentation warns it cannot guarantee uniqueness.
• Crucially, even a perfect generator would not be enough: referreid has only a plain
  (non-unique) INDEX in all three tables. The only UNIQUE key on the main table is userid
  (admin/sql/install.mysql.utf8.sql, line 19). Nothing in the database prevents two users
  from receiving the same referreid.

A collision is not harmless, because referreid is a join key:

• #__alpha_userpoints_details joins on referreid, so two users' transactions would mix.
• The #__alpha_userpoints_combined view does SUM(...) GROUP BY referreid, collapsing two
  users' totals into one row.
• referraluser stores the referrer's referreid, so an invitation token would resolve to
  two users (the "ambiguous invitation" case).

3.5 referreid is load-bearing

referreid is the foreign key of the whole points system, not a display label:

• _details / _details_archive carry only referreid (no userid).
• The combined view (install/install.script.php, lines 198–204) joins and groups on it.
• The referral chain (referraluser → referrees) is keyed on it (PointsUser::removeReferral(), lines 142–156).
• Session/cookie state and URLs carry it (Session::get('referrerid') compared in the account template; raffle ?referrer= links).

3.6 Sync SQL status

The sync (UsersControl::usersync() → Users::Synchro() → PointsUser::recordNewUsers())
is already batched (a single multi-row INSERT), but the ID is generated in a PHP loop
via uniqid() before the insert, and there is a second statement (plus a second
select/insert pair for welcome points). There is no per-row SQL execution, but the ID
generation is not atomic with the insert.

4. Decisions

D1 — Retire the username ('u') option

Decision (recommended): Discontinue username-based IDs.

• Remove the u option from admin/config.xml and delete the username branch from both
  generators.
• Random is already the default ("r"), so this is a low-risk removal with no dependency on
  the ID format anywhere in the code.

Consequences: new users get random IDs only. Existing username-based IDs remain (see
§6) — they still resolve correctly because the ID is treated as an opaque string.

D2 — Consolidate to a single generator

Decision (recommended): One shared function produces the ID; the plugin and the sync both
call it. This removes the existing drift (sanitisation, prefix handling) and gives the
uniqueness/format logic exactly one home.

D3 — ID source: CSPRNG in PHP, not SQL

Decision (recommended): Generate with PHP's random_bytes(), e.g.
strtoupper(bin2hex(random_bytes(16))), optionally prefixed.

Rationale, given the MariaDB-dominant constraint:

• MariaDB has no native CSPRNG SQL function. RANDOM_BYTES() is MySQL 8.0.17+ only, so a
  SQL-side CSPRNG is effectively unavailable to the dominant platform.
• The only portable SQL generator is UUID(), which returns a v1 UUID (timestamp +
  server node). v1 UUIDs are predictable/ordered and leak the server node and creation time;
  they are not unguessable. UUID_SHORT() is worse (a 64-bit time+server_id integer).
• random_bytes(16) gives 128 bits of unguessable entropy, is already an accepted dependency
  of this component (admin/views/Levelrank/tmpl/default_levelrank.php uses
  bin2hex(random_bytes(16))), and works identically on every supported PHP version
  (PHP 7.0+, which Joomla 4/5 already require).

Consequence: the ID is generated in PHP rather than SQL. This is the correct trade-off given
the platform constraint and the desire for an unguessable public token.

D4 — Enforce uniqueness with a UNIQUE index + retry

Decision (recommended): Add UNIQUE KEY on #__alpha_userpoints.referreid (the main
table only — the _details tables legitimately hold many rows per referreid and must stay
non-unique), and wrap generation in a retry-on-duplicate-key loop.

Caveats:

• The migration must first run a duplicate-detection query
  (SELECT referreid, COUNT(*) ... GROUP BY referreid HAVING COUNT(*) > 1) and surface any
  pre-existing collisions rather than letting ALTER TABLE ... ADD UNIQUE fail.
• The single 'GUEST' sentinel row (userid = 0, one row) is compatible with a unique
  index.

D5 — Sync as a single, set-based statement

Decision (recommended): Keep the sync as one batched INSERT, but generate the IDs with
the shared CSPRNG helper (replacing uniqid). The missing-user selection already uses a
LEFT JOIN ... WHERE referreid IS NULL, which is idempotent and safe to re-run.

A pure INSERT ... SELECT with SQL-generated IDs is only achievable via UUID() on
MariaDB, which D3 rejects on security grounds; the PHP-loop-over-IDs cost is negligible
because it builds one statement, not N statements.

D6 — Prefix: keep as an optional cosmetic tag, or drop

Decision (open, leans toward keep-but-simplify): The prefix has no functional purpose
(§3.2). Options:

1. Keep as an optional cosmetic prefix on the random string (PREFIX + <32 hex>), and
   document that it is cosmetic and write-only.
2. Drop the prefix options entirely, leaving a bare random ID.

Recommendation: keep for now (removing config options is a visible breaking change for
little gain), but clearly document that it is cosmetic and does not retroactively apply.

5. Target design (recommended)

Single source of truth for a new ID:

// site/src/Problems/... (shared helper, used by both plugin and sync)
$id = strtoupper($prefix . bin2hex(random_bytes(16)));   // 32 hex chars + prefix

• Store in referreid (varchar(160)) — 32 chars + up to a short prefix fits comfortably.
• UNIQUE KEY on #__alpha_userpoints.referreid.
• Insert with a retry loop on duplicate-key error (regenerate and retry; effectively never
  happens with 128-bit random, but it is the actual guarantee).
• Sync (recordNewUsers) uses the same helper, one batched INSERT, and the existing
  idempotent WHERE referreid IS NULL selection.
• Plugin (onUserAfterSave) uses the same helper; referral-vs-self prefix selection stays
  in the plugin (only it has the referrer context).

6. Rollout / migration

• Do not re-key existing IDs. Existing referreid values (both random and any historical
  username-based ones) stay frozen; all referral links continue to work.
• Adding the UNIQUE index is backward-compatible but must be preceded by a
  duplicate-detection check on existing installs.
• Retiring 'u' stops new username IDs; old ones are grandfathered.
• Consider a note in the admin help text (currently admin/help/en-GB/screen.userpoints.html
  mentions a "7 characters maximum" for the prefix) clarifying that prefix/type changes apply
  only to future registrations.

7. Open questions

1. Should the referreid column also be hidden from the public Users list by default
   (change show_referreid_cols default from 1 to 0), given the privacy direction?
2. Should the REST findUsers endpoint stop echoing username alongside referreid?
3. Is unguessability (anti-enumeration) a hard requirement, or is uniqueness alone
   sufficient? D3 assumes unguessability is desirable for a public token.
4. Do we keep the prefix options (D6), and if so, do we need any length/character
   validation?