Part 2: Building PhysicianTechnologist - Architecture and Database Design
When your "simple blog" becomes a distributed system with vector search. The over-engineering hall of fame and architecture decisions that actually paid off.
Dr. Chad Okay
NHS Resident Doctor & Physician-Technologist
The Stack Selection: Analysis Paralysis Resolved
Choosing the tech stack with Claude Code was like having a very opinionated architect who'd built everything before. I suggested React with a simple Express backend. Claude countered with Next.js 15.4, TypeScript strict mode, Supabase for the backend, Tailwind for styling, Radix UI for components, React Query for state management, and Zod for validation.
"That seems like a lot for a blog," I said.
Claude proceeded to implement the entire stack in 30 minutes. Sometimes it's easier to just let the expert drive.
The Final Stack (and Why)
The package.json became a monument to modern web development. Next.js for SSR/SSG and API routes. React 18 because, well, React. TypeScript with strict mode because Claude absolutely refused to write JavaScript. Supabase brought PostgreSQL with auth and real-time features I'd never use. Tailwind and Radix UI made everything look professional without effort. React Query and Zod handled data fetching and validation like adults.
Then came the kitchen sink: OpenAI integration for semantic search, markdown rendering, reading time calculations ("5 min read" for every post regardless of length), and 47 other dependencies I'm still discovering.
// The "simple blog" dependency count
{
"dependencies": {
// ... 50+ packages
}
}
The Database That Grew Like a Garden
Evolution of the Schema
The database started innocently. Day 1 was a naive posts table with id, title, content, and created_at. Simple. Clean. Wrong.
By week 3, I had a monster with 8 interconnected tables. Posts grew to 12 columns. Authors exploded to 19 columns including JSONB fields for flexibility. Post notifications, content embeddings, search queries, and migration tracking tables appeared like mushrooms after rain.
The Over-Engineering Hall of Fame
The Status Workflow System
What could have been a simple boolean for published/unpublished became a complex state machine with draft, pending_review, published, and archived statuses. Plus three timestamp fields to track the journey: published_at, first_published_at (which never changes after first publish), and newsletter_sent_at (to prevent duplicate sends).
This seemed excessive until I accidentally published a draft at 2 AM. The status workflow saved me from explaining why my half-written thoughts on AI ethics were suddenly public.
Vector Embeddings for 6 Blog Posts
The crown jewel of over-engineering: implementing pgvector for semantic search on a blog with single-digit posts. I'm doing cosine similarity searches with OpenAI embeddings on six blog posts. A simple full-text search would have sufficed. Hell, Ctrl+F would have sufficed.
CREATE TABLE content_embeddings (
embedding vector(1536), -- OpenAI ada-002 dimensions
-- ... for 6 posts
);
But here's the thing - when I have 600 posts (optimistic, I know), this architecture won't need changes. The "Related Posts" feature works surprisingly well, even if it's just choosing between 5 other posts.
The Database Decisions That Paid Off
Soft Deletes Everywhere
Instead of actually deleting anything, everything gets a deleted_at timestamp. This saved me when I accidentally "deleted" the wrong author. One SQL update and they were back from the digital grave.
JSONB for Flexible Fields
Author specialties, education, and social links all use JSONB fields. No schema migrations needed when adding new social platforms or specialty types. It's like NoSQL and SQL had a baby, and it's beautiful.
Trigger-Based Automation
Database triggers automatically queue posts for embedding generation and newsletter sending when published. Even if the API fails, the database maintains consistency. It's like having a really pedantic assistant who never forgets anything.
The API Layer: Where Standards Met Reality
The Standard Response Format (A Love-Hate Story)
Claude Code insisted on standardising all API responses with success flags, data payloads, error objects with codes and messages, and metadata including timestamps and request IDs. Every. Single. Endpoint.
The good: predictable client-side handling and consistent error patterns. The bad: verbose for simple endpoints and metadata that's mostly noise. The request ID has never been used for debugging, but it's there, waiting for its moment to shine.
The Authentication Middleware Pattern
What started as simple auth checking evolved into 47 lines of token validation, role checking, session management, and refresh logic. The middleware became so complex it probably needs its own documentation. Or therapy.
The Supabase Decision: Why Not Just PostgreSQL?
What I Gained
Instant auth saved weeks of development. The built-in REST API meant direct database access from the frontend (scary but convenient). Row Level Security provided database-enforced access control. And most importantly, no DevOps required - it just worked.
What I Lost
Flexibility disappeared - you do things the Supabase way. Cost predictability went out the window as it scales with usage. Local development requires an internet connection, making plane coding impossible. And debugging RLS policies is like debugging quantum mechanics - you're never quite sure what's happening until you observe it.
The RLS Policies That Nearly Broke Me
It started simple enough: "Authors can edit own posts." Then came "Public can view published posts" and "Admins can do anything" and fifteen other policies that interact in ways I still don't fully understand.
-- The nightmare: debugging why a post won't update
-- Answer: RLS policy was checking the wrong field
-- Time to figure this out: 3 hours
Reflections on Architecture
What was done well? Type safety everywhere eliminated runtime errors. Database-first design means business logic is portable. Standardised patterns make the code maintainable, if verbose. The architecture could handle 100x growth, though 100x of zero is still zero.
What's over-engineered? Vector search for 6 posts is like using a sledgehammer for a thumbtack. Complex status workflows could have been a boolean. API metadata on every response is rarely useful. The newsletter queue system serving exactly zero subscribers.
What's under-engineered? No tests despite Claude writing test specs constantly. No monitoring, so I find out about errors from users (all three of them). No caching strategy means every request hits the database. No backup system beyond trusting Supabase completely.
The Architecture's Hidden Gem: The Migration System
The unsung hero is the migration system with self-documenting, versioned, reversible migrations. Every schema change is tracked, documented, and can be undone. This saved me during the "great author table refactor of March 2025" when I discovered that changing a primary key is harder than it looks.
CREATE TABLE schema_migrations (
version VARCHAR(255) PRIMARY KEY,
rollback_sql TEXT -- Can undo any migration!
);
Next in Part 3: I explore the Claude Code development workflow, the magic of AI-assisted coding, the moments of brilliance, the frustrating bugs, and how I went from idea to implementation in record time. Plus, the story of the 3 AM debugging session that made me question everything.
Navigate This Series
← Previous: Part 1: Discovery and Initial Learning
→ Next: Part 3: Claude Code Acceleration and Development
The Complete Series:
- •Part 1: Discovery and Initial Learning
- •Part 2: Architecture Decisions and Database Design (You are here)
- •Part 3: Claude Code Acceleration and Development
- •Part 4: Deployment, First Posts, and Future Vision
Share this article
Dr. Chad Okay
I am a London‑based NHS Resident Doctor with 8+ years' experience in primary care, emergency and intensive care medicine. I'm developing an AI‑native wearable to tackle metabolic disease. I combine bedside insight with end‑to‑end tech skills, from sensor integration to data visualisation, to deliver practical tools that extend healthy years.
