pbs-obsidian-vault/PBS/Tech/Projects/content-hub-phase5-architecture.md

---
project: content-hub-phase5-architecture
type: project-plan
status: active
tags:
  - pbs
  - flask
  - mysql
  - n8n
  - instagram
  - automation
  - docker
  - traefik
created: 2026-03-19
updated: 2026-03-19
path: PBS/Tech/Projects/
---

# PBS Content Hub Phase 5 — Architecture & Planning Decisions

## Purpose

This document captures all architecture and planning decisions made during
the Phase 5 planning session. It serves as a handoff doc for Claude Code to
implement the Content Hub refactor.

---

## Context

### Phases 1–4 Complete
- ✅ Phase 1 — MySQL schema with single `instagram_posts` table
- ✅ Phase 2 — Reply workflow refactored with error notifications
- ✅ Phase 3 — WordPress → MySQL sync via webhook
- ✅ Phase 4 — Instagram reel publish webhook capturing reel IDs

### Two Existing Apps Being Merged

**PBS-API (production, running on Linode):**
- Location: `/opt/docker/pbs-api/app.py`
- Container: `pbs-api` on internal Docker network
- Internal URL: `http://pbs-api:5000`
- External URL: `https://plantbasedsoutherner.com/api/...` (via Traefik)
- Database: MySQL `pbs_automation`, connects as user `pbs_api`
- Auth: API key via `X-API-Key` header on all endpoints (except
`/api/health`), Traefik BasicAuth on `/pbsadmin` routes
- Current endpoints:
  - `GET /api/health` — health check (no auth)
  - `POST /api/instagram-mapping` — save/update recipe mappings (upsert)
  - `GET /api/instagram-mapping/{keyword}` — lookup recipe by keyword
  - `GET /api/recipes/all` — bulk fetch all recipes
  - `GET /pbsadmin/instagram/recipes` — Jenny's admin interface (inline
keyword editing, search/filter, stats)
- MySQL tables (`pbs_automation`):
  - `instagram_recipes` — post_id (PK), recipe_title, recipe_url, keyword,
created_at, updated_at
  - `instagram_posts_processed` — instagram_post_id (PK), last_checked,
comment_count
  - `instagram_replies` — id (auto PK), instagram_comment_id (unique),
instagram_post_id, comment_text, reply_text, recipe_url, sent_at
- Security: API key stored in `.env` file, Traefik BasicAuth for
`/pbsadmin` with username `jenny`

