---
title: Database Schema
description: "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…"
url: "https://webstepper.io/docs/trustlens/developer-documentation/trustlens-database-schema/"
updated: "2026-10-18"
category: Developer Documentation
---

# Database Schema

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`:

1. `trustlens_customers`
2. `trustlens_events`
3. `trustlens_refunds`
4. `trustlens_signals`
5. `trustlens_category_stats`
6. `trustlens_product_facts`
7. `trustlens_fingerprints`
8. `trustlens_webhook_logs`
9. `trustlens_automation_logs`
10. `trustlens_velocity_events`
11. `trustlens_disputes`
12. `trustlens_refund_cases`
13. `trustlens_refund_case_events`
14. `trustlens_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.

- `id`
- `email_hash`
- `event_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` — `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`. 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_hash`
- `amount`, `currency`, `order_total`, `cumulative_refunded`, `refund_ratio`
- `refund_type` (full / partial), `classification`, `reason`, `item_count`, `shipping_amount`
- `order_status`, `order_delivered`, `order_has_coupon`, `days_since_order`
- `created_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.

- `id`
- `email_hash`
- `module_id` — e.g. `returns`, `coupons`, `account_age`, `system`, `score_update`
- `signal_score` — SMALLINT, positive or negative
- `signal_reason` — varchar
- `created_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.

- `id`
- `email_hash`
- `category_slug` (unique together with `email_hash`)
- `order_count`
- `order_value`
- `refund_count`
- `refund_value`
- `created_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.

- `id`
- `email_hash` — empty for Card-Testing device rows
- `fingerprint_type` — `shipping_address`, `billing_address`, `phone`, `ip_address`, `payment_token`, `device`; Card-Testing rows use `tl_device_session`
- `fingerprint_hash` — keyed HMAC-SHA256 hash
- `first_seen`, `last_seen`, `times_seen`, `country_code`
- `fp_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).

- `id`
- `fingerprint_hash`
- `event_type`
- `ip` (binary)
- `email_hash` (when associated with a customer)
- `decline_code`
- `order_id`
- `created_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.

- `id`
- `rule_id`
- `rule_name`
- `email_hash`
- `trigger_type`
- `action_type`
- `order_id`
- `status` — ok / error / skipped / queued
- `duration_ms`
- `error_message` — the error, or the reason for a skip
- `context_json` — refund details for Refund Processed rules
- `created_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.

- `id`
- `endpoint_id`
- `endpoint_url`
- `event`
- `payload`
- `response_code`, `response_body` — not currently filled
- `created_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.
