Skip to content

Automated Expense Tracking & Reporting System

Architectural documentation for the automated expense tracking system utilizing n8n, NocoDB, and Google Gemini.

1. Architecture: Transition to NocoDB

Data integrity and scalability requirements necessitated the migration from Google Sheets to NocoDB:

Requirement Advantage of NocoDB
Data Typing Enforcement of Currency, Date, and Integer types.
Integrity Foreign Key links between transactions and source emails.
Efficiency Native database filtering for near-zero latency retrieval.
Concurrency Native handling of simultaneous write operations.

2. Database Schema

State is maintained across three relational tables:

  1. Emails: Raw metadata and processing states (parse_status: pending, success, failed).
  2. Transactions: Immutable ledger of parsed financial data mapped to source emails.
  3. Category Rules: Knowledge base for merchant pattern matching and classification.

3. Pipeline 1: Transaction Ingestion

Workflow execution is triggered by transactional email receipt.

Operation Flow

  1. Extraction: Raw email content is processed by Google Gemini for entity extraction (merchant, amount, date).
  2. Categorization: Extracted data is checked against the category_rules table.
    • Match: Deterministic category is applied.
    • No Match: New merchant is flagged for classification.
  3. Commit: Validated data is committed to the transactions table.
  4. Closure: Email status is updated to success to prevent re-processing.

4. Pipeline 2: Daily Financial Summary

Execution is scheduled daily at 22:00 for data aggregation and reporting.

Aggregation Logic

  1. Timezone Filtering: Retrieval of transactions matching the current calendar date (Asia/Jakarta).
  2. Summation: Calculation of total expenditure and itemized breakdown via JavaScript.
  3. Dispatch: Formatted summary delivery to Discord.

Reporting Template

Daily Expense Report
Date: {{ $json.date }}
────────────────
{{ $json.breakdown }}
────────────────
TOTAL SPENT: {{ $json.total_spend }}
(Transactions: {{ $json.transaction_count }})