---
version: 1.0.0
name: buyerkiosk-support-article-prod-deploy
description: |
  Ship BuyerKiosk support article authoring work (kiosk_buykiosk.support_articles
  + support_categories rows) from local dev DB to production. Use when: (1) user
  asks "how do I get these articles into prod" / "deploy these support articles"
  / "push these to production", (2) you've just authored or edited rows in
  support_articles or support_categories locally and the work needs to land on
  the live site, (3) a previous session created new article rows and the user
  wants a deploy artifact (not a manual copy-paste workflow), (4) reviewing why
  a naive "mysqldump --tables support_articles | mysql prod" approach is wrong
  for this table. The naive approach kills prod's view_count, helpful/notHelpful
  reactions, comments, manual status flips (draft→published in admin UI), and
  collides on autoincrement IDs that drift between local and prod. The correct
  pattern is slug-based, idempotent INSERT...ON DUPLICATE KEY UPDATE with FK
  resolution via SELECT-by-slug subqueries. Also covers the 3-bucket deploy
  split (DB / image files / template change) and the recategorization recipe.
author: Claude Code
tags: [buyerkiosk, support-articles, deploy, sql, idempotent]
date: 2026-05-23
---

# BuyerKiosk Support Article Production Deploy Pattern

## Problem

You've authored or edited support articles in the local `kiosk_buykiosk.support_articles`
table (via the admin UI, a previous Claude session, or direct PHP scripts) and now need
to ship the work to production. The naive approaches all have failure modes:

| Naive approach | Why it breaks |
|---|---|
| `mysqldump support_articles \| mysql prod` | Overwrites prod-only data: `view_count`, `userReaction` rows, `commentCount`, manual `status` flips, prod's `author_id` mapping. Also crashes on PK collisions because local autoincrement IDs drift from prod. |
| Direct `INSERT` with hardcoded `id`/`category_id`/`author_id` | Prod's autoincrement counters differ. The new "QuickBooks" category might be id=23 locally but id=27 on prod. The author_id you wrote locally (e.g. 28) may not exist on prod or belong to a different user. |
| Manual admin-UI paste (slug + markdown for each) | Works but is slow, error-prone for 5+ articles, and easy to skip "publish" toggles or category reassignments. |
| Conductor migration JSON | The migration runner is designed for *schema* changes, runs against every store DB by default, and is overkill for central-DB content. |

The right pattern is a **slug-based, idempotent SQL script** that resolves all foreign
keys via slug/username subqueries at apply time.

## Context / Trigger Conditions

Apply this skill when ALL of these are true:

- Work was done in local `kiosk_buykiosk` DB (specifically `support_articles` and/or
  `support_categories`) and now needs to reach production.
- Production has the **same schema** (this is content deploy, not schema migration).
- The article(s) may or may not exist on prod yet — you don't know, and you don't
  want to find out before generating the script.
- Image files referenced by the markdown need to deploy too (they live at
  `public_html/images/support/articles/`).
- A template/CSS change to `userfrosting/templates/themes/default/support/article.html`
  may also be in scope (image-size cap, lightbox tweaks, etc.).

## The 3-Bucket Deploy Split

A support-article deploy almost always has three categories of change. Each travels
through a **different** deploy path:

| Bucket | What | How it reaches prod |
|---|---|---|
| **1. DB content** | Rows in `support_articles` and `support_categories` | Slug-based SQL script (this skill) |
| **2. Image files** | New PNG/JPG in `public_html/images/support/articles/` | rsync / scp / git-tracked deploy (whatever the project already does) |
| **3. Template / CSS** | Edits to `userfrosting/templates/themes/default/support/article.html` and friends | Normal git push → deploy pipeline |

Always confirm the user has a plan for all three, not just the SQL.

**Deploy order matters**: images → template → SQL. If the SQL lands first and a user
opens the new article before the image files arrive, they see broken `<img>` tags.

## Solution: Generate a Slug-Based Idempotent SQL Script

### Step 1 — Inventory the changes

