In previous posts, I built out python code for an application wherein a user could sign up, log in with authentication, and track his/her activities leading to their specific goals. Previously, I had used key-value pairs within a dictionary and lists to store my input values, and once the application session closed, that data was lost. Das is nicht gut! So let’s build a back-end Posgres db using flask. I’ve renamed the app babysteps, too – to me, that better reflects that otentimes our goals requires patience and a focus on consistent showing up and moving forward, one step at a time. No get-rich-quick schemes here! Nosiree, this is about doing the hard work, showing up every day, and seeing the payoff grow over time. This app will help with that (hopefully).

First, I diagramed my app:

Goals fit into a Category (one-to-one relationship), and one goal can have many activities (one-to-many). A user can have many activities (one-to-many). I created the tables first, then added the foreign key constraints. Here’s. the code:

CREATE TABLE goals (
	id SERIAL,
	name VARCHAR(50) NOT NULL,
	description TEXT,
	start_date DATE NOT NULL,
	end_date DATE NOT NULL,
	image VARCHAR(255),
	PRIMARY KEY (id)
);

CREATE TABLE activities (
	id SERIAL,
	date DATE NOT NULL,
	notes TEXT,
	title VARCHAR(100) NOT NULL,
	feeling VARCHAR(100),
	metric_one VARCHAR(100) NOT NULL,
	metric_two VARCHAR(100),
	goal_id INT NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE categories (
	id SERIAL,
	name VARCHAR(50) UNIQUE NOT NULL,
	description TEXT,
	activity_id INT UNIQUE NOT NULL,
	PRIMARY KEY (id)
);

ALTER TABLE categories
ADD CONSTRAINT fk_categories_activities
FOREIGN KEY (activity_id)
REFERENCES activities;

CREATE TABLE users (
	id SERIAL,
	username VARCHAR(255) UNIQUE NOT NULL,
	phone_number VARCHAR(10),
	email VARCHAR(50),
	first_name VARCHAR(100) NOT NULL,
	last_name VARCHAR(100) NOT NULL,
	password VARCHAR(50) UNIQUE NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE users_activities (
	id SERIAL,
	user_id INTEGER NOT NULL,
	activity_id INTEGER NOT NULL,
	PRIMARY KEY (user_id,activity_id)
);

ALTER TABLE users_activities
ADD CONSTRAINT fk_users_activities_users
FOREIGN KEY (user_id)
REFERENCES users;

ALTER TABLE users_activities
ADD CONSTRAINT fk_users_activities_activities
FOREIGN KEY (activity_id)
REFERENCES activities;

ALTER TABLE activities
ADD CONSTRAINT fk_activities_goals
FOREIGN KEY (goal_id)
REFERENCES goals;

I ran these sql statements in PGadmin and the tables were created. Now let’s make the backend.

Here is some code that is useful as a template for security code used in web apps:

import hashlib
import secrets

def scramble(password: str):
    """Hash and salt the given password"""
    salt = secrets.token_hex(16)
    return hashlib.sha512((password + salt).encode('utf-8')).hexdigest()