AI Assistant

Guide 25: AI Assistant

Ask natural language questions and get real answers from your financial data

Overview

The AI Assistant is your conversational interface to everything in OtterLedger. Ask a question in plain English -- "How much did I spend on dining last month?" -- and the assistant translates it into an actual database query, runs it against your data, and presents the results in a readable format. It is not pattern matching or canned responses; the assistant generates real SQL queries against your SQLite database in real time.

Beyond data queries, the assistant also handles general financial conversations, categorization help, budget tracking, invoice lookups, and bill reminders -- all through the same chat interface.

What you'll learn:

• Opening the AI Assistant and managing chat sessions
• Types of questions you can ask (spending, income, budgets, invoices, bills, and more)
• How the NLP-to-SQL engine works under the hood
• Understanding formatted responses, tables, and insights
• Privacy considerations for local vs. cloud AI
• Tips for getting the best results

Time required: Immediate answers -- most queries return in 1-3 seconds

Prerequisites

Before using the AI Assistant, make sure you have:

• Transactions in OtterLedger -- the more data you have, the more useful the assistant becomes
• At least one AI provider configured -- see Guide 38: AI Configuration for setup instructions

The assistant supports four AI providers:

Tip: Local Llama works completely offline and keeps all your data on your computer. Cloud providers generally produce more sophisticated SQL and richer conversational responses.

Opening the AI Assistant

1. Click AI Assistant in the left sidebar navigation
2. The assistant opens with a chat interface, a session list on the left, and a provider selector at the top

[Screenshot: AI Assistant main view showing sidebar sessions, provider dropdown, and chat area]

Chat Sessions

The AI Assistant organizes your conversations into sessions. Each session preserves its full message history, so you can return to a previous conversation at any time.

Creating a New Session

• Click the New Chat button at the top of the session sidebar
• Or simply start typing -- if no session is active, one is created automatically
• The session title is auto-generated from your first message

Switching Between Sessions

• Click any session in the left sidebar to load its conversation history
• The provider used for that session is restored automatically

Deleting a Session

• Right-click a session (or use the delete button) to remove it
• If you delete the currently active session, the chat area clears

Session Persistence

Sessions and their messages are stored in your OtterLedger database. They persist across application restarts. Each session tracks which AI provider was used, so switching providers does not affect existing sessions.

[Screenshot: Session sidebar showing multiple saved conversations with timestamps]

Asking Questions

Type your question in the input field at the bottom and press Enter or click Send. The assistant automatically determines whether your question is:

1. A data query -- questions about your transactions, accounts, balances, budgets, invoices, or bills. These are routed through the NLP-to-SQL engine.
2. A categorization request -- questions about categorizing uncategorized transactions. The assistant fetches your uncategorized items and generates AI suggestions.
3. A general conversation -- questions about accounting concepts, financial advice, or anything else. These use the AI provider's general knowledge.

You do not need to tell the assistant which mode to use. It detects your intent automatically.

How It Works: The NLP-to-SQL Engine

When you ask a data question, here is exactly what happens behind the scenes:

Your Question
    |
    v
Intent Detection ---- Is this a data query, categorization request, or conversation?
    |
    v (data query)
SQL Generation ------ AI translates your question into a SELECT query
    |
    v
SQL Validation ------ Safety checks: only SELECT allowed, no dangerous keywords
    |
    v
Query Execution ----- Runs against your actual SQLite database
    |
    v
Result Formatting --- Converts raw rows into a readable markdown table
    |
    v
Insight Generation -- (Optional) AI adds a brief analytical observation
    |
    v
Response Displayed -- Formatted results shown in the chat

Intent Detection

The assistant uses pattern matching to classify your message. It recognizes hundreds of patterns across these categories:

• Data query patterns -- "show me," "how much," "find transactions," "what are my," etc.
• Conversation patterns -- "hello," "explain accounting," "what is double-entry," etc.
• Follow-up patterns -- "now show me details," "compare to last month," "those same ones," etc.
• Categorization patterns -- "help categorize," "suggest categories," "uncategorized transactions," etc.

SQL Generation

Your question is sent to the configured AI provider along with a simplified database schema. The AI generates a SQLite SELECT query. For example:

SQL Safety

Every generated query passes through a strict validator before execution:

• Only SELECT statements -- INSERT, UPDATE, DELETE, DROP, and all other modification commands are blocked
• No system tables -- queries against sqlite_master, __EFMigrationsHistory, and similar tables are rejected
• No multi-statement injection -- semicolons mid-query are rejected
• No comment injection -- -- and /* */ patterns are blocked
• Automatic LIMIT -- if the query has no LIMIT clause, one is added (default: 100, max: 500)

Your data is never modified by the AI Assistant. It is strictly read-only.

Result Formatting

Raw query results are formatted into markdown tables with smart column handling:

