## Summary `network_content_daily` (backing `get_network_content_volume`) **double-counts posts and comments** for a bounded historical range (roughly **2020 → 2025-08**). Counts in that range are inflated by **exactly 2.000×**. Author counts are **not** affected. The live/incremental path (≈ 2025-10 onward) is correct. Root cause: `process_content_volume_backfill` upserts counts **additively** (`posts = ncd.posts + EXCLUDED.posts`), so any backfill run that overlapped an already-populated block range added its counts on top of the existing ones. The authors table uses `ON CONFLICT DO NOTHING` (idempotent), which is exactly why authors are correct while posts/comments are doubled. ## Evidence — rollup vs. independent recount Independent source of truth: `hivemind_app.hive_posts` (one row per post/comment: `depth = 0` → post, `depth > 0` → comment). Ratio = `network_content_daily` / `hive_posts`, same month. | Month | rollup posts | hive_posts posts | post ratio | comment ratio | |---|---|---|---|---| | 2023-07 | 182,220 | 91,110 | **2.000** | 2.000 | | 2023-08 | 187,758 | 93,879 | **2.000** | 2.000 | | … (every month 2023-07 → 2025-08) | | | **2.000** | 2.000 | | 2025-08 | 142,856 | 71,428 | **2.000** | 2.000 | | 2025-09 (transition) | 94,817 | 65,732 | 1.442 | 1.455 | | 2025-10 | 63,430 | 63,022 | 1.006 | 1.002 | | 2025-11 → 2026-06 | … | … | ~1.00 ✓ | ~1.00 ✓ | Deep-history spot check (confirms the doubling is a **bounded window**, not all-time): | Month | rollup posts | hive_posts posts | ratio | |---|---|---|---| | 2016-06 | 5,126 | 5,126 | 1.000 ✓ | | 2019-06 | 257,151 | 257,152 | 1.000 ✓ | | 2021-06 | 229,908 | 114,954 | **2.000** | | 2022-06 | 207,102 | 103,551 | **2.000** | Author counts are correct everywhere (e.g. 2023-07: rollup `unique_authors` 9,782 vs `hive_posts` distinct authors 9,782 — exact match; the anomalous 2025-05/06 author spikes of ~17.9K also match `hive_posts` exactly, i.e. they are real, not a bug). ## Impact on the served endpoint Over a 36-month window (2023-07 → 2026-07, as rendered by block_explorer_ui): | Metric | Endpoint returns | Actual (hive_posts) | |---|---|---| | Total posts | 4.62M | **~2.56M** | | Total comments | 39.9M | **~22.06M** | | Comments/post | 8.6 | 8.6 (ratio survives the doubling) | | Avg authors/month | 9.8K | 9.8K (correct) | | Peak Aug 2023 | 187.8K posts | ~93.9K posts | Secondary/UX consequence: because the doubling **switches off at the backfill→live boundary (~Sep–Oct 2025)**, the time series shows a sharp artificial "cliff" there. True comments went 784K (Aug 2025) → 540K (Oct 2025), a real ~31% decline, but the endpoint shows 1.57M → 541K, a ~65% drop. Roughly half of the visible late-2025 collapse is the artifact disappearing, not a real drop. ## Root cause (function code) `haf_stats_app.process_content_volume_backfill(_first_block, _last_block)`: ```sql -- counts: NOT idempotent (additive) INSERT INTO haf_stats_backend.network_content_daily AS ncd (day, posts, comments, edits) SELECT ... ON CONFLICT (day) DO UPDATE SET posts = ncd.posts + EXCLUDED.posts, comments = ncd.comments + EXCLUDED.comments, edits = ncd.edits + EXCLUDED.edits; -- authors: idempotent (unaffected) INSERT INTO haf_stats_backend.network_author_daily (day, author_id) SELECT DISTINCT day, author_id FROM classified WHERE is_create ON CONFLICT (day, author_id) DO NOTHING; ``` The additive upsert is required for chunked backfilling (multiple block-chunks contributing to the same calendar day), but it means the function is **not safe to re-run** and any overlapping range double-counts. ## Suggested fix 1. **Data repair:** recompute the affected day range once, cleanly — `DELETE` the doubled days from `network_content_daily` and re-run `process_content_volume_backfill` over **non-overlapping** block ranges. `network_author_daily` needs no repair. 2. **Make backfill re-run-safe (prevent recurrence):** either - track a processed high-water-mark / guard against overlapping ranges, or - stage per-run counts and overwrite the day (`SET ... = EXCLUDED.*`) after a full-day range is computed, rather than blind accumulation. ## Reproduction ```sql -- rollup vs independent recount, any historical month: SELECT date_trunc('month', day) m, sum(posts), sum(comments) FROM haf_stats_backend.network_content_daily WHERE day >= '2023-07-01' AND day < '2023-08-01' GROUP BY 1; SELECT date_trunc('month', created_at) m, count(*) FILTER (WHERE depth = 0) posts, count(*) FILTER (WHERE depth > 0) comments FROM hivemind_app.hive_posts WHERE created_at >= '2023-07-01' AND created_at < '2023-08-01' GROUP BY 1; -- rollup ≈ 2× hive_posts for any month in ~2020..2025-08 ``` Verified live on testapi 2026-07-08.