Access Control & Event/Notification Schemas
You'll learn to
- -Design who-can-see-what into a schema with a visibility column and, for custom sharing, a junction table
- -Model notifications as events recorded in one generic table, decoupled from what caused them
- -Reuse the polymorphic subject idea, and know the trade-off it brings
This closing chapter of the module covers two practical needs that appear in almost every real application: controlling who can see or change a given row, and creating notifications from events without tying every action to the notification code. The second is the schema-design mirror of the Observer pattern from Phase 1.
An Analogy: The Guest List and the Mailbox
A party host keeps a guest list at the door: the door checks the list, and the rooms stay the same. That is access control, where the data is separate from the rule about who may see it. Separately, when something happens at the party, a note goes into each interested guest's mailbox, written by the event and not by each person who triggered it. That is the notifications table.
Modeling Row-Level Access Control
Row-level means the rule applies to individual rows, not a whole table. A post marked "friends only" needs the schema to answer "can user X see this post?" efficiently. The straightforward approach is a visibility column on the post (public, friends_only, private), combined with a read query that checks the friendships table from earlier in this module when the post is friends only.
CREATE TABLE posts (
id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL,
body TEXT NOT NULL,
visibility TEXT NOT NULL DEFAULT 'public' -- 'public', 'friends_only', 'private'
);
-- a viewer can see a post if it's public, their own, or friends_only + they're friends
SELECT p.* FROM posts p
WHERE p.visibility = 'public'
OR p.author_id = :viewer_id
OR (p.visibility = 'friends_only' AND EXISTS (
SELECT 1 FROM friendships f
WHERE (f.user_a_id = LEAST(p.author_id, :viewer_id) AND f.user_b_id = GREATEST(p.author_id, :viewer_id))
));The query reads as three ways in: the post is public, or it is the viewer's own, or it is friends-only and the viewer is a friend of the author. For finer sharing, such as a post visible to a specific list of people, a junction table (post_shares with post_id and shared_with_user_id) generalizes the idea. It is the same shape as the many-to-many tables earlier in this module, applied to permissions instead of participation.
Event and Notification Schemas
A notification must record what happened, who it is for, and whether it has been seen. Crucially, it should be created in response to an event, not scattered as a side effect inside every action that could cause one (a like, a comment, a follow). That mirrors the Observer lesson: the code that raises an event should not need to know every consumer of it.
CREATE TABLE notifications (
id INTEGER PRIMARY KEY,
recipient_id INTEGER NOT NULL,
notification_type TEXT NOT NULL, -- 'new_follower', 'post_liked', 'comment_reply'
actor_id INTEGER NOT NULL, -- who caused this notification
subject_type TEXT, -- polymorphic reference, like the Comments chapter
subject_id INTEGER,
is_read BOOLEAN NOT NULL DEFAULT FALSE,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (recipient_id) REFERENCES users(id),
FOREIGN KEY (actor_id) REFERENCES users(id)
);Suppose Alice follows Bob. One row is written with recipient Bob, type new_follower and actor Alice. When Cy likes Bob's post, another row is written with type post_liked, actor Cy, and the post as the subject. Both live in one table.
Notice that subject_type and subject_id reuse the polymorphic technique from earlier in this module. A notification can point at a post, a comment or a follow, depending on its type. The same trade-off applies, simplicity against enforced integrity, and the same guidance holds: it is acceptable for a lower-stakes, frequently extended table like this.
A single generic notifications table handles every current and future notification type with no schema change. A new type is just a new string value, not a new table or migration, which is the Open/Closed idea from Phase 1 applied to schema evolution.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design Privacy & Access Control and the Notification Engine in the LLD Lab's Social Network act.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!