PI Case Tracker with Full-Text Search

Build a portable Flask app that searches across case PDFs instantly.

Introduction

30 Second Summary

Investigators deal with mountains of case files. Reports, witness statements, and court filings pile up as PDFs. Finding a specific name or detail across dozens of documents means opening each one and searching manually.

In this project, you will build a portable case tracker that automatically extracts text from uploaded PDFs and makes every word instantly searchable. The entire app runs from a single folder with one command.

What You'll Build

Plug a flash drive into any machine with Python, run one command, and a browser-based case dashboard opens. Upload case PDFs, search across all their contents, and instantly see highlighted results ranked by relevance.

By the end of this project, you'll have:

  • A Flask web dashboard where you upload a PDF and immediately see it listed with its filename and upload timestamp.
  • Automatic PDF text extraction using pypdf that pulls content from every uploaded file and stores it in a SQLite database, so you never have to open a PDF just to search for a word.
  • A full-text search bar powered by SQLite FTS5 that finds any word or phrase across all case documents in milliseconds, ranks results by relevance, and highlights matching snippets so you see context without opening each file.
  • Secret Mission: Add searchable case notes with timestamps and tags that integrate into your unified search.

Are there any prerequisites?

You should be comfortable with basic Python (variables, functions, and if statements). No web development experience is needed.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

Set Up the Project on Your Flash Drive

A portable app needs a self-contained project structure. Everything the case tracker depends on (code, database, uploaded documents, and even the Python environment) must live inside a single folder that travels with the flash drive.

In this step, you will create that project structure, install the two libraries the app needs, and launch a minimal placeholder page to prove it all works.

In this step, get ready to:
  • Create the project directory with subfolders for templates, static files, and documents.
  • Set up a Python virtual environment and install Flask and pypdf.
  • Create a minimal Flask app and a launcher script, then verify the app runs.
Create the project directory structure

The project folder holds everything: app code, HTML templates, CSS, uploaded case documents, and the database. Putting it all in one directory means copying the folder to a flash drive is all you need to take it anywhere.

  • Open a terminal by pressing Ctrl+Alt+T.
  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create the project folder and its subfolders by running this command:
mkdir -p pi-case-tracker/{templates,static,documents}
  • Move into the project folder by running:
cd pi-case-tracker
  • Confirm the folders exist by running:
ls

You should see documents, static, and templates listed in the output.

Don't see all three folders?

Make sure you typed the curly braces exactly as shown: {templates,static,documents} with no spaces after the commas. If a folder is missing, create it individually with mkdir templates (or whichever is missing).

Help me fix my directory structure for the PI case tracker.

Set up the virtual environment and install packages

A virtual environment keeps the project's Python packages separate from the system. This means the flash drive carries its own copy of Flask and pypdf, independent of whatever is installed on the host machine.

  • Create a virtual environment inside the project by running:
python3 -m venv venv
  • Activate the virtual environment by running:
source venv/bin/activate

Your terminal prompt now shows (venv) at the beginning. This confirms Python commands will use the project's isolated environment.

  • Install Flask and pypdf by running:
pip install Flask==3.1.3 pypdf==6.15.0

You will see download and install progress for both packages and their dependencies. The final line confirms successful installation.

Why these two libraries?

Flask is a lightweight web framework that serves the browser-based dashboard. pypdf is a pure-Python library that extracts text from PDF files with zero external dependencies, making it perfect for a portable flash-drive app.

Seeing a 'pip not found' error?

Make sure you activated the virtual environment first. Your prompt should start with (venv). If it does not, run source venv/bin/activate again and retry the install.

Help me troubleshoot my pip install.

Create the Flask app and launcher script

Now you have the environment ready. Time to write the actual app file and a convenience script that launches it.

  • Open the project in VS Code by running:
code .

What does code . do?

The code command launches VS Code from the terminal. The dot (.) tells it to open the current folder as a project.

  • In VS Code, create a new file called app.py by clicking File in the top menu bar.
  • Click New File.
  • Type app.py as the filename and press Enter.
  • Paste the following code into app.py:
import os
from flask import Flask

# Set paths relative to this file so the app works from any directory
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DOCUMENTS_DIR = os.path.join(BASE_DIR, "documents")
DATABASE = os.path.join(BASE_DIR, "case.db")

# Create the Flask application
app = Flask(__name__)
app.secret_key = "case-tracker-secret-key"
app.config["UPLOAD_FOLDER"] = DOCUMENTS_DIR

# Ensure the documents folder exists
os.makedirs(DOCUMENTS_DIR, exist_ok=True)

@app.route("/")
def dashboard():
    return "<h1>PI Case Tracker</h1>"

if __name__ == "__main__":
    app.run(debug=True, port=5001)

What does this code do?

  • BASE_DIR calculates the folder this script lives in. All other paths are built relative to it, so the app works regardless of where the flash drive is mounted.
  • DOCUMENTS_DIR and DATABASE point to the case files and database inside the same project folder.
  • os.makedirs(DOCUMENTS_DIR, exist_ok=True) creates the documents folder if it does not already exist, preventing crashes on a fresh run.
  • The single route (/) returns a placeholder heading so you can verify the server starts.
  • Port 5001 avoids conflicts with other services that commonly use port 5000.
  • Save app.py by pressing Ctrl+S.
  • Create another new file called run.sh using the same File > New File method.
  • Paste the following into run.sh:
#!/bin/bash
cd "$(dirname "$0")"
source venv/bin/activate
python app.py

What does run.sh do?

This launcher script means you can start the app with a single command from any location. It changes to the script's own directory, activates the virtual environment, and starts the Flask server. On a flash drive, double-clicking this file is all it takes.

  • Save run.sh by pressing Ctrl+S.
  • Make the launcher script executable by running this in the VS Code terminal (open it with Ctrl+` if it is not visible):
chmod +x run.sh

Before you run the app, what do you think you will see when you open http://127.0.0.1:5001 in your browser?

  • Start the Flask server by running:
python app.py

You will see output indicating the server is running, including a line showing http://127.0.0.1:5001.

  • Open your browser and navigate to http://127.0.0.1:5001.

You should see a page with the heading "PI Case Tracker" displayed. That confirms Flask is serving your app from the project directory.

  • Return to the VS Code terminal and press Ctrl+C to stop the server.

Don't see the page in your browser?

  • Check the terminal for error messages. A ModuleNotFoundError: No module named 'flask' means the virtual environment is not active. Run source venv/bin/activate and try again.
  • Make sure you are visiting http://127.0.0.1:5001 (port 5001), not 5000.
  • If the terminal shows "Address already in use", another process is on that port. Stop it or change the port number in app.py.

Help me troubleshoot my Flask app not showing in the browser.

✔️ Awesome, I've got everything!

Great. Double check you have saved both files.

ⓧ I'd like to double check the full code

Here is the complete app.py for this step:

import os
from flask import Flask

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DOCUMENTS_DIR = os.path.join(BASE_DIR, "documents")
DATABASE = os.path.join(BASE_DIR, "case.db")

app = Flask(__name__)
app.secret_key = "case-tracker-secret-key"
app.config["UPLOAD_FOLDER"] = DOCUMENTS_DIR

os.makedirs(DOCUMENTS_DIR, exist_ok=True)

@app.route("/")
def dashboard():
    return "<h1>PI Case Tracker</h1>"

if __name__ == "__main__":
    app.run(debug=True, port=5001)

And the complete run.sh:

#!/bin/bash
cd "$(dirname "$0")"
source venv/bin/activate
python app.py

Your project skeleton is alive. Next up, you will build the case dashboard and wire up PDF file uploads so documents actually flow into the tracker.

Build the Case Dashboard with Document Upload

Your app runs, but all it shows is a bare heading. Uploaded PDFs would just be files in a folder with no way to browse or manage them from a browser.

The dashboard is your home base for the case. In this step, you will build a database-backed dashboard that lists every document you upload, plus a file upload form that saves PDFs and records them automatically.

In this step, get ready to:
  • Set up a SQLite database to store document records.
  • Build a dashboard page that displays all uploaded documents.
  • Add a file upload route that accepts and saves PDF files.
Set up the SQLite database

Every document you upload needs a record: its filename, where it lives on disk, and when it was added. SQLite stores all of this in a single file that travels with your project.

  • In app.py, find the import lines at the top of the file:
import os
from flask import Flask
  • Replace those two lines with these updated imports:
import os
import sqlite3
from datetime import datetime
from flask import Flask, render_template, request, redirect, url_for, flash

What are these new imports for?

  • sqlite3 lets Python talk to the SQLite database built into the standard library.
  • datetime timestamps each upload.
  • render_template, request, redirect, url_for, and flash are Flask helpers for serving HTML pages, handling form data, and showing status messages.
  • Add the database helper functions below the os.makedirs(DOCUMENTS_DIR, exist_ok=True) line by copying this code:

def get_db():
    # Connect to the SQLite database and enable column-name access
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row
    return conn

What does this code do?

get_db() opens a connection to case.db. Setting conn.row_factory = sqlite3.Row means you can access columns by name (like doc.filename) instead of by number.

  • Add the init_db() function directly below get_db() by copying this code:

def init_db():
    # Create the documents table if it doesn't already exist
    conn = get_db()
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS documents (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            filename TEXT NOT NULL,
            filepath TEXT NOT NULL,
            content TEXT,
            uploaded_at TEXT NOT NULL
        );
    """)
    conn.commit()
    conn.close()

What does this code do?

init_db() creates a documents table with columns for the file's name, path, extracted text content, and upload timestamp. The IF NOT EXISTS clause makes it safe to call on every startup without duplicating the table.

  • Scroll down to the bottom of app.py and find this line:
if __name__ == "__main__":
    app.run(debug=True, port=5001)
  • Add init_db() above app.run() so it looks like this:
if __name__ == "__main__":
    init_db()
    app.run(debug=True, port=5001)
  • Save app.py.
  • Stop the running app with Ctrl+C in your terminal, then restart it by running:
python app.py
  • Confirm the database was created by running this in a second terminal:
ls *.db

You should see case.db in the output. The database is ready.

Don't see case.db?

  • Make sure you added init_db() inside the if __name__ == "__main__": block, not outside it.
  • Check that your terminal is in the project directory (the same folder as app.py).
  • If the app crashed on startup, check the terminal for a traceback. A missing comma or typo in the imports will stop it from running.

Help me debug why case.db wasn't created when I run app.py

Now you need HTML templates so the dashboard route has something to render. Stop the app with Ctrl+C for now.

  • Create the base template that all pages will extend by creating a new file templates/base.html and pasting this code:
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PI Case Tracker</title>
    <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}">
</head>
<body>
    <nav>
        <a href="{{ url_for('dashboard') }}">Dashboard</a>
        <a href="{{ url_for('upload') }}">Upload Document</a>
        <a href="{{ url_for('search') }}">Search</a>
    </nav>
    <main>
        {% with messages = get_flashed_messages() %}
        {% if messages %}
        <div class="flash">
            {% for message in messages %}
            <p>{{ message }}</p>
            {% endfor %}
        </div>
        {% endif %}
        {% endwith %}
        {% block content %}{% endblock %}
    </main>
</body>
</html>

What does this template do?

This is the shell every page shares. It defines the navigation bar with links to Dashboard, Upload, and Search. It also handles flash messages (status alerts that appear after actions like uploading a file).

The {% block content %}{% endblock %} line is where each page inserts its own content.

  • Save templates/base.html.
  • Confirm the file exists by running:
ls templates/

You should see base.html listed.

  • Create the dashboard page by creating a new file templates/dashboard.html and pasting this code:
{% extends "base.html" %}
{% block content %}
<h1>Case Dashboard</h1>
<p>Total Documents: {{ documents|length }}</p>
<h2>Documents</h2>
{% if documents %}
<table>
    <thead>
        <tr>
            <th>Filename</th>
            <th>Uploaded</th>
            <th>Actions</th>
        </tr>
    </thead>
    <tbody>
        {% for doc in documents %}
        <tr>
            <td>{{ doc.filename }}</td>
            <td>{{ doc.uploaded_at }}</td>
            <td><a href="#">View</a></td>
        </tr>
        {% endfor %}
    </tbody>
</table>
{% else %}
<p>No documents uploaded yet. <a href="{{ url_for('upload') }}">Upload your first document.</a></p>
{% endif %}
{% endblock %}

What does this template do?

  • {% extends "base.html" %} inherits the navigation and layout from the base template.
  • The {% if documents %} block shows a table when documents exist, or a friendly "upload your first" link when the database is empty.
  • The {% for doc in documents %} loop creates one table row per uploaded document.
  • Now update the dashboard() route in app.py to query the database and render the template. Find this existing route:
@app.route("/")
def dashboard():
    return "<h1>PI Case Tracker</h1>"
  • Replace it with this updated version:
@app.route("/")
def dashboard():
    # Fetch all documents, newest first
    conn = get_db()
    documents = conn.execute(
        "SELECT * FROM documents ORDER BY uploaded_at DESC"
    ).fetchall()
    conn.close()
    return render_template("dashboard.html", documents=documents)

What does this code do?

The route opens a database connection, fetches every row from the documents table sorted by upload time (newest first), then passes that list to the template for rendering.

  • Save app.py and templates/dashboard.html.
  • Restart the app by running:
python app.py
  • Open http://127.0.0.1:5001 in your browser.

You should see a "Case Dashboard" heading with a navigation bar at the top and the message "No documents uploaded yet." The page has a working layout instead of bare text.

Seeing a TemplateNotFound error?

  • Make sure base.html and dashboard.html are inside a folder named exactly templates (not "template" without the s).
  • Check that the templates folder is in the same directory as app.py.

Help me fix a TemplateNotFound error in my Flask app

Add a file upload route

The dashboard can display documents, but there is no way to get files into the system yet. You need a route that accepts a PDF upload, saves the file to the documents/ folder, and records it in the database.

  • In app.py, add the upload route below the dashboard() function (above the if __name__ block) by copying this code:

@app.route("/upload", methods=["GET", "POST"])
def upload():
    if request.method == "POST":
        # Check that a file was actually submitted
        if "file" not in request.files:
            flash("No file selected.")
            return redirect(request.url)
        file = request.files["file"]
        if file.filename == "":
            flash("No file selected.")
            return redirect(request.url)
        # Only accept PDF files
        if file and file.filename.lower().endswith(".pdf"):
            filename = file.filename
            filepath = os.path.join(DOCUMENTS_DIR, filename)
            file.save(filepath)
            uploaded_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            conn = get_db()
            conn.execute(
                "INSERT INTO documents (filename, filepath, content, uploaded_at) VALUES (?, ?, ?, ?)",
                (filename, filepath, "", uploaded_at),
            )
            conn.commit()
            conn.close()
            flash(f"Uploaded: {filename}")
            return redirect(url_for("dashboard"))
        else:
            flash("Only PDF files are allowed.")
            return redirect(request.url)
    return render_template("upload.html")

What does this code do?

  • On a GET request, it renders the upload form (which you will create next).
  • On a POST request, it validates that a PDF was submitted, saves the file to the documents/ folder, and inserts a row into the database.
  • The content column is left as an empty string for now. You will fill it with extracted PDF text in the next step.
  • After a successful upload, it redirects back to the dashboard so the learner immediately sees the new entry.
  • Save app.py.
Wire up the upload form and test

The upload route references an upload.html template that does not exist yet. You need a simple form with a file picker and submit button.

  • Create a new file templates/upload.html and paste this code:
{% extends "base.html" %}
{% block content %}
<h1>Upload Document</h1>
<form method="POST" enctype="multipart/form-data">
    <label for="file">Select a PDF file:</label>
    <input type="file" name="file" id="file" accept=".pdf">
    <button type="submit">Upload</button>
</form>
{% endblock %}

What does this code do?

The enctype="multipart/form-data" attribute tells the browser to send the file as binary data instead of plain text. Without it, file uploads silently fail. The accept=".pdf" attribute filters the file picker to show only PDFs.

  • Save templates/upload.html.
  • Restart the app by pressing Ctrl+C and running:
python app.py

Before you test the upload, what do you expect to see on the dashboard after uploading a PDF? Think about what data the route saves to the database.

  • Open http://127.0.0.1:5001 in your browser.
  • Click Upload Document in the navigation bar.
  • Click the file picker and select any PDF file from your computer.
  • Click Upload.

You should be redirected to the dashboard. Your PDF filename now appears in the documents table with the current date and time in the "Uploaded" column. A green flash message confirms the upload.

No PDF handy?

Open any text editor, type a few sentences, and export or print to PDF. Alternatively, search for "sample PDF" online to download a test file.

Upload doesn't work or page shows an error?

  • If you see "Method Not Allowed", check that your @app.route("/upload", methods=["GET", "POST"]) includes both methods.
  • If you see "No file selected" after choosing a file, make sure the form tag has enctype="multipart/form-data" in upload.html.
  • If the file uploads but doesn't appear on the dashboard, check that init_db() is being called in the main block so the table exists.

Help me debug why my PDF upload isn't working in Flask

✔️ Awesome, I've got everything!

Great. Double check you have saved all three files: app.py, templates/base.html, templates/dashboard.html, and templates/upload.html.

ⓧ I'd like to double check the full code

Scroll up and confirm each code block is present in your project, in order:

  • app.py: updated imports, get_db() function, init_db() function, updated dashboard() route, upload() route, and init_db() call in the main block.
  • templates/base.html: full HTML shell with nav links and flash message handling.
  • templates/dashboard.html: extends base, shows document count and table.
  • templates/upload.html: extends base, file upload form with PDF filter.

Your case tracker now has a working dashboard and file upload system. Next up, you will make those uploaded PDFs actually useful by extracting their text so you can search through them.

Extract PDF Text and Add Basic Search

Right now your case tracker stores PDFs as files on disk, but their contents are locked inside. If you needed to find which report mentions a suspect's name, you would have to open every single PDF and read through it manually.

In this step, you will extract text from uploaded PDFs automatically and build a basic search page. The search uses SQL LIKE queries. You will see firsthand why this approach falls short for real investigation work.

In this step, get ready to:
  • Add automatic PDF text extraction using pypdf.
  • Build a search page that queries document content with SQL LIKE.
  • Observe the limitations of substring matching for real-world search.
Add PDF text extraction to uploads

The pypdf library can read a PDF file and return its text content as a plain Python string. You will add a helper function that loops through every page in a PDF and combines the extracted text.

  • In app.py, find the line that says from flask import Flask, render_template, request, redirect, url_for, flash and add this import directly below it:
from pypdf import PdfReader
  • Next, find the init_db() function (the one that ends with conn.close()). Add this new function directly below it:
def extract_text_from_pdf(filepath):
    try:
        reader = PdfReader(filepath)
        text = ""
        for page in reader.pages:
            page_text = page.extract_text()
            if page_text:
                text += page_text + "\n"
        return text.strip()
    except Exception as e:
        return f"[Error extracting text: {e}]"

What does this code do?

This function takes a file path to a PDF and returns all its text as one string.

  • PdfReader(filepath) opens the PDF file for reading.
  • The for page in reader.pages loop goes through every page and extracts whatever text it can find.
  • The try/except block catches corrupted or image-only PDFs gracefully instead of crashing the app.

Now you need to wire this function into the upload route so extraction happens automatically every time a PDF is uploaded.

  • In app.py, scroll down to the upload() function. Find this section:
            file.save(filepath)
            uploaded_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            conn = get_db()
            conn.execute(
                "INSERT INTO documents (filename, filepath, content, uploaded_at) VALUES (?, ?, ?, ?)",
                (filename, filepath, "", uploaded_at),
            )
            conn.commit()
            conn.close()
            flash(f"Uploaded: {filename}")
  • Replace that entire section with this updated version:
            file.save(filepath)
            content = extract_text_from_pdf(filepath)
            uploaded_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            conn = get_db()
            conn.execute(
                "INSERT INTO documents (filename, filepath, content, uploaded_at) VALUES (?, ?, ?, ?)",
                (filename, filepath, content, uploaded_at),
            )
            conn.commit()
            conn.close()
            flash(f"Uploaded and indexed: {filename}")

What changed here?

  • content = extract_text_from_pdf(filepath) calls your new function right after the file is saved to disk.
  • The INSERT now stores the extracted text in the content column instead of an empty string.
  • The flash message changes to "Uploaded and indexed" so you know extraction happened.
  • Save app.py.
  • Switch back to your browser and click Upload Document in the navigation.
  • Upload any PDF file you have on your machine.

You should see a green flash message that says "Uploaded and indexed:" followed by the filename. The word "indexed" confirms that text extraction ran successfully.

Still seeing just "Uploaded:" without "indexed"?

  • Make sure you saved app.py after making both changes (the import and the upload route).
  • Check that from pypdf import PdfReader appears at the top of your file. If Flask cannot import pypdf, the server will show an error in the terminal.
  • Confirm you replaced the old section completely, including the flash message line.

Need more help? Why isn't my PDF text extraction working?

Add a search page with LIKE query

With text now stored in the database, you can query it. The simplest approach is SQL LIKE with wildcards. This matches any row where the content column contains the search string as a substring.

  • In app.py, add this new route directly above the if __name__ == "__main__": block at the bottom of the file:
@app.route("/search")
def search():
    query = request.args.get("q", "").strip()
    results = []
    if query:
        conn = get_db()
        results = conn.execute(
            "SELECT * FROM documents WHERE content LIKE ?",
            (f"%{query}%",),
        ).fetchall()
        conn.close()
    return render_template("search.html", query=query, results=results)

What does this route do?

  • request.args.get("q", "") reads the search term from the URL query string (e.g. /search?q=witness).
  • WHERE content LIKE ? with %query% wildcards finds any document whose content contains the search term as a substring.
  • If the search box is empty, it skips the query and shows no results.
  • Save app.py.

Now you need a template for the search page to render results.

  • Create a new file called search.html inside the templates/ folder and paste this code:
{% extends "base.html" %}
{% block content %}
<h1>Search Documents</h1>
<form method="GET" action="{{ url_for('search') }}">
    <input type="text" name="q" value="{{ query }}" placeholder="Search case documents...">
    <button type="submit">Search</button>
</form>
{% if query %}
<h2>Results for "{{ query }}"</h2>
{% if results %}
<ul class="results">
    {% for result in results %}
    <li>
        <a href="#">{{ result.filename }}</a>
        <span class="date">{{ result.uploaded_at }}</span>
    </li>
    {% endfor %}
</ul>
{% else %}
<p>No results found.</p>
{% endif %}
{% endif %}
{% endblock %}

What does this template do?

  • The form sends a GET request with the search term as a q parameter in the URL.
  • Results show only the filename and upload date. There is no snippet or context about where the match occurred.
  • If no results match, it displays "No results found." so the learner can clearly see the failure.
  • Save templates/search.html.
  • Switch back to your browser and click Search in the navigation bar.
  • Type a word you know appears in the PDF you just uploaded and press Search.

You should see the filename of your uploaded PDF appear in the results list. The search works for exact substring matches.

Search page shows an error or no results?

  • Check that the PDF you are searching was uploaded AFTER you added the text extraction code. Documents uploaded in the previous step have empty content and will never match.
  • Confirm the file is named search.html (not Search.html) and lives inside the templates/ folder.
  • Look at your terminal for a traceback. A TemplateNotFound error means Flask cannot find the file.

Need more help? My search page isn't returning any results even though I uploaded a PDF.

✔️ Awesome, I've got everything!

Great. Make sure both app.py and templates/search.html are saved.

ⓧ I'd like to double check the full code

Scroll up and confirm your app.py has each of these pieces in order:

  • The from pypdf import PdfReader import at the top.
  • The extract_text_from_pdf() function below init_db().
  • The updated upload route with content = extract_text_from_pdf(filepath) and the "Uploaded and indexed" flash message.
  • The search() route above the if __name__ block.

Your templates/search.html should look like this:

{% extends "base.html" %}
{% block content %}
<h1>Search Documents</h1>
<form method="GET" action="{{ url_for('search') }}">
    <input type="text" name="q" value="{{ query }}" placeholder="Search case documents...">
    <button type="submit">Search</button>
</form>
{% if query %}
<h2>Results for "{{ query }}"</h2>
{% if results %}
<ul class="results">
    {% for result in results %}
    <li>
        <a href="#">{{ result.filename }}</a>
        <span class="date">{{ result.uploaded_at }}</span>
    </li>
    {% endfor %}
</ul>
{% else %}
<p>No results found.</p>
{% endif %}
{% endif %}
{% endblock %}
Hit the limits of LIKE search

Your search works for exact substring matches. But real investigation work demands more. Try pushing the search to see where it breaks down.

  • Think of two words that both appear in your uploaded PDF, but NOT right next to each other. For example, a first name and a location, or two keywords from different paragraphs.
  • Before you search, predict: if you type both words together (e.g. "witness downtown"), will the LIKE query find your document?
  • Type those two words separated by a space into the search bar and press Search.

You get "No results found." even though both words exist in the document. The LIKE query searches for the exact substring you typed. Because those two words do not appear adjacent in that exact order, the match fails.

  • Now look at the results you DID get earlier (when searching a single word). Notice there is no snippet showing WHERE in the document the word appears. There is no relevance ranking either.

Why does this matter for a PI?

Imagine you have 50 case PDFs and you need to find every document that mentions a suspect. With LIKE search:

  • Multi-word queries only match if the exact phrase appears as a contiguous substring. Searching "John downtown" misses a document that mentions "John" in paragraph 1 and "downtown" in paragraph 5.
  • Results have no ranking. A document that mentions the word once looks the same as one that mentions it 20 times.
  • There are no highlighted snippets. You have to open each document and manually find the matching section.

These are exactly the problems that full-text search engines solve. You will fix all of them in the next step.

You have built a working search. It handles exact substrings fine. But for real investigation work you need word-boundary matching, relevance ranking, and highlighted snippets. In the next step, you will replace this brittle LIKE query with SQLite FTS5 and fix every limitation you just experienced.

Upgrade to Full-Text Search with SQLite FTS5

In the previous step, you experienced the limitations of LIKE search firsthand. Partial words like "invest" missed documents containing "investigation", multi-word phrases failed, and results came back in no particular order.

SQLite's FTS5 (Full-Text Search version 5) solves every one of those problems. It tokenizes your text so word boundaries are understood, ranks results by relevance, supports prefix matching, and can highlight the matching text in context snippets.

In this step, get ready to:
  • Create an FTS5 virtual table and update the upload route to index new documents automatically.
  • Replace the LIKE search with an FTS5 MATCH query that ranks results and shows highlighted snippets.
  • Add a document detail page and wire up clickable links from the dashboard and search results.
Create the FTS5 virtual table

An FTS5 virtual table is a separate index structure that SQLite maintains alongside your regular table. It breaks text into individual tokens (words) so it can match queries against those tokens instantly, instead of scanning every row character by character.

  • In app.py, find the init_db() function and replace it with this updated version that creates both the documents table and the FTS5 index:
def init_db():
    conn = get_db()
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS documents (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            filename TEXT NOT NULL,
            filepath TEXT NOT NULL,
            content TEXT,
            uploaded_at TEXT NOT NULL
        );
        CREATE VIRTUAL TABLE IF NOT EXISTS documents_fts USING fts5(
            filename, content
        );
    """)
    conn.commit()
    conn.close()

What does this code do?

CREATE VIRTUAL TABLE IF NOT EXISTS documents_fts USING fts5(filename, content) creates a full-text search index on two columns: the filename and the extracted text content.

FTS5 is built into SQLite, so there is nothing extra to install. It ships with every Python installation that includes the sqlite3 module.

Update the upload route to index into FTS5

Every new PDF upload needs to be inserted into both the regular documents table and the documents_fts index. The FTS table uses the document's row ID to link back to the original record.

  • In the upload() function, find this block inside the if file and file.filename.lower().endswith(".pdf"): branch:
            conn = get_db()
            conn.execute(
                "INSERT INTO documents (filename, filepath, content, uploaded_at) VALUES (?, ?, ?, ?)",
                (filename, filepath, content, uploaded_at),
            )
            conn.commit()
            conn.close()
  • Replace that entire block with this updated version that captures the document ID and inserts into the FTS table:
            conn = get_db()
            # Insert into the regular documents table
            cursor = conn.execute(
                "INSERT INTO documents (filename, filepath, content, uploaded_at) VALUES (?, ?, ?, ?)",
                (filename, filepath, content, uploaded_at),
            )
            # Get the new row's ID and index it in FTS5
            doc_id = cursor.lastrowid
            conn.execute(
                "INSERT INTO documents_fts (rowid, filename, content) VALUES (?, ?, ?)",
                (doc_id, filename, content),
            )
            conn.commit()
            conn.close()

What does this code do?

  • cursor = conn.execute(...) stores the cursor object so you can access lastrowid after the insert.
  • doc_id = cursor.lastrowid captures the auto-generated ID from the documents table.
  • The second INSERT stores the same content in the FTS5 index, linking it back via rowid so you can join results back to the original document later.
  • Save app.py.
  • Stop the running app in your terminal by pressing Ctrl+C.

Your old database does not have the FTS5 table yet, and existing documents are not indexed. The simplest fix is to delete the database and start fresh so both tables are created together.

  • Delete the old database and restart the app by running these commands:
rm case.db
python app.py
  • Open http://127.0.0.1:5001 in your browser. You should see the empty dashboard with "No documents uploaded yet."
  • Click Upload Document and upload a PDF file you have handy. After uploading, confirm the file appears in the dashboard table.

Seeing an error about the FTS5 table?

  • Make sure you deleted case.db before restarting. If the old database still exists, the FTS5 virtual table creation might conflict with stale data.
  • Check that your init_db() has both CREATE statements inside the same executescript() call, separated by a semicolon.

Help me debug my FTS5 table creation in init_db()

Replace LIKE search with FTS5 MATCH

Now for the real upgrade. The LIKE query scanned every row looking for a substring. FTS5 uses a MATCH query that searches its pre-built token index, returns results ranked by relevance, and can extract highlighted snippets showing where the match occurred.

  • In app.py, find the entire search() route and replace it with this FTS5-powered version:
@app.route("/search")
def search():
    query = request.args.get("q", "").strip()
    results = []
    if query:
        conn = get_db()
        results = conn.execute(
            """
            SELECT d.id, d.filename, d.uploaded_at,
                   snippet(documents_fts, 1, '<mark>', '</mark>', '...', 30) AS snippet
            FROM documents_fts
            JOIN documents d ON d.id = documents_fts.rowid
            WHERE documents_fts MATCH ?
            ORDER BY bm25(documents_fts)
            """,
            (query,),
        ).fetchall()
        conn.close()
    return render_template("search.html", query=query, results=results)

What does this query do?

  • WHERE documents_fts MATCH ? searches the FTS5 index for tokenized matches instead of scanning every row.
  • snippet(documents_fts, 1, '<mark>', '</mark>', '...', 30) extracts a text excerpt around the matching term, wrapping it in HTML <mark> tags for highlighting. The 1 refers to the second column (content), and 30 caps the snippet at 30 tokens.
  • ORDER BY bm25(documents_fts) ranks results by relevance. BM25 returns negative values where more negative means more relevant, so a standard ascending sort puts the best matches first.
  • Save app.py.

The search results now include a snippet field, so you need to update the template to display it.

  • Open templates/search.html and replace its entire contents with this updated version:
{% extends "base.html" %}
{% block content %}
<h1>Search Documents</h1>
<form method="GET" action="{{ url_for('search') }}">
    <input type="text" name="q" value="{{ query }}" placeholder="Search case documents...">
    <button type="submit">Search</button>
</form>
{% if query %}
<h2>Results for "{{ query }}"</h2>
{% if results %}
<ul class="results">
    {% for result in results %}
    <li>
        <a href="{{ url_for('view_document', doc_id=result.id) }}">{{ result.filename }}</a>
        <span class="date">{{ result.uploaded_at }}</span>
        <p class="snippet">{{ result.snippet|safe }}</p>
    </li>
    {% endfor %}
</ul>
{% else %}
<p>No results found.</p>
{% endif %}
{% endif %}
{% endblock %}

What changed in this template?

  • Each result now links to url_for('view_document', doc_id=result.id) instead of # (you will create that route shortly).
  • The {{ result.snippet|safe }} line renders the highlighted excerpt. The |safe filter allows the <mark> HTML tags to render as actual highlighting instead of escaped text.
  • Save templates/search.html.

Search returning no results?

  • Make sure you re-uploaded a PDF after deleting case.db. Only documents uploaded after the FTS5 table was created will appear in search results.
  • Check that your query uses a word you know is in the document. FTS5 matches whole words by default. Use a trailing * for prefix matching (e.g. invest*).

Help me debug my FTS5 search query

Add a document detail page

Search results now show a snippet of matching text, but you also need a way to read the full extracted content of any document. A detail page lets you click through from search results or the dashboard to see everything.

  • In app.py, add this new route below the search() function and above the if __name__ == "__main__": block:
@app.route("/document/<int:doc_id>")
def view_document(doc_id):
    conn = get_db()
    document = conn.execute(
        "SELECT * FROM documents WHERE id = ?", (doc_id,)
    ).fetchone()
    conn.close()
    if document is None:
        flash("Document not found.")
        return redirect(url_for("dashboard"))
    return render_template("document.html", document=document)

What does this route do?

The URL pattern /document/<int:doc_id> captures a document ID from the URL. Flask converts it to an integer and passes it to the function. If no document matches that ID, the user gets redirected to the dashboard with a flash message.

  • Save app.py.
  • Create a new file templates/document.html and add this template:
{% extends "base.html" %}
{% block content %}
<h1>{{ document.filename }}</h1>
<p>Uploaded: {{ document.uploaded_at }}</p>
<h2>Extracted Text</h2>
<pre class="document-text">{{ document.content }}</pre>
<a href="{{ url_for('dashboard') }}">Back to Dashboard</a>
{% endblock %}

What does this template do?

The <pre class="document-text"> tag preserves the whitespace and line breaks from the extracted PDF text, so the content reads naturally instead of collapsing into a single block.

  • Save templates/document.html.

Now update the dashboard so the "View" links actually point to the document detail page instead of #.

  • In templates/dashboard.html, find this line inside the table row loop:
            <td><a href="#">View</a></td>
  • Replace it with:
            <td><a href="{{ url_for('view_document', doc_id=doc.id) }}">View</a></td>
  • Save templates/dashboard.html.

Your browser should auto-reload (Flask's debug mode detects file changes). Click Dashboard in the navigation, then click View next to your uploaded document. You should see the document's filename as a heading and the full extracted text below it.

Seeing a TemplateSyntaxError or 404?

  • Make sure document.html is saved inside the templates/ folder, not the project root.
  • Check that the route decorator uses <int:doc_id> with the angle brackets. Missing the type converter or the brackets causes a 404.

Help me fix my document detail page

Now for the moment of truth. Before you test the search, think about what you expect to happen differently from the LIKE search in the previous step.

  • Click Search in the navigation.
  • Pick a word you know is in your uploaded PDF. Type the first few letters followed by * to test prefix matching (for example, if your document mentions "investigation", type invest*).
  • Click Search.

You should see your document listed with a highlighted snippet showing the matching text wrapped in a yellow background. The prefix query finds all words starting with those letters. This is exactly what LIKE could not do.

Why does prefix matching matter for investigation work?

When reviewing case documents, you often know the root of a word but not its exact form. A search for invest* catches "investigation", "investigator", "investing", and "invested" in a single query. LIKE would require separate searches for each variation.

✔️ Awesome, I've got everything!

Great. Double check you have saved all your files: app.py, templates/search.html, templates/document.html, and templates/dashboard.html.

ⓧ I'd like to double check the full code

Here is each file you modified or created in this step. Scroll up to confirm each section is present in your code, in order.

app.py should contain these sections in order:

  • Imports and config (unchanged from before).
  • get_db() function (unchanged).
  • init_db() with both CREATE TABLE and CREATE VIRTUAL TABLE statements.
  • extract_text_from_pdf() function (unchanged).
  • dashboard() route (unchanged).
  • upload() route with cursor.lastrowid and FTS5 insert.
  • search() route with FTS5 MATCH, bm25, and snippet.
  • view_document() route.
  • if __name__ == "__main__": block at the bottom.

templates/search.html:

{% extends "base.html" %}
{% block content %}
<h1>Search Documents</h1>
<form method="GET" action="{{ url_for('search') }}">
    <input type="text" name="q" value="{{ query }}" placeholder="Search case documents...">
    <button type="submit">Search</button>
</form>
{% if query %}
<h2>Results for "{{ query }}"</h2>
{% if results %}
<ul class="results">
    {% for result in results %}
    <li>
        <a href="{{ url_for('view_document', doc_id=result.id) }}">{{ result.filename }}</a>
        <span class="date">{{ result.uploaded_at }}</span>
        <p class="snippet">{{ result.snippet|safe }}</p>
    </li>
    {% endfor %}
</ul>
{% else %}
<p>No results found.</p>
{% endif %}
{% endif %}
{% endblock %}

templates/document.html:

{% extends "base.html" %}
{% block content %}
<h1>{{ document.filename }}</h1>
<p>Uploaded: {{ document.uploaded_at }}</p>
<h2>Extracted Text</h2>
<pre class="document-text">{{ document.content }}</pre>
<a href="{{ url_for('dashboard') }}">Back to Dashboard</a>
{% endblock %}

templates/dashboard.html should have the View link updated to:

<td><a href="{{ url_for('view_document', doc_id=doc.id) }}">View</a></td>

Your case tracker now has real investigative power. You can search across all your case documents with prefix matching, see exactly where terms appear in highlighted context, and click through to read the full extracted text. All of this runs locally from a single SQLite file with zero external services.

Secret mission

Add Searchable Case Notes

Your case tracker handles documents well, but real investigations need quick notes too. Witness observations, phone call summaries, stakeout timestamps. Can you add a notes system that is also indexed for full-text search, so one search bar finds everything?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • A SQLite database file (case.db) storing document metadata, extracted text, and FTS5 indexes.
  • Uploaded PDF files in the documents/ folder.
  • A Python virtual environment in the venv/ folder.

Keep everything running

No action needed. Choose this if you are still actively building or plan to use the case tracker regularly.

  • Your project folder stays on your Desktop (or flash drive) ready to run anytime.
  • To start the app again, open a terminal in your project folder and run bash run.sh.
  • Your database, uploaded documents, and virtual environment remain intact.

Pause - I'll come back to this later

Stop the running Flask server to free up your terminal. Everything else stays in place for when you return.

  • Press Ctrl+C in the terminal running the Flask app to stop the server.
  • All your files, database, and virtual environment remain where they are.
  • When you are ready to return, open a terminal in your project folder and run bash run.sh to start the app again.

Delete - I don't want to use this again

Remove all project files and start completely fresh. This deletes your database, uploaded documents, and virtual environment permanently.

  • Press Ctrl+C in the terminal running the Flask app to stop the server.
  • Navigate to your Desktop (or wherever you created the project folder) by running:
cd ~/Desktop
  • Delete the entire project folder by running:
rm -rf pi-case-tracker
  • Confirm the folder is gone by running:
ls

You should no longer see your project folder in the output.

Nice Work!

Nice Work!

You just built a fully portable PI case tracker that extracts text from PDFs and searches across every document in milliseconds. Plug it into any machine with Python, run one command, and you have a browser-based investigation dashboard ready to go.

You've learned how to:

  • Build a Flask web application from scratch with routes, templates, and file upload handling.
  • Extract text from PDF documents programmatically using pypdf and index that content for instant retrieval.
  • Implement SQLite FTS5 full-text search with relevance ranking using bm25() and highlighted snippets using snippet().
  • Secret Mission: Added a case notes system with tags and timestamps, also indexed for full-text search alongside documents.

Ready to quiz yourself?