Home

Automated Blog Content Pipeline

A content generation and scheduling pipeline has been implemented to automate the creation of blog posts for "The Shopper's Guide".

Overview

The system uses a blog_ideas table as a work queue for a Python script (content_worker.py) that leverages the Gemini 2.5 Flash API to write and schedule articles.

Workflow

1. Ideation: New blog post ideas are added to the blog_ideas table with a pending or approved status. This is the starting point for the entire pipeline.
2. Automation: The content_worker.py script is run. It fetches all approved ideas from the database.
3. Content Generation: For each idea, the script generates a detailed prompt and uses the Gemini API to write the blog post content as HTML.
4. Scheduling: The newly generated post is saved to the blog_posts table with a scheduled status and a scheduled_for timestamp. The script automatically schedules one post per day to ensure consistent engagement.
5. State Update: The original idea in the blog_ideas table is updated to drafted and linked to the new post to prevent re-generation.

How to Use

1. Add a New Idea: To add a new topic to the content queue, insert a row into the blog_ideas table. Set the status to 'approved' to have the worker pick it up on its next run.

   -- Example:
   INSERT INTO blog_ideas (topic, category_id, suggested_products, status, notes)
   VALUES (
       'The Ultimate Guide to Smart Home Security',
       (SELECT id FROM blog_categories WHERE slug = 'tech-innovation'),
       '["Smart Doorbell", "Indoor Security Camera", "Smart Locks"]',
       'approved',
       'Focus on ease of installation and privacy features.'
   );
   

2. Run the Content Worker: Execute the script from the /var/www/ directory. Ensure your GEMINI_API_KEY is set in the .env file.

   # Navigate to the script's location
   cd /var/www/Fulfilora
   
   # Run the script using its virtual environment
   ./content_worker_venv/bin/python ./content_worker.py