Author: Smin Rana

  • MySQL RANGE Partition

    MySQL RANGE Partition

    MySQL RANGE Partition

    When partition doesn’t help

    • A lot of relations in the table.
    • A small table and won’t grow in the future.
    • Don’t use a partition unless you have 1M rows.

    It’s tempting to believe that partition will solve performance problems. But it is so often wrong. Partitioning splits up one table into several smaller tables. But table size is rarely a performance issue. Instead, I/O time and indexes are the issues.

    What is Partitioning?

    Partitioning is a physical database design technique that many data modelers and DBAs are quite familiar with. Although partitioning can be used to accomplish a number of various objectives, the main goal is to reduce the amount of data read for particular SQL operations so that overall response time is reduced.

    1. Horizontal Partitioning – this form of partitioning segments table rows so that distinct groups of physical row-based datasets are formed that can be addressed individually (one partition) or collectively (one-to-all partitions). All columns defined to a table are found in each set of partitions so no actual table attributes are missing. An example of horizontal partitioning might be a table that contains ten years worth of historical invoice data being partitioned into ten distinct partitions, where each partition contains a single year’s worth of data
      1. Range – this partitioning mode allows a DBA to specify various ranges for which data is assigned. For example, a DBA may create a partitioned table that is segmented by three partitions that contain data for the 1980’s, 1990’s, and everything beyond and including the year 2000.
      2. Hash – this partitioning mode allows a DBA to separate data based on a computed hash key that is defined on one or more table columns, with the end goal being an equal distribution of values among partitions. For example, a DBA may create a partitioned table that has ten partitions that are based on the table’s primary key.
      3. Key – a special form of Hash where MySQL guarantees even distribution of data through a system-generated hash key.
      4. List – this partitioning mode allows a DBA to segment data based on a pre-defined list of values that the DBA specifies. For example, a DBA may create a partitioned table that contains three partitions based on the years 2004, 2005, and 2006.
      5. Composite – this final partitioning mode allows a DBA to perform sub-partitioning where a table is initially partitioned by, for example range partitioning, but then each partition is segmented even further by another method (for example, hash).
    2. Vertical Partitioning – this partitioning scheme is traditionally used to reduce the width of a target table by splitting a table vertically so that only certain columns are included in a particular dataset, with each partition including all rows. An example of vertical partitioning might be a table that contains a number of very wide text or BLOB columns that aren’t addressed often being broken into two tables that has the most referenced columns in one table and the seldom-referenced text or BLOB data in another.

    Why the Range partition is the best choice in my case

    CREATE TABLE `HISTORY` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `symbol` varchar(16) NOT NULL,
      `open` float DEFAULT NULL,
      `high` float DEFAULT NULL,
      `low` float DEFAULT NULL,
      `close` float DEFAULT NULL,
      `volume` double DEFAULT '0',
      `exchange` varchar(8) NOT NULL,
      `timestamp` int(16) NOT NULL DEFAULT '0',
      `date` varchar(16) DEFAULT NULL,
      `update_date_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      PRIMARY KEY (`id`),
      KEY `symbol` (`symbol`),
      KEY `exchange` (`exchange`),
      KEY `timestamp` (`timestamp`)
    ) ENGINE=InnoDB AUTO_INCREMENT=144753037 DEFAULT CHARSET=utf8;

    My table structure is very simple. We will store Citigroup Inc daily stock data. Example dataset in CSV.
    Since I have another 30K different company stock data like Citygroup and my total rows of this table is 128,947,460 at this moment. A Range partition is perfect in this case.

    I will partition the table by year, like all the 2018 data in a partition called ‘p_2018’ and for the year 2019 it is ‘p_2019’.

    My primary key is ID which is AUTO_INCREMENT. I have to find the ID of the first trading day of the next year 2019 (1st January), then I can create a partition for all 2018 data. ID 56839003 is the first trading of the year 2019.

    PARTITION p_2018 VALUES less than (56839003)

    Now p_2018 has all the data from 2018, we can create another partition for 2019, 2020, and so on.

    How my existing SQL query gonna affected

    None of your SQL needs to be changed.
    But you can take advantage of the partition by adding your partition name in your SQL SELECT, INSERT queries.

    SELECT * FROM  `HISTORY` PARTITION (`p_2018`) WHERE  `symbol`='C';

    PARTITION SQLs

    SHOW PARTITION

    SHOW CREATE TABLE HISTORY

    ADD PARTITION

    ALTER TABLE `HISTORY` 
    
    PARTITION BY RANGE(`id`) 
    
    (
    
     PARTITION p_2018 VALUES less than (56839003),
    
     PARTITION p_2019 VALUES less than (61466903),
    
     PARTITION p_others VALUES LESS THAN MAXVALUE
    
    );

    DROP PARTITION

    ALTER TABLE t1 DISCARD PARTITION p_2018, p_2019 TABLESPACE;

    DELETE PARTITION

    All partitions

    ALTER TABLE t1 REMOVE PARTITIONING;

    One Partition

    ALTER TABLE t1 DROP PARTITION p_2018, p_2019;

    IMPORT PARTITION

    Import Tablespace Partition

    ALTER TABLE t1 IMPORT PARTITION p_2018, p_2019 TABLESPACE;

    REORGANIZE PARTITION

    ALTER TABLE SIGNALS 
    REORGANIZE PARTITION p_others INTO (
        PARTITION p_2018 VALUES less than (91466903),
        PARTITION p_2019 VALUES LESS THAN MAXVALUE
    );

    SELECT PARTITION

    Rows of each partition using information_schema

    SELECT PARTITION_ORDINAL_POSITION, TABLE_ROWS, PARTITION_METHOD FROM informatio_schema.PARTITIONS WHERE TABLE_SCHEMA = 'table' AND TABLE_NAME = 'HISTORY';

    Few things to remember

    • Duplicate your database before you try any partitions.
    • You have to find your partition method. RANGE partition may not be suitable in your case.
    • Run the same query before and after the partition and compare the time it takes.

    Spread the love
  • Decode Apple Receipt

    Decode Apple Receipt

    Decode Apple Receipt

    Verify with the server-side click here

    /**
     * ***********************************************************************
     *  SMINRANA CONFIDENTIAL
     *   __________________
     *
     * Copyright 2020  SMINRANA
     * All Rights Reserved.
     *
     * NOTICE:  All information contained herein is, and remains
     * the property of SMINRANA and its suppliers,
     * if any.  The intellectual and technical concepts contained
     * herein are proprietary to SMINRANA
     * and its suppliers and may be covered by U.S. and Foreign Patents,
     * patents in process, and are protected by trade secret or copyright law.
     * Dissemination of this information or reproduction of this material
     * is strictly forbidden unless prior written permission is obtained
     * from SMINRANA.
     * www.sminrana.com
     *
     */
    
    import SwiftUI
    import StoreKit
    import Combine
    
    class AppStorageManager: NSObject, ObservableObject, SKProductsRequestDelegate, SKPaymentTransactionObserver {
    
        @AppStorage("username") var username: String = ""
        @AppStorage("password") var password: String = ""
        
        override init() {
            super.init()
            
            SKPaymentQueue.default().add(self)
        }
        
        @Published var products = [SKProduct]()
        
        func getProdcut(indetifiers: [String]) {
            print("Start requesting products ...")
            let request = SKProductsRequest(productIdentifiers: Set(indetifiers))
            request.delegate = self
            request.start()
        }
        
    
        // SKProductsRequestDelegate
    
        func productsRequest(_ request: SKProductsRequest, didReceive response: SKProductsResponse) {
            print("Did receive response \(response.products)")
                    
            if !response.products.isEmpty {
                for fetchedProduct in response.products {
                    DispatchQueue.main.async {
                        self.products.append(fetchedProduct)
                    }
                }
            }
            
            for invalidIdentifier in response.invalidProductIdentifiers {
                print("Invalid identifiers found: \(invalidIdentifier)")
            }
        }
        
        func request(_ request: SKRequest, didFailWithError error: Error) {
            print("Request did fail: \(error)")
        }
        
    
        // Transaction
        
        @Published var transactionState: SKPaymentTransactionState?
        
        func purchaseProduct(product: SKProduct) {
            if SKPaymentQueue.canMakePayments() {
                let payment = SKPayment(product: product)
                SKPaymentQueue.default().add(payment)
            } else {
                print("User can't make payment.")
            }
        }
    
        func restorePurchase() {
            SKPaymentQueue.default().restoreCompletedTransactions()
        }
        
        struct PaymentReceiptResponseModel: Codable {
            var status: Int
            var email: String?
            var password: String?
            var message: String?
        }
        
        // SKPaymentTransactionObserver
    
    
        // This gets called when transaction purchased by user
        func paymentQueue(_ queue: SKPaymentQueue, updatedTransactions transactions: [SKPaymentTransaction]) {
            for transaction in transactions {
                switch transaction.transactionState {
                case .purchasing:
                    self.transactionState = .purchasing
                case .purchased:
                    print("===============Purchased================")
                    UserDefaults.standard.setValue(true, forKey: transaction.payment.productIdentifier)
    
                    if let appStoreReceiptURL = Bundle.main.appStoreReceiptURL,
                        FileManager.default.fileExists(atPath: appStoreReceiptURL.path) {
    
                        do {
                            let receiptData = try Data(contentsOf: appStoreReceiptURL, options: .alwaysMapped)
                            let receiptString = receiptData.base64EncodedString(options: [])
                            
                           // Send receiptString to server for further verification
                        }
                        catch { print("Couldn't read receipt data with error: " + error.localizedDescription) }
                    }
    
                case .restored:
                    UserDefaults.standard.setValue(true, forKey: transaction.payment.productIdentifier)
    
                    queue.finishTransaction(transaction)
                    print("==================RESTORED State=============")
                    self.transactionState = .restored
                case .failed, .deferred:
                    print("Payment Queue Error: \(String(describing: transaction.error))")
                    queue.finishTransaction(transaction)
                    self.transactionState = .failed
                default:
                    print(">>>> something else")
                    queue.finishTransaction(transaction)
                }
            }
        }
        
        // This gets called when a transaction restored by user
        func paymentQueueRestoreCompletedTransactionsFinished(_ queue: SKPaymentQueue) {
            print("===============Restored================")
            if let appStoreReceiptURL = Bundle.main.appStoreReceiptURL,
                FileManager.default.fileExists(atPath: appStoreReceiptURL.path) {
    
                do {
                    let receiptData = try Data(contentsOf: appStoreReceiptURL, options: .alwaysMapped)
                    let receiptString = receiptData.base64EncodedString(options: [])
                    
                    // Send receiptString to server for further verification
                }
                catch { print("Couldn't read receipt data with error: " + error.localizedDescription) }
            }
        }
        
      
    }
    
    Spread the love
  • App Store Server Notifications V2 in Laravel (Step-by-Step Guide + PHP JWT Example)

    App Store Server Notifications V2 in Laravel (Step-by-Step Guide + PHP JWT Example)

    App Store Server Notifications

    App Store Server Notifications V2 allows Apple to notify your backend in real time about subscription events such as renewals, cancellations, refunds, and billing issues. In this guide, you’ll learn how to decode, verify, and process App Store Server Notifications V2 using PHP and Laravel, with practical examples and best practices for production use.

    App offering subscription based products must use App Store Server Notification to verify the purchase, renew the subscription, cancel the subscription, and more.

    What are App Store Server Notifications?

    App Store Server Notifications are webhooks sent by Apple to your server whenever a significant event happens to an in-app purchase or auto-renewable subscription.

    Common events include:

    • Subscription renewal
    • Cancellation or expiration
    • Refunds and revocations
    • Billing retry failures
    • Price increases

    With Server Notifications V2, Apple sends a signed payload (JWT) that contains detailed transaction and renewal information, allowing your backend to stay in sync without relying solely on client-side validation.

    Why Use Version 2 (vs V1)?

    Apple introduced Server Notifications V2 to replace V1 with a more secure, structured, and extensible format.

    Key advantages of V2:

    • Uses JWT (JSON Web Tokens) for payload security
    • Provides richer transaction and renewal data
    • Supports App Store Server API integration
    • Better future compatibility with Apple’s subscription system

    If you’re building or maintaining a modern subscription-based app, V2 is the recommended and future-proof choice.

    How Apple Server Notifications Work
    The notification flow looks like this:

    1. A subscription event occurs on the App Store
    2. Apple sends a POST request to your webhook endpoint
    3. The request contains a signedPayload
    4. Your server:
      • Decodes the JWT
      • Verifies the signature
      • Extracts transaction and renewal data
    5. Your backend updates the user subscription status accordingly

    This process ensures your server remains the source of truth for subscription state.

    My Intentions

    My main goal is to retrieve the originalTransactionId from the notification data. Then, when a subscription expires or Is Refunded, it will downgrade the user’s subscription status.

    Related reading

    Step-by-Step Implementation (without library)

    If you prefer full control, you can implement Server Notifications V2 without any third-party libraries.

    High-level steps:

    1. Read the raw POST body from Apple
    2. Extract the signedPayload
    3. Split the JWT into header, payload, and signature
    4. Base64-decode the payload
    5. Parse and process the JSON data

    This approach is useful for:

    • Learning how V2 works internally
    • Minimal dependencies
    • Custom verification logic

    However, you must be careful with signature verification and edge cases.

    Implementation with JWT Library

    Using a JWT library simplifies decoding and validation while reducing security risks.

    Typical steps:

    1. Install a trusted JWT library
    2. Decode the signedPayload
    3. Validate the JWT signature using Apple’s public key
    4. Extract:
      • notificationType
      • subtype
      • transactionInfo
      • renewalInfo

    This method is recommended for most production systems because it is safer, cleaner, and easier to maintain.

    App Store Configuration

    User purchases we verify Apple’s receipt and we save originalTransactionId in our database. So each user has originalTransactionId and we can find the user with this originalTransactionId. You can read more about this here Auto-renewable subscriptions with SwiftUI

    Make sure you set your app store notification URL on the AppStoreConnect. Choose Version 2 Notifications.

    App Store Server Notifications  V2 with JWT, PHP and Laravel

    Each data will look like this, call it signedPayload.

    App Store Server Notifications  V2 with JWT, PHP and Laravel

    The signedPayload object is a JWS representation. To get the transaction and subscription renewal details from the notification payload, process the signedPayload as follows:

    1. Parse signedPayload to identify the JWS header, payload, and signature representations.
    2. Base64 URL-decode the payload to get the responseBodyV2DecodedPayload. The decoded payload contains the notificationType , subtype, other notification metadata, and a data object.
    3. The data object contains a signedTransactionInfo (JWSTransaction) and depending on the notification type, a signedRenewalInfo (JWSRenewalInfo). Parse and Base64 URL-decode these signed JWS representations to get transaction and subscription renewal details.

    Each of the signed JWS representations, signedPayloadsignedTransactionInfo, and signedRenewalInfo, have a JWS signature that you can validate on your server. Use the algorithm specified in the header’s alg parameter to validate the signature. For more information about validating signatures, see the JSON Web Signature (JWS) IETF RFC 7515 specification.

    Hopefully, you are already getting this data. Now let’s get originalTransactionId from this. We will do this without any 3rd party library first to understand the process.

    First thing, I will download the Apple root certificate and make it.PEM file from it.

    Download the certificate https://www.apple.com/certificateauthority/AppleRootCA-G3.cer

    Get .PEM file from it, on your Mac Terminal

    openssl x509 -in AppleRootCA-G3.cer -out apple_root.pem

    For testing purposes, I’m loading apple_root.pem and my signedPayload from a file called notification.json (replace it with file_get_contents(‘php://input’);) and then decoding the signedPayload. signedPayload has three parts, separated by .(dot), line 13.

    The first part is the header, the Second part is the body (payload), and the Third part is the signature. The header has an algorithm and x5c, x5c has three elements. Certificate, intermediate certificate, and root certificate. We can verify the certificate in two steps. Once the verification is completed, we know we have signedPayload from Apple.

    Finally, decode the payload again and get the originalTransactionId from lines 56 to 65.

    Server Side

    For this article, our production server URL looks like inafiz.com/jwt.php. You can get whatever Apple sends you and write a log in your server if you are interested.

    $appleData = file_get_contents('php://input');
    $file = fopen(
        "/var/www/html/appstore_prod.log", "a"
    );
    fwrite($file, $appleData);
    fclose($file);
    

    Without any 3rd party library(not recommended).

    <?php
    
    ini_set('display_errors', 1);
    error_reporting(E_ALL);
    
    // Download the certificate -> https://www.apple.com/certificateauthority/AppleRootCA-G3.cer
    // Convert it to .PEM file, run on macOS terminal ->  ```bash openssl x509 -in AppleRootCA-G3.cer -out apple_root.pem```
    
    $pem = file_get_contents('apple_root.pem');
    $data = file_get_contents('notification.json'); // replace with file_get_contents('php://input');
    $json = json_decode($data);
    
    $header_payload_secret = explode('.', $json->signedPayload);
    
    //------------------------------------------
    // Header
    //------------------------------------------
    $header = json_decode(base64_decode($header_payload_secret[0]));
    $algorithm = $header->alg;
    $x5c = $header->x5c; // array
    $certificate = $x5c[0];
    $intermediate_certificate = $x5c[1];
    $root_certificate = $x5c[2];
    
    $certificate =
          "-----BEGIN CERTIFICATE-----\n"
        . $certificate
        . "\n-----END CERTIFICATE-----";
    
    $intermediate_certificate =
          "-----BEGIN CERTIFICATE-----\n"
        . $intermediate_certificate
        . "\n-----END CERTIFICATE-----";
    
    $root_certificate =
          "-----BEGIN CERTIFICATE-----\n"
        . $root_certificate
        . "\n-----END CERTIFICATE-----";
    
    //------------------------------------------
    // Verify the notification request   
    //------------------------------------------
    if (openssl_x509_verify($intermediate_certificate, $root_certificate) != 1){ 
        echo 'Intermediate and Root certificate do not match';
        exit;
    }
    
    // Verify again with Apple root certificate
    if (openssl_x509_verify($root_certificate, $pem) == 1){
        //------------------------------------------
        // Payload
        //------------------------------------------
        // https://developer.apple.com/documentation/appstoreservernotifications/notificationtype
        // https://developer.apple.com/documentation/appstoreservernotifications/subtype
        $payload = json_decode(base64_decode($header_payload_secret[1]));
        $notificationType = $payload->notificationType;
        $subtype = $payload->subtype;
    
        if ($notificationType == "EXPIRED" || $notificationType == "REFUND") {
            $transactionInfo = $payload->data->signedTransactionInfo;
            $ti = explode('.', $transactionInfo);
            
            $data = json_decode(base64_decode($ti[1]));
            var_dump($data); // this will contain our originalTransactionId
        }
    } else {
        echo 'Header is not valid';
        exit;
    }

    Using firebase/php-jwt in composer (recommended), big difference is to use the public key to decode the payload using JWT.

    composer require firebase/php-jwt
    <?php
    
    ini_set('display_errors', 1);
    error_reporting(E_ALL);
    
    // No need these 3 lines for Laravel
    require_once './vendor/firebase/php-jwt/src/JWT.php';
    require_once './vendor/firebase/php-jwt/src/JWK.php';
    require_once './vendor/firebase/php-jwt/src/Key.php';
    
    use Firebase\JWT\JWT;
    use Firebase\JWT\Key;
    
    // Download the certificate -> https://www.apple.com/certificateauthority/AppleRootCA-G3.cer
    // Convert it to .PEM file, run on macOS terminal ->  ```bash openssl x509 -in AppleRootCA-G3.cer -out apple_root.pem```
    
    $pem = file_get_contents('apple_root.pem');
    
    $data = file_get_contents('notification.json');  // replace with file_get_contents('php://input');
    $json = json_decode($data);
    
    $header_payload_secret = explode('.', $json->signedPayload);
    
    //------------------------------------------
    // Header
    //------------------------------------------
    $header = json_decode(base64_decode($header_payload_secret[0]));
    $algorithm = $header->alg;
    $x5c = $header->x5c; // array
    $certificate = $x5c[0];
    $intermediate_certificate = $x5c[1];
    $root_certificate = $x5c[2];
    
    $certificate =
          "-----BEGIN CERTIFICATE-----\n"
        . $certificate
        . "\n-----END CERTIFICATE-----";
    
    $intermediate_certificate =
          "-----BEGIN CERTIFICATE-----\n"
        . $intermediate_certificate
        . "\n-----END CERTIFICATE-----";
    
    $root_certificate =
          "-----BEGIN CERTIFICATE-----\n"
        . $root_certificate
        . "\n-----END CERTIFICATE-----";
    
    //------------------------------------------
    // Verify the notification request   
    //------------------------------------------
    
    if (openssl_x509_verify($intermediate_certificate, $root_certificate) != 1){ 
        echo 'Intermediate and Root certificate do not match';
        exit;
    }
    
    // Verify again with Apple root certificate
    if (openssl_x509_verify($root_certificate, $pem) == 1){
        $cert_object = openssl_x509_read($certificate);
        $pkey_object = openssl_pkey_get_public($cert_object);
        $pkey_array = openssl_pkey_get_details($pkey_object);
        $publicKey = $pkey_array['key'];
    
        //------------------------------------------
        // Payload
        //------------------------------------------
        $payload = json_decode(base64_decode($header_payload_secret[1]));
        $notificationType = $payload->notificationType;
    
        //if ($notificationType == "EXPIRED" || $notificationType == "REFUND") {
            $transactionInfo = $payload->data->signedTransactionInfo;
            $signedRenewalInfo = $payload->data->signedRenewalInfo;
    
            $transactionDecodedData = JWT::decode($transactionInfo, new Key($publicKey, $algorithm));
            var_dump($transactionDecodedData->originalTransactionId);
            echo "========================================";
            $signedRenewalDecodedData = JWT::decode($signedRenewalInfo, new Key($publicKey, $algorithm));
            var_dump($signedRenewalDecodedData);
        //}
    
    } else {
        echo 'Header is not valid';
        exit;
    }

    Conclusion & Next Steps

    App Store Server Notifications V2 are essential for managing subscriptions reliably in modern iOS apps.

    By implementing them correctly in PHP and Laravel, you can:

    • Keep subscription states accurate
    • React to real-time billing events
    • Reduce reliance on client-side receipt validation

    Next steps:

    • Add App Store Server API integration
    • Store originalTransactionId as a primary key
    • Build retry-safe webhook processing
    • Automate subscription audits

    If you’d like a full Laravel package or production-ready webhook example, feel free to reach out or leave a comment

    Download the source code

    Spread the love