AI App Review · Case Study

Custom Fields the Database Couldn't Search

Someone digging through a tall unsorted pile of files while a wooden card catalog sits unused behind them

Product: CRM with customer-defined records, before its customer beta. Client: anonymous. Engagement: paid code review. Status: written up for the founder as fix before beta.

How it came up

This came from the same paid code review as the 5,000-record cutoff. The CRM let each customer define their own kinds of records, for example projects and the time entries logged against them, with whatever fields they needed.

Every field in one block of text

Each of those custom records kept all of its fields together in a single block of text, in a format called JSON. It's an easy design for an AI coding tool to produce: one column can hold any field a customer invents, and nothing about the database has to change when they add one.

The cost is that the database can't look inside that text. It could find all the records of one kind, but it couldn't filter, sort, or search on any of their fields. So the app loaded every record of that kind and sorted through them itself.

It's like having to flip through every page of a book to find something, instead of looking it up in the index. A database index is that lookup: it lets the database jump straight to the matching records.

Where it showed

Opening one project was the clearest case. To show that project's time entries and add up its hours, the app loaded every time entry in the customer's entire account, then kept the ones that belonged to this project. Search, and list views that filtered or sorted on a custom field, worked the same way.

That means the time to open one project grew with the customer's whole history, not with the size of that project. A customer three years in would wait for three years of time entries to load every time they opened a project.

The plain list of all projects was fine; it paged through the database properly. The problem was everything that needed to look inside a record.

Then it would start losing records

It got worse because of the hidden limit in the other case study: these loads stopped at about 5,000 records. Once a customer passed 5,000 time entries, a project's total hours, its related lists, and search results would be silently incomplete. Slow first, then wrong, with no error either time.

The fix

Store the fields people filter on, and especially the links between records (which project a time entry belongs to), where the database can search them directly, as separate indexed fields or a small link table. Then opening a project asks the database for that project's entries instead of loading all of them.

This is much cheaper to change before customers have years of data in it. Afterward, it's the same change plus a migration of every existing record.

Confidence: the structure and the 5,000 cutoff are certain from the code. How slow a page gets before that point is an estimate; I didn't load-test it.

Time spent: part of a three-hour code review. Status: written up for the founder before the beta.

What this says about AI-built apps

AI coding tools optimize for "it works now." Storing everything in one flexible blob works perfectly on a laptop with fifty test records, and every demo looks fast. The bill arrives with real data, months after the code was written, which is exactly when it's most expensive to fix.

Has anyone outside your team read the code?

An AI App Review is two full days in your code, by hand, then a ranked report and a 90-minute walkthrough with your team. It's $1,500, one time.

Request an AI App Review

See what the review covers