# Diff-harness canonical JSON — the contract (spec_version 1) Both extractors — `extract_legacy.py` (old side: Supabase / landed `legacy` schema) and the new-side emitter (grows per-milestone, 09-build-plan) — emit **this exact document shape**. `compare.py` diffs two documents field-by-field. Green across all history = migration correctness; green on the parallel-run month = behavioral parity (05-migration-plan step 0). This file IS the contract. A change here is a `spec_version` bump and a change to BOTH extractors in the same session. ## Serialization rules (canonical form) 1. UTF-8, `json.dumps(doc, ensure_ascii=False, indent=2, sort_keys=True)` + trailing newline. Object key order is therefore irrelevant at build time; **all determinism lives in keys and explicitly sorted lists**. 2. **Integers** (counts, seconds) are JSON numbers. **Every non-integer numeric is a string** — never a JSON float. Floats are a build error, not a formatting choice. 3. **Money is a per-currency map**: `{"CAD": "1234.50"}` — sums and scalar amounts alike (one rule, no special cases). Currencies never merge (07 #21). The legacy side tags everything `"CAD"` (committed default; the `_usd` column suffix was a misnomer). NULL scalar money → JSON `null`. Empty/all-NULL sums → `{"CAD": "0.00"}`. 4. **Quantization** (`ROUND_HALF_UP`, applied once, after any arithmetic): - cost / billout / amounts / income / manual_total / rates → 2dp - `source_rate` / `source_amount` sums → 4dp (raw import provenance keeps source precision) - `billout_adjustment_pct` → 4dp string (not money, no currency) 5. UUIDs are lowercase strings. Dates are `"YYYY-MM-DD"`. `meta. extracted_at` is UTC ISO-8601 and the only timestamp in the doc. 6. Fields are always present; absent values are `null` (never a missing key). ## The grouping key (pinned — survives the schema change) Per-project aggregates group by **`project_id` on both sides** (05): - **Assigned** rows (`project_id` NOT NULL): key = the project UUID. Never the text triple — text-cache drift must not produce phantom groups. The migration carries project ids unchanged; that stability is a contract obligation (as are time-entry ids, invoice ids, and cc-batch ids — all compared by id below). - **Unassigned** rows (`project_id` NULL): key = `"unassigned:" + json.dumps([operator, client, project], ensure_ascii=False, separators=(",", ":"))` — the raw text triple **verbatim** (case-sensitive, untrimmed, `null` for NULL). Old side reads `time_entries.operator/client/project`; new side reads `raw_operator/raw_client/raw_project` — identical keys by construction, since raw_* is the verbatim carried payload. ## Document shape ``` { "spec_version": 1, "months": { "": { "": } }, "invoices": { "": }, "cc": { "batches": { "": }, "highlight_keyword_count": int, "auto_rule_count": int }, "entities": { "operators": ..., "projects": ..., "team_members": ..., "unassigned_entry_count": int }, "meta": // informational — allowlisted, never gates } ``` ### months — per-month, per-project aggregates Month = `to_char(start_at, 'YYYY-MM')` on the **naive** source timestamp (preserved wall-clock; no TZ conversion exists to apply). Every time entry lands in exactly one (month, group) cell. ``` "months": { "2026-03": { "": { "project_name": "Site Rebuild", // display aid; assigned groups only "entry_count": 41, "source_seconds": 96120, // sum(coalesce(duration_seconds, 0)) "cost": {"CAD": "1291.00"}, // sum(billout_cost_usd) [2dp] "billout": {"CAD": "2402.50"}, // sum(billout_amount_usd) [2dp] "billout_adjusted": {"CAD": "2642.75"},// see formula below [2dp] "source_rate_sum": {"CAD": "150.0000"}, // sum(source_rate_usd) [4dp] "source_amount_sum": {"CAD": "812.3300"} // sum(source_amount_usd) [4dp] }, "unassigned:[\"PlusROI\",null,\"Mystery\"]": { "project_name": null, ... same fields ... } } } ``` - Sums use SQL `SUM` semantics (NULL addends skipped); an all-NULL sum is emitted as zero money (rule 3). - **billout_adjusted** (per ADR #040, read-time, per (month × project) window): `quantize2(billout_sum × (1 + pct/100) + amount)` with the project's `billout_adjustment_pct` / `billout_adjustment_amount`, NULL → 0. Rounded once, at the end, HALF_UP. Unassigned groups have no project ⇒ `billout_adjusted == billout`. Both sides compute this from THIS formula — internal consistency is what the harness checks; the app's own RPC rounding parity is M3's test suite, not the harness's job. ### invoices — one object per invoice, keyed by id ``` "": { "name": "PlusROI 2026-05", "status": "paid", // open | sent | paid (verbatim) "invoice_date": "2026-05-31", // or null "manual_total": {"CAD": "5000.00"}, // or null (null = use the sum) "entry_count": 353, "attached_entry_ids": ["0a1b...", ...], // ALL attached entry uuids, sorted lexicographically "computed_total": {"CAD": "4998.75"} // sum(billout_amount_usd) over attached entries [2dp] } ``` Old side: attached = `time_entries.invoice_id = `. New side: entries reached via `invoice_lines` of kind `time` (`invoice_line_id` lock chain). Entry ids carry through migration unchanged (05 step 2.6) — the id-set comparison is the point. ### cc — batches keyed by id, plus org-level rule counts ``` "cc": { "batches": { "": { "name": "May statement", "line_count": 74, "assigned_totals": { "": {"line_count": 12, "amount": {"CAD": "418.22"}}, "unassigned": {"line_count": 3, "amount": {"CAD": "61.90"}} } } }, "highlight_keyword_count": 9, "auto_rule_count": 14 } ``` - `assigned_totals` groups lines by `project_id` (plain `"unassigned"` key for NULL — cc lines carry no raw text triple); `amount` = `sum(amount_usd)` [2dp]. Projects with zero lines don't appear. - No billed-state exists old-side (bill-through was a stateless paste export); the new `invoice_line_id` on expense lines starts NULL everywhere, so billed totals enter this spec only when the new invoicing feature ships (spec_version bump then). - Keyword/rule counts are org-level inventory checks (`cc_highlight_keywords`, `cc_auto_rules` migrate near-verbatim). ### entities — inventories with entry counts ``` "entities": { "operators": { "PlusROI": { "entry_count": 3120, // via the FK chain (see note) "clients": { "Brentwood": {"entry_count": 411} } } }, "projects": { "": { "name": "Site Rebuild", "client": "Brentwood", "operator": "PlusROI", "entry_count": 411, "income": {"CAD": "12000.00"}, // or null "billout_adjustment_pct": "10.0000", // or null "billout_adjustment_amount": {"CAD": "250.00"} // or null } }, "team_members": { "info@adipramono.com": { // key = lower(email) "display_names": ["Adi Pramono"], // sorted unique "entry_count": 2044, "rates": [ // one row per team_member row {"cost": {"CAD": "14.00"}, "billout": {"CAD": "35.00"}, "active": true} ] } }, "unassigned_entry_count": 37 } ``` - **Entry counts flow through the FK chain only** (entry → project → client → operator). Text columns are never counted — drift is a data bug, not a grouping input. Unassigned entries are counted once, in `unassigned_entry_count` (so `sum(operator entry_counts) + unassigned_entry_count` = total entries — a built-in completeness check). - Operators/clients are keyed by **name verbatim** (their ids do not survive the party migration; names do). Clients nest under their operator (client names are operator-scoped, ADR #035). Operators or clients with zero entries still appear (inventory, not usage). Name uniqueness is per-org in the legacy schema, so a same-named operator in two orgs would collide here — extractors must fail loudly on a duplicate key rather than merge (none exist today; only `bowden-works` carries data). - **team_members** are keyed by `lower(email)` — the same dedupe key the party migration uses, so one person in two teams (rian) is ONE key with TWO `rates` rows. Teams themselves are dissolving and are deliberately absent. `rates` rows sort by (cost is null, cost, billout is null, billout, active). New side: one rates row per engagement, from its cost/billout `compensation_terms`; `active` = `ended_on IS NULL` (old side: `is_active`). - Entries whose email matched no member (worker NULL) appear in no team_member's count — same construction both sides (worker_party_id NULL). ### meta — informational only (allowlisted by default) ``` "meta": { "extracted_at": "2026-07-07T21:14:03Z", "source_mode": "supabase-public" | "legacy-schema", "schema": "public" | "legacy", "row_counts": { "": int | null } // null = table absent in source } ``` Never gates a comparison: physical row counts differ across schemas by design (e.g. `teams` has no new-side table). The seeded allowlist carries `meta/**` with that reason. Old-side baseline row counts are still recorded here per the M0 gate ("baseline capture"). ## Comparison + allowlist rules - `compare.py old.json new.json` flattens both docs to `path → value` (path segments joined with `/`), reports every difference (changed / only-in-old / only-in-new), and exits 0 only if every diff matches an allowlist entry. - Lists of scalars (entry ids, display_names) compare as one value with an added/removed readout; lists of objects (rates) compare element-wise by index. - `allowlist.json` entries are `{"path": , "reason": }`. **As narrow as possible** — an entry that matched nothing is reported (stale allowlist). Documented accepted diffs from 05 that never reach this doc (dropped `rate_proportion` / `converted_*` columns) need no entries; the one conditional case — judgment #22 stragglers attaching to a "Legacy transfers" invoice — gets an entry pinned to that invoice's exact id + the exact entry-id diff, only if it ever fires. - Path caveat: `/` inside a name/email would ambiguate a displayed path. Harmless for comparison (both sides build identical paths); keep allowlist patterns anchored on uuid/section segments.