Add users table with SteemHunt API — cache profile data, X account, refresh button

[realproject7]

Summary

Create a users table to cache profile data instead of calling external APIs on every page load. Store ALL available data from SteemHunt/Neynar — Supabase storage is free, API calls are not. Reference ~/Projects/dropcast for the full pattern.

1. Users Table (Supabase migration)

Reference: ~/Projects/dropcast/scripts/supabase-schema.sql + add-steemhunt-columns.sql + BE-148-add-x-stats-columns.sql + add-quotient-score.sql

Store everything available — Supabase row storage costs nothing extra:

Core identity:

• `id` (UUID PK), `fid` (INTEGER UNIQUE), `username`, `display_name`, `pfp_url`
• `custody_address`, `verified_addresses` (TEXT[])
• `primary_address` (TEXT)

Profile metadata:

• `bio`, `url`, `location`

Social handles:

• `twitter` (X handle — from SteemHunt)
• `github` (GitHub handle — from SteemHunt)

Farcaster stats:

• `follower_count`, `following_count`
• `power_badge` (BOOLEAN), `is_pro_subscriber` (BOOLEAN)
• `neynar_score` (DECIMAL)
• `spam_label` (INTEGER)
• `fc_created_at` (TIMESTAMPTZ)

X/Twitter stats:

• `x_followers_count` (BIGINT), `x_following_count` (BIGINT)
• `x_verified` (BOOLEAN), `x_display_name` (TEXT)
• `x_stats_fetched_at` (TIMESTAMPTZ) — 5-min cooldown key

Quotient score:

• `quotient_score` (DECIMAL), `quotient_rank` (INTEGER)
• `quotient_labels` (JSONB)
• `quotient_updated_at` (TIMESTAMPTZ) — 7-day refresh

Freshness tracking:

• `stats_fetched_at` (TIMESTAMPTZ) — Neynar score, 7-day refresh
• `steemhunt_fetched_at` (TIMESTAMPTZ) — SteemHunt data, 5-min cooldown
• `created_at`, `updated_at` (auto-trigger)

2. SteemHunt API as Primary Source (FREE)

Reference: `~/Projects/dropcast/lib/farcaster-indexer.ts`

• Primary: SteemHunt API (`https://fc.hunt.town\`)
  • By FID: `GET /users/byFid/{fid}`
  • By wallet: `GET /users/byWallet/{address}`
  • Returns: username, display_name, pfp_url, twitter, github, bio, url, location, verified_addresses, follower_count, following_count, spam_label, pro_subscriber, created_at
  • 1-hour in-memory cache, 5-min DB cooldown
  • Circuit breaker: opens after 5 failures, resets after 1 min
  • Retry: 3 attempts with exponential backoff, 3s timeout per attempt
• Fallback: Neynar API (current implementation — keep as backup)
  • Only called if SteemHunt fails or circuit breaker is open
  • Also used for neynar_score (SteemHunt doesn't provide this)

3. Register on Wallet Connect

Reference: `~/Projects/dropcast/app/api/user/register-by-wallet/route.ts`

• Create `POST /api/user/register-by-wallet`
• Called when user connects wallet
• Flow: wallet → SteemHunt lookup → upsert ALL fields to users table
• If user exists and data is fresh (< 5 min), skip API call and return cached

4. X/Twitter Account Data

Reference: `~/Projects/dropcast/lib/twitterapi.ts` + `/api/user/x-stats/route.ts`

• X handle comes from SteemHunt API's `twitter` field (no extra call needed for handle)
• X stats (followers, verified badge, display name) fetched separately via twitterapi.io when requested
• Create `POST /api/user/x-stats` endpoint
• 5-min cooldown between X stats refreshes
• Requires `TWITTERAPI_IO_KEY` env var

5. Quotient Score

Reference: `~/Projects/dropcast/scripts/add-quotient-score.sql`

• Fetch from Quotient API during onboard if stale (> 7 days)
• Store score, rank, and labels (JSONB)
• Non-blocking — don't fail onboard if Quotient API is down

6. Profile Display — Use Cached Data

• Profile page reads from `users` table first
• Falls back to live API call only if user not in table
• Display X handle + link to X profile when available
• Show Quotient score/rank if available

7. Refresh Button on Own Profile

• Show "Refresh Profile" button only when viewing your OWN profile
• Calls `POST /api/user/onboard` with `forceRefresh=true`
• Enforces 5-min cooldown (show remaining time if on cooldown)
• Re-fetches from SteemHunt API and updates ALL fields in users table
• No automatic cron — all refreshes are user-initiated

Files to Create/Modify

• New: `supabase/migrations/00027_create_users_table.sql`
• New: `lib/farcaster-indexer.ts` (SteemHunt API client with cache + circuit breaker)
• New: `src/app/api/user/register-by-wallet/route.ts`
• New: `src/app/api/user/onboard/route.ts`
• New: `src/app/api/user/x-stats/route.ts`
• Modify: wallet connect flow to call register-by-wallet
• Modify: profile page to read from users table first, then API fallback
• Modify: all components displaying user identity to prefer DB data

Acceptance Criteria

• Users table created with ALL columns (matching Dropcast schema)
• SteemHunt API as primary source with Neynar fallback + circuit breaker
• User data saved on wallet connect (all available fields)
• Profile page reads from DB (not live API every time)
• X/Twitter handle and stats displayed when available
• Quotient score stored and displayed
• Refresh button on own profile with 5-min cooldown
• Existing profile page functionality preserved (agent metadata, writer stats, etc.)