engineering-notes.md

Engineering notes

These are the five patterns worth understanding before you touch apps-script/Code.gs or the notebooks — each one exists because a naive version broke in production at some scale. Problem, root cause, fix.

1. Streaming UTF-8-safe reader

Problem. Reading a multi-megabyte result file (a DBFS file, in the reference adapter) into the sheet needs to happen in bounded chunks — Apps Script and most HTTP/storage APIs cap a single read around 1 MB. A naive loop that just appends each chunk's bytes to one growing byte array runs out of memory well before the file is fully read.

Root cause. Two compounding issues:

1. O(n²) concatenation. Repeatedly growing a flat byte array (bytes = bytes.concat(newBytes)) copies the entire accumulated array on every chunk. For a file made of k chunks, that's O(k²) total bytes copied — fine for a few chunks, and an OOM crash for a file tens of MB large (hundreds of chunks).
2. UTF-8 characters can straddle a chunk boundary. A multi-byte character (accents, non-Latin scripts, emoji) can have its lead byte at the very end of one 1 MB chunk and its continuation bytes at the start of the next. If you decode each chunk independently, that character gets corrupted or throws a decode error.

Fix. Two changes, both present in apps-script/Code.gs::_backendRead and mirrored in backend/adapters/databricks.py::DatabricksBackend.fetch_result:

• Decode each chunk to a string as soon as it's read, and concatenate strings, not byte arrays. V8 (and Python) string concatenation is a cheap rope/join operation, not a full copy — this alone turns the O(k²) byte copy into O(k) amortized string work.
• Before decoding a chunk, scan back up to 4 bytes from its end (_safe_utf8_cut in Python; the inline loop in _backendRead) to check whether the tail is an incomplete multi-byte lead-byte sequence. If it is, hold those trailing bytes back (carry) and prepend them to the next chunk before decoding, so every decode operates on whole characters only. The carry is flushed once after the loop in case the file ends mid-carry.

2. Batch write with found/not-found reorder

Problem. Writing Complete-mode results one row (one API call) at a time is the obvious implementation, and it's the one that fails first: Apps Script has a hard 6-minute execution limit, and a few thousand rows at one setValues call each blows through it well before the sheet is done.

Root cause. Range.setValues() and Range.setBackground() each have fixed per-call overhead (a round trip to the Sheets service) independent of how much data they carry. Calling them once per row multiplies that overhead by the row count instead of amortizing it.

Fix. _writeResultsToSheet in apps-script/Code.gs:

• Sorts all results by their target row, splits them into found and notFound (_not_found: true) groups, and concatenates found-then-not-found — so a mix of hits and misses always renders as a clean block of matches on top and a clean block of misses at the bottom, in original load order within each group.
• Writes the entire block in exactly one setValues() call and one setBackgrounds() call, instead of per-row calls.
• After writing, resets the background of any leftover rows below the written block (resid.setBackground(null) over the full remaining region). This is a full-region reset, not a per-cell diff — a narrower reset (e.g. only cells that previously had content) can leave stale red backgrounds behind if a later run produces fewer not-found rows than a previous one did.

3. Dynamic filter sidebar with field auto-selection

Problem. A Generate request needs both "which rows" (filters) and "which columns" (fields) specified. If those two are independent controls, a user who filters by tier but forgets to check the tier field gets a result that can't be inspected for the very thing they filtered on.

Root cause. Filters and fields are logically coupled (you almost always want to see the column you just filtered by) but are naturally two separate UI sections (a "Filters" block and a "Fields" block in Sidebar.html), so nothing forces that coupling by default.

Fix. Each filter control's onchange/oninput handler calls autoSelectField(fieldName) (see Sidebar.html), which finds the matching field checkbox by value and checks it — status and tier checkboxes auto-select the status/tier fields, and the created-at date inputs auto-select created_at. The mapping between filter and field is data, not hardcoded per-control logic: examples/field-catalog.example.json documents it as {"key", "widget", "auto_fields", "values"} per filter, so a generated or reconfigured sidebar can derive the same behavior from the catalog instead of duplicating it in markup.

4. Dynamic per-destination cap + auto-fallback to CSV

Problem. A Generate request can return anywhere from a handful of rows to millions. Every destination format has a physical limit — a Google Sheet has a ~10M-cell budget, .xlsx has a hard 1,048,576-row limit — and materializing a result larger than the destination's limit either fails outright or silently truncates.

Root cause. The row count isn't known until the query actually runs (it depends on the filters), so the cap check can't happen at request time on the front-end — it has to happen after the count is known, and it has to happen in a place that can still choose a different destination before doing any expensive materialization.

Fix. resolve_destination(requested, row_count) in backend/interface.py centralizes the decision: if the requested destination has a finite cap (DESTINATIONS = {"sheet": 10_000_000, "xlsx": 1_048_576, "csv": None}) and row_count exceeds it, the effective destination becomes csv (uncapped) and the response carries forced: true. backend/notebooks/generate_list.py inlines a related check (SHEET_LIMIT/EXCEL_LIMIT, kept in sync with DESTINATIONS's values by comment, but not with its semantics for sheet): resolve_destination treats DESTINATIONS["sheet"] = 10_000_000 as a flat row-count cap, while generate_list.py treats the same number as a cell budget and divides it by the requested column count (SHEET_LIMIT // num_cols) to get the actual row cap it enforces. For xlsx the two are equivalent (a row limit is a row limit); for sheet the notebook's check is the stricter, more accurate one. This runs before the branch that collects rows — so an over-cap sheet/xlsx request never reaches a .collect()/.toJSON() call at all; it goes straight to the CSV branch instead. This decision deliberately lives backend-side (in the notebook, mirroring the interface), not in Apps Script, because the backend is the only side that knows the row count and the only side positioned to avoid the expensive path before starting it.

5. Distributed export without collect

Problem. Once a result is routed to CSV (because it's too large for sheet/xlsx, or because the user asked for CSV directly), producing that CSV must not depend on being small enough to fit in a single process's memory — that would just move the size limit rather than remove it.

Root cause. The obvious CSV export — collect() all rows to the driver, then write them out — has exactly the same memory ceiling as any other collect()-based path. It defeats the purpose of choosing CSV as the "uncapped" destination.

Fix. The CSV branch in generate_list.py writes with df.coalesce(1).write.mode("overwrite").option("header", True).csv(path) — a distributed write executed by the query engine's workers, not a collect to the driver. coalesce(1) asks for a single output part-file for a clean single-file download/share experience, but the write itself never pulls the full result through the driver's memory. The notebook marks the cloud-storage-upload-and-share-link step as a documented stub (see the comment block above the CSV branch) rather than wiring a real bucket, because that step is inherently platform-specific (S3 vs. GCS vs. Azure Blob vs. a Drive share) — but the distributed-write half of the pattern, the part that actually avoids the memory ceiling, is real and runnable as shown.