DEV Community: kavela

Launching PlatformTrack: a weekly-synced streaming availability index for 17 platforms x 57 countries

kavela — Thu, 28 May 2026 21:06:31 +0000

Streaming availability is a deceptively simple question. Does Netflix carry The Bear in Turkey? How many movies does Disney+ offer in Saudi Arabia versus Germany? Which country has the deepest streaming catalog in the world?

The answers are not where you'd expect. The deepest Netflix catalog in the world isn't in the United States — it's in the Netherlands (9,871 titles), followed by Slovakia, Bulgaria, Spain and Portugal. The US ranks 35th out of 56 markets we track. That's the kind of finding that's hard to surface unless someone is collecting the data systematically, week after week.

We just launched PlatformTrack to do exactly that.

What it is

PlatformTrack is a public index of streaming-platform catalog presence. For every (platform, country) pair we monitor, it records:

Total catalog size (movies + series)
Movie/series split
Popularity-ranked sample of titles
A weekly snapshot for historical trend analysis

Coverage at launch: 17 platforms × 57 countries = 969 pairs, refreshed every Monday at 03:00 UTC.

The site surfaces it across a few page types:

/country/{slug}/ — which platforms serve this country and how deep each catalog is
/platform/{slug}/ — which countries this platform reaches and how its catalog scales
/platform/{slug}/{country}/ — the cross-section: catalog composition, top titles, snapshot trend
/compare/{a}-vs-{b}/{country}/ — head-to-head platform comparison in a market
/ranking/ and /ranking/platforms/ — global leaderboards

The architecture

The interesting build choices were on the data side.

Source: TMDB Watch Providers

All catalog measurements come from TMDB's Watch Providers feed, which is sourced upstream from JustWatch. The canonical query is the Discover endpoint with a watch_region and with_watch_providers filter:

GET /3/discover/{movie|tv}
    ?watch_region={ISO}
    &with_watch_providers={provider_id}
    &sort_by=popularity.desc

The first page returns total_results, which is the catalog size. Subsequent pages are used to harvest the popularity-ranked sample. We don't walk every title; we record the total and snapshot the top ~40 per pair for downstream features (exclusivity calc, trending bands, "top titles" lists).

At 17 platforms × 57 countries × 2 media types × ~2 pages, that's ~3,800 API calls per sync. At 4 req/sec to stay under TMDB's rate limit, the full sync runs in about 17 minutes.

Storage: 5-table schema

pt_platforms     -- tmdb_provider_id, slug, name, logo_url, platform_type
pt_countries     -- iso_code, slug, name, region
pt_availability  -- platform_id, country_id, title_count, movie_count, series_count,
                 -- exclusive_count, last_synced
pt_top_titles    -- the popularity-ranked sample per pair
pt_snapshots     -- the append-only history table for time-series queries

The split between pt_availability (live, overwritten weekly) and pt_snapshots (append-only) is what makes historical analysis cheap. Country pages show the current state; deep-link analyses pull from snapshots.

Frontend: WordPress + virtual URLs

We needed pSEO at scale (hundreds of programmatic pages) without the overhead of real WP posts for each. The plugin (pt-engine) registers rewrite rules that route URLs like /platform/netflix/turkey/ to virtual templates:

add_rewrite_rule('^platform/([^/]+)/([^/]+)/?$',
    'index.php?pt_page_type=platform_country' .
    '&pt_platform=$matches[1]&pt_country=$matches[2]', 'top');

The template_include filter maps pt_page_type to a PHP template file in the plugin. No posts in wp_posts, no editing overhead, instant updates when the data table changes.

The trade-off: WP's native sitemap generator doesn't see these URLs, so the plugin generates its own at /sitemap-pt-{index,countries,platforms,cross}.xml from the data tables.

Sync pipeline

The weekly sync is a Python script on the same host as MySQL:

for country in countries:
    for platform in platforms:
        movie_total, movie_sample = discover_count("movie", country.iso, platform.tmdb_id)
        tv_total,    tv_sample    = discover_count("tv",    country.iso, platform.tmdb_id)
        upsert_availability(platform.id, country.id, movie_total, tv_total)
        replace_top_titles(platform.id, country.id, movie_sample + tv_sample)
        snapshot(platform.id, country.id, today, movie_total + tv_total, movie_total, tv_total)
    conn.commit()  # per-country, so a mid-run failure leaves coherent state
compute_exclusives()

After the sync, an IndexNow ping submits the changed URLs to Bing and Yandex.

Some findings from launch week

A few patterns emerged immediately from the first full sync that are worth reporting:

Europe splits into three tiers. The UK runs 141,533 title-availability records across 13 platforms. The Western Balkans run 11,000–17,000 across 5 platforms. The middle 15 markets cluster between 35,000 and 55,000. It's a sharp three-step distribution, not a smooth curve.

The GCC bundle is real. Bahrain, Kuwait, Qatar and Oman cluster inside a 290-title band — essentially identical supply, despite being separately licensed markets on paper. The UAE and Saudi Arabia anchor the deep end (17,601 and 15,797 titles respectively).

Netflix's catalog is mid-pack in the US. This was the most counter-intuitive finding. The US Netflix catalog is smaller than every European mid-tier market and most of MENA. The deepest Netflix catalogs sit in markets where Netflix is the only major streaming option, because third-party content hasn't been pulled into competing services.

We wrote these up in three research briefs on the launch:

The site is positioned for journalists, academics and analysts — every figure is citation-ready with an "How to cite" block and CC-BY 4.0 licensing on the aggregate data.

What's next

Two things are queued for the next few weeks:

Historical trend reports — once the snapshot table has eight weeks of data, country-level catalog growth/decline becomes a publishable beat.
More platforms — Hulu, Peacock and Rakuten Viki are on the shortlist. The blocker is TMDB watch-provider coverage, not our pipeline.

If you build streaming products, do regional licensing analysis, or just want to know whether your subscription is actually larger or smaller in your country than in your friend's: platformtrack.com.

The underlying availability data is also exposed via the StreamWatchHub API on RapidAPI if you want programmatic access — per-title lookups and bulk endpoints.

Methodology, data dictionary and full caveats in the Research section.

Questions, corrections or custom data requests: research@platformtrack.com.

Build a "Where to Watch" feature in 50 lines with the StreamWatchHub API

kavela — Sat, 23 May 2026 17:47:14 +0000

Most teams shipping a "where can I watch X?" feature run into the same set of problems:

JustWatch does not offer a public API.
TMDB watch-providers covers the major markets only, without rent prices or deep links.
Live sports availability requires a separate broadcaster feed.
Platform names, regional catalogs, and deep-link formats all need to be reconciled.

StreamWatchHub consolidates this into a single REST endpoint and one consistent schema, covering every streaming service plus live football broadcasters across 10 countries. The API is now live on RapidAPI with a free tier (1,000 calls/month), so it can be evaluated without a payment method.

This post walks through wiring a production-ready "where to watch" feature in roughly 50 lines of Node.js plus a small React component.

What the API returns

A single call per title, with the monetization type flagged on every offer:

curl "https://streamwatchhub.p.rapidapi.com/v1/search?q=the+bear&country=US" \
  -H "X-RapidAPI-Key: $KEY" \
  -H "X-RapidAPI-Host: streamwatchhub.p.rapidapi.com"

Response (trimmed):

{
  "results": [{
    "tmdb_id": 136315,
    "title": "The Bear",
    "type": "series",
    "year": 2022,
    "offers": [
      { "platform": "hulu",     "type": "flatrate", "url": "https://hulu.com/series/..." },
      { "platform": "disney",   "type": "flatrate", "url": "https://disneyplus.com/..." },
      { "platform": "apple",    "type": "rent",     "price": "$2.99", "url": "https://tv.apple.com/..." }
    ]
  }]
}

No per-platform SDKs, no scraping fallbacks. The same response shape applies to movies, series, and football matches — sports responses substitute kickoff_at and broadcasters[] for offers[].

1. Sign up and obtain an API key

Visit the RapidAPI listing and select the free plan.
Copy the issued RapidAPI key and export it: export RAPIDAPI_KEY=...

2. A minimal Node client

// swh.js
const HOST = "streamwatchhub.p.rapidapi.com";
const BASE = `https://${HOST}/v1`;

export async function whereToWatch(query, country = "US") {
  const url = new URL(`${BASE}/search`);
  url.searchParams.set("q", query);
  url.searchParams.set("country", country);

  const res = await fetch(url, {
    headers: {
      "X-RapidAPI-Key":  process.env.RAPIDAPI_KEY,
      "X-RapidAPI-Host": HOST,
    },
  });
  if (!res.ok) throw new Error(`SWH ${res.status}`);
  return res.json();
}

No client library is required. The full surface is seven GET endpoints (/search, /title/{id}/offers, /series/{id}/season/{n}/offers, /match/{id}/broadcasters, /fixtures, /platforms, /changes).

3. A drop-in React component

import { useEffect, useState } from "react";
import { whereToWatch } from "./swh";

export function WhereToWatch({ title, country = "US" }) {
  const [data, setData] = useState(null);
  const [err,  setErr]  = useState(null);

  useEffect(() => {
    whereToWatch(title, country).then(setData).catch(setErr);
  }, [title, country]);

  if (err)        return <p>Couldn't load options.</p>;
  if (!data)      return <p>Loading…</p>;
  const hit = data.results?.[0];
  if (!hit)       return <p>No streaming match in {country}.</p>;

  return (
    <div>
      <h3>{hit.title} <small>({hit.year})</small></h3>
      <ul>
        {hit.offers.map((o, i) => (
          <li key={i}>
            <a href={o.url} target="_blank" rel="noopener">
              <strong>{o.platform}</strong> — {o.type}
              {o.price && <> · {o.price}</>}
            </a>
          </li>
        ))}
      </ul>
    </div>
  );
}

Usage: <WhereToWatch title="The Bear" country="US" />.

Current coverage

14,000+ titles (movies and TV series), refreshed daily
70+ providers — Netflix, Disney+, HBO Max, Apple TV, OSN+, Shahid, Movistar Plus+, Kinopoisk, Now TV, Sky Go, Paramount+, and others
10 countries at launch — US, GB, DE, ES, IT, FR, TR, BR, IN, SA (more being added)
Live football — 23 leagues including the Big Five plus UCL / UEL / UECL and the Saudi Pro League, with per-country broadcaster resolution (the same fixture airs on different channels in each market, and the API resolves the correct one)
Confidence scores on broadcaster matches so low-confidence entries can be filtered out client-side
Deep links that open the correct detail page on each platform

Roadmap

