Complete database schema for an Instagram-like visual social platform featuring user profiles, photo and video posts with multiple media, Instagram Stories with viewer tracking, Reels short-form video, comment threads, likes, hashtag discovery, follow relationships, close friends lists, direct messaging with threads, and highlight collections.
This schema splits an account into users and profiles. users holds what the system needs to let someone in — credentials, phone, the is_private flag; profiles holds what other people see, including the follower, following and post counts. The counts are stored rather than derived because they are read on every profile view and written comparatively rarely.
Content is not one table. posts is the grid, stories expires after a day, and reels is its own table rather than a flag on posts. They are separated because almost nothing about them is shared: a story has an expiry and a viewer list, a reel has a play count, and a post has a carousel of media and a location.
The follow graph is a single directional table with an is_pending flag. A follow of a public account is created already accepted; a follow of a private one sits pending until approved. Both are the same row, so the request and the relationship never drift apart.
They look similar enough to merge — both are a user's media with a caption and a like count. But a reel has a play count, a fixed vertical aspect, no carousel and no location, while a post has none of the first and all of the rest. Merging them means a table where half the columns are null depending on a type flag, and every feed query has to filter on that flag. Separate tables cost a union on the rare screen that shows both.
follower_count, post_count, like_count and comment_count could all be derived. They are stored because a profile page reads four of them and writes none, and a count over a follow table with millions of rows is not a query you want on a page load. The cost is that the counter and the rows can drift, so they need a periodic reconciliation — which is cheaper than being correct on every read.
story_views records who watched, not how many, because the owner is shown the list of viewers. A counter would answer the wrong question, and the table is naturally bounded — the story expires and the rows can go with it.
A request to follow a private account could be its own table, moved on approval. Keeping it as is_pending on the follow row means approving is one update rather than a delete and an insert, and a rejected request is a delete either way. It also makes 'is this person following me' a single lookup instead of checking two places.