-- Post mentions — users tagged on a post (separate from the locked caption).
-- Membership table; the count is small and edited rarely, so it's derived at
-- read time (no denormalized counter / worker needed).
CREATE TABLE IF NOT EXISTS post_mentions (
    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)
);

-- List a post's mentions (insertion order) + hydrate mention counts across a feed.
CREATE INDEX IF NOT EXISTS post_mentions_post_idx ON post_mentions (post_id, created_at);
CREATE INDEX IF NOT EXISTS post_mentions_user_idx ON post_mentions (user_id);