Build an AI-Agnostic Second Brain

Build a Python CLI to store and search AI-tagged notes with SQLite.

Introduction

30 Second Summary

Useful ideas often end up scattered across text files or chat windows. Finding one sentence later can take longer than writing it.

In this project, you will build a local second brain with Python backed by SQLite. The command-line interface keeps original notes separate from AI-generated metadata.

What You'll Build

Picture searching for a phrase buried in an old note to reveal its untouched source beside a concise AI summary.

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

  • A local note record that displays untouched source text beside separate AI-generated metadata.
  • A full-text search that highlights phrases buried inside saved notes.
  • A safe deletion workflow backed by a complete database snapshot.
  • Secret Mission: Prove provider independence by retrieving a portability note prepared through a second AI provider.

Are there any prerequisites?

No prior coding or database experience is required. The guide sets up every required tool from scratch on your Windows PC.

Before We Start

Before the hands-on work begins, define what belongs in your local second brain. This commitment keeps your original notes trustworthy when AI-generated titles or summaries change.

Set Up Your Windows Workspace

A dependable workspace keeps environment problems from disguising the database concepts you want to learn.

In this step, Python 3.14.7 will prepare your Windows PC to run the future command-line interface.

Visual Studio Code will hold the empty second-brain-sql workspace. Claude will prove that your AI prompt path is ready.

In this step, get ready to:
  • Confirm the Python 3.14.7 runtime in Windows PowerShell.
  • Prepare the second-brain-sql workspace in Visual Studio Code.
  • Verify that Claude can return a precise JSON response.
Install the Python runtime

The command-line interface in later steps runs through Python. Pinning the runtime now gives every later command the same starting point.

  • Press the Windows key to open Windows search.
  • Type PowerShell into the search field.
  • Select Windows PowerShell from the search results.
  • Check the installed Python runtimes by running this command:
py list

What Does py list Show?

The py list command asks the Python Install Manager to display every installed runtime. The result tells you whether Python 3.14.7 is ready.

✔️ I see the required version

Good work. Python 3.14.7 is installed and available through the Python Install Manager.

ⓧ I see an older version

The Python Install Manager is available. Your PC still needs the current 3.14 runtime.

  • Install the Python 3.14 runtime by running this command:
py install 3.14

What Does This Command Do?

The Python Install Manager downloads the current Python 3.14 release. The installed patch release should appear as 3.14.7.

  • Confirm the newly installed runtime by running this command:
py list

What Confirms Success?

The runtime list now includes Python 3.14.7. That entry confirms the installation succeeded.

ⓧ PowerShell cannot find py

Windows cannot use the py command until the Python Install Manager is installed.

  • Visit the official Python downloads page in your browser.
  • Install the Python Install Manager by following the page's Windows instructions.
  • Close the current PowerShell window after the installation finishes.
  • Press the Windows key to open Windows search.
  • Type PowerShell into the search field.
  • Select Windows PowerShell from the search results.
  • Install the Python 3.14 runtime by running this command:
py install 3.14

What Does This Command Do?

The Python Install Manager downloads the current Python 3.14 runtime. The manager keeps the installation available through the py command.

  • Confirm the installed runtime by running this command:
py list

What Confirms Success?

The runtime list includes Python 3.14.7 when the installation is complete.

Why Use the Python Install Manager?

The Python Install Manager controls which runtime the py command launches. This makes the required Python release explicit before you build the project.

PowerShell Still Cannot Find Python?

  • Confirm that the Python Install Manager finished installing before you reopened PowerShell.
  • Close every older PowerShell window because each one keeps its previous command path.

Still stuck? Help me diagnose why the py command is unavailable after installing Python Install Manager on Windows. Then share the error details in the NextWork community.

Prepare the Visual Studio Code workspace

The editor gives your project a visible home. Opening a named folder also fixes the location for every command you run later.

  • Visit the official Visual Studio Code Windows setup page in your browser.
  • Download the recommended Windows User setup.
  • Run the downloaded installer.
  • Complete the installation wizard with its default choices.
  • Close the PowerShell window from earlier after the installation finishes.
  • Press the Windows key to open Windows search.
  • Type PowerShell into the search field.
  • Select Windows PowerShell from the search results.

Why Restart PowerShell?

The Visual Studio Code installer adds the code command to the Windows command path. A fresh PowerShell window reads that updated path.

  • Create the second-brain-sql folder on your Desktop by running these commands:
Set-Location -Path ([Environment]::GetFolderPath('Desktop'))
New-Item -Path . -Name "second-brain-sql" -ItemType "Directory"

What Do These Commands Do?

  • The first command finds your actual Desktop location. This also works when Windows stores the Desktop through OneDrive.
  • The second command creates a directory named second-brain-sql in that location.

PowerShell lists a directory named second-brain-sql. That output confirms the folder exists.

  • Move PowerShell into the new folder by running this command:
Set-Location -Path .\second-brain-sql -PassThru

What Does This Command Do?

The command changes PowerShell's current location to second-brain-sql. The PassThru parameter prints the resulting path for confirmation.

The displayed path ends with Desktop\second-brain-sql. PowerShell now runs future project commands from the correct folder.

Before you run the next command, what folder name do you expect Visual Studio Code to show?

  • Open the current folder in Visual Studio Code by running this command:
code .

What Does This Command Do?

The code . command opens the current PowerShell location in Visual Studio Code. The dot represents the second-brain-sql folder you just entered.

  • Approve the folder if Visual Studio Code asks whether you trust it.

You should see second-brain-sql at the top of the Explorer sidebar. The empty Explorer confirms that no database or project files have been created.

✔️ Awesome, I've got everything!

Great. The second-brain-sql folder exists on your Desktop and is open in Visual Studio Code.

ⓧ I'd like to double check the full code

There is no project code to compare yet. Confirm only the workspace state below.

  • The second-brain-sql folder appears at the top of the Visual Studio Code Explorer sidebar.
  • The Explorer contains no files or subfolders.
  • The PowerShell location ends with Desktop\second-brain-sql.

Visual Studio Code Did Not Open?

  • Confirm that you restarted PowerShell after installing Visual Studio Code.
  • Confirm that the PowerShell path ends with second-brain-sql before you retry the command.

Still stuck? Help me fix the code command after installing Visual Studio Code on Windows. Then share the PowerShell output in the NextWork community.

Confirm the Claude prompt path

Claude provides the metadata test for this walkthrough. Your local project stays free of provider-specific API code.

  • Visit the Claude web app in your browser.
  • Use the account flow to access a Free chat.
  • Open a blank chat.
  • Paste the following prompt into the chat input:
Return exactly this JSON object and nothing else:

{"status":"ready"}

What Does This Prompt Check?

The prompt checks whether the chat can follow a strict JSON output contract. Later prompts use the same idea to produce metadata that Python can read.

  • Send the prompt to Claude.

Claude should return exactly {"status":"ready"}. This proves the web chat can provide machine-readable metadata without an API key.

Claude Added Extra Text?

  • Paste the prompt again in a new chat if the response contains earlier conversation context.
  • Confirm that the requested JSON object still uses double quotation marks around both strings.

Still stuck? Help me ask Claude to return one exact JSON object without commentary or Markdown fences. Then share a redacted screenshot in the NextWork community.

Your three tools now have one final check. The result should connect the Python runtime to the open workspace.

  • Switch back to the PowerShell window from earlier.

Before you run the checks, do you expect PowerShell to find both Python and Visual Studio Code?

  • Confirm the Python runtime by running this command:
py list

What Does This Final Python Check Prove?

The command proves that the fresh PowerShell session can reach the Python Install Manager. Its output should list Python 3.14.7.

