SQLite Hybrid Search System (Keyword + Vector)

This project implements a hybrid search engine in SQLite, supporting both Full-Text/Keyword queries and Semantic Vector-Based Search using sqlite-vec and fastembed. The dataset is sourced from open-source data for ML.

---

⚡ Quickstart Reference

1. Setup Environment

Initialize the Python virtual environment and install the dependencies from the requirements.txt file:

# Create and activate virtual environment
python3 -m venv venv
source venv/bin/activate

# Upgrade pip and install requirements
pip install --upgrade pip
pip install -r requirements.txt

2. Import Data (If starting from scratch)

If washed.db is missing, you can import and index the data:

# 1. Import CSV records into SQLite (6.6 million rows)
python import_csv.py

# 2. Index the first 10,000 records for semantic search
python index_vectors.py 10000

3. Run Search Queries

Perform a hybrid keyword and vector query:

# General search
python search.py "<query>"

# Search specifically by name and address
python search.py --name "<name>" --addr "<address>"

---

📂 Project Directory Structure

• requirements.txt: Python package requirements (sqlite-vec, pysqlite3, fastembed, numpy).
• import_csv.py: Imports CSV records into the SQLite database.
• index_vectors.py: Generates vector embeddings and indexes them into the peoples_vec virtual table.
• search.py: Main search script combining keyword and semantic searches.
• washed.db.part.*: Split database chunks (each <95MB, tracked by Git LFS). They are automatically reassembled on the fly into washed.db when you run search or indexing queries.
• venv/: Local Python virtual environment for managing project dependencies.

---

🚀 Running Search Queries (Detailed)

Make sure you are in the activated virtual environment (source venv/bin/activate).

Search by Name:

python search.py --name "<name>"

Search by Address (combines keyword and semantic vector search):

python search.py --addr "<address>"

Search by both Name and Address:

python search.py --name "<name>" --addr "<address>"

Limit Results (using -k):

python search.py --name "<name>" -k 3

---

⚙️ Search Architecture Details

1. Deduplication: The database filters out duplicate records by using the unique id_card field during the search result merge phase.
2. Quality Filter: Vector matches with a cosine similarity score below 0.65 are automatically filtered out to ensure relevance.