IdeaWell

Project ideas from Hacker News discussions.

Show HN: 22 GB of Hacker News in SQLite

Original Article

Hacker News Discussion

📝 Discussion Summary (Click to expand)

1. Praise for Innovative Browser-Based HN Archive

Users lauded the static, sharded SQLite approach for offline HN access. "Pretty neat project. I never thought you could do this in the first place, very much inspiring." - jacquesm
"Don't miss how this works. It's not a server-side application - this code runs entirely in your browser using SQLite compiled to WASM." - simonw
"I love this so much, on my phone this is much faster than actual HN." - meander_water

2. Alternatives to SQLite: DuckDB, Range Requests, PMTiles

Frequent suggestions for DuckDB's columnar analytics or HTTP range requests over sharding. "Question - did you consider tradeoffs between duckdb (or other columnar stores) and SQLite?" - carbocation
"One interesting feature of DuckDB is that it can run queries against HTTP ranges of a static file hosted via HTTPS." - simonw
"PMTiles is exactly that: a production-ready, single-file, static container for vector tiles built around HTTP range requests." - Humphrey

3. Technical Issues and Data Acquisition Fixes

Repo 404s (due to privacy), browser bugs, query slowness, and BigQuery sourcing dominated fixes. "The GitHub page is no longer available, which is a shame." - sodafountan (repeated by many)
"select * from items limit 10 and it is slowly iterating through the shards." - kristianp
"1. download_hn.sh - bash script that queries BigQuery and saves the data to *.json.gz." - keepamovin

🚀 Project Ideas

Kiwix HN Archive Generator

Summary

  • A CLI tool and hosted service to convert HN SQLite shards (or BigQuery dumps) into Kiwix .zim files for offline browsing on low-end devices like tablets and dumb phones.
  • Core value: Enables instant, searchable, offline HN access via Kiwix app, addressing mobile compatibility and full offline needs.

Details

Key Value
Target Audience HN archivists, offline enthusiasts, mobile users (e.g., "Wonder if you could turn this into a .zim file for offline browsing with Kiwix")
Core Feature Automated ETL from shards/JSON to .zim with search/indexing, Kiwix-lib integration
Tech Stack Node.js/Python, mwoffliner/kiwix-tools, SQLite/DuckDB input
Difficulty Medium
Monetization Hobby

Notes

  • HN users rave about Kiwix for Wikipedia/StackOverflow; multiple calls ("Oh this should TOTALLY be available... on Kiwix", "It'd be great if you could add it to Kiwix").
  • High utility for "offline-only-day" breaks; submission potential to Kiwix library for distribution.

DuckDB HN Query Optimizer

Summary

  • Browser-based HN viewer replacing SQLite shards with DuckDB WASM on Parquet shards, enabling fast analytics queries without full scans.
  • Core value: 10x faster queries on large datasets (e.g., "select * limit 10" instant), better compression for text-heavy data.

Details

Key Value
Target Audience Data tinkerers, analysts frustrated by shard iteration ("slowly iterating through shards", "duckdb might be even more performant")
Core Feature Converter script (shards -> columnar Parquet), DuckDB-WASM UI with NL queries, shard pruning
Tech Stack DuckDB-WASM, Apache Arrow/Parquet, Svelte/Vue for UI
Difficulty Medium
Monetization Revenue-ready: Freemium (local CLI free, hosted queries $5/mo)

Notes

  • Commenters push DuckDB repeatedly ("tradeoffs between duckdb... and SQLite?", "DuckDB is... tailored to analytics", "using duckdb might be even more performant").
  • Sparks HN debates on columnar vs row stores; practical for heatmaps/stats like WadeGrimridge's.

Range-Request HN SQLite Server

Summary

  • Static-file hoster using sql.js-httpvfs or custom VFS for single 10GB gzipped SQLite DB on S3/CF, with browser caching and Firefox support.
  • Core value: No shard selection UI, instant partial loads via HTTP ranges, works on all browsers/static hosts.

Details

Key Value
Target Audience Deployers tired of shards/404s ("why not... HTTP Range Requests instead", "VFS support is amazing")
Core Feature Build script for single DB + VFS index, auto-caching WASM client, FF polyfill
Tech Stack sql.js-httpvfs/PMTiles-inspired, Zstd-seekable, Cloudflare Workers
Difficulty High
Monetization Hobby

Notes

  • Enthusiasm for ranges ("remniscent of... sqlite.js-httpvfs", "PMTiles is exactly that", "Is there anything more production grade?"); solves "query tab looks quite complex with all these content shards".
  • HN loves VFS hacks (simonw recoveries); enables BitTorrent/edge distro experiments.

Read Later