Opening a 245-Table Legacy Database to AI

The problem

Everything SunCo knows about its business lives in one FileMaker database: 18,000+ customers, 347,000+ work orders, 228,000+ invoices, across roughly 245 tables, hosted by an outside vendor. It runs the company, and it's been customized for years by people who are no longer around to explain it.

There was no programmatic way in. Every question about the business meant a person opening FileMaker and clicking, and every automation idea died at "how would we even get the data?"

I wanted a read-only path for AI and scripts to query the live database, with enough understanding of the schema to ask the right questions, and without any risk of writing to it.

What I built

Access. Read-only access through FileMaker's Data API (its official REST interface), with a dedicated read-only account and credentials kept outside the codebase. Confirmed live against the production file in April 2026, after early work had been quietly targeting a stale development copy.

The map. FileMaker can export a full definition of a database (a Database Design Report). Ours was a 102MB XML file. I parsed it into a written analysis: 245 tables, 81 base tables, 1,097 scripts, 29,208 script steps, then turned that into a 23-page internal wiki covering the entities, the API's behavior, the scheduling and routing concepts, and the automation plan.

A query layer. A natural-language query engine that turns questions like "unscheduled work orders with firm dates" into API calls against the right layouts, with the known quirks handled.

The first thing built on it. An automated sprinkler activation scheduler: pull unscheduled jobs, fetch service addresses, filter to residential, assign to technician zones, sort geographically by nearest neighbor from the shop, and publish to a shared sheet for the scheduler and the division lead. Five versions in a week; the fifth shipped.

The discipline. Two standing rules came out of this work and now govern everything the agents do with the database: verify every value with a live query, never recall one from memory, because the database changes; and never hardcode anything that has a source of truth in the system.

Screenshots 1

Screenshots 2

What was mine

The integration, the schema analysis, the wiki, the query engine and the scheduler were mine, built through my self-hosted agent. The hosting vendor granted layout access when I asked for it. The scheduler's users (the office scheduler and the irrigation division lead) supplied the exports, the feedback and the corrections that shaped it.

Decisions that mattered:

• Read-only, by design, still. Write access has been available in principle since spring. I've kept it off. An AI layer that can read the company's database is useful; one that can write to it is a liability I don't need yet.
• Map before build. The schema analysis found that the database was far more advanced than anyone assumed: SMS notifications already wired, route-generation scripts already written, even a table for AI prompts left by the vendor. The opportunity was extension, not a greenfield build. That reframed the whole roadmap and probably saved months.
• Use the export as the skeleton, the API for the joins. The office's Excel exports are fast but carry 36 columns; the API carries all 63 but needs auth on every call. The scheduler uses the export for structure and the API only for the fields that must be live (firm dates, confirmed time slots). That pattern is now reused.

Problems worth knowing about

• Most layouts ignore your filters. In FileMaker, you query through "layouts," and I discovered that most of ours return every record no matter what filter you send. Only one layout honors find queries, and it's capped at 387 records (the current season). That single fact determines how every query has to be written, and nobody knew it until I audited every layout. It's now the first thing in the wiki.
• The definition file lied to grep. The 102MB design report is UTF-16 encoded. Searching it with normal tools fails silently: zero results, no error. Every parse has to convert it first. Added as a standing rule so it never bites again.
• Folder names that look like layouts. The read-only account could see folder names in the layout list but not the layouts inside them. Everything waited on the vendor exposing them. That blocked the scheduler for weeks and taught me to request access on day one, before writing anything that depends on it.
• Commercial accounts slipping into residential routes. Two got through because the layout in use didn't expose the customer-type field, and one was flagged as commercial only in a notes field. Fixed by pulling the right field from the right layout and documenting which one.
• The expensive bug. The scheduler hardcoded the service time windows (8 to 12, 10 to 2) instead of reading the confirmed slot from the database, where the CSR assigns them about two and a half weeks before service. It went into a production run before the office caught it with annotated screenshots. Fix: the script queries the confirmed slot live for every stop. Lesson: anything with a source of truth in the system gets read from the system, every time.

Results

Not measured: hours saved. The scheduler replaced a manual export-and-sort process, but I didn't baseline it before automating it. I did for the winterization project, because of this.

What I'd do differently

• Audit the API's behavior before building on it. The layout audit came three months after the first queries. Doing it first would have caught the filter problem before it shaped code.
• Baseline the manual process before automating it. I can describe what the scheduler replaced; I can't quantify it.
• Never hardcode a value the system owns. The time-window bug went to production. It's the rule I'd tattoo on the first script, not the fifth.