Persisted Content Remediation
Safe workflow for fixing stored content bodies (not UI code) when format, encoding, or structure is wrong. Applies to any table/column (book_chapters.content, content_items.content, archive_items.content, etc.) and any source/target format: HTML, Markdown, MDX, plain text, hybrid, JSON.
When to use
- User asks to fix, normalize, convert, or clean up persisted content in a database
- Audit found format drift (e.g. Markdown stored where HTML is expected)
- Ingest pipeline skipped conversion or used a naive converter
- Hybrid bodies (HTML + leaked
## / ** / [^n])
- Wrong-field writes, truncation, empty placeholders, test/E2E pollution
- Not for: live editor UX, rendering component changes, or greenfield ingest (use the ingest skill instead)
Golden rules (non-negotiable)
- Audit before write. Classify every affected row; know the canonical target format from code (reader + service layer), not assumptions.
- MCP/database reads OK; MCP bulk writes NOT OK. Use Supabase MCP
execute_sql for classification and spot checks only. Remediation writes go through a local script + service role client.
- Deterministic transforms only. Libraries (
marked, remark, sanitize-html) — never paraphrase or "clean up" prose with the LLM.
- Scope to bad rows. UPDATE only IDs matching the audit query. Never "fix all rows in the table."
- Backup before apply. Export affected
(id, content, word_count, updated_at, …) to JSON or a backup table.
- Dry-run default. Script must support
--dry-run; write human-reviewable diffs before --apply.
- Re-audit after apply. Re-run classification SQL; counts of bad buckets should drop to zero (or documented exceptions).
- One tier at a time. Pure-format fixes before hybrid surgery before cosmetic cleanup.
Phase 0 — Discover canonical format
Before auditing data, read the code path that owns the field:
| Question |
Where to look |
| What format does the reader expect? |
Components using dangerouslySetInnerHTML, MDX renderer, ReactMarkdown, TipTap JSON |
| What format does write path produce? |
Service create* / update*, ingest mappers, cleanupContent() |
| What metadata must stay in sync? |
word_count, estimated_reading_time, updated_at |
| Is there a naive converter to avoid? |
e.g. repo markdownToHtml() — headings only, leaks inline MD |
Document finding in the audit report: target format, detection heuristics, forbidden double-conversion.
Phase 1 — Audit (read-only)
1a. Locate the authoritative table
-- Example: find populated content tables
SELECT table_name FROM information_schema.columns
WHERE table_schema = 'public' AND column_name = 'content'
ORDER BY table_name;
Confirm row counts; ignore empty duplicate/legacy tables.
1b. Classify formats in SQL
Use mutually exclusive buckets (top-down). Adapt patterns to the domain — see reference/audit-patterns.md.
Minimum buckets:
| Bucket |
Meaning |
target_format |
Matches canonical (e.g. HTML fragment) |
pure_source_format |
Entire body is wrong format (e.g. raw Markdown, no HTML) |
hybrid |
Target format + leaked source tokens |
empty_or_tiny |
Null, '', or placeholder (<p></p>) |
plain_text |
Neither target nor recognizable markup |
structured_other |
JSON, MDX/JSX, XML — rare but flag explicitly |
1c. Severity rank
Worst → mildest:
- Pure wrong format — visible breakage (literal
##, **)
- Hybrid structural leaks —
## headings inside HTML
- Inline artifacts — footnotes
[^1], stray bold, EPUB links
- Cosmetic noise — HTML comments (
<!-- page: N -->), entities
- Empty/test rows — delete or leave; not format conversion
1d. Write audit report
Path convention (ZenWrite): docs/build/<domain>/<topic>-audit.md
Include: TL;DR, canonical format evidence (code citations), distribution table, per-entity breakdown, severity, root-cause read, recommended tiers, SQL appendix.
Stop here unless user explicitly approves remediation.
Phase 2 — Plan remediation tiers
| Tier |
Typical action |
Risk |
| T1 Pure source → target |
Full doc conversion via library |
Low (with backup) |
| T2 Hybrid structural |
Re-ingest from source file OR HTML-aware partial convert |
Medium |
| T3 Inline artifacts |
Targeted rules (footnotes policy, bold, links) |
Medium–high |
| T4 Cosmetic strip |
Comment/whitespace normalization |
Low |
| T5 Row delete |
Test/E2E rows only |
Low (confirm with user) |
Do not combine tiers in one apply pass.
Phase 3 — Implement remediation script
Create under scripts/remediate-<domain>.ts (or repo convention). Required flags:
tsx scripts/remediate-<domain>.ts --dry-run [--bucket pure_markdown] [--book-id UUID] [--limit N]
tsx scripts/remediate-<domain>.ts --apply --backup ./backups/<timestamp>.json [--bucket ...]
Script requirements
// Pattern (adapt per repo)
import './load-local-env.js';
import { createClient } from '@supabase/supabase-js';
import { marked } from 'marked'; // or remark/unified — pin version, verify docs
import { createHash } from 'crypto';
import { writeFileSync, mkdirSync } from 'fs';
const dryRun = !process.argv.includes('--apply');
const backupPath = getArg('--backup');
// 1. SELECT only rows matching audit bucket (same SQL as Phase 1)
// 2. For each row: transform deterministically; NEVER call LLM
// 3. Validate: sha256 before/after, length ratio, format re-check
// 4. If validation fails → skip row, log to remediation-skipped.json
// 5. dry-run → write diff files; apply → UPDATE + sync word_count/reading_time
Transform selection
| Source → Target |
Tool |
Caution |
| MD → HTML |
marked / remark + rehype-sanitize |
Configure allowed tags to match reader |
| MDX → HTML |
MDX compile OR strip JSX if MDX not supported |
MDX ≠ MD; needs separate pipeline |
| HTML → HTML (hybrid) |
Parse DOM (node-html-parser, cheerio); convert text nodes only |
Never regex whole document |
| HTML → MD |
Only if target is MD (rare) |
Lossy; document why |
| Plain → HTML |
Wrap in <p>, escape entities |
Minimal |
| Strip comments |
DOM or targeted regex on known patterns |
Low risk |
Never run MD converter on already-HTML bodies unless you've extracted markdown islands first.
Validation gates (per row)
Skip and flag if any fail:
Diff output
Write to docs/build/<domain>/remediation-diffs/<id>.diff or a single JSONL with { id, beforeHead, afterHead, warnings }.
Phase 4 — Review gate
Before --apply:
- User reviews audit + sample diffs (minimum 3 rows: small, medium, large; include non-English if present)
- Confirm tier scope and row count
- Confirm backup path exists
Agent must not run --apply without explicit user approval.
Phase 5 — Apply and verify
# Apply
tsx scripts/remediate-<domain>.ts --apply --backup ./backups/2026-07-08-book-chapters.json --bucket pure_markdown
# Re-audit (same SQL as Phase 1)
# Expect: pure_source_format count → 0 for applied bucket
Print session report:
- Rows selected / transformed / skipped / failed
- Backup path
- Before/after bucket counts
- Skipped row IDs + reasons
- Suggested manual follow-ups
Tool choice matrix
| Task |
Tool |
Notes |
| Schema discovery |
Supabase MCP list_tables, execute_sql |
Read-only |
| Format classification |
Supabase MCP execute_sql |
Read-only |
| Spot-check bodies |
Supabase MCP or script SELECT |
Prefer script for large bodies |
| Backup export |
Local script → JSON |
Full fidelity |
| Transform |
Local script + library |
Deterministic |
| Write |
Local script UPDATE |
Transactional per batch optional |
| Bulk write via MCP |
Forbidden |
Truncation, no rollback, no diff review |
Anti-patterns
| Anti-pattern |
Why it fails |
| LLM rewrites chapter/article in chat |
Hallucination, truncation, tone drift |
UPDATE ... SET content = '...' via MCP with inline body |
Context limits; untested |
Run naive markdownToHtml() on hybrid HTML |
Double-wraps, breaks tags |
| Regex replace on 40k-char HTML |
Eats closing tags, nested structures |
| Fix all rows "while we're at it" |
Damages good content |
| Skip backup "it's just 56 rows" |
Irreversible without PITR |
| Assume MDX when codebase uses HTML |
Wrong target format |
Repo integration (ZenWrite)
| Resource |
Path |
| Example audit |
docs/build/books/chapter-content-format-audit.md |
| Naive converter (do not use for full remediation) |
server/services/admin/ingest/contentCleanup.ts |
| Chapter write path |
server/services/books/bookChapters.service.ts |
| Migration script pattern |
scripts/migrate-books-to-catalog.ts |
| Markdown lib |
marked in package.json |
| Supabase project |
Movemental vhaiiiykcukrlyvwlgip |
Invocation
/persisted-content-remediation audit book_chapters.content
/persisted-content-remediation plan --report docs/build/books/chapter-content-format-audit.md
/persisted-content-remediation script book-chapters --tier 1 --dry-run
Related skills
movement-leader-harvest-ingest — forward ingest (prevent recurrence)
type-safety-chain — if remediation touches schema/contracts
validate — post-change repo validation
Additional resources
- SQL classification templates: reference/audit-patterns.md
- Worked example walkthrough: reference/worked-example-book-chapters.md
1---2name: persisted-content-remediation3description: >-4 Safely audit and remediate persisted text/content fields in Supabase or Postgres when bodies5 are in the wrong format (HTML, Markdown, MDX, plain text, hybrid, JSON, empty). Use for content6 format drift, ingest bypass, wrong-field storage, leaked markup, or batch normalization — always7 audit-first, backup, dry-run, deterministic transforms, never AI-rewrite in place.8disable-model-invocation: true9metadata:10 source: https://skillmd.com/skills/joshuashepherd/persisted-content-remediation11---1213# Persisted Content Remediation1415Safe workflow for fixing **stored content bodies** (not UI code) when format, encoding, or structure is wrong. Applies to any table/column (`book_chapters.content`, `content_items.content`, `archive_items.content`, etc.) and any source/target format: **HTML, Markdown, MDX, plain text, hybrid, JSON**.1617## When to use1819- User asks to fix, normalize, convert, or clean up **persisted content** in a database20- Audit found format drift (e.g. Markdown stored where HTML is expected)21- Ingest pipeline skipped conversion or used a naive converter22- Hybrid bodies (HTML + leaked `##` / `**` / `[^n]`)23- Wrong-field writes, truncation, empty placeholders, test/E2E pollution24- **Not** for: live editor UX, rendering component changes, or greenfield ingest (use the ingest skill instead)2526## Golden rules (non-negotiable)27281. **Audit before write.** Classify every affected row; know the canonical target format from code (reader + service layer), not assumptions.292. **MCP/database reads OK; MCP bulk writes NOT OK.** Use Supabase MCP `execute_sql` for classification and spot checks only. Remediation writes go through a **local script** + service role client.303. **Deterministic transforms only.** Libraries (`marked`, `remark`, `sanitize-html`) — never paraphrase or "clean up" prose with the LLM.314. **Scope to bad rows.** UPDATE only IDs matching the audit query. Never "fix all rows in the table."325. **Backup before apply.** Export affected `(id, content, word_count, updated_at, …)` to JSON or a backup table.336. **Dry-run default.** Script must support `--dry-run`; write human-reviewable diffs before `--apply`.347. **Re-audit after apply.** Re-run classification SQL; counts of bad buckets should drop to zero (or documented exceptions).358. **One tier at a time.** Pure-format fixes before hybrid surgery before cosmetic cleanup.3637## Phase 0 — Discover canonical format3839Before auditing data, read the **code path** that owns the field:4041| Question | Where to look |42|----------|---------------|43| What format does the reader expect? | Components using `dangerouslySetInnerHTML`, MDX renderer, `ReactMarkdown`, TipTap JSON |44| What format does write path produce? | Service `create*` / `update*`, ingest mappers, `cleanupContent()` |45| What metadata must stay in sync? | `word_count`, `estimated_reading_time`, `updated_at` |46| Is there a naive converter to avoid? | e.g. repo `markdownToHtml()` — headings only, leaks inline MD |4748Document finding in the audit report: **target format**, **detection heuristics**, **forbidden double-conversion**.4950## Phase 1 — Audit (read-only)5152### 1a. Locate the authoritative table5354```sql55-- Example: find populated content tables56SELECT table_name FROM information_schema.columns57WHERE table_schema = 'public' AND column_name = 'content'58ORDER BY table_name;59```6061Confirm row counts; ignore empty duplicate/legacy tables.6263### 1b. Classify formats in SQL6465Use mutually exclusive buckets (top-down). Adapt patterns to the domain — see [reference/audit-patterns.md](reference/audit-patterns.md).6667Minimum buckets:6869| Bucket | Meaning |70|--------|---------|71| `target_format` | Matches canonical (e.g. HTML fragment) |72| `pure_source_format` | Entire body is wrong format (e.g. raw Markdown, no HTML) |73| `hybrid` | Target format + leaked source tokens |74| `empty_or_tiny` | Null, `''`, or placeholder (`<p></p>`) |75| `plain_text` | Neither target nor recognizable markup |76| `structured_other` | JSON, MDX/JSX, XML — rare but flag explicitly |7778### 1c. Severity rank7980Worst → mildest:81821. **Pure wrong format** — visible breakage (literal `##`, `**`)832. **Hybrid structural leaks** — `##` headings inside HTML843. **Inline artifacts** — footnotes `[^1]`, stray bold, EPUB links854. **Cosmetic noise** — HTML comments (`<!-- page: N -->`), entities865. **Empty/test rows** — delete or leave; not format conversion8788### 1d. Write audit report8990Path convention (ZenWrite): `docs/build/<domain>/<topic>-audit.md`9192Include: TL;DR, canonical format evidence (code citations), distribution table, per-entity breakdown, severity, root-cause read, recommended tiers, SQL appendix.9394**Stop here unless user explicitly approves remediation.**9596## Phase 2 — Plan remediation tiers9798| Tier | Typical action | Risk |99|------|----------------|------|100| T1 Pure source → target | Full doc conversion via library | Low (with backup) |101| T2 Hybrid structural | Re-ingest from source file OR HTML-aware partial convert | Medium |102| T3 Inline artifacts | Targeted rules (footnotes policy, bold, links) | Medium–high |103| T4 Cosmetic strip | Comment/whitespace normalization | Low |104| T5 Row delete | Test/E2E rows only | Low (confirm with user) |105106**Do not combine tiers in one apply pass.**107108## Phase 3 — Implement remediation script109110Create under `scripts/remediate-<domain>.ts` (or repo convention). Required flags:111112```113tsx scripts/remediate-<domain>.ts --dry-run [--bucket pure_markdown] [--book-id UUID] [--limit N]114tsx scripts/remediate-<domain>.ts --apply --backup ./backups/<timestamp>.json [--bucket ...]115```116117### Script requirements118119```typescript120// Pattern (adapt per repo)121import './load-local-env.js';122import { createClient } from '@supabase/supabase-js';123import { marked } from 'marked'; // or remark/unified — pin version, verify docs124import { createHash } from 'crypto';125import { writeFileSync, mkdirSync } from 'fs';126127const dryRun = !process.argv.includes('--apply');128const backupPath = getArg('--backup');129130// 1. SELECT only rows matching audit bucket (same SQL as Phase 1)131// 2. For each row: transform deterministically; NEVER call LLM132// 3. Validate: sha256 before/after, length ratio, format re-check133// 4. If validation fails → skip row, log to remediation-skipped.json134// 5. dry-run → write diff files; apply → UPDATE + sync word_count/reading_time135```136137### Transform selection138139| Source → Target | Tool | Caution |140|-----------------|------|---------|141| MD → HTML | `marked` / `remark` + `rehype-sanitize` | Configure allowed tags to match reader |142| MDX → HTML | MDX compile OR strip JSX if MDX not supported | MDX ≠ MD; needs separate pipeline |143| HTML → HTML (hybrid) | Parse DOM (`node-html-parser`, `cheerio`); convert text nodes only | Never regex whole document |144| HTML → MD | Only if target is MD (rare) | Lossy; document why |145| Plain → HTML | Wrap in `<p>`, escape entities | Minimal |146| Strip comments | DOM or targeted regex on known patterns | Low risk |147148**Never run MD converter on already-HTML bodies** unless you've extracted markdown islands first.149150### Validation gates (per row)151152Skip and flag if any fail:153154- [ ] `sha256(before)` recorded in backup155- [ ] Plain-text word count within ±15% (or policy threshold)156- [ ] `len(after) >= 0.85 * len(before)` for MD→HTML (HTML adds tags)157- [ ] Re-classification passes target bucket158- [ ] No `<script>`, event handlers, or disallowed tags (if sanitizing)159- [ ] Unicode/CJK/RTL preserved (spot-check non-Latin samples)160161### Diff output162163Write to `docs/build/<domain>/remediation-diffs/<id>.diff` or a single JSONL with `{ id, beforeHead, afterHead, warnings }`.164165## Phase 4 — Review gate166167Before `--apply`:1681691. User reviews audit + sample diffs (minimum 3 rows: small, medium, large; include non-English if present)1702. Confirm tier scope and row count1713. Confirm backup path exists172173**Agent must not run `--apply` without explicit user approval.**174175## Phase 5 — Apply and verify176177```bash178# Apply179tsx scripts/remediate-<domain>.ts --apply --backup ./backups/2026-07-08-book-chapters.json --bucket pure_markdown180181# Re-audit (same SQL as Phase 1)182# Expect: pure_source_format count → 0 for applied bucket183```184185Print session report:186187- Rows selected / transformed / skipped / failed188- Backup path189- Before/after bucket counts190- Skipped row IDs + reasons191- Suggested manual follow-ups192193## Tool choice matrix194195| Task | Tool | Notes |196|------|------|-------|197| Schema discovery | Supabase MCP `list_tables`, `execute_sql` | Read-only |198| Format classification | Supabase MCP `execute_sql` | Read-only |199| Spot-check bodies | Supabase MCP or script SELECT | Prefer script for large bodies |200| Backup export | Local script → JSON | Full fidelity |201| Transform | Local script + library | Deterministic |202| Write | Local script UPDATE | Transactional per batch optional |203| Bulk write via MCP | **Forbidden** | Truncation, no rollback, no diff review |204205## Anti-patterns206207| Anti-pattern | Why it fails |208|--------------|--------------|209| LLM rewrites chapter/article in chat | Hallucination, truncation, tone drift |210| `UPDATE ... SET content = '...'` via MCP with inline body | Context limits; untested |211| Run naive `markdownToHtml()` on hybrid HTML | Double-wraps, breaks tags |212| Regex replace on 40k-char HTML | Eats closing tags, nested structures |213| Fix all rows "while we're at it" | Damages good content |214| Skip backup "it's just 56 rows" | Irreversible without PITR |215| Assume MDX when codebase uses HTML | Wrong target format |216217## Repo integration (ZenWrite)218219| Resource | Path |220|----------|------|221| Example audit | `docs/build/books/chapter-content-format-audit.md` |222| Naive converter (do not use for full remediation) | `server/services/admin/ingest/contentCleanup.ts` |223| Chapter write path | `server/services/books/bookChapters.service.ts` |224| Migration script pattern | `scripts/migrate-books-to-catalog.ts` |225| Markdown lib | `marked` in `package.json` |226| Supabase project | Movemental `vhaiiiykcukrlyvwlgip` |227228## Invocation229230```231/persisted-content-remediation audit book_chapters.content232/persisted-content-remediation plan --report docs/build/books/chapter-content-format-audit.md233/persisted-content-remediation script book-chapters --tier 1 --dry-run234```235236## Related skills237238- **`movement-leader-harvest-ingest`** — forward ingest (prevent recurrence)239- **`type-safety-chain`** — if remediation touches schema/contracts240- **`validate`** — post-change repo validation241242## Additional resources243244- SQL classification templates: [reference/audit-patterns.md](reference/audit-patterns.md)245- Worked example walkthrough: [reference/worked-example-book-chapters.md](reference/worked-example-book-chapters.md)