Social Media Posts Section (and/or page)

[esthor]

Should we also show our recent skeets/tweets/linkedin influencer posts, etc.?

Scope creep: Would be amazing to have this hooked up with a scheduler or some abstraction layer where the user can click the post and choose to either go to it on bsky/twitter/linkedin.

[esthor]

@coderabbitai plan

[coderabbitai]

Coding Plan

Summary

• Leverage the existing cron job pattern (luma-sync) to create a Bluesky-specific sync job that fetches posts from organizers with Bluesky handles
• Use the established Electric SQL + TanStack DB collection pattern for real-time post synchronization to the client
• Build on existing UI component patterns (ProfileCard, PastEventsGrid) to create a consistent PostCard and posts page
• Design schema with platform extensibility in mind, but implement only Bluesky initially due to API access limitations on other platforms

Design Choices

Design Choice 1: Which social platforms should be supported for post fetching?

Options Considered:

1. Support all three platforms (Bluesky, Twitter, LinkedIn) with graceful degradation
2. Support only Bluesky initially, with schema extensibility for future platforms

Chosen Option: 2

Rationale: Support only Bluesky for now since it's the only platform with free public API access; the schema will include a platform field for future extensibility. Based on web search results, Twitter/X API requires paid access for reading timelines and LinkedIn has no public read API.

Design Choice 2: Should this be a new page or a section on an existing page?

Options Considered:

1. Add a section to the existing About page where organizers are shown
2. Create a dedicated /posts page with its own navigation link
3. Both: dedicated page plus a "latest posts" preview section on About

Chosen Option: 2

Rationale: Create a dedicated /posts page since it provides better UX for browsing multiple posts and follows the existing pattern of separate pages for distinct content types (/speakers, /about). The ticket mentions "section (and/or page)" leaving flexibility.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Data Layer Foundation

Establish the database schema for storing social media posts and create a client library for fetching posts from the Bluesky public API. This phase focuses on the core data infrastructure needed before any sync or UI work.

Task 1: Add Social Posts Table to Schema

Define a new social_posts table that stores cached social media posts with author attribution and platform-specific metadata.

• Add platformEnum with initial value 'bluesky' (extensible for future platforms)
• Create socialPostsTable in app/src/lib/schema.ts with columns: id (uuid PK), profile_id (FK to profiles), platform (enum), external_id (unique post identifier from platform), content (text), external_url (link to original), posted_at (original post timestamp), media_urls (jsonb array, nullable), plus standard created_at/updated_at
• Add unique constraint on (platform, external_id) to prevent duplicate posts
• Create corresponding Drizzle migration

Task 2: Create Bluesky Client Library

Build a typed client for the Bluesky public API following the pattern established by the Luma client.

• Create app/src/lib/bluesky/ directory with client implementation
• Implement fetchAuthorFeed(handle, options) using the public endpoint https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed
• Support limit and filter parameters (default to posts_no_replies)
• Parse response into typed BlueskyPost objects with fields: uri, cid, text, createdAt, authorHandle, embedImages
• Handle errors gracefully (non-existent handles, rate limits) with logging
• Add BLUESKY_SYNC_ENABLED optional env var to app/example.env for feature flagging

🤖 Prompt for AI agents

Implement the data layer foundation for the social media posts feature. The goal
is to define the database schema and a typed Bluesky API client before any sync
or UI work begins.

**Task 1: Add Social Posts Table to Schema (`app/src/lib/schema.ts`)**
- Define a `platformEnum` Drizzle enum with the initial value `'bluesky'`
- Create a `socialPostsTable` with the following columns:
  - `id`: uuid, primary key
  - `profile_id`: FK referencing the profiles table
  - `platform`: the platform enum
  - `external_id`: unique post identifier from the platform (text)
  - `content`: text
  - `external_url`: link to the original post
  - `posted_at`: timestamp for the original post
  - `media_urls`: jsonb array, nullable
  - `created_at` / `updated_at`: standard timestamps
