A Table That Exists but Is Never Populated

A design doc sat in Claude Desktop. Six steps for modularizing every Google service — Analytics, Search Console, Ads, Site Audits — into THDCRM's integration framework. Clean architecture diagram. Comprehensive spec. The kind of document that makes you feel like the work is almost done.

A previous session had already built most of it. PR #33 for the core feature, PR #35 for nine bug fixes from code review. Both merged. The question wasn't "what needs to be built" but "what's actually left?" — and that question required reading the code against the spec, line by line, to find the gap.

Five of six steps: done. Step three — Google Analytics — was 90% complete. Everything worked. You could see pageviews, sessions, bounce rates, all live from the GA4 API. But the keyword was "live." Every dashboard load hit the API. No data persisted. No historical trends. No way to see last Tuesday's traffic without asking Google in real-time. The GaDailyMetric model existed. The migration had created the table. The table was empty.

The missing piece was 110 lines of code: a syncDailyMetrics() method on GoogleAnalyticsService. It fetches GA4 daily data — pageviews, users, sessions, bounce rate, average session duration — and Search Console daily data — clicks, impressions, CTR, average position. Merges both sources by date. Upserts to the database. Named header mapping instead of positional indexing, because positional parsing of API responses is a bug waiting for Google to reorder a column.

The sync button now persists ninety days of history alongside the live dashboard refresh. A new history endpoint serves persisted data from the database with zero API calls. The scheduler runs daily at 5 AM, backfilling the last seven days for every active GA integration — lightweight enough to run unattended, thorough enough to catch gaps. The deep ninety-day sync stays manual, triggered by the sync button when someone wants the full picture.

With that, the design doc was complete. Six steps, all shipped. The integration framework supports four providers — HousecallPro, Google Analytics, Google Ads, and Site Audits — with a pattern clean enough that adding a fifth is a copy-and-adapt exercise, not an architecture decision.

The session also closed a loose end that had been sitting on the open items list: the last WordPress client site without REST API credentials. Created programmatically via WP-CLI over SSH, verified with a 200 response, config file updated. All seven sites now have working authentication.

And the 519-line CLAUDE.md — the infrastructure bible that both Claude Desktop and Claude Code need to do their work — became a symlink to iCloud Drive instead of a manually copied file. Edit from either context, iCloud keeps them in sync. A small thing that eliminates a class of "which version is current?" confusion that compounds over weeks.

The real lesson of this session is the value of the assessment phase. The design doc said six steps. The previous session had built five and a half. Without reading the code against the spec, the gap would have stayed invisible — a table that exists but is never populated, a feature that works but doesn't remember.