# CMORoom MySQL schema map

All tables use the `cmoroom_` prefix. Scripts:

| Script | Purpose |
| --- | --- |
| `001_platform.sql` | Auth, media, site settings, audit |
| `002_content.sql` | Pages, nav, and content entities (API-shaped columns) |
| `003_seed_pages.sql` | Known public page slugs + default site settings |
| `004_seed_admin.sql` | Dev admin user (not for production passwords) |
| `005_api_entities.sql` | Brands, testimonials, gallery (also covered in 002) |
| `006_seed_api_content.sql` | Catalog seed generated by `npm run db:seed` |
| `create-all.sql` | Runs schema + page/admin seeds; then run `db:seed` |

Setup:

```bash
npm run db:setup   # ensure schema + seed catalog from mocks
```

## Table → design / API source

| Table | Design / API |
| --- | --- |
| `cmoroom_users` | `dashboard/login.php` |
| `cmoroom_sessions` | DB-backed HTTP-only admin sessions |
| `cmoroom_media_assets` | Upload fields across editors; `/api/admin/media*` (S3 or local `public/uploads`) |
| — | `008_media_original_name.sql` adds `original_name` on existing DBs |
| `cmoroom_site_settings` | `dashboard/settings.php` (+ footer about/social JSON) |
| `cmoroom_audit_logs` | Admin accountability; `/api/admin/dashboard/activity` |
| `cmoroom_notifications` | Admin header bell; `/api/admin/notifications` (+ `read-all`) — TW-461 / `010_notifications.sql` |
| `cmoroom_pages` | `dashboard/pages.php` |
| `cmoroom_page_sections` | Per-page editors + legal meta config |
| `cmoroom_page_section_paragraphs` | Ordered body paragraphs |
| `cmoroom_page_seo` | `dashboard/seo.php` / page SEO |
| `cmoroom_team_members` | `/api/public/team`, `team-form.php` |
| `cmoroom_page_team_members` | Ordered team picks on CMORoom / AdMedia |
| `cmoroom_menu_items` | `header-menu.php`; `/api/admin/appearance/header-menu` + `/api/public/header-menu` |
| `cmoroom_footer_columns` / `cmoroom_footer_links` | `footer-menu.php`; `/api/admin/appearance/footer-menu` + `/api/public/footer-menu` (about/social in `site_settings`) |
| `cmoroom_faq_items` | `/api/public/faqs` |
| `cmoroom_policy_sections` | `/api/public/legal/[slug]` |
| `cmoroom_brands` | `/api/public/brands` |
| `cmoroom_testimonials` | `/api/public/testimonials` |
| `cmoroom_gallery_items` | `/api/public/gallery` |
| `cmoroom_cities` | `/api/public/cities`; dashboard summary “Cities Hosting” |
| `cmoroom_event_categories` | Event category select |
| `cmoroom_events` | `/api/public/events` (+ city/detail); dashboard summary upcoming / last-month |
| `cmoroom_invitations` | `/api/public/invitations` POST + `/api/admin/invitations*` + `/api/admin/dashboard/*` |
| — | `007_invitations_columns.sql` adds `phone` + `admin_note` on existing DBs |
| — | `008_media_original_name.sql` adds `original_name` on existing DBs |
| — | `009_password_resets.sql` adds `cmoroom_password_resets` for forgot-password |
| — | `010_notifications.sql` adds `cmoroom_notifications` for admin header alerts |
| `cmoroom_insight_posts` | `/api/public/insights` |
| `cmoroom_industries` | `/api/public/industries` |
| `cmoroom_spotlight_episodes` | `/api/public/episodes`, `/spotlight/featured`, `/editorials/[slug]` (alias `/editorial/[slug]`); admin `/api/admin/spotlight-episodes`, `/api/admin/editorials/[slug]` |

## Editorial (editorial.php) = view of a spotlight episode (TW-448)

An editorial is not a separate table. It is the long-form page of one `cmoroom_spotlight_episodes` row,
stored in `editorial_json`, and shares the episode's `slug`, `is_visible` and `meta_*` SEO columns.
Create the episode with `POST /api/admin/spotlight-episodes` (TW-382); edit the long-form fields with either
the episode form (TW-382) or `PUT /api/admin/editorials/[slug]` (TW-448). Both merge into the same JSON.

| editorial.php field | Storage |
|---|---|
| `eyebrow`, `title`, `title_accent`, `standfirst`, `guest_role`, `author`, `author_role`, `published`, `updated`, `readtime`, `inline_img`, `img_caption`, `quote` | `editorial_json` (`eyebrow`, `title`, `titleAccent`, `standfirst`, `guestRole`, `author`, `authorRole`, `published`, `updated`, `readTime`, `inlineImage`, `imageCaption`, `quote`) |
| `intro[]`, `qa[]` (`question` + `answer[]`), `closing[]`, `takeaways[]` | `editorial_json.intro` / `qa` / `closing` / `takeaways` |
| rich body (episode form only) | `editorial_json.bodyHtml` — sanitized; replaces `intro[]` on the page when non-empty |
| `guest` | `editorial_json.guest` + `executive_name` |
| `video` | `editorial_json.videoUrl` + `youtube_url` (public embed URL built from either) |
| `slug` | `slug` (episode + editorial URL) |

