PI Case Tracker with Full-Text Search

Build a Flask case management system with PDF text extraction and search.

Introduction

30 Second Summary

Imagine you have 50 case files sitting on your computer. A client calls and asks which case mentions "John Smith." Without a search system, you would have to open every single PDF and skim through each one manually.

In this project, you will build a web-based case management system that stores investigation files, automatically extracts text from PDFs and Word documents, and lets you search across all file contents instantly from your browser.

What You'll Build

You'll have a self-hosted case tracker running on your Ubuntu server. Open your browser, see all your cases on a dark-themed dashboard, upload evidence documents, and type a name into the search bar to find exactly which document mentions it, with the matching text highlighted.

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

  • A case dashboard where you create cases, upload documents, and manage investigations from any device on your network.
  • Automatic text extraction that reads the contents of your PDFs and Word docs the moment you upload them, making every word searchable without opening a single file.
  • A full-text search engine that finds content buried inside documents, highlights the matching text in context, and links you straight to the right case.
  • Secret Mission: Generate professional PDF case reports summarizing an investigation and its complete file inventory.

Are there any prerequisites?

This guide assumes you have Python 3.14 and VS Code installed on an Ubuntu 26.04 server. Everything else you need (Flask, PyMuPDF, python-docx) is installed during the first step.

Before We Start

Before We Start...

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

You're building a self-hosted case management system that lets you upload investigation documents and instantly search across all file contents from any device on your network.

Set Up Your Project Environment

Your case tracker needs three Python libraries to work. Flask provides the web interface, PyMuPDF reads PDF files, and python-docx handles Word documents.

Installing these in a virtual environment keeps them isolated from other Python projects on your server. In this step, you will create the project structure and install everything you need.

In this step, get ready to:
  • Create a project directory and Python virtual environment.
  • Install Flask, PyMuPDF, and python-docx.
  • Create the project file structure.
Create a project directory and virtual environment

Before writing any code, you need a dedicated folder for the project and an isolated Python environment inside it.

  • Press Ctrl+Alt+T to open a terminal window.
  • Move to your Desktop and create the project folder by running these commands:
cd ~/Desktop
mkdir pi-case-tracker
cd pi-case-tracker
  • Confirm you are in the new directory by running:
pwd

You should see a path ending in pi-case-tracker.

  • Create a Python virtual environment and activate it by running:
python3 -m venv venv
source venv/bin/activate

Your terminal prompt now shows (venv) at the beginning. This means all packages you install will stay inside this project.

Not seeing (venv) in your prompt?

Make sure you typed source venv/bin/activate (not just venv/bin/activate). The source command is required to apply the environment to your current shell session.

If you see an error about python3 not being found, confirm Python is installed by running python3 --version.

help me fix my virtual environment activation

Install Flask, PyMuPDF, and python-docx

With your virtual environment active, you can install the three libraries your project depends on.

  • Install all three libraries by running:
pip install Flask pymupdf python-docx

You will see download progress bars followed by a message listing everything that was installed. Look for Successfully installed near the end of the output.

What are these three libraries?

Flask is a lightweight web framework that lets you build web pages and handle browser requests with Python. It powers the entire case tracker interface.

PyMuPDF reads PDF files and extracts their text content. python-docx does the same for Word documents. Together, they let your app search inside uploaded files.

Seeing permission errors or timeouts?

If you see a permissions error, make sure your prompt shows (venv). Installing without an active virtual environment tries to write to system directories.

If the download times out, check your internet connection and try the command again. pip will resume from where it left off.

help me fix pip install errors on Ubuntu

Create the project file structure

Your app needs a main Python file and two directories. One holds HTML templates for the web pages. The other stores uploaded files from investigations.

  • Create the application file and both directories by running:
touch app.py
mkdir templates uploads
  • Confirm the file structure by running:
ls

You should see app.py, templates, uploads, and venv listed in the output.

Before you run the next command, what do you think will happen if all three libraries installed correctly?

  • Verify everything works by running:
python -c "import flask; import pymupdf; import docx; print('All good!')"

You should see All good! printed. This confirms Flask, PyMuPDF, and python-docx are all importable from your virtual environment.

Seeing a ModuleNotFoundError?

If the import fails for any library, make sure your terminal still shows (venv) in the prompt. If not, reactivate with source venv/bin/activate and run the pip install command again.

Note that the package you install is python-docx (with a hyphen), but you import it as docx (no hyphen). This is expected.

help me fix my Python imports

Your environment is ready and all dependencies are installed. Next up, you will build the case dashboard and see your app running in the browser.

Build the Case Dashboard

Your project environment is ready with all dependencies installed. Now you need the core foundation: a database to store cases and web pages to display them.

In this step, you'll create a SQLite database schema, build Flask routes to handle page requests, and create HTML templates so you can see a working dashboard in your browser.

In this step, get ready to:
  • Create the SQLite database with a cases table.
  • Build Flask routes to display and create cases.
  • Create HTML templates for the dashboard and case creation form.
Set up the Flask app and database schema

Every Flask app starts with imports, configuration, and a way to talk to the database. You'll build the app foundation first, then verify the database creates itself on startup.

  • Open app.py in VS Code and paste the imports and app configuration:
import os
import sqlite3
from datetime import datetime

from flask import (
    Flask, request, redirect, url_for, render_template,
    send_from_directory, flash, g
)
from werkzeug.utils import secure_filename

import pymupdf
from docx import Document

# Create the Flask app
app = Flask(__name__)
app.secret_key = "pi-case-tracker-secret-key"
app.config["UPLOAD_FOLDER"] = os.path.join(os.path.dirname(os.path.abspath(__file__)), "uploads")
app.config["MAX_CONTENT_LENGTH"] = 50 * 1024 * 1024  # 50 MB max upload

