Issues — ThunderScan

Critical

Missing FK index on orders.customer_id

Schema Quality Table: orders · Today 09:14

Performance
Critical

SQL Fix

Risk

Every JOIN from order_items to customers triggers a full table scan on orders (2.4M rows). Query latency degrades exponentially under load.

AI Impact

Text-to-SQL joins on orders → customers will timeout or return incomplete results, causing AI agent failures in production environments.

Auto-Generated SQL Fix

-- Add missing index on orders.customer_id -- Est. impact: 95% query speed improvement on order lookups CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id); -- Add FK constraint (run in a maintenance window) ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT;

View in ERD

Critical

2,341 orphaned rows in order_items

Ref. Integrity Table: order_items · Today 09:15

Data Quality
Critical

SQL Fix

Risk

2,341 rows in order_items reference order IDs that no longer exist in orders. Hard-deletes without cascades caused this. Financial reporting is likely incorrect.

Compliance

Dangling references may cause incorrect revenue calculations in SOC 2 and GDPR data-subject export requests, producing incomplete or inaccurate responses.

Auto-Generated SQL Fix

-- Step 1: Identify orphaned rows (verify count) SELECT COUNT(*) FROM order_items oi LEFT JOIN orders o ON oi.order_id = o.id WHERE o.id IS NULL; -- Expected: 2341 -- Step 2: Archive before deleting (recommended) CREATE TABLE order_items_orphaned_backup AS SELECT oi.* FROM order_items oi LEFT JOIN orders o ON oi.order_id = o.id WHERE o.id IS NULL; -- Step 3: Remove orphans DELETE FROM order_items WHERE order_id NOT IN (SELECT id FROM orders);

View in ERD

High

Unencrypted PII — customers.email, .phone

Security GDPR Art. 25 · Today 09:18

Compliance
GDPR

SQL Fix

Risk

Contact PII stored in plaintext violates GDPR Article 25 (data protection by design). Columns email, phone, and full_name require encryption-at-rest.

AI Impact

Unencrypted PII in training or retrieval contexts increases data-leak risk when these columns are used as vector embeddings or in LLM context windows.

Auto-Generated SQL Fix

-- Enable column-level encryption via pgcrypto CREATE EXTENSION IF NOT EXISTS pgcrypto; -- Encrypt existing plaintext data (backup first!) UPDATE customers SET email = pgp_sym_encrypt(email, :'ENCRYPTION_KEY'), phone = pgp_sym_encrypt(phone, :'ENCRYPTION_KEY'), full_name = pgp_sym_encrypt(full_name, :'ENCRYPTION_KEY') WHERE email IS NOT NULL;

Security Page

High

Audit logging not configured on payments

Security SOC 2 CC6.1 · Today 09:19

Compliance
SOC 2

SQL Fix

Risk

No triggers or row-level audit logging configured on payments. SOC 2 CC6.1 requires comprehensive audit trails for all financial data access and modifications. This will fail a SOC 2 Type II audit.

Auto-Generated SQL Fix

-- Create audit log table CREATE TABLE IF NOT EXISTS payments_audit ( id BIGSERIAL PRIMARY KEY, action TEXT NOT NULL, row_data JSONB, changed_by TEXT DEFAULT current_user, changed_at TIMESTAMPTZ DEFAULT now() ); -- Trigger function to capture changes CREATE OR REPLACE FUNCTION audit_payments() RETURNS TRIGGER AS $$ BEGIN INSERT INTO payments_audit(action, row_data) VALUES (TG_OP, to_jsonb(NEW)); RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_payments_audit AFTER INSERT OR UPDATE OR DELETE ON payments FOR EACH ROW EXECUTE FUNCTION audit_payments();

Security Page

High

Table sessions has no primary key

AI Readiness Table: sessions · Today 09:20

AI Readiness
Blocker

SQL Fix

Risk

Tables without a primary key cannot be reliably referenced in foreign keys or joined deterministically. This is a hard blocker for generating accurate Text-to-SQL queries on the sessions table and will cause duplicate rows in vector retrieval.

Auto-Generated SQL Fix

-- Add surrogate primary key to sessions ALTER TABLE sessions ADD COLUMN id BIGSERIAL PRIMARY KEY; -- Or promote existing unique column to PK if suitable -- ALTER TABLE sessions ADD PRIMARY KEY (session_token);

AI Readiness

Medium

3NF violation — products.category_name

Normalization Table: products · Today 09:16

Data Quality
Medium

SQL Fix

Risk

Transitive dependency: category_name depends on category_id, not on the PK. Causes update anomalies — renaming a category requires updating every product row instead of one categories row.

Auto-Generated SQL Fix

-- Extract category into its own table (3NF) CREATE TABLE categories ( id SERIAL PRIMARY KEY, name TEXT NOT NULL UNIQUE ); INSERT INTO categories(name) SELECT DISTINCT category_name FROM products; ALTER TABLE products ADD COLUMN category_id INT REFERENCES categories(id); UPDATE products p SET category_id = c.id FROM categories c WHERE p.category_name = c.name; ALTER TABLE products DROP COLUMN category_name;

Normalization

Medium

2NF violation — user_sessions.user_agent

Normalization Table: user_sessions · Today 09:16

Data Quality
Medium

SQL Fix

Risk

user_agent is a partial dependency — it depends on device_id but not the full composite key of user_sessions. Extract into a devices lookup table to fix 2NF.

View Simulation

Medium

Nullable reports.generated_at should be NOT NULL

Schema Quality Table: reports · Today 09:17

Schema
Medium

SQL Fix

Risk

generated_at is semantically required for every report but allows NULL. This creates ambiguous records and breaks chronological ordering logic in report-generation queries.

Auto-Generated SQL Fix

-- Backfill NULLs first (use created_at as fallback) UPDATE reports SET generated_at = created_at WHERE generated_at IS NULL; -- Then enforce NOT NULL ALTER TABLE reports ALTER COLUMN generated_at SET NOT NULL, ALTER COLUMN generated_at SET DEFAULT now();

Medium

4 columns use unbounded VARCHAR without length

Schema Quality Tables: users, products · Today 09:17

Schema
Medium

SQL Fix

Risk

Unbounded VARCHAR allows arbitrarily large strings, risking storage bloat and breaking API response contracts. Affects: users.username, users.bio, products.name, products.sku.

Schema Page

Low

Missing compound index on inventory(product_id, warehouse_id)

Schema Quality Table: inventory · Today 09:21

Performance
Low

SQL Fix

Detail

Inventory lookup queries filter on both product_id and warehouse_id together. A compound index would eliminate a secondary filter pass and reduce I/O.

Auto-Generated SQL Fix

CREATE INDEX CONCURRENTLY idx_inventory_product_warehouse ON inventory (product_id, warehouse_id);

Low

No rich text columns detected for embedding on products

AI Readiness Table: products · Today 09:21

AI Readiness
Low

Suggestion

The products table has no description or long-text column. Adding one would enable semantic vector search over product data, significantly improving RAG and AI catalogue search accuracy.

AI Readiness

Low

Unused index on analytics_events.session_id

Schema Quality Table: analytics_events · Today 09:22

Performance
Low

SQL Fix

Detail

Index idx_analytics_session has 0 scans in the past 30 days (pg_stat_user_indexes). Dead indexes waste storage and slow down writes. Safe to drop after confirming no ad-hoc queries rely on it.

Auto-Generated SQL Fix

-- Verify no queries use this index first SELECT idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE indexrelname = 'idx_analytics_session'; -- Drop unused index (saves ~180MB write overhead) DROP INDEX CONCURRENTLY idx_analytics_session;

All Issues