GetStream reaction dedup via Postgres cache (sh-backend)

20260516-getstream-reaction-dedup.md

GetStream Reaction Dedup via Postgres Cache

Date: 2026-05-16
Branch: develop
Commit: c046d4ae

---

Problem

Every call to POST /v1/posts/{post_id}/reactions fired 2–3 GetStream API calls:

1. reactions.filter(activity_id, user_id) — scan for existing reactions by this user
2. reactions.delete(reaction["id"]) — for each existing reaction found (replace case)
3. reactions.add(...) — the actual write

Every call to DELETE /v1/reactions/{reaction_id} fired:

1. reactions.get(reaction_id) — read to verify ownership and fetch post_id
2. reactions.delete(reaction_id) — the actual delete

There was no local record of reactions, so every mutation required asking GetStream first.

---

Solution

New table: post_reactions

One row per (post_id, profile_id) — enforced by a unique constraint.
Caches the stream_reaction_id so we can delete by ID without a prior read.

CREATE TABLE post_reactions (
    id                 SERIAL PRIMARY KEY,
    post_id            INTEGER NOT NULL REFERENCES posts(id)    ON DELETE CASCADE,
    profile_id         INTEGER NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
    kind               VARCHAR(50)  NOT NULL,
    stream_reaction_id VARCHAR(200) NOT NULL,
    created_at         TIMESTAMP    NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_post_reaction_post_profile UNIQUE (post_id, profile_id)
);

New add-reaction flow

Step	Before	After
Check for existing reaction	reactions.filter(activity_id, user_id) → GetStream read	SELECT … WHERE post_id=? AND profile_id=? → Postgres read
Delete old reaction (replace)	reactions.delete(id) per result	reactions.delete(stream_reaction_id) — single targeted delete, ID already known
Write new reaction	reactions.add(...)	unchanged
Persist cache	—	INSERT INTO post_reactions

GetStream calls: 3 → 1 (replace case) or 2 → 1 (new reaction).

New delete-reaction flow

Step	Before	After
Verify ownership + get post_id	reactions.get(reaction_id) → GetStream read	SELECT … WHERE stream_reaction_id=? AND profile_id=? → Postgres read
Delete	reactions.delete(reaction_id)	unchanged
Clean up cache	—	DELETE FROM post_reactions WHERE id=?

GetStream calls: 2 → 1.

---

Files Changed

File	Change
app/db/models.py	Added PostReaction SQLModel
app/sherpahealthy/reactions.py	Rewrote create_reaction and delete_reaction to use Postgres cache
supabase/migrations/20260516010000_add_post_reactions_table.sql	Dev/staging migration
alembic/versions/b8c9d0e1f2a3_add_post_reactions_table.py	Production migration (merges both current heads)

---

Bonus: SITEMAP_ENABLED guard

Added a SITEMAP_ENABLED (default false) feature flag.
/sitemap*.xml and /robots.txt return 401 unless the flag is true.
Set to true only in the production configmap — prevents dev/staging content from being indexed.

---

Bonus: mock_getstream autouse test fixture

Added to tests/conftest.py. Patches get_stream_client in every module that imports it and stubs find_post_activity_id. All test runs now execute zero real GetStream API calls.

Modules patched:

• app.sherpahealthy.feeds (source)
• app.sherpahealthy.reactions
• app.sherpahealthy.comments
• app.sherpahealthy.posts.comments
• app.sherpahealthy.posts.main
• app.sherpahealthy.full_posts
• app.sherpahealthy.posts.feeds_http

---

Trade-offs & Notes

• get_post_reactions (the public read endpoint) still queries GetStream directly — it returns full reaction lists with profile data and is not on the hot write path.
• post_reactions has ON DELETE CASCADE on both FKs, so cleanup on user/post deletion is automatic; no explicit cleanup needed in application code.
• The unique constraint prevents duplicate rows even under concurrent requests.
• stream_reaction_id is sourced from GetStream's reactions.add() response and stored as-is.