Gateway QoS telemetry

-- The publisher list is data, not code: the reference subgraph hard-coded an allowlist that went stale
-- when the signer rotated, and rejected every message for a month while reporting itself healthy.
-- Rejected documents are counted, never silently dropped.
CREATE VIEW qos_publisher AS
SELECT * FROM (VALUES
  ('0x0b8cef00f90553b9535845be6abbe3797582d424', 'Edge & Node gateway QoS publisher to 2026-07-01 03:50 UTC, block 46972821'),
  ('0x8cbbe43f97f80efa6ba0a95f3d544e03f84db0ce', 'Edge & Node gateway QoS publisher from 2026-07-03 14:55 UTC, block 47014403')
) AS t(address, note);

-- The calls a listed publisher sent, by the sender nuthatch records on every call row (`tx_from`).
CREATE VIEW qos_published_call AS
SELECT CAST(c.block_number AS BIGINT) AS block_number,
       CAST(c.log_index AS BIGINT) AS log_index
FROM data_edge__call_submit_qo_s_payload c
JOIN qos_publisher p ON p.address = lower(c.tx_from);

-- A typed row names the call that named its document (`source_log_index`). nuthatch plans a CID once per
-- block from the first row naming it, so a stranger re-posting a published CID ahead of the publisher in
-- the same block would take the document; it would show here rather than vanish.
CREATE VIEW qos_rejected_documents AS
SELECT 'indexer' AS topic, CAST(r.block_number AS BIGINT) AS block_number, r.cid, count(*) AS rows
FROM qos_indexer_attempt_rows r
LEFT JOIN qos_published_call c
  ON c.block_number = CAST(r.block_number AS BIGINT) AND c.log_index = CAST(r.source_log_index AS BIGINT)
WHERE c.block_number IS NULL
GROUP BY ALL
UNION ALL
SELECT 'query', CAST(r.block_number AS BIGINT), r.cid, count(*)
FROM qos_query_result_rows r
LEFT JOIN qos_published_call c
  ON c.block_number = CAST(r.block_number AS BIGINT) AND c.log_index = CAST(r.source_log_index AS BIGINT)
WHERE c.block_number IS NULL
GROUP BY ALL;

-- `require_verified`: nuthatch stores only documents proven against their CID (RFC-0037 slice 7), so
-- every row this build writes is verified. The switch stays as the guard against a row an older build
-- stored unverified.
CREATE VIEW qos_settings AS
SELECT TRUE AS require_verified;

-- Each UTC day with its bucket starts as the decimal text the typed rows store. Rows match on the day
-- computed from them; the text range is redundant to that, but DuckDB pushes it into the Parquet scan so
-- `day = ...` reads one day's segments. Text orders like the number only at one length: ten digits here.
CREATE VIEW qos_day_bounds AS
SELECT DATE '1970-01-01' + CAST(d AS INTEGER) AS day,
       CAST(d * 86400 AS VARCHAR) AS first_start_epoch,
       CAST(d * 86400 + 86399 AS VARCHAR) AS last_start_epoch
FROM range(11575, 115740) AS t(d);

