When we set out to build BestMedSpa.us, the brief was deceptively simple: create a place where consumers can find and compare US med spa providers by treatment, location, and reviews. What that actually required was a two-sided marketplace with automated CRM lead routing, a scored provider matching algorithm, programmatic SEO covering thousands of URLs, a production-grade admin dashboard, and a database-independent fallback system, all in a single Next.js application.
This post is a complete technical breakdown of how we built it, the architectural decisions we made, and what we learned along the way.
The Problem We Were Solving
The US medical aesthetics industry is fragmented. There are tens of thousands of licensed med spa providers across the country, but consumers have no neutral, structured place to compare them side by side. Yelp has some listings. Google Maps has others. Neither surfaces treatment-specific filtering, verified pricing intent, or structured lead intake.
On the provider side, the problem is inverted: a qualified patient who searches "laser hair removal Austin TX" is invisible to the practice unless they happen to land on the right page and manually reach out. There is no automated lead capture layer, no CRM pipeline sync, no treatment-intent matching.
We built BestMedSpa.us to solve both sides simultaneously, a consumer-facing search and comparison marketplace that invisibly routes qualified leads into provider CRM pipelines in real time.
Architecture Decision: One App, Two Audiences
The most important architectural call we made early was to build the consumer experience and the provider CRM automation inside a single Next.js 14 App Router application, rather than splitting them into separate services.
This meant:
- Consumer routes (
/,/start,/matches/[leadId],/medspas/[cityState],/clinic/[cityState]/[slug]) are statically optimized where possible and server-rendered where real-time data is needed. - Admin routes (
/admin/*) are protected behind JWT authentication and server-side rendered withforce-dynamic. - API routes (
/api/leads,/api/leads/[id]/actions,/api/admin/*) handle the CRM sync, lead matching, and admin CRUD operations. - Background scripts (CLI tools for CSV import, image fetching, review scraping, SEO content generation) run outside the Next.js process.
The single-app structure keeps infrastructure simple: one Node.js process on cPanel, one deployment pipeline, one configuration surface.
Database Design: Raw SQL, No ORM
We made an explicit decision to use raw parameterized SQL via mysql2/promise with no ORM layer (no Prisma, no Drizzle, no Sequelize). Here is why.
The lead matching query joins leads, lead_matches, clinics, clinic_services, and services in a single query with GROUP_CONCAT for service aggregation. An ORM would either generate an inefficient N+1 query or require complex relation configuration. Writing the query directly gave us full control over join order, index usage, and result shape.
The database schema has 13 tables:
clinics, 55-column provider records with scoring fields (fit_score,data_quality_score,icp_fit), social URLs, founder info, geo coordinates, and CRM metadataleads, consumer requests with treatment, location, budget, timeline, contact info, and GHL sync statelead_matches, scored, ranked provider matches per leadlead_actions, post-match consumer actions (appointment request, callback, enquiry) with GHL stage synccities/zip_codes, geography tables for SEO page generation and proximity matchingservices/clinic_services, many-to-many treatment taxonomyclaim_requests, provider-initiated profile claimsfeatured_listings, paid featured placements with date ranges and priorityclinic_instagram_posts, per-clinic social media contentadmin_users, JWT-authenticated admin accountsimport_jobs/staging_clinics, ETL pipeline tracking
Every foreign key has an explicit constraint. Every lookup column has an index. All inputs are parameterized, no string interpolation in any query.
The Provider Import Pipeline
We started with 10,333 raw med spa records in CSV format, covering providers across 50 US states. The import pipeline works in three phases:
Phase 1, Staging. Raw CSV rows are loaded into staging_clinics with their raw JSON preserved and an import_status field for row-level tracking. This gives us a recoverable audit trail for every import run.
Phase 2, Validation and normalization. Each row is normalized: company names are deduplicated by a composite key of name + city + state, slugs are generated deterministically, phone numbers are validated, Google Maps URLs are checked for format, and lat/long are extracted from coordinates.
Phase 3, Production insert. Validated rows are upserted into clinics using INSERT ... ON DUPLICATE KEY UPDATE on the (slug, city_slug, state_slug) unique key. The import_jobs table tracks total rows, inserted, updated, skipped, and any error messages per run.
After import, the import-zipcodes script rebuilds the zip_codes table from the imported clinic dataset, so ZIP lookup and geo-proximity matching stay current without a separate data source.
Lead Capture: The Wizard
The consumer entry point is a multi-step lead wizard at /start. It was deliberately designed to feel like a search tool, not a form.
Phase 1, Discover. The consumer selects treatment (from 14 categories), budget range, and timeline. Location input accepts ZIP code, city name, or full address and resolves to a LocationSuggestion with city, state, ZIP, and optional lat/long. As soon as enough inputs are filled, the wizard hits /api/matches/preview and renders a live ranked provider list, sort by best match, highest rated, most reviewed, or nearest, before asking for contact details.
Phase 2, Enquire. A four-step contact intake (name to phone with international dial code to email to notes) is gated behind the provider preview. The consumer sees who they are matching with before committing their contact details. This design consistently outperforms traditional forms that collect contact details before showing any value.
On submission, the lead record is created in MySQL, the matching algorithm runs, and GHL sync fires, all before the redirect to /matches/[leadId].
Provider Matching Algorithm
Every lead is matched to up to 8 providers. The matching logic runs a scored SQL query with multiple filter levels:
- Selected provider first, if the consumer clicked a specific clinic card before entering contact details, that clinic is always rank 1.
- ZIP match, clinics in the same ZIP code rank highest.
- City + state match, clinics in the same city score next.
- State match, state-wide fallback for sparse markets.
- Service match, clinics with the requested treatment in their
clinic_servicesjoin get a score boost. - Rating and review count, used as tiebreakers within each geographic tier.
- Featured status, featured clinics rank before unfeatured clinics at the same tier.
- Data quality score, higher-quality records rank above sparse records.
The result is a deterministic, reproducible ranking that surfaces the most relevant provider first, not the highest bidder.
GoHighLevel CRM Automation
The GHL integration is the business engine behind the marketplace. Every consumer action triggers an automated CRM event with no manual steps:
On lead submission:
upsertContact(), creates or updates a GHL contact with name, email, phone (E.164 format), city, state, address, source tag ("BestMedSpa.us"), and treatment/budget/timeline tags.createOpportunity(), opens a new opportunity in the configured pipeline with the correct stage, monetary value derived from budget range, and status "open".createContactNote(), attaches a structured note to the contact listing the lead details and all matched provider names, locations, and scores.
On match page actions (appointment request, callback, enquiry):
updateOpportunityStage(), moves the GHL opportunity to the configured booked/callback stage.createProviderActionNote(), appends a note detailing the specific action taken, preferred time, and message.
All GHL state (contact ID, opportunity ID, sync status, sync error) is stored on the leads table. If GHL sync fails, the lead is still captured locally and the error is surfaced in the admin dashboard for manual retry.
The GHL service layer also handles duplicate opportunity detection, if a lead re-submits and GHL returns a duplicate error, the contact sync continues without creating a second opportunity.
Admin Dashboard
The admin panel at /admin is a server-rendered Next.js route group protected by JWT session cookies (8-hour expiry, HS256 signing, httpOnly + sameSite: lax).
The dashboard homepage shows live metrics: total leads, new leads today, unworked leads, GHL sync failures (highlighted in warning color), total clinics, featured listings count, and pending claim requests.
Admin sections:
- Leads, sortable list of all leads with GHL sync status, treatment, location, name, and status badge. Clicking a lead shows full detail including matched providers.
- Lead Matches, cross-table view of all lead-to-clinic match records.
- Clinics, full provider list with edit, featured toggle, and claim status. Clinic edit form covers all 55 fields including social URLs, geo coordinates, scoring fields, and CRM data.
- Claims, pending provider claim requests with approve/reject actions.
- Featured, featured listing manager with priority ordering, start/end date control, and status toggle.
- Import, CSV import interface for bulk clinic uploads with per-run job tracking.
- Communications, admin notes and activity log (not verifiable from code reviewed, may be in development).
Programmatic SEO Architecture
The SEO system generates several distinct URL patterns:
/medspas/[cityState], city landing pages (e.g.,/medspas/austin-tx) for 75+ markets/[service]/[cityState], service-city pages (e.g.,/botox/austin-tx) for 14 treatments × 75+ cities/clinic/[cityState]/[slug], individual provider profiles/blog/[slug], treatment guides and comparison articles/blog/category/[slug], category index pages
The dynamic sitemap covers all combinations up to 49,000 URLs and includes lastModified, changeFrequency, and priority per URL type.
Every page type emits relevant JSON-LD structured data:
- City pages: Organization, WebSite, BreadcrumbList, FAQPage, ItemList (of providers), Service
- Clinic profiles: MedicalBusiness/LocalBusiness with AggregateRating, GeoCoordinates, address, telephone, medicalSpecialty, knowsAbout
- Blog articles: Article, BlogPosting, BreadcrumbList, FAQPage, HowTo (on how-to content), SpeakableSpecification
- Research pages: Dataset schema for data-driven content
The robots.ts file excludes admin routes, match pages, and thank-you flows from indexing.
Google Review Scraper
Provider ratings on the platform come from two sources: the import CSV (which includes Google Maps rating and review count at import time) and a Selenium-based Python scraper for live review content.
The scraper reads data/medspas.csv, opens each provider's Google Maps URL in a headless Chrome browser via Selenium WebDriver, scrolls the reviews panel to load all reviews, extracts review cards (author, rating, text, date), and writes normalized rows to data/google-reviews.csv.
The pipeline maintains state in var/google-review-pipeline-state.json, tracking which clinics have been scraped, which are pending, and which failed, so long scraping runs can be interrupted and resumed without re-scraping completed clinics.
AI Content Agent
Blog production at scale requires a repeatable content pipeline. We built a CLI tool at seo-agent/generate-article.ts that:
- Reads a target keyword (from argument or keyword file)
- Infers related services based on keyword content
- Generates MDX frontmatter with title, slug, description, categories, focus keyword, related services, and related cities
- Creates a structured article draft ready for editorial review
The agent reads a prompts/article-writer.md prompt template that enforces Technovier's content standards: evidence-based claims, no invented statistics, proper heading hierarchy, and internal linking conventions.
This allows a single operator to generate and review 10–20 article drafts per hour, maintaining editorial quality while scaling content output.
Analytics and Event Tracking
Microsoft Clarity was chosen for session analytics over Google Analytics for two reasons: heatmaps and session replay come built-in, and the privacy model is better suited for a healthcare-adjacent platform.
Custom events tracked:
lead_submitted, treatment, budget, timeline, location, lead IDwizard_step_N, step number and step name (discovery, sort, contact details)provider_action, action type, clinic ID, provider name, lead IDclinic_page_view, clinic ID, name, citysearch, query, search type
High-value events (lead submission, provider action) trigger a Clarity upgrade() call to ensure those sessions are recorded at full fidelity.
CI/CD and Deployment
The deployment pipeline runs on GitHub Actions with three steps: lint (eslint), build (next build), and FTP sync to cPanel using SamKirkland/FTP-Deploy-Action.
Environment variables are injected at build time via GitHub Secrets, no .env file is committed to the repository. The workflow supports both push-to-main triggers and manual workflow_dispatch runs.
The platform runs Node.js 18.20.8 on cPanel with a custom server.js entry point. The Next.js standalone output is not used, the full build directory deploys with a custom server wrapper that handles cPanel's Node.js application model.
What We Learned
Raw SQL is not a liability at this scale. With 13 tables and complex multi-join queries for lead matching, writing the queries directly gave us better performance and easier debugging than any ORM would have. The parameterized queries are safe, the indexes are explicit, and there is no "magic" between our code and the database.
Fallback mode is not optional for marketplaces. A directory platform that goes dark during a DB hiccup destroys trust. Building the CSV-backed fallback into every data read path from day one meant the consumer experience is uninterrupted regardless of infrastructure events.
Lead capture should show value before collecting contact details. The wizard's "discover first, contact second" flow, showing ranked provider matches before asking for name, phone, and email, reduces form abandonment compared to traditional gated intake forms.
GHL automation completeness matters. Partial GHL integrations (contact only, no opportunity, no notes) leave providers with half the data. The full integration, contact + opportunity + note + stage update on every consumer action, means providers see a complete, actionable CRM record from the first touchpoint.
Programmatic SEO requires structured data completeness. City and service-city pages with no structured data rank poorly against pages with full schema coverage. Building the schema layer into the page architecture from the start, rather than retrofitting it later, is significantly more efficient.
The Stack in Summary
| Layer | Technology |
|---|---|
| Framework | Next.js 14 App Router, TypeScript |
| Database | MySQL 2 (raw parameterized SQL) |
| Validation | Zod |
| Authentication | Jose JWT, bcryptjs |
| Maps | Leaflet, Google Maps API |
| CRM | GoHighLevel REST API |
| Analytics | Microsoft Clarity (custom events) |
| Images | Pexels API |
| Review Scraping | Selenium, Python |
| Content Agent | Custom CLI (TypeScript + OpenAI) |
| CI/CD | GitHub Actions to FTP to cPanel |
| Search Engine | IndexNow (Bing Webmaster) |
Technovier Revenue System Resources
If you are building a marketplace, lead routing system, or CRM automation layer, Technovier builds these end to end:
- CRM Automation Services, GoHighLevel setup, pipeline design, lead routing
- Web Application Development, custom Next.js platforms, databases, API integrations
- AI Automation Services, AI-powered workflows, content agents, lead scoring
- Healthcare Industry Solutions, lead generation and CRM systems for healthcare providers
- Revenue Systems Audit, map your current lead-to-revenue gap and get a build plan
Free Revenue Calculators
Test your current system performance before you build:
- Speed-to-Lead Calculator, how much revenue are you losing from slow lead response?
- Funnel Leak Calculator, where is your pipeline leaking revenue?
- Lead Generation ROI Calculator, is your current lead spend profitable?
