6 Python Scripts + SQLite: Turning a 2TB Broadcast Archive into a Searchable Library

From Chaos to Searchable: The Birth of a Personal Search Engine

Many people have precious yet hard-to-manage data sitting on their home NAS — photos, videos, audio files scattered across deeply nested folders with no consistent naming conventions. When you want to find a specific piece of content, you're often left rummaging through your memory like searching for a needle in a haystack.

NAS (Network Attached Storage) is the most common private storage solution for homes and small offices. Consumer NAS products from brands like Synology, QNAP, and TrueNAS have seen steady sales growth in recent years, reflecting growing concerns about data sovereignty and privacy. However, the built-in search and management features offered by NAS vendors are typically limited to basic filename search and EXIF metadata indexing, with very limited support for deep retrieval of unstructured content like audio and video. This has created strong demand for DIY solutions — from media servers like Plex and Jellyfin to the self-built search engine described in this article.

Recently, a Reddit user shared his solution: facing a daily broadcast show archive spanning 15 years and exceeding 2TB, he used 6 Python scripts plus SQLite to transform a chaotic NAS folder into a searchable, playable audio library.

The value of this case isn't in cutting-edge technology — it's in demonstrating how to elegantly solve a real, thorny personal data management problem using the most humble tools available.

The Core Problem: 15 Years of Accumulated "Data Entropy"

The author's pain point was very specific: he had archives of a daily broadcast show on his home NAS, spanning over 15 years and containing thousands of episodes. These files were scattered across chaotic nested directories with wildly inconsistent filenames.

The result? When he wanted to find "that episode where they did that thing," his only option was to roughly recall what year the event happened and then manually dig through folders. For an archive with thousands of episodes, this was a nearly impossible task.

This is actually a classic unstructured data retrieval problem. Unstructured data refers to data without a predefined data model or that isn't organized in a predefined way — including audio, video, images, emails, social media posts, and more. According to IDC estimates, over 80% of the world's data is unstructured, growing at roughly 60% per year. Traditional relational databases and text search engines excel at handling structured data (like tables and fields) but are helpless when it comes to binary content like audio and video. To search this type of data, you typically need to first perform "content transcription" (e.g., speech-to-text) or "feature extraction" (e.g., image recognition to generate tags), building searchable text or vector indexes for unstructured content.

Audio itself can't be indexed by text search engines, and chaotic filenames provide no useful metadata. To bring this pile of data to life, the key was building a bridge between content and files.

A Six-Step Pipeline: Turning Chaos into Order

The author designed a clear data processing pipeline, with each Python script handling a specific job:

1. Inventory Scan

The first step was traversing the entire NAS directory tree and writing every audio file's information into a files table in SQLite. This step established a complete inventory of "physical files" and served as the foundation for everything that followed.

SQLite is the most widely deployed database engine in the world, with an estimated one trillion or more active instances. It was created by D. Richard Hipp in 2000, originally designed as an embedded database for U.S. Navy guided missile destroyers. Unlike client-server databases such as MySQL and PostgreSQL, SQLite is an "embedded" database — the entire database is a single file on disk, requiring no separate server process. Applications read and write to it directly through function calls. This design makes it the go-to choice for mobile apps (both Android and iOS have SQLite built in), embedded devices, and personal projects. It follows ACID transaction properties, supports most of the SQL standard, and a single database file can be up to 281TB in size. For the use case in this article, this means the author didn't need to install or maintain any database server — a single .db file held all the data.

2. Rundown Scraping

This is the cleverest part of the entire solution. The author discovered that the show had fan-maintained episode rundowns — detailed text records of every episode's content, accumulated over decades.

Fan-maintained episode guides are a quintessential product of internet crowdsourcing culture. Many long-running broadcast shows, podcasts, and TV series have detailed content records voluntarily compiled by devoted listeners or viewers, commonly found on dedicated wiki sites, Reddit communities, or specialized forums. The quality of this "crowdsourced metadata" is often surprisingly high — because it's painstakingly hand-compiled by people who genuinely love the content, its level of detail and accuracy sometimes surpasses official sources. In data engineering, this approach is called "leveraging existing data sources" — investigating whether usable external data sources already exist before producing your own.

He wrote a Python script to scrape these episode-by-episode text rundowns and store them in a rundowns table. This step essentially found a "text stand-in" for audio that couldn't be directly searched.

3. Date Extraction

The next challenge: how to match text rundowns to audio files? The author's answer was air dates.

He wrote parsing scripts to extract broadcast dates from the chaotic filenames and write them back to the SQLite database. The date became the natural primary key connecting the two worlds.

4. Full-Text Indexing (FTS5)

The author built a SQLite FTS5 full-text index on all the rundown text. FTS5 (Full-Text Search version 5) is a full-text search extension built into SQLite starting from version 3.9.0 (2015), an upgrade from the earlier FTS3/FTS4. Its core principle is building an "inverted index": unlike regular databases that store data row by row, an inverted index uses "terms" as keys, recording which documents each term appears in and at which positions. This is the same underlying principle used by search engines like Google. When a user searches for a phrase, the system only needs to find the intersection of matching terms in the index, rather than scanning all text row by row.

