Database schema¶

payments-hub creates its own 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, FAILED, CANCELING
`created_at`	`DATETIME`
`expires_at`	`DATETIME`	Provider-set expiry. For a Frisbii card, the card's expiry date
`payment_link`	`TEXT`	Redirect URL for agreement creation
`provider`	`VARCHAR(30)`	e.g. `mobilepay`, `frisbii`
`gateway`	`VARCHAR(30)`	Which gateway of the provider, e.g. `credit_card`, `paypal`
`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, CAPTURING, REFUNDING, CANCELING
`provider`	`VARCHAR(30)`
`gateway`	`VARCHAR(30)`	Which gateway of the provider
`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. For Frisbii the invoice handle — the id charges are addressed by
`ext_transaction_id`	`VARCHAR(60)`	The provider's id for the single charge attempt this row describes. Frisbii adds a transaction per attempt on the same invoice, so several rows can share `ext_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`

Meta tables¶

Each entity with meta has two tables: {entity}_meta_keys holds the key names and {entity}_meta_data the values, keyed by parent_id with a foreign key to the entity. So paymenthub_agreements_meta_keys / paymenthub_agreements_meta_data, and the same pair for paymenthub_payments.

Agreement meta is where the card import records its provenance: migrated_from, legacy_quickpay_transaction_id, card_last4 and so on. See Migrations.

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
        varchar gateway
        tinyint is_default
    }
    paymenthub_agreements_meta_data {
        int id PK
        int parent_id FK
        int meta_key_id FK
        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
        varchar ext_transaction_id
        enum status
        varchar provider
        varchar gateway
        decimal amount
    }
    paymenthub_payments_meta_data {
        int id PK
        int parent_id FK
        int meta_key_id FK
        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_agreements_meta_data : "parent_id"
    paymenthub_payments ||--o{ paymenthub_payments_meta_data : "parent_id"