Database Tables
This document describes the database tables created by the TSJ Subscriptions plugin.
Core Tables
jc_subscriptions
Stores subscription-specific data extending WooCommerce subscriptions.
| Column | Type | Description |
|---|---|---|
| subscription_id | bigint(20) | Primary key, references WooCommerce subscription |
| start_issue_number | bigint(20) | Starting issue number |
| expiry_issue_number | bigint(20) | Expiry issue number |
| copies_owed | int | Number of copies owed |
| deferred_amount | decimal(5,2) | Deferred income amount |
| includes_current_issue | boolean | Whether includes current issue |
| shipping_soundex | varchar(64) | Soundex code for shipping address |
| shipping_address_key | varchar(256) | Key for shipping address |
| current_expiry_group_entry_id | bigint(20) | Current expiry group entry ID |
| initial_entitlements | int | Initial entitlements |
| initial_entitlement_amount | decimal(5,2) | Initial entitlement amount |
| renewal_entitlements | int | Renewal entitlements |
| renewal_entitlement_amount | decimal(5,2) | Renewal entitlement amount |
| issue_shipping_basis | varchar(12) | Issue shipping basis |
| gift_notification_date | date | Gift notification date |
| duplicate_checked | boolean | Whether checked for duplicates |
| benefactor_subscription | boolean | Whether benefactor subscription |
| sku | varchar(64) | SKU |
| dubious | boolean | Whether subscription is dubious |
jc_subscription_issues
Tracks issues for each subscription.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| issue_id | bigint(20) | Issue ID |
| subscription_id | bigint(20) | Subscription ID |
| earned_incremental | decimal(5,2) | Earned incremental amount |
| status | varchar(30) | Status (awaiting_shipment, needs_reshipping, shipped, reshipped) |
| earnings_processed | boolean | Whether earnings processed |
| first_issue | boolean | Whether first issue |
| first_renewal_issue | boolean | Whether first renewal issue |
| shipping_batches | json | Shipping batches data |
jc_expiry_groups
Manages subscription expiry groups.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| type | varchar(8) | Group type |
| issue_id | bigint(20) | Issue ID |
| absolute_issue_number | int | Absolute issue number |
| month | varchar(8) | Month |
jc_expiry_group_entries
Links subscriptions to expiry groups.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| subscription_id | bigint(20) | Subscription ID |
| expiry_group_id | bigint(20) | Expiry group ID |
| date_added | datetime | Date added |
| date_updated | datetime | Date updated |
| expiry_status | varchar(64) | Expiry status |
| next_expiry_group_id | bigint(20) | Next expiry group ID |
| new_subscription_id | bigint(20) | New subscription ID |
| first_renewal | tinyint | Whether first renewal |
Premium Features Tables
jc_premium_entitlements
Tracks premium entitlements.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| subscription_id | bigint(20) | Subscription ID |
| order_id | bigint(20) | Order ID |
| order_type | varchar(16) | Order type |
| gifted | boolean | Whether gifted |
| entitlement_number | int | Entitlement number |
| selected_product_id | bigint | Selected product ID |
| selected_variation_id | bigint | Selected variation ID |
| date_bought | datetime | Date bought |
| deferred_amount | decimal(5,2) | Deferred amount |
| redemption_date | datetime | Redemption date |
| redemption_order_id | bigint(20) | Redemption order ID |
| earned_income | decimal(5,2) | Earned income |
| redemption_gift | varchar(1024) | Redemption gift |
| notes | varchar(1024) | Notes |
jc_premium_gifts
Stores premium gift information.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| entitlement_number | int | Entitlement number |
| gift_name | varchar(64) | Gift name |
| product_id | bigint | Product ID |
| simple_product | boolean | Whether simple product |
Activity and Reporting Tables
jc_activities
Tracks system activities.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| datetime | datetime | Activity datetime |
| user_id | bigint(20) | User ID |
| item_id | bigint(20) | Item ID |
| type | varchar(64) | Activity type |
| category | varchar(64) | Activity category |
| supplemental | JSON | Supplemental data |
jc_expired_monthly_earnings
Tracks expired monthly earnings.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| subscription_id | bigint(20) | Subscription ID |
| date_earned | datetime | Date earned |
| earned_income | decimal(5,2) | Earned income |
jc_reports
Stores report information.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| date_created | datetime | Date created |
| report_type | varchar(64) | Report type |
| context | JSON | Report context |
| downloads | JSON | Download information |
| status | varchar(32) | Report status |
jc_async_reports
Stores asynchronous report information.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| title | varchar(128) | Report title |
| created_by | bigint(20) | Created by user ID |
| date_created | datetime | Date created |
| report_type | varchar(64) | Report type |
| status | varchar(32) | Report status |
| context | JSON | Report context |
| steps_processed | int | Steps processed |
| total_steps | int | Total steps |
| downloads | JSON | Download information |
| processing_step | int | Current processing step |
| step_start_time | int | Step start time |
| step_end_time | int | Step end time |
jc_renewal_statistics
Stores renewal statistics.
| Column | Type | Description |
|---|---|---|
| id | bigint(20) | Primary key |
| report_id | bigint(20) | Report ID |
| expiry_group_id | bigint(20) | Expiry group ID |
| conversion_renewal | varchar(16) | Conversion renewal type |
| type | varchar(16) | Type |
| level | varchar(16) | Level |
| sku | varchar(64) | SKU |
| self_gift | varchar(16) | Self/gift status |
| ar_manual | varchar(16) | AR manual status |
| start_size | int | Start size |
| cancelled | int | Cancelled count |
| merged | int | Merged count |
| extended_by_merge | int | Extended by merge count |
| manually_adjusted | int | Manually adjusted count |
| renewed | int | Renewed count |
| renewed_by_merge | int | Renewed by merge count |
| renewed_by_resubscribe | int | Renewed by resubscribe count |
| awaiting_renewal | int | Awaiting renewal count |
| pending_cancellation | int | Pending cancellation count |
| expired | int | Expired count |
duplicate_log
Tracks duplicate subscription checks.
| Column | Type | Description |
|---|---|---|
| candidate_id | bigint(20) | Primary key |
| dupe_key | varchar(128) | Duplicate key |
| status | varchar(40) | Status |
| subscription_id | bigint(20) | Subscription ID |
| duplicate_id | bigint(20) | Duplicate ID |
| updated | datetime | Last updated |