Payment Order Number Design and Idempotency Tables: Generation Rules and Unique Index Backstop

Payment Order Number Design and Idempotency Tables: Generation Rules and Unique Index Backstop

The order number looks like the simplest field in a payment system and is actually its foundation. Choose the generation strategy badly and the problems do not appear at launch but accumulate and surface six months later: colliding order numbers, values rejected as too long after adding a channel, auto-increment IDs leaking real transaction volume, and the same payment processed twice under concurrency.

What follows is an implementable design rather than conceptual background. For channel-specific integration detail, see WeChat Pay APIv3 integration in practice, and for the callback side, implementing a multi-channel payment callback framework.

1. Start With the Channel Constraints

Order numbers cannot be designed freely because channels impose explicit limits:

The conclusion is clear: take the intersection and use at most 32 alphanumeric characters. Any channel can then consume the same order number, and adding a channel later leaves the order system untouched. Confirm current length and character rules against each channel's official documentation.

The other distinction that must be made is between the internal order number and the channel transaction number. The internal number is generated by you, globally unique and immutable. The channel transaction number is an attribute returned by the channel and is only recorded. Conflating the two breaks the order system the moment you switch channels.

2. Five Generation Strategies Compared

The last option is the recommendation for a practical reason: it is the only one satisfying fixed length, no central dependency, rough time ordering and a clean character set at once.

A workable structure is business prefix (2) + timestamp to the second (14) + shard (2) + sequence (6) + check digits (2), 26 characters total. The business prefix makes orders readable, the timestamp provides the time ordering that helps index locality, the shard supports multi-instance deployment, the sequence resolves same-second concurrency, and the check digits catch transcription errors quickly.

Implement the sequence with Redis INCR keyed by shard plus second with an expiry. It increments within a second, resets automatically across seconds, guarantees uniqueness and never depends on a database write.

3. Building the Idempotency Table

A unique order number is only the first step; what actually prevents duplication is the idempotency table. The core idea: let a database unique index make the final decision instead of checking before writing.

CREATE TABLE `payment_idempotency` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `idem_key` varchar(128) NOT NULL COMMENT 'biz_type:order_no:event',
  `biz_type` varchar(32) NOT NULL COMMENT 'order/refund/payout',
  `order_no` varchar(32) NOT NULL,
  `channel` varchar(16) NOT NULL,
  `channel_txn_id` varchar(64) DEFAULT NULL,
  `amount` bigint NOT NULL COMMENT 'integer minor currency unit',
  `status` tinyint NOT NULL DEFAULT 0 COMMENT '0 processing, 1 success, 2 failed',
  `result` json DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_idem_key` (`idem_key`),
  KEY `idx_order_no` (`order_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The uk_idem_key index is what matters. Handling becomes:

try {
    DB::table('payment_idempotency')->insert([
        'idem_key'   => "{$bizType}:{$orderNo}:{$event}",
        'biz_type'   => $bizType,
        'order_no'   => $orderNo,
        'channel'    => $channel,
        'amount'     => $amount,
        'status'     => 0,
        'created_at' => now(),
        'updated_at' => now(),
    ]);
} catch (QueryException $e) {
    if (isDuplicateKey($e)) {
        // Already exists: this event is being or has been processed
        return true;
    }
    throw $e;
}
// First insert succeeded, continue with business logic

The value here is that concurrency is resolved by the database unique index rather than an application-level check. When two threads arrive together only one insert succeeds and the other takes the duplicate branch. That is far more reliable under load than if (!exists()) { insert(); }.

amount is bigint rather than decimal to avoid floating point error. All amounts are stored as integers in the minor currency unit.

4. State Machine and Transitions

Payment states must permit only defined transitions, with no skipping and no going back:

final class PaymentState
{
    public const PENDING  = 'pending';
    public const PAID     = 'paid';
    public const CLOSED   = 'closed';
    public const REFUNDED = 'refunded';

    private const TRANSITIONS = [
        self::PENDING  => [self::PAID, self::CLOSED],
        self::PAID     => [self::REFUNDED],
        self::CLOSED   => [],
        self::REFUNDED => [],
    ];

    public static function canTransit(string $from, string $to): bool
    {
        return in_array($to, self::TRANSITIONS[$from] ?? [], true);
    }
}

Correctness also requires a conditional update:

UPDATE orders
SET status = 'paid', paid_at = NOW()
WHERE order_no = ? AND status = 'pending';

If the affected row count is zero the state was changed by another request, and the right response is to query the channel rather than retry the update. The WHERE ... AND status = ? clause is optimistic locking, lighter than SELECT ... FOR UPDATE and far less prone to deadlocks.

5. Six Duplicate Payments That Really Happen

First, repeated callback delivery. The channel retries and your endpoint lacks idempotency, so the same order ships twice. The idempotency table covers this.

Second, callback and active query racing. After payment you query the channel while the callback arrives, and both paths book the entry. Fix by routing both paths through the same idempotency key.

Third, close-versus-pay race. You close the order while the user pays; the close fails but funds arrived. Query immediately after a failed close and trust the channel state.

Fourth, repeated user clicks. No debounce or button disabling, so the user creates two orders and pays twice. This is a product issue, but a uniqueness constraint on user, item and a time window can backstop it.

Fifth, parallel channel attempts. The same order is open in two tabs with Alipay and WeChat Pay, and the user pays both. Add a mutual exclusion so a processing order cannot be started again.

Sixth, clock rollback collisions. Snowflake without rollback handling repeats sequences. If you insist on timestamp-based generation, include a sequence component rather than a bare timestamp.

6. Launch Checklist

Order numbers are at most 32 alphanumeric characters with a business prefix and timestamp; internal order numbers and channel transaction numbers are stored separately; the idempotency table has a unique index with a key covering business type and event; amounts are integer minor units; state transitions are whitelisted and updates carry a state condition; callbacks, queries and closes share one idempotency path; multi-channel attempts on one order are mutually exclusive; reconciliation can reverse-look-up every ledger entry by order number.

For field normalization on the reconciliation side, see building a reconciliation automation script. For the reconciliation process itself, see the reconciliation and bill download handbook. For the general chain, see payment transaction flow basics. More options are collected under the Payment category.

Source: 16IDC original. Product capabilities, pricing and eligibility requirements follow the relevant providers’ official documentation and contract terms.