-- The typed rows nuthatch exploded from each proven document when it resolved (RFC-0037 slice 8), from
-- a listed publisher only. Nothing here parses JSON. Stored columns are cast once, here: numbers arrive
-- as their decimal text, and an absent one is empty text, which reads as NULL.
CREATE VIEW qos_indexer_attempt_raw AS
SELECT r.cid,
       CAST(d.verified AS VARCHAR) = 'true' AS verified,
       CAST(r.block_number AS BIGINT) AS block_number,
       CAST(r.document_log_index AS BIGINT) AS log_index,
       CAST(r.element AS BIGINT) AS element,
       r.indexer_wallet, r.indexer_url, r.subgraph_deployment_ipfs_hash, r.chain, r.gateway_id,
       CAST(r.start_epoch AS BIGINT) AS start_epoch,
       CAST(r.end_epoch AS BIGINT) AS end_epoch,
       CAST(nullif(r.query_count, '') AS DOUBLE) AS query_count,
       CAST(nullif(r.num_indexer_200_responses, '') AS DOUBLE) AS num_indexer_200_responses,
       CAST(nullif(r.proportion_indexer_200_responses, '') AS DOUBLE) AS proportion_indexer_200_responses,
       CAST(nullif(r.avg_indexer_latency_ms, '') AS DOUBLE) AS avg_indexer_latency_ms,
       CAST(nullif(r.max_indexer_latency_ms, '') AS DOUBLE) AS max_indexer_latency_ms,
       CAST(nullif(r.stdev_indexer_latency_ms, '') AS DOUBLE) AS stdev_indexer_latency_ms,
       CAST(nullif(r.avg_indexer_blocks_behind, '') AS DOUBLE) AS avg_indexer_blocks_behind,
       CAST(nullif(r.max_indexer_blocks_behind, '') AS DOUBLE) AS max_indexer_blocks_behind,
       CAST(nullif(r.avg_query_fee, '') AS DOUBLE) AS avg_query_fee,
       CAST(nullif(r.max_query_fee, '') AS DOUBLE) AS max_query_fee,
       CAST(nullif(r.total_query_fees, '') AS DOUBLE) AS total_query_fees,
       k.day
