Repository navigation
Description
Root cause of the oversized rows in #1188
Two columns store an unbounded per-file list inline, so a row's size scales with how many files a dataset has rather than being bounded by anything. In both cases the detail is a duplicate of a richer artifact that already exists outside D1, which is what makes this straightforward to fix.
1. audit_log.details on import_verify_forced
The verification writes the entire missingKeys array into the audit row's details JSON. The largest holds 12,397 git-annex keys in a single row, 1.15 MB. There are 244 such rows averaging 46 KB, totalling 11.25 MB of the 12.72 MB audit_log - one action is roughly 88 percent of the entire audit log. The same 12,397-key payload repeats byte-for-byte across several rows, so a repeatedly-forced verification pays the full cost each time.
This duplicates .nemar/availability-report.json (backend/src/services/availability-report.ts, epic #999 / issue #1000), which already records "how much of a dataset's declared version-manifest content is actually present in S3, and exactly which files are missing (+ why)", committed to the dataset repo's main branch. That artifact is strictly better than the audit blob: it is keyed by manifest path rather than annex key (deliberately, because two paths can share one key and keying off the key collapses two genuinely missing paths into one), it carries a reason per entry (zero_byte / absent), and it is versioned in git rather than sitting in the operational database.
2. datasets.zarr_data_failures
A JSON array of per-file Zarr conversion failures, written by the zarr-ready callback (backend/src/routes/callbacks/zarr-ready.ts). The largest holds 877 entries at 178 KB in one row; 199 rows carry it, totalling 2.99 MB, about 29 percent of all datasets data. This is what pushes the four oversized datasets rows past D1's statement limit - their ordinary text is small by comparison (readme at most 8 KB, enrichment_json 2-4 KB, description under 600 bytes).
This duplicates the published Zarr index.json, which already carries its own failures list (scripts/zarr/generate_zarr.py, and purge_non_raw_stores.py reads and rewrites it). The index is the source of truth the viewer consumes; D1 holds a copy.
Direction
An audit row, and a catalog column, should carry flags and counts - how many were checked, how many present, how many missing, whether it completed - plus a pointer to where the detail lives. The detail itself belongs in the artifact that already owns it: the availability report for missing content, the Zarr index for conversion failures.
That is a stronger fix than truncating the lists, because a truncated list is still the wrong thing in the wrong place: it makes the row smaller without making the data reachable, and it silently discards entries 51 through 12,397. Replacing the payload with a count plus a pointer keeps every operational question answerable ("877 files failed, see the index") while making row size independent of dataset size.
Concretely this would:
- fix Production D1 backup cannot be restored: 15 statements exceed D1's SQLITE_TOOBIG limit #1188's restore failure at the source rather than working around it in the backup format,
- shrink the backup by roughly 14 MB of its 25 MB,
- and remove ~3 MB from
datasets, which every catalog query and every backup currently carries.
Worth deciding as part of this whether audit_log should keep unbounded details at all, or whether that column needs a size guard so the next payload of this shape cannot recreate the problem.
Activity
[-]Cap unbounded per-file lists in audit_log.details and datasets.zarr_data_failures[/-][+]Audit and catalog rows should carry counts and pointers, not inline per-file lists[/+]neuromechanist commentedon Sep 2, 2026
Landed in PR #1190, squash-merged to
dev. Closing by hand because GitHub only honours closing keywords on merges into the default branch.