# File types the app accepts
ALLOWED_EXTENSIONS = {"pdf", "docx", "doc", "txt", "png", "jpg", "jpeg", "gif", "bmp"}

# Ensure the uploads directory exists
os.makedirs(app.config["UPLOAD_FOLDER"], exist_ok=True)

What does this code do?

  • The Flask imports bring in everything you need for routes, templates, file handling, and flash messages.
  • pymupdf and docx are imported now so the app is ready for file processing in later steps.
  • MAX_CONTENT_LENGTH caps uploads at 50 MB. Flask automatically rejects anything larger.
  • ALLOWED_EXTENSIONS defines which file types your app will accept.
  • Below the code you just added, paste the database helper functions and startup code:
def get_db():
    # Reuse the same connection for the entire request
    if "db" not in g:
        g.db = sqlite3.connect("cases.db")
        g.db.row_factory = sqlite3.Row
    return g.db

@app.teardown_appcontext
def close_db(exception):
    db = g.pop("db", None)
    if db is not None:
        db.close()

def init_db():
    # Create the cases table schema
    conn = sqlite3.connect("cases.db")
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS cases (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            client TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'Open',
            description TEXT,
            created_at TEXT NOT NULL
        );
    """)
    conn.close()

with app.app_context():
    init_db()

if __name__ == "__main__":
    app.run(host="0.0.0.0", debug=True)

What does this code do?

  • get_db() opens a database connection and stores it in Flask's g object so every function in the same request shares one connection.
  • close_db() runs automatically after each request finishes, preventing connection leaks.
  • init_db() creates the cases table with columns for title, client, status, description, and creation date.
  • The with app.app_context(): init_db() block runs the database setup every time the app starts. If the table already exists, nothing changes.
  • Save app.py.
  • Test that the app starts and creates the database by running:
python app.py

You should see output showing the server is running on all addresses at port 5000.

  • Press Ctrl+C to stop the server.
  • Confirm the database file was created by running:
ls cases.db

You should see cases.db listed in the output.

Seeing an error when you run the app?

  • If you see ModuleNotFoundError, make sure your virtual environment is activated. You should see (venv) at the start of your terminal prompt.
  • If you see IndentationError, check that you pasted the code exactly. Python is sensitive to spacing.

Still stuck? Help me debug why my Flask app won't start. I'm getting an error after pasting the imports, config, and database helper code into app.py.

Build the dashboard routes and templates

Routes tell Flask what to do when someone visits a URL. You need two routes: one to display the case list and one to handle creating new cases.

  • In app.py, find the line that says with app.app_context(): and paste the following code above it:
def allowed_file(filename):
    return "." in filename and filename.rsplit(".", 1)[1].lower() in ALLOWED_EXTENSIONS

@app.route("/")
def dashboard():
    # Fetch all cases, newest first
    db = get_db()
    cases = db.execute(
        "SELECT * FROM cases ORDER BY created_at DESC"
    ).fetchall()
    return render_template("dashboard.html", cases=cases)

@app.route("/cases/new", methods=["GET", "POST"])
def create_case():
    if request.method == "POST":
        # Read form data submitted by the user
        title = request.form["title"]
        client = request.form["client"]
        description = request.form["description"]
        status = request.form.get("status", "Open")
        created_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        # Insert the new case into the database
        db = get_db()
        db.execute(
            "INSERT INTO cases (title, client, status, description, created_at) VALUES (?, ?, ?, ?, ?)",
            (title, client, status, description, created_at)
        )
        db.commit()
        flash("Case created successfully.")
        return redirect(url_for("dashboard"))
    return render_template("create_case.html")

What do these routes do?

  • allowed_file() checks whether an uploaded filename has an extension in the allowed set.
  • The / route queries all cases from the database and passes them to the dashboard template.
  • The /cases/new route handles both displaying the form (GET) and saving form data (POST). After saving, it redirects back to the dashboard.
  • The ? placeholders in the SQL query prevent injection attacks by separating data from commands.
  • Save app.py.
  • Verify the routes load without errors by running:
python app.py

You should see the server start again on port 5000. Press Ctrl+C to stop it.

Seeing NameError or IndentationError?

  • Make sure you pasted the routes code ABOVE the with app.app_context(): line, not below it.
  • Check that def allowed_file starts at column 0 (no leading spaces).

Need more help? Help me fix my Flask routes code. I'm getting an error after adding the dashboard and create_case routes above the app context line.

Now you need HTML templates so Flask has something to render when these routes are hit. Flask uses Jinja2 templating, where a base template defines shared layout and child templates fill in page-specific content.

  • Create a new file templates/base.html and paste the first part of the layout template:
<!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>
    <style>
        * { margin: 0; padding: 0; box-sizing: border-box; }
        body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif; background: #1a1a2e; color: #eee; min-height: 100vh; }
        .container { max-width: 900px; margin: 0 auto; padding: 20px; }
        header { background: #16213e; padding: 15px 20px; margin-bottom: 30px; border-radius: 8px; display: flex; justify-content: space-between; align-items: center; }
        header h1 { font-size: 1.4rem; color: #4fc3f7; }
        header a { color: #4fc3f7; text-decoration: none; }
        .flash { background: #2e7d32; color: white; padding: 10px 15px; border-radius: 5px; margin-bottom: 15px; }
        .btn { display: inline-block; padding: 10px 20px; background: #4fc3f7; color: #1a1a2e; text-decoration: none; border-radius: 5px; border: none; cursor: pointer; font-size: 0.95rem; font-weight: 600; }
        .btn:hover { background: #81d4fa; }
        .btn-secondary { background: #546e7a; color: white; }
        .card { background: #16213e; border-radius: 8px; padding: 20px; margin-bottom: 15px; }
        .card h2 { color: #4fc3f7; margin-bottom: 10px; }
        .card a { color: #4fc3f7; text-decoration: none; }
        input, textarea, select { width: 100%; padding: 10px; margin: 8px 0 15px 0; border-radius: 5px; border: 1px solid #333; background: #0f3460; color: #eee; font-size: 0.95rem; }
        textarea { min-height: 100px; resize: vertical; }
        label { font-weight: 600; color: #b0bec5; }

What is this template?

This is the base layout that every page in your app will share. The <style> block defines a dark theme with card-based layout, styled buttons, and form inputs.

  • Continue in the same file, pasting this directly below what you just added:
        table { width: 100%; border-collapse: collapse; }
        th, td { text-align: left; padding: 10px; border-bottom: 1px solid #333; }
        th { color: #4fc3f7; }
        .status-open { color: #66bb6a; }
        .status-closed { color: #ef5350; }
        .search-box { display: flex; gap: 10px; }
        .search-box input { flex: 1; margin: 0; }
        .search-box button { margin: 0; }
        mark { background: #ffeb3b; color: #000; padding: 1px 3px; border-radius: 2px; }
    </style>
</head>
<body>
    <div class="container">
        <header>
            <h1><a href="/">PI Case Tracker</a></h1>
            <a href="/">Dashboard</a>
        </header>
        {% with messages = get_flashed_messages() %}
        {% if messages %}
        {% for message in messages %}
        <div class="flash">{{ message }}</div>
        {% endfor %}
        {% endif %}
        {% endwith %}
        {% block content %}{% endblock %}
    </div>
</body>
</html>

What does this second half do?

  • The remaining CSS rules style tables, status badges, and the search box you'll add later.
  • The <body> section defines the shared page header with a link back to the dashboard.
  • The {% block content %} tag is where child templates inject their page-specific HTML.
  • Flash messages (like "Case created successfully") display automatically at the top of every page.
  • Save templates/base.html.
  • Create templates/dashboard.html and paste the dashboard template:
{% extends "base.html" %}
{% block content %}
<div class="card">
    <form action="/search" method="get" class="search-box">
        <input type="text" name="q" placeholder="Search inside all case files..." value="">
        <button type="submit" class="btn">Search</button>
    </form>
</div>
<div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 20px;">
    <h2 style="color: #4fc3f7;">Cases ({{ cases|length }})</h2>
    <a href="/cases/new" class="btn">+ New Case</a>
</div>
{% if cases %}
{% for case in cases %}
<div class="card">
    <h2><a href="/cases/{{ case.id }}">{{ case.title }}</a></h2>
    <p>Client: {{ case.client }} |
       Status: <span class="status-{{ case.status|lower }}">{{ case.status }}</span> |
       Created: {{ case.created_at }}</p>
    {% if case.description %}
    <p style="margin-top: 8px; color: #90a4ae;">{{ case.description[:150] }}{% if case.description|length > 150 %}...{% endif %}</p>
    {% endif %}
</div>
{% endfor %}
{% else %}
<div class="card">
    <p>No cases yet. Create your first case to get started.</p>
</div>
{% endif %}
{% endblock %}

What does this template do?

  • extends "base.html" inherits the shared layout, header, and styles.
  • The search box at the top will connect to the search route you build in a later step.
  • The {% for case in cases %} loop renders a card for each case in the database.
  • If no cases exist yet, it shows a friendly "No cases yet" message instead.
  • Save templates/dashboard.html.
  • Start the Flask server by running:
flask --app app run --host=0.0.0.0

Before you open the browser, what do you expect to see on the page?

  • Open your browser and navigate to http://your-server-ip:5000 (replace with your Ubuntu server's actual IP address).

You should see the PI Case Tracker dashboard with a dark theme, a search bar, a "Cases (0)" heading, a + New Case button, and the message "No cases yet. Create your first case to get started."

Seeing TemplateNotFound error?

  • Make sure your files are inside the templates/ directory (not the project root).
  • Confirm the filenames match exactly: base.html and dashboard.html (lowercase, no typos).
  • Run ls templates/ to verify both files are listed.

Still not working? Help me fix a TemplateNotFound error in my Flask app. I created base.html and dashboard.html but the server can't find them.

Now you need the form template so the + New Case button has somewhere to go.

  • Create templates/create_case.html and paste this form template:
{% extends "base.html" %}
{% block content %}
<div class="card">
    <h2>Create New Case</h2>
    <form method="post" action="/cases/new">
        <label for="title">Case Title</label>
        <input type="text" id="title" name="title" placeholder="e.g., Missing Person - Jane Doe" required>

        <label for="client">Client Name</label>
        <input type="text" id="client" name="client" placeholder="e.g., John Smith" required>

        <label for="status">Status</label>
        <select id="status" name="status">
            <option value="Open">Open</option>
            <option value="In Progress">In Progress</option>
            <option value="Closed">Closed</option>
        </select>

        <label for="description">Description</label>
        <textarea id="description" name="description" placeholder="Case details, notes, objectives..."></textarea>

        <button type="submit" class="btn">Create Case</button>
        <a href="/" class="btn btn-secondary" style="margin-left: 10px;">Cancel</a>
    </form>
</div>
{% endblock %}

What does this form do?

  • The form submits a POST request to /cases/new, which your create_case() route handles.
  • The required attribute on title and client fields prevents empty submissions.
  • The status dropdown defaults to "Open" but lets the user pick "In Progress" or "Closed" if needed.
  • Save templates/create_case.html.
  • In your browser, click the + New Case button on the dashboard.
  • Fill in a test case with any title (e.g., Missing Person - Jane Doe) and client name.
  • Click Create Case.

You should be redirected to the dashboard where your new case now appears in the list with a green "Case created successfully" flash message.

Form not submitting or case not appearing?

  • If clicking Create Case does nothing, check that the form has method="post" and action="/cases/new" in create_case.html.
  • If you see a 500 error, check your terminal for the traceback. A common cause is a typo in the column names in the INSERT statement.

Need help? Help me debug why my case creation form isn't working in Flask.

✔️ Awesome, I've got everything!

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

ⓧ I'd like to double check the full code

Your app.py should contain these sections in order. Scroll up and confirm each one is present:

  • Imports and app configuration (ending with os.makedirs call).
  • get_db() and close_db() functions.
  • init_db() function with the CREATE TABLE statement.
  • allowed_file(), dashboard(), and create_case() functions.
  • with app.app_context(): init_db() and if __name__ == "__main__" block at the very bottom.

Your templates/ folder should contain base.html, dashboard.html, and create_case.html. Run ls templates/ to confirm all three are present.

Your case dashboard is live and you can create cases from any device on your network. Next up, you'll add the ability to upload evidence files directly to a case.

Add File Uploads to Cases

Your case dashboard is live and you can create and view cases. Now you need a way to attach evidence documents to those cases.

In this step, you will add a file upload system that lets you attach PDFs, Word documents, images, and text files directly to a case. Each uploaded file will appear in a list on the case page with a download link.

In this step, get ready to:
  • Add a files table to your database for storing upload metadata.
  • Build a case detail page with an upload form and file list.
  • Create routes to handle uploading, storing, and downloading files.
Add the files table to your database

Your database currently only tracks cases. You need a second table to store metadata about each uploaded file, like its name, type, and which case it belongs to.

  • In app.py, find the init_db() function.
  • Replace the entire function with this updated version that creates both tables:
def init_db():
    conn = sqlite3.connect("cases.db")
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS cases (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            client TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'Open',
            description TEXT,
            created_at TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            case_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            original_name TEXT NOT NULL,
            file_type TEXT NOT NULL,
            uploaded_at TEXT NOT NULL,
            extracted_text TEXT,
            FOREIGN KEY (case_id) REFERENCES cases(id)
        );
    """)
    conn.close()

