Expense Tracker App Database Design

Income,Expense Tracker using Azure SQL Database and App Service

This blog will cover steps to develop a single page Flask based web-app using Azure Services.

Solution Overview

A single page Flask based income and expense tracker web application for a single user is developed and deployed on Azure Platform. It integrates with MS Single Sign-on using msal library

Azure Services Used

Azure SQL Database
Azure App Service
Azure Active Directory

Application Overview

In this simple income-expense tracker web application, a user can log in, view income, expenses and add income, expenses. Income and/or expense is referred to as an item. Each item consists of a date of expense, category, amount. The item(s) are stored in Azure SQL Server.

The income-expense tracker includes the following components:

A webapp using Python with the Flask framework.
A SQL database that contains a users and income-expense table for the webapp to query.

Prerequisites

Python 3.7 or higher version
Visual Studio Code Editor

Development Environment Setup on Linux

Install Visual Studio Code
Install Python 3
Create Python Virtual Environment - Execute the below command to create python virtual environment

              # Create the environment python3 -m venv venv # Activate the environment source ./venv/bin/activate

Download requirements.txt. It contains all the required libraries. Execute the below command to install all of them.

              pip install -r requirements.txt

Overview of Application Code

Tables

              CREATE TABLE USERS (     id INT NOT NULL IDENTITY(1, 1),     username VARCHAR(64) NOT NULL,     password_hash VARCHAR(128) NOT NULL,     PRIMARY KEY (id) );  CREATE TABLE INCOME_EXPENSE(     id INT NOT NULL IDENTITY(1, 1),     category VARCHAR(150) NOT NULL,     date DATE NOT NULL DEFAULT(GETDATE()),     amount INT NOT NULL,     user_id INT NOT NULL,     PRIMARY KEY (id),     FOREIGN KEY (user_id) REFERENCES users(id) );

Sample Data

              INSERT INTO dbo.users (username, password_hash) VALUES ('admin', 'pbkdf2:sha256:150000$QlIrz6Hg$5f4cd25d78a6c79906a53f74ef5d3bb2609af2b39d9e5dd6f3beabd8c854dd60')  INSERT INTO dbo.income_expense (category, amount, user_id) VALUES (     'income',     5000,     1 );

Models

                              class                User                (                UserMixin                ,                db                .                Model                ):                __tablename__                =                'users'                id                =                db                .                Column                (                db                .                Integer                ,                primary_key                =                True                )                username                =                db                .                Column                (                db                .                String                (                64                ),                index                =                True                ,                unique                =                True                )                password_hash                =                db                .                Column                (                db                .                String                (                128                ))                def                __repr__                (                self                ):                return                '<User {}>'                .                format                (                self                .                username                )                class                Income_Expense                (                db                .                Model                ):                __tablename__                =                'income_expense'                id                =                db                .                Column                (                db                .                Integer                ,                primary_key                =                True                )                category                =                db                .                Column                (                db                .                String                (                150                ))                date                =                db                .                Column                (                db                .                Date                )                amount                =                db                .                Column                (                db                .                Integer                )                user_id                =                db                .                Column                (                db                .                Integer                ,                db                .                ForeignKey                (                'users.id'                ))                def                __repr__                (                self                ):                return                '<Amount {}>'                .                format                (                self                .                amount                )                def                save_changes                (                self                ,                form                ,                userId                ,                new                =                False                ):                self                .                category                =                form                .                category                .                data                self                .                amount                =                form                .                amount                .                data                self                .                date                =                form                .                date                .                data                self                .                user_id                =                userId                if                new                :                db                .                session                .                add                (                self                )                db                .                session                .                commit                ()

Forms

                              class                LoginForm                (                FlaskForm                ):                username                =                StringField                (                'Username'                ,                validators                =                [                DataRequired                ()])                password                =                PasswordField                (                'Password'                ,                validators                =                [                DataRequired                ()])                remember_me                =                BooleanField                (                'Remember Me'                )                submit                =                SubmitField                (                'Sign In'                )                class                IncomeExpenseForm                (                FlaskForm                ):                category                =                StringField                (                'Category'                ,                validators                =                [                DataRequired                ()])                amount                =                IntegerField                (                'Amount'                ,                validators                =                [                DataRequired                ()])                date                =                DateField                (                'Date'                ,                format                =                '%m/%d/%y'                ,                render_kw                =                {                'placeholder'                :                '3/16/21 for March 16, 2021'                },                validators                =                [                DataRequired                ()])                submit                =                SubmitField                (                'Save'                )

config.py - contains the code to read the configurable values from an external source like environment variables.

views.py - contains the routing logic for login, logout, creating and showing items.

Github contains the complete source code

Azure Infrastructure Setup

Resource Group and Azure SQL Database Creation

Integration with Active Directory

Azure App Service Creation and Deployment