Before generating SQL, list out:

```
- New categories (by slug): e.g. 'quickbooks'
- New articles (by slug): e.g. 'customize-customer-disclosure', 'quickbooks-overview'
- Edited articles (by slug): e.g. 'customize-printed-receipt'
- Recategorizations (slug → new category slug): e.g. 9 'quickbooks-*' slugs into 'quickbooks'
```

Slug, slug, slug. **Never refer to a row by local id.**

### Step 2 — Generate the SQL via PHP/PDO

Use PHP with PDO so you get proper `quote()` escaping for the multi-paragraph markdown
bodies (which contain backticks, single quotes, em-dashes, embedded `*`, etc.).

```php
$pdo = new PDO('mysql:host=localhost;dbname=kiosk_buykiosk;charset=utf8mb4', $u, $p, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
]);

function q(PDO $pdo, $v) { return $v === null ? 'NULL' : $pdo->quote($v); }

// Read each article's current content from local DB
$row = $pdo->prepare("SELECT title, content_md FROM support_articles WHERE id = :i");
// ...

// Emit SQL with q($pdo, $contentMd) — never string-concat raw markdown.
```

Put the script in `userfrosting/scripts/` so it's discoverable next to similar admin scripts.

### Step 3 — Category upsert (slug-keyed)

```sql
INSERT INTO support_categories
    (name, slug, description, icon, sort_order, parent_id, is_active, created_at, updated_at)
VALUES (
    'QuickBooks',
    'quickbooks',
    '...',
    'fa-file-invoice-dollar',
    12, NULL, 1, NOW(), NOW()
)
ON DUPLICATE KEY UPDATE
    name = VALUES(name),
    description = VALUES(description),
    icon = VALUES(icon),
    is_active = 1,
    updated_at = NOW();
```

`slug` is `UNIQUE` on `support_categories`, so ON DUPLICATE KEY does the right thing.

### Step 4 — Article upsert (slug-keyed, with FK-via-subquery)

```sql
INSERT INTO support_articles
    (category_id, title, slug, content_md, content_html, author_id,
     status, view_count, is_featured, created_at, updated_at)
VALUES (
    (SELECT id FROM support_categories WHERE slug = 'customer-checkin-kiosk' LIMIT 1),
    'Customize the Printed Receipt',
    'customize-printed-receipt',
    '<escaped markdown via PDO::quote>',
    NULL,
    COALESCE((SELECT id FROM kiosk_users.users WHERE username = 'rvanvuren' LIMIT 1), 1),
    'published', 0, 0, NOW(), NOW()
)
ON DUPLICATE KEY UPDATE
    content_md = VALUES(content_md),
    content_html = NULL,
    category_id = VALUES(category_id),
    updated_at = NOW();
```

**Author lookup must use the canonical `kiosk_users.users` table** with the
`username` column. The legacy `kiosk_users.uf_user` table still exists (with
column `user_name` — note the underscore) and is tempting because it's smaller,
but it's deprecated. New code should always resolve authors against
`kiosk_users.users WHERE username = '<handle>'`. The MySQL user running the
script needs `SELECT` permission on `kiosk_users.users` (the support article
table lives in `kiosk_buykiosk`, so this is a cross-database query — make sure
prod grants permit it).

**The INSERT-path `status` value should match local DB state per article**, not
a hardcoded 'draft'. If the local article is `archived` (e.g. a deprecated
single-page QB article superseded by modular ones), the INSERT path needs
`'archived'` so that if prod doesn't have the row yet, it lands with the right
state. Existing rows are protected from status changes by the ON DUPLICATE
KEY UPDATE clause omitting `status`. Generate the script by *reading status
from the local DB row*, not by hardcoding.

**Critical:** the `ON DUPLICATE KEY UPDATE` clause omits `status`, `view_count`,
`is_featured`, and `author_id`. Those are prod-state fields you must not stomp:

