Build a Local People File Vault

Store and manage files for people in a local SQLite database with Python.

Introduction

30 Second Summary

Think about all the files you deal with for different people. Contracts for a client, photos from a friend, invoices from a vendor. They end up scattered across random folders with no easy way to find what belongs to whom.

In this project, you will build a command-line File Vault that stores people and their associated files in a local SQLite database, with an organized folder structure on your Linux system.

What You'll Build

You will run your File Vault from the terminal, add a person, attach a PDF to them, then search your entire vault and see the result come back instantly.

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

  • A working CLI tool where you add people, attach any file to them, and query everything back from a relational database you designed yourself.
  • A file search that finds files across all people by name or type, so you can locate a document without remembering who it belongs to.
  • An integrity checker that detects when files have gone missing from disk and cleans up the broken database references automatically.
  • Secret Mission: Add a tagging system with a many-to-many relationship so you can categorize and filter files by custom tags.

Are there any prerequisites?

None. This guide walks you through every setup step from scratch. You will use only Python standard library modules, so there is nothing extra to install beyond Python itself.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters. This is your moment to commit to the goal before any hands-on work begins.

Set Up Your Project

Every project starts with a solid foundation. Before you can write database code or manage files, you need a project folder, a verified Python environment, and a test file to work with.

In this step, you will create your project structure, confirm that Python and its built-in SQLite module are ready, and prepare a sample file you will attach to a person later.

In this step, get ready to:
  • Create the project folder and open it in VS Code.
  • Verify Python 3.14.6 and the sqlite3 module are available.
  • Create a test file to use in later steps.
Create the project folder and open it in VS Code

Your File Vault project needs its own folder on your system. You will create it in your home directory and then open it in VS Code so you have an editor and terminal in one place.

  • Press Ctrl+Alt+T to open a terminal window.
  • Create your project folder and move into it by running this command:
mkdir ~/file-vault-project && cd ~/file-vault-project
  • Open the folder in VS Code by running:
code .

VS Code opens with your empty file-vault-project folder loaded in the Explorer sidebar.

VS Code not opening?

If you see a "command not found" message, open VS Code manually, then press Ctrl+Shift+P to open the Command Palette. Type Shell Command: Install 'code' command in PATH and select it. Then close and reopen your terminal and try code . again.