You should see Python 3.14.7 in the runtime list.

  • Confirm the current folder through Visual Studio Code by running this command:
code .

What Does This Final Editor Check Prove?

The command proves that the code command is available in the fresh PowerShell session. It also confirms that the current location is your project folder.

Visual Studio Code should show the empty second-brain-sql folder in the Explorer.

Claude should still show the exact {"status":"ready"} response in your browser. Together, these three results confirm that the Windows workspace is ready.

Your Windows workspace is ready. Next, you'll turn the empty folder into a command-line application that stores its first note in SQLite.

Store and Inspect Your First Note

Your Windows workspace is ready. You can now turn the empty project folder into a working local knowledge store.

A trustworthy second brain needs to preserve each original note before adding AI-generated interpretation. In this step, your Python program stores both forms in a local SQLite database.

In this step, get ready to:
  • Create a Python CLI that initializes the notes database.
  • Separate the raw note from its JSON metadata.
  • Verify the stored note through the CLI.
Create the core database program

The first working slice needs a table plus commands for creating it. The table keeps the original text in raw_text while the AI interpretation lives in ai_summary.

The database file acts as the durable boundary of the project. Later features can improve retrieval without rewriting the source material.

  • Use the new-file control in the VS Code file sidebar.
  • Name the file brain.py.
  • Add the imports plus the core schema by pasting this first chunk:
import argparse
import json
import sqlite3
from pathlib import Path

DB_PATH = Path("second-brain.db")

CORE_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    raw_text TEXT NOT NULL,
    ai_summary TEXT NOT NULL,
    source TEXT NOT NULL,
    source_file TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""

What does this foundation define?

  • The imports provide command parsing, JSON reading, database access, and local path handling.
  • The DB_PATH constant gives every command one database location.
  • The schema stores the untouched note separately from its generated summary.
  • Save brain.py in VS Code.
  • Confirm that brain.py is listed inside the second-brain-sql folder.

Seeing red underlines in the schema?

Check that both triple-quote lines surround the complete SQL statement. Confirm that the closing parenthesis appears before the final triple quotes.

Still stuck? Help me compare my Python schema with the project version.

  • Add the database connection plus initialization helpers below the schema:
def connect_db():
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection


def require_database():
    if not DB_PATH.exists():
        raise SystemExit("Database not found. Run `python brain.py init` first.")


def initialize_database():
    connection = connect_db()
    try:
        connection.executescript(CORE_SCHEMA_SQL)
        connection.commit()
    finally:
        connection.close()
    print(f"Database ready: {DB_PATH}")
    print(f"SQLite runtime: {sqlite3.sqlite_version}")

Why use connection helpers?

  • The connection helper applies named column access to every database operation.
  • The database check prevents later commands from creating an unintended empty file.
  • The initialization function executes the schema as one repeatable operation.
  • Save brain.py.
  • Confirm that the editor shows connect_db() below the schema.
  • Confirm that initialize_database() ends with two print statements.

Seeing indentation errors in the helpers?

Keep each function at the left edge of the file. Indent the statements inside each function with four spaces.

Need another pair of eyes? Help me fix the indentation in these database functions.

  • Add the command parser below initialize_database() by pasting this chunk:
def build_parser():
    parser = argparse.ArgumentParser(
        description="Manage an AI-agnostic second brain in SQLite.",
        suggest_on_error=True,
    )
    subparsers = parser.add_subparsers(dest="command", required=True)

    subparsers.add_parser("init", help="Create the core notes table.")
    subparsers.add_parser("list", help="List stored notes.")

    ingest_parser = subparsers.add_parser("ingest", help="Ingest raw text and JSON metadata.")
    ingest_parser.add_argument("raw_file", type=Path)
    ingest_parser.add_argument("metadata_file", type=Path)

    show_parser = subparsers.add_parser("show", help="Show one stored note.")
    show_parser.add_argument("note_id", type=int)

    return parser

What does the parser control?

  • The parser defines the four commands available in this first working slice.
  • The ingest command expects paths to one raw file plus one metadata file.
  • The show command expects a numeric note ID.
  • Save brain.py.
  • Confirm that the parser contains init, ingest, list, and show.

Does the parser show an error?

Check that each parser definition stays inside build_parser(). Make sure return parser has the same indentation as the other statements in that function.

Still blocked? Help me debug my command parser.

  • Add the command dispatcher below build_parser() by pasting this final program chunk:
def main():
    args = build_parser().parse_args()

    if args.command == "init":
        initialize_database()
    elif args.command == "ingest":
        ingest_note(args.raw_file, args.metadata_file)
    elif args.command == "list":
        list_notes()
    elif args.command == "show":
        show_note(args.note_id)


if __name__ == "__main__":
    main()

How does the command dispatcher work?

  • The parser records the command selected in PowerShell.
  • The conditional branches call the matching Python function.
  • The final guard starts the CLI when you run brain.py directly.
  • Save brain.py.

Before you initialize the database, what new file do you expect the program to create?

  • Create the core database by running this command in the existing PowerShell terminal:
python brain.py init

What does this command do?

The command runs initialize_database(). That function creates the database file plus the notes table.

You'll see Database ready: second-brain.db followed by a line identifying the SQLite runtime. The new second-brain.db file confirms that the schema now exists.

That first database file is a real milestone. Your note store now has a durable home.

Did initialization fail?

  • Confirm that PowerShell is still inside the second-brain-sql folder.
  • Check the terminal output for the first referenced line in brain.py.

Need help with the output? Help me debug the database initialization command.

Add separate source and metadata files

The source file holds the words you want to preserve. The separate JSON file holds the title, summary, and source label that an AI can prepare.

  • Use the new-file control in the VS Code file sidebar.
  • Name the file sample-note.txt.
  • Add the immutable source note by pasting this text:
SQLite is an embedded SQL database that keeps application data in a local file.
Transactions provide ACID behavior for related changes, which makes inserts and deletions predictable.
A full-text index can make words inside long notes discoverable without turning the source files into a manually maintained wiki.

Why keep this text intact?

This file is the evidence behind every generated title or summary. Keeping it intact lets you inspect the original wording whenever an interpretation seems incomplete.

  • Save sample-note.txt.
  • Confirm that the file contains three lines of source text.

Does the source file look incomplete?

Check that the first line begins with SQLite. Confirm that the final line ends with the word wiki.

Need help comparing the file? Help me check my immutable sample note.

  • Use the new-file control in the VS Code file sidebar.
  • Name the file sample-metadata.json.
  • Add the interpreted metadata by pasting this object:
{
  "title": "Local Knowledge Base Design",
  "summary": "SQLite provides structured local storage, predictable cleanup, and an optional full-text index for note retrieval.",
  "source": "sample"
}

What belongs in the metadata file?

  • The title gives the note a short name for lists.
  • The summary captures an interpretation grounded in the source.
  • The source label records where the note came from.
  • Save sample-metadata.json.
  • Confirm that the file contains exactly the title, summary, and source keys.

Is the metadata marked as invalid?

Check that every key plus every value uses double quotes. Confirm that the final property has no trailing comma.

Still seeing a problem? Help me repair this metadata object.

Run the first ingestion cycle

Ingestion reads the raw text plus metadata through separate paths. It validates the interpretation before writing one combined database record.

  • In brain.py, find def build_parser():.
  • Add the validation portion of ingest_note() immediately above that line:
def ingest_note(raw_path, metadata_path):
    require_database()

    raw_text = raw_path.read_text(encoding="utf-8")
    try:
        metadata = json.loads(metadata_path.read_text(encoding="utf-8"))
    except ValueError as error:
        raise SystemExit(f"Metadata is not valid JSON: {error}") from error

    if not isinstance(metadata, dict):
        raise SystemExit("Metadata must be one JSON object.")

    required_fields = ("title", "summary", "source")
    missing_fields = [field for field in required_fields if not metadata.get(field)]
    if missing_fields:
        raise SystemExit(
            "Metadata is missing required fields: " + ", ".join(missing_fields)
        )