What does the files table store?

  • case_id links each file to its parent case using a foreign key.
  • filename is the stored name on disk (with a timestamp prefix to avoid collisions).
  • original_name keeps the human-readable name the user uploaded.
  • extracted_text will hold text pulled from documents later. For now it stays empty.
  • Save app.py.
  • Restart the Flask server by pressing Ctrl+C in your terminal and running this command:
flask --app app run --host=0.0.0.0

The server should start without errors. The new files table is now created alongside your existing cases table.

Seeing a database error on startup?

  • Check that the SQL in your init_db() function has the closing ); after each CREATE TABLE statement.
  • Make sure the triple-quoted string """ is closed properly after the second table definition.

Help me fix a database error in my init_db() function.

Build the case detail page

When a user clicks a case title on the dashboard, they need a dedicated page showing case information, an upload form, and a list of attached files.

  • In app.py, add the following route below the create_case() function:
@app.route("/cases/<int:case_id>")
def case_detail(case_id):
    db = get_db()
    # Look up the case and all its associated files
    case = db.execute("SELECT * FROM cases WHERE id = ?", (case_id,)).fetchone()
    if case is None:
        flash("Case not found.")
        return redirect(url_for("dashboard"))
    files = db.execute(
        "SELECT * FROM files WHERE case_id = ? ORDER BY uploaded_at DESC", (case_id,)
    ).fetchall()
    return render_template("case_detail.html", case=case, files=files)

What does this route do?

  • The URL pattern /cases/<int:case_id> captures the case ID from the URL and passes it to the function.
  • If the case does not exist, it flashes an error and redirects to the dashboard.
  • It queries both the case and its files, passing them to the template for rendering.
  • Save app.py.
  • Create a new file called case_detail.html inside your templates/ folder.
  • Add the case information card and upload form by pasting this code:
{% extends "base.html" %}
{% block content %}
<div class="card">
    <h2>{{ case.title }}</h2>
    <p>Client: {{ case.client }} |
       Status: <span class="status-{{ case.status|lower }}">{{ case.status }}</span> |
       Created: {{ case.created_at }}</p>
    {% if case.description %}
    <p style="margin-top: 10px; color: #b0bec5;">{{ case.description }}</p>
    {% endif %}
</div>

<div class="card">
    <h2>Upload File</h2>
    <form method="post" action="/cases/{{ case.id }}/upload" enctype="multipart/form-data">
        <input type="file" name="file" required>
        <button type="submit" class="btn" style="margin-top: 10px;">Upload</button>
    </form>
    <p style="margin-top: 8px; color: #78909c; font-size: 0.85rem;">Allowed: PDF, DOCX, TXT, PNG, JPG, GIF, BMP (max 50 MB)</p>
</div>
{% endblock %}

What does this template do?

  • The first card displays the case title, client, status, and description.
  • The second card has a file upload form. The enctype="multipart/form-data" attribute is required for file uploads to work.
  • The form posts to /cases/{{ case.id }}/upload which you will create in the next substep.

The page also needs a section to display uploaded files. This shows a table when files exist and a friendly message when the case has no files yet.

  • In case_detail.html, find the {% endblock %} tag at the bottom of the file.
  • Add this files table section directly above {% endblock %}:
<div class="card">
    <h2>Files ({{ files|length }})</h2>
    {% if files %}
    <table>
        <tr>
            <th>File Name</th>
            <th>Type</th>
            <th>Uploaded</th>
            <th>Action</th>
        </tr>
        {% for file in files %}
        <tr>
            <td>{{ file.original_name }}</td>
            <td>{{ file.file_type|upper }}</td>
            <td>{{ file.uploaded_at }}</td>
            <td><a href="/uploads/{{ file.filename }}" class="btn btn-secondary" style="padding: 5px 10px; font-size: 0.8rem;">Download</a></td>
        </tr>
        {% endfor %}
    </table>
    {% else %}
    <p>No files uploaded yet.</p>
    {% endif %}
</div>

What does this section do?

  • It shows a count of files in the heading.
  • If files exist, it renders a table with the file name, type, upload date, and a download link.
  • If no files are uploaded yet, it shows a placeholder message.
  • Save case_detail.html.
  • Restart the Flask server and open your browser to http://your-server-ip:5000.
  • Click on a case title in the dashboard.

You should see the case detail page with the case information, an upload form, and a "Files (0)" section showing "No files uploaded yet."

Page not loading or showing an error?

  • If you see a template error, check that case_detail.html is inside the templates/ folder, not the project root.
  • Make sure your file starts with {% extends "base.html" %} and that the quotes around base.html are straight quotes, not curly quotes.

Help me fix my case detail page template error.

Handle file uploads and downloads

The upload form submits to a route that does not exist yet. You need a route to receive the file, validate it, save it to disk, and record its metadata in the database.

  • In app.py, add the upload route below the case_detail() function:
@app.route("/cases/<int:case_id>/upload", methods=["POST"])
def upload_file(case_id):
    if "file" not in request.files:
        flash("No file selected.")
        return redirect(url_for("case_detail", case_id=case_id))
    file = request.files["file"]
    if file.filename == "":
        flash("No file selected.")
        return redirect(url_for("case_detail", case_id=case_id))
    if file and allowed_file(file.filename):
        # Secure filename, prepend timestamp, save file, then store metadata
        original_name = file.filename
        filename = secure_filename(file.filename)
        timestamp = datetime.now().strftime("%Y%m%d%H%M%S")
        filename = f"{timestamp}_{filename}"
        filepath = os.path.join(app.config["UPLOAD_FOLDER"], filename)
        file.save(filepath)
        ext = original_name.rsplit(".", 1)[1].lower()
        extracted_text = None
        uploaded_at = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        db = get_db()
        db.execute(
            "INSERT INTO files (case_id, filename, original_name, file_type, uploaded_at, extracted_text) VALUES (?, ?, ?, ?, ?, ?)",
            (case_id, filename, original_name, ext, uploaded_at, extracted_text)
        )
        db.commit()
        flash(f"File '{original_name}' uploaded successfully.")
    else:
        flash("File type not allowed.")
    return redirect(url_for("case_detail", case_id=case_id))

What does this route do?

  • It first validates that a file was actually submitted and that the file extension is allowed.
  • secure_filename() strips dangerous characters from the filename to prevent path traversal attacks.
  • A timestamp prefix is added so two files with the same name do not overwrite each other.
  • extracted_text is set to None for now. You will add text extraction in the next step.

You also need a route to serve uploaded files back to the browser when a user clicks a download link.

  • Add this download route directly below the upload_file() function:
@app.route("/uploads/<filename>")
def download_file(filename):
    # Serve the requested file from the uploads directory
    return send_from_directory(app.config["UPLOAD_FOLDER"], filename)

What does this route do?

send_from_directory() safely serves a file from the uploads folder. It handles content types and prevents directory traversal.

  • Save app.py.
  • Restart the Flask server.

Before you test, what do you expect to happen when you upload a PDF to a case? Will it appear immediately in the files list?

  • Open your browser and navigate to a case by clicking its title on the dashboard.
  • Click the file input and select any PDF file from your computer.
  • Click the Upload button.

You should see a green flash message confirming the upload. The file now appears in the "Files (1)" table with its name, type, upload timestamp, and a Download link.

  • Upload a second file to the same case and confirm both files appear in the table.
  • Click the Download link on one of the files to confirm it downloads correctly.

Upload not working or file not appearing?

  • If you see "File type not allowed", check that the file extension is one of: pdf, docx, doc, txt, png, jpg, jpeg, gif, bmp.
  • If you see "No file selected" even after choosing a file, make sure the form in case_detail.html has enctype="multipart/form-data" on the form tag.
  • If the download link gives a 404, verify that the uploads/ folder exists in your project directory.

Help me debug my file upload route.

✔️ Awesome, I've got everything!

Great. Double check that you have saved both app.py and templates/case_detail.html.

ⓧ I'd like to double check the full code

Your app.py should contain these sections in order. Scroll up and confirm each one is present:

  • Imports, app config, and ALLOWED_EXTENSIONS (unchanged from Step 2).
  • get_db() and close_db() helpers (unchanged).
  • init_db() with both cases and files CREATE TABLE statements.
  • allowed_file() helper (unchanged).
  • dashboard() route and create_case() route (unchanged).
  • case_detail() route (new this step).
  • upload_file() route (new this step).
  • download_file() route (new this step).
  • The with app.app_context(): init_db() block and if __name__ == "__main__": at the bottom.

Your templates/case_detail.html should contain three cards: case info, upload form, and files table. Confirm it starts with {% extends "base.html" %} and ends with {% endblock %}.

Your case tracker now accepts file uploads and stores them with metadata. But there is a problem. If you uploaded a PDF full of important names and dates, you have no way to search inside it. The next step solves that by extracting text from every uploaded document.

Extract Text and Build the Search Index

Your case tracker can store files, but their contents are locked away. If you uploaded a PDF mentioning "John Smith", you have no way to find it without opening every file manually.

This step teaches the app to read inside uploaded documents and build a search index. You will add text extraction for PDFs, Word documents, and plain text files, then create a full-text search index that makes every word inside every document instantly queryable.

In this step, get ready to:
  • Add text extraction functions for PDFs, Word docs, and plain text files.
  • Create an FTS5 virtual table with automatic sync triggers.
  • Hook text extraction into the upload flow so every new file is automatically indexed.
Add text extraction functions for uploaded files

Each uploaded file type needs different logic to pull out its text content. PDFs use PyMuPDF to iterate through pages, Word documents use python-docx to read paragraphs, and plain text files are read directly.

  • Stop the running Flask server by pressing Ctrl+C in your terminal.
  • In VS Code, open app.py and find the allowed_file function.
  • Add the following extract_text function directly below allowed_file:
def extract_text(filepath, filename):
    ext = filename.rsplit(".", 1)[1].lower()
    text = ""
    try:
        if ext == "pdf":
            doc = pymupdf.open(filepath)
            for page in doc:
                text += page.get_text()
            doc.close()
        elif ext in ("docx", "doc"):
            doc = Document(filepath)
            for paragraph in doc.paragraphs:
                text += paragraph.text + "\n"
        elif ext == "txt":
            with open(filepath, "r", errors="ignore") as f:
                text = f.read()
    except Exception as e:
        text = f"[Error extracting text: {e}]"
    return text

What does this code do?

This function takes a file path and filename, detects the file extension, and extracts readable text from it.

  • For PDFs, pymupdf.open() opens the file and page.get_text() pulls text from each page.
  • For Word docs, Document() loads the file and iterates through its paragraphs.
  • For plain text, it reads the file directly with errors="ignore" to handle unusual encodings gracefully.
  • If anything goes wrong, it catches the error instead of crashing the upload.
  • Save app.py.
  • Create a quick test file by running this command in your terminal:
echo "surveillance report for case 42" > test_extract.txt
  • Verify that extract_text works by running:
python -c "from app import extract_text; print(extract_text('test_extract.txt', 'test_extract.txt'))"

You should see surveillance report for case 42 printed in your terminal.

Seeing an ImportError?

Make sure your virtual environment is active. You should see (venv) at the start of your terminal prompt. If not, activate it with source venv/bin/activate and try again.

If you see a message about cases.db, that is expected. The import triggers init_db() which is fine.

Help me debug my extract_text function

Create the FTS5 search index

SQLite has a built-in full-text search engine called FTS5. It creates a special virtual table that stores an optimized search index alongside your regular data.

Why FTS5 instead of LIKE queries?

A LIKE '%keyword%' query scans every row in the table sequentially. With 50 cases and hundreds of documents, this becomes painfully slow.

FTS5 builds an inverted index (like the index at the back of a textbook) that maps each word to the rows containing it. Lookups are nearly instant regardless of table size. It also supports relevance ranking and snippet extraction.

  • In app.py, scroll to the init_db() function.
  • Find the closing ); of the CREATE TABLE IF NOT EXISTS files statement.
  • Add the following SQL directly after that closing ); but still inside the triple-quoted string:
        CREATE VIRTUAL TABLE IF NOT EXISTS files_fts USING fts5(
            original_name,
            extracted_text,
            content='files',
            content_rowid='id',
            tokenize='porter unicode61'
        );

        CREATE TRIGGER IF NOT EXISTS files_ai AFTER INSERT ON files BEGIN
            INSERT INTO files_fts(rowid, original_name, extracted_text)
            VALUES (new.id, new.original_name, new.extracted_text);
        END;

        CREATE TRIGGER IF NOT EXISTS files_ad AFTER DELETE ON files BEGIN
            INSERT INTO files_fts(files_fts, rowid, original_name, extracted_text)
            VALUES ('delete', old.id, old.original_name, old.extracted_text);
        END;

        CREATE TRIGGER IF NOT EXISTS files_au AFTER UPDATE ON files BEGIN
            INSERT INTO files_fts(files_fts, rowid, original_name, extracted_text)
            VALUES ('delete', old.id, old.original_name, old.extracted_text);
            INSERT INTO files_fts(rowid, original_name, extracted_text)
            VALUES (new.id, new.original_name, new.extracted_text);
        END;

What does this SQL do?

  • The CREATE VIRTUAL TABLE statement creates an FTS5 index that tracks original_name and extracted_text from the files table.
  • The tokenize='porter unicode61' setting enables Porter stemming, so searching for "running" also matches "run" and "runs".
  • The three triggers (files_ai, files_ad, files_au) keep the search index automatically in sync whenever rows are inserted, deleted, or updated in the files table.
  • Save app.py.
  • You need to delete the existing database so the new schema is created fresh. Run:
rm -f cases.db
  • Restart the Flask server to trigger init_db() and create the new tables by running:
flask --app app run --host=0.0.0.0
  • The server should start without errors. Stop it again with Ctrl+C so you can verify the table was created.
  • Confirm the FTS5 table exists by running:
python -c "import sqlite3; conn = sqlite3.connect('cases.db'); print([row[0] for row in conn.execute(\"SELECT name FROM sqlite_master WHERE type IN ('table','trigger')\").fetchall()])"

You should see files_fts, files_ai, files_ad, and files_au in the output list alongside your existing tables.

Server crashed on startup?

If you see a SQL syntax error, double-check that the new SQL sits inside the existing triple-quoted string (between conn.executescript(""" and the closing """)). Each statement must end with a semicolon.

If files_fts is missing from the output, make sure you placed the new SQL after the files table's closing ); and before the final """.

