Prevent Duplicate Reward Credits: Idempotent Postbacks

How do you make a postback idempotent and prevent duplicate reward credits?

Use the transaction ID from the postback as your idempotency key, put a unique constraint on it in your database, and apply the reward and the record of that reward inside one database transaction. When a copy of the same event arrives, the database refuses the second record, your code skips the balance change, and you still answer 200 so the sender stops retrying. A reversal takes back only what you stored for that transaction, and only once.

Below: why duplicates happen, which value to use as the key, a tested MySQL and PostgreSQL schema, and complete handlers in PHP (PDO) and Node.js. For the full threat model, read Postback security, and for the basics of the signature, What is HMAC?

Why the same postback arrives more than once

A postback is a server-to-server call that reports a reward (Webhook vs postback explains how it differs from other callbacks). Any sender that retries will sometimes deliver an event twice, and a sender that never retried would lose rewards whenever your server hiccuped.

Sharklio sends a GET for every event and expects a 2xx within 6 seconds. On failure or timeout it retries up to 5 times, after about 1 minute, 5 minutes, 30 minutes, 2 hours, and 6 hours. The Postbacks docs say: “The same event can arrive twice, for example after a retry.” Common causes:

RFC 9110, section 9.2.2 calls a method idempotent “if the intended effect on the server of multiple identical requests with that method is the same as the effect for a single such request”, and says such requests can be repeated automatically when a connection fails before the response is read. GET is one of them, so clients and proxies feel free to repeat it. A postback is a GET that changes a balance: HTTP will not make it safe to repeat, so your handler has to.

Proxies do this too. The nginx proxy module docs give error timeout as the default for proxy_next_upstream and hold back only POST, LOCK and PATCH from the next server. With several app servers behind nginx, a GET postback can be replayed before Sharklio has retried anything (checked on October 22, 2026).

The idempotency key: the transaction ID

An idempotency key is the same on every copy of one operation and different for every other one. In payment APIs the client creates it: Stripe’s idempotent requests docs have you send an Idempotency-Key header so you can “safely repeat the request”. A postback already carries one. You only pick the right field:

So your key is the pair (transaction ID, entry type). The Credits and reversals page confirms nothing else can happen: “A result is credited once, and a credited result can later be reversed.” The docs fix no format for the ID, so store it as text and never parse it. Steps are explained in multi-event offers.

Step 1: a ledger table with a unique constraint

Keep a small ledger: one row per balance change, with a signed amount. The unique constraint on (transaction_id, entry) is what makes it idempotent. The examples assume a users table with a coins column of type DECIMAL(19,3), since rewards are whole numbers or have up to 3 decimals (see Rewards and currency). Never store currency in a float.

MySQL and MariaDB

