Persisted Content Remediation

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)

1. Audit before write. Classify every affected row; know the canonical target format from code (reader + service layer), not assumptions.
2. 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.
3. Deterministic transforms only. Libraries (marked, remark, sanitize-html) — never paraphrase or "clean up" prose with the LLM.
4. Scope to bad rows. UPDATE only IDs matching the audit query. Never "fix all rows in the table."
5. Backup before apply. Export affected (id, content, word_count, updated_at, …) to JSON or a backup table.
6. Dry-run default. Script must support --dry-run; write human-reviewable diffs before --apply.
7. Re-audit after apply. Re-run classification SQL; counts of bad buckets should drop to zero (or documented exceptions).
8. 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:

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:

1c. Severity rank

Worst → mildest:

1. Pure wrong format — visible breakage (literal ##, **)
2. Hybrid structural leaks — ## headings inside HTML
3. Inline artifacts — footnotes [^1], stray bold, EPUB links
4. Cosmetic noise — HTML comments (<!-- page: N -->), entities
5. 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

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

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:

• sha256(before) recorded in backup
• Plain-text word count within ±15% (or policy threshold)
• len(after) >= 0.85 * len(before) for MD→HTML (HTML adds tags)
• Re-classification passes target bucket
• No <script>, event handlers, or disallowed tags (if sanitizing)
• Unicode/CJK/RTL preserved (spot-check non-Latin samples)

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:

1. User reviews audit + sample diffs (minimum 3 rows: small, medium, large; include non-English if present)
2. Confirm tier scope and row count
3. 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

Anti-patterns

Repo integration (ZenWrite)

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