# feat: store MAAD results as compact f32 rows · PR \#119 · flambo/atlantis

[View on GitCafe](https://git.cafe/flambo/atlantis/pull/119)

Repository: [flambo/atlantis](https://git.cafe/flambo/atlantis)

Visibility: public

State: merged

Source repository: flambo/atlantis

Source branch: maad/07-compact-storage

Target branch: maad/06-weighted-pipeline

Head commit: 08c62c11f76e3a1c9ea8033f108b7209bbe786f5

[Discussion and reviews](https://git.cafe/flambo/atlantis/pull/119)

[Commits](https://git.cafe/flambo/atlantis/pull/119/commits?format=markdown)

[Files changed](https://git.cafe/flambo/atlantis/pull/119/diffs?format=markdown)

## Description

---

> [!NOTE]
> 🤖 **Claude Opus 5.5 on behalf of Oliver**

## ELI5

MAAD results used to be saved as big JSON text, 7 rows per time bucket. Now each measure gets one row of packed 32-bit number arrays, so a day of data shrinks from ~660 MB to ~70 MB. The charts show the same numbers.

## Why

After the rest of this stack, MAAD JSON took up 92% of each day's database. A 390-day reprocess would be ~257 GB, and parsing that JSON was the main cost of the chart routes. A design study on a real day compared compact JSON, typed columns, f64/f32/f16 blobs and several index layouts. f32 blobs in a rowid table won: they were 12.6× smaller, and their rounding error stays far below MAAD's own sd. Decimal rounding and f16 were rejected because 22% of sd values are below 1e-3.

## What changes

- `address_maad_stats` replaces `address_structure_stats`. It has one row per scope, side and measure, with:
  - REAL `d0`/`d1`/`d2`
  - INTEGER counts and prefix bounds
  - `tau`, `tau_sd` and `spectrum` as little-endian f32 BLOBs. `spectrum` holds interleaved (α, f) pairs of variable length: zero-length means computed but empty, and NULL means not computed (packets/bytes).
  - A unique timeseries key plus a `(granularity, bucket_start)` index.
- `maad_q_grid` stores the q grid once per IP version.
- The only rounding is `value as f32`. The product schema moves to 7 and the MAAD contract to 6, which needs a fresh product DB.
- **Schema CHECKs** (Rust, local SQLite, Drizzle schema and `0006`, all identical):
  - `tau` must be a non-empty BLOB of whole f32 values.
  - `tau_sd` must have the same type and length as `tau`.
  - `spectrum` must be a BLOB of whole (α, f) pairs.
  - SQLite CHECKs can't read other tables, so `q_count` is enforced by `verify` and by the API instead.
- `verify` checks blob lengths against the grid and spectrum presence per measure. `extract` copies `maad_q_grid`.
- `compare` checks d-values and curves element by element. It also requires identical `maad_q_grid` rows for every IP version whose curves both databases store in the window. A grid that is missing or different on either side makes the result incompatible, and the report lists those versions in `maad_q_grid.mismatched_ip_versions`.
- The web routes decode blobs with `decodeF32`, which accepts `Uint8Array`, `ArrayBuffer` or D1's `number[]`. Response shapes are unchanged. A non-NULL curve must have a grid, and `tau`/`tau_sd` must each hold exactly `q_count` values. A misaligned blob or an odd spectrum is also rejected. Any of these returns the route's 500 database error instead of a partial or empty chart.

## Review path

1. Rebuild a day with `netflow-db pipeline`, then run `verify` with all `--require-*` flags. Expected: OK.
2. On the dashboard, the spectrum card and the file-detail structure/spectrum charts (with error bars) look the same for each measure, IP version and direction. `measure=packets` on a spectrum route still returns 400.
3. Run `compare` on the rebuilt DB against a copy of itself, then again after `UPDATE maad_q_grid SET q_step = 0.25` on the copy. Expected: the first is compatible, and the second exits nonzero with `mismatched_ip_versions: [4, …]`.
4. Optional: in a local copy, `UPDATE maad_q_grid SET q_count = q_count + 1` and open a structure chart. Expected: the route returns 500 ("Database query failed" / "Failed to get structure statistics") and no chart is drawn.

## Deployment and decisions

- ⚠️ Production D1 will be cut over to a **fresh database** (drop → migrate → reload the reprocessed product) in the infra stack. Migrations `0003`/`0005` are therefore not restructured: on that path their intermediate table copies run against an empty DB. `0006` has not been applied anywhere, so its CHECKs were edited in place rather than added in a new migration. Applying `0006` to a populated legacy D1 would drop the old MAAD table without converting it.
- A DB built without MAAD has no q grid and no curves, and the routes keep returning empty data. The dashboard view PR will show an explicit "MAAD not computed" state for this case.
- Legitimate NULL results, such as too few addresses or a spectrum for packets/bytes, still come back as empty points.
- `compare` between different builds may need `--maad-absolute-tolerance ≈ 1e-6`, because values can round to neighbouring f32s.

## Verification

- Automated:
  - Passed: `bun run format`, `bun run lint`, `bun run typecheck`, `cargo fmt --check`, clippy `-D warnings`, `test:db` and `test:web`.
  - New tests:
    - Decoder: misaligned blobs, missing grid, `q_count` mismatch, missing or short `tau_sd`, odd spectrum.
    - Routes: 500 on a bad grid, 200 for a DB without MAAD.
    - Schema CHECKs, for both the migrated and the local schema: accept valid rows and reject misaligned, empty, mismatched and text blobs.
    - Rust storage CHECK test.
    - `compare` on a changed q_min, a changed q_step, a deleted grid row and a dropped grid table.
  - `test:e2e` passes on the restacked branch (9 tests), using the shared local schema fixture from #108.
- Earlier manual runs on a real production day:
  - Size and time: 69.4 MB after VACUUM vs 661 MB, with the same 24:26 wall time as layer 6. `verify` OK.
  - Every stored τ/sd/spectrum value equals the previous JSON value cast to f32 (max difference 5.96e-8). Dimensions, counts and all non-MAAD tables are identical.
  - 720 requests across all 5 MAAD routes, granularities, IP versions, measures and directions matched the previous build within f32 rounding. structure-stats is about 2× faster.
- Remaining manual checks:
  - Re-run steps 1–3 on a real day with the new CHECKs to confirm the pipeline writer never trips them.
  - Confirm the fresh-D1 cutover path (drop → migrate → load) in the infra stack.

---
Made by Claude Opus 5.5 (with Opus 5.5 subagents) via Claude Code.