Building My Own Dashboard, Part 2: The Database Migration

The CRM was the first thing that broke. Markdown tables in a Growth Tracker file worked fine for five contacts. At fifty, they became a liability: no real queries, no joins, no filtering, every edit a manual GitHub API dance of fetch-patch-commit. The same pattern repeated across the system: wait for pain, then solve it. Postgres didn’t arrive because I planned it from day one. It arrived because the pain became real.

Planning the database up front would have added complexity I didn’t need yet, and complexity I couldn’t have judged correctly without seeing which problems were real. Every phase below was triggered by something that was already hurting.

How do you migrate without breaking things?

The migration happened in four phases over several months. Each phase moved one domain: CRM foundation, content search, marketing calendar, documents and tokens.

The key decision: I kept the repository function names exactly as they were in the markdown-era code, like getContacts, createContact and searchCrm. The callers (the API routes, the CLI and the AI tools) barely changed beyond adding await where a call used to return immediately. Nothing needed rewriting. They just started awaiting promises.

Phase 1: CRM foundation

I introduced Drizzle and Neon Postgres. Companies, contacts, opportunities, activities and tasks, previously markdown tables hand-patched via the GitHub API, became real Postgres tables with a proper repository layer.

Why it hurt: a markdown-table CRM doesn’t scale past a handful of records and has no real query capability (filtering, joins, aggregate reporting). This was the foundational move that made every later Postgres-backed feature possible.

The decision: I kept the function names the same so callers changed as little as possible. I also kept the ID namespaces, so nothing downstream had to change how it handled ids.

Phase 2: Content search off disk

The knowledge base’s /docs/* pages and search tool stopped reading content/ off disk at request time. They now read a Postgres-backed index, kept in sync by a GitHub webhook on every push and rebuildable with one command.

Why it hurt: reading disk content directly doesn’t scale as a search backend, and it doesn’t reflect edits made through the GitHub API (agent writes, one-off scripts) until the next deploy.

The decision: I deliberately kept two separate read paths. The agent’s document tools still read and write live against GitHub, because the agent needs to see its own write on the very next tool call, and the webhook delivery isn’t instant. The dashboard’s pages and ranked search go through Postgres, which is fast but can lag by however long the webhook takes to fire. The index holds one row per heading section, so a search match returns the specific section that matched.

Phase 3: Marketing calendar off markdown and Redis

The editorial calendar and its draft content, previously a markdown calendar table plus a separate Redis hash, merged into one posts table in Postgres.

Why it hurt: splitting calendar metadata from draft content meant two systems could drift. One Postgres table can’t.

Phase 4: Documents and tokens off Redis

CRM document metadata moved from Redis to a new Postgres documents table, and the files moved to private Vercel Blob storage. LinkedIn OAuth tokens moved onto encrypted Postgres storage.

Why it hurt: I’m phasing Redis out everywhere except short-lived uses like cron locks. Public blob URLs were also a real exposure: anyone with the URL could fetch a client’s file.

The decision: the Blob object’s storage pathname is never returned from the core or any API route. The document type doesn’t even have a pathname field. The only code path that can read a document is the dedicated, auth-gated download route.

Why keep two read paths for search?

The AI assistant reads and writes live against GitHub. The dashboard reads through Postgres.

The assistant needs to see its own edit on the very next tool call. The dashboard needs fast ranked search without hitting GitHub’s API. Both are true. The webhook delivers. The assistant doesn’t wait.

What was the security mistake?

I gave the AI assistant a read-only database role, scoped to the handful of tables its one SQL tool was allowed to touch. read-only, a short timeout, an explicit list. It was supposed to be a real boundary, not a convention.

Then the very first migration added a grant-by-default rule, so that any table added later would be covered automatically and nobody would have to remember. That is the part that matters. The boundary stopped being a decision anyone made and became a side effect.

By the time I caught it, the assistant could read most of the database. Not just the tables, it was supposed to. Every table where the app keeps something secret was readable.

I found it by accident, while rotating database passwords for an unrelated reason, which is the least reassuring way to find anything.

The fix removed the default grant, revoked everything, and gave back exactly what the two tools use. A new table is now unreadable until a migration deliberately grants it, and the allowlist is derived from the schema rather than hand-kept, so it can’t quietly drift.

The general lesson is the one I’d underline: a security boundary that depends on people remembering is not a boundary. Convenience defaults are how access control turns into access everything, and it will do it silently.

How did I stop content commits building?

At the time, vercel.json had an ignoreCommand that skipped rebuilding for commits that only touched content/. Machine output, weekly interview deliveries, monthly research digests and the weekly product review all commit to content/, and they triggered zero Vercel builds. The dashboard still saw new content because the webhook indexed it.

That eliminated dozens of skipped deployments every month. Changes to packages/core and root dependency files still triggered builds, but content-only commits didn’t.

I later went a step further. Automatic Git-triggered builds are now off entirely, and a deploy happens when I trigger one deliberately, with a scheduled backstop in case I forget.

What’s the pattern?

Content-first, database-second. Markdown in git until it hurts. Postgres when the pain is real. Repository names that don’t change. Two read paths where they serve different callers. Security boundaries at the database layer, not the application layer. Build rules that keep machine output from polluting the deployment pipeline.

The database migration wasn’t a single event. It was four separate phases, each solving a specific problem that had become painful. Each phase taught something that shaped the next.

The security mistake is the one I’d flag for anyone building with AI. Deciding what an assistant can and can’t touch, and making that a boundary instead of a habit, is the kind of call an Agentic AI Product Strategy engagement works through.

Next in this series: The CRM That Grew Up, and the day the contact form started feeding itself.