Build an AI‑Powered Expense Tracker with GLM‑4V‑Flash and MaxKB

Many people have tried traditional bookkeeping apps that require manual entry of each expense, which quickly becomes impractical and leads to uninstalling the app.

The goal is a simple assistant that can capture a receipt photo, automatically recognize the expense type, time, and amount, and store the data for real‑time querying and statistics.

Using Zhipu's free GLM‑4V‑Flash multimodal model, the assistant can extract information from various receipts (invoices, train tickets, restaurant slips, WeChat/Alipay payments, digital RMB records) without needing custom OCR models.

Implementation Idea

Upload or photograph a receipt and extract its content with GLM‑4V‑Flash.

Process the extracted information with GLM‑4‑Flash to generate an INSERT SQL statement.

Execute the SQL via MaxKB's function library to store the record.

Orchestrate the whole flow with MaxKB workflow.

Note: GLM‑4V‑Flash excels at fast single‑image understanding, suitable for real‑time or batch image analysis.

Feasibility Verification

Obtain an API key from Zhipu AI Open Platform:

#智谱AI开放平台地址
https://bigmodel.cn/usercenter

Install the Zhipu Python SDK:

#安装SDK库
pip install zhipuai

Validate the model with the following script:

from zhipuai import ZhipuAI
client = ZhipuAI(api_key='YOUR_API_KEY')
response = client.chat.completions.create(
    model='glm-4v-flash',
    messages=[
        {
            'role': 'user',
            'content': [
                {'type': 'text', 'text': '请提取这张消费中消费信息'},
                {'type': 'image_url', 'image_url': {'url': 'https://example.com/receipt.jpg'}}
            ]
        }
    ]
)
print(response.choices[0].message)

Multiple tests showed near‑100% accuracy in extracting receipt details.

Database Design

Create a MySQL database and a consumptions table:

#创建finance库
CREATE DATABASE finance /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
#创建consumptions表
CREATE TABLE consumptions (
    consumption_id INT NOT NULL AUTO_INCREMENT,
    amount DECIMAL(10,2) NOT NULL,
    consumption_date DATETIME NOT NULL,
    category VARCHAR(255) COLLATE utf8mb4_general_ci NOT NULL,
    PRIMARY KEY (consumption_id)
) ENGINE=InnoDB AUTO_INCREMENT=2572 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Generate 300 sample records for testing:

INSERT INTO `consumptions` (`amount`, `consumption_date`, `category`)
SELECT
  FLOOR(RAND()*2000+1) AS amount,
  DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND()*335) DAY) AS consumption_date,
  CASE
    WHEN RAND() < 0.25 THEN '餐饮'
    WHEN RAND() < 0.5 THEN '购物'
    WHEN RAND() < 0.75 THEN '交通'
    ELSE '娱乐'
  END AS category
FROM (
  SELECT a.N + b.N*10 + c.N*100 AS N
  FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
  CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
  CROSS JOIN (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
  LIMIT 300
) AS nums;

Integrating Zhipu AI

Step 1: Add GLM‑4V‑Flash and GLM‑4‑Flash models to MaxKB (both free and unlimited).

Note: MaxKB is an open‑source RAG‑based knowledge‑base Q&A system.

Function Library Design

Two Python functions are needed: one to write data into MySQL and another to read all consumption records.

Install the MySQL connector inside the MaxKB container:

#安装MySQL连接库
docker exec -it maxkb bash
pip install mysql-connector-python -i https://mirrors.aliyun.com/pypi/simple/

Write function:

import mysql.connector
from mysql.connector import Error

def execute_sql_query(query):
    """Execute a SQL query and return result or affected rows."""
    connection = None
    result = None
    try:
        connection = mysql.connector.connect(
            host='10.1.240.130',
            user='root',
            passwd='Password123@mysql',
            database='finance'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute(query)
            if query.strip().upper().startswith('SELECT'):
                result = cursor.fetchall()
            else:
                connection.commit()
                result = cursor.rowcount
    except Error as e:
        print(f'Error while connecting to MySQL {e}')
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print('MySQL connection is closed')
    return result

Read function (queries all records):

import mysql.connector
from mysql.connector import Error

def execute_sql_query():
    """Fetch all consumption records from the finance database."""
    connection = None
    result = None
    query = 'SELECT amount, consumption_date, category FROM finance.consumptions;'
    try:
        connection = mysql.connector.connect(
            host='xxx.xxx.xxx.xxx',
            user='root',
            passwd='xxxxxx',
            database='finance'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            cursor.execute(query)
            result = cursor.fetchall()
    except Error as e:
        print(f'Error while connecting to MySQL {e}')
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print('MySQL connection is closed')
    return result

Building the Assistant

After completing verification and preparation, create a MaxKB advanced workflow that enables image upload, calls GLM‑4V‑Flash to extract receipt information, generates an INSERT SQL statement, and stores it in MySQL.

Prompt used for the model:

图片：{{开始.image}}
第一点要求：理解图片中的信息，请判断图片信息是否属于消费类型，如果不属于消费类型，请直接输出0，无需执行第二点要求。如果是消费类型，执行第二点要求。
第二点要求：
- 判断消费属于哪类（餐饮、出行、住房、服装、娱乐、家庭、工作）
- 提取金额和时间（格式 2024-12-17 12:00:00）
- 根据提取的信息生成如下SQL：INSERT INTO `consumptions` (`amount`,`consumption_date`,`category`) VALUES (100.00,'2024-12-17 12:00:00','餐饮');
- 只输出完整的SQL语句。

The workflow then includes a query node to retrieve consumption data on demand.

Assistant Effect

By photographing a receipt, the GLM‑4V‑Flash model extracts details, generates the appropriate SQL, and inserts the record automatically, enabling quick and accurate expense management.

Examples include automatic recognition and entry for receipt, WeChat Pay, and Alipay records, as well as querying capabilities such as “What were the expenses on 2023‑01‑01?” or “Total spending last month?”.