27 lines
1.3 KiB
SQL
27 lines
1.3 KiB
SQL
-- Post likes.
|
|
--
|
|
-- `post_likes` is the membership table (one row per user-per-post like),
|
|
-- written synchronously so a viewer's like state and the "liked by" list are
|
|
-- immediately consistent. The UNIQUE(post_id, user_id) makes liking idempotent.
|
|
--
|
|
-- `posts.likes_count` is a denormalized counter that is NOT updated on every
|
|
-- like (that hot-row UPDATE doesn't scale for popular posts). Instead each
|
|
-- like/unlike nudges a per-post delta in Redis, and a background worker folds
|
|
-- the deltas into this column every 15 minutes. Reads return
|
|
-- likes_count + the still-pending Redis delta, so the displayed total is
|
|
-- always current.
|
|
ALTER TABLE posts ADD COLUMN IF NOT EXISTS likes_count INTEGER NOT NULL DEFAULT 0;
|
|
|
|
CREATE TABLE IF NOT EXISTS post_likes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (post_id, user_id)
|
|
);
|
|
|
|
-- Listing "who liked this post", newest first.
|
|
CREATE INDEX IF NOT EXISTS post_likes_post_idx ON post_likes (post_id, created_at DESC);
|
|
-- "Have I liked these posts?" — viewer-state hydration across a feed.
|
|
CREATE INDEX IF NOT EXISTS post_likes_user_idx ON post_likes (user_id);
|