Help me fix the SQL error in my init_db function

Connect text extraction to file uploads

The extraction function exists and the search index is ready. Now you need to call extract_text() during the upload process so every new file gets its text stored and indexed automatically.

  • In app.py, scroll to the upload_file route.
  • Find this line:
        extracted_text = None
  • Replace None with a call to extract_text, so the line now looks like this:
        extracted_text = extract_text(filepath, original_name)

Why pass both filepath and original_name?

The filepath tells the function where the file lives on disk (with the timestamp prefix). The original_name provides the real file extension so the function knows which extraction method to use.

  • Save app.py.

Before you test, think about what should happen when you upload a text file containing the word "surveillance". Do you expect the FTS query to find it?

  • Start the Flask server by running:
flask --app app run --host=0.0.0.0
  • Open your browser and navigate to your app at http://your-server-ip:5000.
  • Create a new case (or open an existing one).
  • Upload the test_extract.txt file you created earlier (it contains "surveillance report for case 42").
  • Stop the server with Ctrl+C so you can query the database.
  • Verify the text was extracted and indexed by running:
python -c "import sqlite3; conn = sqlite3.connect('cases.db'); print(conn.execute(\"SELECT extracted_text FROM files_fts WHERE files_fts MATCH 'surveillance'\").fetchall())"