A dedicated Sports RapidAPI listing is launching in the coming weeks; football fixtures are already accessible inside the current Movies & Series listing.
Additional countries are scheduled: Mexico, Argentina, Australia, Japan, and Korea.
A natural-language /recommend endpoint is under evaluation ("where can I watch the Champions League final in Romania tonight?") — the team is still assessing whether it adds enough value beyond the structured endpoints.

Try it

🌐 Landing and documentation: streamwatchhub.com/api
💚 Free tier on RapidAPI: Movies & Series listing
📦 Source examples (cURL, Node, Python, PHP): github.com/Kaveladev/streamwatchhub

Feedback is welcome before the v1 schema is frozen. One open question for readers: how should confidence_score be exposed — as a raw number, a low / medium / high enum, or hidden entirely below a threshold? Comments are appreciated.

Thanks for reading.

Building a 32-URL economy microsite on top of a 754,000-row SQLite dataset

kavela — Wed, 20 May 2026 22:15:40 +0000

Building a 32-URL economy microsite on top of a 754,000-row SQLite dataset

We launched GDPIndex, a reference site for the twenty G20 economies. The numeric data sits in a 152 MB SQLite file (216 countries × 102 indicators × 754,809 rows of annual country-data). The interesting part of the build is not the dataset itself but how we exposed only a tiny, focused slice of it — and why we resisted the urge to publish thousands of pages.

This post is about the WordPress side of that decision: how the site routes 32 URLs against a half-million-row store without going near WP_Query, and how we split structured numbers from country-specific editorial narrative so each page has a different skeleton.

Why 32 URLs

The same SQLite already powers a much larger reference site — HistorySaid — with about 600 indexed URLs across 216 countries and 9 economic themes. GDPIndex is a deliberate subset:

20 country pages at /{country}/economy/ (G20: 19 nations + EU)
9 ranking pages at /rankings/{indicator}/ covering the GDP-family series
3 static (about, methodology, privacy, terms)

That cap is the point. Google now treats large auto-generated reference sites with suspicion, and we already had the per-country data — what was missing was editorial reason for each page to exist. Twenty G20 economies, each with a structural story that has been the subject of decades of literature, is a defensible scope.

Virtual routing without `WP_Query`

WordPress's rewrite system is great for posts, taxonomies, and the bits of the URL space that map onto database records. It is terrible for serving 30 URLs whose content is computed at request time from data in a sibling table. Every workaround we have seen — custom post types, ACF-driven templates, rewrite endpoints — ends up either creating 30 dummy posts to "host" the URLs or fighting the parse_request lifecycle.

A cleaner approach: never let WordPress think about these URLs at all. Hook template_redirect at priority 0, parse $_SERVER['REQUEST_URI'] yourself, and short-circuit the response if the path matches one of your virtual routes.

class GI_Router {
    public function __construct(GI_DB $db, $engine) {
        add_filter('wp_sitemaps_enabled', '__return_false');
        add_action('parse_request', [$this, 'early_intercept'], 0);
        add_action('template_redirect', [$this, 'handle_request'], 0);
    }

    public function handle_request(): void {
        $path = trim(strtok($_SERVER['REQUEST_URI'], '?'), '/');
        if ($path === '') return;
        $segs = array_values(array_filter(explode('/', $path)));
        $route = $this->resolve($segs);
        if (!$route) return;

        global $hs_route;
        $hs_route = $route;

        status_header(200);
        global $wp_query;
        $wp_query->is_404 = false;
        $wp_query->is_page = true;

        get_header();
        include get_stylesheet_directory() . '/templates/' . $route['template'] . '.php';
        get_footer();
        exit;
    }
    // ...
}

The resolver is a single switch on path shape:

private function resolve(array $segs): ?array {
    $g20 = gi_g20_map();          // ['turkey' => 'turkiye', ...]
    $gdp_inds = gi_gdp_indicators();

    // /{country}/economy
    if (count($segs) === 2 && $segs[1] === 'economy' && isset($g20[$segs[0]])) {
        return [
            'type' => 'country_economy',
            'template' => 'country-economy',
            'url_slug' => $segs[0],
            'db_slug'  => $g20[$segs[0]],
            // ...
        ];
    }

    // Bare /{country}/ → 301 to /economy
    if (count($segs) === 1 && isset($g20[$segs[0]])) {
        return ['redirect' => '/' . $segs[0] . '/economy/'];
    }

    // /rankings/{indicator}
    if (count($segs) === 2 && $segs[0] === 'rankings' && in_array($segs[1], $gdp_inds, true)) {
        // ...
    }

    return null; // not our URL — let WP handle it
}

Returning null is critical: anything we don't claim falls through to WordPress, so /wp-admin/, /wp-json/, the favicon, and robots.txt all keep working.

We also intercept sitemap.xml, robots.txt, and llms.txt on parse_request (earlier than template_redirect), because WordPress 5.5+ ships a core sitemap at /wp-sitemap.xml and will 301 you there before your template hook runs. wp_sitemaps_enabled → false disables that core handler.

Two slugs per country

The dataset and the URL space don't agree on slugs. SQLite has russian-federation, turkiye, korea, european-union. The URLs we want are russia, turkey, south-korea, eu. The router holds a single canonical map:

function gi_g20_map(): array {
    return [
        'argentina'      => 'argentina',
        'australia'      => 'australia',
        // ...
        'russia'         => 'russian-federation',
        'south-korea'    => 'korea',
        'turkey'         => 'turkiye',
        'united-kingdom' => 'united-kingdom',
        'united-states'  => 'united-states',
    ];
}

Every layer that touches a country goes through this map: the router resolves URL → DB slug, the data layer queries by DB slug, the page renderer flips back to the URL slug when emitting internal links, and the JSON-LD BreadcrumbList uses URL slugs end-to-end. Putting the map in one function and refusing to allow ad-hoc string substitution anywhere else is what keeps the site consistent.

Two custom tables: structure plus narrative

The page template is shared across all 20 countries (hero + KPI strip + chart-bearing sections + FAQ + peer-rank table), but the content of each section is different per country. The "middle-income trap" section appears on Türkiye, China and Brazil; it does not appear on Germany. So we cannot store sections as a fixed schema — we store them as JSON.

CREATE TABLE wp_gi_country (
    slug VARCHAR(64) NOT NULL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    iso3 CHAR(3),
    headline_question TEXT,
    intro_html LONGTEXT,
    sections_json LONGTEXT,   -- [{heading, body_html, charts:[...], tables:[...]}]
    faq_json TEXT,            -- [{q, a}]
    word_count INT DEFAULT 0,
    generated_at DATETIME,
    locked TINYINT(1) DEFAULT 0
);

CREATE TABLE wp_gi_ranking (
    slug VARCHAR(64) NOT NULL PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    analysis_html LONGTEXT,
    word_count INT DEFAULT 0,
    generated_at DATETIME
);

A single PHP page template iterates sections_json and renders each section with its own chart and table specs. Chart specs reference the indicator slug (gdp, gdp-per-capita, gdp-growth, …); the template loads the time-series from SQLite at render time and ships a Chart.js config to the browser as a data-config attribute.

This separation also matters for editorial workflow: numbers come from SQLite (refreshed nightly from the source feed), and narrative lives in MySQL alongside the rest of WordPress. We can re-render the narrative without rebuilding the data, and we can refresh the data without touching the narrative.

The page-level "linker matrix"

To make 20 country pages a useful cluster instead of 20 dead ends, the template emits the same internal-link block on every page, but with the current country removed and a peer-selection step on top:

$peers = array_diff(array_keys(gi_g20_map()), [$url_slug]);
shuffle($peers);
$sample = array_slice($peers, 0, 5);

Plus a static link to /rankings/gdp/ from every country page, and a <th>Country</th> cell on every ranking row that links back to /{country}/economy/. Every node in the 29-page graph (20 country + 9 ranking) has at least eight outbound internal links, and every node has at least nine inbound links from peer pages.

Sitemap and JSON-LD discipline

With 32 URLs and a small fixed shape, the sitemap is a 75-line PHP function:

public function urls(): array {
    $urls = [
        ['loc' => home_url('/'), 'changefreq' => 'weekly', 'priority' => '1.0'],
        ['loc' => home_url('/about/'), 'changefreq' => 'yearly', 'priority' => '0.4'],
        // ...
    ];
    foreach (array_keys(gi_g20_map()) as $slug) {
        $urls[] = ['loc' => home_url('/' . $slug . '/economy/'),
                   'changefreq' => 'monthly', 'priority' => '0.9'];
    }
    foreach (gi_gdp_indicators() as $ind) {
        $urls[] = ['loc' => home_url('/rankings/' . $ind . '/'),
                   'changefreq' => 'monthly', 'priority' => '0.7'];
    }
    return $urls;
}

Each country page emits an @graph JSON-LD payload with five nodes: Article, Country, one StatisticalDataset per data table cited, FAQPage, and BreadcrumbList. Ranking pages add ItemList whose itemListElement[].url points back at the country profile. Inspecting any page with Google's Rich Results Test surfaces all five entity types correctly.

What we kept out

A few things we explicitly did not build because they would have undermined the focus:

No author pages or post archives. This is a reference site; it has no blog.
No tag taxonomy. Each country has a fixed analytical angle; adding a "tags" axis would dilute the structure.
No automatic monthly republish. Data refreshes nightly, but the narrative is only regenerated when an inflection year appears in the underlying series or when a major policy event materially changes the structural picture.
No comment system. It is a citation surface, not a forum.

What's published

Live at gdpindex.org. Twenty country profiles, nine ranking pages, three static pages. Total page count: 32. Average word count per country profile: ~4,500.

The same pattern (custom router → custom tables → bespoke per-instance schema) generalises to any focused reference site sitting on top of structured data. If you find yourself fighting WP_Query to expose computed content, take five hours and write the router instead. WordPress is happy to step out of the way once you ask it firmly.

Originally published at gdpindex.org.

How we repurposed a Cloudflare Email Worker domain into a SaaS without taking it down

kavela — Mon, 18 May 2026 12:08:29 +0000

Last week we did something we'd been putting off for months: we took a domain that was working — newmail.click, a throwaway-inbox utility wired to a Cloudflare Email Worker — and repurposed it into a paid SaaS without unplugging anything.

The result is a domain doing two completely unrelated jobs:

Apex (newmail.click) — a Next.js email signature builder with click + open tracking, $19/mo.
inbox.newmail.click — the original throwaway-inbox worker, untouched, still serving the PBN bot that depends on it.

Both share one zone, one MX record set, one set of nameservers. Neither knows the other exists. Here is how we got there, and why we'd do it again instead of registering a fresh domain.

