Database Schema
9 min read
- View as Markdown Open this page as plain Markdown (opens in a new tab)
- Open in ChatGPT Ask questions about this page (opens in a new tab)
- Open in Claude Ask questions about this page (opens in a new tab)
- Open in Google AI Studio Ask questions about this page (Google account required) (opens in a new tab)
TrustLens uses fourteen custom database tables. This page documents the schema for developers who need to query data directly, build custom reports, or troubleshoot at the database level. Schema creation lives in includes/class-install.php.
All tables use the WordPress database prefix (typically wp_) followed by trustlens_.
The Authoritative Table List #
Fourteen tables, as enumerated in all_expected_tables_exist() in class-install.php:
trustlens_customerstrustlens_eventstrustlens_refundstrustlens_signalstrustlens_category_statstrustlens_product_factstrustlens_fingerprintstrustlens_webhook_logstrustlens_automation_logstrustlens_velocity_eventstrustlens_disputestrustlens_refund_casestrustlens_refund_case_eventstrustlens_network_flags
That is the complete set. There are no separate tables for linked accounts, admin notes, automation rules, or customer meta — those concepts are represented as columns on trustlens_customers, as rows in the existing tables, or in WordPress options.
trustlens_customers #
The primary customer trust profile table. One row per customer TrustLens has seen. Columns:
| Column | Notes |
|---|---|
id |
auto-increment |
email_hash |
keyed HMAC-SHA256 of the normalized email (64 hex chars), unique |
customer_id |
WP user ID if available |
customer_email |
plaintext; NULL for records created from a hash alone (e.g. a blocked card-testing email). GDPR erasure deletes the whole row |
customer_type |
enum: user, guest |
trust_score |
0–100, default 50 |
segment |
enum: vip / trusted / normal / caution / risk / critical |
| Order stats | |
total_orders |
counted orders — completed, or refunded |
total_order_value |
decimal — lifetime value of counted orders |
cancelled_orders |
count |
order_edits |
count — not currently written by any module, so always 0 |
| Refund stats | |
total_refunds |
count of refunded orders |
total_refund_value |
decimal |
full_refunds |
refunds where amount equals order total |
partial_refunds |
refunds smaller than order total |
return_rate |
computed: refunded orders / counted orders × 100 |
last_refund_date |
datetime |
| Coupon stats | |
total_coupons_used |
count |
first_order_coupons |
count of first-order-type coupons used |
coupon_then_refund |
count of coupon-then-refund cycles |
| Chargeback stats | |
total_disputes |
count |
disputes_won |
count |
disputes_lost |
count |
| Other module stats | |
reviews_before_refund |
count — reserved; not currently written |
linked_accounts |
integer — number of linked accounts. From 1.3.23 it holds the same count as linked_count; it previously received a JSON list that MySQL stored as 0, so rows not rechecked since then may still read 0 |
linked_count |
integer — number of linked accounts; the maintained count, kept on both sides of a link and read by the Linked Accounts automation condition |
shipping_address_hash |
reserved — not currently written; linked-account matching uses trustlens_fingerprints |
last_ip |
reserved — not currently written |
| Status / metadata | |
is_blocked |
boolean |
is_allowlisted |
boolean |
is_flagged |
boolean — flagged for review |
admin_notes |
TEXT — free-form admin notes, one timestamped line per note |
tags |
text — JSON array of tag strings |
| Timestamps | |
first_order_date, last_order_date, score_updated_at |
datetime |
created_at, updated_at |
datetime, auto-managed |
Indexes include email_hash (unique), segment, trust_score, customer_id, is_blocked, is_flagged, return_rate, shipping_address_hash.
trustlens_events #
Append-only event log. One row per significant event in a customer’s history.
idemail_hashevent_type— varchar; e.g.order_created,order_counted,order_cancelled,full_refund,partial_refund,coupon_used,coupon_refund,category_refund,multi_account_coupon_abuse,address_anomaly_detected,dispute_created,dispute_closed,during_attack_window,checkout_blocked,payment_gateway_restricted,customer_blocked,customer_unblocked,customer_allowlisted,customer_allowlist_removed,customer_flagged,customer_unflagged,network_early_warning,automation_triggered, etc.event_data—longtextcolumn holding JSON-encoded payload (not a strictJSONcolumn type)order_idcreated_at
Indexed on email_hash, event_type, order_id, created_at. There’s no automatic purge — rows go only when the customer’s data is deleted or erased.
trustlens_refunds #
The refund ledger — one row per refund, unique on (source, external_refund_id) so the same refund is never counted twice.
id,source,external_refund_id,wc_refund_id,order_id,email_hashamount,currency,order_total,cumulative_refunded,refund_ratiorefund_type(full / partial),classification,reason,item_count,shipping_amountorder_status,order_delivered,order_has_coupon,days_since_ordercreated_at,updated_at,category_processed_at,automation_processed_at,deleted_at(set when a refund is deleted)
trustlens_signals #
Signal breakdown per customer. On every recalculation the customer’s current signal rows are replaced, so they hold only the latest signal state. Rows with module_id = 'score_update' are the exception: one per customer per day, holding that day’s final score as the history behind the Dashboard’s trust-score trend chart.
idemail_hashmodule_id— e.g.returns,coupons,account_age,system,score_updatesignal_score— SMALLINT, positive or negativesignal_reason— varcharcreated_at
Sum of the current signal_score values (excluding score_update rows) plus 50 (base), kept within 0–100, should equal the customer’s trust_score — unless the customer is allowlisted (pinned at 100, no signals) or a trustlens/trust_score filter changes the result.
trustlens_category_stats #
Per-customer per-category order and refund statistics for the Categories module.
idemail_hashcategory_slug(unique together withemail_hash)order_countorder_valuerefund_countrefund_valuecreated_at,updated_at
trustlens_product_facts #
Order-item snapshots used by Product Risk Intelligence — one row per order item (unique on order_id + order_item_id), with email_hash, product and variation IDs, SKU, product name, category slugs, currency, ordered and refunded quantity and value, virtual / downloadable / serial flags, and optional COGS, margin and resale value.
trustlens_fingerprints #
Address, phone, IP, payment-token and device fingerprint storage, shared by linked-account detection and Card-Testing Defense.
idemail_hash— empty for Card-Testing device rowsfingerprint_type—shipping_address,billing_address,phone,ip_address,payment_token,device; Card-Testing rows usetl_device_sessionfingerprint_hash— keyed HMAC-SHA256 hashfirst_seen,last_seen,times_seen,country_codefp_source,decline_count_24h,taint_flag,taint_reason,tainted_at— Card-Testing state
Linked-account detection runs by joining this table against itself on fingerprint_hash for rows with different email_hash values; only a shared shipping or billing address, phone or payment token links two accounts on its own. The per-customer linked_accounts / linked_count columns on trustlens_customers store the precomputed count. Fingerprints are removed 365 days after they were last seen.
trustlens_velocity_events #
Card-Testing Defense event log — checkout submissions, declines and gate blocks, keyed by fingerprint and queried by rolling window. Kept 48 hours by default (Card-Testing Defense → Event retention, 24 hours to 7 days).
idfingerprint_hashevent_typeip(binary)email_hash(when associated with a customer)decline_codeorder_idcreated_at
trustlens_automation_logs #
Pro. Per-run log of automation rule evaluations and action results. Note the plural logs. Rows older than 90 days are purged daily.
idrule_idrule_nameemail_hashtrigger_typeaction_typeorder_idstatus— ok / error / skipped / queuedduration_mserror_message— the error, or the reason for a skipcontext_json— refund details for Refund Processed rulescreated_at
trustlens_webhook_logs #
Pro. Delivery log for the global webhook endpoints (Settings → Integrations → Webhooks); automation-rule webhooks are logged in trustlens_automation_logs instead. Global deliveries are sent without waiting for a reply, so the response columns stay empty. The table is capped at the most recent 1,000 rows.
idendpoint_idendpoint_urleventpayloadresponse_code,response_body— not currently filledcreated_at
trustlens_disputes #
The chargeback worklist — one row per dispute, unique on the gateway’s dispute_id (manual entries get a generated ID): order_id, email_hash, gateway, reason, amount, currency, status, is_open, outcome, due_by, opened_at, closed_at, updated_at.
trustlens_refund_cases and trustlens_refund_case_events #
Pro. Refund Defense cases (one row per case, with risk score, recommendation, return and approval state, tracking, inspection and evidence) and their append-only audit trail (event type, event data, acting user, time).
trustlens_network_flags #
Pro. Store Trust Network flags — one row per customer group hash and reporting store, with level, reason and whether the flag is this store’s own contribution (is_local) or pulled from a peer.
Where Other Concepts Live #
| Concept | Storage |
|---|---|
| Disputes | One row per dispute in trustlens_disputes; counter columns on trustlens_customers: total_disputes, disputes_won, disputes_lost; and events in trustlens_events as event_type = 'dispute_created' (when first recorded) and 'dispute_closed' (when resolved won/lost). The order also carries _trustlens_dispute / _trustlens_dispute_data meta. |
| Linked accounts | Count in the linked_accounts / linked_count columns. Underlying matching uses trustlens_fingerprints. |
| Admin notes | admin_notes TEXT column on trustlens_customers. |
| Tags | tags column (JSON array) on trustlens_customers |
| Automation rules | Stored serialized in the trustlens_automation_rules WordPress option (not a custom table). |
| Deferred order holds (1.3.23) | Order meta _trustlens_deferred_hold — a Hold Order, Cancel Order or Require Verification waiting for payment. Removed once applied. |
| Settings | Standard WordPress options — trustlens_* prefix. |
| HMAC secrets | WordPress options — never in tables (trustlens_automation_webhook_secret for automation webhooks; per-endpoint secrets inside trustlens_webhook_endpoints). |
Schema Versioning #
The schema version is tracked in the trustlens_db_version option. On plugin activation, and whenever that option doesn’t match the plugin’s TRUSTLENS_DB_VERSION constant, class-install.php runs dbDelta() and the registered migrations. Migrations are additive — existing data is preserved across upgrades. TrustLens 1.3.23 adds no tables or columns.
The schema version isn’t shown in the admin; read the option (e.g. wp option get trustlens_db_version).
Direct SQL Examples #
Top Risk Customers #
SELECT customer_email, trust_score, segment, total_refunds, return_rate
FROM wp_trustlens_customers
WHERE segment IN ('risk', 'critical')
ORDER BY trust_score ASC
LIMIT 50;
Customers With Multiple Disputes #
SELECT customer_email, total_disputes, disputes_lost, trust_score FROM wp_trustlens_customers WHERE total_disputes >= 2 ORDER BY disputes_lost DESC;
Recent Events for a Customer #
SELECT event_type, event_data, created_at FROM wp_trustlens_events WHERE email_hash = '...' ORDER BY created_at DESC LIMIT 50;
Linked-Account Candidates by Shared Shipping Address #
SELECT a.email_hash AS hash_a, b.email_hash AS hash_b, a.fingerprint_hash FROM wp_trustlens_fingerprints a JOIN wp_trustlens_fingerprints b ON a.fingerprint_hash = b.fingerprint_hash AND a.fingerprint_type = 'shipping_address' AND b.fingerprint_type = 'shipping_address' AND a.email_hash < b.email_hash;
Direct Database Modifications #
Read queries are safe. Do not modify TrustLens tables directly except in maintenance contexts — direct writes bypass:
- Score recalculation
- Event logging
- Cache invalidation
- Action hooks (downstream automation)
Use the REST API or PHP helper functions (wstl_block_customer(), etc.) for state changes.
Updated on October 18, 2026