But how I automate budget management

I know I should do budget tracking but kinda hard to keep doing it everyday. Then I come up it have a way to do it.

I have an app that I really like for tracking my expenses. If you are looking for an app like this, I would recommend Cashew. It’s an app that is written using Material You, beautiful and the most feature-rich I’ve ever used. It’s also free and open-source. But this blog post is not about recommending an app. The app is already good. The problem is that I’m too lazy to add data more everytime I paid something. After using the app for a while, I found that there was a big gap in my data that I had to request a statement from the bank to add it myself lol. (which is not that bad actually, but reading pdf is not that fun especially in Thai)

From the statements in PDF that I requested from the bank, I found that the bank that provides the best details is Kasikorn Bank. It’s contain both the recipient’s name and the store name. Unfortunately, it’s not good enough since I have to sit and extract the data myself. AI can help a lot but if it misses, it’s really hard to find.

But then one day (last Sunday) I thought that we could use Cloudflare Email Routing + Email Worker to receive emails from the bank and extract the data to make it into a budget.

Here’s how

Before doing anything, the domain you use must be a domain that uses Cloudflare name servers. Personally, I recommend using Cloudfalre even if you buy a domain from any provider other provider.

1. Create a new Worker that is an email worker which can be done using the CLI or in the dashboard. But in this case, I will use the CLI for easier source control and use Wrangler to start by setting up as follows

pnpx wrangler login # if not done yet
pnpm create cloudflare
# After that, choose the settings as you like. In this blog, I use TypeScript

2. Add D1 to store data Honestly, I wanted to use the Google Sheet API a lot but in cloudflare worker, because it’s an edge, I can’t use some Node.js functions (like multiple encoding algorithms) so it’s okay to use D1

pnpm wrangler d1 create budget-tracking

Here you will get database_name binding and database_id don’t forget to save it in wrangler.toml

3. Set up the database Create a schema.sql file that contains an SQL file specifying the table structure to be used. In this case, I focus on storing email content so I will use only one table

/* ./schema.sql */

DROP TABLE IF EXISTS transactions;
CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  sender VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  metadata TEXT NOT NULL
);

And apply both remote and local with the command

pnpx wrangler d1 execute budget-tracking --local --file=./schema.sql
pnpx wrangler d1 execute budget-tracking --remote --file=./schema.sql

[!note] This way you can run SQL commands both locally and remotely with pnpx wrangler d1 execute budget-tracking --local --command="SELECT * FROM transactions"

4. The real hero of the job, The code to let the Worker handle our email. Don’t forget to install postal-mime with pnpm i postal-mime first

// ./src/index.ts
import PostalMime, { type Email } from 'postal-mime';

export default {
	async email(message, env, ctx) {
		const email = await PostalMime.parse(message.raw);

		function randomId() {
			return Math.random().toString(36).substring(7);
		}

		try {
			const metadata = {
				cc: email.cc,
				bcc: email.bcc,
				subject: email.subject,
				date: email.date,
				headers: email.headers,
				from: email.from,
				messageId: email.messageId,
				attachments: [],
				deliveredTo: email.deliveredTo,
				to: email.to,
				inReplyTo: email.inReplyTo,
				references: email.references,
				replyTo: email.replyTo,
				returnPath: email.returnPath,
				sender: email.sender,
			} satisfies Email;

			const stmt = env.DB.prepare("INSERT INTO transactions (sender, content, metadata) VALUES (?1, ?2, ?3)");
			await stmt.bind(message.from, email.html ?? email.text, JSON.stringify(metadata)).run();
		} catch (err) {
			console.error(err);
		}

		await message.forward(env.FORWARD);
	}
} satisfies ExportedHandler<Env>;

But don’t forget to set up the Destination address first before you can use it. Otherwise, the email won’t be sent. And add FORWARD=your_dest@mail.com insize .dev.vars too. Treat it like a .env file. Then put this var in secret with wrangler secret put FORWARD and fill in the email you want to forward to.

5. Finally, deploy and check if it works or not

pnpm wrangler deploy

1. Set up the email that the bank sends You should get a verification email normally. What’s more is that there is a database that stores the email data that comes in and can be accessed from D1 that we created. Don’t accidentally create an endpoint that opens this data to others

---

As I just finished this, I will try to separate the data for import into cashew and share it in the next post when I collect enough data…