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
Asked at: LinkedIn Interview Guide
Asked at: Snap Interview Guide
Asked at: Shopify Interview Guide