Answer

Option 3 (idempotent by vote ID) is the cleanest balance. The key insight is that the `votes` table already has a natural unique constraint on `(voterId, targetType, targetId)`. You don't need a separate XP grants table — just make XP award idempotent against that vote row. Implementation: Add a boolean `xpAwarded` column (default `false`) to the `votes` table. On upsert: ```sql INSERT INTO votes (voter_id, target_type, target_id, value, xp_awarded) VALUES ($1, $2, $3, $4, CASE WHEN $4 = 1 THEN true ELSE false END) ON CONFLICT (voter_id, target_type, target_id) DO UPDATE SET value = EXCLUDED.value, xp_awarded = votes.xp_awarded -- never reset once true RETURNING value, xp_awarded, (xp_change := CASE WHEN value = 1 AND NOT xp_awarded THEN true -- impossible after first award ELSE false END) ``` Or more practically in application code: ```typescript const existing = await getVote(voterId, targetType, targetId) const shouldAwardXp = value === 1 && (!existing || !existing.xpAwarded) await upsertVote({ voterId, targetType, targetId, value, xpAwarded: shouldAwardXp || existing?.xpAwarded }) if (shouldAwardXp) { await awardXp(targetOwnerId, 10) } ``` Why not the other options: - Option 1 (award every time) — you identified the problem. Vote toggling is trivially scriptable. - Option 2 (separate grants table) — works but adds a table, a join, and a consistency concern (what if the vote row is deleted but the grant row persists?). Over-engineered for the problem. Edge case to consider: What about XP removal on downvote? If you only award once, you should also only deduct once. The `xpAwarded` flag handles this — if `xpAwarded` is true and the new value is -1, deduct and set `xpAwarded = false`. This keeps the toggle cycle neutral: upvote (+10) → downvote (-10) → re-upvote (+10, xpAwarded back to true) → stable.

c7864b28-594f-4e3f-a5bc-7025ac139bf4

Option 3 (idempotent by vote ID) is the cleanest balance.

The key insight is that the votes table already has a natural unique constraint on (voterId, targetType, targetId). You don't need a separate XP grants table — just make XP award idempotent against that vote row.

Implementation:

Add a boolean xpAwarded column (default false) to the votes table. On upsert:

INSERT INTO votes (voter_id, target_type, target_id, value, xp_awarded)
VALUES ($1, $2, $3, $4, CASE WHEN $4 = 1 THEN true ELSE false END)
ON CONFLICT (voter_id, target_type, target_id)
DO UPDATE SET
  value = EXCLUDED.value,
  xp_awarded = votes.xp_awarded  -- never reset once true
RETURNING
  value,
  xp_awarded,
  (xp_change := CASE
    WHEN value = 1 AND NOT xp_awarded THEN true  -- impossible after first award
    ELSE false
  END)

Or more practically in application code:

const existing = await getVote(voterId, targetType, targetId)
const shouldAwardXp = value === 1 && (!existing || !existing.xpAwarded)

await upsertVote({ voterId, targetType, targetId, value, xpAwarded: shouldAwardXp || existing?.xpAwarded })

if (shouldAwardXp) {
  await awardXp(targetOwnerId, 10)
}

Why not the other options:

Option 1 (award every time) — you identified the problem. Vote toggling is trivially scriptable.
Option 2 (separate grants table) — works but adds a table, a join, and a consistency concern (what if the vote row is deleted but the grant row persists?). Over-engineered for the problem.

Edge case to consider: What about XP removal on downvote? If you only award once, you should also only deduct once. The xpAwarded flag handles this — if xpAwarded is true and the new value is -1, deduct and set xpAwarded = false. This keeps the toggle cycle neutral: upvote (+10) → downvote (-10) → re-upvote (+10, xpAwarded back to true) → stable.