How does ingestion protect the record?

  • The function stops if the database has not been initialized.
  • It reads the source file without modifying its text.
  • It rejects malformed metadata plus missing required fields.
  • Save brain.py.
  • Confirm that required_fields contains three field names.

Is the validation block misaligned?

Keep the entire block inside ingest_note(). The two lines inside the final raise SystemExit call need one additional indentation level.

Need help with the nesting? Help me fix this ingestion validation block.

  • Add the database-writing portion directly below the missing-fields check:
    connection = connect_db()
    try:
        cursor = connection.execute(
            """
            INSERT INTO notes(title, raw_text, ai_summary, source, source_file)
            VALUES (?, ?, ?, ?, ?)
            """,
            (
                str(metadata["title"]).strip(),
                raw_text,
                str(metadata["summary"]).strip(),
                str(metadata["source"]).strip(),
                str(raw_path),
            ),
        )
        connection.commit()
        note_id = cursor.lastrowid
    finally:
        connection.close()

    print(f"Ingested note {note_id}: {metadata['title']}")

What does the database write preserve?

  • The insert maps the original text to raw_text.
  • The metadata summary maps to ai_summary.
  • The committed row receives a database-generated note ID.
  • Save brain.py.
  • Ingest both files by running this command:
python brain.py ingest sample-note.txt sample-metadata.json

What does the ingest command combine?

The command supplies one source path plus one metadata path to ingest_note(). The function writes them into separate columns within the same row.

You'll see Ingested note 1: Local Knowledge Base Design. That message confirms the first note now has ID 1.

Did ingestion report a file or metadata error?

  • Confirm that both sample files are listed beside brain.py.
  • Check that the metadata file contains valid JSON with all three required keys.

Still stuck? Help me debug the first ingestion command.

  • In brain.py, find def build_parser():.
  • Add list_notes() immediately above that line:
def list_notes():
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            "SELECT id, title, source, created_at FROM notes ORDER BY id"
        ).fetchall()
    finally:
        connection.close()

    if not rows:
        print("No notes found.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']} | {row['created_at']}")

Why keep the list concise?

The list command returns enough information to identify each record. The full source stays available through the separate show command.

  • Save brain.py.

Before you list the notes, how many records do you expect the database to return?

  • List the stored records by running this command:
python brain.py list

What does the list command prove?

The command queries the durable database record. It does not reread the two input files.

You'll see one row beginning with 1: Local Knowledge Base Design | sample |. The final value is the stored creation time.

Does the list command show no notes?

Return to the ingest output. Confirm that it reported note ID 1 before you ran the list command.

Need help tracing the missing row? Help me diagnose an empty note list.

  • In brain.py, find def build_parser():.
  • Add the lookup portion of show_note() immediately above that line:
def show_note(note_id):
    require_database()
    connection = connect_db()
    try:
        row = connection.execute(
            """
            SELECT id, title, raw_text, ai_summary, source, source_file, created_at
            FROM notes
            WHERE id = ?
            """,
            (note_id,),
        ).fetchone()
    finally:
        connection.close()

    if row is None:
        print(f"Note {note_id} was not found.")
        return

How does note lookup stay precise?

The function uses the requested note ID as a bound query value. It returns one row or a clear missing-note message.

  • Add the output portion directly below the missing-note check:
    print(f"ID: {row['id']}")
    print(f"Title: {row['title']}")
    print(f"Source: {row['source']}")
    print(f"Source file: {row['source_file']}")
    print(f"Created: {row['created_at']}")
    print("\nAI summary:")
    print(row["ai_summary"])
    print("\nOriginal text:")
    print(row["raw_text"])

Why show both versions?

The output labels the generated summary separately from the original text. You can compare interpretation with evidence whenever you inspect a note.

  • Remove the trailing space after the final parenthesis if VS Code inserted one.
  • Save brain.py.

Before you inspect note 1, which two text sections do you expect to see?

  • Inspect the first note by running this command:
python brain.py show 1

What does the inspection prove?

The command retrieves the saved database row by ID. Its output separates the AI summary from the original text with clear labels.

You'll see the title plus source details first. You'll then see AI summary: followed by Original text: and the three-line source note.

You now have a working ingestion loop. Your first note remains inspectable in both its original form and its interpreted form.

Is note 1 missing or incomplete?

  • Run the list command again to confirm that note ID 1 exists.
  • Check that the print statements remain indented inside show_note().

Still stuck? Help me debug the show command output.

✔️ Awesome, I've got everything!

Great. Confirm that all three project files are saved. Keep second-brain.db in the same folder.

ⓧ I'd like to double check the full code

Compare your complete brain.py file with this cumulative version.

import argparse
import json
import sqlite3
from pathlib import Path

DB_PATH = Path("second-brain.db")

CORE_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    raw_text TEXT NOT NULL,
    ai_summary TEXT NOT NULL,
    source TEXT NOT NULL,
    source_file TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""


def connect_db():
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection


def require_database():
    if not DB_PATH.exists():
        raise SystemExit("Database not found. Run `python brain.py init` first.")


def initialize_database():
    connection = connect_db()
    try:
        connection.executescript(CORE_SCHEMA_SQL)
        connection.commit()
    finally:
        connection.close()
    print(f"Database ready: {DB_PATH}")
    print(f"SQLite runtime: {sqlite3.sqlite_version}")


def ingest_note(raw_path, metadata_path):
    require_database()

    raw_text = raw_path.read_text(encoding="utf-8")
    try:
        metadata = json.loads(metadata_path.read_text(encoding="utf-8"))
    except ValueError as error:
        raise SystemExit(f"Metadata is not valid JSON: {error}") from error

    if not isinstance(metadata, dict):
        raise SystemExit("Metadata must be one JSON object.")

    required_fields = ("title", "summary", "source")
    missing_fields = [field for field in required_fields if not metadata.get(field)]
    if missing_fields:
        raise SystemExit(
            "Metadata is missing required fields: " + ", ".join(missing_fields)
        )

    connection = connect_db()
    try:
        cursor = connection.execute(
            """
            INSERT INTO notes(title, raw_text, ai_summary, source, source_file)
            VALUES (?, ?, ?, ?, ?)
            """,
            (
                str(metadata["title"]).strip(),
                raw_text,
                str(metadata["summary"]).strip(),
                str(metadata["source"]).strip(),
                str(raw_path),
            ),
        )
        connection.commit()
        note_id = cursor.lastrowid
    finally:
        connection.close()

    print(f"Ingested note {note_id}: {metadata['title']}")


def list_notes():
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            "SELECT id, title, source, created_at FROM notes ORDER BY id"
        ).fetchall()
    finally:
        connection.close()

    if not rows:
        print("No notes found.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']} | {row['created_at']}")


def show_note(note_id):
    require_database()
    connection = connect_db()
    try:
        row = connection.execute(
            """
            SELECT id, title, raw_text, ai_summary, source, source_file, created_at
            FROM notes
            WHERE id = ?
            """,
            (note_id,),
        ).fetchone()
    finally:
        connection.close()

    if row is None:
        print(f"Note {note_id} was not found.")
        return

    print(f"ID: {row['id']}")
    print(f"Title: {row['title']}")
    print(f"Source: {row['source']}")
    print(f"Source file: {row['source_file']}")
    print(f"Created: {row['created_at']}")
    print("\nAI summary:")
    print(row["ai_summary"])
    print("\nOriginal text:")
    print(row["raw_text"])


