Access Control & Event/Notification Schemas
You'll learn to
- -Design row-level access control (who can see or edit which rows) directly into a schema
- -Model an event-driven notification schema that stays decoupled from the actions that trigger it
This closing chapter of the module ties together two practical concerns that show up in almost every real application: controlling who can see or modify a given row, and generating notifications from events without tightly coupling every action to the notification logic itself - the schema-design mirror of the Observer pattern from Phase 1.
Modeling Row-Level Access Control
A post with a "friends only" visibility setting needs the schema to answer "can user X see this post?" efficiently. The straightforward approach: a visibility enum column on the post itself (public, friends_only, private), combined with a query that joins against the friendships table from earlier in this module when visibility 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))
));For more granular sharing (a post visible to a specific, custom list of people, not just "friends"), a junction table (post_shares with post_id and shared_with_user_id) generalizes the pattern - exactly the same shape as the many-to-many relationships covered earlier in this module, just applied to permissions instead of participation.
Event and Notification Schemas
A notification needs to record what happened, who it is for, and whether it has been seen - and critically, it should be triggered by an event, not embedded as a side effect scattered inside every action that could produce one (a like, a comment, a follow), mirroring the Observer pattern's core lesson from Phase 1 that a producer should not need to know every consumer of an event it raises.
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)
);Notice subject_type and subject_id reuse the polymorphic-association technique from earlier in this module, since a notification can reasonably point at a post, a comment, or a follow relationship depending on notification_type - the same trade-off (simplicity vs. enforced referential integrity) applies here too, and the same guidance holds: acceptable for a lower-stakes, frequently-extended table like this one.
A single, generic notifications table handles every current and future notification type without a schema change - a new notification_type value is just a new string, not a new table or migration, echoing the Open/Closed reasoning 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.