Content Pipeline¶

End-to-end flow from content creation through AI generation, design rendering, branding, validation, scheduling, and publishing to social media platforms.

Pipeline Overview¶

Content Entry (CSV/API/AI)
    ↓
[CGEN/UGEN] AI Content Generation
    ↓
[DGEN/DPGEN] Polotno Design Generation + Template Selection
    ↓
[TIV] Validation (Color/Contrast Check)
    ↓
[MCC/TCH] Color Correction
    ↓
[UCL/TSB] Brand Application
    ↓
Content Planner Assignment (INSERTED → SCHEDULED)
    ↓
Feed Distribution (PENDING → INSERTED)
    ↓
First Comment Generation
    ↓
Platform Publishing (Facebook, Instagram, LinkedIn, TikTok)
    ↓
Status Verification → Insights Collection

Phase 1: Content Entry¶

Content enters the system through three paths:

A. CSV/Excel Import¶

Jobs: job_csvFileUpload.js, job_excel_to_library.js

File uploaded to S3, record created in tLibraryUploadLog with status 'Inserted'
Cron picks up pending records
Parses file, performs duplicate checking
Inserts rows into tUserLibrary (user-specific) or tLibrary (global)
Creates job type 'UMGEN' (User Media Generation) for each new entry
Updates upload log status to 'Completed'

B. API Routes¶

Direct creation via REST endpoints in routes/routes.js and routes/auth.js. Manual content includes header, title, body, footer fields.

C. AI Content Generation¶

Triggered by job type 'CGEN' or 'UGEN' in the tJobs table.

Phase 2: AI Content Generation¶

Job: `job_content_generation.js` (Type: CGEN)¶

Schedule: Every 15 seconds

Picks jobs with type='CGEN', status=0
Updates status to 1 (processing)
Retrieves subject info and default categories
For each category, calls OpenAI API for content generation
Creates library entries with conditioned=1
If user has AI role (role_type=5), auto-creates 'DGEN' job
Updates status to 2 (completed) or 3 (error)

Output: Populates tLibrary.iSearchTerms with image search terms for later image generation.

Job: `job_new_content_generation.js` (Type: UGEN)¶

Variant for user-specific subjects. Handles language preferences from tSetting.languageId.

AI Providers Used¶

Provider	Package	Use Case
OpenAI GPT	`openai`	Content text generation
Google Gemini	`@google/generative-ai`	Content analysis, industry classification
Llama 3 (Bedrock)	`@aws-sdk/client-bedrock-runtime`	First comments, subject recommendations
Custom prompts	`tLanguageModels.prompt`	Provider-specific prompt templates

Phase 3: Template & Design Generation¶

Job: `job_design_generation.js` (Type: DGEN)¶

Schedule: Every minute

Template Selection Logic: 1. Match template by content structure (which fields are populated: header, title, body, footer) 2. Filter by subjectId and noId (network org ID) 3. Prioritize by lastUsed timestamp (round-robin load balancing) 4. Check brand/accent color compatibility

Design Rendering: 1. Create unique filename and UUID 2. Call Polotno API (createInstance) with template JSON and content data 3. Store generated JSON in S3: /generatedFiles/jsonFiles/{code}.json 4. Store rendered PNG in S3: /generatedFiles/images/{code}.png 5. Create tTempalte_Status record to track rendering state 6. Update tUploads with image URLs

Multi-Variant Support: Single content spawns up to 3 design variants (from tSVariants) for A/B testing.

Job: `job_generate_images.js`¶

Schedule: Every 5 minutes

Generates AI images to replace placeholder assets in templates using Leonardo AI / Stable Diffusion, based on iSearchTerms from the content generation phase.

Phase 4: Validation¶

Job: `job_post_validation.js` (Type: TIV)¶

Schedule: Every 10 seconds

Picks media records pending color verification
Downloads Polotno JSON from S3
Extracts text elements, checks text/background color contrast
Calls Google Vertex AI to analyze rendered image
Flags text elements that need color adjustment
Updates tMedia.ColourChecked=1

Job: `job_media_color_correction.js` (Type: MCC)¶