- `status` — reviewers may have flipped draft→published in prod admin UI
- `view_count` — prod accumulates reader hits you don't want to reset
- `is_featured` — prod editors may have curated this
- `author_id` — prod's user IDs differ from yours; keep whatever's there

`content_html` is set to `NULL` deliberately so the next render regenerates from the
fresh markdown. This relies on the article-render template lazily re-rendering
markdown when `content_html` is NULL (which `ArticleManager` does).

### Step 5 — Don't rely on bulk `UPDATE...WHERE slug IN (...)` for recategorization

**ANTI-PATTERN** (this is what v1 did wrong):

```sql
-- BAD: silently no-ops if any slug is missing on prod
UPDATE support_articles
SET category_id = (SELECT id FROM support_categories WHERE slug = 'quickbooks' LIMIT 1)
WHERE slug IN ('quickbooks-connect-and-configure', 'quickbooks-account-mapping', ...);
```

It looks safe and idempotent. But if prod doesn't have those slugs yet (because
the modular articles never made it from local → prod in some prior deploy), the
UPDATE silently affects 0 rows. The deploy "succeeds" — but the articles never
land on prod. The bug is invisible until someone clicks into the new category
on the live site and sees a half-empty page.

**Correct pattern:** include **every article in scope** as a full upsert, with
the right `category_slug` in the INSERT clause. Then the ON DUPLICATE KEY
UPDATE clause's `category_id = VALUES(category_id)` recategorizes any existing
rows AND inserts any missing ones. Same statement does both jobs.

So a "recategorize 9 articles from Integrations to QuickBooks" deploy should
emit 9 full `INSERT...ON DUPLICATE KEY UPDATE` statements, one per article,
each carrying the article's full content_md from local. The bulk UPDATE
becomes unnecessary.

**Rule of thumb:** never trust that prod has a specific row just because local
does. Treat every prod deploy as "this row may not exist yet." Upsert all
affected articles, every time.

### Step 6 — Wrap in a transaction

```sql
START TRANSACTION;
-- ... all the upserts and updates ...
COMMIT;
```

Don't put `ROLLBACK;` at the end "to be safe" — that defeats the deploy. The user
runs the file as-is; if they want a dry run, they comment out `COMMIT;` and inspect
state interactively.

## Verification

After generating the script:

1. **Parse-check by sourcing into local DB** (which is already in the post-state):
   ```bash
   mysql -h localhost -u <user> -p<pw> kiosk_buykiosk < deploy-support-articles-YYYY-MM-DD.sql
   ```
   Idempotent, so this just bumps `updated_at`. If it errors, fix the script.

2. **Read the script back** and confirm:
   - No hardcoded IDs anywhere (no `WHERE id = 94`, no `category_id = 23`)
   - Every `ON DUPLICATE KEY UPDATE` clause excludes `status`, `view_count`, `is_featured`, `author_id`
   - `content_html = NULL` on every UPDATE
   - Wrapped in `START TRANSACTION ... COMMIT;`

3. **Add the verification queries** to the bottom of the script, commented out:
   ```sql
   -- SELECT id, title, slug, status, category_id, LENGTH(content_md) AS bytes, updated_at
   --   FROM support_articles WHERE slug IN (...) ORDER BY slug;
   ```

4. **Hand off to the user** with:
   - File path
   - List of image files to copy (separate deploy bucket)
   - Template file changes (separate git push)
   - Backup-table SQL the user can run before deploy for instant rollback

## Backup / Rollback Recipe

Before the user runs the deploy SQL on prod, give them this:

```sql
CREATE TABLE support_articles_backup_YYYYMMDD AS
SELECT * FROM support_articles
WHERE slug IN ('slug1', 'slug2', ...);  -- all slugs the deploy will touch
```

If the deploy goes wrong, restore those rows from the backup table.

## Example: Real Output From a Deploy

A 4-article + 1-category + 9-article-recategorization deploy fit in ~30KB of SQL,
broken into 5 statements:

| Statement | Rows affected on prod (existing site) |
|---|---|
| 1× `INSERT ... ON DUPLICATE KEY UPDATE` (category) | 1 |
| 3× `INSERT ... ON DUPLICATE KEY UPDATE` (articles) | 1-3 |
| 1× `UPDATE ... WHERE slug IN (...)` (recategorize) | 0-9 (depends which slugs exist) |

No row counts blow up because everything is keyed on UNIQUE slug.

## Notes

- The `support_articles` table has columns `content_md` (longtext) and `content_html`
  (longtext, nullable). The pattern relies on the render layer regenerating
  `content_html` from `content_md` when it's NULL. If a future change makes
  `content_html` non-nullable or stops the lazy-render behavior, this skill needs
  updating.

- `support_categories.slug` and `support_articles.slug` are both `UNIQUE`. That's
  what makes the ON DUPLICATE KEY UPDATE pattern work. If a future migration drops
  the unique constraint, this skill breaks silently (it would INSERT a duplicate
  instead of updating).

- The `author_id` resolution `COALESCE((SELECT id FROM uf_user WHERE user_name = '...'), 1)`
  falls back to id=1 if the username doesn't exist on prod. That's a soft default —
  the article will appear authored by user-id-1. The admin can fix author attribution
  later if needed; the article content lands correctly regardless.

- Image files for support articles always live at
  `public_html/images/support/articles/`. The markdown references them as
  `/images/support/articles/foo.png`. If you author an article that references an
  image not yet in that directory, the deploy will succeed but the article will
  show broken images until the file lands. **Always deploy images before SQL.**

- The image-size cap in `userfrosting/templates/themes/default/support/article.html`
  is currently 400×400 px (with a built-in lightbox JS for click-to-zoom showing the
  full-resolution source). If you change those caps locally, that template edit
  travels via git, not via this SQL script.

- Do **not** use the Conductor migration system for content deploys. Conductor is
  schema-only and runs against every store DB by default. Support article content
  lives in `kiosk_buykiosk` (central DB), not store DBs.

## Anti-Patterns to Avoid

- ❌ `mysqldump --tables support_articles support_categories | mysql prod`
- ❌ Hardcoded `category_id = 23` (local autoincrement won't match prod)
- ❌ Hardcoded `id =` anywhere (will collide with prod IDs)
- ❌ Author lookup via `kiosk_users.uf_user WHERE user_name = ...` (legacy table; use
  `kiosk_users.users WHERE username = ...` instead)
- ❌ Bulk `UPDATE...WHERE slug IN (...)` to recategorize articles you're not also
  upserting content for. If prod doesn't have the slug, the UPDATE silently no-ops
  and the article never lands.
- ❌ Generating the script with hardcoded `status = 'draft'` for every article.
  Read the local row's actual status — archived/published articles need their
  status carried through the INSERT path so fresh prod rows land in the right state.
- ❌ `INSERT ... ON DUPLICATE KEY UPDATE status = VALUES(status)` (stomps reviewer publish flips)
- ❌ `INSERT ... ON DUPLICATE KEY UPDATE view_count = 0` (resets reader counters)
- ❌ Including the deploy SQL in the Conductor migration pipeline
- ❌ Skipping the `content_html = NULL` reset (stale rendered HTML overrides new markdown)
- ❌ Embedding raw markdown via PHP string concat instead of `PDO::quote()` (single
  quotes inside the body — "customer's" — will break the SQL silently)
- ❌ Only upserting "new" articles and assuming "edited" articles are already on
  prod with matching content. Prod history may diverge from local — always upsert
  every article that's in scope.

## References

- BuyerKiosk schema: `kiosk_buykiosk.support_articles` and `support_categories`
- Render layer that re-generates content_html from content_md:
  `userfrosting/src/BuyerKiosk/Support/ArticleManager.php`
- Article view template: `userfrosting/templates/themes/default/support/article.html`
- Existing companion skill: `support-article-generator` (covers *authoring*, not deploy)
- Sample deploy output:
  `userfrosting/scripts/deploy-support-articles-2026-05-23.sql`