CREATE TABLE reward_ledger (
  id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  transaction_id VARCHAR(191)  NOT NULL,
  entry          ENUM('credit', 'reversal') NOT NULL,
  user_id        VARCHAR(191)  NOT NULL,
  amount         DECIMAL(19,3) NOT NULL,
  event_id       VARCHAR(191)  NULL,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_tx_entry (transaction_id, entry)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Mind the collation. MySQL 8.4 uses utf8mb4_0900_ai_ci by default, and the MySQL manual says _ai and _ci mean accent- and case-insensitive. Under it, ABC and abc are duplicates: in our test the unique key failed with error 1062 on that pair. Compare IDs byte for byte with utf8mb4_bin.

PostgreSQL

CREATE TABLE reward_ledger (
  id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  transaction_id text          NOT NULL,
  entry          text          NOT NULL CHECK (entry IN ('credit', 'reversal')),
  user_id        text          NOT NULL,
  amount         numeric(19,3) NOT NULL,
  event_id       text,
  created_at     timestamptz   NOT NULL DEFAULT now(),
  CONSTRAINT uq_tx_entry UNIQUE (transaction_id, entry)
);

Adding the constraint to a live table

Find existing duplicates first, because the constraint cannot be created while they exist. Fix each one by hand (usually: keep the first row, correct the balance), then add the key:

SELECT transaction_id, entry, COUNT(*) AS copies
FROM reward_ledger
GROUP BY transaction_id, entry
HAVING COUNT(*) > 1;

MySQL (8.4 accepted this in place, without blocking writes, in our test):

ALTER TABLE reward_ledger
  ADD UNIQUE KEY uq_tx_entry (transaction_id, entry),
  ALGORITHM=INPLACE, LOCK=NONE;

PostgreSQL, without locking out writes:

CREATE UNIQUE INDEX CONCURRENTLY uq_tx_entry
  ON reward_ledger (transaction_id, entry);

The CREATE INDEX docs say CONCURRENTLY cannot run inside a transaction block and that a failed build leaves an “invalid” index to drop and rebuild. The INSERT docs add that during the build, INSERT ... ON CONFLICT may fail with a unique violation. With the handlers below, that just means a 500 and a retry.

Step 2: credit inside one database transaction

The ledger row and the balance change must succeed or fail together. Add coins and crash before the row, and the retry pays again. Write the row and crash before the coins, and the user loses the reward. Inside one transaction, both handlers do four things:

1. Lock the wallet with SELECT ... FOR UPDATE on the user’s row. Copies of one event share a user, so they queue instead of racing. It also checks the user exists.
2. Insert the ledger entry. If the unique key refuses it, this is a repeat: change nothing.
3. Change the balance, only if the insert happened.
4. Commit, then answer.

Then answer:

Sharklio does not read the response body or follow redirects, so the status code is the whole answer. Run slow work (emails, push notifications) after the commit, in the background, to stay well under 6 seconds.

Step 3: the handlers in PHP and Node.js

Both handlers read this postback URL. Add it in your dashboard under Apps, your app, Postback, with your own domain and path:

https://yoursite.com/postback?tx={transaction_id}&user={user_id}&reward={reward}&status={status}&event={event_id}&hash={hash}

The hash is the HMAC-SHA256, as lowercase hex, of transaction_id:user_id:reward:status with your postback secret key from the Integration tab (not the link hash salt). Hash the values exactly as they arrive.

PHP with PDO (MySQL or MariaDB)

<?php
// postback.php: MySQL or MariaDB with PDO
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=app;charset=utf8mb4',
    getenv('DB_USER'),
    getenv('DB_PASS'),
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
);
$secret = getenv('SHARKLIO_POSTBACK_KEY');
if (!is_string($secret) || $secret === '') {
    http_response_code(500);
    exit;
}

$p = [];
foreach (['tx', 'user', 'reward', 'status', 'event', 'hash'] as $k) {
    if (!isset($_GET[$k]) || !is_string($_GET[$k])) {
        http_response_code(403);
        exit;
    }
    $p[$k] = $_GET[$k];
}
$expected = hash_hmac('sha256', $p['tx'] . ':' . $p['user'] . ':' . $p['reward'] . ':' . $p['status'], $secret);
if (!hash_equals($expected, $p['hash'])) {
    http_response_code(403);
    exit;
}

try {
    $pdo->beginTransaction();
    if ($p['status'] === '1') {
        credit($pdo, $p);
    } elseif ($p['status'] === '2') {
        reverse($pdo, $p);
    }
    $pdo->commit();
    http_response_code(200);
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    error_log('postback ' . $p['tx'] . ': ' . $e->getMessage());
    http_response_code(500);
}

function add_entry(PDO $pdo, string $tx, string $entry, string $userId, string $amount, string $eventId): bool
{
    try {
        $pdo->prepare('INSERT INTO reward_ledger (transaction_id, entry, user_id, amount, event_id) VALUES (?, ?, ?, ?, ?)')
            ->execute([$tx, $entry, $userId, $amount, $eventId]);
        return true;
    } catch (PDOException $e) {
        if (($e->errorInfo[1] ?? 0) === 1062) {
            return false;
        }
        throw $e;
    }
}

function lock_wallet(PDO $pdo, string $userId): void
{
    $st = $pdo->prepare('SELECT coins FROM users WHERE id = ? FOR UPDATE');
    $st->execute([$userId]);
    if ($st->fetch() === false) {
        throw new RuntimeException('unknown user ' . $userId);
    }
}

function change_balance(PDO $pdo, string $userId, string $amount): void
{
    $pdo->prepare('UPDATE users SET coins = coins + ? WHERE id = ?')->execute([$amount, $userId]);
}

function credit(PDO $pdo, array $p): void
{
    lock_wallet($pdo, $p['user']);
    if (add_entry($pdo, $p['tx'], 'credit', $p['user'], $p['reward'], $p['event'])) {
        change_balance($pdo, $p['user'], $p['reward']);
    }
}