The two sample posts in `editorial.php` (`brandi-stone-watkins`, `will-ferguson`) are seeded by `npm run db:seed`.

### Page seed scripts

| Script | Ticket |
| --- | --- |
| `sql/pages/cmoroom.sql` | TW-375 — CMORoom about page defaults |
| `sql/pages/admedia.sql` | TW-396 — AdMedia page defaults |
| `sql/pages/home.sql` | TW-393 — Home page defaults |
| `sql/pages/network.sql` | TW-399 — Network page defaults |
| `sql/pages/faq.sql` | TW-402 — FAQ page chrome |
| `sql/pages/privacy-policy.sql` | TW-387 — Privacy chrome |
| `sql/pages/terms.sql` | TW-388 — Terms chrome |

```bash
npm run db:seed:cmoroom
npm run db:seed:admedia
npm run db:seed:home
npm run db:seed:pages
```

CMORoom APIs (TW-374):

- `GET /api/public/pages/cmoroom`
- `GET /api/admin/pages/cmoroom` (auth)
- `PUT /api/admin/pages/cmoroom` (auth, save live)

AdMedia APIs (TW-379):

- `GET /api/public/pages/admedia`
- `GET /api/admin/pages/admedia` (auth)
- `PUT /api/admin/pages/admedia` (auth, save live)

Home APIs (TW-378):

- `GET /api/public/pages/home`
- `GET /api/admin/pages/home` (auth)
- `PUT /api/admin/pages/home` (auth, save live)

Network / FAQ / Legal APIs:

| Page | Seed | Public | Admin |
| --- | --- | --- | --- |
| Network (TW-380) | `sql/pages/network.sql` | `GET /api/public/pages/network` | `GET`/`PUT /api/admin/pages/network` |
| FAQ (TW-386) | `sql/pages/faq.sql` | `GET /api/public/pages/faq` | `GET`/`PUT /api/admin/pages/faq` |
| Privacy (TW-387) | `sql/pages/privacy-policy.sql` | `GET /api/public/pages/privacy-policy` | `GET`/`PUT /api/admin/pages/privacy-policy` |
| Terms (TW-388) | `sql/pages/terms.sql` | `GET /api/public/pages/terms` | `GET`/`PUT /api/admin/pages/terms` |

```bash
npm run db:seed:pages
```

FAQ Q&A is stored in `cmoroom_faq_items`. Network photos sync `cmoroom_gallery_items`. Privacy/Terms bodies use `cmoroom_policy_sections` + `legal_meta`.

## Notes

- v1 has no draft/publish workflow; use `is_visible` (and episode show_* / `is_featured` flags).
- Rich blocks live in `config_json` / `editorial_json` / `detail_json` / `feature_json`.
- Public asset paths are stored as URL columns (`image_url`, `logo_url`, …) for mock→DB parity; `*_media_id` FKs remain for S3 uploads later.
- Indexes cover slugs, FKs, and common list sorts.

## Deletes (TW-449)

All deletes are **hard deletes**, admin-only, and write a `cmoroom_audit_logs` row (`*_DELETED`, actor = the
signed-in admin, `meta_json.snapshot` = the deleted row) in the same transaction as the delete. Soft-delete was
not used: every reference is either cleaned up or blocks the delete, and a soft-deleted row would keep its slug
taken and need an extra filter on every public query.

| Endpoint | Action | References |
|---|---|---|
| `DELETE /api/admin/team-members/:id` | `TEAM_MEMBER_DELETED` | Page picks (`cmoroom_page_team_members`) cascade; the pages are returned as `removedFromPages`. Group positions are renumbered. |
| `DELETE /api/admin/events/:id` | `EVENT_DELETED` | Nothing references an event; `cmoroom_event_seo` / `cmoroom_event_past_rooms` cascade. |
| `DELETE /api/admin/insight-posts/:id` | `INSIGHT_POST_DELETED` | Stories cascade; other posts' featured slides / related posts pointing at it cascade and are returned as `detachedFrom`. |
| `DELETE /api/admin/spotlight-episodes/:id` | `SPOTLIGHT_EPISODE_DELETED` | The editorial is in the same row. Hub picks (ids inside `cmoroom_page_sections` JSON) are removed; returned as `removedFromHub`. |
| `DELETE /api/admin/media/:id` | `MEDIA_DELETED` | **Blocked (409)** while referenced by any FK to `cmoroom_media_assets` or by its URL / S3 key in a content URL, image, JSON or HTML column (details list each place). Then: DB row + audit commit, then the S3 object; a failed S3 delete returns `objectDeleted: false` and is logged (orphaned object, never a dangling row). 503 when S3 is not configured. |

Each delete revalidates the public paths that showed the item (media is unreferenced by then, so none).
Redis is not used as a cache yet, so there is nothing to invalidate there.