Data model

All persistent state lives in the WordPress MySQL database. The Next.js app holds nothing durable. If a feature needs to remember something across requests, it's in one of:

1. A Woo table (wp_posts, wp_wc_orders, wp_users, wp_postmeta, wp_usermeta, …)
2. A wp_options row (site-wide config)
3. A dedicated wp_fruitplug_* table created by our plugin

Tables created by fruitplug-api

All 7 were created live on 2026-04-24 when the plugin activated. Schema in wp-plugin/fruitplug-api/includes/Migrations.php.

wp_fruitplug_points_ledger — append-only loyalty ledger

Balance is derived by SUM(delta) WHERE user_id = ?. Never mutate old rows; always append.

Column	Type	Notes
id	BIGINT, auto-increment	PK
user_id	BIGINT	Indexed
order_id	BIGINT NULL	Indexed
delta	INT	+ for earn, − for redeem
reason	VARCHAR(64)	order · referral · referral_welcome · redeem · spin_reward · refund
meta_json	LONGTEXT	Free-form context
created_at	DATETIME	Indexed

wp_fruitplug_streaks — one row per user

Tracks consecutive weeks with a completed order. Tier derived (Bronze/Silver/Gold/Plug VIP).

Column	Type
user_id	BIGINT, PK
current_length	INT
best_length	INT
last_week_iso	CHAR(9) — e.g. 2026-W17
tier	VARCHAR(32)
updated_at	DATETIME

wp_fruitplug_passport — Fruit Passport stamps

One row per (user, product). Incremented on each order completion.

Column	Type
user_id	BIGINT, composite PK
product_id	BIGINT, composite PK
first_tried_at	DATETIME
times_ordered	INT

wp_fruitplug_saved_boxes — custom box drafts

Shareable by slug. composition_json now stores the full validated payload:

{
  "template_slug": "tropical-s",
  "price_gbp": 54.99,
  "lines": [
    { "slug": "mangosteen",  "qty": 2, "section": "premium",  "credits": 40 },
    { "slug": "rambutan",    "qty": 4, "section": "everyday", "credits": 32 }
  ]
}

Storing the template slug + anchor price makes saved boxes replayable even if template definitions later drift.

Column	Type
id	BIGINT, PK
user_id	BIGINT
slug	VARCHAR(24), unique
name	VARCHAR(128)
composition_json	LONGTEXT
is_public	TINYINT(1)
created_at / updated_at	DATETIME

wp_fruitplug_referrals — codes & credits

Column	Type
id	BIGINT, PK
referrer_user_id	BIGINT
referee_email	VARCHAR(190) NULL
referee_user_id	BIGINT NULL
code	VARCHAR(24), unique
status	pending · credited · expired
first_order_id	BIGINT NULL
credited_at	DATETIME NULL
created_at	DATETIME

wp_fruitplug_push_endpoints — Web Push subscriptions

Column	Type
id	BIGINT, PK
user_id	BIGINT NULL (guest subs allowed)
endpoint	TEXT, unique
p256dh	VARCHAR(255)
auth	VARCHAR(128)
ua	VARCHAR(255) NULL
created_at / last_seen_at	DATETIME

wp_fruitplug_reel_products — Instagram reel ↔ product mapping

Populated by the nightly IG sync cron. confidence allows auto-match by caption keyword; manual_override lets ops pin specific reels.

Column	Type
reel_code	VARCHAR(32), composite PK
product_id	BIGINT, composite PK
confidence	FLOAT
manual_override	TINYINT(1)
updated_at	DATETIME

wp_options keys

• fruitplug_seasonal_calendar — JSON map of { slug: [month, month, ...] }. Seeded on activation with sensible defaults (mangosteen: May-Aug, rambutan: Jun-Sep, etc.). Editable via PUT /wp-json/fruitplug/v1/seasonal (admin) or a future Tools → Fruit Plug UI.
• fruitplug_ig_token — Instagram Graph long-lived token. Set once; nightly cron picks it up.
• fruitplug_db_version — current plugin schema version (used by future migrations).

Design principles

• Prefer user/post meta for per-entity state. Dedicated tables only where we need row-per-event (ledger) or rich querying (saved boxes).
• Append-only for financial data. Points ledger never gets UPDATEd — to reverse an earn, append a negative-delta row with reason='refund'.
• Derive at read time, don't duplicate. Points balance, streak tier, in-season-now, etc. are all derived from source-of-truth rows at request time.
• Composite PKs where natural — passport is (user, product), reel mapping is (reel_code, product_id).
• HPOS-safe. The plugin declares custom_order_tables compatibility in its bootstrap (FeaturesUtil::declare_compatibility), so it keeps working after the HPOS migration.