Jestr (2014): The Architecture of a Social App and the Power of PostgreSQL Views

#ios #socialmedia #node #postgres

When building a modern social media application, engineers are immediately confronted with two massive hurdles: data security and performance. Social networks are inherently complex webs of relational data—users create posts, posts have comments, comments belong to users, and posts receive reactions. Fetching this data usually results in the dreaded "N+1 query problem" or highly convoluted Object-Relational Mapping (ORM) code that is slow, heavy, and prone to exposing sensitive data.

Back in 2014, during the development of the Jestr ecosystem, the engineering team tackled this problem head-on. The project was split cleanly into a native iOS frontend (jestr-app) and its backend engine (jestr-api). After consulting with an engineer at Facebook, the Jestr team implemented a brilliant, highly optimized architecture relying heavily on PostgreSQL Views and native JSON serialization.

The fundamental philosophy? No accidental data leaves the DB, and results are highly optimized for mobile client consumption.

Let's dive into the anatomy of the Jestr app and how its backend achieved an impenetrable, high-performance architecture well ahead of its time.

The Jestr iOS App: The Need for Speed on Mobile

To understand why the backend was engineered the way it was, we have to look at the frontend. In 2014, the mobile landscape was defined by devices like the iPhone 5s and the newly released iPhone 6. While powerful for their time, mobile processors and cellular networks (3G/LTE transitions) were easily overwhelmed by poorly optimized web APIs.

The Jestr iOS application was a fully-fledged visual social network. A user's timeline was dense, consisting of:

High-resolution image posts
Author profiles and avatars
Nested comment threads
Reaction counts (referred to natively as "Coolerz")
Associated hashtags

Achieving a butter-smooth 60 frames-per-second scrolling experience on a 2014 iPhone meant the client app needed to do as little "thinking" as possible. If the iOS app had to make multiple network requests to fetch a post, then fetch its author, then fetch the top comments, the UI would stutter, and the device's battery would drain rapidly. The iOS client needed a single, perfectly formatted data payload delivered instantly.

The Problem with Traditional API Architectures

In a standard web API from that era, data flow usually looked like this:

The iOS client requests a feed of posts.
The API server queries the Post table.
For each post, the API queries the User table for the author, the Comment table for recent comments, and the Like table for reactions.
The API (often written in Node.js, Python, or Ruby) loops through these rows, serializes them into JSON objects in memory, and sends them to the client.

This approach had two fatal flaws. First, doing JSON serialization at the API application layer consumed immense amounts of CPU and memory on the server, creating massive bottlenecks during traffic spikes.

Second, it created a massive security risk. If a backend developer added a sensitive field (like email_address, password_hash, or location_coordinates) to the User table, a lazy SELECT * or an improperly configured ORM serializer might accidentally send that sensitive data straight to the public iOS client.

The Facebook-Consulted Solution: Postgres Views and Native JSON

Through consultation with a Facebook employee familiar with the grueling demands of massive-scale social feeds, the Jestr team pivoted. Instead of letting the Node API handle data aggregation and serialization, they pushed that responsibility entirely down to the database layer.

PostgreSQL had just started rolling out robust native JSON support around 2012-2014. By utilizing SQL Views (like user_public_view, comment_public_view, and cool_public_view) alongside PostgreSQL's row_to_json and array_to_json functions, Jestr essentially turned the database into a secure, pre-formatted JSON engine built specifically for the iOS app.

1. Impregnable Data Security

By exclusively using "Public Views," the database acts as a strict whitelist. Let's look at how Jestr securely fetched user data:

SELECT u.id,
    u.username,
    u.profile,
    u.location,
    u.private,
    ( SELECT row_to_json(user_stats.*) AS row_to_json
        FROM ( SELECT usv.*
                FROM user_stat_view usv
                WHERE upuv.id = p.user_id) user_stats) AS "stats"
FROM "user" u
WHERE u.deleted = false;

Because the API strictly queries these public views rather than the raw base tables, no accidental data can ever leave the database. Even if a backend developer makes a mistake in the API layer, they physically do not have access to the underlying sensitive columns. The view acts as an airtight contract between the raw relational data and the iOS frontend.

2. The Mega-Query: Serializing Complex Relationships

