# Data tables: indexation and served pages

SpeedWorkers exposes two tables in the Botify data model, queryable in BigQuery Standard SQL (through the `tables_*` MCP tools or BQL). Always prefix with `catalog.`:

| Table | Source | One row per |
|---|---|---|
| `catalog.speedworkers_indexed_pages` | SpeedWorkers | indexation / refresh event — an attempt to fetch, render and cache a URL |
| `catalog.speedworkers_served_pages` | SpeedWorkers | bot request handled by SpeedWorkers |

The split mirrors the product: **indexed** is the write side (what went into the cache and how), **served** is the read side (what bots actually received). Inventory Monitoring and Render Quality are built on the first; Delivery Center and the speed figures on the second. `date` is a UTC timestamp in both.

For the history of specific URLs, `speedworkers_page_history` (see `tools.md`) runs this kind of query for you, resolves the URLs the way SpeedWorkers does, and hands out event IDs to read the stored HTML. Write SQL for aggregates, distributions and joins.

Neither table is a snapshot of current state — both are event logs. To get the latest state of a URL, take the most recent row per URL. Refresh periods can be up to 60 days, so a seven-day lookback misses still-current cached URLs. Use a lookback at least as long as the longest refresh period (60 days is a safe default); this is the latest state among URLs that emitted an indexation event in that window:

```sql
SELECT * FROM catalog.speedworkers_indexed_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 60 DAY)
QUALIFY ROW_NUMBER() OVER (PARTITION BY url, pageDeviceId ORDER BY date DESC) = 1
```

## `speedworkers_indexed_pages`

**Identity and scope** — `url`, `device` / `pageDevice` / `pageDeviceId` (0 unknown, 1 desktop, 2 mobile, 3 both), `key`, `urlHash` / `urlFnv64Hash`.

**Cache behavior applied** — `sectionName`, `sectionId`, `sectionVersion` ("section" is the cache behavior in the data model; the default behavior has its own section). `refreshRuleId` identifies the advanced rule that decided the refresh.

**Inventory source** — `inputId`, `inputIds` (a URL can come from several sources), `fileUri` for file-based sources, and `InputType`, which describes how the indexation was triggered rather than the configured source type.

**Outcome** — `indexed` (boolean), `deliverable`, `notDeliverableReason`, `statusPage` (HTTP code from the origin), `previousStatusPage`, `countSameStatusPage` and `dateStatusPageUpdate` (what the advanced rules on consecutive status codes work from), `errorMessage`, `indexationPrevented`, `indexable`.

**Lifecycle** — `datePageCrawled`, `refreshTime`, `expirationTime`, `priority`, `refreshRequestPriority` (0 emergency, 1 high, 2 medium, 3 low; -1 or NULL when there was no refresh), and `operation`, a bitmask of what happened to the URL in the inventory.

**Rendering and weight** — `renderStatus` (bitmask; 0 typically means a plain HTML fetch with no rendering), `liveRendered`, `jsFailed`, `jsError`, `isHtml`, `contentType`, `pageSize`, `previousPageSize`, `parseHtmlMs`, plus the fetch-cost family: `fetchedHtmlSize`, `fetchedGetSizeMain` / `fetchedPostSizeMain`, `fetchedGetSizeThirdParty` / `fetchedPostSizeThirdParty`, and the matching request counts `fetchedGetsMain`, `fetchedPostsMain`, `fetchedGetsThirdParty`, `fetchedPostsThirdParty`. These are the fields to use when quantifying how much work a page costs to render, and how much of it is third-party.

**Timings** — `timeOriginalFetchMs` and `timeOriginalRenderMs`: how long the origin took to serve and render the page. These are the baseline for the speed-gain story.

