Fix SQLite disk I/O on bucket mount

Dockerfile

@@ -4,7 +4,8 @@ ENV PYTHONDONTWRITEBYTECODE=1 \
     PYTHONUNBUFFERED=1 \
     UV_COMPILE_BYTECODE=1 \
     UV_LINK_MODE=copy \
-    WATERLEAF_DATA_DIR=/data
+    WATERLEAF_DATA_DIR=/data \
+    WATERLEAF_DATABASE_DIR=/tmp/waterleaf-db
 
 RUN pip install --no-cache-dir uv==0.11.8 \
     && useradd --create-home --uid 1000 user \

README.md

@@ -41,7 +41,8 @@ Hackathon.
 
 - **UI and web:** Gradio Blocks mounted in FastAPI
 - **Authentication:** Hugging Face OAuth
-- **Persistence:** SQLite and normalized JPEGs on an attached HF Storage Bucket
+- **Persistence:** Local SQLite with an HF Storage Bucket snapshot, plus bucket-backed
+  JPEGs and calendar exports
 - **Vision model:** `ggml-org/gemma-4-26B-A4B-it-GGUF`
 - **Runtime:** llama.cpp `server-cuda13-b9445` on a Modal L4
 - **Taxonomy:** GBIF Species API
@@ -193,8 +194,11 @@ In **Space Settings → Storage Buckets**:
 2. Attach it read-write.
 3. Set the mount path to `/data`.
 
-The Docker image already sets `WATERLEAF_DATA_DIR=/data`. Without this mount,
-saved gardens and images disappear when the Space restarts.
+The Docker image sets `WATERLEAF_DATA_DIR=/data` for bucket-backed media,
+exports, and database snapshots. It keeps the live SQLite file under
+`WATERLEAF_DATABASE_DIR=/tmp/waterleaf-db` so SQLite uses local container disk
+instead of the bucket mount for file locking. Without the bucket mount, images,
+calendar exports, and saved-garden snapshots disappear when the Space restarts.
 
 ### 4. Configure secrets and variables
 
@@ -207,6 +211,7 @@ In **Space Settings → Variables and secrets**, add:
 | `MODAL_SECRET` | Secret | Production | Modal proxy token secret |
 | `PUBLIC_BASE_URL` | Variable | Optional | Override the derived Space URL |
 | `WATERLEAF_DATA_DIR` | Variable | No | Defaults to `/data` in Docker |
+| `WATERLEAF_DATABASE_DIR` | Variable | No | Defaults to `/tmp/waterleaf-db` in Docker |
 
 Use only the base Modal URL for `MODAL_ENDPOINT`; do not append
 `/v1/chat/completions`.

docs/architecture.md

@@ -5,7 +5,7 @@ flowchart LR
     U[Gardener] --> G[Gradio Blocks]
     G --> A[FastAPI application]
     A --> O[Hugging Face OAuth]
-    A --> S[(SQLite + images\nHF Storage Bucket)]
+    A --> S[(Local SQLite\nbucket snapshot + images)]
     A --> M[Modal proxy-auth endpoint]
     M --> L[llama.cpp\nGemma 4 26B-A4B GGUF]
     A --> B[GBIF taxonomy]

docs/field-notes.md

@@ -22,7 +22,7 @@ flowchart LR
     U[Gardener] --> G[Gradio Blocks]
     G --> A[FastAPI application]
     A --> O[Hugging Face OAuth]
-    A --> S[(SQLite + images\nHF Storage Bucket)]
+    A --> S[(Local SQLite\nbucket snapshot + images)]
     A --> M[Modal proxy-auth endpoint]
     M --> L[llama.cpp\nGemma 4 26B-A4B GGUF]
     A --> B[GBIF taxonomy]

tests/test_runtime.py

@@ -11,6 +11,19 @@ def test_settings_derive_public_url_from_space_host(monkeypatch, tmp_path):
 
     assert settings.public_base_url == "https://hkaraoguz-waterleaf.hf.space"
     assert settings.database_path == tmp_path / "waterleaf.sqlite3"
