Build SePay webhook with PHP

CREATE DATABASE sepay_webhook CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
USE sepay_webhook;
 
CREATE TABLE transactions (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    sepay_id        BIGINT NOT NULL UNIQUE,
    gateway         VARCHAR(100) NOT NULL,
    transaction_date DATETIME NOT NULL,
    account_number  VARCHAR(100),
    sub_account     VARCHAR(250),
    code            VARCHAR(250),
    amount_in       BIGINT NOT NULL DEFAULT 0,
    amount_out      BIGINT NOT NULL DEFAULT 0,
    accumulated     BIGINT NOT NULL DEFAULT 0,
    content         TEXT,
    reference_code  VARCHAR(255),
    body            JSON NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_account (account_number, transaction_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# /etc/nginx/fastcgi_params or systemd env
SEPAY_WEBHOOK_SECRET=<your_secret_key>

<?php
// Log errors, don't display to client
ini_set('log_errors', '1');
ini_set('display_errors', '0');
 
header('Content-Type: application/json');
 
$pdo = new PDO(
    'mysql:host=localhost;dbname=sepay_webhook;charset=utf8mb4',
    'db_user',
    'db_password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);
 
try {
    // 1. Read raw body, must be the original bytes to match the signature
    $body = file_get_contents('php://input');
    if ($body === false || $body === '') {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Empty body']);
        exit;
    }
 
    // 2. Verify HMAC-SHA256
    $signature = $_SERVER['HTTP_X_SEPAY_SIGNATURE'] ?? '';
    $timestamp = (int) ($_SERVER['HTTP_X_SEPAY_TIMESTAMP'] ?? 0);
    $secret    = getenv('SEPAY_WEBHOOK_SECRET');
 
    // Anti-replay: reject timestamps off by more than 5 minutes
    if (abs(time() - $timestamp) > 300) {
        http_response_code(401);
        echo json_encode(['success' => false, 'message' => 'Request expired']);
        exit;
    }
 
    $expected = 'sha256=' . hash_hmac('sha256', $timestamp . '.' . $body, $secret);
    if (!hash_equals($expected, $signature)) {
        http_response_code(401);
        echo json_encode(['success' => false, 'message' => 'Invalid signature']);
        exit;
    }
 
    // 3. Parse JSON
    $data = json_decode($body, true);
    if (!is_array($data) || empty($data['id'])) {
        http_response_code(400);
        echo json_encode(['success' => false, 'message' => 'Invalid payload']);
        exit;
    }
 
    // 4. INSERT IGNORE: race-safe dedup at DB level
    $sql = 'INSERT IGNORE INTO transactions
        (sepay_id, gateway, transaction_date, account_number, sub_account,
         code, amount_in, amount_out, accumulated, content, reference_code, body)
        VALUES (:id, :gateway, :date, :account, :sub,
                :code, :in, :out, :accumulated, :content, :ref, :body)';
 
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        'id'          => $data['id'],
        'gateway'     => $data['gateway'],
        'date'        => $data['transactionDate'],
        'account'     => $data['accountNumber'],
        'sub'         => $data['subAccount'] ?? '',
        'code'        => $data['code'],
        'in'          => $data['transferType'] === 'in'  ? $data['transferAmount'] : 0,
        'out'         => $data['transferType'] === 'out' ? $data['transferAmount'] : 0,
        'accumulated' => $data['accumulated'] ?? 0,
        'content'     => $data['content'],
        'ref'         => $data['referenceCode'] ?? '',
        'body'        => $body,
    ]);
 
    if ($stmt->rowCount() === 0) {
        // rowCount = 0 means already processed. Return OK so SePay doesn't retry.
        echo json_encode(['success' => true]);
        exit;
    }
 
    // 5. Business logic: runs only once per transaction
    if ($data['transferType'] === 'in' && !empty($data['code'])) {
        // Example: update orders
        $update = $pdo->prepare(
            "UPDATE orders SET status = 'paid', paid_at = NOW()
             WHERE code = :code AND status = 'pending' AND amount <= :amount"
        );
        $update->execute([
            'code'   => $data['code'],
            'amount' => $data['transferAmount'],
        ]);
 
        // TODO: enqueue jobs to send email, update stock, etc.
    }
 
    echo json_encode(['success' => true]);
 
} catch (Throwable $e) {
    error_log('SePay webhook error: ' . $e->getMessage());
    http_response_code(500);
    echo json_encode(['success' => false, 'message' => 'Internal error']);
}

SELECT * FROM transactions ORDER BY id DESC LIMIT 5;