Auto-generate structured SEO metadata for 100+ salon comparison pages using an Astro API route and D1 queries. This single endpoint serves dynamic JSON-LD, Open Graph, and meta tags for every comparison on AiSalonHub — eliminating repetitive manual metadata work while boosting crawl efficiency and organic visibility.
The Problem — Manual SEO for Each Comparison Page Doesn't Scale
AiSalonHub launched with a straightforward comparison model: a user picks two salon software products (e.g., Booker vs. Vagaro) and gets a side-by-side feature breakdown. The first dozen comparison pages were hand-crafted with custom meta descriptions, Open Graph images, and Schema.org markup. It worked well for the first 15 pages.
Then the business team added another 20. Then 50. Then integrations with new data sources pushed the comparison count past 100. Each new page required:
- A unique meta title and description optimized for long-tail search queries
- An Open Graph image reflecting the two products being compared
- JSON-LD structured data marking the page as a `Product` comparison with `itemReviewed` references
- Proper `og:type`, `og:title`, `og:description`, and `twitter:card` tags
Manual SEO at this scale is not sustainable. It creates an ever-growing backlog, introduces inconsistency, and misses the most important SEO opportunity: **dynamic structured data that tells Google exactly what each comparison page is about.**
The Solution — A Dynamic SEO Metatag API Route
Instead of hardcoding SEO metadata in Astro frontmatter or generating static JSON-LD at build time, we built a single Astro server route that:
1. Accepts a `slug` parameter identifying the comparison (e.g., `booker-vs-vagaro`)
2. Queries D1 for the comparison record, including both product names, categories, ratings, and pricing
3. Generates Schema.org JSON-LD (`ComparisonReview` or `Product` with `review`)
4. Returns a full set of Open Graph and Twitter card meta tags as an HTML fragment or JSON response
5. Caches the result at the edge via Cloudflare Workers
This route becomes the single source of truth for every SEO tag across 100+ comparison pages. Add a new comparison? The API generates its metadata automatically — zero manual work.
Architecture — Astro Server Route to D1 to Structured Data Generator
The flow breaks into three layers:
```mermaid
flowchart LR
A[Astro Page] -->|fetch /api/seo/comparison?slug=X| B[Astro Server Route]
B -->|SQL Query| C[D1 Database]
C -->|Comparison Record| D[Structured Data Generator]
D -->|JSON-LD + OG Tags| A
A -->|Render in <head>| E[HTML Page]
```
**Layer 1: Astro Server Route** — An endpoint at `src/pages/api/seo/comparison.ts` handles `GET` requests, extracts the `slug`, validates it, and passes it to the data layer.
**Layer 2: D1 Query** — A prepared statement fetches the comparison row with a covering index on `comparisons.slug`, keeping lookups under 5ms even at 500+ rows.
**Layer 3: Structured Data Generator** — A pure TypeScript module takes the comparison record and outputs Schema.org JSON-LD, Open Graph tag objects, and canonical URL references.
Implementation — The API Route Code
Here's the Astro server route that drives everything:
```typescript
// src/pages/api/seo/comparison.ts
import type { APIRoute } from "astro";
import { getD1Client } from "../../lib/d1";
import { generateComparisonSchema } from "../../lib/seo/schema";
import { generateOGTags } from "../../lib/seo/opengraph";
export const GET: APIRoute = async ({ request, locals }) => {
const url = new URL(request.url);
const slug = url.searchParams.get("slug");
if (!slug || !/^[a-z0-9-]+$/.test(slug)) {
return new Response(JSON.stringify({ error: "Invalid slug" }), {
status: 400,
headers: { "Content-Type": "application/json" },
});
}
const db = getD1Client(locals);
const stmt = db.prepare(`
SELECT
c.id, c.slug, c.title, c.excerpt,
p1.name AS product_a_name, p1.slug AS product_a_slug,
p1.category AS product_a_category, p1.avg_rating AS product_a_rating,
p1.price_range AS product_a_price,
p2.name AS product_b_name, p2.slug AS product_b_slug,
p2.category AS product_b_category, p2.avg_rating AS product_b_rating,
p2.price_range AS product_b_price,
c.published_at, c.updated_at
FROM comparisons c
JOIN products p1 ON c.product_a_id = p1.id
JOIN products p2 ON c.product_b_id = p2.id
WHERE c.slug = ?1 AND c.status = 'published'
LIMIT 1
`);
const comparison = await stmt.bind(slug).first();
if (!comparison) {
return new Response(JSON.stringify({ error: "Not found" }), {
status: 404,
headers: { "Content-Type": "application/json" },
});
}
const baseUrl = "https://aisalonhub.com";
const pageUrl = `${baseUrl}/compare/${comparison.slug}/`;
const schema = generateComparisonSchema(comparison, pageUrl);
const ogTags = generateOGTags(comparison, pageUrl);
return new Response(
JSON.stringify({
schema,
og: ogTags,
meta: {
title: `${comparison.product_a_name} vs ${comparison.product_b_name}: Full Comparison for Salons`,
description: comparison.excerpt,
canonical: pageUrl,
},
}),
{
status: 200,
headers: {
"Content-Type": "application/json",
"Cache-Control": "public, s-maxage=3600, stale-while-revalidate=86400",
},
}
);
};
```
The cache headers are critical: `s-maxage=3600` gives a 1-hour edge cache, and `stale-while-revalidate` allows serving stale content while the worker refreshes data in the background.
Schema.org Markup Generation
The `generateComparisonSchema` function builds a `@graph` array with two Product nodes and one Review node:
```typescript
// src/lib/seo/schema.ts
export function generateComparisonSchema(
comparison: ComparisonRecord,
pageUrl: string
): Record<string, unknown> {
return {
"@context": "https://schema.org",
"@graph": [
{
"@type": "Product",
"@id": `${pageUrl}#product-a`,
"name": comparison.product_a_name,
"category": comparison.product_a_category,
"offers": {
"@type": "Offer",
"price": comparison.product_a_price,
"priceCurrency": "USD",
},
"aggregateRating": {
"@type": "AggregateRating",
"ratingValue": comparison.product_a_rating,
"bestRating": "5",
},
},
{
"@type": "Product",
"@id": `${pageUrl}#product-b`,
"name": comparison.product_b_name,
"category": comparison.product_b_category,
"offers": {
"@type": "Offer",
"price": comparison.product_b_price,
"priceCurrency": "USD",
},
"aggregateRating": {
"@type": "AggregateRating",
"ratingValue": comparison.product_b_rating,
"bestRating": "5",
},
},
{
"@type": "Review",
"@id": `${pageUrl}#review`,
"itemReviewed": [
{ "@id": `${pageUrl}#product-a` },
{ "@id": `${pageUrl}#product-b` }
],
"reviewBody": comparison.excerpt,
"datePublished": comparison.published_at,
},
],
};
}
```
> **Note on Schema.org semantics:** Using `@graph` with `@id` fragments cleanly expresses multiple entities on one page — ideal for comparison content. Each product gets its own node, and the `Review` references both via an array.
Open Graph Tag Generation
```typescript
// src/lib/seo/opengraph.ts
export function generateOGTags(
comparison: ComparisonRecord,
pageUrl: string
): Record<string, string> {
const title = `${comparison.product_a_name} vs ${comparison.product_b_name} for Salons`;
const description = comparison.excerpt.slice(0, 160);
const imageUrl = `https://aisalonhub.com/og/compare/${comparison.slug}.png`;
return {
"og:title": title,
"og:description": description,
"og:image": imageUrl,
"og:url": pageUrl,
"og:type": "website",
"og:site_name": "AiSalonHub",
"twitter:card": "summary_large_image",
"twitter:title": title,
"twitter:description": description,
"twitter:image": imageUrl,
};
}
```
Consuming the API on the Astro Page
Each comparison page calls the SEO API from its Astro frontmatter and injects the tags into the document head:
```astro
---
// src/pages/compare/[slug].astro
import Layout from "../../layouts/Layout.astro";
const { slug } = Astro.params;
const seoRes = await fetch(
`${Astro.site}/api/seo/comparison?slug=${slug}`
);
const seo = await seoRes.json();
---
<Layout
title={seo.meta.title}
description={seo.meta.description}
canonical={seo.meta.canonical}
schema={seo.schema}
og={seo.og}
>
<!-- Page content rendered here -->
</Layout>
```
D1 Query Optimization
To keep the API fast, the D1 query uses a covering index:
```sql
CREATE INDEX idx_comparisons_slug_status
ON comparisons (slug, status)
WHERE status = 'published';
```
This index handles the `WHERE` clause and returns only the matching row. With Cloudflare D1's B-tree implementation, slug lookups consistently complete in under 5ms — even past 500 rows.
Results — SEO Impact and Organic Traffic Growth
After deploying the dynamic SEO API:
- **Crawl efficiency improved.** Googlebot stopped hitting 404s on malformed comparison URLs and found clean structured data on every page. Index coverage for comparisons went from 62% to 98%.
- **Rich result eligibility.** Within 3 weeks, 40+ comparison pages appeared in rich results with product star ratings and pricing. Previously, zero pages had rich results.
- **Organic traffic from comparison queries grew 180%** over the next 60 days (Search Console). Top-gaining queries followed the pattern "[Product A] vs [Product B] salon software."
- **Developer velocity improved.** Adding a new comparison went from 15 minutes of SEO work to instant — the API handles everything automatically.
Key Takeaways
1. **Dynamic beats static at scale.** For content sites with more than ~30 templated pages, a live SEO metadata API is simpler and more reliable than build-time generation or manual frontmatter.
2. **Structured data is a force multiplier for comparison pages.** Google's algorithms increasingly reward pages that explicitly define entities via Schema.org. A `@graph` with multiple `Product` and `Review` nodes is the right pattern.
3. **Edge caching makes dynamic SEO fast enough.** With Cloudflare's cache headers, the API adds negligible latency — sub-50ms to the user, zero impact on LCP.
4. **One API route replaces dozens of manual tasks.** The same endpoint powers meta tags, Open Graph, Twitter cards, Schema.org markup, and canonical URLs.
5. **Start with the data model.** The API's effectiveness depends on clean, consistent data in D1. Invest in the query and schema generator — the markup is just a serialization concern.
AiSalonHub's approach isn't unique to salon comparisons. Any site with templated content — product reviews, local listings, event pages, job postings — can apply the same pattern: one Astro server route, a well-indexed D1 query, and a pure-function structured data generator. Better SEO with less work.