About four months ago, a client I'd been doing maintenance work for came back with a new request. They were running a small property management operation — a few hundred rental units, tracked in a MySQL database I'd originally built for them two years prior. The request sounded simple: "We just want a dashboard where we can see monthly payment summaries, outstanding balances, and unit occupancy at a glance."
It wasn't simple. It never is. The schema had accreted two years of business logic, nullable columns that weren't supposed to be nullable, and a payments table that had been retrofitted three times. But the scope was still internal tooling — no public traffic, no complex auth, just a secure admin panel on shared cPanel hosting. The kind of project I've done a dozen times.
What was different this time is that I used Claude as an active coding partner throughout the build, not just for one-off questions. I want to be honest about that experience — not to sell you on AI-assisted development, but to give you the specific breakdown of where it helped, where it failed, and one place where it nearly caused a real problem in production.
What the Project Actually Looked Like
Before I talk about the AI collaboration, you need to understand the technical context. This is a shared cPanel hosting environment running PHP 8.1 and MySQL 5.7. No Composer in production. No SSH-accessible queue workers. No Redis. The stack is vanilla PHP, vanilla JS, a tiny amount of GSAP for micro-animations, and raw MySQL queries — no ORM.
The existing database had four key tables relevant to this dashboard: units, tenants, lease_agreements, and payments. The payments table had columns like amount_due, amount_paid, payment_date, status (an ENUM: 'pending', 'partial', 'paid', 'overdue'), and a notes TEXT column that had been used inconsistently for years.
The deliverable was a single-page dashboard with three panels: a monthly payment summary (with month-over-month comparison), an outstanding balances table, and a unit occupancy rate display. Plus a CSV export for the accountant.
Before: My Old Internal Tool Workflow
My previous approach to internal tooling was methodical but slow. I'd spend the first day mapping the schema, writing out all the queries by hand in a scratch file, testing each one in phpMyAdmin, then slowly wiring them into PHP. I'm not a slow developer, but aggregation queries across multiple joined tables — especially when you're dealing with weird nulls and mixed status logic — eat time. A lot of it.
The CSV export was always the last thing I'd do, and I'd always underestimate it. Encoding issues, column ordering, the accountant wanting something slightly different from what I'd built. Another half-day gone.
Total time for a project like this: realistically three to four full working days.
After: What the AI Collaboration Actually Looked Like
This time I opened a Claude conversation and front-loaded it with real context. I pasted the CREATE TABLE statements for all four tables. I included five sample rows from payments (anonymised). I described the hosting constraints. I told it what version of MySQL I was on.
Then I asked for the monthly payment summary query first.
The first output was close but wrong in an important way. It used DATE_FORMAT(payment_date, '%Y-%m') for grouping, which works — but it was grouping on payment_date instead of the month the payment was due, which meant payments received late were appearing in the wrong month's summary. That's a business logic error, not a syntax error. Claude had no way to know that was the intent unless I told it.
When I corrected it and explained the distinction, the revised query came back accurate:
SELECT
DATE_FORMAT(p.due_date, '%Y-%m') AS month,
SUM(p.amount_due) AS total_due,
SUM(p.amount_paid) AS total_collected,
SUM(p.amount_due - p.amount_paid) AS total_outstanding,
COUNT(CASE WHEN p.status = 'paid' THEN 1 END) AS fully_paid_count,
COUNT(CASE WHEN p.status IN ('partial', 'overdue', 'pending') THEN 1 END) AS not_paid_count
FROM payments p
WHERE p.due_date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 6 MONTH), '%Y-%m-01')
GROUP BY DATE_FORMAT(p.due_date, '%Y-%m')
ORDER BY month DESC;That took maybe twenty minutes total — iteration included. Doing it from scratch would have been closer to ninety.
Where It Genuinely Saved Me
The CSV Export Utility
This was the clearest win of the entire project. I gave Claude the query result shape and told it I needed a PHP function that would stream a CSV to the browser without writing a temp file to disk (shared hosting, limited /tmp reliability). What I got back was clean, handled UTF-8 BOM correctly for Excel compatibility, and included proper header escaping. I have written this function from memory more times than I care to count. This time I didn't have to.
The Occupancy Rate Logic
Occupancy sounds simple — active leases divided by total units — but the client's data model had a wrinkle. Units could be "under maintenance" with a boolean flag, and those should be excluded from the denominator. I described this, Claude incorporated it cleanly into the query, and flagged that I might also want to exclude units where is_active = 0. That was correct. I hadn't thought to mention it explicitly.
Frontend Table Rendering
The outstanding balances panel needed a sortable table with client-side filtering. I asked for vanilla JS — no libraries — and got something I'd estimate at 70% usable out of the box. I rewrote the sort logic (it wasn't handling the numeric columns correctly — was sorting them as strings), but the DOM scaffolding and event binding saved me probably an hour.
Where It Failed, and One Near-Miss I Won't Forget
The Permissions Logic Was Quietly Wrong
The dashboard had two user roles: admin and viewer. Viewers shouldn't see payment amounts — only occupancy status. I asked Claude to add role-based display logic to the PHP rendering layer.
What it returned looked correct at first glance. It was checking $_SESSION['role'] before rendering each panel. But the check was only in the display layer — the underlying AJAX endpoints that returned the payment data were completely unprotected. A viewer who knew the endpoint URL could have called it directly and received full payment data with no restriction.
I caught it during my own review pass, not during testing. If I'd shipped that, it wouldn't have been a catastrophic breach — this is an internal tool on a locked-down admin path — but it would have been a real access control failure. The lesson here isn't "AI is bad." It's that AI handles the happy path fluently and forgets the threat model. I've written about this before in the context of AI-assisted code review — the confident, plausible output is what makes it dangerous when you're tired and moving fast.
The Month-Over-Month Comparison
The client wanted to see the current month's collection rate compared to the same month last year. The query Claude generated for this worked — until you ran it in January. The year-offset logic broke at the year boundary because of how it was using MONTH() and YEAR() separately instead of doing date arithmetic properly. It wasn't wrong in a loud way. It just returned subtly incorrect numbers. That's the category of bug I genuinely find hardest to catch because the output looks reasonable.
The Honest Before / After
Before: Three to four working days. All query logic hand-written. CSV export always takes longer than expected. I'm competent at all of it but it's slow, grinding work.
After: One and a half working days. Significant time saved on query scaffolding, export utilities, and frontend boilerplate. One near-miss on access control that I caught manually. One subtle data bug in year-boundary logic that I caught during QA.
Net result: genuinely faster, with caveats. The caveats aren't small. If I hadn't been reviewing critically — if I'd been skimming instead of reading — the access control gap would have shipped.
This connects to something I've been thinking about since I wrote about using Claude to audit its own AI outputs: the output quality has gotten good enough that it requires real discipline to stay sceptical. The better the tool gets, the more tempting it is to trust it without verifying.
What I'd Do Differently Next Time
- Security logic is off-limits for delegation. I'll write all auth and permission checks myself, from scratch, and treat any AI-generated code in that area as a starting draft to be fully replaced.
- Date arithmetic gets a dedicated test suite. Even small, manual ones. Year boundaries, month-end edge cases, leap years if relevant. AI-generated date logic fails quietly at edges.
- Front-load even more schema context. The one query error I got early on (grouping by payment date vs. due date) was because I hadn't been explicit enough about business intent. More upfront context means fewer correction cycles.
- Use AI most aggressively for export and rendering utilities. This is where the time savings are real and the risks are low. Scaffolding tables, generating CSV output, writing repetitive data-mapping code — AI is fast and accurate here.
Closing Thought
Internal tools are an interesting category for AI collaboration because the stakes feel low — no public users, limited blast radius — which is exactly why it's easy to get sloppy. The access control near-miss happened partly because I was moving quickly and the context felt low-risk. That's the wrong way to think about it. Internal tools still touch real data, sometimes sensitive data, and "it's just for us" is not an excuse for weak security logic.
What I'd say to anyone considering this approach: use AI aggressively for the parts of internal tooling that are genuinely tedious — aggregation queries, export functions, table scaffolding. Stay completely hands-on for anything involving permissions, financial accuracy, or edge-case data logic. The time savings are real. So is the failure mode.
Frequently Asked Questions
Is AI actually useful for building internal tools, or does it just generate boilerplate?
It's genuinely useful for scaffolding UI components, writing repetitive query logic, and generating export utilities — but it struggles with context-specific business rules, shared hosting constraints, and any logic where the "why" matters as much as the "what." You still need to drive.
What kinds of tasks should I never outsource entirely to AI when building a database-backed tool?
Anything touching access control, row-level permissions, and financial aggregation logic. AI will produce plausible-looking code that silently ignores edge cases your data actually contains — I've shipped that mistake once and won't again.
How do you structure prompts when using AI for internal tooling work?
I give Claude explicit schema context, sample row data, the exact MySQL version I'm on, and the specific constraint I'm working around (usually shared hosting limitations). The more boring detail I front-load, the fewer corrections I need on the back end.
