Database

Migration Rules (non-negotiable)

• Never modify a migration that has already been run — if a mistake is found, create a new migration to correct it

• Never use --ignore-missing — this flag is forbidden; it masks history mismatches and hides real problems

• Always run sqlx migrate run immediately after creating a migration to confirm it applies cleanly before touching anything else

• If the dev DB has migration problems (history mismatch, failed migration, checksum error), reset it — do not work around it:

  rm database/data/db.sqlite
  sqlx database create
  sqlx migrate run   # from database/ crate, picks up DATABASE_URL from .env
  

  Or from workspace root:

  sqlx migrate run --source database/migrations --database-url sqlite://database/data/db.sqlite
  

Project Structure

database/
├── migrations/          # SQLx migration files (timestamp-prefixed)
├── src/
│   ├── lib.rs           # Pool setup, test helpers (setup_test_db, setup_test_repository_manager)
│   ├── models.rs        # DB model structs (FileInfo, FileSetFileInfo, …)
│   ├── repository/      # One file per entity
│   └── repository_manager.rs  # Aggregates all repositories
├── docs/schema/         # tbls-generated ER diagrams and schema.json

Schema Conventions

ON DELETE policy:

• CASCADE — dependent records are deleted with parent (e.g., file_set_file_info when file_set is deleted)
• SET NULL — FK becomes null (used sparingly)
• RESTRICT — prevent deletion if dependents exist

SQLite Migration Constraints

SQLite has limited ALTER TABLE support:

• ✅ ADD COLUMN works
• ❌ DROP COLUMN, RENAME COLUMN, change NOT NULL, change DEFAULT — require recreate-table

Recreate-table pattern:

PRAGMA foreign_keys = OFF;

CREATE TABLE my_table_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    -- new definition here
);

INSERT INTO my_table_new SELECT ... FROM my_table;

DROP TABLE my_table;
ALTER TABLE my_table_new RENAME TO my_table;

-- Recreate any indexes that existed on the original table
-- CREATE INDEX ...

PRAGMA foreign_keys = ON;

Repository Pattern

Each entity gets its own repository in database/src/repository/:

pub struct MyEntityRepository {
    pool: Arc<Pool<Sqlite>>,
}

impl MyEntityRepository {
    pub fn new(pool: Arc<Pool<Sqlite>>) -> Self { Self { pool } }

    pub async fn add(&self, ...) -> Result<i64, Error> { ... }

    // Provide _with_tx variants when the operation participates in cross-entity transactions
    pub async fn add_with_tx(&self, tx: &mut Transaction<'_, Sqlite>, ...) -> Result<i64, Error> { ... }
}

After adding a new repository:

1. Add a field to RepositoryManager in repository_manager.rs
2. Initialise it in RepositoryManager::new
3. Add a getter method returning a reference

Query Patterns

Compile-time verified (preferred):

// INSERT
let result = sqlx::query!(
    "INSERT INTO my_table (col1, col2) VALUES (?, ?)",
    val1, val2
).execute(&*self.pool).await?;

// SELECT with struct mapping
let rows = sqlx::query_as::<_, MyModel>(
    "SELECT id, col1, col2 FROM my_table WHERE id = ?"
).bind(id).fetch_all(&*self.pool).await?;

Dynamic IN clauses (use QueryBuilder):

let mut qb = QueryBuilder::<Sqlite>::new("SELECT * FROM my_table WHERE id IN (");
let mut sep = qb.separated(", ");
for id in ids { sep.push_bind(id); }
sep.push_unseparated(") AND active = true");
let rows = qb.build_query_as::<MyModel>().fetch_all(&*self.pool).await?;

Never use string interpolation or format! to build queries — always use bind parameters.

Type Conversions

Invariants to Enforce

• is_available = true ↔ archive_file_name = Some(...) on file_info
• When inserting into file_info via add_file_info, derive is_available from archive_file_name.is_some() — never rely on the column DEFAULT
• update_is_available must only be called when the file is genuinely becoming available

Testing

Always use in-memory SQLite helpers — never a real file DB:

#[async_std::test]
async fn test_example() {
    let pool = database::setup_test_db().await;
    let repo = MyEntityRepository::new(Arc::new(pool));
    // ...
}

setup_test_db() returns a Pool<Sqlite> backed by sqlite::memory: with all migrations applied. setup_test_repository_manager() wraps it in a full RepositoryManager.

Checklist

After any database change:

• Migration SQL file created with descriptive name
• Migration runs cleanly: cargo sqlx migrate run --source database/migrations --database-url sqlite://database/data/db.sqlite
• cargo sqlx prepare --workspace -- --all-targets
• tbls doc --force
• cargo test -p database passes
• Migration file + .sqlx/ + database/docs/schema/ committed together