Building a Social Network: Part I
Defining concepts, models, and security in PostgreSQL
Introduction
Social networks have become a part of daily life for many people around the world today. In this article, we will check out the first step in the design of a very basic social network: creating the database schema.
A social network is a perfect use case for bottom-up design, since we can define security and required behaviors in a robust and scalable way. This allows future upgrades to be largely non-breaking while being relatively easy to implement and maintain.
This social network will feature standard email/password authentication, uploading images, setting a profile photo, posting content (with optional image), and following other users.
Security features include robust password hashing, auto-generated UUID as primary key, limited access API role/user with explicit functions for creating new entries, and strict typing via user-defined types as required.
The project source code for this article series is available here on GitHub.
To test the example database, a PostgreSQL environment is required.
Setup
The setup script db/_create.sh runs a series of SQL files:
The first SQL file executed is db/init.sql:
This script creates the users social_demo_admin and social_demo_api and then the social_demo database with the admin as owner.
Next, social_demo_api_role is created and granted CONNECT and USAGE permissions to allow read access to all information stored within this database, and that role is then granted to social_demo_api, which the API will use to gain limited, explicitly-defined access. This greatly increases security and reduces the potential for developer error.
Core Functions
Core functions and required extensions are defined in db/core.sql:
This and subsequent SQL files are executed within the scope of the social_demo database. The extensions plpgsql and pgcrypto are loaded to facilitate PL/pgSQL and cryptographic functionality as required.
The hash function uses the cryptographic module to generate a hash using the blowfish algorithm and an iteration count of eight. This results in a hash that takes about 100k times longer to break than an MD5, and is generally secure when combined with a good design and proper security measures.
The flush function is part of a privacy feature, and when executed will delete any content older than 24 hours (except for profile photos). This will later be called periodically by the API to remove old content and clear room for more, which is more organic and similar to real-world interactions (not every single event needs a permanent record).
Images
Next let’s check out the images schema in db/images.sql:
Images within the social network may be of type JPG, PNG, or GIF. The type new_image is used to define new images to be added to the images table, which the API can only accomplish by calling add_image with the required data structure. Likewise, delete_image is the only method to delete an image record from the images table. The API will use the UUID generated for the image along with the filetype as a naming convention for files to be stored on disk or in cloud or other storage.
Users
The users schema is located in db/users.sql:
The types authenticated_user and new_user are defined to structure the data required for authenticating and creating users. The users table stores basic standard user data such as login information, name, and creation timestamp. Once again, functions to create and delete users are defined for explicit API access to prevent unwanted side effects to the user table.
Posts
The schema for posts is in db/posts.sql:
Posts are very simple, and effectively consist of text content with an optional image. As before, a data type and create/delete functions are defined for standardization. The posts table references the users and images tables for referential integrity, which PostgreSQL excels at in general. The user_id must match a record in the users table, and if an image_id is provided, it must match a record in the images table.
Followers
The followers schema is located in db/followers.sql:
No social network would be complete without some kind of social interaction structure, and the followers model is a good fit for this application and the temporal nature of content within it. The design of the followers table and supporting functions is kept simple and follows the general secure theme used throughout the application.
Conclusion
These SQL files define a simple database schema with built-in features for security and data integrity suitable for the next stage: building common models to be used by the API and UI implementations for performing authentication, interacting with the database, and providing functionality to mobile, web, and other client applications.
Stay tuned for Part II in this series, in which we’ll look at what it takes to prepare for building a solid API and UI on top of this database, in a secure and robust way that’s easy to deploy to target devices and services.
Thanks for reading!