• Currency formatting -- Amount, Balance, Total, and similar columns display as currency (e.g., $1,234.56)
• Date formatting -- Dates display as "Feb 25, 2026" instead of raw "2026-02-25"
• ID columns hidden -- Internal IDs (AccountId, CategoryId, etc.) are automatically excluded from display
• Long values truncated -- Values over 50 characters are shortened with ellipsis
• Right-aligned numbers -- Numeric columns align to the right for easier scanning

Insight Generation

For analytical queries (spending totals, budget comparisons, category breakdowns), the assistant may add a brief AI-generated insight below the results. This is a 1-2 sentence observation highlighting comparisons, trends, or anomalies. If there is nothing notable, no insight is added.

Question Types

Spending and Expense Questions

Ask about what you have spent, where, and when:

• "How much did I spend on dining this month?"
• "What are my top 10 expenses in January?"
• "Show me all transactions at Amazon"
• "Find expenses over $100 last month"
• "What did I buy at Costco?"
• "How much did I spend on groceries versus dining?"

Income Questions

• "What is my total income this year?"
• "Show me my income for the last 3 months"
• "How much did I receive from [client name]?"

Account and Balance Questions

• "What is my checking account balance?"
• "Show me all my accounts"
• "How much do I owe on credit cards?"

Budget Questions

Compare your actual spending against your budget allocations:

• "How am I doing on my grocery budget?"
• "Which categories are over budget this month?"
• "Show me my budget vs actual spending"
• "How much budget do I have remaining for dining?"
• "Am I on track with my budgets?"

Invoice Questions

Query your invoices and accounts receivable:

• "Show me unpaid invoices"
• "What invoices are overdue?"
• "How much is outstanding across all invoices?"
• "List invoices from this month"
• "What is the total I have invoiced this year?"

Bill and Payment Questions

Check on upcoming and recurring bills:

• "What bills are due this week?"
• "Show me overdue bills"
• "What are my recurring monthly bills?"
• "Which bills are on autopay?"
• "What business bills do I have?"

Transaction Search

Find specific transactions by payee, amount, date, or category:

• "Find transactions from Netflix"
• "Show me uncategorized transactions"
• "Transactions over $500 in the last 6 months"
• "What did I spend at Starbucks this year?"
• "Show my latest 20 transactions"

Categorization Help

Ask the assistant to help with uncategorized transactions:

• "Help me categorize my uncategorized transactions"
• "Suggest categories for my unclassified items"
• "What category should my Starbucks transactions be?"

The assistant will fetch up to 10 uncategorized transactions, run them through the AI categorization engine, and present suggestions with confidence levels. To apply the suggestions, use the Categorization Assistant in the Banking Center (see Guide 23: AI Categorization).

General Financial Conversations

The assistant also answers general questions using the AI provider's knowledge:

• "What is the difference between an asset and a liability?"
• "How does Schedule C work for freelancers?"
• "What expenses can I deduct as a sole proprietor?"
• "Explain budgeting basics"

Note: The assistant provides general financial guidance, not professional tax or legal advice. Consult a qualified professional for complex tax situations.

Example Conversations

Example 1: Spending Analysis

You: "How much did I spend on dining out last month?"

AI Assistant:

Found 12 results:

Payee Name	Amount	Date
Chipotle	-$18.50	Jan 28, 2026
Olive Garden	-$42.30	Jan 25, 2026
...	...	...

Insight: Your dining spending totaled $342.50 across 12 transactions last month, averaging $28.54 per visit. Chipotle was your most frequent restaurant with 4 visits.

Example 2: Budget Check

You: "How am I doing on my budgets this month?"

AI Assistant:

Found 8 results:

Category Name	Budgeted	Spent	Remaining
Groceries	$600.00	-$423.00	$177.00
Dining	$200.00	-$156.00	$44.00
Gas	$150.00	-$178.00	-$28.00
...	...	...	...

Insight: Your Gas category has exceeded its budget by $28. All other categories are within limits, though Dining is at 78% with a week remaining.

Example 3: Invoice Lookup

You: "Show me unpaid invoices"

AI Assistant:

Found 3 results:

Invoice Number	Status	Due Date	Total	Amount Paid
INV-042	Overdue	Feb 1, 2026	$2,500.00	$0.00
INV-045	Sent	Feb 28, 2026	$1,200.00	$0.00
INV-046	Draft	Mar 15, 2026	$800.00	$0.00

Example 4: Follow-Up Questions

You: "Show me my Amazon purchases this month"

AI Assistant: (displays table of Amazon transactions)

You: "Now compare to last month"

