A full-text search index for your Substack archive, self-hosted on your own web server.
This tool takes your Substack export, builds a SQLite full-text search database from it, and gives you a clean search interface you can host anywhere that runs PHP. Readers search your archive and click through to Substack to read the full post — your content stays on Substack, you just get a search engine that actually works.
No Python. No command line. No ongoing maintenance beyond a few clicks after each new issue.
Built by Tom Salzer KJ7T for the Random Wire newsletter. Live demo: etherham.com/rw_search/
- Full-text search across all your posts and their body content
- Filter by year and post type (newsletter, podcast, page)
- Relevance-ranked results with highlighted snippets
- Links that send readers directly to your Substack post
- Shareable search links — put a search in the page URL (
?q=AllStar) and the results load automatically - A web-based update tool — add new issues by uploading a zip, no coding required
On your web server:
- PHP 8.0 or later
- SQLite3 extension enabled
- ZipArchive extension enabled
- A folder outside your web root to store the database securely
Shared cPanel hosting works fine. This was built and tested on a LiteSpeed-based shared host running PHP 8.1.
To check your PHP extensions in cPanel:
Go to cPanel → Software → Select PHP Version → Extensions. Confirm that sqlite3 and zip are checked.
| File | Purpose |
|---|---|
build_index.py |
One-time Python script to build the initial database. Run this in Claude or locally. |
search.php |
PHP endpoint that queries the database and returns JSON results |
index.html |
The search page — edit for your publication name and domain |
update.php |
Password-protected web page for uploading new Substack exports |
.htaccess |
Prevents WordPress and LiteSpeed from interfering with the search folder |
The initial database is built from your full Substack export using build_index.py. You have two options:
Option A — Use Claude (no Python needed):
Upload your Substack export zip to a Claude conversation and ask it to run build_index.py against it. Claude will produce a randomwire_index.db file you can download directly. This is how the original author set it up.
Option B — Run locally:
Install Python 3.8+ and the beautifulsoup4 library (pip install beautifulsoup4), then run:
python build_index.py --zip /path/to/your-substack-export.zipEither way, you end up with a randomwire_index.db file ready to upload.
To get your Substack export: go to your publication Settings → Export, request an export, and download the zip file Substack emails you.
Open search.php and update line 4 with the absolute path to your database:
$db_path = '/home/yourusername/private/randomwire_index.db';Replace yourusername with your actual cPanel username. You can confirm this in cPanel File Manager — it appears at the top of the left panel as /home/yourusername.
Use the absolute path. Do not use a relative path — shared hosts resolve these inconsistently and it will cause a "Database not found" error.
Open update.php and update the two configuration lines near the top:
define('UPDATE_PASSWORD', 'changeme');
define('DB_PATH', '/home/yourusername/private/randomwire_index.db');Choose a strong password. The DB_PATH must match exactly what you put in search.php.
Find the renderCard function and update the URL to your Substack domain:
const url = 'https://www.yourcustomdomain.com/p/' + r.slug;If you use a Substack custom domain, include www. — bare domains often do not resolve correctly for Substack custom domains. If you have no custom domain:
const url = 'https://yourpublication.substack.com/p/' + r.slug;Also update the page title and header text near the top of index.html to match your publication name.
Using FileZilla or cPanel File Manager, create two folders:
/home/yourusername/private/ ← stores the database, NOT web-accessible
/home/yourusername/public_html/rw_search/ ← stores the search page, web-accessible
The private folder must sit alongside public_html, not inside it. This keeps your database file off the open web.
Use an underscore in rw_search, not a hyphen. A hyphen in the folder name can conflict with WordPress URL routing on some hosts, causing a 404 even when the files exist.
| File | Destination |
|---|---|
randomwire_index.db |
/home/yourusername/private/ |
index.html |
/home/yourusername/public_html/rw_search/ |
search.php |
/home/yourusername/public_html/rw_search/ |
update.php |
/home/yourusername/public_html/rw_search/ |
.htaccess |
/home/yourusername/public_html/rw_search/ |
Visit https://yoursite.com/rw_search/search.php?q=test in your browser. You should see a JSON response with results. If you see {"error":"Database not found"}, recheck the path in search.php.
Then visit https://yoursite.com/rw_search/ to confirm the search interface loads and returns results.
The search page reads its search from the URL, so you can send someone a link that opens straight to the results. This is handy when answering a reader's question.
| Parameter | Example | What it does |
|---|---|---|
q |
?q=AllStar |
The search term. Use quotes for an exact phrase. |
year |
&year=2025 |
Limits results to one year. |
type |
&type=podcast |
Limits results to one post type (newsletter or podcast). |
Examples:
https://yoursite.com/rw_search/?q=%22QSO+One%22
https://yoursite.com/rw_search/?q=AllStar&year=2025
The easiest way to get a link is to run the search on the page, then copy the URL from your browser's address bar. The address bar updates as you type. The %22 is the URL-encoded form of a quote mark; typing plain quotes into the address bar works too.
Include the trailing slash after rw_search/. Without it the link still works, but the server adds an extra redirect.
After publishing a new issue, add it to your index in three steps — no coding required.
Step 1: Export from Substack Go to your publication Settings → Export and download a fresh zip. Substack includes all posts every time, so one zip covers everything.
Step 2: Upload via the update tool
Visit https://yoursite.com/rw_search/update.php. Sign in with the password you set in update.php. Choose your zip file and click Upload and update index. The page will report how many posts were added or updated.
Step 3: Verify Visit your search page and search for a word or phrase from the new issue. If it appears in results, you are done.
Re-uploading a zip that includes old posts is safe — existing entries are updated, not duplicated.
The search page shows a WordPress 404
Your WordPress .htaccess is intercepting the request even though the folder exists. Make sure the .htaccess from this repo is uploaded to the rw_search folder. It contains RewriteEngine Off which prevents WordPress from touching the directory. This is a known issue on LiteSpeed-based hosts.
Also confirm the folder uses an underscore (rw_search), not a hyphen (rw-search). These look similar but behave differently with WordPress routing.
Search returns {"error":"Database not found"}
The path in search.php does not match where the database lives. Use the full absolute path starting with /home/yourusername/. Confirm your username from cPanel File Manager.
Search returns HTTP 500
Usually also a path problem — see above. If the path is correct, check that sqlite3 and zip extensions are enabled in cPanel → Select PHP Version → Extensions.
Results appear but links don't work
Check the URL in the renderCard function in index.html. If your Substack publication uses a custom domain, make sure you include www. — bare domains without www often do not resolve for Substack custom domains.
The update tool says "Upload failed"
Your server's PHP upload_max_filesize or post_max_size may be smaller than your export zip. In cPanel → Select PHP Version → Options, increase both values to at least 20M. A full Substack export with 200+ posts is typically 5–10MB.
The first result card appears blank
This is a stub post — Substack sometimes exports near-empty HTML files for drafts or unpublished redirects. The build script skips files under 500 bytes but occasionally one slips through. It does not affect search quality.
- The
update.phppassword is stored in plain text in the PHP file. Choose something strong and do not reuse a password you use elsewhere. - The database lives outside the web root and cannot be downloaded directly by visitors.
- The search endpoint accepts only read queries — there is no way for a visitor to modify the database through the search page.
- Do not upload
update.phpif you do not intend to use the web update tool. The search system works fine without it.
MIT. Use it, adapt it, share it.
Built during a single session with Claude (Anthropic) by Tom Salzer KJ7T. Hard-won lessons from a real deployment on a LiteSpeed shared host running WordPress are baked into the troubleshooting section above. If this saves you time, a mention is appreciated but not required.