A script to help migrate a beads backup when you get unknown column when running bd backup restore

01_the-problem.md

I did a big version jump on my oldest beads project (hadn't worked on it for a while) and was migrating it to the shared dolt server like I have been on all other projects, and then I got these errors.

My issues were restored after running the Python script but I don't guarantee something isn't lost, it seemed good enough :) I.e. 100% vibe coded.

$ bd backup restore
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'hook_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'hook_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'last_activity' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'agent_state' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'agent_state' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_type' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'hook_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'hook_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'agent_state' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'agent_state' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'agent_state' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'last_activity' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'last_activity' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'last_activity' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'agent_state' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'rig' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'hook_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'last_activity' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'last_activity' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'role_bead' in 'issues'
Warning: failed to restore issues row: Error 1054 (HY000): Unknown column 'hook_bead' in 'issues'
✓ Restore complete

  Issues:       0
  Comments:     2
  Dependencies: 59
  Labels:       0
  Events:       142
  Config:       11
  

fix-backup-schema.py

#!/usr/bin/env python3
"""Strip columns from a beads JSONL backup that don't exist in the current schema.

Fixes 'Unknown column' errors during `bd backup restore` when the backup was
created by an older (or newer) version of beads with a different issues schema.

Usage:
    python3 scripts/fix-backup-schema.py .beads/backup/
    python3 scripts/fix-backup-schema.py eu-backup/
    python3 scripts/fix-backup-schema.py --dry-run .beads/backup/

Reads issues.jsonl, strips unknown columns, writes back in place.
The original file is saved as issues.jsonl.bak.
"""

import json
import shutil
import sys
from pathlib import Path

# Current issues table columns as of beads 0.62.0 (from schema.go)
VALID_ISSUES_COLUMNS = {
    "id",
    "content_hash",
    "title",
    "description",
    "design",
    "acceptance_criteria",
    "notes",
    "status",
    "priority",
    "issue_type",
    "assignee",
    "estimated_minutes",
    "created_at",
    "created_by",
    "owner",
    "updated_at",
    "closed_at",
    "closed_by_session",
    "external_ref",
    "spec_id",
    "compaction_level",
    "compacted_at",
    "compacted_at_commit",
    "original_size",
    "sender",
    "ephemeral",
    "no_history",
    "wisp_type",
    "pinned",
    "is_template",
    "mol_type",
    "work_type",
    "source_system",
    "metadata",
    "source_repo",
    "close_reason",
    "event_kind",
    "actor",
    "target",
    "payload",
    "await_type",
    "await_id",
    "timeout_ns",
    "waiters",
    "due_at",
    "defer_until",
    # These are denormalized fields that bd backup restore handles separately
    "labels",
    "dependencies",
}

# Fields that bd backup restore already strips (kept here for documentation)
KNOWN_COMPUTED_FIELDS = {
    "dependency_count",
    "dependent_count",
    "comment_count",
    "parent",
}


def fix_issues_jsonl(backup_dir: Path, dry_run: bool = False) -> None:
    issues_path = backup_dir / "issues.jsonl"
    if not issues_path.exists():
        print(f"No issues.jsonl found in {backup_dir}", file=sys.stderr)
        sys.exit(1)

    lines = issues_path.read_text().splitlines()
    if not lines:
        print("issues.jsonl is empty, nothing to do.")
        return

    allowed = VALID_ISSUES_COLUMNS | KNOWN_COMPUTED_FIELDS
    stripped_columns: set[str] = set()
    fixed_lines: list[str] = []
    rows_modified = 0

    for line in lines:
        if not line.strip():
            continue
        row = json.loads(line)
        unknown = set(row.keys()) - allowed
        if unknown:
            stripped_columns.update(unknown)
            rows_modified += 1
            for col in unknown:
                del row[col]
        fixed_lines.append(json.dumps(row, ensure_ascii=False))

    if not stripped_columns:
        print("No unknown columns found — backup is compatible with current schema.")
        return

    print(f"Columns to strip: {', '.join(sorted(stripped_columns))}")
    print(f"Rows affected: {rows_modified}/{len(fixed_lines)}")

    if dry_run:
        print("\nDry run — no files modified.")
        return

    # Back up original
    bak_path = issues_path.with_suffix(".jsonl.bak")
    shutil.copy2(issues_path, bak_path)
    print(f"Original saved to: {bak_path}")

    # Write fixed file
    issues_path.write_text("\n".join(fixed_lines) + "\n")
    print(f"Fixed issues.jsonl written. Run `bd backup restore` to import.")


def main() -> None:
    args = sys.argv[1:]
    dry_run = "--dry-run" in args
    args = [a for a in args if a != "--dry-run"]

    if not args:
        print(f"Usage: {sys.argv[0]} [--dry-run] BACKUP_DIR", file=sys.stderr)
        sys.exit(1)

    backup_dir = Path(args[0])
    if not backup_dir.is_dir():
        print(f"Not a directory: {backup_dir}", file=sys.stderr)
        sys.exit(1)

    fix_issues_jsonl(backup_dir, dry_run)


if __name__ == "__main__":
    main()