- Add a unique constraint on `(platform, external_id)` to prevent duplicate
posts
- Generate the corresponding Drizzle migration

**Task 2: Create Bluesky Client Library (`app/src/lib/bluesky/`)**
- Create the `app/src/lib/bluesky/` directory and implement a typed client
- Implement `fetchAuthorFeed(handle, options)` that calls
`https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed`
- Support `limit` and `filter` parameters, defaulting `filter` to
`posts_no_replies`
- Parse API responses into typed `BlueskyPost` objects with fields: `uri`,
`cid`, `text`, `createdAt`, `authorHandle`, `embedImages`
- Handle errors gracefully (non-existent handles, rate limits) with logging
- Add `BLUESKY_SYNC_ENABLED` as an optional env var to `app/example.env` for
feature flagging

Phase 2: Background Sync Infrastructure

Implement the cron job and API routes needed to periodically fetch posts from organizer Bluesky accounts and make them available via Electric SQL sync.

Task 1: Create Bluesky Posts Sync Cron Job

Build a scheduled job that fetches recent posts from all organizers with Bluesky handles, following the luma-sync workflow pattern.

• Create route handler at app/src/app/api/cron/bluesky-sync/route.ts with CRON_SECRET authorization check
• Query profiles table for all organizers with non-null bluesky_handle
• For each organizer, fetch recent posts (limit 5-10) using the Bluesky client
• Upsert posts to social_posts table using onConflictDoUpdate on (platform, external_id) to update content if edited
• Return sync summary (posts fetched, inserted, updated)
• Add cron schedule to app/vercel.json (suggest hourly or every 4 hours)

Task 2: Add Social Posts Shape Proxy Route

Create the Electric SQL shape proxy endpoint so posts can be synced to the client via TanStack DB.

• Create app/src/app/api/v1/shapes/social-posts/route.ts following the existing shape proxy pattern
• Configure shape for social_posts table
• No authentication required (posts are public content)

🤖 Prompt for AI agents

Implement the background sync infrastructure for fetching and distributing
social posts. The goal is to create a cron job that populates the database and
an Electric SQL shape proxy that exposes posts to the client.

**Task 1: Bluesky Sync Cron Job (`app/src/app/api/cron/bluesky-sync/route.ts`)**
- Follow the existing luma-sync route handler pattern
- Add `CRON_SECRET` authorization check at the top of the handler
- Query the profiles table for all organizers with a non-null `bluesky_handle`
- For each organizer, call `fetchAuthorFeed` from the Bluesky client (limit 5–10
posts)
- Upsert each post into `social_posts` using `onConflictDoUpdate` on the
`(platform, external_id)` unique constraint to handle edits
- Return a sync summary response (posts fetched, inserted, updated)
- Register the cron job in `app/vercel.json` on an hourly or every-4-hours
schedule

**Task 2: Social Posts Shape Proxy Route
(`app/src/app/api/v1/shapes/social-posts/route.ts`)**
- Follow the existing shape proxy pattern used by other tables
- Configure the shape to target the `social_posts` table
- No authentication required — posts are public content

Phase 3: Frontend Display Layer

Build the UI components and page needed to display the social posts feed, integrating with TanStack DB for reactive updates.

Task 1: Create Social Posts Collection

Define the TanStack DB collection for reactive social posts data on the client.

• Add socialPostsCollection to app/src/lib/collections.ts using electricCollectionOptions
• Define Zod schema matching the social_posts table structure
• Configure shape URL pointing to /api/v1/shapes/social-posts
• Read-only collection (no mutation callbacks needed since posts are synced server-side)

Task 2: Create PostCard Component

Build a reusable card component for displaying individual social media posts, styled consistently with existing card components.