You should see a result containing surveillance report for case 42 in the output. The text was extracted on upload and the FTS5 index found it instantly.

Getting an empty list?

If the query returns [], make sure you uploaded the file AFTER making the code changes in this step. Files uploaded in previous steps have NULL for extracted_text and are not in the FTS index.

Check that extracted_text = extract_text(filepath, original_name) appears in your upload_file route, not extracted_text = None.

Help me debug why my FTS5 query returns empty

✔️ Awesome, I've got everything!

Great. Double check you have saved app.py with all three changes: the extract_text function, the FTS5 schema in init_db, and the updated line in upload_file.

ⓧ I'd like to double check the full code

Your app.py is now longer than 30 lines, so confirm these sections are present in order:

  • Imports (os, sqlite3, datetime, Flask modules, pymupdf, Document) at the top.
  • App config (secret_key, UPLOAD_FOLDER, MAX_CONTENT_LENGTH, ALLOWED_EXTENSIONS).
  • get_db() and close_db() functions.
  • init_db() with cases table, files table, files_fts virtual table, and three triggers (files_ai, files_ad, files_au).
  • allowed_file() function.
  • extract_text() function handling PDF, DOCX/DOC, and TXT.
  • Routes: dashboard, create_case, case_detail, upload_file (using extract_text(filepath, original_name)), download_file.
  • The init_db() call and __main__ block at the bottom.
  • Clean up the test file by running:
rm test_extract.txt

Your app now reads inside every uploaded document and indexes its text for search. Next up, you will build the search bar and results page that lets users query this index from the browser.

Implement Full-Text Search

In the last step, you built the engine that extracts text from uploaded files and indexes it in an FTS5 virtual table. The data is there, ready to be queried.

The problem is that there is no way for users to actually search from the browser. The dashboard has a search bar, but clicking it leads nowhere useful. This step wires that search bar to the FTS5 index so users can type a word and instantly see which files contain it.

In this step, get ready to:
  • Build a search route that queries the FTS5 index with ranked results.
  • Create a search results template with highlighted text snippets.
  • Verify end-to-end search across all uploaded case files.
Add the search route to your Flask app

Your dashboard already has a search form that submits to /search. Right now, that route does not exist in your app, so clicking the search button gives an error. You need a route that accepts the query, runs it against the FTS5 index, and passes the results to a template.

  • In app.py, find the download_file route (the one that returns files from the uploads folder).
  • Add the following search route directly below it, before the with app.app_context(): line:
@app.route("/search")
def search():
    query = request.args.get("q", "").strip()
    results = []
    if query:
        db = get_db()
        results = db.execute("""
            SELECT
                files.id,
                files.case_id,
                files.original_name,
                files.file_type,
                files.uploaded_at,
                cases.title AS case_title,
                snippet(files_fts, 1, '<mark>', '</mark>', '...', 30) AS snippet
            FROM files_fts
            JOIN files ON files.id = files_fts.rowid
            JOIN cases ON cases.id = files.case_id
            WHERE files_fts MATCH ?
            ORDER BY rank
        """, (query,)).fetchall()
    return render_template("search_results.html", query=query, results=results)

