Archon/migration/0.1.0/DB_UPGRADE_INSTRUCTIONS.md

Archon Database Migrations

This folder contains database migration scripts for upgrading existing Archon installations.

Available Migration Scripts

1. backup_database.sql - Pre-Migration Backup

Always run this FIRST before any migration!

Creates timestamped backup tables of all your existing data:

• ✅ Complete backup of archon_crawled_pages
• ✅ Complete backup of archon_code_examples
• ✅ Complete backup of archon_sources
• ✅ Easy restore commands provided
• ✅ Row count verification

2. Migration Scripts (Run in Order)

You only have to run the ones you haven't already! If you don't remember exactly, it is okay to rerun migration scripts.

2.1. 001_add_source_url_display_name.sql

• Adds display name field to sources table
• Improves UI presentation of crawled sources

2.2. 002_add_hybrid_search_tsvector.sql

• Adds full-text search capabilities
• Implements hybrid search with tsvector columns
• Creates optimized search indexes

2.3. 003_ollama_add_columns.sql

• Adds multi-dimensional embedding columns (384, 768, 1024, 1536, 3072 dimensions)
• Adds model tracking fields (llm_chat_model, embedding_model, embedding_dimension)

2.4. 004_ollama_migrate_data.sql

• Migrates existing embeddings to new multi-dimensional columns
• Drops old embedding column after migration
• Removes obsolete indexes

2.5. 005_ollama_create_functions.sql

• Creates search functions for multi-dimensional embeddings
• Adds helper functions for dimension detection
• Maintains backward compatibility with legacy search functions

2.6. 006_ollama_create_indexes_optional.sql

• Creates vector indexes for performance (may timeout on large datasets)
• Creates B-tree indexes for model fields
• Can be skipped if timeout occurs (system will use brute-force search)

2.7. 007_add_priority_column_to_tasks.sql

• Adds priority field to tasks table
• Enables task prioritization in project management

2.8. 008_add_migration_tracking.sql

• Creates migration tracking table
• Records all applied migrations
• Enables migration version control

Migration Process (Follow This Order!)

Step 1: Backup Your Data

-- Run: backup_database.sql
-- This creates timestamped backup tables of all your data

Step 2: Run All Migration Scripts (In Order!)

-- Run each script in sequence:
-- 1. Run: 001_add_source_url_display_name.sql
-- 2. Run: 002_add_hybrid_search_tsvector.sql
-- 3. Run: 003_ollama_add_columns.sql
-- 4. Run: 004_ollama_migrate_data.sql
-- 5. Run: 005_ollama_create_functions.sql
-- 6. Run: 006_ollama_create_indexes_optional.sql (optional - may timeout)
-- 7. Run: 007_add_priority_column_to_tasks.sql
-- 8. Run: 008_add_migration_tracking.sql

Step 3: Restart Services

docker compose restart

How to Run Migrations

Method 1: Using Supabase Dashboard (Recommended)

1. Open your Supabase project dashboard
2. Go to SQL Editor
3. Copy and paste the contents of the migration file
4. Click Run to execute the migration
5. Important: Supabase only shows the result of the last query - all our scripts end with a status summary table that shows the complete results

Method 2: Using psql Command Line

# Connect to your database
psql -h your-supabase-host -p 5432 -U postgres -d postgres

# Run the migrations in order
\i /path/to/001_add_source_url_display_name.sql
\i /path/to/002_add_hybrid_search_tsvector.sql
\i /path/to/003_ollama_add_columns.sql
\i /path/to/004_ollama_migrate_data.sql
\i /path/to/005_ollama_create_functions.sql
\i /path/to/006_ollama_create_indexes_optional.sql
\i /path/to/007_add_priority_column_to_tasks.sql
\i /path/to/008_add_migration_tracking.sql

# Exit
\q

Method 3: Using Docker (if using local Supabase)

# Copy migrations to container
docker cp 001_add_source_url_display_name.sql supabase-db:/tmp/
docker cp 002_add_hybrid_search_tsvector.sql supabase-db:/tmp/
docker cp 003_ollama_add_columns.sql supabase-db:/tmp/
docker cp 004_ollama_migrate_data.sql supabase-db:/tmp/
docker cp 005_ollama_create_functions.sql supabase-db:/tmp/
docker cp 006_ollama_create_indexes_optional.sql supabase-db:/tmp/
docker cp 007_add_priority_column_to_tasks.sql supabase-db:/tmp/
docker cp 008_add_migration_tracking.sql supabase-db:/tmp/

# Execute migrations in order
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/001_add_source_url_display_name.sql
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/002_add_hybrid_search_tsvector.sql
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/003_ollama_add_columns.sql
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/004_ollama_migrate_data.sql
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/005_ollama_create_functions.sql
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/006_ollama_create_indexes_optional.sql
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/007_add_priority_column_to_tasks.sql
docker exec -it supabase-db psql -U postgres -d postgres -f /tmp/008_add_migration_tracking.sql

Migration Safety

• ✅ Safe to run multiple times - Uses IF NOT EXISTS checks
• ✅ Non-destructive - Preserves all existing data
• ✅ Automatic rollback - Uses database transactions
• ✅ Comprehensive logging - Detailed progress notifications

After Migration

1. Restart Archon Services:

   docker-compose restart
   

2. Verify Migration:

   • Check the Archon logs for any errors
   • Try running a test crawl
   • Verify search functionality works

3. Configure New Features:

   • Go to Settings page in Archon UI
   • Configure your preferred LLM and embedding models
   • New crawls will automatically use model tracking