+    assert settings.database_snapshot_path is None
+
+
+def test_settings_can_keep_sqlite_database_off_data_mount(monkeypatch, tmp_path):
+    data_dir = tmp_path / "data"
+    database_dir = tmp_path / "db"
+    monkeypatch.setenv("WATERLEAF_DATA_DIR", str(data_dir))
+    monkeypatch.setenv("WATERLEAF_DATABASE_DIR", str(database_dir))
+
+    settings = Settings.from_env()
+
+    assert settings.database_path == database_dir / "waterleaf.sqlite3"
+    assert settings.database_snapshot_path == data_dir / "waterleaf.sqlite3"
 
 
 def test_runtime_uses_demo_identification_without_modal_endpoint(tmp_path):

tests/test_storage.py

@@ -81,3 +81,36 @@ def test_store_replaces_schedule_atomically(tmp_path):
         {"date": "2026-06-14", "reason": "Edited", "confidence": "manual"}
     ]
 
+
+def test_store_uses_local_database_and_syncs_snapshot(tmp_path):
+    snapshot_path = tmp_path / "bucket" / "waterleaf.sqlite3"
+    seed_store = GardenStore(snapshot_path)
+    seed = seed_store.save_plant("alice", _draft())
+    seed_store.replace_schedule(
+        "alice",
+        seed.id,
+        [{"date": "2026-06-14", "reason": "Seed", "confidence": "manual"}],
+    )
+
+    local_path = tmp_path / "local" / "waterleaf.sqlite3"
+    store = GardenStore(local_path, snapshot_path=snapshot_path)
+    saved = store.save_plant("alice", _draft())
+    store.replace_schedule(
+        "alice",
+        saved.id,
+        [{"date": "2026-06-15", "reason": "Synced", "confidence": "manual"}],
+    )
+
+    assert local_path.exists()
+    assert store.get_schedule("alice", seed.id) == [
+        {"date": "2026-06-14", "reason": "Seed", "confidence": "manual"}
+    ]
+
+    reloaded = GardenStore(
+        tmp_path / "reloaded" / "waterleaf.sqlite3",
+        snapshot_path=snapshot_path,
+    )
+    assert {plant.id for plant in reloaded.list_plants("alice")} == {seed.id, saved.id}
+    assert reloaded.get_schedule("alice", saved.id) == [
+        {"date": "2026-06-15", "reason": "Synced", "confidence": "manual"}
+    ]

waterleaf/runtime.py

@@ -30,7 +30,7 @@ def build_application(settings: Settings | None = None) -> WaterleafApplication:
         identification = build_demo_identification()
 
     return WaterleafApplication(
-        store=GardenStore(settings.database_path),
+        store=GardenStore(settings.database_path, snapshot_path=settings.database_snapshot_path),
         media_directory=settings.media_directory,
         export_directory=settings.export_directory,
         public_base_url=settings.public_base_url,

waterleaf/settings.py

@@ -12,9 +12,16 @@ class Settings:
     modal_endpoint: str | None
     modal_key: str | None
     modal_secret: str | None
+    database_directory: Path | None = None
 
     @property
     def database_path(self) -> Path:
+        return (self.database_directory or self.data_directory) / "waterleaf.sqlite3"
+
+    @property
+    def database_snapshot_path(self) -> Path | None:
+        if self.database_directory is None or self.database_directory == self.data_directory:
+            return None
         return self.data_directory / "waterleaf.sqlite3"
 
     @property
@@ -28,6 +35,7 @@ class Settings:
     @classmethod
     def from_env(cls) -> Settings:
         data_directory = Path(os.getenv("WATERLEAF_DATA_DIR", "data"))
+        database_directory = os.getenv("WATERLEAF_DATABASE_DIR")
         public_base_url = os.getenv("PUBLIC_BASE_URL")
         if not public_base_url:
             space_host = os.getenv("SPACE_HOST")
@@ -40,4 +48,5 @@ class Settings:
             modal_endpoint=os.getenv("MODAL_ENDPOINT"),
             modal_key=os.getenv("MODAL_KEY"),
             modal_secret=os.getenv("MODAL_SECRET"),
+            database_directory=Path(database_directory) if database_directory else None,
         )

waterleaf/storage.py