FROM qos_indexer_attempt_rows r
JOIN qos_day_bounds k
  ON k.day = DATE '1970-01-01' + CAST(CAST(r.start_epoch AS BIGINT) // 86400 AS INTEGER)
 AND r.start_epoch BETWEEN k.first_start_epoch AND k.last_start_epoch
JOIN qos_indexer_payload d
  ON CAST(d.block_number AS BIGINT) = CAST(r.block_number AS BIGINT)
 AND CAST(d.log_index AS BIGINT) = CAST(r.document_log_index AS BIGINT)
JOIN qos_published_call c
  ON c.block_number = CAST(r.block_number AS BIGINT) AND c.log_index = CAST(r.source_log_index AS BIGINT)
WHERE CAST(d.verified AS VARCHAR) = 'true'
   OR NOT (SELECT require_verified FROM qos_settings);

CREATE VIEW qos_query_result_raw AS
SELECT r.cid,
       CAST(d.verified AS VARCHAR) = 'true' AS verified,
       CAST(r.block_number AS BIGINT) AS block_number,
       CAST(r.document_log_index AS BIGINT) AS log_index,
       CAST(r.element AS BIGINT) AS element,
       r.subgraph_deployment_ipfs_hash, r.chain, r.gateway_id,
       CAST(r.start_epoch AS BIGINT) AS start_epoch,
       CAST(r.end_epoch AS BIGINT) AS end_epoch,
       CAST(nullif(r.query_count, '') AS DOUBLE) AS query_count,
       CAST(nullif(r.gateway_query_success_rate, '') AS DOUBLE) AS gateway_query_success_rate,
       CAST(nullif(r.user_attributed_error_rate, '') AS DOUBLE) AS user_attributed_error_rate,
       CAST(nullif(r.avg_gateway_latency_ms, '') AS DOUBLE) AS avg_gateway_latency_ms,
       CAST(nullif(r.max_gateway_latency_ms, '') AS DOUBLE) AS max_gateway_latency_ms,
       CAST(nullif(r.stdev_gateway_latency_ms, '') AS DOUBLE) AS stdev_gateway_latency_ms,
       CAST(nullif(r.avg_query_fee, '') AS DOUBLE) AS avg_query_fee,
       CAST(nullif(r.max_query_fee, '') AS DOUBLE) AS max_query_fee,
       CAST(nullif(r.total_query_fees, '') AS DOUBLE) AS total_query_fees,
       CAST(CAST(nullif(r.most_recent_query_ts, '') AS DOUBLE) AS BIGINT) AS most_recent_query_ts,
       k.day
FROM qos_query_result_rows r
JOIN qos_day_bounds k
  ON k.day = DATE '1970-01-01' + CAST(CAST(r.start_epoch AS BIGINT) // 86400 AS INTEGER)
 AND r.start_epoch BETWEEN k.first_start_epoch AND k.last_start_epoch
JOIN qos_query_payload d
  ON CAST(d.block_number AS BIGINT) = CAST(r.block_number AS BIGINT)
 AND CAST(d.log_index AS BIGINT) = CAST(r.document_log_index AS BIGINT)
JOIN qos_published_call c
  ON c.block_number = CAST(r.block_number AS BIGINT) AND c.log_index = CAST(r.source_log_index AS BIGINT)
WHERE CAST(d.verified AS VARCHAR) = 'true'
   OR NOT (SELECT require_verified FROM qos_settings);

-- Every document a listed publisher named, from its calldata: the topic, the CID and the bucket. The
-- calldata is hex of UTF-8 JSON, one `{topic, hash, timestamp}` object or an array of them, and
-- `timestamp` is the bucket's end. Calldata that does not decode names no document rather than failing
-- the view. One row per posting, so a few hundred a day whatever the documents hold.
CREATE VIEW qos_posting AS
WITH calls AS (
  SELECT CAST(c.block_number AS BIGINT) AS block_number,
         CAST(c.log_index AS BIGINT) AS log_index,
         CAST(c.block_timestamp AS BIGINT) AS block_timestamp,
         TRY(CAST(decode(unhex(substr(CAST(c._payload AS VARCHAR), 3))) AS VARCHAR)) AS payload
  FROM data_edge__call_submit_qo_s_payload c
  JOIN qos_publisher p ON p.address = lower(c.tx_from)
), named AS (
  SELECT block_number, log_index, block_timestamp,
         unnest(CASE WHEN json_type(payload) = 'ARRAY'
                     THEN TRY(from_json(payload, '[{"topic":"VARCHAR","hash":"VARCHAR","timestamp":"BIGINT"}]'))
                     ELSE [TRY(from_json(payload, '{"topic":"VARCHAR","hash":"VARCHAR","timestamp":"BIGINT"}'))] END) AS doc
  FROM calls
)
SELECT block_number, log_index, block_timestamp,
       CASE doc.topic
         WHEN 'gateway_indexer_attempt_qos_5_minutes_prod_v3' THEN 'indexer'
         WHEN 'gateway_query_result_qos_5_minutes_prod_v3' THEN 'query'
       END AS topic,
       doc.hash AS cid,
       doc.timestamp - 300 AS bucket_start,
       DATE '1970-01-01' + CAST((doc.timestamp - 300) // 86400 AS INTEGER) AS day
FROM named
WHERE doc IS NOT NULL AND doc.topic IN ('gateway_indexer_attempt_qos_5_minutes_prod_v3',
                                        'gateway_query_result_qos_5_minutes_prod_v3');

-- The documents nuthatch has stored, one row each. A document is keyed by its block and CID, the pair its
-- posting names: the document row's `log_index` is a storage slot, not the posting's.
CREATE VIEW qos_stored_document AS
SELECT 'indexer' AS topic, CAST(block_number AS BIGINT) AS block_number, cid
FROM qos_indexer_payload
WHERE CAST(verified AS VARCHAR) = 'true' OR NOT (SELECT require_verified FROM qos_settings)
UNION ALL
SELECT 'query', CAST(block_number AS BIGINT), cid
FROM qos_query_payload
WHERE CAST(verified AS VARCHAR) = 'true' OR NOT (SELECT require_verified FROM qos_settings);

-- Typed rows, one per bucket. A day is the UTC day of the bucket's START: the publisher's own
-- `timestamp` is the bucket's end, so dating by it files the 23:55 bucket under the next day.
--
-- If a bucket is ever published twice (a retried post with a new CID), only the first document for it
-- counts. Summing both would double every figure for those five minutes and nothing downstream could
-- tell. In 2026-09-06..12 no bucket was published twice.

-- The first document for each bucket, by a window partitioned by day and bucket: a day filter passes
-- through the partition, so a one-day statement reads the rows once, where a join back to each bucket's
-- first document read them twice. The key orders documents by block then log index; a log index is
-- always under 1,000,000.
CREATE VIEW qos_indexer_attempt AS
SELECT r.start_epoch AS bucket_start,
       r.end_epoch AS bucket_end,
       r.day,
       lower(r.indexer_wallet) AS indexer,
       r.indexer_url,
       r.subgraph_deployment_ipfs_hash AS deployment,
       lower(r.chain) AS chain,
       lower(r.gateway_id) AS gateway,
       r.query_count,
       -- The published count, not proportion times count: the count is what the gateway observed.
       coalesce(r.num_indexer_200_responses, r.proportion_indexer_200_responses * r.query_count) AS num_200,
       r.avg_indexer_latency_ms AS avg_latency_ms,
       r.max_indexer_latency_ms AS max_latency_ms,
       r.stdev_indexer_latency_ms AS stdev_latency_ms,
       r.avg_indexer_blocks_behind AS avg_blocks_behind,
       r.max_indexer_blocks_behind AS max_blocks_behind,
       r.avg_query_fee,
       r.max_query_fee,
       r.total_query_fees,
       r.cid,
       r.verified,
       r.block_number
FROM qos_indexer_attempt_raw r
QUALIFY r.block_number * 1000000 + r.log_index
  = min(r.block_number * 1000000 + r.log_index) OVER (PARTITION BY r.day, r.start_epoch);

CREATE VIEW qos_query_result AS
SELECT r.start_epoch AS bucket_start,
       r.end_epoch AS bucket_end,
       r.day,
       r.subgraph_deployment_ipfs_hash AS deployment,
       lower(r.chain) AS chain,
       lower(r.gateway_id) AS gateway,
       r.query_count,
       r.gateway_query_success_rate,
       r.user_attributed_error_rate,
       r.avg_gateway_latency_ms,
       r.max_gateway_latency_ms,
       r.stdev_gateway_latency_ms,
       r.avg_query_fee,
       r.max_query_fee,
       r.total_query_fees,
       r.most_recent_query_ts,
       r.cid,
       r.verified,
       r.block_number
FROM qos_query_result_raw r
QUALIFY r.block_number * 1000000 + r.log_index
  = min(r.block_number * 1000000 + r.log_index) OVER (PARTITION BY r.day, r.start_epoch);

-- Block times by the oracle's chain name, identical to kittiwake's `block_time_sec`. A chain not listed
-- has no seconds-behind figure at all: an unknown block time is not evidence of lag.
CREATE VIEW qos_chain_block_time AS
SELECT * FROM (VALUES
  ('mainnet', 12.0), ('sepolia', 12.0), ('moonbeam', 12.0),
  ('arbitrum-one', 0.25), ('arbitrum', 0.25), ('arbitrum-sepolia', 0.25),
  ('base', 2.0), ('base-sepolia', 2.0), ('optimism', 2.0), ('optimism-sepolia', 2.0),
  ('matic', 2.0), ('polygon', 2.0), ('polygon-zkevm', 2.0), ('avalanche', 2.0), ('linea', 2.0),
  ('blast-mainnet', 2.0), ('boba', 2.0),
  ('bsc', 3.0), ('chapel', 3.0), ('scroll', 3.0), ('xlayer-mainnet', 3.0), ('chiliz', 3.0),
  ('chiliz-testnet', 3.0),
  ('gnosis', 5.0), ('xdai', 5.0), ('celo', 5.0), ('fuse', 5.0),
  ('fantom', 1.0), ('unichain', 1.0), ('zksync-era', 1.0), ('monad', 1.0),
  ('sonic', 0.5)
) AS t(chain, block_time_sec);

-- The daily grain the charts and the quality score read. Every rate is a ratio of sums, never a mean of
-- rates: the old Lodestar card averaged daily figures, which put staked.cloud at 62.7% for a week it
-- served at 83.4%. Latency and blocks behind are weighted by queries because the oracle's average
-- behaves as if it includes failed responses (weighting by 200s moved 99 of 374 indexer-days by >20%).
--
-- A "bad bucket" has at least 50 queries and under 90% success. It is what a daily figure hides:
-- Ellipfra read 97.36% for 2026-09-06..12 while one bucket served 0 of 52,286.

CREATE VIEW qos_allocation_bucket AS
SELECT indexer, deployment, chain, gateway, day, bucket_start,
       sum(query_count) AS query_count,
       sum(num_200) AS num_200,
       sum(avg_latency_ms * query_count) AS latency_x_queries,
       sum(avg_blocks_behind * query_count) AS blocks_behind_x_queries,
       sum(total_query_fees) AS total_query_fees
FROM qos_indexer_attempt
GROUP BY ALL;

CREATE VIEW qos_allocation_daily AS
SELECT indexer, deployment, chain, gateway, day,
       sum(query_count) AS query_count,
       sum(num_200) AS num_200,
       sum(num_200) / nullif(sum(query_count), 0) AS success_rate,
       sum(latency_x_queries) / nullif(sum(query_count), 0) AS latency_ms,
       sum(blocks_behind_x_queries) / nullif(sum(query_count), 0) AS blocks_behind,
       sum(total_query_fees) AS total_query_fees,
       sum(total_query_fees) / nullif(sum(query_count), 0) AS avg_query_fee,
       count(*) AS buckets,
       count(*) FILTER (WHERE query_count >= 50 AND num_200 < 0.9 * query_count) AS bad_buckets,
       arg_min(bucket_start, [num_200 / query_count, bucket_start::DOUBLE])
         FILTER (WHERE query_count >= 50) AS worst_bucket_start,
       min(num_200 / query_count) FILTER (WHERE query_count >= 50) AS worst_bucket_success_rate,
       arg_min(query_count, [num_200 / query_count, bucket_start::DOUBLE])
         FILTER (WHERE query_count >= 50) AS worst_bucket_queries,
       -- Sums kept so a coarser grain can re-weight exactly instead of averaging these rows.
       sum(latency_x_queries) AS latency_x_queries,
       sum(blocks_behind_x_queries) AS blocks_behind_x_queries
FROM qos_allocation_bucket
GROUP BY ALL;

-- Each deployment's query total from the gateway's side: the served-share denominator. Indexer attempts
-- undercount it wherever the gateway retried a query on a second indexer.
CREATE VIEW qos_deployment_daily AS
SELECT deployment, chain, gateway, day,
       sum(query_count) AS query_count,
       count(DISTINCT bucket_start) AS buckets
FROM qos_query_result
GROUP BY ALL;

-- Indexer-day in one pass over its allocation buckets: additive figures are sums, rates are ratios of those
-- sums. A bucket is one bucket however many allocations it touched, so bucket counts and the worst bucket
-- read the indexer's bucket totals, taken with a window rather than a second pass over the rows.
CREATE VIEW qos_indexer_bucket AS
SELECT indexer, day, bucket_start,
       sum(query_count) AS query_count,
       sum(num_200) AS num_200
FROM qos_allocation_bucket
GROUP BY ALL;

CREATE VIEW qos_indexer_daily AS
WITH b AS (
  SELECT indexer, day, bucket_start, deployment, chain, gateway,
         query_count, num_200, latency_x_queries, blocks_behind_x_queries, total_query_fees,
         sum(query_count) OVER w AS bucket_query_count,
         sum(num_200) OVER w AS bucket_num_200
  FROM qos_allocation_bucket
  WINDOW w AS (PARTITION BY indexer, day, bucket_start)
), s AS (
  SELECT indexer, day,
         sum(query_count) AS query_count,
         sum(num_200) AS num_200,
         sum(latency_x_queries) AS latency_x_queries,
         sum(blocks_behind_x_queries) AS blocks_behind_x_queries,
         sum(total_query_fees) AS total_query_fees,
         count(DISTINCT (deployment, chain, gateway)) AS allocations,
         count(DISTINCT bucket_start) AS buckets,
         count(DISTINCT bucket_start)
           FILTER (WHERE bucket_query_count >= 50 AND bucket_num_200 < 0.9 * bucket_query_count) AS bad_buckets,
         arg_min(bucket_start, [bucket_num_200 / bucket_query_count, bucket_start::DOUBLE])
           FILTER (WHERE bucket_query_count >= 50) AS worst_bucket_start,
         min(bucket_num_200 / bucket_query_count) FILTER (WHERE bucket_query_count >= 50) AS worst_bucket_success_rate,
         arg_min(bucket_query_count, [bucket_num_200 / bucket_query_count, bucket_start::DOUBLE])
           FILTER (WHERE bucket_query_count >= 50) AS worst_bucket_queries
  FROM b
  GROUP BY indexer, day
)
SELECT indexer, day,
       query_count,
       num_200,
       num_200 / nullif(query_count, 0) AS success_rate,
       latency_x_queries / nullif(query_count, 0) AS latency_ms,
       blocks_behind_x_queries / nullif(query_count, 0) AS blocks_behind,
       total_query_fees,
       total_query_fees / nullif(query_count, 0) AS avg_query_fee,
       allocations,
       buckets, bad_buckets, worst_bucket_start, worst_bucket_success_rate, worst_bucket_queries
FROM s;

-- Seconds behind, net of the lag the whole deployment shares. Blocks behind in blocks means nothing
-- across chains whose block times differ 48-fold, and one dead deployment can dominate it: an
-- arbitrum-sepolia deployment 306M blocks behind put 0x0a015d9e at 1.59M for 2026-09-06..12.
--
-- A peer is credible at 100 measured queries and a floor needs three credible peers, as in kittiwake's
-- quality score. Without a floor there is no peer to be behind, so the chart leaves that deployment out:
-- the raw figure it used to fall back to made a 2-query BSC deployment read as eight years behind.
-- The score keeps its own fallback over its window.

CREATE VIEW qos_indexer_deployment_lag AS
SELECT a.indexer, a.deployment, a.chain, a.day,
       sum(a.query_count) AS query_count,
       sum(a.blocks_behind_x_queries) / nullif(sum(a.query_count), 0) AS blocks_behind,
       sum(a.blocks_behind_x_queries) / nullif(sum(a.query_count), 0) * t.block_time_sec AS seconds_behind
FROM qos_allocation_daily a
LEFT JOIN qos_chain_block_time t ON t.chain = a.chain
GROUP BY a.indexer, a.deployment, a.chain, a.day, t.block_time_sec;

CREATE VIEW qos_deployment_lag_floor AS
SELECT deployment, chain, day,
       min(seconds_behind) AS floor_seconds_behind,
       count(*) AS credible_peers
FROM qos_indexer_deployment_lag
WHERE query_count >= 100 AND seconds_behind IS NOT NULL
GROUP BY ALL
HAVING count(*) >= 3;

-- The floor is `qos_deployment_lag_floor`'s, taken with a window so a one-day statement reads the rows
-- once. A NULL deployment or chain gets no floor, as a join to that view would find none.
CREATE VIEW qos_seconds_behind AS
SELECT indexer, deployment, chain, day, query_count, blocks_behind, seconds_behind, floor_seconds_behind,
       CASE WHEN floor_seconds_behind IS NOT NULL
            THEN greatest(seconds_behind - floor_seconds_behind, 0) END AS seconds_behind_peers
FROM (
  SELECT l.*,
         CASE WHEN deployment IS NOT NULL AND chain IS NOT NULL
               AND count(*) FILTER (WHERE query_count >= 100 AND seconds_behind IS NOT NULL) OVER w >= 3
              THEN min(seconds_behind) FILTER (WHERE query_count >= 100 AND seconds_behind IS NOT NULL) OVER w
         END AS floor_seconds_behind
  FROM qos_indexer_deployment_lag l
  WINDOW w AS (PARTITION BY deployment, chain, day)
);

-- The chart's figures: query-weighted over the indexer's deployments that have a floor, and the share of its
-- queries on those (`share_with_block_time`, named before the floor was required), so a day measured on
-- 40% of traffic does not read as the whole. On 2026-09-07 this took the worst indexer from 15.8M seconds
-- to 3,451 and the median from 25 to 6, with a median 98% of queries still compared.
CREATE VIEW qos_indexer_seconds_behind AS
SELECT indexer, day,
       sum(seconds_behind_peers * query_count) FILTER (WHERE seconds_behind_peers IS NOT NULL)
         / nullif(sum(query_count) FILTER (WHERE seconds_behind_peers IS NOT NULL), 0) AS seconds_behind_peers,
       sum(query_count) FILTER (WHERE seconds_behind_peers > 300)
         / nullif(sum(query_count) FILTER (WHERE seconds_behind_peers IS NOT NULL), 0) AS share_queries_over_5min_behind,
       sum(query_count) FILTER (WHERE seconds_behind_peers IS NOT NULL)
         / nullif(sum(query_count), 0) AS share_with_block_time
FROM qos_seconds_behind
GROUP BY ALL;

-- How current the nest is, network-wide, as two separate questions: when the publisher last posted,
-- and the newest bucket this nest holds figures for. They differ, and conflating them is how a feed
-- that stopped resolving kept reporting itself fresh.
--
-- Read from postings and stored documents, one row per five-minute document, never from the typed rows
-- (thousands per document). A bucket is held when its document is stored, which is when its rows are:
-- nuthatch writes both in one transaction.
CREATE VIEW qos_freshness AS
WITH held AS (
  SELECT p.topic, max(p.bucket_start) AS newest_bucket_start, count(DISTINCT p.bucket_start) AS buckets
  FROM qos_posting p
  JOIN qos_stored_document s ON s.topic = p.topic AND s.block_number = p.block_number AND s.cid = p.cid
  GROUP BY p.topic
), posts AS (
  SELECT max(block_number) AS last_post_block, max(block_timestamp) AS last_post_time
  FROM qos_posting
)
SELECT t.topic,
       h.newest_bucket_start,
       h.newest_bucket_start + 300 AS newest_bucket_end,
       coalesce(h.buckets, 0) AS buckets,
       p.last_post_block,
       p.last_post_time,
       p.last_post_time - (h.newest_bucket_start + 300) AS post_ahead_of_newest_bucket_secs
FROM (VALUES ('indexer'), ('query')) AS t(topic)
LEFT JOIN held h USING (topic)
CROSS JOIN posts p;

-- Per UTC day and topic: the buckets posted, the buckets whose document is stored, the postings whose
-- document is not, and the highest block holding a posting. A document the resolver gave up on is never
-- stored, so it stays unresolved here for ever: a reader deciding a day is final must see it.
CREATE VIEW qos_day_resolution AS
SELECT p.day, p.topic,
       count(DISTINCT p.bucket_start) AS buckets_posted,
       count(DISTINCT p.bucket_start) FILTER (WHERE s.cid IS NOT NULL) AS buckets_stored,
       count(*) FILTER (WHERE s.cid IS NULL) AS unresolved_documents,
       max(p.block_number) AS closing_block
FROM qos_posting p
LEFT JOIN qos_stored_document s
  ON s.topic = p.topic AND s.block_number = p.block_number AND s.cid = p.cid
GROUP BY p.day, p.topic;