**Quality controls** — `qualityControls` (repeated: `name`, `status` — 0 success, 1 warning, 2 fail, 3 error), the counters `qualityControlsSuccessCount` / `WarningCount` / `FailCount` / `ErrorCount`, and `qualityControlsFinalStatus` (worst status among the page's controls).

**SEO metadata seen at render time** — `canonicalUrl`, `canonicalEqual`, `canonicalHash`, and `pageMetadata` (bitmask: 1 nofollow, 2 noindex, 4 canonical different from the URL, 8 relative canonical), `redirectUrl`.

**Alterations** — repeated records describing modifications applied to the page (`step`: at render time or at serve time; `operation`: link, canonical, hreflang, title, h1, description, redirects, elements…; `action`: added, changed, removed; `count`). This is how to audit what was changed on the delivered HTML.

**Page properties** — `properties` and `previousProperties` (name/value pairs), the values available to advanced cache rules.

## `speedworkers_served_pages`

**The request** — `url`, `rawUrl` (as received), `cacheUrl` (the normalized key actually looked up, which differs when parameters are ignored), `userAgent`, `ip`, `ifModifiedSince`, `token`.

**Who asked** — `searchEngineId` and `botId`, resolved from the user-agent and IP. `botFilterResult` records the verification outcome: IP belongs to the claimed bot, does not belong, uncertain, unknown user-agent, missing client IP, or explicitly allowed/blocked. `requestType` separates production bot traffic (0, Classic) from previews (1), tests (2) and checks (3); higher values are internal request types. `preview` flags preview traffic.

**Outcome** — `delivered`, `cacheHit`, `deliveredFrom` (0 nowhere, 1 cache, 2 fetch, 3 live render, 4 custom section response such as a 404/410 rule, 5 ignored-params redirect, 6 live fetch), `deliveredContent` (0 not delivered, 1 original HTML, 2 custom response), `deliveredFormat` (0 none, 1 HTML, 2 Markdown, 3 JSON), `notDeliveredReason`, `cacheNotDeliveredReason` (the original reason when the request then fell back to a live fetch or live render), `authenticationSucceed`, and the status codes `statusPage`, `statusRequest`, `statusPageFinal`, `cacheStatusPage`, `cacheErrorMessage`, `errorMessage`, `redirectUrl`.

**Speed** — `timeTotalMs` and `timeDeliveryMs` for what SpeedWorkers took, broken down into `timeAuthMs`, `timeGetConfMs`, `timeDbMs`, `timeContentMs`, `timeDecompressMs`, `timeProcessMs`, `timeEncodeMs`, `timeParseDomMs`, `timeExecJsMs`, `timeExecPageWorkersMs`, `timeStreamingKvMs`, `timeLiveMs`, `timePreviewPageMs`. Against them, `timeOriginalFetchMs`, `timeOriginalRenderMs` and `timeOriginalTotalMs` give what the origin would have taken — the pair behind the "N times faster" figure in the Preview.

**Freshness of what was served** — `indexTime`, `nextRefreshTime`, `expirationTime`, `datePageCrawled`. A delivery is stale after `nextRefreshTime` and until `expirationTime`; after expiration the page is no longer served.

**Scope** — `sectionName` / `sectionId` / `sectionVersion` (the behavior at serve time) and `renderSectionName` / `renderSectionId` / `renderSectionVersion` (the behavior in force when the page was cached). A mismatch means settings changed since the page was rendered.

**Quality controls** — the same fields as the indexation table, carried over from the moment the page was cached. This is why a page cached with a failing control contributes a failure on every delivery.

**Sizes** — `basePageSize`, `deliveredPageSize`, `approxResponseSize`, plus the same `fetched*` family when the request triggered a fetch.

**Live Update** — `liveUpdateStatus` reports whether price and availability were successfully patched into the delivered page.

## Not-delivered / not-deliverable reasons

Both tables use the same enum, and it is the machine-readable version of the "not cached" reasons in the interface. Two conventions matter:

- **NULL means delivered.** **0 (`NoReason`) means served from cache with no blocking reason** — it maps to "cached" in reporting, not to a failure.
- Values 1–40 are the actual reasons: `NotCached`, `OriginServerError`, `RequestOriginTimeout`, `QualityCheckFailed`, `InvalidRequest`, `CustomResponse`, `Conf`, `NotHtml`, `JsCodeError`, `UnsupportedMethod`, `LastRefresh4XX`, `LastRefresh5XX`, `OutOfScope`, `DeliveryStopped`, `JsRedirectNotEnabled`, `JsRedirectSameUrl`, `IpFiltered`, `BadToken`, `Throttled`, `NormalizeUrlFailed`, `RequestTimeout`, `ServerOverloaded`, `JsCodeTimeout`, `RedirectLoop`, `InvalidRedirect`, `RedirectToForbiddenHost`, `RenderingFailed`, `RenderingTimeout`, the `LiveRender*` family (start timeout, timeout, not connected, failed, rate limited), `JsForcedCacheMiss`, `TooManyInFlightRequests`, and internal service errors.

Group them when reporting: origin problems (`OriginServerError`, `RequestOriginTimeout`, `LastRefresh4XX/5XX`), rendering problems (`RenderingFailed`, `RenderingTimeout`, `JsCodeError`, `JsCodeTimeout`, the `LiveRender*` family), configuration and scope (`Conf`, `OutOfScope`, `DeliveryStopped`, `NotHtml`, `JsRedirect*`), authentication and filtering (`BadToken`, `InvalidRequest`, `IpFiltered`, `Throttled`), and quality (`QualityCheckFailed`). Each group points at a different owner.

## Bitmask fields

`operation`, `renderStatus`, `pageMetadata` and `pageExtractFailedReasons` are combinable flags. Test a flag with `field & value != 0`, and use `field = 0` for the no-flags case. `operation & 1 != 0` keeps genuine indexation events, which is what indexation dashboards do; `FLUSH | REFRESH` is 6.

## Query recipes

Filter production traffic before computing anything on `served`: previews, tests and status checks inflate the counts.

**Delivery efficiency by day**

```sql
SELECT DATE(date) AS day,
       COUNT(*) AS requests,
       COUNTIF(cacheHit) AS cached,
       SAFE_DIVIDE(COUNTIF(cacheHit), COUNT(*)) AS delivery_efficiency
FROM catalog.speedworkers_served_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND requestType = 0 AND NOT COALESCE(preview, FALSE)
GROUP BY day ORDER BY day
```

**Why requests were not delivered**

```sql
SELECT notDeliveredReason, COUNT(*) AS requests
FROM catalog.speedworkers_served_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND requestType = 0 AND NOT delivered
GROUP BY notDeliveredReason ORDER BY requests DESC
```

**Speed gain, and where SpeedWorkers spends its own time**

```sql
SELECT APPROX_QUANTILES(timeTotalMs, 100)[OFFSET(50)] AS sw_p50_ms,
       APPROX_QUANTILES(timeOriginalTotalMs, 100)[OFFSET(50)] AS origin_p50_ms,
       AVG(timeExecJsMs) AS avg_js_ms,
       AVG(timeExecPageWorkersMs) AS avg_pageworkers_ms
FROM catalog.speedworkers_served_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND delivered AND requestType = 0
```

**Bot mix and cache hit rate per bot**

```sql
SELECT botId, COUNT(*) AS requests,
       SAFE_DIVIDE(COUNTIF(cacheHit), COUNT(*)) AS cache_hit_rate,
       COUNTIF(deliveredFormat = 2) AS markdown_responses
FROM catalog.speedworkers_served_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND requestType = 0
GROUP BY botId ORDER BY requests DESC
```

**Staleness of delivered pages**

```sql
SELECT COUNTIF(date > nextRefreshTime AND date <= expirationTime) AS stale_deliveries,
       COUNT(*) AS deliveries,
       APPROX_QUANTILES(TIMESTAMP_DIFF(date, indexTime, HOUR), 100)[OFFSET(50)] AS median_age_hours
FROM catalog.speedworkers_served_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND delivered AND requestType = 0
```

**Refresh outcomes by cache behavior**

```sql
SELECT sectionName,
       COUNT(*) AS refresh_events,
       COUNTIF(indexed) AS cached,
       COUNTIF(NOT indexed) AS not_cached,
       AVG(timeOriginalFetchMs + IFNULL(timeOriginalRenderMs, 0)) AS avg_origin_ms
FROM catalog.speedworkers_indexed_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND operation & 1 != 0
GROUP BY sectionName ORDER BY refresh_events DESC
```

**Quality control failures by control**

```sql
SELECT qc.name,
       COUNTIF(qc.status = 2) AS fails,
       COUNTIF(qc.status = 3) AS errors,
       COUNT(*) AS executions
FROM catalog.speedworkers_indexed_pages, UNNEST(qualityControls) AS qc
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY qc.name ORDER BY fails DESC
```

**Render cost and third-party weight of the heaviest pages**

```sql
SELECT url,
       AVG(timeOriginalRenderMs) AS avg_render_ms,
       AVG(fetchedGetsMain + fetchedGetsThirdParty) AS avg_requests,
       SAFE_DIVIDE(AVG(fetchedGetSizeThirdParty), AVG(fetchedGetSizeMain + fetchedGetSizeThirdParty)) AS third_party_share
FROM catalog.speedworkers_indexed_pages
WHERE date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND operation & 1 != 0
GROUP BY url ORDER BY avg_render_ms DESC LIMIT 50
```

## Gotchas

- Reporting in the interface and raw table counts will not always match: the interface applies scope, request-type and de-duplication rules. Say which one a figure comes from.
- The same URL can appear several times per day in `indexed` (refresh plus other inventory operations) and many times in `served`. Always decide the grain before aggregating.
- Desktop and mobile are separate cached objects when the behavior caches both, so per-URL counts double unless you group by device.
- `notDeliveredReason = 0` is not an error. Mis-reading it is the most common way to produce a wrong "delivery failures" number.
- Quality control results in `served` describe the state at cache time, not a fresh evaluation.
- Cross-referencing with SiteCrawler or log data works on `url`, but normalize first: `served` also carries `rawUrl` and `cacheUrl`, which can differ from the crawled URL when parameters are ignored.