The problem

The original newmail.click was a 50-line Cloudflare Worker: catch-all *@newmail.click MX → KV store with a 7-day TTL → a single GET /?to=&token= read endpoint that another bot polled. Total operational surface: one Worker, one KV namespace, one auth token.

It worked. It also did exactly nothing for SEO. The apex was a 404. Google had no reason to index anything. The domain was burning $12/year as glorified plumbing.

Meanwhile we had a half-finished signature builder sitting on a different domain that wasn't going to rank for anything because the name was forgettable. newmail.click is short, memorable, and topically adjacent (email → email signatures). The smart move was to merge them.

The dumb move was the obvious one: kill the inbox worker, point everything at the SaaS. We weren't doing that. The PBN bot uses the inbox worker daily. Migrating it would be a week of work to save fifteen minutes of DNS surgery.

The architecture

The trick is recognizing that email routing and HTTP routing are independent layers on the same domain.

newmail.click           A → hetzner (Next.js SaaS)
www.newmail.click       A → hetzner (Next.js SaaS)
inbox.newmail.click     CNAME → Cloudflare Worker
*@newmail.click         MX → Cloudflare Email Routing → Worker → KV

Cloudflare Email Routing operates on MX records. The Next.js app operates on A records. As long as you don't try to route HTTP for inbox.* to the SaaS, or MX for the apex to anywhere weird, the two stacks are invisible to each other.

The handoff plan in our notes is exactly three DNS changes:

Apex + www A records → hetzner 77.42.127.243
inbox subdomain unchanged
MX records unchanged

That's the entire migration. No worker rewrite, no KV export, no downtime on the inbox side.

The SaaS

The signature builder itself is boring on purpose. Next.js 16, better-sqlite3, NextAuth (Google), Stripe checkout, PM2 process on port 3120. We resisted every urge to put it on a "real" database or a serverless platform. SQLite holds the entire signature catalog, the tracking events, and the user records in one file. When it stops holding them, we'll move it. Probably never.

The tracking layer is the only mildly interesting part. Every link in a generated signature is wrapped with /r/{hash} for click redirects, and we attach a /p/{hash}.png pixel for opens. Hash is a deterministic short ID per (user, link, signature_version) tuple so signatures stay stable across email clients that aggressively cache images.

The pSEO surface is 30 EN profession-specific pages (/freelance-designer-email-signature, /real-estate-agent-email-signature, etc.), each 2,800–3,600 words with sector stats, cold-outreach tactics, 10–12 FAQ blocks and FAQPage JSON-LD. Content fill ran through DeepSeek because Gemini's keys were dead at the time. We'd recommend the same — DeepSeek long-form output handled the 90k-word generation pass for under a dollar.

Why share a domain at all

The temptation when launching anything new is to register a clean domain and avoid the cognitive overhead of multi-purpose DNS. We've done this enough times to recognize the trap:

A new domain has zero crawl history. Apex newmail.click had three years of MX-record stability — that's not authority, but it's not nothing either, and Google's spam classifier notices.
One zone means one set of DNSSEC keys, one set of CAA records, one Cloudflare account to keep paid. We have eighteen active domains. Every reduction matters.
The brand association — "newmail" as an email-adjacent product name — works better with both products coexisting. The throwaway-inbox usage signal feeds the apex's topical relevance.

The cost is a slightly more elaborate runbook ("don't touch the MX records when you migrate the A records"). We can live with that.

What we'd do differently

One thing in retrospect: we should have set up the apex landing on the target server (hetzner) from the start, not on the static-hosting box (contabo) where the placeholder currently sits. The plan calls for a DNS swap at cutover, which means a TLS cert reissue on the new server. If the apex had always been on hetzner, the SaaS could go live by replacing the nginx vhost config — zero DNS propagation wait.

The other thing: we removed the Turkish locale before launch because we wanted the EN signal to be unambiguous for Google. In hindsight this was probably premature; the 30 EN profession pages were going to dominate the keyword distribution regardless. We'll add /tr/* back if the TR market shows demand.

Takeaway

Domains aren't products. They're addresses. A single domain can run a Worker on one MX layer, a Next.js app on its apex, and a pSEO surface on its subpaths simultaneously, and no part of that requires you to delete the others.

If you have a domain that's underperforming as a single-purpose utility, look at what else it could host before you go shopping for a new one. Most of the time the answer is "more than you think."

The signature builder is at newmail.click — free tier is unlimited, Pro adds tracking for $19/mo or $149/yr.

How we recovered from a 30,000 to 5 Google deindex on a programmatic SEO site

kavela — Fri, 15 May 2026 06:58:59 +0000

In April, HistorySaid.com had ~30,000 URLs in Google's index. By May 13, the count was five. Five total URLs.

This is the story of what happened — what we think we know about why, and the surgery we performed to recover. If you run a programmatic SEO site on WordPress, especially one with templated cross-product pages (country × indicator, asset × asset, region × year), the same trap is sitting under your traffic.

The site, briefly

HistorySaid.com is a programmatic reference site built on WordPress, but the content lives in custom tables, not wp_posts. There's a virtual router that intercepts URLs and renders pages out of a SQLite database of countries, indicators, figures, signals, comparisons, decades, superlatives — the long-tail combinatorial surface area you'd expect from a "every country × every indicator × every decade" play.

At peak, the sitemap held about 30,120 URLs spread across these page types:

Page type	Count	URL pattern
Country × Indicator	~22,000	`/{country}/{indicator}`
Compare (A vs B)	~7,300	`/compare/{a}-vs-{b}`
Country hub	216	`/{country}/`
Ranking (per indicator)	102	`/ranking/{indicator}`
Figure	151	`/figure/{slug}`
Pillar (insight hub)	67	`/insights/{slug}`
Signal (analytical post)	102	`/signals/{slug}`
Inquiry (long-form Q&A)	~16	`/why/{slug}`, `/how/{slug}`

The first three categories were the volume play. They were also the trap.

Diagnosis

We didn't see a manual action. There was no penalty notice, no message in Google Search Console. The index count just slid. April: 5,000+. Early May: ~1,200. May 13: 5.

The signature of this is well-known among programmatic SEO operators: Google's scaled content abuse classifier. It's a system, not a manual reviewer. It samples a property, decides "this site is producing thin templated content at scale," and broadly deindexes it. We've seen the same playbook hit two other sites in our network this year before we recognized the shape.

The reason is geometric. If a classifier samples 200 URLs from a 30,000-URL site and 195 of them are /{country}/{indicator} pages with <2,000 characters of unique content surrounding identical data tables, the verdict is set. The other 5,000 high-quality pages don't get evaluated individually — they go down with the sample.

We confirmed the diagnosis by category:

22,000 country × indicator pages: identical template, ~1,200 chars unique text per page (mostly programmatic sentences like "Turkey's inflation rate was X% in 2023").
7,300 compare pages: similar pattern, paired data, even less unique copy.
216 country hubs: had real data density, but Google never got to evaluate them individually.

Surgery 1: shed the templated mass

The recovery move was counterintuitive: don't try to fatten 30,000 thin pages. Trim them to 700. Give the classifier a small, dense, unique-data site to re-evaluate.

We did this in two passes.

May 13 pass dropped the worst offenders. In the SEO plugin's robots filter:

public function rankmath_robots($robots) {
    $thin_types = [
        'indicator_decade', 'indicator_superlative',
        'indicator', 'compare',
    ];
    if (in_array($this->route['type'] ?? '', $thin_types, true)) {
        $robots['index']  = 'noindex';
        $robots['follow'] = 'follow';
    }
    return $robots;
}

The matching HTML meta and X-Robots-Tag header went out at template render time, not on a delay. The sitemap router returned 410 Gone for the matching shards so Google would stop trying:

private function serve_sitemap($type, $page) {
    if (in_array($type, ['decades','superlatives','indicators','compare'], true)) {
        status_header(410);
        header('X-Robots-Tag: noindex');
        echo "<?xml version=\"1.0\"?><urlset xmlns=\"...\"></urlset>";
        exit;
    }
    // …
}

After this pass, 738 URLs remained in the sitemap (~2.5% of the original).

May 15 pass went further. The first sweep wasn't enough; the classifier seemed to be re-evaluating and lingering on rankings, assets, groups, and patterns — all of which still had templated bodies under unique titles. We added them to the $thin_types list and the sitemap guard. The indexable spine became 583 URLs: the 216 country hubs, 151 figures, 67 pillars, 102 signals, 16 long-form inquiries, 26 snippets, 5 evergreen pages.

The non-spine pages aren't gone — they still resolve, they still take internal links, they just carry noindex,follow. Google can crawl through them to discover the spine. They just don't compete for index slots.

Surgery 2: thicken the spine with real data + analysis

Trimming alone isn't enough. The 583 survivor pages also had to clearly not look templated. Country hubs were the worst offenders here: the same hero, the same data table, the same auto-generated paragraph.

We built a small enrichment pipeline that does two things per page:

Pulls a small unique data block from a different source than the existing tables.
Generates a 130–180 word analytical paragraph that cites the numbers from that block.

The fresh data source was DBnomics, which aggregates IMF, OECD, World Bank, ECB, BIS series under a free, no-key API. For each of the 216 country hubs we pulled three series from IMF's World Economic Outlook:

GET https://api.db.nomics.world/v22/series/IMF/WEO:latest/{ISO3}.NGDP_RPCH
GET https://api.db.nomics.world/v22/series/IMF/WEO:latest/{ISO3}.PCPIPCH
GET https://api.db.nomics.world/v22/series/IMF/WEO:latest/{ISO3}.LUR

(Real GDP growth, CPI inflation, unemployment rate.)

The 15 most recent observations from each series got stored as JSON in the page's enrichment row, then rendered as three small two-column tables under a Data & analysis heading. Then a DeepSeek prompt was given the same JSON plus the page's existing global rankings and asked to write a single 130–180 word paragraph that cited at least one specific year and value.

The validator did the work the LLM wouldn't:

def validate(text, must_digits):
    n = len(text.split())
    if n < 110 or n > 230:    return False, f"len{n}"
    if BANNED.search(text):   return False, "banned"
    if not any(d in text for d in must_digits): return False, "no-digits"
    return True, "ok"

must_digits was the formatted version of the headline values from the data block — if the model didn't reference at least one of them, the paragraph was rejected and retried. BANNED killed em-dashes, "in conclusion", "overall", first-person plurals, and other LLM tells.

The enrichment table was a single row per spine page:

