geoflow-content-automation

# GEOFlow Content Automation > Skill by [ara.so](https://ara.so) — Daily 2026 Skills collection. GEOFlow is an open-source PHP/PostgreSQL system for automated GEO/SEO content production. It chains model configuration, material management, task scheduling, draft review, and front-end publishing into a single pipeline. It supports any OpenAI-compatible API, runs on Docker Compose, and exposes both a REST API and a CLI. --- ## Installation ### Docker (Recommended) ```bash git clone https://github.com/yaojingang/GEOFlow.git cd GEOFlow cp .env.example .env # Edit .env — set APP_SECRET_KEY, SITE_URL, and DB credentials vi .env # Start web + postgres + scheduler + worker docker compose --profile scheduler up -d --build # Front-end open http://localhost:18080 # Admin panel open http://localhost:18080/geo_admin/ ``` ### Local PHP Server ```bash git clone https://github.com/yaojingang/GEOFlow.git cd GEOFlow export DB_DRIVER=pgsql export DB_HOST=127.0.0.1 export DB_PORT=5432 export DB_NAME=geo_system export DB_USER=geo_user export DB_PASSWORD=geo_password export APP_SECRET_KEY=$(openssl rand -hex 32) export SITE_URL=http://localhost:8080 php -S localhost:8080 router.php open http://localhost:8080/geo_admin/ ``` **Requirements:** PHP 7.4+, extensions `pdo_pgsql` and `curl`, PostgreSQL instance. --- ## Environment Variables ```dotenv # .env (copy from .env.example) HOST_PORT=18080 SITE_URL=http://localhost:18080 APP_SECRET_KEY=$APP_SECRET_KEY # 32+ char random string — never hardcode CRON_INTERVAL=60 # Scheduler poll interval in seconds TZ=Asia/Shanghai # Database DB_DRIVER=pgsql DB_HOST=postgres DB_PORT=5432 DB_NAME=geo_system DB_USER=geo_user DB_PASSWORD=$DB_PASSWORD # Set via environment, not hardcoded ``` Generate a secure key: ```bash openssl rand -hex 32 # or php -r "echo bin2hex(random_bytes(32));" ``` --- ## Default Credentials After first boot, log in at `/geo_admin/` with: - **Username:** `admin` - **Password:** `admin888` **Change both immediately** and rotate `APP_SECRET_KEY` before any public deployment. --- ## Docker Services | Service | Purpose | Profile | |---|---|---| | `web` | Front-end + admin HTTP | always | | `postgres` | PostgreSQL database | always | | `scheduler` | Scans tasks, writes job queue | `scheduler` | | `worker` | Calls AI API, generates content | `scheduler` | ```bash # Web only (no generation) docker compose up -d # Full stack docker compose --profile scheduler up -d # Logs docker compose logs -f worker docker compose logs -f scheduler # Restart a single service docker compose restart worker # Stop everything docker compose --profile scheduler down ``` --- ## Content Generation Pipeline ``` Admin: configure model + prompts + materials ↓ Create Task (title library, model, prompt, image library, publish rules) ↓ Scheduler (bin/cron.php) → writes to job_queue table ↓ Worker (bin/worker.php) → calls AI API → generates article body ↓ Optional: insert images, build SEO meta ↓ Article enters draft → review → publish states ↓ Front-end renders article + SEO/OG tags ``` --- ## Admin Workflow ### 1. Add an AI Model Navigate to **AI配置中心 → AI模型管理** and fill in: - API Base URL (e.g. `https://api.openai.com/v1`) - Model ID (e.g. `gpt-4o`) - API Key (stored encrypted; reference `$AI_API_KEY` in your env) ### 2. Create Materials - **标题库** — Title pool the task draws from - **图片库** — Image pool for article illustrations - **知识库** — Reference knowledge injected into prompts - **提示词模板** — Prompt templates with placeholders ### 3. Create a Task Navigate to **任务管理 → 新建任务**: ``` Title Library → selects article titles Model → which AI model to call Prompt → which prompt template Image Library → optional illustration set Publish Mode → draft | auto-publish Schedule → immediate | cron-based ``` ### 4. Monitor Execution - **任务管理** — Task status, retry controls - **文章管理** — Draft/review/published states - **审核中心** — Review queue for human approval --- ## CLI (`bin/geoflow`) The CLI is the programmatic interface used by automation scripts and the companion skill [`yaojingang/yao-geo-skills`](https://github.com/yaojingang/yao-geo-skills). ```bash # List available commands php bin/geoflow help # Check task status php bin/geoflow task:status --task-id=<id> # Create a task php bin/geoflow task:create \ --title-library=<id> \ --model=<id> \ --prompt=<id> \ --publish-mode=draft # Upload an article draft php bin/geoflow article:create \ --title="Article Title" \ --body="<markdown content>" \ --status=draft # Review and publish an article php bin/geoflow article:review --article-id=<id> --action=approve php bin/geoflow article:publish --article-id=<id> # Run scheduler manually (one cycle) php bin/cron.php # Run worker manually (processes one job then exits) php bin/worker.php --once ``` --- ## REST API (`/api/v1`) All endpoints require Bearer token authentication. ### Generate an API Token ```bash php bin/api/create_token.php --name="my-integration" # Outputs token — store in $GEOFLOW_API_TOKEN ``` ### Common Endpoints ```bash BASE=http://localhost:18080/api/v1 TOKEN=$GEOFLOW_API_TOKEN # List tasks curl -H "Authorization: Bearer $TOKEN" "$BASE/tasks" # Get task detail curl -H "Authorization: Bearer $TOKEN" "$BASE/tasks/<id>" # Create task curl -X POST \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{ "title_library_id": 1, "model_id": 2, "prompt_id": 3, "publish_mode": "draft" }' \ "$BASE/tasks" # List articles curl -H "Authorization: Bearer $TOKEN" "$BASE/articles" # Publish article curl -X POST \ -H "Authorization: Bearer $TOKEN" \ "$BASE/articles/<id>/publish" # Check job queue curl -H "Authorization: Bearer $TOKEN" "$BASE/jobs?status=pending" ``` --- ## PHP Code Examples ### Connect to the Database (using GEOFlow's own layer) ```php <?php // includes/db_support.php provides the connection helper require_once __DIR__ . '/includes/db_support.php'; $pdo = get_db_connection(); // Returns a PDO instance // Always use prepared statements $stmt = $pdo->prepare("SELECT * FROM articles WHERE status = :status ORDER BY created_at DESC LIMIT 10"); $stmt->execute([':status' => 'published']); $articles = $stmt->fetchAll(PDO::FETCH_ASSOC); ``` ### Call the Article Service ```php <?php require_once __DIR__ . '/includes/article_service.php'; // Create a draft $articleId = create_article([ 'title' => 'My Article Title', 'body' => '## Introduction\n\nContent here...', 'status' => 'draft', 'task_id' => 42, 'seo_title' => 'My Article Title | Site Name', 'seo_desc' => 'Short description for search engines.', ]); // Approve and publish approve_article($articleId); publish_article($articleId); ``` ### Trigger the AI Engine Directly ```php <?php require_once __DIR__ . '/includes/ai_engine.php'; // Execute a single task job (task_id, job_id) $result = run_task_job(taskId: 5, jobId: 101); if ($result['success']) { echo "Article created: " . $result['article_id']; } else { echo "Error: " . $result['error']; } ``` ### Queue a Job Manually ```php <?php require_once __DIR__ . '/includes/job_queue_service.php'; // Claim next available job (used by worker) $job = claim_next_job(); if ($job) { try { // ... process the job ... complete_job($job['id']); } catch (Exception $e) { fail_job($job['id'], $e->getMessage()); // Eligible jobs will be retried by scheduler } } // Manually enqueue a task enqueue_task_jobs(taskId: 5); ``` ### Custom Prompt Template in PHP ```php <?php // Prompt templates support placeholder substitution $template = "You are an SEO writer. Write a 600-word article about: {{title}}\n\nBackground knowledge:\n{{knowledge}}"; $prompt = str_replace( ['{{title}}', '{{knowledge}}'], [$articleTitle, $knowledgeBaseContent], $template ); // Pass to AI service require_once __DIR__ . '/includes/ai_service.php'; $response = call_ai_model(modelId: 2, prompt: $prompt); echo $response['content']; ``` --- ## Directory Reference ``` GEOFlow/ ├── index.php Front-end article list ├── article.php Article detail page (SEO + OG) ├── router.php Local dev router for php -S ├── admin/ All admin UI pages ├── api/v1/index.php REST API single entry point ├── bin/ │ ├── geoflow CLI entrypoint │ ├── cron.php Scheduler (run every CRON_INTERVAL seconds) │ └── worker.php AI generation worker (long-running) ├── includes/ │ ├── config.php Global config + constants │ ├── database.php Front-end data access │ ├── ai_engine.php Core generation orchestration │ ├── ai_service.php OpenAI-compatible HTTP client │ ├── job_queue_service.php Claim / complete / fail / retry │ ├── task_service.php Task CRUD │ ├── article_service.php Article lifecycle │ └── api_auth.php Bearer token auth └── docs/ Deployment + API + FAQ docs ``` --- ## Common Patterns ### Auto-publish without review When creating a task, set `publish_mode = auto`. The worker will call `publish_article()` immediately after generation, skipping the review queue. ### Batch retry failed jobs ```bash # Via CLI php bin/geoflow job:retry-failed --task-id=<id> # Via database (emergency) psql $DATABASE_URL -c "UPDATE job_queue SET status='pending', attempts=0 WHERE status='failed' AND task_id=5;" ``` ### Run scheduler outside Docker ```bash # Add to crontab — runs every minute * * * * * cd /path/to/GEOFlow && php bin/cron.php >> /var/log/geoflow-cron.log 2>&1 # Or loop manually while true; do php bin/cron.php; sleep 60; done ``` ### Database maintenance ```bash php bin/db_maintenance.php --vacuum php bin/db_maintenance.php --clean-old-jobs --days=30 ``` --- ## Troubleshooting | Symptom | Likely Cause | Fix | |---|---|---| | Worker generates nothing | Scheduler not running | Start with `--profile scheduler` or run `cron.php` | | `pdo_pgsql` not found | Extension missing | Add `extension=pdo_pgsql` to `php.ini` or install `php-pgsql` | | 500 on admin pages | DB not initialized | Let the web container complete its entrypoint migration on first boot | | AI calls return 401 | Wrong API key | Re-enter key in **AI模型管理**; check key has no leading/trailing spaces | | Articles stuck in draft | Auto-publish off | Set `publish_mode=auto` on the task, or manually approve in 审核中心 | | Jobs pile up in queue | Worker crashed | `docker compose restart worker`; check `docker compose logs worker` | | CSRF token mismatch | Session expired | Re-login to admin; ensure `APP_SECRET_KEY` is stable across restarts | ### Useful Log Commands ```bash # All services docker compose logs -f # Only errors docker compose logs worker 2>&1 | grep -i error # Scheduler activity docker compose logs scheduler | tail -50 # Check pending jobs in DB docker compose exec postgres psql -U geo_user -d geo_system \ -c "SELECT status, COUNT(*) FROM job_queue GROUP BY status;" ``` --- ## Security Checklist - [ ] Change default `admin` / `admin888` credentials immediately