FTS5 supports Boolean queries (AND/OR/NOT), phrase matching, prefix search, BM25 relevance ranking, and more. Compared to Elasticsearch, which requires running a JVM and a dedicated cluster, FTS5's zero-deployment nature gives it a huge advantage in personal projects.

5 & 6. Application Layer and Search Page

Finally, a single-page search application: the user types in a phrase from a show, the system returns the matching episode, links it to the actual audio file through the air date, and starts playback directly.

Core Design: Three-Way Association Using Date as the Primary Key

The most elegant aspect of the entire solution is its join logic. The author summed up the architectural core in one sentence:

"The primary key for the join is the air date: the rundown date matches the date in the filename, and the filename date matches the audio file."

This results in a seamless search experience:

• Type "that segment where they argued about cake"
• → FTS5 full-text index locates the corresponding episode date
• → Displays the text snippet from the rundown
• → Audio playback starts automatically

This is a textbook example of "building an index at minimal cost." The author didn't attempt expensive ASR (Automatic Speech Recognition) — instead, he cleverly leveraged community-maintained text rundowns, transforming a speech search problem into a pure text search problem.

Regarding the cost of an ASR approach, a concrete comparison is worth making: Automatic Speech Recognition (ASR) technology has become more accessible in recent years thanks to open-source models like OpenAI's Whisper, but transcribing a large-scale audio library remains resource-intensive. Taking the 2TB audio library as an example, assuming an average bitrate of 128kbps, the total duration would be approximately 35,000 hours. Using cloud ASR services (such as Google Speech-to-Text or AWS Transcribe), the transcription cost per hour of audio is roughly $1–2, potentially totaling tens of thousands of dollars. Even running Whisper locally, on consumer-grade GPUs the real-time transcription speed is roughly 1/4 to 1/10 of the audio duration (depending on model size), meaning processing 35,000 hours of content could take months of continuous computation. This is why the author chose to leverage existing text rundowns rather than transcribe everything himself — it wasn't just a technical decision, but an extremely pragmatic economic one.

The Underrated Power of SQLite FTS5 Full-Text Search

At the end of his post, the author specifically emphasized: "SQLite FTS5 for personal-scale search is seriously underrated."

This statement deserves reflection from every developer. In today's tech culture, where people rush to deploy Elasticsearch, vector databases, and large language models, it's easy to overlook the power of lightweight tools.

As a built-in SQLite module, FTS5 offers several notable advantages:

• Zero maintenance: No need to deploy a separate service — a single database file handles everything
• Sufficient performance: For personal-scale datasets of thousands to hundreds of thousands of records, retrieval speed is in the millisecond range
• Portable: The entire library is a single file — backup and migration couldn't be simpler
• Zero cost: Fully open-source with no licensing fees

For comparison, while Elasticsearch is powerful, it runs on Java with a minimum recommended memory of 2GB, and in cluster mode typically requires 3 or more nodes for high availability. For personal projects, this level of resource overhead and operational complexity is clearly overkill. Vector databases (such as Pinecone, Milvus, Weaviate) are primarily designed for semantic search and AI application scenarios — when your needs are limited to precise keyword and phrase matching, traditional inverted indexes are actually faster and more reliable.

For personal projects and small-to-medium-scale NAS data management applications, SQLite FTS5 is often a better fit than large-scale search infrastructure.

Engineering Lessons from This Project

Though small in scope, this project embodies several engineering insights worth learning from:

First, leverage existing metadata. The author didn't struggle with speech recognition — instead, he discovered and utilized fan-maintained text rundowns, achieving maximum impact with minimal effort. Before tackling the "hard but correct" approach, check if there's a "clever and effective" shortcut. This mindset has a classic expression in software engineering: "The best code is the code you don't have to write." Similarly, the best data is data you don't have to produce yourself. Before starting any data processing project, spending a few hours researching existing data sources often yields far better ROI than jumping straight into development.

Second, find the right join key. Faced with chaotically formatted data, the author astutely identified "date" as the natural primary key spanning all three data sources, making the entire system's association logic clean and reliable. This reflects an important principle in data modeling: finding "invariants" in messy data. Filenames may vary wildly, directory structures may be deeply nested, but the air date — as an inherent property of the show — remains consistent across different data sources. Identifying and leveraging such invariants is one of a data engineer's core competencies.

Third, use tools that are just good enough. Six Python scripts, one SQLite database, one search page — no over-engineering, just the right amount of solution for the problem. This "just enough" engineering philosophy is deeply rooted in the Unix design tradition: each tool does one thing well, and complex functionality is achieved through composition. The author's six scripts, each handling a specific task and forming a pipeline, are a modern embodiment of this philosophy.

For anyone struggling with their own digital assets, this is an excellent template: true technical skill is often demonstrated not by stacking the latest and most complex tech, but by the judgment to solve real problems with simple tools.