Inbox Tracker

Background

For my Cloud Computing course at IE, we had to build a cloud-enabled expense-management app with Azure Functions as the serverless backend and a provided MySQL instance as the database.

MySQL Database Setup

The first step was to create the tables and schema for the database. This was a simple project so the only tables I created were Expenses, Users, and Categories.

-- Users table to store user information
CREATE TABLE Users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    passwordHash VARCHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories table to categorize expenses
CREATE TABLE Categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

-- Expenses table to store expense records
CREATE TABLE Expenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    userId INT NOT NULL,
    categoryId INT,
    amount DECIMAL(10, 2) NOT NULL,
    description VARCHAR(255),
    notes TEXT,
    receiptURL VARCHAR(255),
    expenseDate DATE NOT NULL,
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES Users(id),
    FOREIGN KEY (categoryId) REFERENCES Categories(id)
);

Creating the Azure Functions

After setting up the database, it was time to create the CRUD functions and connect them to the database and test if they worked.

I first created a helper for connecting azure functions to the MySQL database:

def get_db_connection(): 
    """
    Connects the application to the database.
    Uses environmental variables for the database credentials (from host.json).
    """
    return mysql.connector.connect(
        host=os.environ['DB_HOST'], # How do host.json got to be einvormental variables?
        user=os.environ['DB_USER'],
        password=os.environ['DB_PASSWORD'],
        database=os.environ['DB_NAME']
    )

I also created another helper for executing SQL queries in the function app:

def execute_query(query, params=None):
    conn = get_db_connection() 
    cursor = conn.cursor(dictionary=True) # Returns result of query as a dictionary (attribute:value)
    try:
        if params:
            cursor.execute(query, params)
        else:
            cursor.execute(query)
        
        if query.strip().upper().startswith("SELECT"):
            result = cursor.fetchall() 
        else:
            conn.commit()
            # For INSERT queries, return both rowcount and lastrowid
            result = {
                'rowcount': cursor.rowcount,
                'lastrowid': cursor.lastrowid
            }

        return result
    finally:
        cursor.close()
        conn.close()

This proved to be especially helpful as the number of queries I had to write got bigger and bigger.

Writing the azure functions wasn’t much different to writing normal python functions. There were just the decorators and routes to manage the backend as a service that were kind of new to me. For example, this is how the GetExpenses function looked:

@app.function_name(name="GetExpenses")
@app.route(route="GetExpenses", auth_level=func.AuthLevel.FUNCTION)
def GetExpenses(req: func.HttpRequest) -> func.HttpResponse:
    try:
        userId = req.params.get('userId')
        if not userId:
            return func.HttpResponse("UserID is required", status_code=400)

        query = """
        SELECT e.*, c.name as categoryName
        FROM Expenses e
        LEFT JOIN Categories c ON e.categoryId = c.id
        WHERE e.userId = %s
        ORDER BY e.expenseDate DESC
        """
        result = execute_query(query, (userId,))

        # Ensure categoryName is never null
        for expense in result:
            expense['categoryName'] = expense['categoryName'] or 'Uncategorized'

        logging.info(f"Database query successful. Result: {result}")

        return func.HttpResponse(json.dumps(result, default=str), mimetype="application/json")

    except Exception as e:
        error_message = f"An error occurred in GetExpenses: {str(e)}"
        logging.error(error_message)
        send_to_dead_letter_queue(error_message, "GetExpenses", req.get_json())
        return func.HttpResponse(f"An error occurred: {str(e)}", status_code=500)

The send_to_dead_letter_queue function sends any error_message to Azure Queues to be explored later. I will show the implementation later in this blog.

Testing Azure Functions and Database Connection

After implementing the basic components of some of the Azure Functions, I used curl to test if they worked correctly.

curl "https://inboxtracker.azurewebsites.net/api/GetExpenses?userId=1"

Everything seems to be working well! We can now move on to building the user interface.

Building the frontend

