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).
{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “How do you ensure a scheduled post is published exactly once with multiple workers?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Use FOR UPDATE SKIP LOCKED in the worker query: SELECT * FROM posts WHERE status = SCHEDULED AND scheduled_at <= NOW() FOR UPDATE SKIP LOCKED LIMIT 50. FOR UPDATE locks the selected rows. SKIP LOCKED means competing workers skip rows already locked by another worker — they see and process different rows. Immediately update selected posts to status=PUBLISHING before releasing the lock — this prevents a slow worker from having its selection re-picked by another worker after the lock is released. This pattern allows horizontal scaling of workers with guaranteed at-most-once pickup. For at-least-once with deduplication: also use an idempotency key on the platform publish API call."
}
},
{
"@type": "Question",
"name": "How do you handle OAuth token expiry for multiple social platforms?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Store access_token, refresh_token, and token_expires_at per PublishingAccount. Before each publish: check if token_expires_at < NOW() + 5 minutes. If so: call the platform token refresh endpoint with the refresh_token. Update access_token and token_expires_at in the database. Use the fresh token for the publish. If the refresh fails (refresh_token also expired or revoked): mark the account as REQUIRES_REAUTH and notify the user. Never hardcode token lifetimes — different platforms have different expiry (Twitter never expires, Instagram: 60 days, LinkedIn: 60 days). Some platforms (Instagram) require proactive token refresh before expiry via a cron job (refresh tokens that expire within 30 days)."
}
},
{
"@type": "Question",
"name": "How do you handle platform-specific content constraints?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Each platform has different limits: Twitter: 280 characters (URLs count as 23 chars regardless of length), 4 images or 1 video. Instagram: 2200 character caption, requires at least one image or video, no clickable links in captions. LinkedIn: 3000 characters, supports documents/articles. Facebook: 63,206 characters. Validation approach: validate against platform constraints before scheduling (not at publish time — fail fast). Store a content_warnings field per platform showing constraint violations. Allow the user to edit content per-platform (LinkedIn version vs Twitter version of the same post). At publish time, validate again in the adapter in case constraints changed — return a clear error if the platform API rejects the content."
}
},
{
"@type": "Question",
"name": "How do you implement best-time recommendations for posting?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Collect historical engagement data: for each published post, store publish_time (day_of_week, hour_of_day) and engagement metrics (likes, comments, shares, impressions). For each user, compute average engagement rate per (platform, day_of_week, hour_of_day) bucket. A user with 90 days of post history has data across 168 time buckets (7 days * 24 hours). Recommend top-3 buckets by average engagement rate. Cold start (new users): use global averages or platform-level research data (LinkedIn peaks Tuesday-Thursday 9am, Twitter peaks mornings/evenings). Update recommendations monthly as new post data accumulates. Show the recommendation with confidence level — a bucket with 2 data points is less reliable than one with 20."
}
},
{
"@type": "Question",
"name": "How would you scale this system to support millions of scheduled posts?",
"acceptedAnswer": {
"@type": "Answer",
"text": "Partition the posts table by scheduled_at date — queries for today's due posts only scan one partition. Use a dedicated job queue (Celery, Temporal, AWS SQS) instead of DB polling — the scheduler enqueues publish tasks when a post becomes due; workers pull from the queue. This decouples scheduling (DB) from execution (workers) and handles bursty traffic better. For high-throughput publishing: separate worker pools per platform (Twitter workers, Instagram workers) with platform-specific concurrency limits. Rate limit tracking per platform account: use Redis counters to track API calls per time window and throttle workers accordingly. Store platform API credentials in a secrets manager (AWS Secrets Manager, HashiCorp Vault) rather than in the database."
}
}
]
}
Asked at: Twitter/X Interview Guide
Asked at: LinkedIn Interview Guide
Asked at: Snap Interview Guide
Asked at: Shopify Interview Guide