A PHP script to parse Ko-Fi webhook data and store it in a database

kofi-sync.php

<?php

//
// The KOFI_TOKEN value must match the verification token on your Ko-Fi account
// Database settings should be self explanatory
//

const KOFI_TOKEN = '';
const DB_HOST = '';
const DB_USER = '';
const DB_PASS = '';
const DB_NAME = '';

if (!$_POST['data'] || !$data = json_decode($_POST['data'], true)) {
    http_response_code(500);
    die();
}

if ($data['verification_token'] !== KOFI_TOKEN) {
    http_response_code(500);
    die();
}

$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
$dt = new DateTime($data['timestamp']);
$timestamp = $dt->format('Y-m-d H:i:s');

if ($data['type'] === 'Shop Order') {
    $query = "
        INSERT INTO
            orders 
            (
                message_id,
                timestamp,
                from_name,
                amount,
                email,
                currency,
                full_name,
                street_address,
                city,
                state_or_province,
                postal_code,
                country,
                country_code,
                kofi_transaction_id
            )
        VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
        )";
        
    $conn->execute_query($query, [
        $data['message_id'],
        $timestamp,
        $data['from_name'],
        $data['amount'],
        $data['email'],
        $data['currency'],
        $data['shipping']['full_name'],
        $data['shipping']['street_address'],
        $data['shipping']['city'],
        $data['shipping']['state_or_province'],
        $data['shipping']['postal_code'],
        $data['shipping']['country'],
        $data['shipping']['country_code'],
        $data['kofi_transaction_id']
    ]);

    foreach ($data['shop_items'] as $item) {
        $stmt = $conn->prepare("
            INSERT INTO
                order_items
                (
                    message_id,
                    code,
                    variation,
                    quantity
                )
            VALUES (
                ?, ?, ?, ?
            )");
    
        $stmt->bind_param(
            "ssss",
            $data['message_id'],
            $item['direct_link_code'],
            $item['variation_name'],
            $item['quantity']
        );
        
        $stmt->execute();
    };
    
    exit();
}

if ($data['type'] === 'Donation' || $data['type'] === 'Subscription') {
    $query = "
        INSERT INTO
            donations 
            (
                message_id,
                timestamp,
                type,
                from_name,
                message,
                amount,
                email,
                currency,
                kofi_transaction_id
            )
        VALUES (
            ?, ?, ?, ?, ?, ?, ?, ?, ?
        )";
        
    $conn->execute_query($query, [
        $data['message_id'],
        $timestamp,
        $data['type'],
        $data['from_name'],
        $data['message'],
        $data['amount'],
        $data['email'],
        $data['currency'],
        $data['kofi_transaction_id']
    ]);
    
    exit();
}

http_response_code(500);

schema.sql

DROP TABLE IF EXISTS `donations`;

CREATE TABLE `donations` (
  `message_id` varchar(36) NOT NULL,
  `type` set('Donation','Subscription') NOT NULL,
  `from_name` varchar(64) NOT NULL,
  `message` varchar(512) DEFAULT NULL,
  `amount` decimal(6,2) unsigned NOT NULL,
  `email` varchar(64) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `kofi_transaction_id` varchar(36) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `order_items`;

CREATE TABLE `order_items` (
  `message_id` varchar(36) NOT NULL,
  `code` varchar(10) NOT NULL,
  `variation` varchar(32) DEFAULT NULL,
  `quantity` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY (`message_id`,`code`),
  CONSTRAINT `message_id-orders` FOREIGN KEY (`message_id`) REFERENCES `orders` (`message_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

DROP TABLE IF EXISTS `orders`;

CREATE TABLE `orders` (
  `message_id` varchar(36) NOT NULL,
  `from_name` varchar(64) NOT NULL,
  `amount` decimal(6,2) unsigned NOT NULL,
  `email` varchar(64) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `kofi_transaction_id` varchar(36) NOT NULL,
  `full_name` varchar(64) NOT NULL,
  `street_address` varchar(128) NOT NULL,
  `city` varchar(32) NOT NULL,
  `state_or_province` varchar(32) NOT NULL,
  `postal_code` varchar(10) NOT NULL,
  `country` varchar(16) NOT NULL,
  `country_code` varchar(3) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;