function reverse(PDO $pdo, array $p): void
{
    lock_wallet($pdo, $p['user']);
    if (add_entry($pdo, $p['tx'], 'credit', $p['user'], '0', $p['event'])) {
        add_entry($pdo, $p['tx'], 'reversal', $p['user'], '0', $p['event']);
        return;
    }
    $st = $pdo->prepare("SELECT user_id, amount FROM reward_ledger WHERE transaction_id = ? AND entry = 'credit'");
    $st->execute([$p['tx']]);
    $credit = $st->fetch(PDO::FETCH_ASSOC);
    $back = '-' . $credit['amount'];
    if (add_entry($pdo, $p['tx'], 'reversal', $credit['user_id'], $back, $p['event'])) {
        change_balance($pdo, $credit['user_id'], $back);
    }
}

• Error 1062 means “already there”. It is ER_DUP_ENTRY (SQLSTATE 23000) in MySQL and MariaDB. The InnoDB error handling page says “A duplicate-key error rolls back the SQL statement”, not the transaction, so the handler catches it and carries on.
• No INSERT IGNORE. The MySQL INSERT docs say IGNORE also turns data conversion errors into warnings and stores adjusted values. Catching 1062 ignores duplicates and nothing else.
• Amounts stay strings all the way into the DECIMAL column, never a PHP float.
• Unknown users fail loudly with a 500, so the event shows as failed in your postback report and can be resent later.

For Laravel and the signed link, see Offerwall PHP integration: Laravel and plain PHP.

Node.js with Express 5 and PostgreSQL

// postback.js: Express 5 and PostgreSQL ("type": "module" in package.json)
import crypto from 'node:crypto';
import express from 'express';
import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const app = express();
const KEYS = ['tx', 'user', 'reward', 'status', 'event', 'hash'];

function validHash(p) {
  const expected = Buffer.from(
    crypto.createHmac('sha256', process.env.SHARKLIO_POSTBACK_KEY)
      .update(`${p.tx}:${p.user}:${p.reward}:${p.status}`)
      .digest('hex')
  );
  const given = Buffer.from(p.hash);
  return given.length === expected.length && crypto.timingSafeEqual(given, expected);
}

async function addEntry(db, tx, entry, userId, amount, eventId) {
  const r = await db.query(
    `INSERT INTO reward_ledger (transaction_id, entry, user_id, amount, event_id)
     VALUES ($1, $2, $3, $4, $5)
     ON CONFLICT (transaction_id, entry) DO NOTHING`,
    [tx, entry, userId, amount, eventId]
  );
  return r.rowCount === 1;
}

async function lockWallet(db, userId) {
  const r = await db.query('SELECT coins FROM users WHERE id = $1 FOR UPDATE', [userId]);
  if (r.rowCount !== 1) throw new Error(`unknown user ${userId}`);
}

async function changeBalance(db, userId, amount) {
  await db.query('UPDATE users SET coins = coins + $1 WHERE id = $2', [amount, userId]);
}

async function credit(db, p) {
  await lockWallet(db, p.user);
  if (await addEntry(db, p.tx, 'credit', p.user, p.reward, p.event)) {
    await changeBalance(db, p.user, p.reward);
  }
}

async function reverse(db, p) {
  await lockWallet(db, p.user);
  if (await addEntry(db, p.tx, 'credit', p.user, '0', p.event)) {
    await addEntry(db, p.tx, 'reversal', p.user, '0', p.event);
    return;
  }
  const { rows } = await db.query(
    "SELECT user_id, amount FROM reward_ledger WHERE transaction_id = $1 AND entry = 'credit'",
    [p.tx]
  );
  const back = `-${rows[0].amount}`;
  if (await addEntry(db, p.tx, 'reversal', rows[0].user_id, back, p.event)) {
    await changeBalance(db, rows[0].user_id, back);
  }
}

app.get('/postback', async (req, res) => {
  const p = {};
  for (const key of KEYS) {
    if (typeof req.query[key] !== 'string') return res.sendStatus(403);
    p[key] = req.query[key];
  }
  if (!validHash(p)) return res.sendStatus(403);

  const db = await pool.connect();
  try {
    await db.query('BEGIN');
    if (p.status === '1') await credit(db, p);
    else if (p.status === '2') await reverse(db, p);
    await db.query('COMMIT');
    res.sendStatus(200);
  } catch (err) {
    await db.query('ROLLBACK');
    console.error('postback', p.tx, err.message);
    res.sendStatus(500);
  } finally {
    db.release();
  }
});