• Create app/src/components/post-card.tsx using shadcn Card primitives
• Display author info (avatar, name) with link to their profile — reuse patterns from ProfileCard
• Show post content with reasonable text truncation for very long posts
• Include posted timestamp (relative time, e.g., "2 hours ago")
• Add platform badge/icon (Bluesky logo) to indicate source
• Include external link button/icon that opens the original post on Bluesky
• Handle optional media attachments (show thumbnail previews if present)

Task 3: Create Posts Page

Build the dedicated /posts page that displays the social media feed from organizers.

• Create app/src/app/posts/page.tsx as server component with <PageLayout> wrapper
• Add page metadata (title, description) for SEO
• Create client component PostsFeed that uses useLiveQuery on socialPostsCollection
• Join with profiles/images collections to get author info and avatars
• Display posts in a responsive grid or timeline layout (reference PastEventsGrid pattern)
• Add empty state for when no posts are available
• Consider adding filter by organizer (optional enhancement)

Task 4: Add Navigation Link

Integrate the new posts page into the site navigation.

• Update app/src/components/top-nav.tsx to add "Posts" or "Social" link in the LinkList component
• Place between existing "About us" and "Past speakers" links
• Ensure link appears in both desktop inline nav and mobile popover menu

🤖 Prompt for AI agents

Implement the frontend display layer for the social posts feed. The goal is to
wire up a TanStack DB collection, build a PostCard component, create the /posts
page, and add it to site navigation.

**Task 1: Social Posts Collection (`app/src/lib/collections.ts`)**
- Add `socialPostsCollection` using `electricCollectionOptions`
- Define a Zod schema matching the `social_posts` table structure
- Set the shape URL to `/api/v1/shapes/social-posts`
- This is a read-only collection — no mutation callbacks are needed

**Task 2: PostCard Component (`app/src/components/post-card.tsx`)**
- Use shadcn Card primitives, following the style of existing card components
like ProfileCard
- Display author info (avatar, name) with a link to their profile
- Show post content with reasonable text truncation for very long posts
- Include a relative posted timestamp (e.g., "2 hours ago")
- Add a Bluesky platform badge/icon to indicate source
- Include an external link button/icon that opens the original post on Bluesky
- Handle optional media attachments by showing thumbnail previews if present

**Task 3: Posts Page (`app/src/app/posts/page.tsx`)**
- Create the file as a server component wrapped in `<PageLayout>`
- Add page metadata (title, description) for SEO
- Create a client component `PostsFeed` that uses `useLiveQuery` on
`socialPostsCollection`
- Join with profiles/images collections to resolve author info and avatars
- Display posts in a responsive grid or timeline layout, referencing the
PastEventsGrid pattern
- Add an empty state for when no posts are available
- Optionally add a filter by organizer

**Task 4: Navigation Link (`app/src/components/top-nav.tsx`)**
- Add a "Posts" or "Social" link to the `LinkList` component
- Place it between the existing "About us" and "Past speakers" links
- Ensure it appears in both the desktop inline nav and the mobile popover menu

Research

This is a Next.js App Router codebase for an event/hackathon platform. It uses Drizzle ORM with Neon Postgres, TanStack DB with Electric SQL for real-time sync, and shadcn/ui components. The app already stores organizer profiles with social handles (bluesky_handle, twitter_handle, linkedin_handle) and has an established cron job pattern (luma-sync) for background data fetching. Card components (ProfileCard, TeamCard) and grid patterns (PastEventsGrid) provide UI foundations to build upon.

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Implement the data layer foundation for the social media posts feature. The goal
is to define the database schema and a typed Bluesky API client before any sync
or UI work begins.

**Task 1: Add Social Posts Table to Schema (`app/src/lib/schema.ts`)**
- Define a `platformEnum` Drizzle enum with the initial value `'bluesky'`
- Create a `socialPostsTable` with the following columns:
  - `id`: uuid, primary key
  - `profile_id`: FK referencing the profiles table
  - `platform`: the platform enum
  - `external_id`: unique post identifier from the platform (text)
  - `content`: text
  - `external_url`: link to the original post
  - `posted_at`: timestamp for the original post
  - `media_urls`: jsonb array, nullable
  - `created_at` / `updated_at`: standard timestamps