AI Assistant: (understands the context and shows last month's Amazon purchases for comparison)

The assistant tracks context from your previous query within a session. Follow-up questions like "show me details," "compare to last month," or "break that down by category" are enhanced with context from the prior query. Context expires after 5 minutes of inactivity.

Understanding Responses

Data Query Responses

When the assistant runs a database query, the response typically includes:

1. Result count -- "Found 12 results:" at the top
2. Markdown table -- Formatted with column headers, currency amounts, and dates
3. Truncation notice -- If more than 50 rows were returned, a note indicates how many are not shown
4. Insight (optional) -- A brief AI-generated observation in bold below the table

No Results

If a query returns no data, the assistant responds with: "No results found matching your query." This usually means:

• The time period has no matching transactions
• The category or payee name does not match exactly
• The account does not have the type of data you asked about

Error Responses

If something goes wrong, the assistant provides a clear message:

• "I had trouble understanding that query" -- the AI could not generate valid SQL. Try rephrasing.
• "I couldn't safely execute that query" -- the generated SQL failed validation. This is a safety measure.
• "I encountered an error while trying to query your data" -- a database execution error. Try a simpler question.

Copying Responses

Click the Copy button on any message to copy its full content (including markdown tables) to your clipboard. This is useful for pasting results into emails, spreadsheets, or documents.

Privacy

Local AI (Local Llama)

When using Local Llama, all processing happens on your computer:

• No data is sent to any external server
• Complete privacy -- your financial data stays on your machine
• Works without an internet connection
• Ideal for sensitive financial data

Cloud AI (Gemini, OpenAI, Claude)

When using a cloud provider, your question and relevant database schema are sent to the provider's API:

• The AI provider receives your question text and a simplified schema description
• Query results are generated locally -- only the question goes to the cloud, not your raw data
• Review your provider's privacy policy for data handling details
• Requires an active internet connection

Tip: If privacy is a top concern, use Local Llama for data queries. You can switch providers at any time using the dropdown at the top of the chat.

Tips for Better Results

1. Be specific with time frames -- "dining expenses in January 2026" works better than "recent food spending"
2. Use your exact category names -- The assistant queries your categories as stored. If your category is called "Dining & Restaurants," use that name
3. Include amounts when searching -- "expenses over $200" or "transactions between $50 and $100" helps the AI generate precise filters
4. Ask follow-ups -- After getting results, say "break that down by category" or "compare to last month" to drill deeper
5. One question at a time -- The assistant handles one query per message. Ask compound questions as separate messages
6. Name payees as they appear -- Use the payee name as it shows in your transactions (e.g., "AMZN" not "Amazon" if that is what your bank reports)
7. Try rephrasing -- If the assistant does not understand a query, try stating it differently. "Show me grocery transactions" and "find transactions in the groceries category" may yield different results
8. Use cloud providers for complex queries -- Cloud AI models generally produce better SQL for nuanced questions involving multiple joins, date math, or aggregations

What the AI Assistant Cannot Do

• Modify your data -- All queries are read-only SELECT statements. The assistant cannot create, edit, or delete transactions, accounts, or any other records
• Access external data -- It queries only your OtterLedger database, not stock prices, exchange rates, or external financial services
• Predict the future -- It reports on actual data, not projections (though it may note trends)
• Give professional advice -- It provides general financial guidance, not certified tax, legal, or investment advice
• Apply categorization suggestions -- It can suggest categories, but you apply them through the Banking Center's Categorization Assistant

Troubleshooting

Q: The assistant says "AI provider is not available"

A: Make sure at least one AI provider is configured in Settings. For Local Llama, the model must be downloaded and running. For cloud providers, verify your API key is entered correctly. See Guide 38: AI Configuration.

Q: Data queries return wrong numbers

A:

• Check that your category names match exactly (the AI uses LIKE matching, but close is not always close enough)
• Verify the date range -- "this month" means the current calendar month, "last month" means the previous calendar month
• Remember that expenses are stored as negative amounts. "Expenses over $50" means the AI looks for amounts less than -$50

Q: The assistant treats my data question as a general conversation

A: The intent detector uses pattern matching. If your question is not recognized as a data query, try rephrasing with explicit data keywords: "show me," "find," "how much," "list," "what are my." Adding a time frame or category name also helps trigger data mode.

Q: "No results found" but I know the data exists

A:

• The AI may have generated SQL with a filter that does not match your data exactly
• Try broader terms: "all transactions this month" instead of a specific payee
• Check that the transactions are in the correct date range and are not in Excluded status

Q: Responses are slow

A:

• Cloud providers depend on internet speed and API response times (typically 1-3 seconds)
• Local Llama speed depends on your hardware (CPU/GPU and RAM)
• Very large result sets take longer to format. The assistant caps display at 50 rows and query results at 500 rows

Q: Follow-up questions do not seem to use context from my previous question

A: Follow-up context expires after 5 minutes. If your previous question was more than 5 minutes ago, the assistant treats the new question as standalone. Also, the follow-up must use recognizable patterns like "now show," "compare to," "break that down," "those same," or "more details."

What's Next?

• Guide 23: AI Categorization -- Learn how OtterLedger's 5-tier AI system automatically categorizes your transactions
• Guide 26: Report Gallery -- Explore visual reports for spending, income, budgets, and more
• Guide 38: AI Configuration -- Set up and manage your AI providers, API keys, and model preferences

Need help? Visit the OtterLedger community at github.com/openledger or check the FAQ.