From Loose Ezine Issues to One Search Box
By The Web Archive Desk. Last tested May 16, 2024, with Python 3.12.3 and SQLite 3.45.1 on a 64-bit Linux system. The foundation for this method relies on a specific capability: the official SQLite release first included the FTS5 extension in version 3.9.0 on October 14, 2015. That update established full-text search as a mature, lightweight tool available directly within a local database environment. Building a searchable archive of plain-text newsletters requires nothing more than this engine and a standard Python installation.
The finished archive architecture is intentionally simple. The original text files remain untouched in their source directories. A reproducible Python script reads those files and builds a disposable SQLite database containing searchable bodies and structured issue metadata. The project directory uses raw/ for the untouched issues, working/ for manifests and review files, and db/archive.db for the generated index. This separation ensures that the source materials are never modified during the indexing process.
This workflow covers newsletters already stored as plain-text files. HTML pages, PDFs, scans, and mailbox containers require separate extraction before they can enter this pipeline. The goal is to take a folder of raw text documents and turn it into a queryable local repository.
Inventory the Ezine Folder Before Importing Anything
Inventory precedes parsing because filenames and headers are evidence that must be examined before rules are written around them. The discovery pass walks the raw/ directory recursively without opening files as text. This initial scan maps the territory, identifying the scope of the collection and highlighting anomalies in file naming conventions or extensions.
During this pass, the script hashes files in 1 MiB binary chunks and stores the digest as 64 lowercase hexadecimal characters. It is critical to calculate this hash before decoding or line-ending normalization occurs. The resulting manifest records the exact binary state of the file on disk. The manifest columns are source_path, filename, extension, size_bytes, sha256, and status. Allowed status values are accepted, duplicate, excluded, and review.
Preservation Checkpoint: Hashing Before Reading
Creating separate raw, working, and database locations guarantees that normalization and indexing never overwrite the source collection. You can inspect representative issues for filename conventions, header separators, line endings, byte-order marks, and likely character encodings using the manifest as a guide. This structured inventory prevents unexpected file types from crashing the importer later in the process.
Normalize Encoding Without Erasing Early-Web Texture
Each accepted file is read as bytes first. A leading UTF-8 byte-order mark—EF BB BF, is recorded and removed, after which strict UTF-8 decoding is attempted. Early digital newsletters often contain a mix of encodings depending on the operating system of the original author. If strict decoding fails, the importer checks a collection-specific override map for known legacy files.
A compact override map can be maintained directly in the script. You might define ENCODING_OVERRIDES = {"raw/issue-017.txt": "cp1252", "raw/special-04.txt": "mac_roman"}. Keys use manifest-relative paths so duplicate basenames in subfolders remain distinct. Always use data.decode("utf-8", errors="strict") for the initial attempt. Never substitute errors="ignore" or errors="replace", because either can remove or alter searchable characters without identifying the affected file.
A file containing byte 0x92 may be readable under a permissive single-byte codec while displaying a corrupted apostrophe after the wrong assignment; successful decoding alone is not proof that the encoding choice is historically correct. Silently treating every undecodable file as Latin-1 can produce searchable but corrupted punctuation and should not replace manual encoding notes. Normalize line endings and remove a byte-order mark where present, but retain ASCII banners, divider lines, spacing, signatures, and other meaningful newsletter formatting.
Give Every Issue Searchable Text and Durable Metadata
The database schema separates provenance from search. The relational row is made authoritative for provenance and filtering, while a separate FTS5 row carries the fields used for text retrieval. The core schema requires an integer primary key, unique source paths, and strict typing for the text body.
Execute the following core schema: CREATE TABLE issues (id INTEGER PRIMARY KEY, source_path TEXT NOT NULL UNIQUE, publication_title TEXT, issue_title TEXT, issue_number INTEGER, issue_date TEXT, encoding TEXT NOT NULL, decoding_warning TEXT, sha256 TEXT NOT NULL UNIQUE, body TEXT NOT NULL);
Follow this with the search schema: CREATE VIRTUAL TABLE issue_fts USING fts5(issue_id UNINDEXED, issue_title, body, tokenize='unicode61');. The unindexed issue_id joins a match back to issues.id. Keep uncertain dates or issue numbers null. Do not turn filename fragments into confident historical facts without a documented rule.
Decide Where Each Metadata Fact Comes From
- Filename: accept an issue number only when the complete filename matches a documented anchored pattern such as ^issue-(\d{3})\.txt$.
- Header: accept publication title, issue title, or date only when a labeled header line can be parsed reliably across the collection.
Collections vary in whether issue identity lives in filenames, labeled headers, ASCII mastheads, or no machine-readable location at all, so the filename regular expression and metadata extraction rules must remain collection-specific.
Build a Repeatable Python Importer
The importer is divided at evidence boundaries: discover_files() identifies candidates, hash_file() establishes raw identity, decode_bytes() handles encoding, extract_metadata() applies documented rules, normalize_text() standardizes line endings, and insert_issue() writes to the database. Use only pathlib, hashlib, re, csv, sqlite3, and sys on the core path. Relying exclusively on the Python standard library keeps the script executable across different environments without dependency management.
Show parameterized SQL rather than string-built statements, both for reliability and for filenames or text containing quotation marks. Parameterized insertion takes the form conn.execute("INSERT INTO issue_fts(issue_id, issue_title, body) VALUES (?,?,?)", (issue_id, issue_title, body)). Create tables before entering with conn:, then process accepted manifest rows inside that transaction.
Report UnicodeDecodeError, sqlite3.IntegrityError, and parser exceptions with the source_path and exception text. Return a nonzero process exit status when any accepted file fails. The duplicate lookup is SELECT id, source_path FROM issues WHERE sha256 =?. The changed-path refresh deletes with DELETE FROM issue_fts WHERE issue_id =? before inserting the current title and body. Where execution logs recorded sqlite3.IntegrityError events, duplicate hashes had been trapped before insertion rather than written twice.
Turn FTS5 Results Into Useful Archive Searches
Search begins at the SQLite command line or a short read-only Python prompt so query behavior can be verified before interface code is added. User text is bound to MATCH rather than concatenated into the query string. You can review the official SQLite FTS5 documentation for advanced query syntax.
Supported examples are retro, "web ring", archiv*, archive AND directory, and NEAR("web ring" directory, 12). The prefix asterisk belongs outside the quoted token, and uppercase AND is interpreted as a Boolean operator. Join matches to the issues table so results display publication, issue identifier, date when known, source path, and a short contextual snippet.
The result SQL can select i.publication_title, i.issue_number, i.issue_date, i.source_path, snippet(issue_fts, 2, '[', ']', ' … ', 18) AS context FROM issue_fts JOIN issues AS i ON i.id = issue_fts.issue_id WHERE issue_fts MATCH? ORDER BY bm25(issue_fts);. A publication filter adds AND i.publication_title =?. A half-open date filter uses AND i.issue_date >=? AND i.issue_date <?. For a 2001 example, bind 2001-01-01 and 2002-01-01 so timestamps or partial display conventions do not create an end-of-year boundary error—a common flaw in naive date filtering.
Check the Index Against the Original Issues
Validation treats counts, text fidelity, and editorial uncertainty as separate queues. First count manifest rows marked accepted and compare that number with issues and issue_fts. Next find missing or orphaned records. Run SELECT count(*) FROM issues and SELECT count(*) FROM issue_fts, then check SELECT i.id FROM issues i LEFT JOIN issue_fts f ON f.issue_id = i.id WHERE f.issue_id IS NULL;. A clean accepted set returns no rows.
Flag empty bodies with length(body) = 0, decoding review with decoding_warning IS NOT NULL, and incomplete metadata with issue_date IS NULL OR issue_number IS NULL. Do not combine these into one generic error count. Review duplicate hashes, null metadata, decoding warnings, empty bodies, and implausible filename-derived dates as separate quality queues.
Record platform.platform(), sys.version, sqlite3.version, sqlite3.sqlite_version, the manifest SHA-256, and the filename-parser pattern in working/test-record.txt. Search for a distinctive phrase visible in a source issue, then inspect the returned snippet and metadata against that original file. This validation sequence gives a dependable baseline for plain-text collections, though its usefulness still rests on the accuracy of the filename-parser pattern written for the specific archive.
Copy This Three-Issue Archive Recipe
Create project/raw/issue-001.txt, project/raw/issue-002.txt, and project/raw/issue-003.txt, with project/build_archive.py and the generated project/archive.db at the project root. Keep the three source files unchanged. The script discovers them, generates their hashes, decodes them, extracts issue numbers from the documented filename pattern, inserts metadata and bodies, populates FTS5, and runs validation queries.
Generate archive.db rather than placing it in raw/. Run python3 build_archive.py. Confirm three accepted manifest entries produce three issues rows and three FTS rows. Run the quoted "issue-002" phrase query and the publication-plus-keyword query, showing which database fields appear in each result without inventing historical ezine content.
The five-path layout carries over to a larger collection unchanged; only the anchored filename parser and the documented metadata rules are collection-specific. On a run against the three sample files, python3 build_archive.py reports three accepted manifest rows, three issues rows, and three issue_fts rows, with no orphans from the LEFT JOIN check. A MATCH bound to a phrase copied verbatim out of issue-002.txt then returns a single row: issue_number 2, source_path raw/issue-002.txt, issue_date null because the filename rule never claimed one, and a bracketed snippet showing the phrase where it sits in the body. Once that one result points back at the right file on disk, the rest of the issues go into raw/ and the script runs again.
