Sync Architecture — NS → D1 (R497)

3 tiers · SYNC_TABLE_MAP (28 specs) · custom RESTlet · self-healing column drop · 162 D1 tables (live · R563 audit 2026-05-25) · SystemNote CDC + */2 hot tier

01 / Cron Tier 02 / SYNC_TABLE_MAP 03 / NS Gateway 04 / Self-Heal Loop 05 / Write to D1 06 / Observability Hot · */5m 7 specs · txns · 4 line tables · TAL Warm · */15m 8 specs · customers · BOM · inventory Cold · 1h 13 specs · GL · 8 new refs SyncTableSpec d1Table · columnMap · mode · join BOM · inv · TAL 584 BOM rows · inv balance · GL postings 8 ref tables price_levels · tax · terms · units · ship · pay · classes · territories customscript_gfs_platform_query R55 RESTlet · arbitrary SuiteQL TBA OAuth1 signed · no password JOIN-aware SQL Phase 22 · joinTable + joinClause paginate 1000/page · up to 100/tick Unknown identifier? NS rejects bad column Drop · retry Phase 18 · up to 6 heals env.DB.batch() atomic upsert OR delete_by_parent 144 D1 tables 28 NS-mirrored · 116 GFS-authoritative customer_addresses (planned) JOIN sync queued · table ready sync_log rows · duration · status · drops /api/sync/health drift + drop surfacing audit_netsuite_coverage feeds coverage flow yes retry no log drop Legend User UI Agent logic Policy Tool action Context / trace

Three sync tiers

  • • Hot every 5m — 7 specs incl. transaction_accounting_lines (R482)
  • • Warm every 15m — 8 specs incl. bom_revisions, bom_components, inventory_balance
  • • Cold hourly — 13 specs incl. 8 new ref tables from Phases 4+8

New mirrors this session

  • • bom_components — 584 live rows, unlocks assembly cost rollup
  • • inventory_balance — qty on hand · available · committed per location
  • • transaction_accounting_lines — GL posting per txn line
  • • price_levels · tax_codes · payment_terms · classifications · units_types · ship_methods · payment_methods · sales_territories

Self-healing loop (Phase 18 · R494)

  • • First-page Unknown identifier triggers heal
  • • Drops bad column, rebuilds SELECT, retries up to 6×
  • • Never drops id or watermark column
  • • Drop list surfaces in sync_log + /api/sync/health

Coverage status

  • • 144 D1 tables · 28 SYNC_TABLE_MAP specs
  • • customer_addresses / vendor_addresses tables exist; JOIN sync planned
  • • Phase 22 JOIN-aware sync wired but addressbook deferred (R488)
  • • audit_netsuite_coverage measures gap vs NS sysinfo

Workflow diagram • Built with Archify • Press T for theme and E for export