What does this code do?

This route handles GET requests to /search with a query parameter q.

  • files_fts MATCH ? runs the full-text search query against indexed content. Porter stemming means searching "running" also matches "run" and "runs".
  • snippet(files_fts, 1, '<mark>', '</mark>', '...', 30) extracts a text preview around the match, wrapping matched terms in <mark> tags for highlighting.
  • ORDER BY rank sorts results by relevance, putting the best matches first.
  • The JOIN statements connect the FTS index back to the files and cases tables so you can display the filename and case title alongside the snippet.
  • Save app.py.
Create the search results template

The search route renders a template called search_results.html that does not exist yet. This template needs to show the search bar (so users can refine their query), the result count, and a card for each matched file with a highlighted snippet.

  • Create a new file called search_results.html inside your templates/ folder and paste in the following code:
{% extends "base.html" %}
{% block content %}
<div class="card">
    <form action="/search" method="get" class="search-box">
        <input type="text" name="q" placeholder="Search inside all case files..." value="{{ query }}">
        <button type="submit" class="btn">Search</button>
    </form>
</div>

<h2 style="color: #4fc3f7; margin-bottom: 15px;">
    {% if query %}
    Results for "{{ query }}" ({{ results|length }} found)
    {% else %}
    Enter a search term above
    {% endif %}
</h2>

{% if query and results %}
{% for result in results %}
<div class="card">
    <h2><a href="/cases/{{ result.case_id }}">{{ result.case_title }}</a></h2>
    <p style="margin-bottom: 8px;">File: {{ result.original_name }} ({{ result.file_type|upper }}) | Uploaded: {{ result.uploaded_at }}</p>
    <p style="color: #b0bec5; font-size: 0.9rem;">...{{ result.snippet|safe }}...</p>
</div>
{% endfor %}
{% elif query %}
<div class="card">
    <p>No results found for "{{ query }}". Try different keywords.</p>
</div>
{% endif %}
{% endblock %}

What does this template do?

  • The search bar at the top pre-fills with the current query so users can tweak their search without retyping.
  • Each result card links to the parent case and shows the file name, type, upload date, and a snippet of matching text.
  • result.snippet|safe tells Jinja2 to render the <mark> tags as HTML so matched words appear highlighted in yellow.
  • Save templates/search_results.html.