There was no restriction on teh technology we could use for building the frontend. I had previous experience with React, building ReportCards.ai, so I chose to build the frontend with React. However, I was just starting to look at metaframeworks like Next.js and Remix. I had used Next.js before and wasn’t particularly fond of it, and I also wanted to learn something new so I went with Remix.

Working with Remix was actually quite pleasant. There is a ~20-minute tutorial on their website that I whizzed through and got the gist. Their actions and loaders are really convenient, especially when also using react-query. This is, for example what an action and loader in the project looked like:

action

export const action = async ({ request }: ActionFunctionArgs) => {
  const formData = await request.formData();
  const intent = formData.get("intent");
  const session = await getSession(request.headers.get("Cookie"));
  const userId = session.get("userId");
  if (intent === "logout") {
    
    return redirect("/login", {
      headers: {
        "Set-Cookie": await destroySession(session),
      },
    });
  }
  ...

loader

export const loader = async ({ request }: LoaderFunctionArgs) => {
  const userId = await requireUserId(request);
  
  // Fetch expenses and categories using the userId
  const expensesResponse = await fetch(`${AZURE_FUNCTION_BASE_URL}/GetExpenses?${AZURE_FUNCTION_KEY_CODE}&userId=${userId}`);
  const categoriesResponse = await fetch(`${AZURE_FUNCTION_BASE_URL}/GetCategories?${AZURE_FUNCTION_KEY_CODE}`);

  const [expenses, categories] = await Promise.all([
    expensesResponse.json(),
    categoriesResponse.json(),
  ]);

  return json<LoaderData>({ expenses, categories, userId, error});
};

Shadcn-UI

I used Shadcn-UI components for the styling of the project. It is simply the best component library out there.

Azure Blob Storage for Receipts

After the basic CRUD functionality for expenses had been implemented, it was time to handle receipts and invoices as images. As we were using Azure for the course, we decided to use their object storage offering called Azure Blob Storage to store receipts. The implementation was fairly simple and hassle-free; I just created a container through the Azure Portal and modified my Function App to handle the uploading of receipts:

# Upload the file to blob storage
connect_str = os.environ['AZURE_STORAGE_CONNECTION_STRING']
blob_service_client = BlobServiceClient.from_connection_string(connect_str)
container_name = "receipts"
blob_name = f"{userId}_{expenseDate}_{uuid.uuid4()}{file_extension}"
blob_client = blob_service_client.get_blob_client(container=container_name, blob=blob_name)
            
try:
  blob_client.upload_blob(
    receipt_file.read(),
    content_settings=ContentSettings(content_type=content_type)
  )
  receipt_url = blob_client.url
  logging.info(f"Receipt uploaded to blob storage: {receipt_url}")
  
except ResourceExistsError:
return func.HttpResponse("A blob with this name already exists", status_code=400)

OCR Functionality

As I was building and testing the receipt upload functionality, I found it annoying and redundant to have to enter the expense details after uploading the receipt. I had implemented a basic OCR feature in the past for a hobby project so it occured to me I could implement an OCR function that fills out the form after the user uploads a receipt, saving them a lot of time and effort. It wasn’t too hard to implement either, I just called the gpt-4o-mini model with the receipt image using the OpenAI API and received a JSON response back:

export async function extractExpense(receipt: string) {
    const response = await openai.chat.completions.create({
        model: "gpt-4o-mini",
        messages: [
            {
                "role": "system",
                "content": `Extract and return the following information in JSON format
                from the receipt: { date: date of expense(\"YYYY-MM-DD\"), 
                amount: amount of receipt, 
                description:  title of expense (super brief, put any supporting info in the notes), 
                companyName: name of company issuing the expense, 
                notes: any notes, 
                category: return the corresponding category id from the list of categories: ${categories.map(category => `${category.id}: ${category.name}`).join(', ')} }. Only return the JSON object and nothing else.`
            },
            {
                "role": "user",
                "content": [
                    {
                        "type": "image_url",
                        "image_url": {
                            "url": receipt
                        }
                    }
                ]
            },
        ],
        max_tokens: 300,
  });

Deployment

For the final presentation of the project, we deployed it to Render.com, referring to the docs on deploying an SSR Remix app.