Database Update & Migration Utilities

This guide explains how to update, migrate, and manage your Indexly database safely using three built-in utilities:

• update-db
• migrate-db
• migration-manager

Together, these tools let you inspect schema definitions, direct explicitly authorized FTS5 repair through Doctor, merge data between database files, and track schema evolution without silently discarding indexed content.

🧭 Summary

🌱 Key Features & Highlights

• Schema auto-alignment: Adds missing columns automatically.
• Dry-run mode: Preview all changes without applying them.
• Migration history tracking: Each migration is recorded with a timestamp.
• Semantic FTS5 inspection: Detects module, ordered-column, tokenizer, prefix, and supported-option drift.
• Guarded FTS5 rebuilds: Requires explicit rebuild intent and refuses malformed or unsupported definitions.
• SQLite-consistent snapshots: Uses SQLite’s backup API and verifies the snapshot before an FTS rebuild.
• Interactive confirmation: Prompts you before irreversible operations.
• Path normalization & data validation: Ensures consistent entries across tables.
• Cross-database merging: Import or update specific tables without full re-indexing.

⚙️ update-db Utility

Overview

The update-db script aligns your existing database schema with the latest Indexly definitions. It compares your tables against the expected schema, adds safe missing normal-table columns, and reports FTS5 rebuild needs separately.

Why This Matters

SQLite’s FTS5 tables behave differently from regular tables — updating them requires a total rebuild, whereas normal tables can be extended easily with ALTER TABLE.

CLI Usage

indexly update-db

Optional flags:

--apply         	# Apply schema fixes instead of just checkin
--db path/to/custom.db  # Use a specific database file

To explore all available parameters, run:

indexly show-help --details

Example: Updating Schema Safely

indexly update-db /path/to/custom.db

This previews potential changes. Once confirmed:

indexly update-db /path/to/custom.db --apply

Your schema will be updated and the database backed up automatically.

🔁 Migration Manager Utility

Purpose

migration_manager.py provides a more controlled and version-aware mechanism for managing database migrations — particularly around FTS5 rebuilds and schema tracking.

Key Operations

When to Use

Use this utility when:

• The FTS5 schema definition (columns, prefix, or tokenizer) has changed.
• You want to backfill missing migrations.
• You’re performing controlled schema versioning across environments.

CLI Example

indexly migrate check --db /path/to/custom.db --no-backup

After confirming the actions:

indexly migrate run --db /path/to/custom.db

This aligns tables as needed while recording the migration in schema_migrations. An FTS5 rebuild still requires the command’s explicit rebuild authorization; a check or ordinary migration must not silently rebuild the virtual table.

FTS5 definition states

Indexly classifies the authoritative file_index definition as:

Case, whitespace, quote style, and option ordering alone are equivalent. Indexly uses structured, quote- and parenthesis-aware inspection instead of comma splitting or a loose regular expression.

Verified rebuild and recovery

The FTS5 rebuild is offline maintenance:

1. Stop other database writers.

2. Run indexly doctor --full-integrity and review the result.

3. Start the explicitly authorized rebuild:

   indexly doctor --fix-db --rebuild-fts
   

4. Keep the reported verified snapshot until search and indexing smoke tests pass.

Before mutation, Indexly checks full integrity, the FTS definition, writable storage, conservative free space, snapshot validity, and the writer lock. A preflight failure leaves the original database unchanged.

Data moves through SQLite in a transaction rather than through an unbounded Python row list. Before swap, Indexly verifies logical row counts, null or empty paths, duplicate paths, a fixed-batch digest, the replacement definition, vocabulary access, and representative MATCH behavior. The table swap, vocabulary recreation, and search-cache generation bump are committed together. Transfer, verification, or swap failure rolls back. The rebuild does not run VACUUM.

Indexly reopens and checks the committed database. If post-commit verification fails, it reports the verified snapshot and recovery direction rather than claiming success. Stop writers, preserve the failed database for investigation, and recover from the reported snapshot or re-index the source folders.

Performance maintenance is not migration

indexly perf --opti plans only two bounded, evidence-specific operations: SQLite 3.46+ planner-stat refresh and one 500-page FTS merge. A guarded apply requires a current matching report, a verified SQLite backup, free-space and writer-lock checks, and confirmation. It does not migrate schema, rebuild FTS, change tokenizer or prefix options, change journal mode, run VACUUM, or re-index source files.

Use this page’s Doctor and migration workflows for integrity or schema drift. Both performance actions require measured canonical FTS inspection state match. Measured readiness 0 for missing, drifted, external-content, malformed, unsupported, or uninspectable definitions produces repair_required. Missing legacy or unavailable readiness instead requests fresh evidence or reports unavailable; it does not establish schema damage. Do not use a performance status, database size, or a failed readiness probe as authorization for repair. See Performance Diagnostics and Optimization.

🧩 migrate_db Utility

Overview

The migrate_db utility merges or imports table data between two Indexly databases — safely and interactively.

It is perfect for:

• Consolidating results from multiple Indexly instances.
• Recovering data from backup DBs.
• Merging metadata or tag information without reindexing.

CLI Usage

python -m indexly.migrate_db --source-db path/to/source.db --target-db path/to/target.db --table file_metadata

Optional:

--dry-run    # Preview all changes without modifying the target DB

Example Walkthrough

Step 1. Preview the merge

python -m indexly.migrate_db --source-db old.db --target-db main.db --table file_tags --dry-run

Step 2. Confirm and execute

python -m indexly.migrate_db --source-db old.db --target-db main.db --table file_tags

Before proceeding, you’ll see:

You are about to modify the target DB. Continue? [y/N]:

Answer y to continue or N to abort.

Safety Features

• Path normalization: Ensures consistency of file references.
• Row validation: Skips malformed rows and logs them.
• Column alignment: Adds missing columns in the target table automatically.
• Logging: Failed merges are written to migrate_db.log.

⚖️ FTS5 vs Normal Tables

🧠 When to Use Which Tool

🪶 Appendix: Design Considerations

These utilities follow three guiding design choices:

1. Safety First: Backups and dry-run modes are default behaviors — minimizing risk of accidental loss.
2. Predictability: Schema migrations and rebuilds are fully logged, making debugging transparent.
3. Modularity: Each utility serves a clear purpose — from simple updates to complex merges — without overlapping responsibilities.

🏷️ Related Topics

• Semantic Indexing (Overview)
• Why Semantic Filtering Matters
• Database Design
• Semantic Indexing & Vocabulary Quality
• Indexly Doctor