Modifies Polotno JSON to fix flagged text colors
Calculates complementary colors for proper contrast
Re-renders image from modified JSON
Uploads corrected image + thumbnail to S3

Job: `job_text_color_change.js` (Type: TCH)¶

Adjusts specific text element colors while preserving overall design integrity.

Phase 5: Branding¶

Job: `job_update_color_logo.js` (Type: UCL)¶

Schedule: Every 2 minutes Trigger: Content planner items with status='BRANDING', approved=0

Retrieve content planner and associated media
Download Polotno JSON from tMedia or tGeneratedFiles
Apply brand colors (from account settings) to design elements
Generate new branded image
Create tGeneratedFiles record with new paths
Update content planner status

Output: Branded images stored in /generatedFiles/images/ and /generatedFiles/thumbs/

Job: `job_tempset_branding.js` (Type: TSB)¶

Schedule: Every 5 minutes

Applies branding to entire template sets for consistent visual identity. Updates uploadStatus from 'BRANDING' to 'BRANDED'.

Phase 6: Scheduling¶

Feed Auto-Population¶

Job: job_auto_post.js (every 30 seconds)

Retrieves configured feeds from tFeed_Status where status=PENDING or job_run_at <= NOW()
Reads posting schedule from postInfo JSON (day/hour/minute per slot)
Calculates which posts to publish based on timezone
Creates tContentPlanner entries with status='PENDING'
Links to user folders or subjects based on enableMyLibrary / enableSharedLibrary

Content Assignment¶

Job: job_content_schedule.js (every 20 seconds)

Processes content planners with status='INSERTED':

Calls get_subject_post() to select library item
Creates thumbnail from post image
Updates content planner with:
scheduledMediaId, thumbnailUrl, mediaUrl
status='SCHEDULED'
bColor, aColor (brand/accent colors)
libraryId, source, catId
If source=1 (owned content), creates 'UCL' job for branding
Otherwise marks as ready for publishing

Channel Mapping¶

Job: job_update_cpchannel_accounts.js (every 10 seconds)

Maps content planner entries to connected social media accounts in tCPChannelAccounts, validating token validity before assignment.

Phase 7: Publishing¶

Pre-Publishing: First Comment Generation¶

Job: job_first_comment.js (every 40 seconds)

Trigger: Posts with status='SCHEDULED', has_comment=0, enableFirstComment=1
Uses Llama 3 (model ID=53) to generate an engaging first comment
Stores in tContentPlanner.firstComment
Sets has_comment=1 (success) or 2 (failed)

Publishing Query Pattern¶

All platform jobs use the same selection criteria:

WHERE status IN ('SCHEDULED','FAILED','POSTED')
  AND approved = 1
  AND type = 1
  AND cancelled = 0
  AND channelId = ?       -- platform-specific
  AND publishOn <= NOW()  -- time has arrived

Facebook Publishing¶

Jobs: job_facebook_publish.js, job_facebook_video_publish.js, job_facebook_carousal_publish.js, job_facebook_link_publish.js

Retrieve post data with media from tGeneratedFiles
Get signed S3 URL (15 min expiry)
Fetch account access token from tMemberAuth / tDefaultAccount
Call Facebook Graph API /feed endpoint
Store response in tCPChannelAccounts.Response
Update status: 0→1 (posted) or 2 (failed)

Instagram Publishing¶

Jobs: job_instagram_publish.js, job_instagram_video_publish.js, job_instagram_carousal_publish.js

Uses Instagram Graph API via Facebook (Business accounts). Same flow pattern as Facebook.

LinkedIn Publishing¶

Jobs: job_linkedin_publish.js, job_linkedin_video_publish.js, job_linkedin_carousal_publish.js, job_linkedin_repost_publish.js, job_linkedIn_link_publish.js

OAuth-authenticated requests to LinkedIn API. Supports shares, articles, carousels (document upload), and reposts.

TikTok Publishing¶

Job: job_tiktok_content_publish.js

Call /v2/post/publish/video/init/ for upload initialization
Upload video bytes in chunks to returned URL
Call /v2/post/publish/action/publish/ to finalize
Poll for PUBLISH_COMPLETE status (max 8 attempts, 4s delay)
On success: create social post record via addSPosts()

