Feed
Lesson 13 · about 15 minutes · one table, one RPC, one edge function, and why the edge function is thinner than it looks.
The feed is V0: a global, unranked, newest-first list of trade cards and market events. Nothing writes to it from the app. Rows are produced by ingestion paths you have already met, and the feed function's job is to validate a cursor, call get_feed, and reshape rows. Almost every rule lives in SQL, which is why the pgTAP files outnumber the Deno tests.
Who writes feed_items
| event_type | Writer | Dedup key |
|---|---|---|
trade_buy, trade_sell, trade_short_open, trade_short_close | snaptrade.process_activities, in the same transaction as realized_trades | (event_type, source_id, account_id, ticker). Scoped by account, not user, because SnapTrade activity ids are unique per account. migrations/20260812192023 · 20260812163448 |
news, earnings_call, sec_filing | Triggers on the news, Events and SEC filing tables | (event_type, source_id, ticker) where actor_user_id is null. One article tagged to three tickers is three rows. migrations/20260813105432 · 20260922100000 |
Two partial unique indexes rather than one, because Postgres treats NULL as distinct in a unique index: folding a nullable account_id into the market-event key would silently stop deduping market events. docs/FEED_ITEMS.md, "Schema at a glance"
Read path: GET /feed
AuthMiddleware. Origin is required. The middleware only verifies the JWT signature, so the app's publishable key passes it while signed out; the handler's getUserId is what insists on a real user. feed/index.ts:17-25limit and the cursor, strictly. limit is 1 to 50, not clamped: a bad client should be visible. A half-supplied cursor is a 400, because get_feed would coalesce the missing half and silently re-serve page one. feed/handlers/getFeed.ts:77-128get_feed(limit + 1, afterEventTime, afterId) with the anon client and the user's JWT. The extra row decides hasMore. MAX_LIMIT is 50 so that limit + 1 never hits the RPC's own clamp of 100, which would make the feed look ended at max page size. getFeed.ts:28-37get_feed. Keyset on (event_time desc, id desc), starting at now() rather than infinity so the seek does not walk past pending earnings calls. Bounded to the last 7 days. Joins public_profiles for the actor and, for open positions, composes a live block from open_lots and the current quote, with the short-direction sign handled in SQL. Invoker rights on purpose so the table's RLS still applies. migrations/20260916210000_earnings_call_feed_window.sql:189-281feed_items. Select for authenticated requires actor_user_id is null or is_active_user(actor_user_id), and since 20260916210000 also hides rows timed after now(). No user write policy exists at all. migrations/20260811203535:45-56 · 20260812163100:54toFeedItem folds the flat actor_* columns into an object, and spreads live only when present, so a news card never carries live: null. The result is a discriminated union on event_type. getFeed.ts:134-160 · feed/types/feedTypes.tsOFFSET 40, a row inserted between page one and page two shifts everything down and the client sees a duplicate. Keyset on (event_time, id) asks for "rows strictly older than the last one I saw", which is stable no matter what lands above it. The index feed_items_event_time_id_idx exists for exactly this predicate.Write path: captions
Every trade card is public from the moment it is written; feed_items_always_public_check refuses is_public = false. So POST /feed/post does not publish, it sets or clears a caption. The handler is split like the account handlers: postTradeCore.ts takes injected moderate, getUserId and callRpc, and postTrade.ts wires the real OpenAI client and the service-role post_trade RPC. feed/handlers/postTradeCore.ts:27-36 · postTrade.ts:25-45
- Caption rules are checked twice on purpose. The core collapses newline runs, trims, and counts code points (an emoji is one, not two) against 300; the DB CHECK
feed_items_caption_format_checkrepeats the rule so no other writer can bypass it. postTradeCore.ts:78-90 - Moderation is fail-closed: no verdict means 503 and no write.
- Errors map by SQLSTATE, never by message text.
P0002becomes 404 for "not yours, not a trade card, or gone", one answer so ids cannot be probed.23514becomes 400, with a friendly message on unpost so the constraint name does not leak. postTradeCore.ts:117-147 /feed/unpostis kept but always refused by the constraint. Restoring per-trade privacy means dropping the constraint and the default, not touching the read side. docs/FEED_ITEMS.md, top note
process_activities? A separate feed adapter would need its own trigger or cron, its own idempotency and its own failure mode, and could disagree with realized_trades about the FIFO match. Writing the card in the same transaction guarantees a card exists if and only if a realized trade does, with identical numbers. The payload's realized_pnl is already direction-corrected for shorts; a client that recomputes it inverts every short.Check yourself
Do this in the repo
- Run the two feed tests that need no stack, then the pgTAP set:
cd src && deno test --allow-all supabase/tests/functions/feed-to-feed-item-test.ts supabase/tests/functions/feed-post-trade-test.ts npx supabase test db 2>&1 | grep -E 'feed' - With the stack up and a user JWT, page the feed twice and confirm the second page's first row is strictly older than the cursor you sent:
curl -s "http://localhost:54321/functions/v1/feed?limit=2" -H "apikey: $ANON_KEY" -H "Authorization: Bearer $USER_JWT" -H 'Origin: http://localhost:5173' | jq .pagination - Read the
liveblock inget_feedand write down the sign rule for shorts in one sentence.
Primary source
In-repo docs/FEED_ITEMS.md is the source of truth for payload shapes. For the pagination technique, Use The Index, Luke: No Offset.
Next lesson: News CMS, the other big producer of feed rows. Back: profiles and users, brokerage and SnapTrade.
Ask your teacher in the terminal: "trace a news article from Massive to a feed row" crosses three lessons and is worth doing aloud.