Sherlock

Let me investigate the checkout performance. I'll start by checking the indexes on order-related tables and looking at table statistics.

sherlock -c prod-db indexes orders sherlock -c prod-db stats orders

The orders table has 2.3M rows but no index on customer_id — which is used in every checkout lookup. Sequential scans are taking ~1,200ms. Let me verify with the query plan:

sherlock -c prod-db query "EXPLAIN SELECT * FROM orders WHERE customer_id = 42"

Confirmed — it's a sequential scan. Adding an index on orders.customer_id should bring that down to under 5ms.

I'll map out the schema first to understand the relationship between orders and line items, then build the report.

sherlock -c prod-db introspect sherlock -c prod-db fk order_items

Got it — order_items joins to orders via order_id, and products via product_id. Now I can aggregate:

sherlock -c prod-db query "SELECT DATE_TRUNC('month', o.created_at) AS month, SUM(oi.quantity * oi.unit_price) AS revenue, COUNT(DISTINCT o.id) AS order_count FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.created_at >= '2025-10-01' AND o.created_at < '2026-01-01' GROUP BY month ORDER BY month"

Here's your Q4 breakdown: October did £142k across 1,847 orders, November £218k (Black Friday bump), and December £195k. November was up 53% month-over-month.

Let me look up her account and check for anything that might block checkout.

sherlock -c prod-db query "SELECT id, email, status, suspended_at, suspension_reason FROM users WHERE email = 'jane@example.com'"

Found it — her account was suspended on 2026-02-14 with reason: "payment_dispute". Let me check the audit trail:

sherlock -c prod-db query "SELECT action, details, created_at, performed_by FROM audit_log WHERE user_id = 4872 ORDER BY created_at DESC LIMIT 5"

The suspension was triggered automatically by the payment dispute system. Her PayPal dispute from order #8891 was resolved in her favour 3 days ago, but the account was never reinstated. This looks like a bug in the auto-reinstatement flow.

Welcome aboard! Let me map out the entire schema and the relationships between tables.

sherlock -c prod-db introspect sherlock -c prod-db fk orders

Here's the lay of the land. You've got 23 tables organised into four domains:

Users & Auth: users, sessions, password_resets, roles, user_roles

Products: products, categories, product_categories, inventory

Orders: orders, order_items, payments, refunds, shipping

System: audit_log, settings, migrations, jobs, notifications

The core flow is: users → orders → order_items → products. Payments link back to orders with a one-to-many (supporting split payments).

Let me check the Redis server stats and see what's going on in the cache.

sherlock -c redis-prod info sherlock -c redis-prod keys "sess:*"

Hit rate is only 60% — that's low. You've got 12,400 session keys with a 30-minute TTL, but product caches are expiring every 60 seconds. Let me check for slow operations:

sherlock -c redis-prod slowlog sherlock -c redis-prod inspect "product:catalog:all"

Found it. The slow log shows KEYS * being called every 10 seconds — that's a full keyspace scan in production with 48k keys. It's coming from the health check endpoint. The product catalog key is 4.2MB of serialised JSON being re-fetched every minute. Two fixes needed: replace KEYS with SCAN in the health check, and bump the product cache TTL to at least 5 minutes.