Skip to content

Database schema

payments-hub creates five custom tables, all prefixed {wp_prefix}paymenthub_.

Migrations are versioned and run automatically on boot via Migrator. Migration classes live in src/Core/Migrations/Versions/.

Tables

paymenthub_agreements

Stores recurring-payment agreements (mandates) with a payment provider.

Column Type Notes
id INT UNSIGNED AUTO_INCREMENT Primary key
subscription_id INT WooCommerce subscription post ID
customer_id INT WordPress user ID
ext_agreement_id VARCHAR(60) External provider agreement ID (unique)
session_id VARCHAR(100) Active session ID (cleared when agreement confirmed)
session_expires_at DATETIME Session expiry
status ENUM PENDING, ACTIVE, EXPIRED, REJECTED, CANCELED
created_at DATETIME
expires_at DATETIME Provider-set expiry
payment_link TEXT Redirect URL for agreement creation
provider VARCHAR(30) e.g. mobilepay, frisbii
is_default TINYINT(1) Whether this is the subscription's default agreement

paymenthub_payments

Tracks individual payment attempts.

Column Type Notes
id INT UNSIGNED AUTO_INCREMENT Primary key
order_id INT WooCommerce order post ID
subscription_id INT WooCommerce subscription post ID (nullable)
customer_id INT WordPress user ID (nullable)
created_at DATETIME
due_at DATETIME Scheduled charge date
status ENUM AUTHORIZED, FAILED, PENDING, CANCELED, CAPTURED, REFUNDED, PROCESSING
provider VARCHAR(30)
amount DECIMAL(10,2)
payment_link TEXT Redirect URL for payment session
needs_processing TINYINT(1) Queued for Action Scheduler processing
ext_agreement_id VARCHAR(60) Links to provider agreement
ext_payment_id VARCHAR(60) External provider payment ID
session_id VARCHAR(100) Active session ID
session_expires_at DATETIME

paymenthub_webhooks

Idempotent store for incoming provider webhooks.

Column Type Notes
id BIGINT UNSIGNED AUTO_INCREMENT Primary key
provider VARCHAR(50)
external_id VARCHAR(100) Provider event ID (unique per provider)
payload LONGTEXT Raw JSON payload
status ENUM PENDING, PROCESSED, FAILED, SKIPPED
created_at DATETIME
processed_at DATETIME
last_attempt_at DATETIME

paymenthub_agreement_meta / paymenthub_payment_meta

Key-value meta tables for Agreement and Payment entities respectively (same structure as WordPress post-meta).

ER diagram

erDiagram
    wp_posts["wp_posts (orders/subscriptions)"] {
        bigint ID PK
    }
    wp_users {
        bigint ID PK
    }
    paymenthub_agreements {
        int id PK
        int subscription_id FK
        int customer_id FK
        varchar ext_agreement_id
        varchar session_id
        enum status
        varchar provider
        tinyint is_default
    }
    paymenthub_agreement_meta {
        int meta_id PK
        int agreement_id FK
        varchar meta_key
        text meta_value
    }
    paymenthub_payments {
        int id PK
        int order_id FK
        int subscription_id FK
        int customer_id FK
        varchar ext_agreement_id
        varchar ext_payment_id
        enum status
        varchar provider
        decimal amount
    }
    paymenthub_payment_meta {
        int meta_id PK
        int payment_id FK
        varchar meta_key
        text meta_value
    }
    paymenthub_webhooks {
        bigint id PK
        varchar provider
        varchar external_id
        enum status
        longtext payload
    }

    wp_posts ||--o{ paymenthub_agreements : "subscription_id"
    wp_posts ||--o{ paymenthub_payments : "order_id / subscription_id"
    wp_users ||--o{ paymenthub_agreements : "customer_id"
    wp_users ||--o{ paymenthub_payments : "customer_id"
    paymenthub_agreements ||--o{ paymenthub_agreement_meta : "agreement_id"
    paymenthub_payments ||--o{ paymenthub_payment_meta : "payment_id"