Phase 8: Post-Publishing¶

Status Verification¶

Job: job_update_fb_posted_status.js (every 10 seconds)

Checks posts published 3+ minutes ago
Calls Facebook Graph API to verify is_published=true
Updates status to confirmed or failed

Insights Collection¶

Jobs: dashboard/services/job_post_insights.js, dashboard/services/job_account_insights.js

Collects post metrics (likes, comments, shares, reach) 24–48 hours post-publication.

Error Handling¶

Email: sendPostFailedMail() via SendGrid on publishing failure
Slack: Notifications to channel C05TS9AHBH6 for generation errors
Retry: Failed posts remain in queue and automatically re-enter publishing cycle

Content Planner Status Machine¶

    ┌──────────┐
    │ PENDING  │ ← Feed auto-population (job_auto_post)
    └────┬─────┘
         ↓
    ┌──────────┐
    │ INSERTED │ ← Content assigned (job_content_schedule)
    └────┬─────┘
         ↓
    ┌──────────┐     ┌──────────┐
    │SCHEDULED │────▶│ BRANDING │ (if source=1, owned content)
    └────┬─────┘     └────┬─────┘
         │                 ↓
         │           Brand applied (job_update_color_logo)
         │                 │
         ▼                 ▼
    ┌──────────┐
    │  POSTED  │ ← Platform publish jobs
    └──────────┘
         │
         ↓ (on error)
    ┌──────────┐
    │  FAILED  │ → Re-enters publishing queue
    └──────────┘

Job Type Reference¶

⚠ Earlier versions of this doc listed type codes inferred from file naming, not from grep'd code references. The verification audit found that the actual code uses a different set. The canonical reference now lives in jobs-inventory.md § Job Type Codes; the table below is summarized from that source.

Status codes: 0=pending, 1=processing, 2=completed, 3=retry, 4=final-failure

Code-referenced type codes (verified against grep): BGEN, CGEN, CLKB, DGEN, GEML, HPB, PIC, RGEN, TCC, TCON, TIV, TSB, UCL, UGEN. Some codes' meanings are not yet fully verified — see jobs-inventory.md.

Action: run SELECT DISTINCT type, COUNT(*) FROM tJobs GROUP BY type ORDER BY 2 DESC to settle which codes are actually active in production.

Key Database Tables¶

Table	Role in Pipeline
`tJobs`	Central job queue (type, status, detail JSON)
`tLibrary`	Global content library
`tUserLibrary`	Per-account content library
`tMedia`	Generated design records (JSON + image URLs)
`tUserMedia`	Per-account media records
`tContentPlanner`	Publishing queue with scheduling
`tCPChannels`	Platform channel assignments per post
`tCPChannelAccounts`	Account-level publish tracking per channel
`tFeed_Status`	Auto-posting feed configuration
`tGeneratedFiles`	Finalized branded post files
`tUploads`	Raw asset storage records
`tDefaultTemplates`	Design template library
`tSVariants`	Brand color variant definitions
`tTempalte_Status`	Template rendering state tracking
`tLibraryUploadLog`	CSV/Excel import tracking

S3 Storage Structure¶

{S3_Bucket}/
├── {S3_Path}/
│   ├── upload_thumbnails/{uuid}.png
│   └── uploads/...
└── generatedFiles/
    ├── images/{code}.png      ← Final rendered designs
    ├── jsonFiles/{code}.json  ← Polotno template JSON
    └── thumbs/{code}.png     ← Thumbnail versions

Performance Characteristics¶

Concurrency control: Each job uses isOnProcess flag to prevent parallel execution of the same job
Processing order: FIFO with LIMIT 1 queries (sequential per job)
Template load balancing: Selection by lastUsed timestamp (round-robin)
Variant rotation: usageCount tracking, sequence resets at 30
Retry strategy: TikTok: 8 attempts / 4s delay; OpenAI: 3 retries / exponential backoff; Facebook: re-queue via FAILED status
Environment gating: PUBLISH_KEY controls test vs production publishing; test mode checks tTestMember allowlist