**PBS Content Hub (local development, Travis's machine):**
- CLI + Flask GUI using SQLite/SQLAlchemy
- CLI commands: `pbs init`, `pbs convert`, `pbs scan`, `pbs archive`, `pbs
status`, `pbs list`, `pbs shell`, `pbs activate/deactivate`
- Flask GUI: Dashboard, Project Detail (Overview, Checklist, Description
Builder), Library CRUD
- Models: `Project`, `VideoFile`, `ChecklistItem`, `LibraryItem`
- Library uses single `content_blocks` table: `id, name, content,
block_type, sort_order`

---

## Architecture Decisions

### Decision 1: Single Container (pbs-hub)

**Decision:** Merge PBS-API and Content Hub into ONE Flask app in ONE
container.

**Rationale:**
- Two containers is over-engineered for current scale (team of 2 +
assistant, one automation consumer)
- The admin page Jenny uses today belongs in the same UI as the new Content
Hub features
- Avoids maintaining two Flask apps, two Dockerfiles, two deployments
- The code cleanliness concern is solved with Flask Blueprints, not
separate containers
- If a two-container split is ever needed (membership site, public API),
the service layer architecture makes extraction straightforward later

### Decision 2: Flask Blueprints for Code Organization

**Decision:** Use Flask Blueprints to separate API routes from web UI
routes.

**Target project structure:**
```
pbs-hub/
├── app.py                (creates Flask app, registers blueprints)
├── blueprints/
│   ├── api/              (all JSON endpoints — n8n, CLI, auto-match)
│   │   ├── routes.py
│   │   └── ...
│   ├── web/              (all HTML pages — Jenny's UI, dashboard, reels)
│   │   ├── routes.py
│   │   └── templates/
│   └── ...
├── services/             (business logic — match routine, recipe CRUD)
├── models/               (SQLAlchemy models)
└── ...
```

### Decision 3: Service Layer Refactor

**Decision:** Extract all SQLAlchemy logic into a service layer.

- Flask routes (both API and web) call service functions, never query the
DB directly
- CLI will call the API endpoints over HTTPS (not import service functions)
- This is the key enabler for the CLI → API migration

**Example pattern:**
```python
# services/project_service.py
def get_all_projects():
    return Project.query.all()

def create_project(title):
    project = Project(title=title)
    db.session.add(project)
    db.session.commit()
    return project

# blueprints/api/routes.py
@api_bp.route('/api/projects', methods=['GET'])
def api_list_projects():
    projects = get_all_projects()
    return jsonify([p.to_dict() for p in projects])

# blueprints/web/routes.py
@web_bp.route('/projects')
def list_projects():
    projects = get_all_projects()
    return render_template('projects.html', projects=projects)
```

### Decision 4: SQLite → MySQL Migration

**Decision:** Replace SQLite with MySQL. SQLAlchemy abstracts the engine,
so models barely change — mostly a connection string swap + migration.

- Content Hub connects to the same MySQL instance as the current PBS-API
- Database: `pbs_automation` (existing) or new `pbs_content_hub` database
(TBD)
- MySQL user: `pbs_api` (existing, has access to `pbs_automation.*`)

### Decision 5: CLI Talks to API Over HTTPS

**Decision:** The pbsVM CLI will make HTTPS calls to the Content Hub API
instead of direct SQLAlchemy queries.

- Auth: API key in a local config file or environment variable
- Uses `httpx` (or `requests`) to call API endpoints
- No database credentials on Travis's local machine
- CLI needs network access to push data (file management operations remain
local)

---

## UI & Feature Decisions

### Decision 6: Recipe-to-Reel Linking Strategy

**Decision:** Manual selection in Content Hub.

Jenny selects the recipe from a dropdown when creating a reel record. This
auto-populates the post ID, keyword, and URL. No caption parsing, no
fragile keyword matching.

**Current process being replaced:** Jenny/assistant emails Travis for the
post ID → Travis runs MySQL query → sends back ID. The Content Hub removes
Travis from this loop entirely.

### Decision 7: Reel Record Lifecycle (Two-Stage)

**Decision:** Local-first, then matched to live data.

- **Stage 1 (Pre-publish):** Jenny creates a reel record in the Content Hub
before anything exists on Instagram. Working title, linked recipe, keyword,
built caption. No Instagram reel ID yet.
- **Stage 2 (Post-publish):** Instagram webhook fires → n8n writes reel ID
to `instagram_posts` → hub reads that table and matches to local record.

### Decision 8: n8n Stays Decoupled

**Decision:** n8n is dumb to the hub.

- n8n writes to `instagram_posts` in MySQL — that's its only job
- n8n does NOT call Content Hub API or know about hub reel records
- The Content Hub reads from `instagram_posts` to display what's live
- The hub owns all matching logic
- **One exception:** n8n sends a simple ping to `POST /api/match/run` after
new inserts to trigger the auto-match routine

### Decision 9: Auto-Match Routine

**Decision:** Triggered by n8n ping after new `instagram_posts` insert.

**Logic:**
```
For each hub reel record in "Ready" status (no reel ID linked):
    → Look in instagram_posts for unmatched rows where:
        - post_id matches (recipe link) OR keyword matches
    → Single confident match → auto-link, status → "Live — Matched"
    → Multiple matches → flag as "Needs Review"
    → No match → leave as-is
```

Manual match UI is the fallback for anything auto-match can't resolve.

### Decision 10: Recipe Dropdown Source

**Decision:** MySQL recipes table (not WordPress REST API). Already synced
via webhook, faster, no external calls during form interactions.

### Decision 11: Reel Detail Page — Tabbed Layout

```
[ Overview ]  [ Caption Builder ]
```

**Overview tab:**
- Reel Title (free text)
- Recipe dropdown (from MySQL) → auto-fills keyword + URL
- Keyword (editable — this IS the recipe review/confirm step)
- Recipe URL (read-only)
- Status indicator
- Save / Delete

**Caption Builder tab (separate tab within reel detail):**
- Select library blocks (hashtags, links, CTAs, about snippets)
- Preview assembled caption
- Copy to clipboard

### Decision 12: Recipe Review UI

**Decision:** Not a separate screen. Baked into the reel creation flow —
when Jenny selects a recipe from the dropdown, she sees the record and
confirms/edits the keyword right there.

### Decision 13: Manual Match Correction

A "Link Reel" button on flagged records. Jenny selects from unmatched
`instagram_posts` rows to connect the dots.

---

## Reel Status States

1. **Draft** — reel record created, no recipe linked yet
2. **Ready** — recipe linked, keyword confirmed, caption built. Waiting to
be posted
3. **Live — Matched** — Instagram webhook received, reel ID attached,
auto-reply active
4. **Live — Needs Review** — auto-match couldn't confidently link, Jenny
needs to manually match

---

## Reel List View

Data points per reel (layout TBD — table vs cards, getting Jenny's
feedback):
- Reel title
- Linked recipe name (or "No Recipe" warning)
- Keyword
- Status (color dot + label)
- Date created or last updated

---

## Content Hub Navigation (from earlier design doc)

```
[ Dashboard ]  [ Projects ]  [ Reels ]  [ Library ]  [ Status ]  [ Settings
]
```

| Tab | Primary User | Description |
|-----|-------------|-------------|
| Dashboard | Both | Overview of recent projects and reels |
| Projects | Travis | Video project management (existing pbsVM GUI) |
| Reels | Jenny | Reel manager, caption builder, publishing workflow |
| Library | Both | Shared hashtags, links, about blocks, CTAs |
| Status | Both | Automation health, comment/reply counts, healthcheck |
| Settings | Travis | Global config, API keys, default checklist |

---

## Target Automation Flow

```
Jenny creates reel record in hub → selects recipe → builds caption
    ↓
Jenny posts reel to Instagram
    ↓
Instagram webhook → n8n → writes reel ID to instagram_posts
    ↓
n8n pings hub (POST /api/match/run)
    ↓
Hub auto-match runs:
    - Finds match → links record → status = Live — Matched ✅
    - No confident match → status = Needs Review 🟡
    ↓
Comment comes in on Instagram → n8n looks up instagram_posts → sends DM +
reply
    ↓
Dashboard shows green across the board 🟢
```

---

## Refactor Steps (Implementation Order)

1. **Extract service layer** from current Content Hub SQLAlchemy code
2. **Restructure into Blueprints** (api/ and web/)
3. **Add API routes** alongside existing GUI routes
4. **Migrate PBS-API endpoints** into the new Blueprint structure
5. **Migrate Jenny's admin page** into the Content Hub web UI
6. **Swap SQLite → MySQL** (connection string + migration)
7. **Refactor CLI** to use `httpx` calls to API instead of direct SQLAlchemy
8. **Build Reels tab** (creation form, recipe dropdown, status lifecycle)
9. **Build Caption Builder tab** within reel detail
10. **Build auto-match routine** (`POST /api/match/run`)
11. **Build manual match UI** (fallback for auto-match failures)
12. **Build Status Dashboard** (per-reel health + system health — details
TBD)
13. **Deploy to staging** → test → deploy to production
14. **Update n8n** to point at new container URL + add match/run ping
15. **Retire PBS-API container**

---

## Open Questions (Next Planning Session)

- [ ] Reel list layout: table rows vs cards (get Jenny's feedback)
- [ ] Jenny's assistant access: same login or separate user accounts?
- [ ] Status Dashboard UI details: per-reel health indicators + system
health panel
- [ ] Database schema for hub reel records table
- [ ] Define green/yellow/red status thresholds in detail
- [ ] Database strategy: extend `pbs_automation` or create new
`pbs_content_hub` database?

---

*Project: Plant Based Southerner Content Hub*
*Planning Session: March 19, 2026*
*Participants: Travis & Claude*
...sent from Jenny & Travis