@@ -2,7 +2,9 @@ from __future__ import annotations
 
 import json
 import secrets
+import shutil
 import sqlite3
+import threading
 import uuid
 from datetime import time
 from pathlib import Path
@@ -12,10 +14,15 @@ from waterleaf.models import PlantDraft, PublicPlant, SavedPlant
 
 
 class GardenStore:
-    def __init__(self, database_path: str | Path):
+    def __init__(self, database_path: str | Path, *, snapshot_path: str | Path | None = None):
         self.database_path = Path(database_path)
+        self.snapshot_path = Path(snapshot_path) if snapshot_path else None
+        self._lock = threading.RLock()
         self.database_path.parent.mkdir(parents=True, exist_ok=True)
+        if self.snapshot_path and self.snapshot_path.exists() and not self.database_path.exists():
+            shutil.copy2(self.snapshot_path, self.database_path)
         self._initialize()
+        self._sync_snapshot()
 
     def _connect(self) -> sqlite3.Connection:
         connection = sqlite3.connect(self.database_path)
@@ -24,107 +31,119 @@ class GardenStore:
         return connection
 
     def _initialize(self) -> None:
-        with self._connect() as connection:
-            connection.executescript(
-                """
-                CREATE TABLE IF NOT EXISTS plants (
-                    id TEXT PRIMARY KEY,
-                    owner TEXT NOT NULL,
-                    public_slug TEXT NOT NULL UNIQUE,
-                    nickname TEXT NOT NULL,
-                    common_name TEXT NOT NULL,
-                    scientific_name TEXT NOT NULL,
-                    taxon_key TEXT NOT NULL,
-                    location_name TEXT NOT NULL,
-                    latitude REAL NOT NULL,
-                    longitude REAL NOT NULL,
-                    timezone TEXT NOT NULL,
-                    preferred_time TEXT NOT NULL,
-                    is_container INTEGER NOT NULL,
-                    size_label TEXT NOT NULL,
-                    image_id TEXT NOT NULL,
-                    care_min_days INTEGER,
-                    care_max_days INTEGER,
-                    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
-                );
-                CREATE INDEX IF NOT EXISTS idx_plants_owner ON plants(owner);
-
-                CREATE TABLE IF NOT EXISTS schedules (
-                    plant_id TEXT PRIMARY KEY,
-                    owner TEXT NOT NULL,
-                    events_json TEXT NOT NULL,
-                    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
-                    FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
-                );
-                """
-            )
+        with self._lock:
+            with self._connect() as connection:
+                connection.executescript(
+                    """
+                    CREATE TABLE IF NOT EXISTS plants (
+                        id TEXT PRIMARY KEY,
+                        owner TEXT NOT NULL,
+                        public_slug TEXT NOT NULL UNIQUE,
+                        nickname TEXT NOT NULL,
+                        common_name TEXT NOT NULL,
+                        scientific_name TEXT NOT NULL,
+                        taxon_key TEXT NOT NULL,
+                        location_name TEXT NOT NULL,
+                        latitude REAL NOT NULL,
+                        longitude REAL NOT NULL,
+                        timezone TEXT NOT NULL,
+                        preferred_time TEXT NOT NULL,
+                        is_container INTEGER NOT NULL,
+                        size_label TEXT NOT NULL,
+                        image_id TEXT NOT NULL,
+                        care_min_days INTEGER,
+                        care_max_days INTEGER,
+                        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
+                    );
+                    CREATE INDEX IF NOT EXISTS idx_plants_owner ON plants(owner);
+
+                    CREATE TABLE IF NOT EXISTS schedules (
+                        plant_id TEXT PRIMARY KEY,
+                        owner TEXT NOT NULL,
+                        events_json TEXT NOT NULL,
+                        updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
+                        FOREIGN KEY (plant_id) REFERENCES plants(id) ON DELETE CASCADE
+                    );
+                    """
+                )
+
+    def _sync_snapshot(self) -> None:
+        if not self.snapshot_path:
+            return
+        self.snapshot_path.parent.mkdir(parents=True, exist_ok=True)
+        shutil.copy2(self.database_path, self.snapshot_path)
 
     def save_plant(self, owner: str, draft: PlantDraft) -> SavedPlant:
-        plant_id = str(uuid.uuid4())
-        public_slug = secrets.token_urlsafe(18)
-        values = (
-            plant_id,
-            owner,
-            public_slug,
-            draft.nickname.strip(),
-            draft.common_name.strip(),
-            draft.scientific_name.strip(),
-            draft.taxon_key,
-            draft.location_name.strip(),
-            round(draft.latitude, 2),
-            round(draft.longitude, 2),
-            draft.timezone,
-            draft.preferred_time.isoformat(timespec="minutes"),
-            int(draft.is_container),
-            draft.size_label,
-            draft.image_id,
-            draft.care_min_days,
-            draft.care_max_days,
-        )
-        with self._connect() as connection:
-            connection.execute(
-                """
-                INSERT INTO plants (
-                    id, owner, public_slug, nickname, common_name, scientific_name,
-                    taxon_key, location_name, latitude, longitude, timezone,
-                    preferred_time, is_container, size_label, image_id,
-                    care_min_days, care_max_days
-                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
-                """,
-                values,
+        with self._lock:
+            plant_id = str(uuid.uuid4())
+            public_slug = secrets.token_urlsafe(18)
+            values = (
+                plant_id,
+                owner,
+                public_slug,
+                draft.nickname.strip(),
+                draft.common_name.strip(),
+                draft.scientific_name.strip(),
+                draft.taxon_key,
+                draft.location_name.strip(),
+                round(draft.latitude, 2),
+                round(draft.longitude, 2),
+                draft.timezone,
+                draft.preferred_time.isoformat(timespec="minutes"),
+                int(draft.is_container),
+                draft.size_label,
+                draft.image_id,
+                draft.care_min_days,
+                draft.care_max_days,
             )
-        plant = self.get_plant(owner, plant_id)
+            with self._connect() as connection:
+                connection.execute(
+                    """
+                    INSERT INTO plants (
+                        id, owner, public_slug, nickname, common_name, scientific_name,
+                        taxon_key, location_name, latitude, longitude, timezone,
+                        preferred_time, is_container, size_label, image_id,
+                        care_min_days, care_max_days
+                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+                    """,
+                    values,
+                )
+            self._sync_snapshot()
+            plant = self.get_plant(owner, plant_id)
         if plant is None:
             raise RuntimeError("Saved plant could not be loaded")
         return plant
 
     def list_plants(self, owner: str) -> list[SavedPlant]:
-        with self._connect() as connection:
-            rows = connection.execute(
-                "SELECT * FROM plants WHERE owner = ? ORDER BY created_at, id",
-                (owner,),
-            ).fetchall()
+        with self._lock:
+            with self._connect() as connection:
+                rows = connection.execute(
+                    "SELECT * FROM plants WHERE owner = ? ORDER BY created_at, id",
+                    (owner,),
+                ).fetchall()
         return [self._plant_from_row(row) for row in rows]
 
     def get_plant(self, owner: str, plant_id: str) -> SavedPlant | None:
-        with self._connect() as connection:
-            row = connection.execute(
-                "SELECT * FROM plants WHERE owner = ? AND id = ?",
-                (owner, plant_id),
-            ).fetchone()
+        with self._lock:
+            with self._connect() as connection:
+                row = connection.execute(
+                    "SELECT * FROM plants WHERE owner = ? AND id = ?",
+                    (owner, plant_id),
+                ).fetchone()
         return self._plant_from_row(row) if row else None
 
     def get_public_plant(self, public_slug: str) -> PublicPlant | None:
-        with self._connect() as connection:
-            row = connection.execute(
-                """
-                SELECT public_slug, nickname, common_name, scientific_name,
-                       image_id, is_container, size_label, care_min_days,
-                       care_max_days
-                FROM plants WHERE public_slug = ?
-                """,
-                (public_slug,),
-            ).fetchone()
+        with self._lock:
+            with self._connect() as connection:
+                row = connection.execute(
+                    """
+                    SELECT public_slug, nickname, common_name, scientific_name,
+                           image_id, is_container, size_label, care_min_days,
+                           care_max_days
+                    FROM plants WHERE public_slug = ?
+                    """,
+                    (public_slug,),
+                ).fetchone()
         if not row:
             return None
         return PublicPlant(
@@ -140,19 +159,24 @@ class GardenStore:
         )
 
     def delete_plant(self, owner: str, plant_id: str) -> bool:
-        with self._connect() as connection:
-            cursor = connection.execute(
-                "DELETE FROM plants WHERE owner = ? AND id = ?",
-                (owner, plant_id),
-            )
-        return cursor.rowcount == 1
+        with self._lock:
+            with self._connect() as connection:
+                cursor = connection.execute(
+                    "DELETE FROM plants WHERE owner = ? AND id = ?",
+                    (owner, plant_id),
+                )
+            deleted = cursor.rowcount == 1
+            if deleted:
+                self._sync_snapshot()
+        return deleted
 
     def image_reference_count(self, image_id: str) -> int:
-        with self._connect() as connection:
-            row = connection.execute(
-                "SELECT COUNT(*) AS count FROM plants WHERE image_id = ?",
-                (image_id,),
-            ).fetchone()
+        with self._lock:
+            with self._connect() as connection:
+                row = connection.execute(
+                    "SELECT COUNT(*) AS count FROM plants WHERE image_id = ?",
+                    (image_id,),
+                ).fetchone()
         return int(row["count"])
 
     def replace_schedule(
@@ -161,41 +185,45 @@ class GardenStore:
         plant_id: str,
         events: list[dict[str, Any]],
     ) -> None:
-        if self.get_plant(owner, plant_id) is None:
-            raise PermissionError("Plant not found for owner")
-        payload = json.dumps(events, separators=(",", ":"), sort_keys=True)
-        with self._connect() as connection:
-            connection.execute(
-                """
-                INSERT INTO schedules (plant_id, owner, events_json)
-                VALUES (?, ?, ?)
-                ON CONFLICT(plant_id) DO UPDATE SET
-                    owner = excluded.owner,
-                    events_json = excluded.events_json,
-                    updated_at = CURRENT_TIMESTAMP
-                """,
-                (plant_id, owner, payload),
-            )
+        with self._lock:
+            if self.get_plant(owner, plant_id) is None:
+                raise PermissionError("Plant not found for owner")
+            payload = json.dumps(events, separators=(",", ":"), sort_keys=True)
+            with self._connect() as connection:
+                connection.execute(
+                    """
+                    INSERT INTO schedules (plant_id, owner, events_json)
+                    VALUES (?, ?, ?)
+                    ON CONFLICT(plant_id) DO UPDATE SET
+                        owner = excluded.owner,
+                        events_json = excluded.events_json,
+                        updated_at = CURRENT_TIMESTAMP
+                    """,
+                    (plant_id, owner, payload),
+                )
+            self._sync_snapshot()
 
     def get_schedule(self, owner: str, plant_id: str) -> list[dict[str, Any]]:
-        with self._connect() as connection:
-            row = connection.execute(
-                "SELECT events_json FROM schedules WHERE owner = ? AND plant_id = ?",
-                (owner, plant_id),
-            ).fetchone()
+        with self._lock:
+            with self._connect() as connection:
+                row = connection.execute(
+                    "SELECT events_json FROM schedules WHERE owner = ? AND plant_id = ?",
+                    (owner, plant_id),
+                ).fetchone()
         return json.loads(row["events_json"]) if row else []
 
     def get_public_schedule(self, public_slug: str) -> list[dict[str, Any]]:
-        with self._connect() as connection:
-            row = connection.execute(
-                """
-                SELECT schedules.events_json
-                FROM schedules
-                JOIN plants ON plants.id = schedules.plant_id
-                WHERE plants.public_slug = ?
-                """,
-                (public_slug,),
-            ).fetchone()
+        with self._lock:
+            with self._connect() as connection:
+                row = connection.execute(
+                    """
+                    SELECT schedules.events_json
+                    FROM schedules
+                    JOIN plants ON plants.id = schedules.plant_id
+                    WHERE plants.public_slug = ?
+                    """,
+                    (public_slug,),
+                ).fetchone()
         return json.loads(row["events_json"]) if row else []
 
     @staticmethod