Skip to content

Database Schema

All tables are prefixed with the WordPress table prefix (e.g. wp_arr_logs).

ER Diagram

erDiagram
    WP_POSTS {
        bigint ID PK
        string post_type "shop_subscription"
    }
    WP_USERS {
        bigint ID PK
    }

    arr_logs {
        bigint id PK
        bigint subscription_id FK
        decimal arr_old
        decimal arr_new
        datetime date_changed
        bigint changed_by FK
    }
    arr_reasons {
        int id PK
        varchar name
    }
    arr_contexts {
        int id PK
        varchar name
    }
    arr_log_reasons {
        bigint id PK
        bigint log_id FK
        int reason_id FK
    }
    arr_log_contexts {
        bigint id PK
        bigint log_id FK
        int context_id FK
    }

    WP_POSTS ||--o{ arr_logs : "subscription_id"
    WP_USERS ||--o{ arr_logs : "changed_by"
    arr_logs ||--o{ arr_log_reasons : "log_id"
    arr_logs ||--o{ arr_log_contexts : "log_id"
    arr_reasons ||--o{ arr_log_reasons : "reason_id"
    arr_contexts ||--o{ arr_log_contexts : "context_id"

Database views

Two read-only views are created by Migration_1_0_0 for convenience:

View Joins
view_arr_reasons arr_logs + arr_log_reasons + arr_reasons — one row per log/reason combination
view_arr_contexts arr_logs + arr_log_contexts + arr_contexts — one row per log/context combination

Table reference

arr_logs

Stores every ARR change event for a subscription.

Column Type Description
id bigint UNSIGNED Primary key
subscription_id bigint UNSIGNED WooCommerce subscription post ID
arr_old decimal(50,2) ARR before the change
arr_new decimal(50,2) ARR after the change
date_changed datetime Timestamp of the change
changed_by bigint WordPress user ID (nullable)

arr_reasons

Lookup table of named reasons for an ARR change.

arr_contexts

Lookup table of request contexts (e.g. wp-admin, web, wc-api).

arr_log_reasons / arr_log_contexts

Pivot tables linking a log entry to one or more reasons/contexts.