CREATE TABLE wp_hs_enrichment (
  id BIGINT PRIMARY KEY,
  subject_type VARCHAR(30),     -- country_hub, figure, ranking, pillar
  subject_slug VARCHAR(150),    -- the page slug
  paragraph TEXT,               -- the 130-180w analysis
  paragraph_long LONGTEXT,      -- the 1500-2500w long-form (top 100 only)
  data_json LONGTEXT,           -- the cached data block
  word_count SMALLINT,
  model VARCHAR(50),
  prompt_version VARCHAR(60),
  KEY (subject_type, subject_slug)
);

A single template partial (enrichment-block.php) reads this row, renders the data tables specific to the page type, then renders the paragraph (or the long-form HTML, if it exists for that page). The four spine templates (country.php, figure-single.php, ranking.php, pillar.php) each include the partial with just two lines of context:

$hs_enrich_type = 'country_hub';
$hs_enrich_slug = $country['slug'];
include __DIR__ . '/enrichment-block.php';

For 520 of the 583 spine pages this short-paragraph pass was enough. For the top 100 by data depth and importance — top 40 countries by global top-20 rank coverage, top 40 figures by length of their key-contributions JSON, top 20 pillars chosen to span categories — we ran a second deeper pass.

The deep pass

The same pipeline shape, but the prompt asked for 1500–2500 words in semantic HTML, with required <h2> sections (Macro snapshot, Historical arc, Where the country leads and lags, Linked thinkers and historical figures, Comparable peers, Forward look). Each section had a 250–400 word target. Internal link slots were enumerated in the prompt so the model would weave 4–6 anchor tags to peer countries and linked figures using exact slugs.

Validation here was structural: word count between 1200 and 3000, at least 4 <h2> tags, no code fences, no banned phrases. Output was wrapped in wp_kses() allowing only h2/h3/p/ul/li/strong/em/a[href].

Average output: 1,750 words per top-100 page. Switzerland came in at 1,773 words; Galbraith's page at 1,611. The deep pages render the long-form HTML in place of the short paragraph automatically.

Cross-linking the survivors

A 583-page editorial spine only works if the pages reinforce each other. We added a "Connected on HistorySaid" pill row below the analysis paragraph on every enriched page, generated server-side from three SQLite joins:

On a country hub: linked historical figures (via figure_countries) + inquiries citing this country (via a REST endpoint that queries wp_hs_inquiries).
On a figure page: linked countries (reverse of the above).
On a ranking: top-4 country profiles for that indicator.

The pill row is rendered by the same enrichment-block.php partial, server-side, no JS — Google sees it on first crawl. Internal anchor count on a typical country hub went from ~12 to ~22 outbound links to other spine pages.

Submission

IndexNow handled Bing and Yandex immediately. A single POST per endpoint with 738 URLs in the body and both returned 200/202. Google's Indexing API is a different animal — it requires a service account added as an Owner of the GSC property, has a 200-URL/day quota per property, and only acknowledges "URL_UPDATED" pings, not Search-Console-level submission.

We queued the 583 spine survivors with the top 100 (deep-enriched) at the front and the rest behind. A daily cron at 00:00 UTC submits 150 URLs per day:

0 0 * * * /usr/bin/python3 /root/pipelines/historysaid-indexing/submit_daily.py

Day 1 hits the top 100 plus 50 of the rest. Day 4 finishes the queue. Top-priority pages get the explicit Google "URL_UPDATED" signal first, and on the day they're freshest.

What we expect and what we don't

The honest answer about recovery from a scaled-content classifier verdict is that the signal turnaround takes 2 to 4 weeks. Indexable page count doesn't snap back. What we should see, in order:

Within 7 days: GSC Crawl stats shows the indexable-page crawl rate rising.
Within 14 days: in Indexing > Pages, the "Discovered – currently not indexed" bucket starts shifting to "Crawled – currently not indexed."
Between 21 and 28 days: re-evaluation completes, indexed count starts climbing back.

If 28 days from May 15 the index is still flat at single digits, the remaining lever is trimming further — rankings, snippets, even some of the lower-data country hubs. Smaller is fine. A 200-URL curated site that gets cited by AI overviews and Wikipedia is worth more than 30,000 indexed pages no one reads.

The pages we're betting on most are linked below. They're the ones with the deepest data, the longest analysis, and the most outbound internal links to the rest of the spine.

Switzerland — top 20 in eight global indicators
Canada — natural resources + currency stability profile
Japan — long-arc demographics and debt
Singapore — sovereign wealth and trade leverage
Sweden — small-economy global rank density
Adam Smith — invisible hand, division of labor
John Maynard Keynes — depression-era policy invention
Richest countries in the world — pillar comparison hub

What we'd do differently if we started over

Two things.

First: build the spine before the long tail. We launched with the 30,000 templated pages live and the 583 spine pages buried inside them. The classifier's sample skewed thin from day one. Had the spine launched first and matured before the combinatorial pages were added, the verdict would likely have gone the other way.

Second: instrument the data block earlier. The DBnomics integration would have taken the same week if we'd done it before the deindex as it took after. Pages with real, unique, cited time-series at launch are demonstrably not templated, even if everything around them is. The classifier sees this; we just gave it nothing to see for too long.

Five URLs in the index is a number you don't forget. We'd rather not see it again.

How to find which platform streams a film, series, or live sport in Turkey

kavela — Tue, 12 May 2026 15:36:39 +0000

When users in Turkey try to watch a specific film, series, or live football match, the first question is always the same: which platform actually streams it right now? Netflix, Disney+, BluTV, Exxen, TOD, Tabii, beIN Connect, Amazon Prime, or one of the newer regional services? Here is a short, practical guide.

1) Use a "where to watch" tool

Several sites are purpose-built for this. For the Turkish market, Nereden Izlenir covers films, series, and live sports (domestic and European competitions) in one search. JustWatch covers the same problem globally, but local Turkish streaming services and regional league rights are typically more complete on a Turkey-focused source.

2) Search each platform's own catalog

If you already subscribe to one or two services, opening the app and searching directly is the fastest path. The downside is obvious: you can't see catalogs you don't have access to, and several Turkish streaming services limit catalog previews for non-subscribers.

3) Track broadcast rights for live sport

Football rights change hands more often than people expect. Within a single season, a league can move between linear TV, OTT, and hybrid bundles. The reliable approach is to check broadcaster info at the match level rather than relying on a season-start assumption — the football section of Nereden Izlenir is one source that tracks this per-fixture.

4) Ask an AI assistant — but verify

ChatGPT, Gemini, and Claude can answer "where is X streaming in Turkey", but their training data lags. A library deal that flipped last month will not be reflected. Cross-check the answer against a current database before subscribing.

5) Community sources

Reddit r/TurkishTV, the Turkish forum Donanim Haber, and Ekşi Sözlük entries are surprisingly strong for older or obscure titles. When a film leaves every major service for a long stretch, the community usually surfaces where it ended up.

Quick recap

New film or series: start with a where-to-watch tool, then check platform catalogs.
Live football: never assume — verify at the match-page level, rights shift mid-season.
Older title: combine community sources with platform search.

For deeper guides and live fixture-level broadcaster info: nereden-izlenir.com.

Originally published at nereden-izlenir.com.

Building a 2D guillotine cutting optimizer for sheet goods on Capacitor + React

kavela — Fri, 08 May 2026 06:02:33 +0000

We build small, single-purpose tools at KAVELA LTD. CutList is one of them — give it a stock sheet size and a list of pieces you need to cut, and it returns a layout that fits everything onto as few sheets as possible, with a printable PDF the carpenter can take to the saw.

It's the kind of problem that looks trivial until you start coding it. This post walks through the parts that took the most iteration: why we constrained the solver to guillotine cuts, how we handled kerf width without making the math a nightmare, and the gotchas that come with shipping jsPDF inside a Capacitor app for users who write in Turkish, Greek, Russian, Czech, and Polish.

What "panel cutting" actually means

A carpenter buys plywood, MDF, melamine, or acrylic in standard sheet sizes — 2440×1220 mm in most of Europe, 4×8 ft in North America. A project needs dozens of smaller rectangular pieces: cabinet sides, shelves, drawer fronts, backs.

The job is to figure out how to lay those pieces onto the smallest number of stock sheets, then print a cut diagram so the operator can run the panel saw without thinking about it.

This is 2D bin packing, a classic NP-hard problem. The literature is decades deep. The catch is that real-world panel saws don't make arbitrary cuts — they make guillotine cuts, which constrains the search space substantially.

Guillotine cuts: the constraint that makes everything tractable

A guillotine cut goes all the way across the sheet from edge to edge in one pass. You cannot cut an L-shape, you cannot cut around a piece, you cannot start in the middle. Every cut splits one rectangle into two rectangles.

This matches how panel saws actually work. It also dramatically simplifies the algorithm: any valid layout can be represented as a binary tree of cuts. The solver doesn't have to consider arbitrary nestings — it only has to decide, for each rectangle, whether to split horizontally or vertically and where.

The pure 2D bin-packing literature has prettier solutions (the Maximal Rectangles algorithm, skyline heuristics, or full ILP solvers) that produce tighter layouts when arbitrary cuts are allowed. None of those layouts can actually be produced on a panel saw. We deliberately rejected them.

The solver: shelf-based with first-fit-decreasing

The algorithm that ships is shelf-based. It's not the absolute tightest packer, but it is the one that produces layouts an operator can actually cut.

Sort all pieces by longest edge, descending.
For each piece, try to place it on an existing "shelf" (a horizontal strip across the current sheet) where it fits.
If no shelf fits, open a new shelf at the height of the piece.
If the new shelf doesn't fit on the current sheet, open a new sheet.
Within each shelf, pieces are placed left-to-right.

Variations we tried and rejected:

Best-fit instead of first-fit — marginal improvement on utilization, much slower, occasionally produced non-guillotine layouts depending on rotation handling.
Branch-and-bound — got tighter layouts on small inputs but didn't terminate in reasonable time once piece count crossed ~40. Carpenters routinely throw 60–100 pieces at us.
Ant colony optimization — fun to write, terrible to debug, gave answers that varied between runs (which carpenters hated — they want the same input to produce the same cutsheet).

The shipped algorithm gives 75–90% sheet utilization on typical inputs, runs in under 50 ms for 100 pieces on a phone, and is deterministic. Carpenters care more about determinism and cuttability than about a 3% utilization gain.

Kerf: the millimeter that ruins everything

A saw blade has thickness. When you cut a 2440 mm sheet in half, you don't get two 1220 mm pieces — you get two pieces that together measure 2440 minus the kerf, typically 3 mm for a panel saw blade.

