Alertz

A group stock watchlist where friends post picks with up to three support-level entry prices and position sizes. A Supabase Edge Function runs hourly via pg_cron (Monday to Saturday, within each exchange's trading hours), fetches quotes from Twelve Data, stores a price snapshot per pick, and fires styled HTML email alerts through Resend when a stock drops to a support level. The trigger logic uses an atomic claim pattern in PostgreSQL — one transaction marks the level as triggered and inserts a pending alert log, so duplicate alerts can't happen even if the function runs twice. Historical OHLCV data gets backfilled from Yahoo Finance with a resumable bootstrap job system. The dashboard never calls the price API directly: it reads the stored snapshots and subscribes to Supabase Realtime changes on picks, support levels, and snapshots.

• –Automated price alerts: hourly Edge Function checks quotes against support levels and sends email notifications via Resend, with atomic claim/reopen logic to prevent duplicates
• –Real-time dashboard: one Supabase Realtime channel watches picks, support levels, and price snapshots; snapshot and support-level updates are merged into the UI in place without polling, and new or changed picks reload the list
• –Dual price providers: Twelve Data for hourly quotes, Yahoo Finance for historical OHLCV backfill — both normalized through exchange-aware symbol mapping (NASDAQ, Stockholm, Frankfurt)
• –Social layer: emoji reactions with optimistic UI via React 19 useOptimistic, threaded comments, and per-user interest tracking (watching, planning, positioned, passed)
• –Price proximity bar: a compact visualization plotting support-level dots and current price on a normalized range, so you can see at a glance how close a stock is to an entry
• –Interactive charts: Recharts area charts with support-level reference lines, selectable time ranges, and per-pick or portfolio-wide analytics views

• –Preventing duplicate alerts when the price-check function runs multiple times near a threshold — needed an atomic PostgreSQL claim that both marks the level and inserts the alert in one transaction, with a reopen path if email delivery fails
• –Merging Supabase Realtime subscriptions with server-rendered initial state without stale data or duplication, especially for comments where an INSERT event can repeat a comment the page already has
• –Mapping the same ticker across two different financial APIs — Twelve Data uses SYMBOL:EXCHANGE format while Yahoo uses suffixes like .ST and .DE — while also respecting per-exchange market hours and pre/post-market windows

The alert pipeline runs entirely inside Supabase: pg_cron triggers pg_net which calls the Edge Function, reading the project URL and anon key from Vault at runtime. The claim_support_level_trigger RPC atomically marks a level and inserts a pending alert log; if Resend delivery fails, mark_alert_delivery_failed_and_reopen rolls the level back so the next run retries. On the frontend, the picks list merges Realtime snapshot events into local state keyed by exchange:symbol, with ID-based deduplication for comments. A shared normalization layer handles the Twelve Data / Yahoo Finance symbol format differences and validates OHLCV bar integrity before insert.

A working private watchlist for a group of friends to track stock entries across US, Swedish, and German exchanges. The alert system catches support-level breaches hourly and emails the group with suggested position sizes. The dashboard listens for new price snapshots over Realtime instead of polling, and the social features (reactions, comments, interest tracking) let everyone see who's watching what and discuss entries in context.