---
project: content-hub-database-schema
type: project-plan
status: active
tags:
  - pbs
  - flask
  - mysql
  - n8n
  - instagram
  - automation
created: 2026-03-20
updated: 2026-03-20
path: 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*