Seeing a TemplateNotFound error?

  • Make sure the file is saved as search_results.html (with an underscore, not a hyphen) inside the templates/ folder.
  • Confirm your templates/ folder is in the same directory as app.py. Flask looks for templates relative to the app file.

Why can't Flask find my search_results template?

Test full-text search end to end

Everything is wired up. The dashboard search form sends queries to the new route, which queries the FTS5 index and renders matching results. Time to see it work.

  • If your Flask server is still running, stop it with Ctrl+C.
  • Restart it by running:
flask --app app run --host=0.0.0.0
  • Open your browser and go to http://your-server-ip:5000.

Before you type anything into the search bar, think about what you expect to see. You uploaded a PDF or document in the previous step. What word from inside that document would you search for? Do you think it will show up with a highlighted snippet?

  • In the search bar on the dashboard, type a word you know exists inside one of your uploaded documents.
  • Click the Search button.

You should see the search results page showing the matching file, its parent case name as a clickable link, and a text snippet with your search term highlighted in yellow.

What should I see?

The results page shows a heading like "Results for 'surveillance' (1 found)" and below it a card with the case title, filename, file type, upload date, and a snippet of the document text with your search term wrapped in a yellow highlight. Clicking the case title takes you to that case's detail page.

Search returns no results?

  • Make sure you are searching for a word that actually appears inside the document content, not just the filename.
  • If you uploaded files before adding the FTS5 table and triggers in Step 4, those files were never indexed. Try uploading a new file to a case and searching for a word from that file.
  • Image files (PNG, JPG) do not have extractable text. Search only finds content from PDFs, Word docs, and text files.

Help me debug why my FTS5 search returns no results.

✔️ Awesome, I've got everything!

Great. Double check you have saved both app.py and templates/search_results.html.

ⓧ I'd like to double check the full code

Here is the complete search_results.html template. Confirm yours matches:

{% extends "base.html" %}
{% block content %}
<div class="card">
    <form action="/search" method="get" class="search-box">
        <input type="text" name="q" placeholder="Search inside all case files..." value="{{ query }}">
        <button type="submit" class="btn">Search</button>
    </form>
</div>

<h2 style="color: #4fc3f7; margin-bottom: 15px;">
    {% if query %}
    Results for "{{ query }}" ({{ results|length }} found)
    {% else %}
    Enter a search term above
    {% endif %}
</h2>

{% if query and results %}
{% for result in results %}
<div class="card">
    <h2><a href="/cases/{{ result.case_id }}">{{ result.case_title }}</a></h2>
    <p style="margin-bottom: 8px;">File: {{ result.original_name }} ({{ result.file_type|upper }}) | Uploaded: {{ result.uploaded_at }}</p>
    <p style="color: #b0bec5; font-size: 0.9rem;">...{{ result.snippet|safe }}...</p>
</div>
{% endfor %}
{% elif query %}
<div class="card">
    <p>No results found for "{{ query }}". Try different keywords.</p>
</div>
{% endif %}
{% endblock %}

For app.py, confirm your search() route is placed between the download_file route and the with app.app_context(): block. The route should use @app.route("/search") and execute a files_fts MATCH query with snippet() and ORDER BY rank. Scroll up to the code chunk in this step to compare.

Your case management system is fully functional. You can create cases, upload documents, and search inside all of their contents from a single search bar. Next up is an optional Secret Mission where you generate professional PDF case reports.

Secret mission

Generate a PDF Case Report

Investigators need to produce professional reports for clients summarizing a case and its evidence. Can you use PyMuPDF's PDF creation API to generate a downloadable report from any case page?

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely on your Ubuntu server using local files and an in-process SQLite database. There are no ongoing costs or cloud resources to worry about.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Resources you used:

  • Flask development server running on port 5000.
  • SQLite database file (cases.db) storing case data and FTS5 search indexes.
  • Uploaded files stored in the uploads/ directory.
  • Python virtual environment with Flask, PyMuPDF, and python-docx installed.

Keep everything running

No action needed. Choose this if you are still actively building or testing your case tracker.

  • Your Flask server continues running and serving requests on port 5000.
  • For persistence across terminal sessions, consider running the server with nohup or setting up a systemd service.
  • All case data, uploaded files, and search indexes remain intact.

Pause - I'll come back to this later

Stop the running server to free up memory, but keep all your project files and data intact for later.

  • Switch to the terminal where your Flask server is running.
  • Press Ctrl+C to stop the server.
  • Your data stays in cases.db and your uploaded files stay in uploads/.
  • To restart later, activate your virtual environment and run:
flask --app app run --host=0.0.0.0

Everything picks up right where you left off.

Delete - I don't want to use this again

Remove all project files, the database, uploaded documents, and the virtual environment.

  • Switch to the terminal where your Flask server is running.
  • Press Ctrl+C to stop the server.
  • Navigate to the parent of your project directory and remove it by running:
cd ..
rm -rf pi-case-tracker
  • Confirm the directory is gone by running:
ls

You should no longer see pi-case-tracker in the output. This removes app.py, all templates, cases.db, the uploads/ directory, and the virtual environment.

Nice Work!

Nice Work!

You just built a complete self-hosted case management system from scratch using Python and Flask. Your PI Case Tracker runs on your Ubuntu server, handles file uploads, extracts text from documents, and searches across everything instantly.

You've learned how to:

  • Build a full-stack Flask web application with SQLite database operations, secure file uploads, and HTML templates served from your Ubuntu server.
  • Extract searchable text from PDFs and Word documents using PyMuPDF and python-docx, then index that content automatically on upload.
  • Implement a full-text search engine using SQLite FTS5 with relevance ranking, Porter stemming, and highlighted snippet previews.
  • Secret Mission: Generated downloadable PDF case reports using PyMuPDF's document creation API.

Ready to quiz yourself?