feat: prod-ready sqlite

refactor: remove migration scripts

2 files changed, 59 insertions, 84 deletions

diff --git a/README b/README
index f38a8d1..1494a32 100644
--- a/README
+++ b/README
@@ -3,25 +3,29 @@ shim
 
 small static site host for archive uploads.
 
-
 what it does
-- users upload one archive, app publishes it under a slug
-- public routes: /s/<slug>/... and /_site/<slug>/...
+  - users upload one archive, app publishes it under a slug
+  - public routes: /s/<slug>/... and /_site/<slug>/...
 
 quick start (assumes POSIX)
-- python3 -m venv .venv
-- source .venv/bin/activate
-- pip install -r requirements.txt
-- python3 server.py
-- open http://127.0.0.1:8585/app
+  - python3 -m venv .venv
+  - source .venv/bin/activate
+  - pip install -r requirements.txt
+  - python3 server.py
+  - open http://127.0.0.1:8585/app
 
 config
-- SHIM_APP_NAME: ui/app name (default: shim)
-- SHIM_BIND: bind address (default: 0.0.0.0)
-- SHIM_PORT: port (default: 8585)
-- SHIM_MOJICRYPT_BIN: mojicrypt path (default: ./vendor/mojicrypt)
-- SHIM_COOKIE_SECURE: auto|true|false (default: auto)
+  - SHIM_APP_NAME: ui/app name (default: shim)
+  - SHIM_BIND: bind address (default: 0.0.0.0)
+  - SHIM_PORT: port (default: 8585)
+  - SHIM_MOJICRYPT_BIN: mojicrypt path (default: ./vendor/mojicrypt)
+  - SHIM_COOKIE_SECURE: auto|true|false (default: auto)
+  - SHIM_SQLITE_TIMEOUT_SECONDS (default: 30.0)
+  - SHIM_SQLITE_BUSY_TIMEOUT_MS (default: 30000)
+  - SHIM_SQLITE_CACHE_SIZE_KIB (default: 32768)
+  - SHIM_SQLITE_MMAP_SIZE_BYTES (default: 268435456)
+  - SHIM_SQLITE_WAL_AUTOCHECKPOINT_PAGES (default: 1000)
 
 data paths
-- db: data/shim.db
-- site files: data/sites/
+  - db: data/shim.db
+  - site files: data/sites/
diff --git a/shim_app.py b/shim_app.py
index fae166f..2e9bcdd 100644
--- a/shim_app.py
+++ b/shim_app.py
@@ -675,6 +675,24 @@ def find_site_root(extracted_dir: Path) -> Path:
     return candidates[0].parent
 
 
+def env_int(name: str, default: int, minimum: int) -> int:
+    raw = os.getenv(name, str(default)).strip()
+    try:
+        value = int(raw)
+    except ValueError:
+        value = default
+    return max(value, minimum)
+
+
+def env_float(name: str, default: float, minimum: float) -> float:
+    raw = os.getenv(name, str(default)).strip()
+    try:
+        value = float(raw)
+    except ValueError:
+        value = default
+    return max(value, minimum)
+
+
 def create_app(base_dir: Optional[Path] = None) -> Flask:
     project_dir = Path(base_dir or Path(__file__).parent).resolve()
     app_name = os.getenv("SHIM_APP_NAME", "shim").strip() or "shim"
@@ -710,14 +728,35 @@ def create_app(base_dir: Optional[Path] = None) -> Flask:
     app.config["SHIM_APP_NAME"] = cfg.app_name
     app.config["SHIM_MOJICRYPT_BIN"] = str(cfg.mojicrypt_bin)
 
+    sqlite_timeout_seconds = env_float("SHIM_SQLITE_TIMEOUT_SECONDS", 30.0, 1.0)
+    sqlite_busy_timeout_ms = env_int("SHIM_SQLITE_BUSY_TIMEOUT_MS", 30000, 1000)
+    sqlite_cache_size_kib = env_int("SHIM_SQLITE_CACHE_SIZE_KIB", 32768, 4096)
+    sqlite_mmap_size_bytes = env_int(
+        "SHIM_SQLITE_MMAP_SIZE_BYTES", 256 * 1024 * 1024, 0
+    )
+    sqlite_wal_autocheckpoint_pages = env_int(
+        "SHIM_SQLITE_WAL_AUTOCHECKPOINT_PAGES", 1000, 100
+    )
+
     cookie_secure_mode = os.getenv("SHIM_COOKIE_SECURE", "auto").strip().lower()
     if cookie_secure_mode not in {"auto", "true", "false"}:
         cookie_secure_mode = "auto"
 
     def connect_db() -> sqlite3.Connection:
