# Ubiquity Database Migration Guide: SQLite to Astra DB
This guide provides detailed instructions for migrating from SQLite (local development) to DataStax Astra DB (cloud/enterprise deployment).
## Overview
The Ubiquity database layer supports seamless migration between backends while preserving:
- Consciousness states and history
- Task queues and results
- Memory pool data
- Vector embeddings
## Prerequisites
1. **Astra DB Account**
- Sign up at [astra.datastax.com](https://astra.datastax.com)
- Create a new Serverless (Vector) database
- Enable vector search capabilities
2. **API Credentials**
- Generate an application token with Database Administrator role
- Note your database endpoint URL
- Configure embedding provider (OpenAI recommended)
3. **Astra Streaming (Optional)**
- Create a streaming tenant for pub/sub
- Generate streaming credentials
## Migration Steps
### Step 1: Prepare Astra DB
```bash
# Set environment variables
export ASTRA_DB_ENDPOINT="https://YOUR-DB-ID-REGION.apps.astra.datastax.com"
export ASTRA_DB_TOKEN="AstraCS:YOUR_TOKEN"
export ASTRA_DB_KEYSPACE="ubiquity"
export OPENAI_API_KEY="your-openai-key"
```
### Step 2: Initialize Astra Schema
```rust
use ubiquity_database::{DatabaseConfig, DatabaseBackend, create_database};
// Create Astra configuration
let mut config = DatabaseConfig::default();
config.backend = DatabaseBackend::Astra;
config.astra.endpoint = std::env::var("ASTRA_DB_ENDPOINT")?;
config.astra.token = std::env::var("ASTRA_DB_TOKEN")?;
config.astra.keyspace = std::env::var("ASTRA_DB_KEYSPACE")?;
// Initialize Astra DB
let astra_db = create_database(config.clone()).await?;
astra_db.initialize().await?;
```
### Step 3: Export SQLite Data
Create a migration tool:
```rust
use ubiquity_database::{DatabaseConfig, DatabaseBackend, create_database};
use chrono::{DateTime, Utc, Duration};
async fn export_sqlite_data() -> Result<MigrationData, Box<dyn Error>> {
// Connect to SQLite
let mut config = DatabaseConfig::default();
config.backend = DatabaseBackend::Sqlite;
config.sqlite.path = PathBuf::from("ubiquity.db");
let sqlite_db = create_database(config).await?;
// Export all data
let mut data = MigrationData::default();
// Get all agents
let agents = get_all_agents(&sqlite_db).await?;
// Export consciousness states (last 30 days per agent)
for agent_id in &agents {
let states = sqlite_db.get_consciousness_history(agent_id, 10000).await?;
data.consciousness_states.extend(states);
}
// Export ripples (last 7 days)
let end = Utc::now();
let start = end - Duration::days(7);
data.ripples = sqlite_db.get_ripples(start, end).await?;
// Export pending tasks
data.tasks = sqlite_db.get_pending_tasks().await?;
// Export memory pool data
data.memory_pool_data = export_memory_pools().await?;
Ok(data)
}
#[derive(Default)]
struct MigrationData {
consciousness_states: Vec<ConsciousnessState>,
ripples: Vec<ConsciousnessRipple>,
tasks: Vec<Task>,
memory_pool_data: Vec<(String, Vec<u8>)>,
}
```
### Step 4: Import to Astra DB
```rust
async fn import_to_astra(data: MigrationData) -> Result<(), Box<dyn Error>> {
// Connect to Astra
let mut config = DatabaseConfig::default();
config.backend = DatabaseBackend::Astra;
config.astra.endpoint = std::env::var("ASTRA_DB_ENDPOINT")?;
config.astra.token = std::env::var("ASTRA_DB_TOKEN")?;
let astra_db = create_database(config.clone()).await?;
let memory_pools = create_memory_pools(config).await?;
// Import consciousness states with progress
println!("Importing {} consciousness states...", data.consciousness_states.len());
let pb = ProgressBar::new(data.consciousness_states.len() as u64);
for state in &data.consciousness_states {
astra_db.store_consciousness_state(state).await?;
pb.inc(1);
}
pb.finish();
// Import ripples
println!("Importing {} ripples...", data.ripples.len());
for ripple in &data.ripples {
astra_db.store_ripple(ripple).await?;
}
// Import tasks
println!("Importing {} tasks...", data.tasks.len());
for task in &data.tasks {
astra_db.store_task(task).await?;
}
// Import memory pool data
println!("Importing {} memory pool items...", data.memory_pool_data.len());
for (key, value) in &data.memory_pool_data {
let pool = memory_pools.get_pool_for_key(key).await?;
pool.store(key, value).await?;
}
println!("Migration completed successfully!");
Ok(())
}
```
### Step 5: Verify Migration
```rust
async fn verify_migration() -> Result<(), Box<dyn Error>> {
let astra_db = create_astra_database().await?;
// Verify record counts
let agents = get_all_agents(&astra_db).await?;
println!("Migrated agents: {}", agents.len());
for agent_id in &agents {
let history = astra_db.get_consciousness_history(agent_id, 10).await?;
println!(" Agent {}: {} states", agent_id, history.len());
}
// Test vector search
let sample_embedding = vec![0.1; 1536];
let results = astra_db.vector_search(&sample_embedding, 5).await?;
println!("Vector search returned {} results", results.len());
// Test hybrid search
let query = HybridSearchQuery {
text: "consciousness breakthrough".to_string(),
vector: None,
filters: None,
limit: 10,
rerank: true,
};
let results = astra_db.hybrid_search(query).await?;
println!("Hybrid search returned {} results", results.len());
Ok(())
}
```
## Configuration Updates
### Update Application Configuration
```toml
# config.toml
[database]
backend = "astra" # Changed from "sqlite"
[database.astra]
endpoint = "${ASTRA_DB_ENDPOINT}"
token = "${ASTRA_DB_TOKEN}"
keyspace = "ubiquity"
[database.astra.collections]
consciousness = "consciousness_states"
ripples = "consciousness_ripples"
tasks = "tasks"
pool_prefix = "memory_pool_"
[database.embeddings]
provider = "openai"
model = "text-embedding-3-small"
dimension = 1536
cache_enabled = true
cache_size = 10000
```
### Update Docker Compose
```yaml
version: '3.8'
services:
ubiquity:
image: ubiquity:latest
environment:
- DATABASE_BACKEND=astra
- ASTRA_DB_ENDPOINT=${ASTRA_DB_ENDPOINT}
- ASTRA_DB_TOKEN=${ASTRA_DB_TOKEN}
- OPENAI_API_KEY=${OPENAI_API_KEY}
# Remove volume mount for SQLite
# volumes:
# - ./data:/app/data
```
## Performance Optimization
### 1. Batch Operations
```rust
// Instead of individual inserts
for state in states {
db.store_consciousness_state(&state).await?;
}
// Use batch operations
let documents: Vec<Value> = states.iter()
.map(|s| create_document(s))
.collect();
astra_client.insert_many(documents).await?;
```
### 2. Configure Vector Indexing
```json
{
"createCollection": {
"name": "consciousness_states",
"options": {
"vector": {
"dimension": 1536,
"metric": "cosine",
"service": {
"provider": "openai",
"modelName": "text-embedding-3-small"
}
},
"indexing": {
"allow": ["agent_id", "level", "phase", "timestamp"],
"deny": ["embedding"] // Don't index raw embeddings
}
}
}
}
```
### 3. Optimize Queries
```rust
// Use projections to reduce data transfer
let query = json!({
"find": {
"filter": { "agent_id": agent_id },
"projection": {
"level": 1,
"coherence": 1,
"timestamp": 1
},
"limit": 100
}
});
```
## Rollback Plan
If issues occur, you can rollback to SQLite:
1. **Keep SQLite Updated**: Continue writing to both databases during transition
2. **Switch Config**: Change `backend = "sqlite"` in configuration
3. **Restart Services**: Restart all Ubiquity services
## Monitoring
### Astra DB Metrics
Monitor via Astra Portal:
- Request rate and latency
- Storage usage
- Vector search performance
- Rate limit consumption
### Application Metrics
```rust
// Add metrics collection
use prometheus::{Counter, Histogram};
static DB_QUERIES: Counter = Counter::new("db_queries_total", "Total database queries");
static QUERY_DURATION: Histogram = Histogram::new("db_query_duration_seconds", "Query duration");
// Track operations
let timer = QUERY_DURATION.start_timer();
let result = db.vector_search(&embedding, 10).await?;
timer.observe_duration();
DB_QUERIES.inc();
```
## Common Issues
### Issue 1: Rate Limiting
**Symptom**: 429 errors from Astra DB
**Solution**:
```rust
use tokio::time::{sleep, Duration};
use backoff::{ExponentialBackoff, backoff::Backoff};
async fn with_retry<T, F, Fut>(f: F) -> Result<T, Error>
where
F: Fn() -> Fut,
Fut: Future<Output = Result<T, Error>>,
{
let mut backoff = ExponentialBackoff::default();
loop {
match f().await {
Ok(result) => return Ok(result),
Err(e) if e.is_rate_limit() => {
if let Some(duration) = backoff.next() {
sleep(duration).await;
} else {
return Err(e);
}
}
Err(e) => return Err(e),
}
}
}
```
### Issue 2: Embedding Dimension Mismatch
**Symptom**: Vector operations fail
**Solution**:
1. Verify embedding model configuration
2. Re-generate embeddings if needed:
```rust
async fn regenerate_embeddings() -> Result<(), Error> {
let states = db.get_all_consciousness_states().await?;
for state in states {
let embedding = generator.generate(&state.to_text()).await?;
db.update_embedding(&state.id, &embedding).await?;
}
Ok(())
}
```
### Issue 3: Connection Timeouts
**Symptom**: Timeout errors on large operations
**Solution**:
```rust
// Increase timeout for migration
let client = reqwest::Client::builder()
.timeout(Duration::from_secs(300))
.build()?;
```
## Best Practices
1. **Test in Staging**: Always test migration in a staging environment first
2. **Backup Data**: Keep SQLite backups until migration is verified
3. **Monitor Performance**: Track query latency and throughput
4. **Gradual Migration**: Consider migrating by agent or time period
5. **Document Changes**: Update runbooks and documentation
## Support
For migration assistance:
- Astra DB Documentation: [docs.datastax.com](https://docs.datastax.com)
- Ubiquity Discord: [discord.gg/ubiquity](https://discord.gg/ubiquity)
- GitHub Issues: [github.com/ubiquity/ubiquity-rs](https://github.com/ubiquity/ubiquity-rs)