app.listen(3000);

• ON CONFLICT DO NOTHING, not a caught error. In PostgreSQL a unique violation (SQLSTATE 23505) aborts the whole transaction: in our test the next statement failed with “current transaction is aborted”. ON CONFLICT ... DO NOTHING skips the row quietly, and rowCount says whether it was inserted. With PHP and PostgreSQL, use the same clause and rowCount().
• One client per transaction. The node-postgres docs say “You must use the same client instance for all statements within a transaction”, hence pool.connect() and release() in finally.
• Errors become 500. If pool.connect() fails or the key is missing, Express 5 still answers 500: its migration guide says rejected promises in async handlers go to the error handler as if you called next(err).

For Next.js, see Offerwall postback in Node.js, Express and Next.js. Other languages: the Python, Go and C# guides.

Reversals: once, and only after a credit

A reversal (status 2, state reversed) takes back a credited result, for example after fraud or a refund. It carries the credit’s {transaction_id}, the same positive {reward} and {payout}, and a new {event_id}. The handlers follow the docs’ rules:

• Take back only what you credited, using the amount and user stored with the credit.
• Only once: the unique key allows one reversal row per transaction.
• Answer 200 even when you ignore it, or it is retried for hours.

A reversal for a credit you never stored

Events of one transaction arrive in order, and a reversal is never sent before its credit. You can still get a reversal with no credit on record: the credit failed every retry, you restored a backup, or a bug rolled it back. The docs say to ignore it.

The handlers go one step further: they insert a zero-amount credit and a zero-amount reversal. The zero credit takes the credit slot in the unique key, so a late copy of the credit is refused and pays nothing, and the zero rows record what happened.

When the user already spent the reward

The docs leave it to you: let the balance go below zero, or hold withdrawals until it is recovered, if your terms allow it. The handlers let it go negative, so make your withdrawal code refuse negative balances. Publisher earnings and reversals covers the effect on your own earnings.

Race conditions with parallel requests

The dangerous copy arrives while the first is still running. Without a constraint, two requests can interleave like this:

SELECT 1 FROM credits WHERE transaction_id = ?   -- request A: nothing found
SELECT 1 FROM credits WHERE transaction_id = ?   -- request B: nothing found
UPDATE users SET coins = coins + 315 ...         -- A pays
UPDATE users SET coins = coins + 315 ...         -- B pays again
INSERT INTO credits ...                          -- both insert, no constraint stops them

With the handlers above, the second request waits on the wallet lock, then the unique key refuses its credit row, and it commits without touching the balance. Even without the wallet lock, the unique index serializes the inserts: the second waits for the first, then gets a duplicate (MySQL) or skips the row (PostgreSQL). The PostgreSQL transaction isolation docs describe this for Read Committed, the default level.

Use the same lock order (wallet, then ledger) everywhere you touch balances, including withdrawals, and keep transactions short. If a deadlock still happens, the 500 and the retry handle it.

Step 4: test that a duplicate cannot pay twice

1. On the Integration tab, use Send a test postback in Integration status to send a credit to the user ID of a real test account (an unknown ID gets a 500 from these handlers). Test transaction IDs start with test_, and offer_id is 0.
2. In Reports, Postbacks, open the row’s Details and copy the full URL that was called.
3. Replay it 20 times at once from a terminal:

   URL='https://yoursite.com/postback?tx=test_...&user=...&reward=...&status=1&event=...&hash=...'
   seq 20 | xargs -P 20 -I{} curl -s -o /dev/null -w "%{http_code}\n" "$URL" | sort | uniq -c

   You should see 20 answers of 200, one credit row in reward_ledger, and the test balance raised once.
4. Send a test reversal. It gets its own transaction ID, so it is a reversal of something never credited: expect 200, no balance change, and two zero rows. Replay it too.
5. Delete the test_ rows and reset the test balance when you are done.

The full checklist is in How to test your app before launch. For real events that failed meanwhile, see How to resend a failed postback: a resend keeps its transaction ID, so with a unique key it is always safe.

Frequently asked questions