To generate a complete, rich "Post" object for the iOS feed, the database handled the assembly of the author, a preview of comments, the "coolerz", and hashtags all in one swoop:

SELECT p.id,
    p.user_id,
    -- 1. Fetching the Author Safely
    ( SELECT row_to_json(user_profile.*) AS row_to_json
        FROM ( SELECT upuv.id, upuv.username, upuv.profile_picture
               FROM user_public_view upuv
               WHERE upuv.id = p.user_id) user_profile) AS author,
    p.images,

    -- 2. Fetching and Counting Comments
    ( SELECT row_to_json(comments.*) AS row_to_json
        FROM ( SELECT ( SELECT count(*) AS count
                        FROM comment_public_view cpuv
                        WHERE cpuv.post_id = p.id) AS count,
                 array_to_json(array_agg(row_to_json(comments_1.*))) AS data
               FROM ( SELECT cpuv.id, cpuv.created_time, cpuv.message, cpuv.from
                      FROM comment_public_view cpuv
                      LIMIT 10) comments_1) comments) AS comments,

    -- 3. Fetching and Counting "Coolerz" (Likes)
    ( SELECT row_to_json(coolerz.*) AS row_to_json
        FROM ( SELECT ( SELECT count(*) AS count
                        FROM cool_public_view copuv
                        WHERE copuv.post_id = p.id) AS count,
                 array_to_json(array_agg(row_to_json(coolerz_1.*))) AS data
               FROM  (SELECT copuv.user
                      FROM cool_public_view copuv
                      WHERE copuv.post_id = p.id
                      LIMIT 10) coolerz_1) coolerz) AS coolerz,

    -- 4. Aggregating Hashtags
    ( SELECT array_to_json(array_agg(row_to_json("row".*))) AS array_to_json
        FROM ( SELECT h.hash
               FROM hash_public_view h
               WHERE h.post_id = p.id) "row") AS hashtags,
    p.date AS created_time
FROM post p
WHERE p.deleted = false
ORDER BY p.date DESC;

Notice the sheer elegance here:

Rich Data, One Trip: The API makes exactly one query to the database. The iOS app gets a single JSON response containing everything it needs to render a fully interactive post.
Smart Previews: Subqueries limit comments and coolerz to 10. This gives the mobile UI exactly what it needs for a feed preview without over-fetching data and eating up the user's cellular data plan.
Pre-Baked JSON: By the time the data reaches the jestr-api server, it is already perfectly formatted JSON. The backend doesn't need to loop over objects; it just passes the string directly to the iOS client.

3. Caching for High-Speed Mobile Feeds

This architectural choice provided a massive performance benefit when combined with caching. Because the output of these complex queries were pure JSON strings, the backend could easily cache the exact string output of post_public_view in a memory store like Redis.

Furthermore, querying these unified data sets simplified the logic required to build the actual timeline the user sees when they open the app:

-- Feed Generation via UNION
(SELECT ppv.id, ppv.user_id, ppv.author, ppv.images, ppv.comments, ppv.coolers, ppv.hashtags, ppv.created_time 
 FROM post_public_view ppv)
UNION
(SELECT ppv.id, ppv.to_id AS "user_id", ppv.author, ppv.images, ppv.comments, ppv.coolers, ppv.hashtags, ppv.created_time 
 FROM post_public_view ppv)

By leveraging UNION on the pre-constructed public views, Jestr effortlessly combined disparate data streams into a single timeline. The iOS app asks for the feed, the API pulls the pre-compiled, pre-formatted JSON string from the cache, and the timeline loads in milliseconds.

Conclusion

The architecture behind jestr-api and jestr-app proves that sometimes, the best place to handle data logic is right where the data lives. By relying on PostgreSQL's robust view system and native JSON capabilities, Jestr's engineering team achieved an ideal trifecta back in 2014:

Absolute Security: Guaranteed by strict public views that prevented accidental data leaks to the mobile client.
Mobile Optimization: Delivering fully formed, heavily nested JSON objects in a single network request to keep the iOS app lightweight and fast.
Simplicity: The API layer became a thin, fast proxy that simply passed cached JSON strings straight to the phone.

It was a masterclass in treating the database as an active participant in the architecture rather than a dumb storage box—a paradigm shift heavily influenced by the hyper-scaled realities of giants like Facebook, and an engineering design that remains remarkably relevant today.