Database Schema

TrustLens uses eight 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 #

Eight tables, as enumerated in all_expected_tables_exist() in class-install.php:

1. trustlens_customers
2. trustlens_events
3. trustlens_signals
4. trustlens_category_stats
5. trustlens_fingerprints
6. trustlens_webhook_logs
7. trustlens_automation_logs
8. trustlens_velocity_events

That is the complete set. There are no separate tables for disputes, 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 scored customer. Columns:

Column	Notes
id	auto-increment
email_hash	keyed HMAC-SHA256 of email, unique
customer_id	WP user ID if available
customer_email	plaintext (NULL after GDPR erasure)
customer_type	enum: user, guest
trust_score	0–100, default 50
segment	enum: vip / trusted / normal / caution / risk / critical
Order stats
total_orders	completed order count
total_order_value	decimal
cancelled_orders	count
order_edits	count of edits — used by Orders module
Refund stats
total_refunds	count
total_refund_value	decimal
full_refunds	refunds where amount equals order total
partial_refunds	refunds smaller than order total
return_rate	computed: refunds / 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	used by review-manipulation detection
linked_accounts	integer count
linked_count	migration-added secondary count
shipping_address_hash	for linked-accounts matching
last_ip	hashed
Status / metadata
is_blocked	boolean
is_allowlisted	boolean
admin_notes	TEXT — free-form admin notes
tags	text — comma-separated or serialized tags
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, return_rate.

---

trustlens_events #

Append-only event log. One row per significant event in a customer’s history.

• id
• email_hash
• event_type — varchar; e.g. order_placed, refund_issued, coupon_applied, dispute_filed, linked_account_detected, shipping_anomaly, score_updated, blocked, etc.
• event_data — longtext column holding JSON-encoded payload (not a strict JSON column type)
• order_id
• created_at

Indexed on email_hash, event_type, order_id, created_at.

---

trustlens_signals #

Current signal breakdown per customer. Rewritten on every recalculation — this table holds only the latest signal state, not history.

• id
• email_hash
• module_id — e.g. returns, coupons, account_age
• signal_score — SMALLINT, positive or negative
• signal_reason — varchar
• created_at

Sum of signal_score values plus 50 (base) should equal the customer’s trust_score.

---

trustlens_category_stats #

Per-customer per-category return statistics for the Categories module.

• id
• email_hash
• category_slug
• orders_count
• refunds_count
• refund_value
• last_updated

---

trustlens_fingerprints #

Device, address, IP, phone, and payment-method fingerprint storage. Linked-account detection works by matching hashes here.

• id
• email_hash
• fingerprint_type — e.g. shipping_address, ip, device, phone, payment
• fingerprint_hash — keyed HMAC-SHA256 hash
• first_seen, last_seen

Linked-account detection runs by joining this table against itself on fingerprint_hash for rows with different email_hash values. The per-customer linked_accounts / linked_count columns on trustlens_customers store the precomputed aggregate.

---

trustlens_velocity_events #

Card-Testing Defense event log — decline attempts, lockouts, Panic Freeze activations.

• id
• fingerprint_hash
• event_type
• decline_code
• email_hash (when associated with a customer)
• created_at

---

trustlens_automation_logs #

Pro. Per-firing log of automation rule evaluations and action results. Note the plural logs.

• id
• rule_id
• rule_name
• email_hash
• trigger_type
• action_type
• order_id
• status — fired / skipped / failed
• duration_ms
• error_message
• created_at

---

trustlens_webhook_logs #

Pro. Outgoing webhook delivery log.

• id
• url
• payload
• response_status
• response_body (truncated)
• attempt — retry counter
• created_at

---

Where Other Concepts Live #

Concept	Storage
Disputes	Counter columns on trustlens_customers: total_disputes, disputes_won, disputes_lost. Individual dispute events are written to trustlens_events as event_type = 'dispute_filed' / dispute_status_changed.
Linked accounts	Aggregate count in linked_accounts / linked_count columns. Underlying matching uses trustlens_fingerprints.
Admin notes	admin_notes TEXT column on trustlens_customers. Notes additions are also logged to trustlens_events.
Tags	tags column on trustlens_customers
Automation rules	Stored serialized in the trustlens_automation_rules WordPress option (not a custom table).
Settings	Standard WordPress options — trustlens_* prefix.
HMAC secrets	WordPress options — never in tables.

---

Schema Versioning #

The schema version is tracked in the trustlens_db_version option. On plugin activation, class-install.php runs dbDelta() and any registered migrations. Migrations are additive — existing data is preserved across upgrades.

Current schema version is visible in TrustLens → Settings → Data.

---

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.