#!/usr/bin/env bash # ============================================================================= # TravelCommons — Staging DB cleanup with before/after regression capture # # Replicates the 2026-08-29 migrate-clone cleanup (handoff §4 + §7.2) on # staging.travelcommons.com (WP 7.1, Kimi V1.4.4), and proves via a # same-environment before/after page diff that the cleanup changes nothing. # # RUN FROM THE STAGING DOCROOT (the folder containing wp-config.php), # e.g.: cd ~/staging.travelcommons.com # # Phases (run in this order): # bash tc-staging-cleanup.sh baseline # 1. capture pre-cleanup pages # bash tc-staging-cleanup.sh audit # 2. show what WOULD be deleted (read-only) # bash tc-staging-cleanup.sh backup # 3. wp db export # bash tc-staging-cleanup.sh run # 4. perform the cleanup (typed confirmation) # bash tc-staging-cleanup.sh revisions # 4b. OPTIONAL: prune post revisions (see below) # bash tc-staging-cleanup.sh after # 5. re-capture + diff vs baseline # # REVIEW THE AUDIT OUTPUT BEFORE RUNNING PHASE 4. # # The 'revisions' phase is separate because it is the ONLY step that discards # real data (edit history) rather than orphaned data. It keeps the newest # revisions per post (default 3; override: TC_KEEP_REVISIONS=5). Front-end # output is unaffected, so run it before or after 'after' — the diff stays valid. # # Production reuse (later, per §7.2): invoke with overrides — # TC_SITE="https://travelcommons.com" bash tc-staging-cleanup.sh baseline # (production is still 6.9.7, so its before/after diff should likewise be zero) # ============================================================================= set -euo pipefail SITE="${TC_SITE:-https://staging.travelcommons.com}" WORKDIR="$HOME/tc-cleanup-work" BASE="$WORKDIR/baseline" AFTER="$WORKDIR/after" DIFFS="$WORKDIR/diffs" # Pages = the same set used in the 2026-08-29 clone parity test (§4) PAGES=( "home|/" "podcast-archive|/podcast-archive/" "podcasts-p1|/category/podcasts/" "podcasts-p2|/category/podcasts/page/2/" "podcasts-p26|/category/podcasts/page/26/" "destinations|/destinations/" "episode-200|/2024/05/23/podcast-200-wrapping-up-the-travelcommons-journey/" ) # --- SQL snippets (kept identical to the audit/run pairs) -------------------- SQL_ORPHAN_LOGS_COUNT="SELECT COUNT(*) AS orphan_logs FROM wp_actionscheduler_logs l LEFT JOIN wp_actionscheduler_actions a ON l.action_id = a.action_id WHERE a.action_id IS NULL;" SQL_ORPHAN_LOGS_DELETE="DELETE l FROM wp_actionscheduler_logs l LEFT JOIN wp_actionscheduler_actions a ON l.action_id = a.action_id WHERE a.action_id IS NULL;" SQL_FAILED_LIST="SELECT action_id, hook, status, scheduled_date_gmt FROM wp_actionscheduler_actions WHERE status IN ('failed','canceled') ORDER BY action_id;" SQL_AUTOLOAD_KB="SELECT ROUND(SUM(LENGTH(option_value))/1024) AS autoload_kb FROM wp_options WHERE autoload IN ('yes','on','auto','auto-on');" SQL_ELEMENTOR_LIST="SELECT option_name, ROUND(LENGTH(option_value)/1024,1) AS kb, autoload FROM wp_options WHERE (option_name LIKE 'elementor\_%' OR option_name LIKE '\_elementor\_%') AND option_name <> 'ss_podcasting_elementor_templates_disabled' ORDER BY kb DESC;" SQL_ELEMENTOR_DELETE="DELETE FROM wp_options WHERE (option_name LIKE 'elementor\_%' OR option_name LIKE '\_elementor\_%') AND option_name <> 'ss_podcasting_elementor_templates_disabled';" SQL_DEAD_OPTS_LIST="SELECT option_name, ROUND(LENGTH(option_value)/1024,1) AS kb, autoload FROM wp_options WHERE option_name IN ('redux_builder_amp','wpts_compat','tt_font_theme_options') OR option_name LIKE '%tt_font_theme_options%';" SQL_DEAD_OPTS_DELETE="DELETE FROM wp_options WHERE option_name IN ('redux_builder_amp','wpts_compat','tt_font_theme_options') OR option_name LIKE '%tt_font_theme_options%';" SQL_FAT_TRANSIENTS_LIST="SELECT option_name, ROUND(LENGTH(option_value)/1024,1) AS kb, autoload FROM wp_options WHERE option_name LIKE '\_site\_transient\_feed\_%' OR option_name LIKE '%dirsize_cache%' OR option_name LIKE '%wp\_theme\_files\_patterns%' ORDER BY kb DESC;" SQL_FAT_TRANSIENTS_DELETE="DELETE FROM wp_options WHERE option_name LIKE '\_site\_transient\_feed\_%' OR option_name LIKE '%dirsize_cache%' OR option_name LIKE '%wp\_theme\_files\_patterns%';" SQL_GS_LIST="SELECT p.ID, p.post_title, t.slug AS theme FROM wp_posts p JOIN wp_term_relationships tr ON p.ID = tr.object_id JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'wp_theme' JOIN wp_terms t ON tt.term_id = t.term_id WHERE p.post_type = 'wp_global_styles' ORDER BY p.ID;" SQL_GS_TOTAL="SELECT COUNT(*) FROM wp_posts WHERE post_type='wp_global_styles';" SQL_GS_DELETE_IDS="SELECT p.ID FROM wp_posts p JOIN wp_term_relationships tr ON p.ID = tr.object_id JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'wp_theme' JOIN wp_terms t ON tt.term_id = t.term_id WHERE p.post_type = 'wp_global_styles' AND t.slug <> 'twentytwentyfive-kimi-child-3' AND p.ID <> 4705;" SQL_REVISION_COUNT="SELECT COUNT(*) AS row_count FROM wp_posts WHERE post_type = 'revision';" SQL_ORPHAN_POSTMETA_COUNT="SELECT COUNT(*) AS row_count FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;" ORPHAN_TABLES="wp_wpforms_logs wp_wpforms_payment_meta wp_wpforms_payments wp_wpforms_tasks_meta wp_e_events wp_eau_snapshots wp_eau_snapshot_items" # --- Helpers ----------------------------------------------------------------- guard_environment() { local actual actual="$(wp option get siteurl)" if [ "$actual" != "$SITE" ]; then echo "ABORT: siteurl here is '$actual' but this run targets '$SITE'." echo " Wrong directory/server — nothing was touched." exit 1 fi local prefix prefix="$(wp db prefix)" if [ "$prefix" != "wp_" ]; then echo "ABORT: table prefix is '$prefix', script assumes 'wp_'. Edit before running." exit 1 fi echo "Environment OK: $actual (prefix $prefix)" } fetch_pages() { local dest="$1" mkdir -p "$dest" local buster buster="$(date +%s)$RANDOM" echo "Fetching ${#PAGES[@]} pages from $SITE (nocache=$buster) -> $dest" for entry in "${PAGES[@]}"; do local label="${entry%%|*}" local path="${entry#*|}" local url="${SITE}${path}?nocache=${buster}" local code code="$(curl -sS --max-time 60 -A "tc-cleanup-check/1.0" -H 'Cache-Control: no-cache' \ -o "$dest/$label.html" -w "%{http_code}" "$url")" echo "$code" > "$dest/$label.code" local status="OK" [ "$code" = "200" ] || status="HTTP-$code <-- investigate" grep -q '' "$dest/$label.html" || status="$status MISSING <-- investigate" if grep -qiE 'Fatal error|Parse error|Uncaught error|Warning|Notice|Deprecated' "$dest/$label.html"; then status="$status PHP-ERROR-TEXT <-- investigate" fi printf ' %-16s %s\n' "$label" "$status" done } normalize() { # Strip known-changing noise so the diff shows only real differences. # (% is the sed delimiter because patterns contain # and |.) sed -E \ -e 's%boost-cache/static/[A-Za-z0-9._-]+%boost-cache/static/HASH%g' \ -e 's%https?://pixel\.wp\.com[^"<> ]*%JETPACK-PIXEL%g' \ -e 's%https?://stats\.wp\.com/[^"<> ]*%JETPACK-STATS%g' \ -e 's%(\?|&(#038;|amp;)?)nocache=[0-9]+%%g' \ -e 's%%%g' \ -e 's%%%g' \ "$1" } counts_snapshot() { echo "--- snapshot: $(date) ---" echo -n "actionscheduler_logs rows : "; wp db query "SELECT COUNT(*) AS row_count FROM wp_actionscheduler_logs;" --skip-column-names echo -n " of which orphaned : "; wp db query "$SQL_ORPHAN_LOGS_COUNT" --skip-column-names echo -n "wp_options autoload (KB) : "; wp db query "$SQL_AUTOLOAD_KB" --skip-column-names echo -n "wp_global_styles rows : "; wp db query "$SQL_GS_TOTAL" --skip-column-names echo -n "DB size (MB) : "; wp db query "SELECT ROUND(SUM(data_length + index_length)/1048576,1) AS db_mb FROM information_schema.tables WHERE table_schema = DATABASE();" --skip-column-names } # --- Phases ------------------------------------------------------------------ phase_baseline() { guard_environment if [ -d "$BASE" ] && [ -z "${TC_FORCE:-}" ]; then echo "ABORT: $BASE already exists. A baseline must be PRE-cleanup." echo " If you really want to re-baseline, rerun with TC_FORCE=1." exit 1 fi fetch_pages "$BASE" echo echo "Baseline captured. Next: bash $0 audit" } phase_audit() { guard_environment mkdir -p "$WORKDIR" { echo "=== TravelCommons cleanup AUDIT (read-only) — $SITE ===" echo -n "WP core : "; wp core version echo -n "Parent theme : "; wp option get template echo -n "Child theme : "; wp option get stylesheet echo counts_snapshot echo echo "--- Failed/canceled Action Scheduler actions (delete candidates) ---" wp db query "$SQL_FAILED_LIST" echo echo "--- Orphaned elementor_* options (ss_podcasting_elementor_templates_disabled is KEPT) ---" wp db query "$SQL_ELEMENTOR_LIST" echo echo "--- Dead named options (AMP / WPtouch / Secondline fonts + transients) ---" wp db query "$SQL_DEAD_OPTS_LIST" echo echo "--- Fat regenerable transients ---" wp db query "$SQL_FAT_TRANSIENTS_LIST" echo echo "--- Orphan plugin tables (must all be ~0 rows; plugins must be absent) ---" for t in $ORPHAN_TABLES; do if wp db tables --all-tables | grep -qx "$t"; then c="$(wp db query "SELECT COUNT(*) AS row_count FROM $t;" --skip-column-names)" echo " $t : exists, $c rows" else echo " $t : ABSENT (nothing to drop)" fi done echo " Departed plugins still installed? (expect no output below):" wp plugin list --format=csv | grep -Ei 'wpforms|elementor|wptouch|secondline|satchmo|amp-wp' || echo " none found" echo echo "--- wp_global_styles rows and their theme tags (KEEP only 4705 / twentytwentyfive-kimi-child-3) ---" wp db query "$SQL_GS_LIST" echo echo "--- Orphan wp_template rows 4652/4703 (expect count 0; deleted on production 2026-08-26) ---" echo -n " count: "; wp post list --post_type=wp_template --post__in=4652,4703 --format=count wp db query "SELECT p.ID, p.post_title, p.post_name, t.slug AS theme FROM wp_posts p LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'wp_theme' LEFT JOIN wp_terms t ON tt.term_id = t.term_id WHERE p.ID IN (4652,4703) AND p.post_type = 'wp_template';" echo echo "--- Post revisions in wp_posts (NOT deleted by 'run'; see optional 'revisions' phase) ---" echo -n " MariaDB version : "; wp db query "SELECT VERSION();" --skip-column-names echo -n " total revisions : "; wp db query "$SQL_REVISION_COUNT" --skip-column-names echo -n " posts with revisions : "; wp db query "SELECT COUNT(DISTINCT post_parent) AS row_count FROM wp_posts WHERE post_type = 'revision';" --skip-column-names echo -n " orphaned postmeta rows: "; wp db query "$SQL_ORPHAN_POSTMETA_COUNT" --skip-column-names echo " most-revised posts (parent ID : revision count):" wp db query "SELECT post_parent, COUNT(*) AS revisions FROM wp_posts WHERE post_type = 'revision' GROUP BY post_parent ORDER BY revisions DESC LIMIT 10;" } | tee "$WORKDIR/audit-$(date +%Y%m%d-%H%M%S).txt" echo echo "Audit saved to $WORKDIR. Review it, then: bash $0 backup" } phase_backup() { guard_environment mkdir -p "$WORKDIR" local f="$WORKDIR/backup-staging-before-cleanup-$(date +%Y%m%d-%H%M%S).sql" echo "Exporting database to $f ..." wp db export "$f" ls -lh "$f" echo echo "Recommended: pull a copy off the server, e.g. from your Mac:" echo " scp $(whoami)@$(hostname -f 2>/dev/null || hostname):$f ~/Downloads/" echo echo "Next: bash $0 run" } phase_run() { guard_environment echo "This will DELETE data from the database at $SITE." echo "You should have reviewed 'audit' output and completed 'backup' first." read -r -p "Type CLEAN-STAGING to proceed: " ans if [ "$ans" != "CLEAN-STAGING" ]; then echo "Aborted — nothing changed."; exit 1; fi echo echo "== Step 1/6: Action Scheduler ==" echo -n "Orphan logs before: "; wp db query "$SQL_ORPHAN_LOGS_COUNT" --skip-column-names wp db query "$SQL_ORPHAN_LOGS_DELETE" wp db query "OPTIMIZE TABLE wp_actionscheduler_logs;" wp action-scheduler clean || echo "(action-scheduler clean reported nothing to do)" echo "Failed/canceled actions present:" wp db query "$SQL_FAILED_LIST" read -r -p "Delete ALL failed/canceled actions listed above? [y/N] " fans if [ "$fans" = "y" ] || [ "$fans" = "Y" ]; then wp db query "DELETE FROM wp_actionscheduler_actions WHERE status IN ('failed','canceled');" echo "Failed/canceled actions deleted." else echo "Skipped failed-action deletion." fi echo -n "Orphan logs after : "; wp db query "$SQL_ORPHAN_LOGS_COUNT" --skip-column-names echo echo "== Step 2/6: orphaned elementor_* options (keeping ss_podcasting_elementor_templates_disabled) ==" wp db query "$SQL_ELEMENTOR_DELETE" echo -n "Remaining elementor_* rows (expect only the SSP one): " wp db query "SELECT COUNT(*) AS row_count FROM wp_options WHERE option_name LIKE 'elementor\_%' OR option_name LIKE '\_elementor\_%';" --skip-column-names echo echo "== Step 3/6: dead named options + fat transients ==" wp db query "$SQL_DEAD_OPTS_DELETE" wp db query "$SQL_FAT_TRANSIENTS_DELETE" echo -n "Expired transients swept: " wp transient delete --expired echo -n "Autoload now (KB): "; wp db query "$SQL_AUTOLOAD_KB" --skip-column-names echo echo "== Step 4/6: drop orphan plugin tables ==" wp db query "DROP TABLE IF EXISTS $(echo "$ORPHAN_TABLES" | tr ' ' ',');" echo "Remaining tables:" wp db tables --all-tables echo echo "== Step 5/6: legacy wp_global_styles rows (keep 4705 / twentytwentyfive-kimi-child-3) ==" local IDS IDS="$(wp db query "$SQL_GS_DELETE_IDS" --skip-column-names | paste -sd' ' -)" if [ -n "$IDS" ]; then echo "Deleting wp_global_styles IDs: $IDS" wp post delete $IDS --force else echo "No legacy wp_global_styles rows found — nothing to delete." fi echo "Remaining wp_global_styles rows:" wp db query "$SQL_GS_LIST" echo echo "== Step 6/6: orphan wp_template rows 4652/4703 (deleted on production 2026-08-26; §5.4) ==" local TIDS TIDS="$(wp db query "SELECT p.ID FROM wp_posts p LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'wp_theme' LEFT JOIN wp_terms t ON tt.term_id = t.term_id WHERE p.ID IN (4652,4703) AND p.post_type = 'wp_template' AND (t.slug IS NULL OR t.slug <> 'twentytwentyfive-kimi-child-3');" --skip-column-names | paste -sd' ' -)" if [ -n "$TIDS" ]; then echo "Deleting orphan wp_template IDs: $TIDS (tagged to retired themes; confirmed inert)" wp post delete $TIDS --force else echo "None present (or unexpectedly tagged to the live theme — left untouched; inspect manually)." fi echo counts_snapshot echo echo "Cleanup complete." echo "MANUAL STEP: WP Super Cache -> Delete Cache -> Preload Cache Now (per §6)." echo "Then: bash $0 after" } phase_after() { guard_environment if [ ! -d "$BASE" ]; then echo "ABORT: no baseline found at $BASE — run the baseline phase first (pre-cleanup)." exit 1 fi fetch_pages "$AFTER" mkdir -p "$DIFFS" echo echo "=== Diff results (baseline vs after, noise-normalized) ===" local fail=0 for entry in "${PAGES[@]}"; do local label="${entry%%|*}" # NOTE: process substitution (<(...)) is NOT used — some hosts lack /dev/fd. normalize "$BASE/$label.html" > "$DIFFS/$label.base.norm" normalize "$AFTER/$label.html" > "$DIFFS/$label.after.norm" diff -u "$DIFFS/$label.base.norm" "$DIFFS/$label.after.norm" > "$DIFFS/$label.diff" || true local lines lines="$(wc -l < "$DIFFS/$label.diff" | tr -d ' ')" local lb la hb ha lb="$(grep -o '." } phase_revisions() { guard_environment local KEEP="${TC_KEEP_REVISIONS:-3}" case "$KEEP" in *[!0-9]*|'') echo "ABORT: TC_KEEP_REVISIONS must be a number (got '$KEEP')."; exit 1;; esac echo "=== Revision pruning ($SITE) ===" echo "This deletes post revisions from wp_posts, KEEPING the $KEEP most recent per post." echo "(Change the keep-count with e.g.: TC_KEEP_REVISIONS=5 bash $0 revisions)" echo echo "WARNING: unlike every other phase, this discards real data — your edit history." echo "Front-end risk is zero (revisions never render), so the 'after' diff stays valid." echo echo -n "Revisions currently in wp_posts: " wp db query "$SQL_REVISION_COUNT" --skip-column-names read -r -p "Type PRUNE-REVISIONS to proceed: " ans if [ "$ans" != "PRUNE-REVISIONS" ]; then echo "Aborted — nothing changed."; exit 1; fi echo echo "Deleting all but the $KEEP newest revisions per post" echo "(uses ROW_NUMBER(); requires MariaDB 10.2+ / MySQL 8+ — see audit output)..." wp db query "DELETE r FROM wp_posts r JOIN (SELECT ID FROM (SELECT ID, ROW_NUMBER() OVER (PARTITION BY post_parent ORDER BY post_date DESC, ID DESC) AS rn FROM wp_posts WHERE post_type = 'revision') ranked WHERE rn > $KEEP) old ON r.ID = old.ID;" echo -n "Revisions remaining: " wp db query "$SQL_REVISION_COUNT" --skip-column-names echo echo "Sweeping orphaned postmeta (rows whose post no longer exists)..." wp db query "DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts p ON pm.post_id = p.ID WHERE p.ID IS NULL;" echo "Reclaiming space..." wp db query "OPTIMIZE TABLE wp_posts;" wp db query "OPTIMIZE TABLE wp_postmeta;" echo -n "DB size now (MB): "; wp db query "SELECT ROUND(SUM(data_length + index_length)/1048576,1) AS db_mb FROM information_schema.tables WHERE table_schema = DATABASE();" --skip-column-names echo echo "Optional, to stop future re-bloat, add to wp-config.php (above the 'stop editing' line):" echo " define( 'WP_POST_REVISIONS', 5 );" } # --- Dispatch ---------------------------------------------------------------- case "${1:-}" in baseline) phase_baseline ;; audit) phase_audit ;; backup) phase_backup ;; run) phase_run ;; revisions) phase_revisions ;; after) phase_after ;; *) echo "Usage: bash $0 {baseline|audit|backup|run|revisions|after}" echo "Run phases in order from the staging docroot. See header comments." exit 1 ;; esac