elph_core/floppy/
migrations.rs

1//! Versioned schema migrations for the floppy memory store.
2//!
3//! Schema derived from [memelord](https://github.com/glommer/memelord) (`packages/sdk`).
4//! Host applications can map [`FloppyMigration`] entries into their own migration runner.
5
6use anyhow::Result;
7use turso::Connection;
8
9use super::util::drain_rows;
10
11/// One versioned SQL migration for the floppy store.
12///
13/// Field layout matches common host migration runners so consumers can map entries
14/// without coupling this module to a specific application crate.
15#[derive(Debug, Clone, Copy)]
16pub struct FloppyMigration {
17    pub version: i64,
18    pub name: &'static str,
19    pub up: &'static str,
20}
21
22pub const V1_NAME: &str = "floppy_create_schema";
23pub const V1_UP: &str = r#"
24CREATE TABLE IF NOT EXISTS memories (
25    id              TEXT PRIMARY KEY,
26    content         TEXT NOT NULL,
27    embedding       BLOB,
28    category        TEXT NOT NULL,
29    weight          REAL DEFAULT 1.0,
30    initial_cost    INTEGER DEFAULT 0,
31    created_at      INTEGER NOT NULL,
32    last_retrieved  INTEGER,
33    retrieval_count INTEGER DEFAULT 0,
34    source_task     TEXT
35);
36
37CREATE TABLE IF NOT EXISTS tasks (
38    id               TEXT PRIMARY KEY,
39    description      TEXT,
40    embedding        BLOB,
41    tokens_used      INTEGER,
42    tool_calls       INTEGER,
43    errors           INTEGER,
44    user_corrections INTEGER,
45    completed        INTEGER,
46    task_score       REAL,
47    started_at       INTEGER,
48    finished_at      INTEGER
49);
50
51CREATE TABLE IF NOT EXISTS memory_retrievals (
52    memory_id   TEXT,
53    task_id     TEXT,
54    similarity  REAL,
55    self_report REAL,
56    credit      REAL,
57    PRIMARY KEY (memory_id, task_id)
58);
59
60CREATE TABLE IF NOT EXISTS meta (
61    key   TEXT PRIMARY KEY,
62    value TEXT NOT NULL
63)"#;
64
65pub const V2_NAME: &str = "floppy_fix_truncated_embeddings";
66pub const V2_UP: &str = "UPDATE memories SET embedding = NULL WHERE embedding IS NOT NULL AND length(embedding) < 1536";
67
68pub const V3_NAME: &str = "floppy_query_indexes";
69pub const V3_UP: &str = r#"
70CREATE INDEX IF NOT EXISTS idx_memories_category ON memories(category);
71CREATE INDEX IF NOT EXISTS idx_memories_source_task ON memories(source_task);
72CREATE INDEX IF NOT EXISTS idx_memories_created_at ON memories(created_at DESC);
73CREATE INDEX IF NOT EXISTS idx_memory_retrievals_task_id ON memory_retrievals(task_id);
74CREATE INDEX IF NOT EXISTS idx_tasks_started_at ON tasks(started_at DESC);
75CREATE INDEX IF NOT EXISTS idx_memories_pending_embed ON memories(id) WHERE embedding IS NULL"#;
76
77/// Latest floppy schema version. Hosts should start custom migrations above this.
78pub const LAST_VERSION: i64 = 3;
79
80/// Canonical floppy migration set — inject or extend in the host migration registry.
81pub const MIGRATIONS: &[FloppyMigration] = &[
82    FloppyMigration {
83        version: 1,
84        name: V1_NAME,
85        up: V1_UP,
86    },
87    FloppyMigration {
88        version: 2,
89        name: V2_NAME,
90        up: V2_UP,
91    },
92    FloppyMigration {
93        version: 3,
94        name: V3_NAME,
95        up: V3_UP,
96    },
97];
98
99/// Apply pending floppy migrations using the shared `app_migrations` ledger.
100pub async fn apply(conn: &Connection) -> Result<()> {
101    run(conn, MIGRATIONS).await
102}
103
104async fn run(conn: &Connection, migrations: &[FloppyMigration]) -> Result<()> {
105    conn.execute(
106        "CREATE TABLE IF NOT EXISTS app_migrations (
107            id INTEGER PRIMARY KEY AUTOINCREMENT,
108            version INTEGER NOT NULL,
109            name TEXT NOT NULL,
110            applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
111        )",
112        (),
113    )
114    .await?;
115
116    conn.execute(
117        "CREATE UNIQUE INDEX IF NOT EXISTS idx_app_migrations_version
118         ON app_migrations(version)",
119        (),
120    )
121    .await?;
122
123    let current_version = {
124        let mut rows = conn
125            .query("SELECT COALESCE(MAX(version), 0) FROM app_migrations", ())
126            .await?;
127        let version = if let Some(row) = rows.next().await? {
128            row.get::<i64>(0)?
129        } else {
130            0
131        };
132        // Drain the cursor before DDL — Turso blocks execute_batch on open queries.
133        drain_rows(&mut rows).await?;
134        version
135    };
136
137    for migration in migrations {
138        if migration.version <= current_version {
139            continue;
140        }
141
142        // Turso requires execute_batch for multi-statement DDL (split execute is unreliable).
143        conn.execute_batch(migration.up).await?;
144
145        conn.execute(
146            "INSERT INTO app_migrations (version, name) VALUES (?, ?)",
147            (migration.version, migration.name),
148        )
149        .await?;
150    }
151
152    Ok(())
153}
154
155#[cfg(test)]
156mod tests {
157    use super::*;
158    use turso::Builder;
159
160    #[tokio::test]
161    async fn apply_creates_floppy_tables() {
162        let dir = tempfile::tempdir().expect("tempdir");
163        let db_path = dir.path().join("memory.db");
164        let db = Builder::new_local(db_path.to_string_lossy().as_ref())
165            .experimental_multiprocess_wal(true)
166            .build()
167            .await
168            .expect("build");
169        let conn = db.connect().expect("connect");
170
171        apply(&conn).await.expect("apply");
172
173        let mut rows = conn
174            .query("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name", ())
175            .await
176            .expect("tables");
177        let mut tables = Vec::new();
178        while let Some(row) = rows.next().await.expect("row") {
179            tables.push(row.get::<String>(0).expect("name"));
180        }
181
182        for table in ["app_migrations", "memories", "memory_retrievals", "meta", "tasks"] {
183            assert!(tables.contains(&table.to_string()), "missing table {table}: {tables:?}");
184        }
185    }
186}
elph_core/floppy/migrations.rs

elph_core/floppy/
migrations.rs
