docs/src/sqlite-migration.md
Rama main · réplica de lectura del repositorio original.
# SQLite Database Migrations
Moltis uses [sqlx](https://github.com/launchbadge/sqlx) for database access and its
built-in migration system for schema management. Each crate owns its migrations,
keeping schema definitions close to the code that uses them.
## Architecture
Each crate that uses SQLite has its own `migrations/` directory and exposes a
`run_migrations()` function. The gateway orchestrates running all migrations at
startup in the correct dependency order.
```
crates/
├── projects/
│ ├── migrations/
│ │ └── 20240205100000_init.sql # projects table
│ └── src/lib.rs # run_migrations()
├── sessions/
│ ├── migrations/
│ │ └── 20240205100001_init.sql # sessions, channel_sessions, session_state
│ └── src/lib.rs # run_migrations()
├── cron/
│ ├── migrations/
│ │ └── 20240205100002_init.sql # cron_jobs, cron_runs
│ └── src/lib.rs # run_migrations()
├── gateway/
│ ├── migrations/
│ │ └── 20240205100003_init.sql # auth, message_log, channels, agents, ...
│ └── src/server/
│ └── prepare_core.rs # orchestrates moltis.db migrations
├── webhooks/
│ ├── migrations/
│ │ └── 20260407000000_initial.sql # webhooks, webhook_deliveries, ...
│ └── src/lib.rs # run_migrations()
├── vault/
│ ├── migrations/
│ │ └── 20260214000001_vault_metadata.sql # vault_metadata
│ └── src/lib.rs # run_migrations() (feature-gated)
└── memory/
├── migrations/
│ └── 20240205100004_init.sql # files, chunks, embedding_cache, FTS
└── src/lib.rs # run_migrations() (separate memory.db)
```
## How It Works
### Migration Ownership
Each crate is autonomous and owns its schema:
| Crate | Database | Tables | Migration File |
|-------|----------|--------|----------------|
| `moltis-projects` | `moltis.db` | `projects` | `20240205100000_init.sql` |
| `moltis-sessions` | `moltis.db` | `sessions`, `channel_sessions`, `session_state` | `20240205100001_init.sql` + 9 migrations |
| `moltis-cron` | `moltis.db` | `cron_jobs`, `cron_runs` | `20240205100002_init.sql` + 1 migration |
| `moltis-gateway` | `moltis.db` | `auth_*`, `passkeys`, `api_keys`, `env_variables`, `message_log`, `channels`, `agents`, `session_shares`, `device_pairing`, `ssh_keys`, `ssh_targets`, `auth_audit_log` | `20240205100003_init.sql` + 12 migrations |
| `moltis-webhooks` | `moltis.db` | `webhooks`, `webhook_deliveries`, `webhook_response_actions` | `20260407000000_initial.sql` + 1 migration |
| `moltis-vault` | `moltis.db` | `vault_metadata` | `20260214000001_vault_metadata.sql` (feature-gated) |
| `moltis-memory` | `memory.db` | `files`, `chunks`, `embedding_cache`, `chunks_fts` | `20240205100004_init.sql` |
### Startup Sequence
The gateway runs migrations in dependency order via
`crates/gateway/src/server/prepare_core.rs`:
```rust
moltis_projects::run_migrations(&db_pool).await?; // 1. projects first
moltis_sessions::run_migrations(&db_pool).await?; // 2. sessions (FK → projects)
moltis_cron::run_migrations(&db_pool).await?; // 3. cron (independent)
moltis_webhooks::run_migrations(&db_pool).await?; // 4. webhooks (independent)
crate::run_migrations(&db_pool).await?; // 5. gateway tables
#[cfg(feature = "vault")]
moltis_vault::run_migrations(&db_pool).await?; // 6. vault (feature-gated)
```
Sessions depends on projects due to a foreign key (`sessions.project_id` references
`projects.id`), so projects must migrate first. Memory runs separately against
its own `memory.db` pool.
### Version Tracking
sqlx tracks applied migrations in the `_sqlx_migrations` table:
```sql
SELECT version, description, installed_on, success FROM _sqlx_migrations;
```
Migrations are identified by their timestamp prefix (e.g., `20240205100000`), which
must be globally unique across all crates.
## Database Files
| Database | Location | Crates |
|----------|----------|--------|
| `moltis.db` | `~/.moltis/moltis.db` | projects, sessions, cron, gateway, webhooks, vault |
| `memory.db` | `~/.moltis/memory.db` | memory (separate, managed internally) |
## Adding New Migrations
### Adding a Column to an Existing Table
1. Create a new migration file in the owning crate:
```bash
# Example: adding tags to sessions
touch crates/sessions/migrations/20240301120000_add_tags.sql
```
2. Write the migration SQL:
```sql
-- 20240301120000_add_tags.sql
ALTER TABLE sessions ADD COLUMN tags TEXT;
CREATE INDEX IF NOT EXISTS idx_sessions_tags ON sessions(tags);
```
3. Rebuild to embed the migration:
```bash
cargo build
```
### Adding a New Table to an Existing Crate
1. Create the migration file with a new timestamp:
```bash
touch crates/sessions/migrations/20240302100000_session_bookmarks.sql
```
2. Write the CREATE TABLE statement:
```sql
-- 20240302100000_session_bookmarks.sql
CREATE TABLE IF NOT EXISTS session_bookmarks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
session_key TEXT NOT NULL,
name TEXT NOT NULL,
message_id INTEGER NOT NULL,
created_at INTEGER NOT NULL
);
```
### Adding Tables to a New Crate
1. Create the migrations directory:
```bash
mkdir -p crates/new-feature/migrations
```
2. Create the migration file with a globally unique timestamp:
```bash
touch crates/new-feature/migrations/20240401100000_init.sql
```
3. Add `run_migrations()` to the crate's `lib.rs`:
```rust
pub async fn run_migrations(pool: &sqlx::SqlitePool) -> anyhow::Result<()> {
sqlx::migrate!("./migrations").run(pool).await?;
Ok(())
}
```
4. Call it from `prepare_core.rs` in the appropriate order:
```rust
moltis_new_feature::run_migrations(&db_pool).await?;
```
## Timestamp Convention
Use `YYYYMMDDHHMMSS` format for migration filenames:
- `YYYY` - 4-digit year
- `MM` - 2-digit month
- `DD` - 2-digit day
- `HH` - 2-digit hour (24h)
- `MM` - 2-digit minute
- `SS` - 2-digit second
This ensures global uniqueness across crates. When adding migrations, use the
current timestamp to avoid collisions.
## SQLite Limitations
### ALTER TABLE
SQLite has limited `ALTER TABLE` support:
- **ADD COLUMN**: Supported ✓
- **DROP COLUMN**: SQLite 3.35+ only
- **Rename column**: Requires table recreation
- **Change column type**: Requires table recreation
For complex schema changes, use the table recreation pattern:
```sql
-- Create new table with desired schema
CREATE TABLE sessions_new (
-- new schema
);
-- Copy data (map old columns to new)
INSERT INTO sessions_new SELECT ... FROM sessions;
-- Swap tables
DROP TABLE sessions;
ALTER TABLE sessions_new RENAME TO sessions;
-- Recreate indexes
CREATE INDEX idx_sessions_created_at ON sessions(created_at);
```
### Foreign Keys
SQLite foreign keys are checked at insert/update time, not migration time. Ensure
migrations run in dependency order (parent table first).
## Testing
Unit tests use in-memory databases with the crate's `init()` method:
```rust
#[tokio::test]
async fn test_session_operations() {
let pool = SqlitePool::connect("sqlite::memory:").await.unwrap();
// Create schema for tests (init() retained for this purpose)
SqliteSessionMetadata::init(&pool).await.unwrap();
let meta = SqliteSessionMetadata::new(pool);
// ... test code
}
```
The `init()` methods are retained (marked `#[doc(hidden)]`) specifically for tests.
In production, migrations handle schema creation.
## Troubleshooting
### "failed to run migrations"
1. Check file permissions on `~/.moltis/`
2. Ensure the database file isn't locked by another process
3. Check for syntax errors in migration SQL files
### Migration Order Issues
If you see foreign key errors, verify the migration order in `prepare_core.rs`. Parent
tables must be created before child tables with FK references.
### Checking Migration Status
```bash
sqlite3 ~/.moltis/moltis.db "SELECT version, description, success FROM _sqlx_migrations ORDER BY version"
```
### Resetting Migrations (Development Only)
```bash
# Backup first!
rm ~/.moltis/moltis.db
cargo run # Creates fresh database with all migrations
```
## Best Practices
### DO
- Use timestamp-based version numbers for global uniqueness
- Keep each crate's migrations in its own directory
- Use `IF NOT EXISTS` for idempotent initial migrations
- Test migrations on a copy of production data before deploying
- Keep migrations small and focused
### DON'T
- Modify existing migration files after deployment
- Reuse timestamps across crates
- Put multiple crates' tables in one migration file
- Skip the dependency order in `prepare_core.rs`