Data Access Anti Pattern Auditor
by @quochungto
Audit a persistence layer and schema for data access anti-patterns: N+1 query (SELECT N+1), ripple loading, lazy loading anti-pattern, ghost/proxy identity t...
Scenario A: Rails E-Commerce App with N+1 on Order Items
Trigger: Engineer reports that the orders index page takes 3β8 seconds for 100+ orders.
Process:
1. Glob app/controllers/orders_controller.rb and app/views/orders/index.html.erb.
2. Grep for .each in the view; find order.line_items.count inside the loop.
3. Confirm Order.all in the controller has no .includes(:line_items).
4. Grep app/models/order.rb for has_many :line_items β default lazy.
5. Severity: High (performance; 1+100 queries for 100 orders).
Output snippet:
### AP-01: N+1 β Order.line_items in orders#index
Location: app/views/orders/index.html.erb, line 14
Evidence: <%= order.line_items.count %> inside orders.each loop;
controller: @orders = Order.all (no .includes).
Consequence: 1 + N SQL queries (1 for orders + 1 per order for line_items).
With 200 orders: 201 queries per page load.
Remediation: Order.includes(:line_items) in controller. Or .eager_load(:line_items).
Cross-ref: lazy-load-strategy-implementer.
Scenario B: Hibernate App β Domain Has Inheritance, AR-Style Mapping
Trigger: "Our User entity has a to_domain() method and the mapper is constantly wrong."
Process:
1. Read User.java β finds User extends BaseEntity (AR style) with toUserDomain() method.
2. Read schema β users table has a type discriminator column; subtypes AdminUser,
GuestUser have different behavior.
3. Confirm @Entity on User with @Inheritance(strategy = SINGLE_TABLE) β STI, not AR.
4. The toUserDomain() method converts User to a separate UserDomain POJO β isomorphism broken.
5. Severity: High (mismatch: domain has inheritance + behavior; AR fighting the ORM).
Output snippet:
### AP-02: AR/DM Mismatch β User domain inheritance vs flat ActiveRecord
Location: src/persistence/User.java, toUserDomain() at line 87
Evidence: User uses AR-style @Entity but contains toUserDomain() conversion;
schema has type discriminator for AdminUser/GuestUser subtypes.
Consequence: Every domain operation requires manual conversion; schema changes force
dual updates to entity and domain class; tests require DB for all domain logic.
Remediation: Replace with proper Data Mapper (JPA Mapper or manual Mapper class) that
maps User table β AdminUser/GuestUser domain objects. Cross-ref: data-source-pattern-selector.
Scenario C: PostgreSQL System β Customer Preferences in JSONB, Queried Frequently
Trigger: "We're adding a filter UI for customer preferences and it's slow."
Process:
1. Read schema β customers.preferences column is JSONB.
2. Grep SQL files β find WHERE preferences->>'theme' = 'dark' and WHERE preferences->>'notifications' = 'email'.
3. Grep migrations β find a migration that renames a JSON key inside preferences.
4. Severity: Medium-High (query-loss: querying inside LOB; versioning trap evident).
Output snippet:
### AP-03: Serialized LOB Overuse β customers.preferences
Location: db/migrations/20240301_create_customers.sql (column def),
src/queries/customer_filters.sql (WHERE clause at line 12)
Evidence: customers.preferences JSONB column filtered via ->>'theme' and
->>'notifications' operators; migration 20240512 renames notif_type β notifications.
Consequence: SQL cannot efficiently filter JSONB without expression index; adding
expression indexes is schema-within-schema churn; past migration renamed a JSON key
(versioning trap confirmed).
Remediation: Extract theme VARCHAR(20), notifications_channel VARCHAR(20) as
real columns; retain preferences JSONB only for content never filtered directly.
Cross-ref: object-relational-structural-mapping-guide.
clawhub install bookforge-data-access-anti-pattern-auditor