def build_parser():
    parser = argparse.ArgumentParser(
        description="Manage an AI-agnostic second brain in SQLite.",
        suggest_on_error=True,
    )
    subparsers = parser.add_subparsers(dest="command", required=True)

    subparsers.add_parser("init", help="Create the core notes table.")
    subparsers.add_parser("list", help="List stored notes.")

    ingest_parser = subparsers.add_parser("ingest", help="Ingest raw text and JSON metadata.")
    ingest_parser.add_argument("raw_file", type=Path)
    ingest_parser.add_argument("metadata_file", type=Path)

    show_parser = subparsers.add_parser("show", help="Show one stored note.")
    show_parser.add_argument("note_id", type=int)

    return parser


def main():
    args = build_parser().parse_args()

    if args.command == "init":
        initialize_database()
    elif args.command == "ingest":
        ingest_note(args.raw_file, args.metadata_file)
    elif args.command == "list":
        list_notes()
    elif args.command == "show":
        show_note(args.note_id)


if __name__ == "__main__":
    main()

How to compare this file

Check the function order plus indentation first. Confirm that the parser contains only the four commands introduced in this step.

Your first note is stored as a durable database record. Next, you'll test whether a title-only lookup can find an idea buried in its original text.

Test Title-Only Search

Your first note is stored in a local SQLite database. You can inspect the original text without losing its separate AI summary.

Useful knowledge can sit deeper inside a note than its title suggests. This step gives you a title lookup so you can test that retrieval boundary yourself.

In this step, get ready to:
  • Add a parameterized SQL query for note titles.
  • Compare the title-search result with the immutable source text.
Add the title-only query

A parameterized query uses a placeholder for the search term. SQLite receives that term separately from the query structure.

  • In brain.py, find the end of show_note().
  • Add title_search() below show_note() by pasting this code:
def title_search(query):
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            """
            SELECT id, title, source
            FROM notes
            WHERE title LIKE ?
            ORDER BY id
            """,
            (f"%{query}%",),
        ).fetchall()
    finally:
        connection.close()

    if not rows:
        print("No matching notes.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']}")

What Does This Function Do?

  • The WHERE title LIKE ? clause limits the lookup to the title column.
  • The ? placeholder keeps the search value separate from the SQL statement.
  • The % characters allow a match anywhere within a title.
  • The function prints No matching notes. when the query returns no rows.
  • Save brain.py.
  • Return to the PowerShell panel from earlier.
  • Confirm that the earlier inspection command still runs by entering:
python brain.py show 1

What Does This Check Prove?

The existing command runs the updated Python file. Seeing note 1 confirms that the new function has not introduced a syntax error.

You should still see note ID 1 with its AI summary followed by the original text.

Does the Inspection Command Fail?

  • Check that every statement inside title_search() uses the indentation shown above.
  • Confirm that show_note() ends before title_search() begins.

Still stuck? Help me find the syntax problem in my title search function.

Wire the new command

The search function now exists inside brain.py. The command parser needs a subcommand that captures the search term.

  • In brain.py, find the existing build_parser() function.
  • Replace the complete build_parser() function by pasting this code:
def build_parser():
    parser = argparse.ArgumentParser(
        description="Manage an AI-agnostic second brain in SQLite.",
        suggest_on_error=True,
    )
    subparsers = parser.add_subparsers(dest="command", required=True)

    subparsers.add_parser("init", help="Create the core notes table.")
    subparsers.add_parser("list", help="List stored notes.")

    ingest_parser = subparsers.add_parser("ingest", help="Ingest raw text and JSON metadata.")
    ingest_parser.add_argument("raw_file", type=Path)
    ingest_parser.add_argument("metadata_file", type=Path)

    show_parser = subparsers.add_parser("show", help="Show one stored note.")
    show_parser.add_argument("note_id", type=int)

    title_parser = subparsers.add_parser(
        "title-search", help="Search titles with SQL LIKE."
    )
    title_parser.add_argument("query")
    return parser

How Does the Parser Change?

  • The parser recognizes title-search as a valid subcommand.
  • The query argument captures the term entered after that subcommand.
  • The parser exposes the captured value through args.query.
  • Save brain.py.
  • Confirm that the updated parser still accepts the existing inspection command by entering:
python brain.py show 1

Why Repeat the Inspection Command?

The existing show branch passes through the updated parser. Seeing the stored note confirms that the replacement preserved the earlier command.

You should see Local Knowledge Base Design above the saved summary and original text.

Does the Parser Reject the Command?

  • Check that return parser remains inside build_parser().
  • Confirm that the show parser still defines note_id.

Still stuck? Help me repair the updated command parser.

The parser can now read the new command. The main() function needs a branch that sends the captured query to title_search().

  • In brain.py, find the existing main() function.
  • Replace the complete main() function by pasting this code:
def main():
    args = build_parser().parse_args()

    if args.command == "init":
        initialize_database()
    elif args.command == "ingest":
        ingest_note(args.raw_file, args.metadata_file)
    elif args.command == "list":
        list_notes()
    elif args.command == "show":
        show_note(args.note_id)
    elif args.command == "title-search":
        title_search(args.query)

How Does the New Branch Work?

The final branch watches for title-search. It passes args.query into title_search().

  • Save brain.py.
Test the title boundary

The new command examines the saved title through the query you added. Comparing its result with the complete record reveals which text the lookup can reach.

Before you run the command, do you think a title lookup can find ACID in note 1?

  • Search the saved title for ACID by running:
python brain.py title-search ACID

The Miss Is Intentional

You will see No matching notes. The command worked because it checked only the saved title.

This empty result exposes the retrieval boundary. Knowledge inside the source remains hidden from a title-only query.

  • Display the complete record for note 1 by running:
python brain.py show 1

What Does This Comparison Prove?

The show command reads the complete record. It exposes the separate AI summary plus the immutable source text.

Under Original text:, you should see Transactions provide ACID behavior.

The term exists in raw_text while the title remains Local Knowledge Base Design.

Seeing a Different Result?

  • Confirm that the search term is exactly ACID.
  • Check that title_search() uses WHERE title LIKE ?.
  • Confirm that note 1 still contains the source text from sample-note.txt.

Still stuck? Help me compare the title-search result with note 1.

✔️ Awesome, I've got everything!

Your command now searches note titles. You have also confirmed that it cannot reach terms stored only in the original text.

  • Confirm that brain.py is saved.
  • Confirm that note ID 1 remains available in second-brain.db.

ⓧ I'd like to double check the full code

Compare your complete brain.py file with this cumulative version.

import argparse
import json
import sqlite3
from pathlib import Path

DB_PATH = Path("second-brain.db")

CORE_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    raw_text TEXT NOT NULL,
    ai_summary TEXT NOT NULL,
    source TEXT NOT NULL,
    source_file TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""


def connect_db():
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection


def require_database():
    if not DB_PATH.exists():
        raise SystemExit("Database not found. Run `python brain.py init` first.")


def initialize_database():
    connection = connect_db()
    try:
        connection.executescript(CORE_SCHEMA_SQL)
        connection.commit()
    finally:
        connection.close()
    print(f"Database ready: {DB_PATH}")
    print(f"SQLite runtime: {sqlite3.sqlite_version}")