Naive solvers ignore kerf. They produce layouts that don't physically work — the operator follows the diagram, makes the cuts, and discovers their last piece is 3 mm short.

The way we handle it: kerf is added to every piece's effective dimensions during packing. If the user requests a 600×400 piece with a 3 mm kerf, the packer treats it as 603×403 during placement. The PDF still labels the piece 600×400 (the actual finished dimension), but the cut layout has the kerf reserved between every pair of adjacent pieces.

This works because in a guillotine layout, every piece has cuts on at most two edges (the other two are the sheet edge or share a cut with a neighbor). Adding kerf to every dimension over-reserves slightly on edge pieces — about 1.5 mm of waste per outer edge — which is acceptable. The alternative (tracking which edges are "shared cuts" and only adding kerf there) is bookkeeping-heavy and error-prone.

The setting is exposed as a single number: kerf in millimeters. Default 3. We do not pretend to know your blade.

Rotation and grain

Some pieces can be rotated to fit better; some cannot. Veneered plywood and melamine have a grain direction — rotating a piece 90° to save a few cm of waste produces a finished cabinet with mismatched grain on the door fronts.

The packer accepts a canRotate flag per piece. When false, the piece is placed in its original orientation only. When true, the packer tries both orientations and picks whichever fits. The UI surfaces this as a checkbox per piece (default: rotate allowed) plus a "lock all rotations" toggle for cabinet projects.

We don't try to be clever about partial grain matching. The user knows their material; we trust them.

Capacitor + jsPDF + Turkish: the font VFS rabbit hole

The PDF export uses jsPDF. By default, jsPDF ships with the standard PostScript "core fonts" — Helvetica, Times, Courier — which cannot render Turkish-specific characters. ş, ğ, İ, ı, ç, Ö all render as garbage or blanks.

This is not a Capacitor bug or a bundling bug. The standard PostScript fonts genuinely don't have those glyphs. You have to embed a font that does.

The fix is jsPDF's Virtual File System (VFS). You load a TTF file, base64-encode it, register it as a VFS asset, and tell jsPDF to use it. The standard Roboto family covers Latin Extended (Turkish, Czech, Polish), Greek, and Cyrillic — exactly the locales we ship to.

import { jsPDF } from "jspdf";
import { robotoVFS } from "./roboto-vfs.js";  // base64-encoded TTFs

export function makePDF() {
  const doc = new jsPDF();
  doc.addFileToVFS("Roboto-Regular.ttf", robotoVFS.regular);
  doc.addFileToVFS("Roboto-Bold.ttf", robotoVFS.bold);
  doc.addFont("Roboto-Regular.ttf", "Roboto", "normal");
  doc.addFont("Roboto-Bold.ttf", "Roboto", "bold");
  doc.setFont("Roboto");
  return doc;
}

The VFS file is large — 424 KB after base64 encoding for Regular + Bold — and that's the cost of supporting non-Latin scripts in a client-side PDF generator. It's a one-time hit per app install, gzipped to ~280 KB on the wire if you ship it as part of the bundle.

A small build-time gotcha: the openmaptiles GitHub mirror reliably hosts Apache-2.0 Roboto TTFs at predictable paths. The google/fonts repo paths shift around and 404 from time to time. Don't burn an evening trying to fetch from there — point your build script at the mirror.

Real Google Play Billing inside a Capacitor app

CutList offers a Pro tier (PDF export, WhatsApp share, unlimited saved projects) via subscription. The implementation uses cordova-plugin-purchase v13, which works inside Capacitor when configured correctly.

The non-obvious parts:

The subscription products must exist in Play Console before the app can fetch them. During development this is the most common confusion — the SDK's purchase() call returns PRODUCT_NOT_FOUND even though the code is clearly correct. The fix is to create the SKUs in Play Console and wait ~2 hours for propagation. There is no way to test the billing flow without Play Console SKUs; the emulator can't simulate it.
Price comes from the Play Billing offer, not from your code. Hardcoding "₺50/month" in the UI breaks for users whose Play Store account is in a different region. Read the localized price string from the SKU offer object and render that.
The "Restore Purchases" button is required by Play policy. Users who reinstall, log in on a new device, or switch accounts need a way to re-acknowledge an existing subscription. The plugin exposes a refresh() method; wire a button to it.
Local entitlement cache + server verification. The plugin gives you a local isPro boolean. For a single-user offline tool like ours, that's enough — losing entitlement after a reinstall is fixed by the Restore button. Multi-device sync would need a server. We don't have one for this app, deliberately.

What's deliberately missing

Cloud sync. Saved projects live in localStorage. Reinstall the app and they're gone. We considered Firebase. We decided that the kind of carpenter who uses CutList does not want a Google account dependency for their cabinet job.
Multi-stock-size optimization. The solver assumes a single sheet size per project. If you have offcuts in two sizes, run the solver twice. The math for true multi-bin packing across heterogeneous bins is a real research problem; we won't fake it.
3D / kitchen layout. Out of scope. CutList is a sheet-cutting tool, not a cabinet designer. There are full CAD products for that — we don't compete with them.

Lessons

For domain-constrained optimization problems, the constraint is the algorithm. Guillotine cuts looked like a limitation; they were the thing that made the solver shippable.
Determinism over peak optimality when humans use the output. A 3% better layout that changes between runs is worse than a stable layout users can plan around.
Embedded fonts in client-side PDF are non-negotiable for any app supporting locales beyond ASCII. Plan the 400+ KB into your bundle budget from day one.
Subscription billing in mobile hybrid apps is mostly Play Console configuration, not code. Half a day reading the Billing v6 docs saves a week of debugging "why doesn't purchase() work."

CutList is on Google Play and the landing page is at kavela.pro/apps/Cutlist. Single-purpose, offline-first, no account, no telemetry beyond standard Play install metrics. The free tier covers small projects; Pro removes the saved-project cap and unlocks PDF export.

If you build optimization tools or panel-saw operators and want to compare notes on packing heuristics, drop a comment.

Matching camera pixels to RAL codes on Android: building a color matcher with CIE Lab and DeltaE

kavela — Fri, 08 May 2026 05:56:54 +0000

We build small, single-purpose tools at KAVELA LTD. One of them is RAL Picker — point your phone camera at a furniture surface, a wall, a sample fan-deck, and the closest RAL Classic code shows up in a stable readout. It sits at the intersection of two unforgiving problems: cameras lie about color, and "closest color" only means something if you pick the right color space.

This post walks through the parts of the build that took the most iteration: why we throw away RGB before matching, why we don't trust a single pixel, and how a 213-color lookup ended up cheaper than the camera preview itself.

Why not just match in RGB

The naive version is two lines: read the pixel under the crosshair, run a Euclidean distance over the RAL table, return the smallest. We shipped that first. It was wrong in interesting ways.

RGB is a display format, not a perceptual one. Two colors that look obviously different to a human can be RGB-near; two colors that look like the same beige can be RGB-far. The classic example is dark olive vs. dark teal — they're miles apart visually, surprisingly close in 8-bit RGB.

For anything that has to agree with human judgment — paint matching, fabric, swatches — you want a perceptually uniform color space. CIE Lab is the standard one. Equal numerical distance ≈ equal perceived difference. The metric you compute over Lab is called ΔE (delta-E).

So the matching pipeline becomes:

Camera frame → sRGB pixel
sRGB → linear RGB (undo the gamma curve)
Linear RGB → CIE XYZ (a 3×3 matrix, D65 illuminant)
XYZ → Lab (a non-linear function with a cube-root term)
Lab distance against every RAL color in the table → smallest wins

Steps 1–4 happen once per measurement. Step 5 runs against 213 RAL Classic entries — but only after we've pre-computed their Lab values once at app start. Matching at runtime is a 213-iteration loop over three floats. The camera preview costs more than the match.

fun rgbToLab(r: Int, g: Int, b: Int): FloatArray {
    val rl = srgbToLinear(r / 255f)
    val gl = srgbToLinear(g / 255f)
    val bl = srgbToLinear(b / 255f)

    // sRGB D65 → XYZ
    val x = rl * 0.4124f + gl * 0.3576f + bl * 0.1805f
    val y = rl * 0.2126f + gl * 0.7152f + bl * 0.0722f
    val z = rl * 0.0193f + gl * 0.1192f + bl * 0.9505f

    // XYZ → Lab (D65 reference white)
    val fx = labF(x / 0.95047f)
    val fy = labF(y / 1.00000f)
    val fz = labF(z / 1.08883f)

    return floatArrayOf(
        116f * fy - 16f,
        500f * (fx - fy),
        200f * (fy - fz)
    )
}

srgbToLinear is the standard piecewise gamma function; labF is the cube-root-with-linear-tail. Both fit in five lines. The whole conversion is ~20 lines of Kotlin and runs in microseconds.

ΔE 76 vs ΔE 2000: why we kept the simple one

The ΔE formula has gone through three revisions. ΔE 76 is plain Euclidean distance in Lab. ΔE 94 weights chroma and hue. ΔE 2000 (CIEDE2000) adds rotation terms to fix asymmetries in the blue region and is the modern standard for graphic-arts work.

We tried 2000. We shipped with 76.

The reason is honest: the camera is a much bigger source of error than the metric. Auto white balance is shifting the color cast by ΔE 5–10 frame to frame. Sensor noise on a single pixel is ΔE 2–4. The difference between ΔE 76 and ΔE 2000 across the 213 RAL Classic colors is, in practice, a few edge-case calls — enough to matter for a print shop, not enough to matter when the user is holding a phone three feet from a couch under fluorescent light.

Spending CPU on CIEDE2000 to shave error the camera can't deliver was a misallocation. We'll revisit if we add tristimulus colorimeter input — but that's a different product.

Sampling: one pixel is a lie

A single pixel under the crosshair is too noisy to read straight. JPEG compression artifacts, sensor noise, and the fact that real-world surfaces have sub-pixel grain mean that two consecutive frames on the same target can return colors that are visibly different.

Our v1 averaged a 5×5 patch under the reticle. Better, but still wrong on textured surfaces — woodgrain, brushed metal, fabric weave. The patch averages across high-frequency detail and drifts toward gray.

The current heuristic: take the central pixel and a few neighbors at a fixed offset, drop the highest and lowest L* outliers, then average. It's not statistically rigorous — it's a cheap noise filter that respects the user's intent ("I pointed at that spot, not that spot blurred with its neighbors"). The CameraX preview pipeline gives us the frame as a YUV_420_888 image; we sample directly from the Y/U/V planes rather than converting the whole frame to RGB and then sampling, which would be order-of-magnitude wasteful for a 5-point read.

