# Low-Level Design: Social Media Post Scheduler — Scheduling, Multi-Platform Publishing, and Analytics

Source: https://www.techinterview.org/post/3233464812/lld-social-media-post-scheduler/
Updated: 2026-04-17 · techinterview.org

## Core Entities

**Post**: post_id, user_id, content, media_urls[], platforms[] (TWITTER, INSTAGRAM, LINKEDIN, FACEBOOK), scheduled_at (UTC), status (DRAFT, SCHEDULED, PUBLISHING, PUBLISHED, FAILED), created_at. **PostPlatformResult**: result_id, post_id, platform, external_post_id, status (SUCCESS, FAILED), error_message, published_at. **PublishingAccount**: account_id, user_id, platform, credentials (encrypted OAuth tokens), is_active. **Analytics**: post_id, platform, likes, shares, comments, impressions, fetched_at.

## Scheduling Architecture

Posts are stored with a scheduled_at timestamp. A scheduler worker runs every minute and queries: SELECT * FROM posts WHERE status = SCHEDULED AND scheduled_at  NOW() - INTERVAL 5 minutes FOR UPDATE SKIP LOCKED LIMIT 50. The FOR UPDATE SKIP LOCKED allows multiple workers to run concurrently without processing the same post. Once a post is selected: set status to PUBLISHING (preventing re-pickup), then fan-out to each target platform in parallel.

## Multi-Platform Publishing


```
class PublishingService:
    PLATFORM_ADAPTERS = {
        Platform.TWITTER:   TwitterAdapter,
        Platform.INSTAGRAM: InstagramAdapter,
        Platform.LINKEDIN:  LinkedInAdapter,
    }

    def publish(self, post: Post):
        self.db.update_status(post.id, Status.PUBLISHING)
        results = []
        with ThreadPoolExecutor(max_workers=len(post.platforms)) as ex:
            futures = {
                ex.submit(self.publish_to_platform,
                          post, platform): platform
                for platform in post.platforms
            }
            for future in as_completed(futures):
                results.append(future.result())
        overall = (Status.PUBLISHED
                   if all(r.success for r in results)
                   else Status.FAILED)
        self.db.update_status(post.id, overall)
        self.db.save_results(post.id, results)
```


## Platform Adapters

Each platform has a different API: Twitter uses OAuth 1.0a with tweet creation endpoint; Instagram uses Graph API with media object creation (separate upload + publish steps); LinkedIn uses OAuth 2.0 with UGC posts API. Each adapter handles: OAuth token refresh (tokens expire; refresh before each publish), API rate limits (Twitter: 300 tweets/15min, LinkedIn: 100 posts/day), retry on transient errors (5xx responses, network timeouts), and platform-specific content constraints (Twitter: 280 chars, LinkedIn: 3000 chars). Adapters are stateless -- credentials are fetched from the PublishingAccount table on each call.

## Failure Handling and Retry

Platform failures can be transient (rate limit, network error) or permanent (invalid token, content policy violation). Retry strategy: exponential backoff for transient errors (retry after 1min, 5min, 15min). Give up after 3 retries and mark the platform result as FAILED. Notify the user with the failure reason. Store the error_message on PostPlatformResult for debugging. Re-authentication: if a token is expired or revoked, prompt the user to re-connect the platform account. Do not delete scheduled posts on auth failure -- allow the user to reconnect and retry.

## Analytics Collection

After publishing, schedule analytics fetch jobs at: 1 hour, 6 hours, 24 hours, 7 days post-publication. Each job calls the platform API for the external_post_id and stores likes, shares, comments, impressions in the Analytics table. Compare metrics across platforms for the same post (which platform got the most engagement?). Aggregate per user: total reach this week, best-performing platform, optimal posting time (time of day with highest average engagement from historical data). Platform API rate limits apply to analytics fetching too -- batch requests where possible (Twitter supports 100 tweets per lookup).

## Timezone and Optimal Time Recommendations

Users schedule in their local timezone; store UTC in the database. Convert scheduled_at from user timezone to UTC on input. When displaying, convert back. Best time recommendations: analyze the user historical post data -- for each hour of the week (7 days * 24 hours = 168 buckets), compute average engagement rate. Recommend the top 3 time slots. This is personalized per user and per platform (Instagram audience vs LinkedIn audience have different peak times).

Asked at: [Twitter/X Interview Guide](https://techinterview.org/twitter-interview/)

Asked at: [LinkedIn Interview Guide](https://techinterview.org/linkedin-interview/)

Asked at: [Snap Interview Guide](https://techinterview.org/snap-interview/)

Asked at: [Shopify Interview Guide](https://techinterview.org/shopify-interview/)