npm package: @quilltap/mongodb-to-sqlite
Description: Migrate 2.7.x Quilltap MongoDB database to 2.8.x SQLite database
Author: Charles Sebold and Foundry-9 LLC
Latest version: 1.0.0

# MongoDB to SQLite Migration Tool

A standalone CLI utility to migrate data from a MongoDB database to SQLite format. Designed specifically for migrating Quilltap 2.7.x MongoDB databases to 2.8.x SQLite format.

## Features

- **Complete schema migration** - Creates all Quilltap tables with proper indexes
- **Data transformation** - Automatically converts MongoDB documents to SQLite rows
- **Dependency-aware** - Migrates collections in priority order to maintain referential integrity
- **Dry-run mode** - Preview record counts before executing migration
- **Verbose logging** - Optional detailed output for debugging
- **Batch processing** - Efficiently handles large datasets
- **Error handling** - Graceful error reporting with detailed logs

## Installation

Install globally or use with `npx`:

```bash
npm install -g @quilltap/mongodb-to-sqlite
```

Or use directly without installation:

```bash
npx @quilltap/mongodb-to-sqlite [options]
```

## Usage

### Basic Migration

```bash
mongodb-to-sqlite \
  --mongo-uri "mongodb://localhost:27017" \
  --output ./quilltap.db
```

### With Authentication

```bash
mongodb-to-sqlite \
  --mongo-uri "mongodb://user:password@host:27017" \
  --db-name mydb \
  --output /path/to/quilltap.db
```

### Dry Run (Preview Only)

```bash
mongodb-to-sqlite \
  --mongo-uri "mongodb://localhost:27017" \
  --db-name quilltap \
  --dry-run
```

### Verbose Output

```bash
mongodb-to-sqlite \
  --mongo-uri "mongodb://localhost:27017" \
  --output ./quilltap.db \
  --verbose
```

## Options

| Option | Short | Required | Default | Description |
| -------- | ------- | ---------- | --------- | ------------- |
| `--mongo-uri` | `-m` | Yes | - | MongoDB connection URI |
| `--output` | `-o` | Yes* | - | Output SQLite database path (*not needed with `--dry-run`) |
| `--db-name` | `-d` | No | `quilltap` | MongoDB database name to migrate |
| `--dry-run` | - | No | `false` | Count records without migrating |
| `--verbose` | `-v` | No | `false` | Enable detailed logging |
| `--help` | `-h` | No | - | Show help message |

## Migrated Collections

The tool migrates the following MongoDB collections to SQLite:

### Core Identity & Session

- **users** - User accounts and authentication data
- **accounts** - OAuth/external provider accounts
- **sessions** - Active user sessions
- **api_keys** - API keys for integrations

### Content & Characters

- **characters** - Character definitions and metadata
- **chats** - Chat/conversation records
- **chat_messages** - Individual messages (denormalized from array to rows)
- **memories** - Character memories and knowledge
- **tags** - User-defined tags

### Configuration & Profiles

- **connection_profiles** - LLM provider configurations
- **image_profiles** - Image generation profiles
- **embedding_profiles** - Embedding model profiles
- **roleplay_templates** - Roleplay system prompts
- **prompt_templates** - Reusable prompt templates
- **chat_settings** - User chat preferences

### Storage & Files

- **files** - File metadata
- **folders** - File organization
- **mount_points** - Storage backend configurations
- **file_permissions** - File access controls

### Infrastructure

- **background_jobs** - Async job queue
- **llm_logs** - LLM request/response logs
- **vector_indices** - Embedding vector indices
- **plugin_configs** - Plugin configurations
- **provider_models** - Available LLM models
- **projects** - User projects

### Synchronization

- **sync_instances** - Remote sync targets
- **sync_mappings** - Entity synchronization mappings
- **sync_operations** - Sync operation history
- **user_sync_api_keys** - Sync API credentials

### Migrations

- **migrations_state** - Migration history
- **migrations_metadata** - Migration metadata

## Data Transformations

The tool applies several automatic transformations:

1. **MongoDB ObjectIds** → Text IDs (uses existing `id` field)
2. **Timestamps** → ISO 8601 strings
3. **Booleans** → Integers (0/1)
4. **Objects/Arrays** → JSON strings
5. **Missing timestamps** → Auto-generated with sensible defaults
6. **Chat message denormalization** → Converts embedded message arrays to individual rows

## Examples

### Migrate Local Development Database

```bash
mongodb-to-sqlite \
  -m "mongodb://localhost:27017" \
  -d quilltap \
  -o ./quilltap-local.db \
  -v
```

### Migrate from Remote MongoDB Atlas

```bash
mongodb-to-sqlite \
  -m "mongodb+srv://user:password@cluster.mongodb.net/quilltap" \
  -o ~/data/quilltap.db
```

### Preview Migration Impact

```bash
mongodb-to-sqlite \
  -m "mongodb://localhost:27017" \
  -d quilltap \
  --dry-run
```

Output:

```text
Collection Record Counts:
==================================================
  users                                        12
  characters                                   45
  chats                                       230
  chat_messages                              5420
  memories                                    180
  ...
==================================================
  TOTAL                                      12547
```

## Requirements

- Node.js 16+
- MongoDB 3.6+ (source database)
- Write access to output directory

## Troubleshooting

### Connection Issues

**Problem:** Cannot connect to MongoDB

```text
Error: connect ECONNREFUSED 127.0.0.1:27017
```

**Solution:**

- Verify MongoDB is running
- Check the connection URI is correct
- Ensure network/firewall allows access

### Permission Issues

**Problem:** Cannot write output file

```text
Error: EACCES: permission denied, open '/path/to/quilltap.db'
```

**Solution:**

- Ensure output directory exists and is writable
- Run with appropriate permissions
- Check disk space availability

### Migration Failures

Use `--verbose` flag to see detailed error information:

```bash
mongodb-to-sqlite -m "mongodb://localhost:27017" -o ./quilltap.db -v
```

## Performance Notes

- Migrations are processed in batches (100 documents per batch)
- Large collections (>100k documents) may take several minutes
- Use `--dry-run` first to estimate time required
- Ensure sufficient disk space for SQLite database (typically 1.5x MongoDB size)

## License

MIT

## Support

For issues, questions, or contributions, visit the [GitHub repository](https://github.com/foundry-9/quilltap-mongodb-to-sqlite).