-        conn = sqlite3.connect(str(cfg.db_path))
+        conn = sqlite3.connect(str(cfg.db_path), timeout=sqlite_timeout_seconds)
         # row objects keep query call sites readable and less index-fragile.
         conn.row_factory = sqlite3.Row
+        # wal + busy timeout gives sqlite much better mixed read/write concurrency.
+        conn.execute("PRAGMA journal_mode = WAL")
+        conn.execute("PRAGMA synchronous = NORMAL")
+        conn.execute(f"PRAGMA busy_timeout = {sqlite_busy_timeout_ms}")
+        conn.execute("PRAGMA temp_store = MEMORY")
+        conn.execute(f"PRAGMA cache_size = {-sqlite_cache_size_kib}")
+        conn.execute(f"PRAGMA wal_autocheckpoint = {sqlite_wal_autocheckpoint_pages}")
+        try:
+            conn.execute(f"PRAGMA mmap_size = {sqlite_mmap_size_bytes}")
+        except sqlite3.DatabaseError:
+            pass
         # keep fk checks enabled even on sqlite defaults that disable them.
         conn.execute("PRAGMA foreign_keys = ON")
         try:
@@ -769,74 +808,6 @@ def create_app(base_dir: Optional[Path] = None) -> Flask:
             """
         )
 
-        # migration for existing installs - add and backfill uuid ids for users.
-        user_columns = {
-            row["name"]
-            for row in conn.execute("PRAGMA table_info(users)").fetchall()
-        }
-        if "user_uuid" not in user_columns:
-            conn.execute("ALTER TABLE users ADD COLUMN user_uuid TEXT")
-
-        missing_user_ids = conn.execute(
-            """
-            SELECT id FROM users
-            WHERE user_uuid IS NULL OR user_uuid = ''
-            """
-        ).fetchall()
-        for row in missing_user_ids:
-            conn.execute(
-                "UPDATE users SET user_uuid = ? WHERE id = ?",
-                (str(uuid.uuid4()), row["id"]),
-            )
-
-        conn.execute(
-            "CREATE UNIQUE INDEX IF NOT EXISTS idx_users_user_uuid ON users(user_uuid)"
-        )
-
-        # migration for existing installs - add and backfill csrf tokens for sessions.
-        session_columns = {
-            row["name"]
-            for row in conn.execute("PRAGMA table_info(sessions)").fetchall()
-        }
-        if "csrf_token" not in session_columns:
-            conn.execute("ALTER TABLE sessions ADD COLUMN csrf_token TEXT")
-
-        missing_session_csrf = conn.execute(
-            """
-            SELECT token FROM sessions
-            WHERE csrf_token IS NULL OR csrf_token = ''
-            """
-        ).fetchall()
-        for row in missing_session_csrf:
-            conn.execute(
-                "UPDATE sessions SET csrf_token = ? WHERE token = ?",
-                (secrets.token_urlsafe(32), row["token"]),
-            )
-
-        # migration for existing installs - add and backfill uuid ids for sites.
-        site_columns = {
-            row["name"]
-            for row in conn.execute("PRAGMA table_info(sites)").fetchall()
-        }
-        if "site_uuid" not in site_columns:
-            conn.execute("ALTER TABLE sites ADD COLUMN site_uuid TEXT")
-
-        missing_site_ids = conn.execute(
-            """
-            SELECT id FROM sites
-            WHERE site_uuid IS NULL OR site_uuid = ''
-            """
-        ).fetchall()
-        for row in missing_site_ids:
-            conn.execute(
-                "UPDATE sites SET site_uuid = ? WHERE id = ?",
-                (str(uuid.uuid4()), row["id"]),
-            )
-
-        conn.execute(
-            "CREATE UNIQUE INDEX IF NOT EXISTS idx_sites_site_uuid ON sites(site_uuid)"
-        )
-
     # auth provider is injected as a single backend to keep swap-over simple.
     auth_backend: AuthBackend = LocalMojicryptAuthBackend(
         connect_db=connect_db,