def ingest_note(raw_path, metadata_path):
    require_database()

    raw_text = raw_path.read_text(encoding="utf-8")
    try:
        metadata = json.loads(metadata_path.read_text(encoding="utf-8"))
    except ValueError as error:
        raise SystemExit(f"Metadata is not valid JSON: {error}") from error

    if not isinstance(metadata, dict):
        raise SystemExit("Metadata must be one JSON object.")

    required_fields = ("title", "summary", "source")
    missing_fields = [field for field in required_fields if not metadata.get(field)]
    if missing_fields:
        raise SystemExit(
            "Metadata is missing required fields: " + ", ".join(missing_fields)
        )

    connection = connect_db()
    try:
        cursor = connection.execute(
            """
            INSERT INTO notes(title, raw_text, ai_summary, source, source_file)
            VALUES (?, ?, ?, ?, ?)
            """,
            (
                str(metadata["title"]).strip(),
                raw_text,
                str(metadata["summary"]).strip(),
                str(metadata["source"]).strip(),
                str(raw_path),
            ),
        )
        connection.commit()
        note_id = cursor.lastrowid
    finally:
        connection.close()

    print(f"Ingested note {note_id}: {metadata['title']}")


def list_notes():
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            "SELECT id, title, source, created_at FROM notes ORDER BY id"
        ).fetchall()
    finally:
        connection.close()

    if not rows:
        print("No notes found.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']} | {row['created_at']}")


def show_note(note_id):
    require_database()
    connection = connect_db()
    try:
        row = connection.execute(
            """
            SELECT id, title, raw_text, ai_summary, source, source_file, created_at
            FROM notes
            WHERE id = ?
            """,
            (note_id,),
        ).fetchone()
    finally:
        connection.close()

    if row is None:
        print(f"Note {note_id} was not found.")
        return

    print(f"ID: {row['id']}")
    print(f"Title: {row['title']}")
    print(f"Source: {row['source']}")
    print(f"Source file: {row['source_file']}")
    print(f"Created: {row['created_at']}")
    print("\nAI summary:")
    print(row["ai_summary"])
    print("\nOriginal text:")
    print(row["raw_text"])


def title_search(query):
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            """
            SELECT id, title, source
            FROM notes
            WHERE title LIKE ?
            ORDER BY id
            """,
            (f"%{query}%",),
        ).fetchall()
    finally:
        connection.close()

    if not rows:
        print("No matching notes.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']}")


def build_parser():
    parser = argparse.ArgumentParser(
        description="Manage an AI-agnostic second brain in SQLite.",
        suggest_on_error=True,
    )
    subparsers = parser.add_subparsers(dest="command", required=True)

    subparsers.add_parser("init", help="Create the core notes table.")
    subparsers.add_parser("list", help="List stored notes.")

    ingest_parser = subparsers.add_parser("ingest", help="Ingest raw text and JSON metadata.")
    ingest_parser.add_argument("raw_file", type=Path)
    ingest_parser.add_argument("metadata_file", type=Path)

    show_parser = subparsers.add_parser("show", help="Show one stored note.")
    show_parser.add_argument("note_id", type=int)

    title_parser = subparsers.add_parser(
        "title-search", help="Search titles with SQL LIKE."
    )
    title_parser.add_argument("query")
    return parser


def main():
    args = build_parser().parse_args()

    if args.command == "init":
        initialize_database()
    elif args.command == "ingest":
        ingest_note(args.raw_file, args.metadata_file)
    elif args.command == "list":
        list_notes()
    elif args.command == "show":
        show_note(args.note_id)
    elif args.command == "title-search":
        title_search(args.query)


if __name__ == "__main__":
    main()

How to Compare the File

  • Check that title_search() appears between show_note() and build_parser().
  • Confirm that the parser branch uses title-search.
  • Confirm that the main() branch passes args.query to title_search().

You found the exact retrieval gap created by title-only search. Next, you will make ideas inside the original note discoverable.

Add Full-Text Search and AI Rules

Your title-only search just missed ACID even though the word exists in note 1. That gap proves useful knowledge can hide beyond a title.

In this step, you will add SQLite FTS5 so every stored text field becomes searchable. You will also add provider-neutral instructions that keep raw notes separate from AI-generated metadata.

In this step, get ready to:
  • Build an external-content full-text index that stays synchronized with the notes table.
  • Create shared instructions for AI tools and chat interfaces.
  • Ingest separate raw text and JSON metadata files before finding the note with full-text search.
Build and synchronize the FTS5 index

An external-content index stores searchable terms while the notes table remains the source of truth. Synchronization triggers update that index whenever a note changes.

  • In brain.py, add the FTS5 schema directly below CORE_SCHEMA_SQL by pasting this block:
SEARCH_SCHEMA_SQL = """
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
    title,
    raw_text,
    ai_summary,
    content='notes',
    content_rowid='id'
);

CREATE TRIGGER IF NOT EXISTS notes_ai AFTER INSERT ON notes BEGIN
    INSERT INTO notes_fts(rowid, title, raw_text, ai_summary)
    VALUES (new.id, new.title, new.raw_text, new.ai_summary);
END;

CREATE TRIGGER IF NOT EXISTS notes_ad AFTER DELETE ON notes BEGIN
    INSERT INTO notes_fts(notes_fts, rowid, title, raw_text, ai_summary)
    VALUES ('delete', old.id, old.title, old.raw_text, old.ai_summary);
END;

CREATE TRIGGER IF NOT EXISTS notes_au AFTER UPDATE ON notes BEGIN
    INSERT INTO notes_fts(notes_fts, rowid, title, raw_text, ai_summary)
    VALUES ('delete', old.id, old.title, old.raw_text, old.ai_summary);
    INSERT INTO notes_fts(rowid, title, raw_text, ai_summary)
    VALUES (new.id, new.title, new.raw_text, new.ai_summary);
END;
"""

How does the index stay synchronized?

  • The notes_fts virtual table indexes title, raw_text, and ai_summary from the canonical notes table.
  • The notes_ai trigger adds each new note to the index.
  • The notes_ad trigger removes deleted note text from search results.
  • The notes_au trigger replaces old indexed values after an update.
  • In brain.py, add reindex_search() directly below title_search() by pasting this function:
def reindex_search():
    require_database()
    connection = connect_db()
    try:
        connection.executescript(SEARCH_SCHEMA_SQL)
        connection.execute("INSERT INTO notes_fts(notes_fts) VALUES('rebuild')")
        connection.commit()
    except sqlite3.OperationalError as error:
        raise SystemExit(f"FTS5 setup failed: {error}") from error
    finally:
        connection.close()

    print("FTS5 index created and rebuilt from the notes table.")

Why rebuild the index?

The triggers only synchronize changes that happen after they exist. The rebuild command adds note 1 to the new index immediately.

The error handler also turns an FTS5 setup problem into a clear terminal message. This keeps the failure close to the command that caused it.

  • In build_parser(), add the reindex parser directly after the title-search parser:
    subparsers.add_parser("reindex", help="Create or rebuild the FTS5 index.")

What does this parser line do?

This registers reindex as a command that the CLI recognizes. The command needs no extra arguments because it rebuilds the whole search index.

  • In main(), add the reindex branch directly after the title-search branch:
    elif args.command == "reindex":
        reindex_search()

How is the command connected?

The branch sends the parsed reindex command to reindex_search(). That function creates the virtual table before rebuilding its contents.

  • Save brain.py.

Before you rebuild the index, do you think note 1 can be included even though it was inserted before the triggers existed?

  • Rebuild the index from the PowerShell panel by running this command:
python brain.py reindex

What should you see?

You should see FTS5 index created and rebuilt from the notes table. The rebuild reads the existing notes table, so note 1 is now searchable.

Did the reindex command fail?

  • Check that SEARCH_SCHEMA_SQL closes with three quotation marks after the final trigger.
  • Check that reindex_search() appears before build_parser().

Still stuck? Help me debug my FTS5 reindex command.

  • In brain.py, add search_notes() directly below reindex_search() by pasting this function:
def search_notes(query):
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            """
            SELECT
                notes.id,
                notes.title,
                notes.source,
                snippet(notes_fts, 1, '[', ']', '...', 12) AS excerpt
            FROM notes_fts
            JOIN notes ON notes.id = notes_fts.rowid
            WHERE notes_fts MATCH ?
            ORDER BY rank
            """,
            (query,),
        ).fetchall()
    except sqlite3.OperationalError as error:
        raise SystemExit(
            f"Search failed: {error}. Run `python brain.py reindex` first."
        ) from error
    finally:
        connection.close()
    if not rows:
        print("No matching notes.")
        return
    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']}")
        print(f"   {row['excerpt']}")

How does full-text search work?

  • The MATCH query searches every indexed field through notes_fts.
  • The join retrieves the canonical note title and source from notes.
  • The snippet() call selects a short excerpt from raw_text and surrounds matching terms with square brackets.
  • The rank order places stronger matches first.
  • In build_parser(), add the search parser directly below the reindex parser:
    search_parser = subparsers.add_parser("search", help="Search all indexed note text.")
    search_parser.add_argument("query")

What does the search parser accept?

The parser requires one query argument. That value becomes the full-text expression sent to search_notes().

  • In main(), add the search branch directly below the reindex branch:
    elif args.command == "search":
        search_notes(args.query)

How does the query reach FTS5?

The branch passes args.query into search_notes(). The function binds it as a parameter instead of inserting it into the SQL string.

  • Save brain.py.

Before you search, do you expect the same title-only failure or a match from the original note text?

  • Search every indexed field for ACID by running this command:
python brain.py search ACID

What should you see?

You should see note ID 1 with the title Local Knowledge Base Design. The excerpt contains [ACID] from the immutable source text.

That is the retrieval gap closed. Your CLI can now find an idea even when the title never mentions it.

✔️ Awesome, I've got everything!

Great. Confirm that you saved brain.py before moving on.

ⓧ I'd like to double check the full code

import argparse
import json
import sqlite3
from pathlib import Path

DB_PATH = Path("second-brain.db")

CORE_SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    raw_text TEXT NOT NULL,
    ai_summary TEXT NOT NULL,
    source TEXT NOT NULL,
    source_file TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""

SEARCH_SCHEMA_SQL = """
CREATE VIRTUAL TABLE IF NOT EXISTS notes_fts USING fts5(
    title,
    raw_text,
    ai_summary,
    content='notes',
    content_rowid='id'
);

CREATE TRIGGER IF NOT EXISTS notes_ai AFTER INSERT ON notes BEGIN
    INSERT INTO notes_fts(rowid, title, raw_text, ai_summary)
    VALUES (new.id, new.title, new.raw_text, new.ai_summary);
END;

CREATE TRIGGER IF NOT EXISTS notes_ad AFTER DELETE ON notes BEGIN
    INSERT INTO notes_fts(notes_fts, rowid, title, raw_text, ai_summary)
    VALUES ('delete', old.id, old.title, old.raw_text, old.ai_summary);
END;

CREATE TRIGGER IF NOT EXISTS notes_au AFTER UPDATE ON notes BEGIN
    INSERT INTO notes_fts(notes_fts, rowid, title, raw_text, ai_summary)
    VALUES ('delete', old.id, old.title, old.raw_text, old.ai_summary);
    INSERT INTO notes_fts(rowid, title, raw_text, ai_summary)
    VALUES (new.id, new.title, new.raw_text, new.ai_summary);
END;
"""


def connect_db():
    connection = sqlite3.connect(DB_PATH)
    connection.row_factory = sqlite3.Row
    return connection


def require_database():
    if not DB_PATH.exists():
        raise SystemExit("Database not found. Run `python brain.py init` first.")


def initialize_database():
    connection = connect_db()
    try:
        connection.executescript(CORE_SCHEMA_SQL)
        connection.commit()
    finally:
        connection.close()
    print(f"Database ready: {DB_PATH}")
    print(f"SQLite runtime: {sqlite3.sqlite_version}")


def ingest_note(raw_path, metadata_path):
    require_database()

    raw_text = raw_path.read_text(encoding="utf-8")
    try:
        metadata = json.loads(metadata_path.read_text(encoding="utf-8"))
    except ValueError as error:
        raise SystemExit(f"Metadata is not valid JSON: {error}") from error

    if not isinstance(metadata, dict):
        raise SystemExit("Metadata must be one JSON object.")

    required_fields = ("title", "summary", "source")
    missing_fields = [field for field in required_fields if not metadata.get(field)]
    if missing_fields:
        raise SystemExit(
            "Metadata is missing required fields: " + ", ".join(missing_fields)
        )

    connection = connect_db()
    try:
        cursor = connection.execute(
            """
            INSERT INTO notes(title, raw_text, ai_summary, source, source_file)
            VALUES (?, ?, ?, ?, ?)
            """,
            (
                str(metadata["title"]).strip(),
                raw_text,
                str(metadata["summary"]).strip(),
                str(metadata["source"]).strip(),
                str(raw_path),
            ),
        )
        connection.commit()
        note_id = cursor.lastrowid
    finally:
        connection.close()

    print(f"Ingested note {note_id}: {metadata['title']}")


def list_notes():
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            "SELECT id, title, source, created_at FROM notes ORDER BY id"
        ).fetchall()
    finally:
        connection.close()

    if not rows:
        print("No notes found.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']} | {row['created_at']}")


def show_note(note_id):
    require_database()
    connection = connect_db()
    try:
        row = connection.execute(
            """
            SELECT id, title, raw_text, ai_summary, source, source_file, created_at
            FROM notes
            WHERE id = ?
            """,
            (note_id,),
        ).fetchone()
    finally:
        connection.close()

    if row is None:
        print(f"Note {note_id} was not found.")
        return

    print(f"ID: {row['id']}")
    print(f"Title: {row['title']}")
    print(f"Source: {row['source']}")
    print(f"Source file: {row['source_file']}")
    print(f"Created: {row['created_at']}")
    print("\nAI summary:")
    print(row["ai_summary"])
    print("\nOriginal text:")
    print(row["raw_text"])


def title_search(query):
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            """
            SELECT id, title, source
            FROM notes
            WHERE title LIKE ?
            ORDER BY id
            """,
            (f"%{query}%",),
        ).fetchall()
    finally:
        connection.close()

    if not rows:
        print("No matching notes.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']}")


def reindex_search():
    require_database()
    connection = connect_db()
    try:
        connection.executescript(SEARCH_SCHEMA_SQL)
        connection.execute("INSERT INTO notes_fts(notes_fts) VALUES('rebuild')")
        connection.commit()
    except sqlite3.OperationalError as error:
        raise SystemExit(f"FTS5 setup failed: {error}") from error
    finally:
        connection.close()

    print("FTS5 index created and rebuilt from the notes table.")


def search_notes(query):
    require_database()
    connection = connect_db()
    try:
        rows = connection.execute(
            """
            SELECT
                notes.id,
                notes.title,
                notes.source,
                snippet(notes_fts, 1, '[', ']', '...', 12) AS excerpt
            FROM notes_fts
            JOIN notes ON notes.id = notes_fts.rowid
            WHERE notes_fts MATCH ?
            ORDER BY rank
            """,
            (query,),
        ).fetchall()
    except sqlite3.OperationalError as error:
        raise SystemExit(
            f"Search failed: {error}. Run `python brain.py reindex` first."
        ) from error
    finally:
        connection.close()

    if not rows:
        print("No matching notes.")
        return

    for row in rows:
        print(f"{row['id']}: {row['title']} | {row['source']}")
        print(f"   {row['excerpt']}")