The next iteration on the v1.5 backlog is multi-point sampling — three sample points in a small triangle under the crosshair, with the user able to see the auxiliary points. Average the three Lab values, return the match. It trades a small UI complication for noticeably more stable readings on textured material. The matching code already accepts a list of Lab samples, so the change is mostly UI.

The RAL table is small enough that everything is free

213 colors. Pre-converted to Lab at app start, that's 213 × 3 floats = ~2.5 KB in memory. The full RAL Classic name table with multilingual names is the larger asset — DE/IT/FR/EN/TR labels per code, around 30 KB in resources.

Match cost: 213 × (3 subtractions + 3 squarings + 1 sqrt) per measurement. On a Pixel 6 it's well under a millisecond. The bottleneck for us is camera preview throughput (~30 fps), not the matcher.

This is worth saying because it would be easy to over-engineer. A k-d tree in Lab space sounds clever and would be slower than the linear scan at this size — branch mispredictions and cache misses on a tree traversal lose to a tight loop over a contiguous float array. Bigger RAL sets — RAL Design (1625 colors), RAL Effect (490) — are on the v1.5+ backlog. Even at 1625, a linear scan over pre-computed Lab values is sub-millisecond. We won't reach for spatial indexing until we have a reason.

RAL → HEX/RGB/CMYK: the inverse problem (sort of)

In v1.4 we added a detail sheet: tap any RAL Classic card and you get HEX, RGB, and CMYK values for that code, each one-tap copyable.

HEX and RGB are direct — RAL Classic ships with sRGB equivalents in its specification. CMYK is harder, and we ship it with a disclaimer.

The honest answer: there is no single "correct" CMYK for a RAL code without a target ICC profile. CMYK is a process color space; the actual output depends on the printer, the ink set, the paper, and the rendering intent. What we ship is an indicative sRGB-to-CMYK conversion using a standard naive transform — useful for "I need a starting CMYK in Illustrator," not for "I'm going to color-manage a press run from this." The detail sheet shows the disclaimer next to the CMYK row. A designer who needs a press-accurate CMYK will run the swatch through their own ICC pipeline. We're not pretending to replace that.

CameraX, not Camera2

Native Camera2 gives more control but the wiring is a small project. CameraX with the Preview + ImageAnalysis use-case binding is one screen of setup, handles lifecycle automatically, and gives us the YUV_420_888 analysis frames we need without writing a state machine.

The one rough edge worth flagging: when you bundle play-services-ads (we added an AdMob banner in v1.3) alongside CameraX, Gradle dependency resolution will try to merge com.google.guava:guava:31.x (pulled by ads) with CameraX's com.google.guava:listenablefuture stub. Build fails with Cannot access ListenableFuture.

The fix is to use play-services-ads-lite instead of the full play-services-ads. lite drops the mediation transitive deps, including the conflicting Guava artifacts. Banner ads work fine; mediation networks would need the full dependency, but we don't run mediation. One-line fix in build.gradle.kts, but it took an evening to find — leaving it here so the next person doesn't burn the same evening.

R8 is off, on purpose, for now

We deploy from a 12 GB VPS. R8 minification under the standard Android Gradle Plugin defaults runs out of heap on this box — the daemon dies mid-pass, the build dies with it. AAB without R8 is ~13.8 MB; with R8 it would be 4–5 MB. Play's dynamic delivery softens the install-size impact, but it's still a real cost.

Two ways out, both on the v1.5 backlog: build on a beefier machine (WSL on a 32 GB workstation, or GitHub Actions with the Play SA key as a secret), keep deploying from VPS via SCP. We'll likely move to the CI route — the 30-second AAB upload from VPS is fine, the 4-minute Gradle build is not.

What's on the backlog

The next two cycles are aimed at things that move the matching needle, not surface area:

Multi-point sampling (described above) — bigger stability win than any algorithm change at this point.
Broader RAL sets — Classic is 213 colors and covers the most common asks; RAL Design (1625) is what professional painters actually want. Same pipeline, larger table, same sub-millisecond match.
Color export — CSV and Adobe .ase swatch export from history + favorites. Designer/painter workflow. The data structure is already there; it's a serializer + share intent.

We've avoided the temptation to chase ΔE 2000 or k-d trees. The error budget on this app is dominated by the camera, not the math, and the math is small enough that "linear scan over pre-computed Lab" is the right answer for the foreseeable future.

RAL Picker landing page: kavela.pro/apps/ralpicker (Play Store link + screenshots there). It's a single-purpose tool — camera in, RAL code out, no account, no upload, no telemetry beyond the AdMob banner. The full RAL Classic table works offline.

If you build perceptual color tools and want to compare notes on sampling strategies or larger RAL sets, drop a comment.

Pruning a Sitemap From 327,621 URLs to 2,483 (And Why That Was the Right Call)

kavela — Thu, 07 May 2026 19:49:40 +0000

evrangeguide.com is a programmatic SEO site for EV charging routes — 27 countries, 370 cities, 24 vehicles, and over two thousand canonical inter-city routes. When we totaled the URL space across every page-type combination, we got 327,621 URLs. We listed every one of them in the sitemap.

Google's response was decisive: it indexed almost none of them, and the crawl-budget metrics in Search Console got worse week over week.

This is the story of cutting that sitemap by 99.2% — down to 2,483 URLs — and why scarcity beat coverage.

The architecture

ev-engine is the same family of virtual-routing plugin we use across the network. No wp_posts. The data lives in custom tables:

Table	Rows	Holds
`wp_ev_routes`	2,044	inter-city routes (from-to pairs)
`wp_ev_cities`	370	city metadata + lat/lng
`wp_ev_countries`	27	country hubs
`wp_ev_vehicles`	24	EV models with battery + range specs
`wp_ev_route_stations`	~2.36M	charger stops along each route
`wp_ev_route_vehicles`	147K	per-vehicle stop plans (route × vehicle)

From these, the router (EV_Router) projects URL patterns:

/route/{from}-to-{to}/ — 2,044 base routes
/route/{from}-to-{to}/{vehicle}/ — 2,044 × 24 = ~49K route-vehicle pages
/route/{from}-to-{to}/{tier}/ — long/mid/short-range tier views
/city/{slug}/, /country/{slug}/, /ev/{vehicle}/, /glamour/{slug}/
Index pages: /routes/, /cities/, /countries/, /vehicles/, /glamour/
Multilingual prefixes for tr/de/fr/es (UI-translated, body still EN)

Multiplied across language prefixes and tier slices, the total grew past 327K.

Why we listed all of them

The naive pSEO instinct: more URLs is more surface area. If a long-tail searcher types "Tesla Model 3 Berlin to Munich charging stops," we wanted a page tuned to that exact query. So we generated /route/berlin-to-munich/tesla-model-3/ and 49K of its siblings, then submitted them all.

Google's reality: for a new EV-niche domain with low brand authority, indexing budget is allocated as a function of trust × content uniqueness × discovery cost. We were maximizing the third variable while having close to zero of the first.

What the data showed

Two months in, GSC had:

~327K URLs discovered but flagged "Crawled – currently not indexed" or "Discovered – currently not indexed"
A handful of routes indexed; most of the indexed pages were the small set of unique hubs (countries, vehicles)
Crawl rate stuck low because Google was sampling thousands of near-duplicate route-vehicle and tier slices

The route-vehicle pages were structurally near-duplicate: same route metadata, same map, same station list, with one paragraph of vehicle-specific stop math. The tier pages (long/mid/short-range) were even worse — they sliced the same route by an arbitrary battery-range threshold.

We had built thousands of pages that, from Google's perspective, were dilutions of one good page.

The prune

We kept what was structurally unique and dropped the rest from the sitemap:

Page type	Kept in sitemap	Behavior
Routes	2,044	indexable, in sitemap
Cities	370	indexable, in sitemap
Countries	27	indexable, in sitemap
Vehicles	24	indexable, in sitemap
Glamour pages	18	curated highlights, in sitemap
Route × tier	0	`noindex, follow`
Route × vehicle	0	`noindex, follow`
Multilingual (non-EN)	0	`noindex` (UI translation only, body EN)

The templates that emit noindex, follow (route-range.php, route-vehicle-weather.php) keep crawl flow going — internal links from these pages still pass discovery to the canonical route page — but the pages themselves do not compete for index slots. New sitemap total: 2,483 URLs.

The single-DB migration that made this possible

A quick aside, because it's part of the same story. Until early May, the EV data tables (wp_ev_*) lived on a separate Hostinger Business database, while the WP core tables lived on contabo. The plugin had a custom ev_data_db() function that returned a second wpdb connection.

This worked for a year, then quietly broke after a Hostinger network change: cross-host queries started timing out under crawler load. The fix:

Whitelist contabo's IP on Hostinger Remote MySQL (92.113.22.128, IPv4-forced — their host is IPv4-only).
Dump the EV tables (88MB gzipped) and import into wp_evrangeguide on contabo's local MariaDB.
Patch ev_data_db() to return $wpdb directly. Single connection from then on.

public function ev_data_db() {
    global $wpdb;
    return $wpdb;
}

While we were in there we caught one more bug: a related-routes query that was casting distance into a BIGINT UNSIGNED and underflowing on "distance less than threshold" comparisons. Fix:

WHERE CAST(r.distance_km AS SIGNED) < {threshold}

Without that cast, MariaDB was returning empty result sets for any negative comparison (which is half of all related-route queries).

IndexNow + GSC API

We re-submit the pruned sitemap via the GSC Sitemaps API after each rebuild. The shared service account (nererdenizlenir@dekorfiyat.iam.gserviceaccount.com) is siteOwner on every property in our network, so a single key lets us PUT to:

https://www.googleapis.com/webmasters/v3/sites/{site}/sitemaps/{feedpath}

Returns 204; Google re-fetches the sitemap within 24h. We push fresh URL changes via IndexNow (key at the webroot, batched to api.indexnow.org). Google's old /ping?sitemap= endpoint is fully deprecated since 2023, so the GSC API path is the only programmatic re-submit channel.

What we'd tell our March-self

For a new pSEO domain, the right shape of the sitemap is:

Hubs first. Cities, countries, vehicles, categories — these are the pages with strongest internal-link equity and the easiest to make uniquely useful.
One canonical layer per long-tail query. Routes go in; route-vehicle and route-tier slices stay live but noindex, follow. Internal navigation is the discovery path; the sitemap is the indexing intent.
Cap multilingual to noindex until you have the translation budget for genuine localization. Body-language mismatch (English body under a de/ URL) is worse than no localization at all.
Resubmit via the GSC API after every prune. It's a 204 response. Don't wait for Googlebot to discover the change.
Watch "Discovered – currently not indexed" as a primary metric, not a footnote. If it's growing faster than "Indexed," you're polluting the surface area.

