herbygitea/pbs-obsidian-vault
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
main
pbs-obsidian-vault/PBS/Tech/Projects/content-hub-database-schema.md

7.0 KiB
Raw Permalink Blame History

project type status tags created updated path
content-hub-database-schema project-plan active
pbs
flask
mysql
n8n
instagram
automation
2026-03-20 2026-03-20 PBS/Tech/Projects/

PBS Content Hub — Database Schema & Table Relationships

Purpose

This document defines the database schema and table relationships for the PBS Content Hub. It supplements the Phase 5 Architecture & Planning Decisions doc and serves as a handoff for Claude Code implementation.

Three Data Domains Being Unified

1. PBS Video Manager (migrating from local SQLite to MySQL)

  • Local app being migrated to the server as part of the Content Hub
  • projects table stores all project info (name, status, description, etc.)
  • Projects are the central concept — a project can be a reel, YouTube video, or any future content type

2. PBS Recipe App (live on production MySQL)

  • Already in MySQL (pbs_automation database)
  • pbs_recipes table — stores WordPress recipe data
  • Powers Jenny's admin page and n8n recipe lookups

3. Instagram Reel Workflow (currently in n8n data tables)

  • Being migrated from n8n internal storage to MySQL
  • Becomes the generic platform_posts table to support Instagram now and YouTube/other platforms later

Relationship Model

projects is the hub that connects everything. It reaches out to both pbs_recipes and platform_posts. Neither of those tables needs to reference back to projects or to each other.

project_types
    id ←─── type_id
                  \
                  projects
                  /      \
    recipe_id ───→        ←─── platform_post_id
    pbs_recipes              platform_posts

Why platform_posts has no FK to projects

The platform table is just platform data. There is no scenario where platform_posts needs to look up project info. Projects owns the relationship to both recipes and platform posts.

Why pbs_recipes has no FK to projects

Same reasoning. Recipes exist independently in WordPress. Projects link to them, not the other way around.

Table Definitions

pbs_recipes (exists in MySQL, no changes)

Column Type Notes
post_id INT, PK WordPress post ID
recipe_title VARCHAR(255)
recipe_url VARCHAR(255)
keyword VARCHAR(100), nullable Trigger word for automation
created_at TIMESTAMP
updated_at TIMESTAMP auto-update

CRUD: Yes — existing Jenny admin page migrates into Content Hub.

project_types (new)

Column Type Notes
id INT, PK, auto increment
name VARCHAR(50) reel, youtube, etc.
created_at TIMESTAMP

CRUD: Yes — managed in Settings tab. Allows adding new content types without code changes.

projects (new — replaces local SQLite video_projects)

Column Type Notes
id INT, PK, auto increment
title VARCHAR(255) Working title
type_id INT, FK → project_types.id
status VARCHAR(50) draft, ready, live-matched, live-needs-review.
Kept as VARCHAR for now — will become a lookup table when Trello
integration is built
recipe_id INT, FK → pbs_recipes.post_id, nullable Linked recipe
platform_post_id INT, FK → platform_posts.id, nullable Linked
platform post
created_at TIMESTAMP
updated_at TIMESTAMP auto-update

CRUD: Yes — this is the main workspace in the Content Hub.

Note: Additional columns from the existing SQLite video_projects schema (description, file paths, checklist data, etc.) will be added separately. This doc focuses on the connecting fields.

platform_posts (new — replaces n8n insta_reel table)

Column Type Notes
id INT, PK, auto increment Internal ID
platform VARCHAR(50) instagram, youtube, tiktok, etc.
platform_post_id VARCHAR(50) Native ID from the platform. VARCHAR
because Instagram IDs overflow JavaScript integer limits
url VARCHAR(255), nullable Link to the post on the platform
published_at TIMESTAMP, nullable When it went live
created_at TIMESTAMP When we recorded it
updated_at TIMESTAMP auto-update

CRUD: Yes — view/manage platform data in the Content Hub.

n8n Lookup Path (Comment Reply Automation)

When n8n receives a comment on an Instagram reel and needs the keyword + recipe URL:

platform_posts (match on platform_post_id = reel ID from webhook)
    → projects (WHERE platform_post_id = platform_posts.id)
    → pbs_recipes (WHERE post_id = projects.recipe_id)
    → return keyword + recipe_url for the reply

If any join in the chain returns null, n8n triggers the existing error notification to Travis.

Auto-Match Routine

Triggered by n8n ping to POST /api/match/run after new platform_posts insert.

For each project in "Ready" status with no platform_post_id:
    → Look in platform_posts for unmatched rows (no project pointing at
them) where:
        - Matching criteria (post_id via recipe link, keyword, or other
heuristics)
    → Single confident match → update projects.platform_post_id, status →
"live-matched"
    → Multiple matches → status → "live-needs-review"
    → No match → leave as-is

Manual match UI is the fallback — Jenny selects from unmatched platform_posts to link to a project.

Error Checking / Healthcheck (Real-Time)

No separate table needed. The Status Dashboard computes checks on page load by querying the four tables:

  • Platform posts with no project linked (unmatched)
  • Projects in "ready" status with no platform_post_id for X days
  • Projects linked to a recipe that has no keyword
  • Broken join chains (project → recipe or project → platform_post returns null)

Displayed as green/yellow/red indicators per project and per system on the Status tab.

CRUD Summary

Table CRUD Location Primary User
pbs_recipes Reels tab (migrated admin page) Jenny
project_types Settings tab Travis
projects Projects + Reels tabs Both
platform_posts Status tab / Reels tab Both

Existing Tables to Keep (n8n automation logging)

These tables remain in pbs_automation and are not part of the Content Hub schema, but n8n continues to use them:

  • instagram_posts_processed — tracks processed Instagram posts (prevents duplicate processing)
  • instagram_replies — comment reply log for debugging/analytics

Migration Notes

  • pbs_recipes already exists in MySQL — no migration needed
  • projects replaces local SQLite video_projects — SQLAlchemy model update + data migration
  • platform_posts replaces n8n internal insta_reel data table — data migration from n8n to MySQL
  • project_types is new — seed with initial values (reel, youtube)
  • All new tables go in the pbs_automation database (or new pbs_content_hub database — TBD, still an open question)

Project: Plant Based Southerner Content Hub Planning Session: March 20, 2026 Participants: Travis & Claude