def delete_note(note_id):
    require_database()
    connection = connect_db()
    try:
        row = connection.execute(
            "SELECT title FROM notes WHERE id = ?", (note_id,)
        ).fetchone()
        if row is None:
            print(f"Note {note_id} was not found.")
            return

        connection.execute("DELETE FROM notes WHERE id = ?", (note_id,))
        connection.commit()
        title = row["title"]
    finally:
        connection.close()

    print(f"Deleted note {note_id}: {title}")


def backup_database(target_path):
    require_database()
    target_path.parent.mkdir(parents=True, exist_ok=True)

    source_connection = connect_db()
    target_connection = sqlite3.connect(target_path)
    try:
        source_connection.backup(target_connection)
        row = target_connection.execute("SELECT COUNT(*) FROM notes").fetchone()
        note_count = row[0]
    finally:
        target_connection.close()
        source_connection.close()

    print(f"Backup created at {target_path} with {note_count} note(s).")


def vacuum_database():
    require_database()
    connection = connect_db()
    try:
        connection.execute("VACUUM")
    finally:
        connection.close()
    print("Database vacuum complete.")


def build_parser():
    parser = argparse.ArgumentParser(
        description="Manage an AI-agnostic second brain in SQLite.",
        suggest_on_error=True,
    )
    subparsers = parser.add_subparsers(dest="command", required=True)

    subparsers.add_parser("init", help="Create the core notes table.")
    subparsers.add_parser("list", help="List stored notes.")

    ingest_parser = subparsers.add_parser("ingest", help="Ingest raw text and JSON metadata.")
    ingest_parser.add_argument("raw_file", type=Path)
    ingest_parser.add_argument("metadata_file", type=Path)

    show_parser = subparsers.add_parser("show", help="Show one stored note.")
    show_parser.add_argument("note_id", type=int)

    title_parser = subparsers.add_parser(
        "title-search", help="Search titles with SQL LIKE."
    )
    title_parser.add_argument("query")

    subparsers.add_parser("reindex", help="Create or rebuild the FTS5 index.")

    search_parser = subparsers.add_parser("search", help="Search all indexed note text.")
    search_parser.add_argument("query")

    delete_parser = subparsers.add_parser("delete", help="Delete one note by ID.")
    delete_parser.add_argument("note_id", type=int)

    backup_parser = subparsers.add_parser("backup", help="Create a consistent backup.")
    backup_parser.add_argument("target", type=Path)

    subparsers.add_parser("vacuum", help="Reclaim unused database space.")
    return parser


def main():
    args = build_parser().parse_args()

    if args.command == "init":
        initialize_database()
    elif args.command == "ingest":
        ingest_note(args.raw_file, args.metadata_file)
    elif args.command == "list":
        list_notes()
    elif args.command == "show":
        show_note(args.note_id)
    elif args.command == "title-search":
        title_search(args.query)
    elif args.command == "reindex":
        reindex_search()
    elif args.command == "search":
        search_notes(args.query)
    elif args.command == "delete":
        delete_note(args.note_id)
    elif args.command == "backup":
        backup_database(args.target)
    elif args.command == "vacuum":
        vacuum_database()


if __name__ == "__main__":
    main()
Add provider-neutral AI instructions

Your database accepts a stable metadata contract instead of depending on one provider. Shared Markdown instructions give different AI tools the same rules while the Python CLI remains in control of storage.

  • In the VS Code Explorer sidebar, use the new-file icon to create AGENTS.md inside second-brain-sql.
  • Add the shared project rules by pasting this content:
# Second Brain Project Rules

- Treat raw input files as immutable source material.
- Store AI-generated titles and summaries separately from original text.
- Never edit `second-brain.db` directly.
- Use `python brain.py ingest <raw-file> <metadata-file>` to add knowledge.
- Use `python brain.py search <query>` to retrieve knowledge.
- Use `python brain.py backup <target-file>` before destructive cleanup.
- Use `python brain.py delete <note-id>` for deletion so the FTS5 trigger stays synchronized.
- Return metadata as one valid JSON object with the keys `title`, `summary`, and `source`.

What do these rules protect?

The rules keep raw source files immutable while placing generated titles and summaries in separate fields. They also direct AI tools through deterministic CLI commands instead of direct database edits.

  • Save AGENTS.md.

You should now see AGENTS.md listed beside brain.py in the Explorer sidebar.

  • Use the new-file icon to create CLAUDE.md inside second-brain-sql.
  • Import the shared rules by pasting this line:
@AGENTS.md

Why import the shared file?

The import gives Claude the same project rules stored in AGENTS.md. One shared file prevents provider-specific instructions from drifting apart.

  • Save CLAUDE.md.

You should now see both instruction files in the Explorer sidebar.

  • Use the new-file icon to create ingest-prompt.md inside second-brain-sql.
  • Add the copy-and-paste prompt by pasting this content:
# Prepare second-brain metadata

Read the raw note I provide and return exactly one valid JSON object with these keys:

- `title`: a concise title grounded in the note
- `summary`: one or two factual sentences grounded only in the note
- `source`: the source label I provide

Do not include Markdown fences, commentary, or additional keys.
Do not rewrite or include the original note in the response. The local application stores the original file separately.
If the note contains uncertainty or disagreement, preserve that uncertainty in the summary.

Source label:

PASTE_SOURCE_LABEL_HERE

Raw note:

PASTE_RAW_NOTE_HERE

What does the fallback prompt enforce?

The prompt limits the response to title, summary, and source. It also tells the AI to leave the original note out of its response.

Any chat interface can follow this contract. Your local application remains responsible for reading and storing the untouched source file.

  • Save ingest-prompt.md.

Your Explorer sidebar should now list AGENTS.md, CLAUDE.md, and ingest-prompt.md beside the existing project files.

Ingest AI-prepared metadata

The final check uses Claude as a metadata producer while your local files remain authoritative. The same process works with another interface because the database only expects the JSON contract.

  • Use the new-file icon in the Explorer sidebar to create personal-note.txt inside second-brain-sql.
  • Write two or three sentences about a topic you want to remember.
  • Include one distinctive word that you can search for later.
  • Record that word here: your search phrase.
  • Save personal-note.txt.

You should see personal-note.txt in the Explorer sidebar. This file remains the original source for your new record.

  • Switch back to the Claude chat from earlier.
  • Paste the complete contents of ingest-prompt.md into the message field.
  • Replace PASTE_SOURCE_LABEL_HERE with personal.
  • Replace PASTE_RAW_NOTE_HERE with the complete contents of personal-note.txt.
  • Send the message.

Claude should return one JSON object containing title, summary, and source. The response should not contain the original note.

  • Return to the second-brain-sql folder in VS Code.
  • Use the new-file icon to create personal-metadata.json.
  • Paste Claude's JSON-only response into personal-metadata.json.
  • Save personal-metadata.json.

You should now see the raw note and metadata as two separate files. The raw note keeps your words while the JSON file holds the generated interpretation.

  • Ingest the two personal files through the CLI by running this command:
python brain.py ingest personal-note.txt personal-metadata.json

What should the ingest command report?

You should see a new note ID followed by the generated title. The application reads the raw text locally before storing the separate metadata fields.

Did the personal note fail to ingest?

  • Check that personal-metadata.json contains one JSON object without Markdown fences.
  • Check that the object contains non-empty title, summary, and source values.
  • Check that both personal files are saved inside second-brain-sql.

Still stuck? Help me fix my personal note ingestion.

Before you run the final search, do you expect the result to depend on which AI produced the metadata?

  • Search for your search phrase by running this command:
python brain.py search [[PERSONAL_SEARCH_PHRASE="your search phrase"]]

What proves the workflow works?