When you're sitting on 327K URLs and Google is indexing a few hundred, the fastest path to better indexing isn't more content. It's less surface area, sharper hubs, and patience.

Live site: evrangeguide.com

Why Every Internal Link On Our Site Was a 404 (And the Smart-301 That Fixed It)

kavela — Thu, 07 May 2026 19:46:04 +0000

BeverageIndex.com is a programmatic SEO site indexing drink prices across 98 cities and 30 drinks. 3,728 indexable URLs. We launched it in March, submitted the sitemap to Google Search Console, and watched indexing flatline at zero for two full months.

When we finally crawled our own site with a friendly bot, the cause was embarrassingly simple: every drink and city link in our internal navigation pointed to a URL that returned 404.

The setup

bi-engine is a virtual-routing WordPress plugin (no wp_posts) backed by these tables:

Table	Rows	What it holds
`wp_bi_drinks`	30	id=slug; category enum: coffee/beer/wine/spirits/tea/juice/specialty/soft_drinks
`wp_bi_cities`	98	id=slug, country, currency, lat/lng
`wp_bi_prices`	2,940	drink × city, USD-normalized
`wp_bi_routes`	3,727	pre-computed `/{drink}/{city}/` paths

The theme is unusual for a pSEO site: a single SPA terminal template (template-terminal.php) hydrates from a JSON payload + /wp-json/bi/v1/enriched. Country hubs, city hubs, drink hubs, category hubs, and city × drink price grids all render through the same client-side template, distinguished by URL pattern.

The URL patterns

The canonical paths are namespaced:

/category/{slug}/ — coffee, beer, wine, etc.
/city/{slug}/ — amsterdam, tokyo, istanbul…
/drink/{slug}/ — americano, cappuccino, coca-cola…
/country/{slug}/ — by name slug or ISO2
/{drink}/{city}/ — 3,727 price grids (e.g. /americano/amsterdam/)

The last pattern is the workhorse. It's also where the sitemap gets most of its URLs.

The bug

Look at any internal hub page on the site. The HTML is full of links like:

<a href="/americano/">Americano</a>
<a href="/amsterdam/">Amsterdam</a>
<a href="/coffee/">Coffee</a>

Not /drink/americano/. Not /city/amsterdam/. Slug-only.

And slug-only didn't have a route. Googlebot crawled the homepage, followed every drink link, and got 404. Crawled the city hub, followed every drink × city link → those worked because /{drink}/{city}/ was registered. But the hub anchors themselves were dead.

We had two reasonable choices:

Rewrite all the internal links to use the canonical /category/, /drink/, /city/, /country/ prefixes. Hundreds of edits across the SPA template and JSON payload generators.
Make slug-only URLs work, by 301-redirecting them to the canonical prefixed form.

We picked (2) because it's also good externally — third parties linking to beverageindex.com/cappuccino/ will resolve correctly forever, even if our internal nav drifts.

The smart-301

We added a single rewrite rule, bi_slug_only, plus a handler that does a 4-way disambiguation:

public function maybe_slug_redirect($slug) {
    // 1. Drink?
    if ($this->db->drink_exists($slug)) {
        wp_redirect("/drink/{$slug}/", 301); exit;
    }
    // 2. City?
    if ($this->db->city_exists($slug)) {
        wp_redirect("/city/{$slug}/", 301); exit;
    }
    // 3. Category? (URL hyphen ↔ DB underscore)
    $cat_db = str_replace('-', '_', $slug);
    if (in_array($cat_db, $this->categories, true)) {
        wp_redirect("/category/{$slug}/", 301); exit;
    }
    // 4. Country? (ISO2 or full-name slug)
    if ($country = $this->db->country_lookup($slug)) {
        wp_redirect("/country/{$country->slug}/", 301); exit;
    }
    // Unknown — explicit 404 with theme template
    status_header(404);
    include get_404_template();
    exit;
}

The ordering matters: drinks shadow categories shadow countries. We had two collisions to handle — coffee and soft-drinks are both potential drink slugs and category slugs. They're categories, so we resolved by reordering the lookups (drink table first, but neither coffee nor soft_drinks is in wp_bi_drinks, so they fall through to the category check correctly).

The other 301 we didn't notice

While fixing this, we caught a second crawl-budget leak. The sitemap index bi-sitemap.xml and its five children (bi-sitemap-cities.xml, bi-sitemap-drinks.xml, bi-sitemap-categories.xml, bi-sitemap-countries.xml, bi-sitemap-prices-1.xml) were all 301-redirecting to a trailing-slash variant before serving. Every Googlebot fetch was burning two requests instead of one.

WordPress's redirect_canonical filter was the culprit. The fix:

add_filter('redirect_canonical', [$this, 'skip_canonical_for_sitemap'], 10, 2);

public function skip_canonical_for_sitemap($redirect_url, $requested_url) {
    if (strpos($requested_url, 'bi-sitemap') !== false) {
        return false;
    }
    return $redirect_url;
}

Now all six sitemap files return 200 directly.

IndexNow: not all engines treat all domains equally

We push fresh slugs to IndexNow on publish. beverageindex's IndexNow key (0047e09268ff…ccb) is happily accepted by both Yandex (HTTP 202) and api.indexnow.org / Bing (HTTP 200).

Meanwhile, our sister site assetvs.com gets 403 UserForbiddedToAccessSite from api.indexnow.org, while Yandex still accepts. Same key format, same payload, different domain reputation — assetvs probably tripped a Bing trust signal during its 2-month zero-indexing window. The lesson: if you operate a network, don't assume IndexNow status from one domain transfers to another. Test each.

Where indexing actually stands

Fix shipped. Sitemap re-submitted via the GSC Sitemaps API (PUT returns 204). 3,728 URLs are now linkable from anywhere on the site. Whether that converts into indexing in the next two weeks is the real test.

The broader lesson is one we've now repeated across multiple network properties: for a new pSEO domain, the technical indexing tax shows up as silent 4xx noise, not as a single dramatic failure. A trailing-slash 301 here, a 404'd internal link there, a missing FX table on another property. None of it is loud. All of it compounds.

Live site: beverageindex.com

Lighting Up 2,232 Dead URLs With One World Bank API Call

kavela — Thu, 07 May 2026 19:46:03 +0000

On assetvs.com we run a programmatic SEO site comparing investment returns across 16 countries and 5 asset classes (stocks, bonds, real estate, bills, gold). The site has 3,655 URLs in the sitemap. Last week we discovered that 2,232 of them — every currency-pair page — were silently 404ing.

This is the story of how one missing table turned a third of our site dark, and how a single World Bank endpoint brought it all back online in an afternoon.

The architecture (quick recap)

assetvs uses the same virtual-routing pattern we've written about before on the network: no wp_posts, just custom tables and rewrite rules. The relevant tables are:

Table	Rows	Purpose
`wp_ir_countries`	16	G7 + EU + ANZ + JP, ISO codes
`wp_ir_assets`	5	stocks/bonds/real-estate/bills/gold
`wp_ir_annual_returns`	3,955	nominal returns, country × asset × year
`wp_ir_inflation`	976	for real-return calculation
`wp_ir_comparisons`	100	full-period asset-vs-asset summaries
`wp_ir_exchange_rates`	0 → 976	currency pair history

That last row is the punchline.

The URL patterns

The router (IR_Router) maps slugs onto eight page types:

/{country}/ and /{asset}/ — 21 hubs
/{country}/{asset}/ — 80 country-asset combos
/{country}/{asset_a}-vs-{asset_b}/ — 100 comparisons (alphabetical canonical)
/{country}/{asset_a}-vs-{asset_b}/{decade}/ — sliced by 1960s–2010s
/{asset}/{country_a}-vs-{country_b}/ — 600 cross-country comparisons
/currencies/{a}-{b}/ and /currencies/{a}-{b}/{year}/ — 2,232 currency pages
/rankings/{asset}/, /about/, /data/, etc.

Everything renders server-side from the custom tables. No posts, no taxonomies, no theme fight.

The bug, hiding in plain sight

The currency builder (build_currency_pair) had this shape:

$history = $this->get_fx_pair_history($a, $b);
if (empty($history)) {
    return null;
}
// ...render the page

Returning null from the page builder cascades up to a 404. Reasonable defense — but wp_ir_exchange_rates had never been populated. We'd built the schema, written the renderer, generated 2,232 rewrite rules, listed the URLs in the sitemap, and submitted them to Google Search Console for two months. All of them 404.

GSC's coverage report had been flagging "Not found (404)" for weeks, but with 3,655 URLs and zero indexed pages overall (new domain trust issues), nobody had drilled into which URLs were 404 vs which were just unindexed-but-live.

The fix: one API

The World Bank publishes annual official exchange rates as indicator PA.NUS.FCRF, free, no auth:

https://api.worldbank.org/v2/country/{ISO}/indicator/PA.NUS.FCRF?date=1960:2020&format=json

For each of our 16 countries, that's one HTTP call returning 61 annual rates against USD. Sixteen calls total. We wrote a one-shot fill script:

foreach ($countries as $c) {
    $rows = wb_fetch($c->iso2);
    foreach ($rows as $year => $rate) {
        $wpdb->insert('wp_ir_exchange_rates', [
            'country_id' => $c->id,
            'year'       => $year,
            'rate_per_usd' => $rate,
            'yoy_change' => $yoy,
        ]);
    }
}

Result: 976 rows. The currency pair builder derives any (A, B) pair by triangulating through USD — so 16 base currencies give us C(16,2) × 2 directions × 61 years × a few view modes = the 2,232 URLs we'd been promising Google for two months.

Lessons we keep relearning

1. "Sitemap submitted" ≠ "page renders." GSC will happily accept a sitemap of 404s and quietly file them under "Discovered – currently not indexed" or "Not found." There is no alarm. Build a synthetic monitor that hits one URL per page-type weekly and asserts HTTP 200. We'd have caught this in March.

2. Returning null from a builder is a foot-gun. The render path collapses three failure modes into one 404: missing data, missing slug, and genuinely-unknown URL. Now we log a distinct warning when a known-routed slug returns null, so missing tables surface in error logs instead of just the GSC dashboard.

3. New-domain GSC trust dwarfs technical issues. Even with the FX table filled, indexing didn't snap back overnight. We're at zero indexed pages two months in, and lighting up the currency tier is a prerequisite for indexing — but not sufficient. Content depth is next: stock index names per country, country-level macro from World Bank, narrative blocks generated through our LLM pool.

