Table of contents
- What is Social Audio
-
2.2 Tech stack
Using Postgres to query for information
4.1 List all rooms
4.2 List rooms for those clubs that the user follows
4.3 Retrieve information for a single room
If you're reading this article, you mostly likely discovered it via some form of social media. They are platforms that enable people across the world to interact with each other and share information on what's happening in their own corners of the world. And for more than 3 decades since their advent, social media apps allowed sharing information mostly in the form of text or images, particularly when it comes to sharing content in real time.
In early 2020, an app called Clubhouse popped up in the iOS app store. It was a social media app, where the primary form of content was not photos, videos or text, but audio. By the end of the year in the midst of the COVID-19 pandemic, when social distancing and remote work had become the norm, the app exploded in popularity - gaining over half a million users!
And just like usual when a new feature in an app goes viral, tech giants in the social media space take a crack at cloning and offering it in an attempt to enamor their users! Ever since Clubhouse's first release in 2020, companies like Twitter, Facebook, Slack, Spotify, Discord and even Reddit have either been creating products that compete directly with Clubhouse, or adding Clubhouse-like features to their existing platforms. These features are collectively known as social audio - a new subclass of social media. Let's have look at what those features are.
What is Social Audio?
A Social Audio application can be thought of as part-radio, part house party, part conference-call, part-podcast — all rolled into one. Once you're inside such an app, you can listen to conversations happening within a room, or start one yourself on a topic or theme of your choice. The theme could be anything — interviewing a celebrity or an expert, a discussion on a niche topic like the future of AI, or simply chit-chat.
Here's a side-by-side comparison of Clubhouse and one of its competitor app, Spotify Greenroom —
Feature #1: Discover conversations
The home-screen on both the apps display a curated list of rooms for you based on your interests, and the clubs or groups that you follow. These rooms are spaces where conversations occur in real-time. There are some slight differences between what information is shown — but both apps show the room name, the number of participants, and give you a tiny preview of the list of speakers.
Feature #2: Participate in conversations
Once you click on a room in the previous screen, the app takes you to the room and you'll be able to listen to ongoing conversations. On the top, you see the panel members, followed by people who are part of the audience. If you're a host, or invited as a speaker or moderator, you'll be able to speak as well.
Feature #3: Leave a conversation
Both these apps offer the user to leave the room anytime at the click of a button. This will have the effect of returning you to the home screen.
That's pretty much the core features! Plenty of these apps also have additional features common in social media platforms, like exchanging chat messages, subscribing to topics that interest you, and also following other users and clubs.
Using your own voice to interact with people just feels very natural. This explains why during COVID-19 lockdowns, friends, relatives and colleagues started relying on apps like Zoom, Whatsapp Video, and Google Hangouts for connecting from time to time.
While the question of whether social audio apps will continue to remain popular remains to be seen, it's very interesting to learn the underlying engineering that makes them work! How does Clubhouse, for example, enable users to join rooms and socialize using their voice?
So if you're a developer and feel the need to scratch that programming itch, keep reading!
What you will build
In this tutorial, you'll learn to clone some of the most salient features of Clubhouse. You won't focus on scale or performance right now, but you'll develop an understanding of what core technologies make applications like these possible. At the end of this tutorial series, you will build the following features:
- You'll attempt to mimic the Hallways feature in the Clubhouse app, where users are able to browse and discover active rooms.
- Selecting a room to join it, where you can see all the other participants in the room and participate in the conversation.
This will be a programming tutorial mainly focused on functionality, so your focus will not be much on web design, (though you're welcome to do so if you wish!).
What you will learn
#1: Relational Database Design
You'll learn how to design and architecture a relational database system for representing the most essential data entities required for this app.
#2: Real Time Communication
You'll learn how to implement WebRTC to enable real time communication of audio, to enable live conversations within a club room.
Technologies that you will learn and use
- On the front-end you'll use HTML, CSS and JavaScript to build the UI. You'll also use PeerJS — a library that allows you to easily for implement Real Time Transmission of audio/video in WebRTC.
- On the back-end, you'll use NodeJS/Express for building web server and APIs. You'll also use Socket.io for implementing a signalling-server as is required in WebRTC based applications — of which you'll learn more about later.
- You'll learn to use PostgreSQL as your database. It's fast, extremely extensible to a wide array of needs (including hyperlocal business applications), has support for both SQL and NO-SQL data models, and is scalable.
Prerequisites
- It will be helpful to have at least a theoretical understanding of how relational database systems work.
- Some knowledge of JavaScript and NodeJS is also useful.
Let's get started!
In this tutorial, you'll learn how to construct an efficient relational database model for organizing data and effective querying. You'll then build an API service that allows a client to retrieve information, which can then be displayed on a user interface.
Go to this link, and follow the instructions to download the repository and install all the necessary project dependencies. It contains some boilerplate code and helper methods for you to use and build the rest of the project as you go about the tutorial.
Designing the Entity-Relationship Model
An entity can be thought of as a category of data or information in a system, which is of particular interest to us. When you design an entity-relationship model for a system, you identify entities and their relationships with each other, which can be stored in a database.
Broadly speaking, your app has the following entities:
- Users: Users can join a room and participate in an ongoing conversation.
- Clubs: A club will usually be centered around a particular topic, and can consist of multiple live sessions or "rooms".
- Rooms: A room is a live discussion where participants can tune in and listen, or speak too. A club can have multiple rooms.
- Topics: A club can belong to a certain category, like Technology or Geopolitics. Any rooms created within a club will have conversations related to this topic. As such, there can be multiple clubs around a certain theme or topic.
We could imagine more entities in the system but these will suffice for now. If you built data models for each of the above entities, what would they look like? Take a moment to think!
Here's how our Entity Relationship Diagram could look like:
Entity Relationship Diagram
What we have here is an Entity-Relationship Diagram, or ERD for short. As the name suggests, it helps us visualize the various entities in a system and their relationships with each other. The above diagram is obviously a very simplistic version of how various entities within the actual Clubhouse app might look like and relate with each other. However, it still serves as a useful starting point!
Let's take a closer look at some of these relationships:
- A club has exactly one founder. This is a one-to-one relationship.
- A club has many rooms. That's a one-to-many relationship. A room however, may or may not be associated with a club.
Similarly, you can identify some more relationships:
- A club can have many followers (users)
- A user can follow many clubs
So there exists a many-to-many relationship between a user and a club! In ERD and relational-database terminology, this relationship is referred to as an associative entity, and is implemented by introducing an associative table or, cross-reference table. So in-order to denote this relationship, we'll have a third table which you can name followers, indicating the relationship:
Similarly, we have a relationship between a user and a room:
- A room can have many participants (users)
- A user could be a participant at many rooms
So there exists a many-to-many relationship between users and rooms. Just like before, you'll use a cross-reference table participants in-order to map out this relationship:
Designing the Schema in PostgreSQL
PostgreSQL is amongst the most popular relational databases used all over the world, being in the top 2 widely used databases for 2 years in a row in 2019 and 2020. It has a very high compliance with SQL standards, supports a wide range of native data types (including custom types as well as JSON!), and is extremely extensible.
Let's begin creating the above tables using the psql prompt. Once you've fired up the psql terminal, run the following command to create a new database:
CREATE DATABASE clubhouse;
Next, switch to this database so that the subsequent commands apply to this database by typing \c clubhouse;. Here are some commonly used queries:
-
\l: List available databases. -
\dt: List existing tables. -
SELECT * FROM tablename: Will print all rows and columns in a table. -
DROP TABLE tablename: Will delete the table. -
DROP DATABASE databasename: Deletes the database.
Next, you'll start by adding the following tables, one by one:
CREATE TABLE topics (
id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description VARCHAR(500)
);
CREATE TABLE users (
id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(50) NOT NULL UNIQUE,
name VARCHAR(100),
bio VARCHAR(350),
join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE clubs (
id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
founder_id BIGINT NOT NULL references users(id),
name VARCHAR(100) NOT NULL,
topic_id BIGINT NOT NULL,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (topic_id) REFERENCES topics(id)
);
CREATE TABLE rooms (
id BIGINT GENERATED BY default AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL,
club_id BIGINT references clubs(id) ON UPDATE CASCADE ON DELETE CASCADE,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE followers (
club_id BIGINT REFERENCES clubs(id) NOT NULL,
user_id BIGINT REFERENCES users(id) NOT NULL,
UNIQUE(club_id, user_id),
PRIMARY KEY (club_id, user_id),
FOREIGN KEY (club_id) REFERENCES clubs(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE TABLE participants (
room_id BIGINT REFERENCES rooms(id) NOT NULL,
user_id BIGINT REFERENCES users(id) NOT NULL,
role VARCHAR(10) DEFAULT 'AUDIENCE' NOT NULL CHECK(role IN ('HOST', 'MODERATOR', 'SPEAKER', 'AUDIENCE')),
join_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
peer_id VARCHAR(50),
UNIQUE(room_id, user_id),
PRIMARY KEY (room_id, user_id),
FOREIGN KEY (room_id) REFERENCES rooms(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE
);
A participant could have one of the following roles:
- Host — This would be those users who created the room and are in the main speaker panel. They also have the highest privileges within that room, including moderator privileges.
- A moderator is also present in the speaker panel and can invite other speakers to the room.
- A speaker is also part of the speaker panel, but cannot add or invite other users.
- Finally, the other participants will be part of the audience. They can only listen, but not speak with other users.
Once you've created the above tables, you'll also add some sample data. Run the commands from the models/db-schema.txt file in your repository to add some sample data.
Once you're done, you can run SELECT * FROM users; for the users table and the rest of the tables too in-order to verify if your data has been imported successfully. I highly recommend you to take a few moments to run the above query for the rest of the tables and study the data that's stored in them.
Querying for information
We can imagine writing the following queries for our application:
- A query to retrieve a list of all available rooms and their information. It would be useful to display this for a user who haven't logged in, so that they may get a glimpse of the kind of conversations that occur with the app!
- Similarly, a user that has logged in would be interested to browse through the rooms for those clubs that they follow.
- And finally when a user joins a room, we'll need a query that retrieves the club-name, room-name and the information about all the participants in the room which we can display in the UI.
Query #1: List all rooms
This basically includes:
- All the rooms that are being hosted within a club; AND
- All the rooms that aren't within any specific club
Here's a screenshot of the home screen when you log into clubhouse:
The screen contains a scrollable list of rooms, displayed within a card. If you notice closely, each room-card has the following information:
- If the room is being hosted within a club, the name of the club appears on the top (with a green icon next to it).
- The names of some of the participants within the room. On Clubhouse, these are usually the names of the hosts, speakers or moderators within the room.
- On the bottom, you also see the overall number of room participants, and also the number of people in the speaker panel.
Basically, our query needs to give us data in the following format:
Let's break it down:
Step 1: Extract the room ID, room name and the club name that the room is a part of
SELECT
rooms.id,
rooms.name,
clubs.name AS club_name
FROM clubs
RIGHT JOIN rooms
ON rooms.club_id = clubs.id
ORDER BY rooms.date DESC
LIMIT 100;
Running this query in your psql prompt will give you the following result:
How does this work?
- We mention the names of the columns that we're interested in in the beginning. Since our room table and our clubs table both have a column called name, we can rename either of these columns using the
ASclause to assign an alias. - In the FROM clause, you mention the table or tables from which you can extract the info of one or several of the columns that we mentioned in the beginning.
- And finally, we do a RIGHT JOIN on the rooms table. A JOIN creates an link between two tables based on a given condition. This condition is given within the
ONclause. - The ORDER BY clause allows you to determine in what order should the query results be returned in. In social networking apps, the latest (or most recent) information is seen in the top, we'll retrieve the rooms in descending order of their creation date.
- The LIMIT clause places an upper limit on how many rows should be returned.
Types of JOINS in a relational database management system:
- An
INNER JOINreturns only the common elements between both the tables — in other words, the intersection where they match on the joined column given by theONclause. - A
LEFT JOINjoins the rows from the first (LEFT) table to the second (RIGHT) table. But unlike the INNER JOIN, it will also include the rows from the LEFT table even if they don't match the condition. - A
RIGHT JOINis similar to the LEFT JOIN, except that it will include rows from the second (RIGHT) table that don't match the condition.
Why did we apply a RIGHT JOIN?
By selecting the clubs table and applying a RIGHT JOIN on the rooms table, we create an intersection between these two tables. This way, each row within our query result will contain room information and its associated club name too. But we also want to include those rows (rooms) that aren't associated with any club, and hence we specifically apply a RIGHT JOIN.
Step #2: Retrive the information of the panel participants for each room
The participants table contains the information about the participants within a room. It has the following foreign keys:
-
room_idrefers to the rooms table -
user_idrefers to the users table
So we'll need to add a subquery that:
- Selects the column names that we're interested in: the participant's user ID, their name and their type.
- Applies an INNER JOIN on the users table, so that each participant row in the subquery is associated with its respective participant name from the users table.
- Apply a
WHEREclause to match the participant rows with their respective rooms - And a filter to return only those participants that are either a host, moderator or speaker.
SELECT
rooms.id,
rooms.name,
clubs.name AS club_name,
(
SELECT json_agg(participant)
FROM (
SELECT participants.user_id, participants.role, users.name
FROM participants
INNER JOIN users
ON participants.user_id = users.id
WHERE participants.room_id = rooms.id
AND participants.role IN ('HOST', 'MODERATOR', 'SPEAKER')
LIMIT 6
) participant
) AS panel
FROM clubs
RIGHT JOIN rooms
ON rooms.club_id = clubs.id
INNER JOIN participants
ON participants.room_id = rooms.id
ORDER BY rooms.date DESC
LIMIT 100;
The json_agg function is an aggregate function provided by POSTGRESQL that groups the results of the inner subquery into a JSON array.
Step #3: Retrive the count of the participants in the panel, and the total number of participants in the room
For this, we'll apply a very similar query to the previous subquery, except this time we don't need to apply an INNER JOIN on the users table.
For fetching the number of participants on the panel list, we just need to select those rows that match the participant row to its associated room id, and apply a filter on the participant-types. Then all you need to do is return a COUNT of those rows.
So here's how the final query looks like:
SELECT
rooms.id,
rooms.name,
clubs.name AS club_name,
(
SELECT json_agg(participant)
FROM (
SELECT participants.user_id, participants.role, users.name
FROM participants
INNER JOIN users
ON participants.user_id = users.id
WHERE participants.room_id = rooms.id
AND participants.role IN ('HOST', 'MODERATOR', 'SPEAKER')
LIMIT 6
) participant
) AS panel,
COUNT(participants.user_id) FILTER (
WHERE participants.room_id = rooms.id
AND participants.role NOT IN ('AUDIENCE')
) as panel_count,
COUNT(participants.user_id) FILTER (
WHERE participants.room_id = rooms.id
) as participant_countFROM clubs
RIGHT JOIN rooms
ON rooms.club_id = clubs.id
INNER JOIN participants
ON participants.room_id = rooms.id
GROUP BY rooms.id, rooms.name, clubs.name
ORDER BY rooms.date DESC
LIMIT 100;
Whew, that was a lot! If you've followed the tutorial this far, I commend you!
Let's move on to the next query —
Query #2: List rooms for those clubs that the user follows
Fetching all room information is great. But wouldn't it be better if we tailored the room discovery feature for each user just a little bit?
The following query will return the room list information for the user Elon, whose user_id is 5:
SELECT
rooms.id,
rooms.name,
clubs.name AS club_name,
(
SELECT json_agg(participant)
FROM (
SELECT participants.user_id, participants.role, users.name
FROM participants
INNER JOIN users
ON participants.user_id = users.id
WHERE participants.room_id = rooms.id
AND participants.role != 'AUDIENCE'
LIMIT 6
) participant
) AS panel,
COUNT(participants.user_id) FILTER (
WHERE participants.room_id = rooms.id
AND participants.role != 'AUDIENCE'
) as panel_count,
COUNT(participants.user_id) FILTER (
WHERE participants.room_id = rooms.id
) as participant_count
FROM clubs
RIGHT JOIN rooms
ON rooms.club_id = clubs.id
INNER JOIN participants
ON participants.room_id = rooms.id
LEFT JOIN followers
ON followers.club_id = clubs.id
WHERE followers.user_id = 5
OR rooms.club_id IS NULL
GROUP BY rooms.id, rooms.name, clubs.name
ORDER BY rooms.date DESC
LIMIT 100;
The above query is almost exactly the same as the previous one, except for a few key differences:
...
LEFT JOIN followers
ON followers.club_id = clubs.id
WHERE followers.user_id = 5
OR rooms.club_id IS NULL
...
We do a LEFT JOIN on the followers table, and apply a WHERE clause so that we get those rows where elon is a follower. But because of this third join, it also has the effect of removing the rooms that don't belong to any club, so we apply one more condition using the OR clause.
And here are the results! —
As you may observe, the previous query returned 12 rows, this query returned only 8 rows — which makes sense, given that this user isn't following all the available clubs in the app.
Query #3: Retrieve information for a single room
When you tap or click on a room in clubhouse, the app opens up the room and you're officially a participant. You're able to see the users in the speaker list, and also other participants who aren't on the panel, but are simply listening.
So next, you'll write a query that for a given room ID retrieves the following room information:
- Name of the club
- Name of the room
- The names of speakers on the panel
- The names of the rest of the participants in the audience
Here's how the resulting query response might look like:
Let's write a query that fetches the above details for the room id 3:
SELECT
rooms.id,
rooms.name,
clubs.name AS club_name,
(
SELECT json_agg(participant)
FROM (
SELECT
participants.user_id, participants.role, participants.peer_id, users.name
FROM participants, users
WHERE participants.user_id = users.id
AND participants.room_id = rooms.id
AND participants.role IN ('HOST', 'MODERATOR','SPEAKER')
) participant
) AS panel,
(
SELECT json_agg(participant)
FROM (
SELECT participants.user_id, participants.role, participants.peer_id, users.name
FROM participants, users
WHERE participants.user_id = users.id
AND participants.room_id = rooms.id
AND participants.role = 'AUDIENCE'
) participant
) AS audience
FROM rooms
LEFT JOIN clubs
ON rooms.club_id = clubs.id
WHERE rooms.id = 3;
Since the first (left) table is rooms and the second (right) table is clubs, we apply a LEFT JOIN. This makes sure that the row from the rooms table isn't discarded even if it isn't associated with any club.
Building a web service
Perfect! Now let's try doing the same, except this time you'll create APIs that uses the above queries to return the information we seek.
A brief explanation about some of the packages that you'll use:
- Express is a web framework for NodeJS. It has lots of APIs, utilities and middlewares in its ecosystem to help you build your application.
-
pg is a NodeJS middleware for PostgreSQL. It helps your Node app talk to your database. You'll use this to run the same commands that you ran using the
psqlterminal, except this time it will be within your web server. - handlebars is a templating engine. You can use it to do parsing and apply conditional logic within html templates on the server before serving them to the client.
Feature #1: List and display rooms
Backend
Let's begin by first creating functions for each one of our previous queries.
Inside models/index.js, you'll add code for the following functions:
-
getRooms(limit): Returns all available rooms -
getRoomsForUser(limit): Returns rooms for those clubs that the user follows -
getRoomDetails(roomId): Returns information for the given room id
Next, write a function getUserByUsername(username) that fetches information of a user using their username. Add the following in models/users.js:
async function getUserByUsername(username) {
try {
const text = `
SELECT * from users
WHERE username = $1
`;
const values = [username];
const res = await db.query(text, values);
return res.rows[0];
} catch (err) {
console.log(err.stack);
}
}
APIs
Next, you'll write APIs that utilize the above functions and expose them in a web service:
-
/rooms: A GET request to this route will return all available rooms. If you pass a query param?userId=6, then the API should return only those rooms of those clubs that the user is a member of. -
/rooms/:roomId: A GET request to this route will return the participant information for a particular room.
Add the following code inside routes/rooms.js:
router.get('/:roomId', async function(req, res, next) {
const room = await db.getRoomDetails(req.params.roomId);
if (room) {
res.json({
room: {
id: room.id,
name: room.name,
club_name: room.club_name,
audience: room.audience ? room.audience : [],
panel: room.panel ? room.panel : [],
}
});
} else {
res.status(404).json({
message: "Room does not exist"
});
}
});
router.get('/', async function(req, res, next) {
let rooms = [];
const userId = req.query.userId;
if (req.query.userId) {
rooms = await db.getRoomsForUser(userId);
} else {
rooms = await db.getRooms();
}
res.json({
rooms,
});
});
Finally, run npm start in your project root, and open the following URLs on your browser:
- http://localhost:3000/rooms: This will return a list of all available rooms.
- http://localhost:3000/rooms?userId=1: Returns all rooms for a specific user.
- http://localhost:3000/rooms/4: Returns information for a single room.
Frontend
Going to http://localhost:3000 displays an empty page. First, we'll add a fake login, so when you type http://localhost:3000/username=rick, it displays a message greeting the user Rick Sanchez.
On your routes/index.js, add the following code:
/* GET home page. */
router.get('/', async function(req, res, next) {
const username = req.query.username;
let userInfo = {};
if (username) {
const user = await db.getUserByUsername(username);
if (user) {
userInfo = {
username: user.username,
name: user.name,
userId: user.id,
};
}
}
res.render('index', userInfo);
});
In the above code, we check for the request query params' value for username. Then, we use the username to extract the user information, and interpolate these values for the HTML document inside views/index.hbs. The interpolation is done by handlebars.
Now when you load the http://localhost:3000/username=rick, it will display the message "Howdy, Rick Sanchez" on the header.
Next, let's use our APIs to fetch a list of rooms and display them on the UI
- First, extract the user details in our HTML file and save them in a variable. Add the following code inside public/modules/index.js:
const user = document.getElementById('user');
updateUserInfo({
id: user.getAttribute('data-userId'),
name: user.getAttribute('data-name'),
username: user.getAttribute('data-username')
});
- Add the following function inside models/api.js that makes a fetch request to our endpoint to fetch the list of rooms, and stores them in a
ROOMSvariable:
export async function fetchRooms(userId) {
const url = userId ? `/rooms?userId=${userId}` : '/rooms';
const response = await fetch(url, {
method: 'GET'
});
if (response.ok) {
const result = await response.json();
updateRooms(result.rooms);
return result;
} else {
return Promise.reject(Error(response.statusText));
}
}
Now, you'll use this function to fetch the list of rooms, and utilize a helper method to display them in the UI. Add the following code in modules/index.js:
async function displayRooms() {
let result;
if (USER_INFO.id) {
result = await fetchRoomsForUser(USER_INFO.id);
} else {
result = await fetchRooms();
}
renderRooms(ROOMS);
}
And we're done!
You should see the following page when you head over to http://localhost:3000/?username-rick:
Next Steps
In the next tutorial, you'll learn how to implement the next feature — joining a room and interacting with other participants via audio!
If you liked this tutorial or have any questions, do leave a like or a comment!
Special thanks to Prashanth Molakala for helping with optimizing the postgresql queries 🙏
Top comments (0)