Mapper catalog row count

What it measures

The total number of rows in the endpoints table of mapper.db.

How it is produced

The canonical query counts table rows, distinct URL strings, and duplicate URL rows. Supporting queries decompose the same table by parsed host and discovery source.

Canonical count and URL uniqueness

SELECT
  COUNT(*) AS rows,
  COUNT(DISTINCT url) AS distinct_urls,
  COUNT(*) - COUNT(DISTINCT url) AS duplicate_url_rows
FROM endpoints;

Domain decomposition

WITH parsed AS (
  SELECT
    id,
    url,
    CASE WHEN instr(url,'://')>0 THEN substr(url,instr(url,'://')+3) ELSE url END AS rest
  FROM endpoints
),
hosts AS (
  SELECT lower(CASE WHEN instr(rest,'/')>0 THEN substr(rest,1,instr(rest,'/')-1) ELSE rest END) AS host
  FROM parsed
)
SELECT
  COALESCE(NULLIF(host,''),'(blank)') AS host,
  COUNT(*) AS endpoints,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM endpoints), 6) AS pct
FROM hosts
GROUP BY host
ORDER BY endpoints DESC
LIMIT 25;

Source decomposition

SELECT
  registry_source,
  COUNT(*) AS n,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM endpoints), 6) AS pct
FROM endpoints
GROUP BY registry_source
ORDER BY n DESC
LIMIT 20;

Limits and assumptions

• This is a catalog-table count, not a complete-universe count.
• The result depends on source lists and crawler coverage, and the table does not guarantee completeness.
• The row count hides host and discovery-source concentration; the aggregate is misleading without them.
• The deterministic replacement defines canonical URL normalization and deduplication, stores each source observation separately, requires source-coverage reports, and publishes both catalog rows and source-independent distinct hosts or URLs discovered by deterministic crawlers.

How to refute this

1. Re-run the canonical SQL and get a different row count.
2. Find URL canonicalization duplicates that make COUNT(*) overstate distinct endpoints.
3. Run a broader crawler/source import and show large numbers of valid endpoints missing from endpoints.

Last validated

2026-06-08