experiment: stream CSV import via DuckDB Appender to show progress

[kueda]

Opening a large archive (100GB+) can take 10+ minutes with no meaningful feedback during the DuckDB import step, which is likely the dominant bottleneck. The current create_from_core_files uses a single CREATE TABLE AS SELECT * FROM read_csv(...) — one atomic SQL call with no progress API.

Proposed approach

Replace the read_csv SQL with Rust-side CSV streaming into DuckDB's Appender API in batches. Thread a progress: f64 callback (0.0–1.0) up through create_from_core_files → Archive::open → open_archive, and surface it as real percentage progress on the loading screen.

Key changes

• src-tauri/Cargo.toml: add csv = "1.3.1"
• ArchiveOpenProgress::CreatingDatabase: add progress: f64 field; change internal progress channel from String to the enum directly
• Database::create_from_core_files: add on_progress: F callback parameter; count rows first (cheap line scan), build CREATE TABLE DDL from sniffed columns + TYPE_OVERRIDES, stream rows via Appender in 50k-row batches, calling on_progress after each flush
• dwca/archive.rs: thread progress closure into create_from_core_files
• +page.svelte: read progress.progress on creatingDatabase events; update archiveLoadingProgress derivation to use actual value in the 40–95% range

Type conversion per row

• VARCHAR: pass as &str
• DOUBLE (decimalLatitude, decimalLongitude): parse::<f64>().ok() → Option<f64>, empty → None
• BOOLEAN (captive, hasCoordinate, etc.): match "true"/"1" → Some(true), "false"/"0" → Some(false), "" → None

Extension tables are left on read_csv for now (usually much smaller than occurrences).

Risks

Risk	Mitigation
Appender column order diverges from CREATE TABLE	Build both from a single shared Vec<(col, type)>
BOOLEAN format variation across archives	Lowercase + check both forms; warn on unrecognised values
Archives with no rows	Guard total_rows == 0, call on_progress(1.0) immediately
Regression on NULL / empty-column-drop behaviour	Existing tests cover this; add explicit test for NULL handling in Appender path

Why this is an experiment

Estimated 50k–100k tokens to implement, with the wide range driven by how finicky the Appender type handling turns out to be. The improvement is real but only matters for very large archives. Worth doing if those users are a priority.

[kueda]

Implementation Plan

Overview

Replace the single-shot CREATE TABLE AS SELECT * FROM read_csv(...) with a Rust-side CSV read feeding DuckDB's Appender API in batches. Thread a progress: f64 callback (0.0–1.0) through the call stack and surface it as real progress on the loading screen.

---

Step 1 — Add csv crate to src-tauri

chuck-core already has csv = "1.3.1". Add the same to src-tauri/Cargo.toml. It's not in src-tauri currently.

---

Step 2 — Add progress to the CreatingDatabase variant

In src-tauri/src/commands/archive.rs, change:

CreatingDatabase,

to:

CreatingDatabase { progress: f64 },  // 0.0–1.0

The progress channel currently passes String. Change it to pass the ArchiveOpenProgress enum directly instead of strings, so create_from_core_files can emit intermediate CreatingDatabase { progress } events without string encoding. This means updating Archive::open's closure signature and the channel match arms in open_archive.

---

Step 3 — Thread the callback into create_from_core_files

Archive::open → Database::create_from_core_files currently has no progress parameter. Add one:

pub fn create_from_core_files<F: FnMut(f64)>(
    core_files: &[PathBuf],
    extensions: &[ExtensionInfo],
    db_path: &Path,
    core_id_column: &str,
    mut on_progress: F,
) -> Result<Self>

In dwca/archive.rs, pass a closure that sends a CreatingDatabase { progress } event through the channel.

---

Step 4 — Replace read_csv SQL with Appender streaming for occurrences

This is the core change, all inside create_from_core_files:

4a. Count total rows — iterate all core_files, count newlines minus one header each. This is a cheap O(n) pass but avoids loading the whole file. Store as total_rows: u64.

4b. Create empty table — instead of CREATE TABLE AS SELECT *, use sniff_csv to get column names (existing logic), then build a CREATE TABLE occurrences (col1 TYPE, col2 TYPE, ...) DDL manually. Most columns are VARCHAR; apply TYPE_OVERRIDES for the known typed columns. Keep NULLSTR behavior by converting empty strings to None during row iteration.

4c. Stream rows via Appender — iterate core_files again with the csv crate:

let mut appender = conn.appender("occurrences")?;
let mut rows_done: u64 = 0;
const BATCH: u64 = 50_000;

for file in core_files {
    let mut rdr = csv::Reader::from_path(file)?;
    for record in rdr.records() {
        let record = record?;
        appender.append_row(params![/* typed fields */])?;
        rows_done += 1;
        if rows_done % BATCH == 0 {
            appender.flush()?;
            on_progress(rows_done as f64 / total_rows as f64);
        }
    }
}
appender.flush()?;
on_progress(1.0);

4d. Type conversion per row — for each field, apply:

• VARCHAR: pass as &str, empty string stays as empty string (DuckDB NULLs columns in post-step anyway via drop_empty_columns)
• DOUBLE columns: field.parse::<f64>().ok() → Option<f64>; empty string → None
• BOOLEAN columns: match "true"/"1" → Some(true), "false"/"0" → Some(false), "" → None

The column ordering in the Appender params must exactly match the column order from the CREATE TABLE DDL — tie these together via a shared Vec<(name, type)> built once from sniff_csv.

4e. Leave extension tables as-is for now — they're typically much smaller (multimedia, etc.). Can apply the same pattern in a follow-up.

---

Step 5 — Update the frontend

+page.svelte:

• Add let archiveDbProgress = $state(0)
• In the creatingDatabase case of the event handler, read progress.progress and set archiveDbProgress
• Update archiveLoadingProgress derivation:

  case 'creatingDatabase':
    return 40 + archiveDbProgress * 55;  // 40–95% range

• Update the loading text: "Creating database... (N%)" using archiveDbProgress

---

Step 6 — Tests

• Add a unit test in database.rs that creates a large-ish CSV (e.g. 200k rows), calls create_from_core_files with a progress callback, and asserts the callback fires multiple times with monotonically increasing values and ends at 1.0.
• Run the existing cargo test suite to catch any regression in NULL handling, BOOLEAN parsing, etc.
• Manual smoke test opening a real large archive.

---

Risk areas

Risk	Mitigation
Appender column order diverges from CREATE TABLE	Build both from a single shared Vec<(col, type)>
BOOLEAN format variation across archives ("true" vs "1" vs "True")	Lowercase + check both forms; emit a warning log on unrecognised values
Archives where core files have no rows	Guard total_rows == 0 → skip streaming, call on_progress(1.0) immediately
Regression on existing nullstr / empty-column-drop behaviour	Existing tests cover this; NULL handling in Appender path needs explicit test