- Add a unique constraint on `(platform, external_id)` to prevent duplicate
posts
- Generate the corresponding Drizzle migration

**Task 2: Create Bluesky Client Library (`app/src/lib/bluesky/`)**
- Create the `app/src/lib/bluesky/` directory and implement a typed client
- Implement `fetchAuthorFeed(handle, options)` that calls
`https://public.api.bsky.app/xrpc/app.bsky.feed.getAuthorFeed`
- Support `limit` and `filter` parameters, defaulting `filter` to
`posts_no_replies`
- Parse API responses into typed `BlueskyPost` objects with fields: `uri`,
`cid`, `text`, `createdAt`, `authorHandle`, `embedImages`
- Handle errors gracefully (non-existent handles, rate limits) with logging
- Add `BLUESKY_SYNC_ENABLED` as an optional env var to `app/example.env` for
feature flagging
===============================================================================

Task: 2

Implement the background sync infrastructure for fetching and distributing
social posts. The goal is to create a cron job that populates the database and
an Electric SQL shape proxy that exposes posts to the client.

**Task 1: Bluesky Sync Cron Job (`app/src/app/api/cron/bluesky-sync/route.ts`)**
- Follow the existing luma-sync route handler pattern
- Add `CRON_SECRET` authorization check at the top of the handler
- Query the profiles table for all organizers with a non-null `bluesky_handle`
- For each organizer, call `fetchAuthorFeed` from the Bluesky client (limit 5–10
posts)
- Upsert each post into `social_posts` using `onConflictDoUpdate` on the
`(platform, external_id)` unique constraint to handle edits
- Return a sync summary response (posts fetched, inserted, updated)
- Register the cron job in `app/vercel.json` on an hourly or every-4-hours
schedule

**Task 2: Social Posts Shape Proxy Route
(`app/src/app/api/v1/shapes/social-posts/route.ts`)**
- Follow the existing shape proxy pattern used by other tables
- Configure the shape to target the `social_posts` table
- No authentication required — posts are public content
===============================================================================

Task: 3

Implement the frontend display layer for the social posts feed. The goal is to
wire up a TanStack DB collection, build a PostCard component, create the /posts
page, and add it to site navigation.

**Task 1: Social Posts Collection (`app/src/lib/collections.ts`)**
- Add `socialPostsCollection` using `electricCollectionOptions`
- Define a Zod schema matching the `social_posts` table structure
- Set the shape URL to `/api/v1/shapes/social-posts`
- This is a read-only collection — no mutation callbacks are needed

**Task 2: PostCard Component (`app/src/components/post-card.tsx`)**
- Use shadcn Card primitives, following the style of existing card components
like ProfileCard
- Display author info (avatar, name) with a link to their profile
- Show post content with reasonable text truncation for very long posts
- Include a relative posted timestamp (e.g., "2 hours ago")
- Add a Bluesky platform badge/icon to indicate source
- Include an external link button/icon that opens the original post on Bluesky
- Handle optional media attachments by showing thumbnail previews if present

**Task 3: Posts Page (`app/src/app/posts/page.tsx`)**
- Create the file as a server component wrapped in `<PageLayout>`
- Add page metadata (title, description) for SEO
- Create a client component `PostsFeed` that uses `useLiveQuery` on
`socialPostsCollection`
- Join with profiles/images collections to resolve author info and avatars
- Display posts in a responsive grid or timeline layout, referencing the
PastEventsGrid pattern
- Add an empty state for when no posts are available
- Optionally add a filter by organizer

**Task 4: Navigation Link (`app/src/components/top-nav.tsx`)**
- Add a "Posts" or "Social" link to the `LinkList` component
- Place it between the existing "About us" and "Past speakers" links
- Ensure it appears in both the desktop inline nav and the mobile popover menu

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For design choice 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!