You should see your personal note's ID and generated title. Its excerpt contains your distinctive word inside square brackets.

That result proves FTS5 indexed the untouched raw text after ingestion. No API key or model identifier exists in the project files.

Can't find the personal phrase?

  • Check that the searched word appears exactly in personal-note.txt.
  • Run the ingest command again only if the earlier command did not report a new note ID.
  • Use one distinctive word for this check so the shell passes it as one query argument.

Still stuck? Help me trace why FTS5 cannot find my personal note.

Your second brain now retrieves ideas from titles, original text, and AI summaries through one local index. Next, you will protect that knowledge with a backup before testing safe deletion and compaction.

Back Up and Clean Up Your Knowledge Base

Your second brain now preserves raw notes separately from AI-generated metadata. Full-text search can retrieve ideas from every stored text field.

Reliable knowledge management needs a reversible cleanup path. You will protect the SQLite database before removing one sample record.

In this step, get ready to:
  • Create a consistent backup before changing the live database.
  • Delete the sample note through the application.
  • Compact the live database after deletion.

The CLI from earlier already includes backup, deletion, and compaction commands. This step runs those commands against your live database.

Create a consistent backup

A useful backup captures one consistent point in time. SQLite's backup API copies the database through an active connection.

  • Use the PowerShell panel from earlier to write the snapshot to backups\before-cleanup.db by running this command:
python brain.py backup backups\before-cleanup.db

How does the backup stay consistent?

  • The backup_database(target_path) function creates the destination directory.
  • The source_connection.backup(target_connection) call copies a consistent snapshot.
  • The function opens the copied database to count its stored notes.

You'll see a confirmation containing Backup created at backups\before-cleanup.db. The reported count is at least 2 because the snapshot contains the sample note plus your personal note.

Backup command did not finish?

  • Confirm the PowerShell prompt ends in second-brain-sql.
  • Confirm second-brain.db remains inside that folder.

Still stuck? Help me diagnose why my SQLite backup command is failing.

Delete a record through the application

A record ID identifies the exact database row to remove. Listing the current records lets you confirm the sample note still has ID 1.

  • List the current note IDs by running this command:
python brain.py list

What does this list prove?

The list_notes() function reads the live notes table. Its output gives you the IDs needed for controlled deletion.

You should see an entry beginning with 1: Local Knowledge Base Design. Your separately ingested personal note also remains in the list.

Deleting note 1 changes the live database. Your new backup keeps the complete pre-cleanup state available.

  • Remove sample note ID 1 by running this command:
python brain.py delete 1

What gets deleted?

  • The delete_note(1) function removes one row from the live notes table.
  • The notes_ad trigger removes the corresponding entry from notes_fts.
  • The original sample-note.txt file remains untouched.

You'll see Deleted note 1: Local Knowledge Base Design. The application has removed the sample record through the synchronized path.

  • Search for the sample term ACID again by running this command:
python brain.py search ACID

What does this search check?

The search reads from notes_fts. An empty result proves the deletion trigger kept the full-text index aligned with the live table.

You'll see No matching notes. Note ID 1 has disappeared from both storage and search.

Still finding note 1?

  • Confirm the deletion output names note ID 1.
  • Confirm brain.py still defines the notes_ad trigger inside SEARCH_SCHEMA_SQL.

Need a hand? Help me check why my deleted SQLite note still appears in FTS5 search.

Reclaim space and verify the result

Deleted rows can leave unused pages inside the database file. VACUUM rebuilds the live file to reclaim that space.

  • Compact second-brain.db by running this command:
python brain.py vacuum

What does compaction preserve?

  • The vacuum_database() function runs VACUUM against the live database.
  • The rebuild preserves your remaining records.
  • The backup remains a separate point-in-time file.

You'll see Database vacuum complete. Your live database has been rebuilt around the records that remain.

Before you run the final checks, do you expect note ID 1 to appear in either result?

  • Check the final live database state by running these commands:
python brain.py list
python brain.py search ACID

What do these checks prove?

  • The first command confirms which records remain in the live notes table.
  • The second command confirms the sample term is absent from the synchronized search index.

Your personal note remains in the list. The search prints No matching notes.

  • Switch back to Visual Studio Code from earlier.
  • Expand the backups folder in the Explorer sidebar.

You'll see before-cleanup.db inside the folder. That snapshot still contains note ID 1 because you created it before deletion.

Compaction or final checks failed?

  • Close any database viewer that currently has second-brain.db open.
  • Return to the PowerShell panel inside second-brain-sql.
  • Run the failed cleanup command again.

Still blocked? Help me diagnose my final SQLite cleanup checks.

You completed the safety loop. The live database is compact while the backup still preserves the pre-cleanup records.

Secret mission

Prove the Workflow Works with Another AI

Your local CLI already accepts metadata through a provider-neutral JSON contract. Use a second AI provider to prepare a new note, then prove that the unchanged SQLite workflow can ingest and find it.

Clean Up Your Resources

Clean Up Your Resources

Your second brain lives entirely in local files, so it has no ongoing hosting cost. You can keep the project available, pause your work, or remove every project file.

Resources you used:

  • The live second-brain.db database with its synchronized FTS5 index.
  • The point-in-time backup at backups\before-cleanup.db.
  • The Python CLI file brain.py.
  • The provider-neutral instruction files AGENTS.md, CLAUDE.md, and ingest-prompt.md.
  • The raw note files sample-note.txt and provider-test.txt.
  • The metadata files sample-metadata.json and provider-test.json.

Keep everything running

No action is needed. Choose this option if you want to keep adding notes or experimenting with other metadata providers.

  • Keep the second-brain-sql folder in its current location.
  • Preserve second-brain.db for future note ingestion.
  • Make a fresh backup before your next deletion session.

Your searchable notes remain available without a background database server or ongoing local cost.

Pause - I'll come back to this later

SQLite has no background server to stop. Closing your editor stops the editing session without changing the project files.

  • Close the Visual Studio Code window from earlier.
  • Close the PowerShell window from earlier.
  • Keep the second-brain-sql folder in its current location.

Your live database, backup, source notes, metadata files, and AI instructions remain intact for your return.

Delete - I don't want to use this again

Deletion is irreversible. The quick backup check below shows exactly what the folder removal includes.

The deletion removes second-brain.db. It also removes backups\before-cleanup.db.

First, verify the backup that deletion removes:

  • In Visual Studio Code, confirm backups\before-cleanup.db is visible inside the current second-brain-sql folder.
  • Close the Visual Studio Code window from earlier.
  • Close the PowerShell window from earlier.

Now remove the local project folder:

  • Press the Windows key to open search.
  • Type File Explorer into the search box.
  • Press Enter to open it.
  • Navigate to the location where you created the second-brain-sql folder.
  • Delete the second-brain-sql folder through File Explorer.
  • Approve the deletion confirmation if File Explorer displays one.

You should no longer see second-brain-sql in its previous location. The live database and every backup stored inside the folder are gone.

Nice Work!

Nice Work!

You did it! You've built a local second brain that keeps your original notes trustworthy.

You've learned how to:

  • Built a local second brain with Python. Stored its records in a single SQLite database file. Kept immutable source text separate from AI-generated metadata.
  • Experienced the limit of title-only search. Replaced it with full-text search through SQLite FTS5. Kept the search index synchronized with external-content triggers.
  • Created a consistent backup before deletion. Deleted a record through the CLI. Its synchronized search entry disappeared with it. Reclaimed unused database space with VACUUM.
  • Completed the optional Secret Mission with a second AI provider. Reused the provider-neutral instructions. Kept the ingestion contract unchanged. Found portability through the same FTS5 index.

Ready to quiz yourself?