4. Cross-site editorial linking is cheap and free crawl signal. We added a historysaid_for_country($slug) linker that pulls the top 5 inquiries citing the country slug from historysaid.com/wp-json/hs/v1/inquiries/by-country/{slug} (12-hour transient cache) and renders them as a "Further Reading" block. United States gets 8 inquiries, Germany 10, UK 10. Two sites, one network, free internal-link equity.

What's next

Fill stock_index_name per country (S&P 500, FTSE 100, DAX, Nikkei…) so each /{country}/stocks/ page has a named anchor instead of generic "stocks."
Pull World Bank macro (GDP per capita, inflation regime, currency volatility) into country hubs.
Generate narrative paragraphs (~150 words) per country-asset page through our LLM pool — same enrichment pattern we use on HistorySaid's 18,304 indicator pages.

The larger lesson: when 2,232 URLs go dark, it usually isn't an SEO problem. It's a missing INSERT.

Live site: assetvs.com

Turning World Bank Data Into 50K+ Searchable Pages with WordPress

kavela — Tue, 24 Mar 2026 11:28:45 +0000

What if you could make decades of World Bank and IMF economic data actually accessible and browsable - not buried in spreadsheets and PDF reports that nobody reads?

That's what we built with historysaid.com: a programmatic SEO site that transforms raw international development data into 50,000+ structured, searchable pages. Every country, every indicator, every year - all queryable, all browsable, all indexed by Google.

This post covers the architectural thinking behind it and what we learned building it.

The Data Problem

The World Bank and IMF publish some of the richest economic datasets on the planet:

GDP, inflation, trade balances, debt levels for 200+ countries
Time series spanning 60+ years (some indicators go back to the 1960s)
Hundreds of unique economic indicators covering everything from agricultural output to internet penetration rates
Regular updates as new data gets published quarterly or annually

But the official portals are designed for researchers and economists who already know what they're looking for. You need to know specific indicator codes and use clunky query builders to extract data into spreadsheets.

There's no way to just... explore. To browse. To stumble upon interesting economic stories by clicking around.

We wanted to change that.

Why Not Just Build a Dashboard?

We considered building a single-page dashboard app with interactive charts and filters. But dashboards have a fundamental SEO problem: they're one URL. Google can't index the state of your filters. If someone searches "Turkey GDP growth history", a dashboard app won't rank because that specific view doesn't have its own URL.

Programmatic SEO solves this. Each unique combination of country + indicator gets its own page, its own URL, its own title, and its own meta description. Google can index all 50K of them.

We chose WordPress for the same reasons we used it for startup-cost.com (see our previous post): cheap hosting, familiar ecosystem, and a powerful rewrite engine that nobody uses to its full potential.

The Architecture - Overview

We built a custom WordPress plugin that handles everything from data ingestion to page rendering.

Data Pipeline

The data flows through several stages:

World Bank API --> Fetch & Parse --> Validate --> Normalize --> MySQL
IMF Data Portal --> Fetch & Parse --> Validate --> Normalize --> MySQL
                                                                  |
MySQL --> Virtual URL Routing --> Template Engine --> HTML Page

Each data source has its own ingestion logic because the API formats differ significantly. The World Bank provides a well-documented REST API with JSON responses, while IMF data comes in a different structure. We wrote adapters that normalize both into a common internal format.

The pipeline runs on a scheduled basis. When new data is published by either source, our next run picks it up automatically and updates the relevant records.

Data Quality Challenges

Working with international economic data is messier than you'd expect:

Missing values everywhere - Some countries don't report certain indicators for certain years. We handle nulls gracefully rather than showing zeros.
Delayed reporting - Some nations publish data 2-3 years late. Our pages show the most recent available data and clearly indicate the time period.
Unit inconsistency - Some values are in current USD, some in constant USD, some in percentages. Each indicator carries its unit metadata.
Country code mismatches - The World Bank uses ISO 3166-1 alpha-3 codes, the IMF sometimes uses its own codes. Our normalization layer handles the mapping.

Database Design Principles

We use custom MySQL tables (not wp_posts) following the same pattern from our startup-cost.com engine. The key design decisions:

Proper normalization - Countries, indicators, and data points are separate tables with foreign key relationships
Appropriate data types - We use high-precision decimal types for economic values because the data ranges from tiny percentages to trillion-dollar GDP figures. Floating point would introduce precision errors that data-savvy users would notice.
Strategic indexing - Our most common query patterns (all data for a country+indicator, all countries for an indicator+year) each have compound indexes that resolve in single-digit milliseconds
Roughly 4 million data points in the main table, all queryable in under 10ms thanks to proper indexing

Page Types

Our routing creates four types of pages:

Country pages - Overview of all available indicators for a country. Shows key stats, latest values, and links to explore each indicator in depth.
Country + Indicator pages - The core of the site. Detailed time series data with charts, data tables, summary statistics, and trend analysis. This is the bulk of our 50K pages.
Indicator pages - Global comparison view. Shows all countries ranked by a specific indicator, with the ability to see how they compare.
Comparison pages - Side-by-side country comparisons for a given indicator. Perfect for searches like "Japan vs South Korea GDP."

Zero rows in wp_posts. Everything is computed from data tables on each request (with caching for popular pages).

Charts and Data Display

Each data page includes an interactive chart (using a lightweight client-side charting library) and a full data table. The chart data is embedded as JSON in the page - fast, cacheable, and SEO-friendly since the actual values are also present in the HTML table.

We also calculate and display summary statistics: latest value, historical min/max, average, and trend direction. These make each page genuinely informative rather than just a raw data dump.

SEO Strategy

Every page gets unique, data-driven SEO elements:

Dynamic titles that include the country name and indicator name
Meta descriptions that include actual data values ("Turkey's GDP was $X in 2024. Explore the full trend from 1960 to 2024...")
Schema.org Dataset markup so Google understands these are data pages with temporal and spatial coverage
Breadcrumb navigation for clear site hierarchy

Each meta description contains real numbers from the data, making every page genuinely unique in Google's eyes.

Caching

With 50K+ pages, not everything can be pre-cached. We use a tiered approach:

Popular combinations (major countries + major indicators) get pre-cached with longer TTLs
Medium-traffic pages are cached on demand
Long-tail pages have shorter TTLs and are generated fresh when needed

We also cache aggregated data (like country rankings and regional averages) at the query level since multiple pages reference the same aggregations.

Internal Linking

Strong internal linking is essential for a site this large. Without it, search engines would never discover most of the pages:

Each country page links to all available indicators for that country
Each indicator page links to top countries for that indicator
Breadcrumbs on every page create clear hierarchy
Related content suggestions based on geographic and thematic proximity
Comparison links suggest relevant country pairs

The internal link graph ensures that any page on the site is reachable within 3-4 clicks from the homepage.

Results After 12 Months

50,000+ pages indexed in Google Search Console
Average TTFB: ~150ms on shared hosting
Database queries consistently under 10ms
Zero editorial work - the site runs itself, updated automatically from source data
Growing organic traffic from long-tail searches like "Nigeria inflation rate 2015" or "Vietnam GDP per capita history"

The Reusable Pattern

This is the same architectural pattern we use across multiple sites at Kavela:

Find an interesting, structured dataset
Design custom tables optimized for the specific data model
Build a data pipeline that keeps the database fresh
Use WordPress virtual routing to create SEO-friendly URLs
Render pages dynamically from the data
Generate chunked sitemaps and build strong internal links

The key is making sure every page offers genuine value - real data, real calculations, real insights. Template spam with swapped-out city names won't work. Search engines are smart enough to detect that. But if every page genuinely answers a different question with different data, you've built something valuable.

Explore it yourself: historysaid.com

Built by Kavela Ltd - turning data into discoverable web experiences.

DEV Community: kavela

Launching PlatformTrack: a weekly-synced streaming availability index for 17 platforms x 57 countries

What it is

The architecture

Source: TMDB Watch Providers

Storage: 5-table schema

Frontend: WordPress + virtual URLs

Sync pipeline

Some findings from launch week

What's next

Build a "Where to Watch" feature in 50 lines with the StreamWatchHub API

What the API returns

1. Sign up and obtain an API key

2. A minimal Node client

3. A drop-in React component

Current coverage

Roadmap

Try it

Building a 32-URL economy microsite on top of a 754,000-row SQLite dataset

Building a 32-URL economy microsite on top of a 754,000-row SQLite dataset

Why 32 URLs

Virtual routing without WP_Query

Two slugs per country

Two custom tables: structure plus narrative

The page-level "linker matrix"

Sitemap and JSON-LD discipline

What we kept out

What's published

How we repurposed a Cloudflare Email Worker domain into a SaaS without taking it down

The problem

The architecture

The SaaS

Why share a domain at all

What we'd do differently

Takeaway

How we recovered from a 30,000 to 5 Google deindex on a programmatic SEO site

The site, briefly

Diagnosis

Surgery 1: shed the templated mass

Surgery 2: thicken the spine with real data + analysis

The deep pass

Cross-linking the survivors

Submission

What we expect and what we don't

What we'd do differently if we started over

How to find which platform streams a film, series, or live sport in Turkey

1) Use a "where to watch" tool

2) Search each platform's own catalog

3) Track broadcast rights for live sport

4) Ask an AI assistant — but verify

5) Community sources

Quick recap

Building a 2D guillotine cutting optimizer for sheet goods on Capacitor + React

What "panel cutting" actually means

Guillotine cuts: the constraint that makes everything tractable

The solver: shelf-based with first-fit-decreasing

Kerf: the millimeter that ruins everything

Rotation and grain

Capacitor + jsPDF + Turkish: the font VFS rabbit hole

Real Google Play Billing inside a Capacitor app

What's deliberately missing

Lessons

Matching camera pixels to RAL codes on Android: building a color matcher with CIE Lab and DeltaE

Why not just match in RGB

ΔE 76 vs ΔE 2000: why we kept the simple one

Sampling: one pixel is a lie

The RAL table is small enough that everything is free

RAL → HEX/RGB/CMYK: the inverse problem (sort of)

CameraX, not Camera2

R8 is off, on purpose, for now

What's on the backlog

Pruning a Sitemap From 327,621 URLs to 2,483 (And Why That Was the Right Call)

The architecture

Why we listed all of them

What the data showed

The prune

The single-DB migration that made this possible

IndexNow + GSC API

What we'd tell our March-self

Why Every Internal Link On Our Site Was a 404 (And the Smart-301 That Fixed It)

Virtual routing without `WP_Query`