Help me fix the code command in my terminal.

  • In VS Code, open the terminal by pressing Ctrl+` (backtick key, usually above Tab).
  • Create a new empty file called vault.py by running:
touch vault.py
  • Confirm the file exists by running:
ls

You should see vault.py listed in the output.

Verify Python and sqlite3 are working

Your File Vault uses Python to run all its logic and the sqlite3 module (bundled with Python) to interact with the database. You need to confirm both are available before writing any code.

  • Before you run the next command, what version number do you expect to see?
  • Check your Python version by running:
python3 --version

You should see Python 3.14.6 printed in the terminal.

  • Confirm the sqlite3 module is available by running:
python3 -c "import sqlite3; print(sqlite3.sqlite_version)"

You should see a SQLite version number printed (something like 3.46.0 or similar). The exact number does not matter as long as it prints without errors.

What does this command do?

The -c flag lets you run a short Python script directly from the terminal without creating a file. This one imports the sqlite3 module and prints the version of the SQLite library it uses under the hood.

The sqlite3 module comes bundled with Python, so you never need to install it separately. It gives your Python code the ability to create and query SQLite databases.

Seeing an error instead of a version?

If you see command not found, try running python --version (without the 3). Some Linux setups alias the command differently.

Help me figure out why python3 is not working.

Create a test file to use later

In the next steps, you will attach files to people in your vault. You need a sample file on disk to test that feature. This command creates a simple text file in your home directory.

  • Create a test document by running:
echo "This is a test document" > ~/test-document.txt
  • Confirm the file was created by running:
cat ~/test-document.txt

You should see This is a test document printed in the terminal.

Why create this file now?

In Step 3, you will build an attach_file() function that copies files into the vault and records them in the database. Having ~/test-document.txt ready means you can test that feature immediately without hunting for a file to use.

Your project is ready. Next up, you will create the SQLite database and write your first SQL tables to store people and their files.

Create Your Database

Your project folder is ready and Python is confirmed working. Now you need a place to store information about people and their files.

Before you can manage files for different people, you need a database with tables that define the relationship between them. In this step, you will create a SQLite database, insert your first person record, and query it back to prove the system works.

In this step, get ready to:
  • Write the database initialization function that creates your tables.
  • Add a function to insert people records safely.
  • Query data back from the database to confirm everything works.
Write the database initialization code

Every database project starts with two decisions: where to store the data, and what shape it takes. Your vault stores the database file and all attached files under a single ~/file_vault/ directory.

  • In VS Code, open vault.py and paste these imports and configuration constants:
import sqlite3
import os
import shutil
from pathlib import Path

# Configuration
VAULT_DIR = Path.home() / "file_vault" / "files"
DB_PATH = Path.home() / "file_vault" / "vault.db"

What does this code do?

  • sqlite3 is Python's built-in module for working with SQLite databases.
  • os and shutil handle file system operations like checking paths and copying files.
  • Path.home() returns your home directory. The vault lives at ~/file_vault/ with files organized in a files/ subfolder.
  • Save vault.py.

Now you need a function that creates the vault folder on disk and sets up the database tables. The two tables define the core relationship: people own files.

  • Below the constants, add the init_vault() function:
def init_vault():
    """Create vault directory and database tables."""
    VAULT_DIR.mkdir(parents=True, exist_ok=True)

    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.executescript("""
        CREATE TABLE IF NOT EXISTS people (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT,
            notes TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY,
            person_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            filepath TEXT NOT NULL,
            file_type TEXT,
            file_size INTEGER,
            added_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (person_id) REFERENCES people(id)
        );
    """)

    con.commit()
    con.close()
    print("Vault initialized successfully!")

What does this code do?

  • VAULT_DIR.mkdir(parents=True, exist_ok=True) creates the folder and all parent folders. If they already exist, it does nothing.
  • sqlite3.connect() opens a connection to the database file. If the file does not exist yet, SQLite creates it automatically.
  • executescript() runs multiple SQL statements at once. It issues an implicit commit before executing.
  • The people table stores a person's name, email, and notes. The files table links each file to a person via the FOREIGN KEY on person_id.
  • CREATE TABLE IF NOT EXISTS means you can run this function multiple times without errors.
  • Save vault.py.
  • Test that your initialization works by running this command in the VS Code terminal:
python3 -c "from vault import init_vault; init_vault()"

You should see Vault initialized successfully! printed in the terminal.

  • Confirm the vault directory was created by running:
ls ~/file_vault

You should see files and vault.db listed.

Seeing an error?

  • If you see ModuleNotFoundError, make sure your terminal is in the ~/file-vault-project folder where vault.py lives.
  • If you see SyntaxError, check that the triple-quoted SQL string uses three double-quotes and has matching opening and closing """.
  • help me debug my init_vault function
Write an add_person() function and test it

The vault can create tables, but it cannot store anyone yet. You need a function that inserts a person record using parameterized queries to prevent SQL injection.

  • Below the init_vault() function in vault.py, add the add_person() function followed by test calls at the very bottom of the file:
def add_person(name, email="", notes=""):
    """Add a new person to the database."""
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.execute(
        "INSERT INTO people (name, email, notes) VALUES (?, ?, ?)",
        (name, email, notes)
    )

    con.commit()
    person_id = cur.lastrowid
    con.close()
    print(f"Added person: {name} (ID: {person_id})")
    return person_id


init_vault()
add_person("Alice Johnson", "alice@example.com", "Test contact")

What does this code do?

  • The ? placeholders in the SQL tell SQLite to safely insert the values without letting special characters break the query. This is called a parameterized query.
  • cur.lastrowid returns the ID that SQLite automatically assigned to the new row.
  • The two lines at the bottom actually run the functions. init_vault() ensures the database exists, then add_person() inserts Alice as the first record.
  • Save vault.py.
  • Delete the database from the earlier test so you start fresh by running:
rm ~/file_vault/vault.db
  • Run your script:
python3 vault.py

You should see two lines printed:

Vault initialized successfully!
Added person: Alice Johnson (ID: 1)

Seeing ID: 2 instead of ID: 1?

This means Alice was already inserted from a previous run. Delete the database and run again:

rm ~/file_vault/vault.db
python3 vault.py

help me fix my add_person function

Query the data back

Writing data is only half the story. You need to prove the data actually lives in the database by reading it back with a SELECT query.

  • At the bottom of vault.py, below the add_person() call, add this query code:
con = sqlite3.connect(DB_PATH)
cur = con.cursor()
cur.execute("SELECT id, name, email FROM people")
rows = cur.fetchall()
con.close()
print(rows)

What does this code do?

  • SELECT id, name, email FROM people asks the database to return those three columns for every row in the people table.
  • fetchall() retrieves all matching rows as a list of tuples.
  • Save vault.py.
  • Delete the database so the script runs on a clean slate:
rm ~/file_vault/vault.db

Before you run the script, what do you expect the SELECT query to print? Think about the shape of the data.

  • Run the script:
python3 vault.py

You should see all three lines of output:

Vault initialized successfully!
Added person: Alice Johnson (ID: 1)
[(1, 'Alice Johnson', 'alice@example.com')]

Alice's row comes back as a tuple inside a list. The data went into SQLite and came back out.

Seeing an empty list or multiple rows?

  • An empty list [] means the SELECT ran before the INSERT. Check that your add_person() call appears above the SELECT query in the file.
  • Multiple rows means the database had leftover data. Run rm ~/file_vault/vault.db then python3 vault.py again.
  • help me debug my SELECT query in vault.py

✔️ Awesome, I've got everything!

Great. Double check you have saved vault.py.

ⓧ I'd like to double check the full code

Here is the complete vault.py at this point. Scroll through and confirm each section is present in order: imports, constants, init_vault(), add_person(), and the test code at the bottom.

import sqlite3
import os
import shutil
from pathlib import Path

# Configuration
VAULT_DIR = Path.home() / "file_vault" / "files"
DB_PATH = Path.home() / "file_vault" / "vault.db"

The init_vault() function:

def init_vault():
    """Create vault directory and database tables."""
    VAULT_DIR.mkdir(parents=True, exist_ok=True)

    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.executescript("""
        CREATE TABLE IF NOT EXISTS people (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT,
            notes TEXT,
            created_at TEXT DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY,
            person_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            filepath TEXT NOT NULL,
            file_type TEXT,
            file_size INTEGER,
            added_at TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (person_id) REFERENCES people(id)
        );
    """)

    con.commit()
    con.close()
    print("Vault initialized successfully!")

The add_person() function and the test code at the bottom:

def add_person(name, email="", notes=""):
    """Add a new person to the database."""
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.execute(
        "INSERT INTO people (name, email, notes) VALUES (?, ?, ?)",
        (name, email, notes)
    )

    con.commit()
    person_id = cur.lastrowid
    con.close()
    print(f"Added person: {name} (ID: {person_id})")
    return person_id


init_vault()
add_person("Alice Johnson", "alice@example.com", "Test contact")

con = sqlite3.connect(DB_PATH)
cur = con.cursor()
cur.execute("SELECT id, name, email FROM people")
rows = cur.fetchall()
con.close()
print(rows)

Your database is alive. You can create tables, insert records, and read them back with SQL. Next up, you will build the core feature: attaching real files to people and copying them into an organized vault folder.

Attach Files and List Them

Your database has a people table and a files table, but no actual files are being tracked yet. The database holds metadata, but the real value is linking actual files on disk to people records.

In this step, you will build the core feature: copying a file into an organized vault folder and recording its location in the database. You will also build a query that lists all files belonging to a specific person.

In this step, get ready to:
  • Build a function that copies files into the vault and records them in the database.
  • Build a function that lists all files belonging to a person using a JOIN query.
  • Test both by attaching the test file to Alice and listing her files.
Build the attach_file() function

The attach_file() function needs to do several things: check that the source file exists, create a folder for the person inside the vault, copy the file there, and record it in the database.

  • In vault.py, add the first part of the attach_file() function below the add_person() function (above the existing test code at the bottom):
def attach_file(person_id, source_path):
    """Copy a file into the vault and link it to a person."""
    source = Path(source_path)

    if not source.exists():
        print(f"Error: File not found: {source_path}")
        return

    # Create person's folder in vault
    person_dir = VAULT_DIR / str(person_id)
    person_dir.mkdir(parents=True, exist_ok=True)

    # Copy file to vault
    dest = person_dir / source.name

    # Handle duplicate filenames
    if dest.exists():
        stem = source.stem
        suffix = source.suffix
        counter = 1
        while dest.exists():
            dest = person_dir / f"{stem}_{counter}{suffix}"
            counter += 1

    shutil.copy2(str(source), str(dest))

What does this code do?

  • Path(source_path) converts the file path string into a Path object so you can call .exists(), .stem, and .suffix on it.
  • Each person gets their own subfolder inside the vault (e.g. files/1/ for person ID 1). This keeps files organized by owner.
  • The duplicate filename handler appends a counter (e.g. report_1.pdf) if a file with the same name already exists in the vault.
  • shutil.copy2() copies the file and preserves its metadata (modification time, permissions).
  • Now add the second part of attach_file() directly below the shutil.copy2() line, still inside the function (indented one level):
    # Get file info
    file_size = dest.stat().st_size
    file_type = source.suffix.lower().lstrip(".")

    # Record in database
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.execute(
        "INSERT INTO files (person_id, filename, filepath, file_type, file_size) VALUES (?, ?, ?, ?, ?)",
        (person_id, dest.name, str(dest), file_type, file_size)
    )

    con.commit()
    con.close()
    print(f"Attached: {source.name} -> Person ID {person_id}")

What does this code do?

  • dest.stat().st_size gets the file size in bytes from the copied file on disk.
  • source.suffix.lower().lstrip(".") extracts the file extension (like txt or pdf) without the leading dot.
  • The INSERT uses ? placeholders to safely insert the file metadata into the files table, linking it to the person via person_id.
  • Save vault.py.
  • Run your script to confirm there are no syntax errors by running:
python3 vault.py

You should see "Vault initialized successfully!" and "Added person: Alice Johnson (ID: 2)" from the old test code still running at the bottom. The important thing is no errors appear.

Seeing a SyntaxError or IndentationError?

  • Make sure the second part of the function is indented with the same level as the first part (one level inside the def).
  • Check that the entire attach_file() function sits between the add_person() function and the test code at the bottom.
  • Confirm you have matching f-string quotes in the print statement.

Help me fix my attach_file function

Build the list_files() function

Now you need a way to see what files are attached to a person. The list_files() function uses a JOIN query to pull file information alongside the person's name from both tables at once.

  • In vault.py, add the list_files() function below attach_file() (still above the test code at the bottom):
def list_files(person_id):
    """List all files for a specific person."""
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.execute(
        """SELECT p.name, f.filename, f.file_type, f.file_size, f.added_at
           FROM files f
           JOIN people p ON f.person_id = p.id
           WHERE f.person_id = ?
           ORDER BY f.added_at""",
        (person_id,)
    )
    rows = cur.fetchall()
    con.close()

    if not rows:
        print(f"No files found for person ID {person_id}.")
        return

    print(f"\n--- Files for {rows[0][0]} ---")
    for row in rows:
        size_kb = row[3] / 1024
        print(f"  {row[1]} ({row[2]}) - {size_kb:.1f} KB - Added: {row[4]}")
    print()

What does this JOIN query do?

The JOIN connects the files table to the people table using the foreign key relationship (f.person_id = p.id). This lets you pull the person's name alongside their file data in a single query.

The WHERE f.person_id = ? filters to only show files for the person you asked about. The file size is converted from bytes to KB for readability.

Replace the test code and run the script

The old test code at the bottom of your file still adds a person and runs a raw SELECT. You will replace it with calls to your new functions to see the full flow in action.

  • In vault.py, scroll to the bottom and find this test code:
init_vault()
add_person("Alice Johnson", "alice@example.com", "Test contact")

con = sqlite3.connect(DB_PATH)
cur = con.cursor()
cur.execute("SELECT id, name, email FROM people")
rows = cur.fetchall()
con.close()
print(rows)
  • Delete all of that test code and replace it with the following:
init_vault()
attach_file(1, os.path.expanduser("~/test-document.txt"))
list_files(1)

What does this code do?

  • init_vault() ensures the vault directory and tables exist (safe to call multiple times).
  • attach_file(1, ...) attaches the test file to Alice (person ID 1 from Step 2).
  • os.path.expanduser("~/test-document.txt") converts ~ to your full home directory path so Python can find the file.
  • list_files(1) queries and prints all files linked to Alice.
  • Save vault.py.

Before you run this, what do you expect to see? Think about what each of the three function calls will print.

  • Run the script by running:
python3 vault.py

You should see three outputs: "Vault initialized successfully!", then "Attached: test-document.txt -> Person ID 1", then the file listing for Alice showing test-document.txt with its type (txt) and size in KB.

Seeing "Error: File not found"?

  • Make sure ~/test-document.txt still exists. You can recreate it by running echo "This is a test document" > ~/test-document.txt in your terminal.
  • Confirm you used os.path.expanduser("~/test-document.txt") and not just the raw ~ character without expanduser().

Help me debug my attach_file function

  • Confirm the file was copied into the vault by running:
ls ~/file_vault/files/1/

You should see test-document.txt listed. The vault copied the file into a subfolder named after Alice's person ID.

✔️ Awesome, I've got everything!

Great. Double check you have saved vault.py.

ⓧ I'd like to double check the full code

Scroll up and confirm your vault.py contains each of these sections in order:

  • The imports (sqlite3, os, shutil, Path) and configuration constants at the top.
  • The init_vault() function.
  • The add_person() function.
  • The attach_file() function (file check, copy with duplicate handling, database INSERT).
  • The list_files() function (JOIN query, formatted output).
  • The three test calls at the bottom: init_vault(), attach_file(1, ...), list_files(1).

Your vault can now attach files to people and list them back. You have a working relationship between the filesystem and the database. Next up, you will add search and discover what happens when that relationship breaks.

Search Files and Discover the Problem

Your vault can store files and list them for a single person. But with multiple people and dozens of files, you need a way to search across everything at once.

In this step, you will build a search function and an interactive menu to operate your vault from the terminal. Along the way, you will discover a real problem that happens when a database tracks files by path.

In this step, get ready to:
  • Build a search function that queries files across all people.
  • Build an interactive menu so you can operate the vault without editing code.
  • Discover what happens when the database and filesystem fall out of sync.
Build the search function

Right now you can only list files for one specific person. A search function lets you find files across everyone in your vault by filename or file type.

  • In vault.py, add the following function directly below the list_files() function:
def search_files(query):
    """Search files by filename or file type across all people."""
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.execute(
        """SELECT p.name, f.filename, f.file_type, f.file_size
           FROM files f
           JOIN people p ON f.person_id = p.id
           WHERE f.filename LIKE ? OR f.file_type LIKE ?
           ORDER BY p.name""",
        (f"%{query}%", f"%{query}%")
    )
    rows = cur.fetchall()
    con.close()

    if not rows:
        print(f"No files matching '{query}'.")
        return

    print(f"\n--- Search Results for '{query}' ---")
    for row in rows:
        size_kb = row[3] / 1024
        print(f"  {row[0]}: {row[1]} ({row[2]}) - {size_kb:.1f} KB")
    print()

What does this code do?

This function uses a JOIN to pull data from both the files and people tables in a single query.

  • LIKE ? OR f.file_type LIKE ? searches both the filename and file extension columns using wildcards.
  • The f"%{query}%" wraps the search term with % wildcards so it matches anywhere in the string.
  • Results are ordered by person name so they appear grouped together.

Now test the search function before building the full menu. Find the last line at the bottom of your file:

list_files(1)
  • Replace list_files(1) with search_files("txt") so the line now looks like this:
search_files("txt")
  • Save vault.py and run the script by entering this command in your terminal:
python3 vault.py

You should see search results showing test-document.txt matched under Alice Johnson's name, along with its file type and size.

Not seeing search results?

  • Make sure you saved vault.py after adding the function and changing the bottom line.
  • Check that search_files is defined at the same indentation level as list_files (no extra indentation).
  • If you see "No files matching 'txt'", make sure you ran Step 3 successfully (Alice should have a file attached in the database).

Why isn't my search_files function finding any results?

Build the interactive menu

Editing the bottom of the script every time you want to do something is tedious. An interactive menu lets you operate the vault by choosing numbered options in the terminal.

First, remove the three test lines at the bottom of vault.py. Find these lines and delete them:

init_vault()
attach_file(1, os.path.expanduser("~/test-document.txt"))
search_files("txt")
  • Delete all three lines. The file should now end with the search_files() function definition and nothing after it.

Next, you need a helper function that shows everyone in the database. This is used by the menu before attaching or listing files.

  • Add the following function between add_person() and attach_file() in vault.py:
def list_people():
    """List all people in the database."""
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()

    cur.execute("SELECT id, name, email FROM people ORDER BY name")
    rows = cur.fetchall()
    con.close()

    if not rows:
        print("No people in the vault yet.")
        return

    print("\n--- People in Vault ---")
    for row in rows:
        print(f"  ID: {row[0]} | Name: {row[1]} | Email: {row[2]}")
    print()

What does this code do?

  • SELECT id, name, email FROM people ORDER BY name retrieves every person sorted alphabetically.
  • The function prints a formatted table of IDs, names, and emails so you know which person ID to use when attaching files.

Now add the main() function at the very bottom of vault.py (after the search_files() function). This is the first part of the menu:

def main():
    """Main menu loop."""
    init_vault()

    while True:
        print("\n=== File Vault ===")
        print("1. Add a person")
        print("2. List people")
        print("3. Attach a file")
        print("4. List files for a person")
        print("5. Search files")
        print("6. Check integrity")
        print("7. Quit")

        choice = input("\nChoice: ").strip()

        if choice == "1":
            name = input("Name: ").strip()
            email = input("Email (optional): ").strip()
            notes = input("Notes (optional): ").strip()
            add_person(name, email, notes)

        elif choice == "2":
            list_people()

        elif choice == "3":
            list_people()
            person_id = input("Person ID: ").strip()
            source_path = input("File path: ").strip()
            attach_file(int(person_id), source_path)

What does the menu do so far?

  • init_vault() runs once at startup to ensure the database and vault folder exist.
  • The while True loop keeps the program running until the user chooses to quit.
  • Each input() call pauses the program and waits for the user to type something.
  • Directly below that code (still inside the main() function), add the remaining menu options and the entry point:
        elif choice == "4":
            list_people()
            person_id = input("Person ID: ").strip()
            list_files(int(person_id))

        elif choice == "5":
            query = input("Search (filename or type): ").strip()
            search_files(query)

        elif choice == "6":
            check_integrity()

        elif choice == "7":
            print("Goodbye!")
            break

        else:
            print("Invalid choice. Try again.")


if __name__ == "__main__":
    main()

What does this code do?

  • Options 4 and 5 call the list_files() and search_files() functions you already built.
  • Option 6 calls check_integrity() which you will build in the next step. Do not choose option 6 yet.
  • The if __name__ == "__main__": block ensures main() only runs when you execute the script directly.
  • Save vault.py and run the program:
python3 vault.py

You should see the "=== File Vault ===" menu with options 1 through 7. Try option 2 to list people. You should see Alice Johnson with ID 1.

  • Type 7 and press Enter to quit the program.

Seeing a NameError or IndentationError?

  • If you see IndentationError, make sure the second code block (choices 4-7) is indented to match the first block. The elif lines should have exactly 8 spaces of indentation.
  • If you see NameError: name 'list_people' is not defined, check that your list_people() function is placed above main() in the file.
  • Make sure you deleted all three old test lines at the bottom. Leftover lines outside a function can cause unexpected behavior.

Help me fix an error when running my vault.py menu

Expose the dangling reference problem

Your vault works. But it has a hidden flaw. The database stores file paths as plain text. If a file disappears from disk, the database has no way to know.

  • Run your vault program again:
python3 vault.py
  • Type 5 to search files.
  • Type txt as your search query.

You should see test-document.txt in the results under Alice Johnson. The search works. Now leave the program running.

  • Open a second terminal in VS Code by clicking the + icon in the terminal panel.
  • In the new terminal, delete the vault's copy of the test file by running:
rm ~/file_vault/files/1/test-document.txt

The file is gone from disk. Before you switch back to the vault program, think about this: what do you expect option 4 (List files for a person) to show now?

  • Switch back to the terminal running your vault program.
  • Type 4 to list files for a person.
  • Type 1 for Alice's person ID.

The database still shows test-document.txt as if nothing happened. The file is gone from your filesystem, but the database record still points to it.

Why is the database lying?

This is called a dangling reference. The database stores the file path as a plain text string. It has no way to verify whether that path still points to a real file on disk.

This is a fundamental tradeoff of storing files externally (on the filesystem) rather than inside the database. You get better performance for large files, but you lose the guarantee that referenced data actually exists. This is a real problem in any system that tracks files by path.

  • Type 7 to quit the program.

✔️ Awesome, I've got everything!

Great. Double check you have saved vault.py with all your changes.

ⓧ I'd like to double check the full code

Your vault.py is now too long to show in one block. Scroll up through your file and confirm these sections are present, in this order:

  • Imports (sqlite3, os, shutil, Path) and configuration constants at the top.
  • init_vault() function.
  • add_person() function.
  • list_people() function (new in this step).
  • attach_file() function.
  • list_files() function.
  • search_files() function (new in this step).
  • main() function with the full menu loop (new in this step).
  • if __name__ == "__main__": main() at the very bottom.

Make sure there are no leftover test lines (like init_vault() or attach_file(...) calls) floating outside of a function.

You have uncovered a real flaw in your vault. The database happily reports files that no longer exist on disk. In the next step, you will build an integrity checker that detects these ghost entries and cleans them up.

Build the Integrity Checker

In the last step, you exposed a real problem. The database happily reported a file that no longer exists on disk.

When files are stored externally and referenced by path, the database can drift out of sync with reality. You need a health check that scans every file reference, reports what is missing, and offers to clean up the broken entries.

In this step, get ready to:
  • Build a function that scans the database and checks each file path against the filesystem.
  • Add a cleanup prompt that deletes broken references from the database.
  • Run the integrity checker and confirm the ghost entry from Step 4 is gone.
Build the integrity scan

The integrity checker needs to pull every file record from the database and test whether the stored path still points to a real file on disk. If the file is gone, the record is a dangling reference.

  • In vault.py, find the main() function.
  • Add the following check_integrity() function directly above main():
def check_integrity():
    """Check that all file references in the database still exist on disk."""
    con = sqlite3.connect(DB_PATH)
    cur = con.cursor()
    # Query all file records joined with person names
    cur.execute(
        """SELECT f.id, p.name, f.filename, f.filepath
           FROM files f
           JOIN people p ON f.person_id = p.id"""
    )
    rows = cur.fetchall()
    con.close()

    if not rows:
        print("No files in vault to check.")
        return

    # Check each file path against the actual filesystem
    missing = []
    valid = 0
    for row in rows:
        if os.path.exists(row[3]):
            valid += 1
        else:
            missing.append(row)

    print("\n--- Integrity Report ---")
    print(f"  Total files: {len(rows)}")
    print(f"  Valid: {valid}")
    print(f"  Missing: {len(missing)}")

What does this code do?

The SELECT query uses a JOIN to pull each file record alongside the person's name. This gives you readable output showing who owns the broken reference.

The loop calls os.path.exists() on every stored filepath. If the file is still on disk, it counts as valid. If not, it gets added to the missing list for reporting.

  • Save vault.py.
  • Run the program and choose option 6 by running:
python3 vault.py

Type 6 when the menu appears. You should see an integrity report showing Total files: 1, Valid: 0, and Missing: 1.

Seeing a NameError?

If you see NameError: name 'check_integrity' is not defined, make sure your new function is defined above main() in the file. Python reads top to bottom, so the function must exist before main() tries to call it.

help me debug a NameError in my vault.py integrity checker

Add the cleanup prompt

The scan tells you something is wrong, but it does not fix the problem. You need a prompt that lists the broken references and offers to delete them from the database.

  • In check_integrity(), find this line:
    print(f"  Missing: {len(missing)}")
  • Add the following code below that line:
    # Print broken references and offer cleanup
    if missing:
        print("\n  Broken references:")
        for row in missing:
            print(f"    [{row[0]}] {row[1]}: {row[2]} -> {row[3]}")

        answer = input("\n  Remove broken references from database? (yes/no): ")
        if answer.lower() == "yes":
            con = sqlite3.connect(DB_PATH)
            cur = con.cursor()
            for row in missing:
                cur.execute("DELETE FROM files WHERE id = ?", (row[0],))
            con.commit()
            con.close()
            print(f"  Removed {len(missing)} broken references.")
    print()

What does this code do?

When missing files are found, the code prints each broken reference with its database ID, the person's name, and the filepath that no longer exists. This gives the user full context before deciding.

If the user types yes, it loops through the broken records and runs a DELETE FROM files WHERE id = ? for each one. The ? placeholder keeps the query safe from injection, just like your INSERT statements.

  • Save vault.py.
Test the full integrity flow

Your database still has a dangling reference from Step 4. The integrity checker should find it, show you the details, and let you clean it up.

Before you run this, what do you think the integrity report will show? How many files will be missing?

  • Run the program:
python3 vault.py
  • Type 6 to choose Check integrity.
  • You should see the report: Total files: 1, Valid: 0, Missing: 1.
  • Below the report, you should see the broken reference listing test-document.txt and the path that no longer exists.
  • Type yes when prompted to remove broken references.

You should see a confirmation message that 1 broken reference was removed.

  • Now type 4 to list files for Alice (Person ID 1).

You should see No files found for person ID 1. printed. The ghost entry is gone.

  • Type 6 one more time to re-run the integrity check.

The report now shows Missing: 0. Your database and filesystem are back in sync.

Still seeing the broken reference after cleanup?

Make sure you typed yes (lowercase) at the cleanup prompt. Any other input skips the deletion.

If the file is still listed after typing yes, check that your DELETE statement uses row[0] (the file ID). Using the wrong index deletes nothing.

help me debug my integrity checker cleanup

✔️ Awesome, I've got everything!

Double check you have saved vault.py and the integrity check shows Missing: 0 on a clean run.

ⓧ I'd like to double check the full code

Your vault.py is now quite long. Scroll through and confirm these sections are present in order:

  • Imports: sqlite3, os, shutil, Path from pathlib.
  • Configuration: VAULT_DIR and DB_PATH constants.
  • Functions in this order: init_vault(), add_person(), list_people(), attach_file(), list_files(), search_files(), check_integrity(), main().
  • The if __name__ == "__main__": main() block at the very end.

Secret mission

Add a Tagging System

Your vault can list files by person, but what if you want to find all contracts across everyone? Or all photos? A tagging system lets you categorize files with custom labels and search by category. This requires a many-to-many database relationship, a more advanced SQL pattern used in production applications everywhere.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely locally, so there are no ongoing costs. Decide whether to keep your files to continue experimenting, or delete them for a fresh start.

Resources you used:

  • Project folder: ~/file-vault-project/ (contains vault.py).
  • Vault data folder: ~/file_vault/ (contains vault.db and all copied files).
  • Test file: ~/test-document.txt.

Keep everything running

No action needed. Choose this if you want to keep experimenting with your file vault, adding more people, attaching files, or extending the code.

  • Your project code stays in ~/file-vault-project/.
  • Your vault database and files stay in ~/file_vault/.
  • Run python3 ~/file-vault-project/vault.py any time to use your vault again.

Pause - I'll come back to this later

Since this is a local project with no running processes, there is nothing to pause. Your files sit on disk until you decide to delete them.

  • Everything is already paused by default. No server or process runs in the background.
  • When you want to return, open ~/file-vault-project/ in VS Code and run the script again.

Delete - I don't want to use this again

Remove all project files, vault data, and the test file to start completely fresh.

  • Delete the project folder by running:
rm -rf ~/file-vault-project
  • Delete the vault data folder (database and all copied files) by running:
rm -rf ~/file_vault
  • Delete the test file by running:
rm ~/test-document.txt
  • Confirm everything is removed by running:
ls ~/file-vault-project ~/file_vault ~/test-document.txt

You should see "No such file or directory" for each path, confirming the cleanup is complete.

Nice Work!

Nice Work!

You just built a fully functional local file vault from scratch using Python and SQLite. You designed a relational schema, wrote SQL queries, managed real files on disk, and solved a data integrity problem that trips up professional developers.

Here's what you learned:

  • Built a working SQLite database with a relational schema linking people to files via foreign keys.
  • Wrote Python functions that create, read, and delete database records using parameterized queries to prevent SQL injection.
  • Built a file management system that copies files into an organized vault and tracks them by metadata.
  • Discovered and solved the dangling reference problem with a file integrity checker that detects and cleans broken entries.
  • Secret Mission: Implemented a many-to-many tagging system using a junction table and multi-table JOINs.

Ready to quiz yourself?