Synopsis

I worked in a group of four to create an Instagram clone for our Database Systems class as a mobile application. This was a quarter-long project that was supposed to give us exposure to applying the database concepts and techniques that we had learned in class. Interestingly, there was no formal requirement that a relational database had to be used, so many other groups in my class ended up using Firebase. We had 4 milestones that covered project targets over the course of the quarter, and we had to present our work at every milestone. I’m proud to say that we were consistently able to maintain our place in the top-5 groups every milestone.

Our app design focused on replicating core features such as comments, likes, hashtags, user profiles, user privacy, geotagging, and search. Our target audience was young adults who wanted to see photos related to food, or fun spots in their local area. Thus location, hashtags, and followers would become the central focus of their feed.

Requirements

I list the more significant functional and non-functional requirements of our system.

Functional:

• Users should be able to sign up and authenticate with our service using an email and password
• User profile page can be set to private and only shown to followers
  • Profile page displays list of posts, map with pins for each post
• Search returns results for users, hashtags, and locations
• Home Feed displays content from followed users
• Comments and likes are shown on the page for each post
• Activity feed shows interactions for any of a user’s posts

Non-Functional:

• Mobile App navigation should be simple
  • Reuse pages in navigation hierarchy
• Posts should load under 5 seconds
• Works on Android and iOS

Architecture

For our stack we used Expo.io with React Native to create our mobile app frontend, we used Flask to serve our backend, and we used PostgreSQL for our database.

Data Model

For the backend we started by describing the relationships between users, posts, likes and comments.

We then translated our ER diagram into tables.

We then created a list of CRUD interactions for each entity in our system, a REST API for each action, and added a few special queries to support search and the user’s home page.

SELECT post.id, post.caption, post.photo, user.username, like_exists.pid
FROM post
JOIN follow ON follow.follower_uid = auth_user.id AND follow.following_uid = post.uid
LEFT OUTER JOIN ( SELECT post_like.pid 
FROM post_like
WHERE post_like.uid =  auth_user.id) AS like_exists
ON like_exists.pid = post.pid

This query selects posts for the user’s home page based on the users’s followers, and joins info from the post_like table to preload if the user has liked the post in the feed.

UX Design

The main pages of our app can be viewed on this slidedeck or in the demo below.