ENG-003
Achievements that reward the behaviour you want
A table of conditions, a checker that runs after activity, and a unique key so an achievement can only be awarded once.
Achievements cost nothing to give and change what people do. The design question is which behaviour to reward: claims are the obvious one and the least useful, because people were doing that anyway.
Reward the things that keep a site alive — returning on consecutive days, completing a profile, referring someone who becomes active, reaching a first withdrawal. Those are the behaviours worth paying for.
Keep the conditions as data rather than code. Each row is a metric, a threshold and a reward, and adding one is an insert.
/*
CREATE TABLE achievements (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(40) NOT NULL,
name VARCHAR(120) NOT NULL,
metric VARCHAR(40) NOT NULL, -- claims, streak_days, referrals_active, withdrawals
threshold INT UNSIGNED NOT NULL,
reward_units INT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE KEY uq_code (code)
);
CREATE TABLE user_achievements (
user_id INT UNSIGNED NOT NULL,
achievement_id INT UNSIGNED NOT NULL,
awarded_at DATETIME NOT NULL,
PRIMARY KEY (user_id, achievement_id) -- awarding twice is impossible
);
*/
function check_achievements(PDO $pdo, int $userId, array $metrics): array
{
$awarded = [];
$rows = $pdo->query('SELECT * FROM achievements')->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $a) {
$have = (int) ($metrics[$a['metric']] ?? 0);
if ($have < (int) $a['threshold']) {
continue;
}
try {
$pdo->beginTransaction();
// The composite primary key does the deduplication.
$pdo->prepare('INSERT INTO user_achievements (user_id, achievement_id, awarded_at) VALUES (?, ?, NOW())')
->execute([$userId, (int) $a['id']]);
if ((int) $a['reward_units'] > 0) {
$pdo->prepare('UPDATE users SET balance = balance + ? WHERE id = ?')
->execute([(int) $a['reward_units'], $userId]);
$pdo->prepare('INSERT INTO ledger (user_id, kind, units, memo, created_at) VALUES (?, "achievement", ?, ?, NOW())')
->execute([$userId, (int) $a['reward_units'], $a['code']]);
}
$pdo->commit();
$awarded[] = $a;
} catch (PDOException $e) {
$pdo->rollBack();
if (($e->errorInfo[1] ?? 0) !== 1062) { // 1062 = already had it
throw $e;
}
}
}
return $awarded;
}Using it
Run the check after activity that could plausibly move a metric, not on every page load. Computing four aggregates on every request is a lot of database work for a badge.
Show locked achievements with their thresholds. An achievement nobody knows about changes nothing.
Keep rewards small. The badge is most of the motivation; the payout is a token.
What bites people
The composite primary key is what makes this safe. A SELECT then INSERT will double-award under two simultaneous requests.
Do not award retroactively without thinking. Adding an achievement for a metric everyone already exceeds pays out to your whole user base at once.
Streaks need a defined day boundary and a timezone. Pick UTC and say so, or users in one half of the world lose streaks at odd hours.