36 lines
1.7 KiB
SQL
36 lines
1.7 KiB
SQL
-- Per-reshare background image overrides.
|
|
--
|
|
-- Previously every viewer of a reshare saw the canonical post_images row —
|
|
-- so when either the original author or any resharer "Updated background"
|
|
-- the change leaked across all timelines that referenced the post. This
|
|
-- table separates "the post's background" from "this user's reshare of
|
|
-- the post's background": authoring stays in post_images, resharing
|
|
-- stores its own snapshot here.
|
|
--
|
|
-- At reshare time we copy the post's current post_images into this table
|
|
-- so the resharer's feed renders identically to the author's. After that
|
|
-- moment the two are independent — the resharer's background lives only
|
|
-- here, the original author's lives only in post_images, and changes to
|
|
-- one never touch the other.
|
|
--
|
|
-- Cascade: the FK targets the (post_id, user_id) primary key on
|
|
-- post_reshares. Every path that removes a reshare relationship (the
|
|
-- author deletes the post, the resharer un-reshares, either user is
|
|
-- deleted) drops the post_reshares row, which cascades the overrides
|
|
-- away. The user's stated rule — "should not be in sync except deletion"
|
|
-- — is preserved end-to-end without any extra application code.
|
|
|
|
CREATE TABLE IF NOT EXISTS post_reshare_images (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
post_id UUID NOT NULL,
|
|
user_id UUID NOT NULL,
|
|
url TEXT NOT NULL,
|
|
position INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
FOREIGN KEY (post_id, user_id)
|
|
REFERENCES post_reshares (post_id, user_id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_post_reshare_images_lookup
|
|
ON post_reshare_images (post_id, user_id, position);
|