ODS Runtime — TWMD Internal

Sync

Standard incremental synchronization. Retrieves new and changed records since the last successful sync. Safe to run at any time — skips records that have not changed. This is the correct action for routine operations.

Wherefour — Suppliers

Incremental sync of vendors into the suppliers canonical table.

Wherefour — Inventory Items

Incremental sync of inventory items into the inventory_items canonical table.

Wherefour — BOMs

Incremental sync of formulas into the manufacturing_boms canonical table.

Wherefour — Purchase Orders

Incremental sync of purchase orders into the purchase_orders canonical table.

QuickBooks — Customers

Incremental sync of customers into the customers canonical table.

Customers Loaded — Last Sync — Last Result — Watermark —

QuickBooks — Accounts Receivable

Full snapshot of open AR transactions into customer_ar_open_items. Replaces table on each run (truncate-and-reload).

Rows Imported — Snapshot Date — Last Run — Last Result —

Wherefour — Customer Orders

Incremental sync of sales orders into the customer_orders canonical table (with lines and custom lines).

QuickBooks — Invoice History

Incremental sync of QB invoices (paid + unpaid) into customer_invoices. Upserts by invoice ID — balance updates in-place as payments are applied.

Invoices Loaded — Latest Invoice — Watermark —

Recovery — Force Re-Sync

Administrative recovery tool. Force Re-Sync reprocesses all records regardless of previous synchronization status. Use only when:

ODS tables have been manually cleared or rebuilt
Synchronization state appears incorrect or stale
Records are missing that should be present
Recovering from a synchronization failure
Rebuilding connector state after maintenance

Do not use Force Re-Sync for routine daily operation. Normal Sync (above) is sufficient for ongoing maintenance.

Wherefour — Suppliers

Reprocess all supplier records regardless of sync state.

Wherefour — Inventory Items

Reprocess all inventory item records regardless of sync state.

Wherefour — BOMs

Reprocess all BOM records regardless of sync state.

Wherefour — Purchase Orders

Reprocess all purchase order records regardless of sync state.

QuickBooks — Customers

Reprocess all customer records regardless of sync state.

QuickBooks — Invoice History

Reloads all invoices from the last 13 months. Ignores the watermark and re-fetches every record. Use after a database rebuild or when invoice counts look wrong.

Wherefour — Customer Orders

Reprocess all sales order records regardless of sync state.

ODS Table Reference

Every table in the Operational Data Store — what it holds, what populates it, whether it is safe to clear, and how it is rebuilt. Use this reference before performing any manual database maintenance.

QuickBooks Connector

Table	Purpose	Populated By	Safe to Clear	Rebuilt By	Notes
customers	Canonical customer identity — name, type, contact, commercial terms, cross-system IDs	QuickBooks Customer Sync	With caution	Force Re-Sync Customers	Clearing requires clearing `source_record_map` first. FK target for future order entities.
customer_addresses	Billing and shipping addresses for each customer (one row per address type)	QuickBooks Customer Sync	Yes	Any Customer Sync or Force Re-Sync	Automatically re-created on next sync. Safe to clear for maintenance.
customer_ar_open_items	Canonical AR fact table. All open receivable transactions from CustomerBalanceDetail report. Truncate-and-reload on each sync.	QuickBooks Accounts Receivable Sync	Yes	Sync Accounts Receivable	Full snapshot per sync run. Aging days and bucket calculated at import. Safe to clear — next sync restores current state.
vw_customer_ar_balances	Current balance per customer — SUM(open_amount) from customer_ar_open_items	Derived (no sync)	View	—	Semantic view. Always reflects current customer_ar_open_items snapshot.
vw_customer_ar_aging	Balance bucketed by aging: Current / 1–30 / 31–60 / 61–90 / 90+	Derived (no sync)	View	—	Semantic view. Aging buckets computed from aging_days on customer_ar_open_items.
vw_customer_collections	Collections operational queue — balance, oldest due date, days past due, open documents per customer	Derived (no sync)	View	—	Semantic view. Collection status and assigned collector are future fields (currently NULL).

Wherefour Connector

Table	Purpose	Populated By	Safe to Clear	Rebuilt By	Notes
suppliers	Canonical supplier / vendor identity — name, contact, payment terms	Wherefour Supplier Sync	No	Force Re-Sync Suppliers	FK target for `purchase_orders.supplier_id` and `supplier_materials.supplier_id`. Clearing breaks PO and SM relationships.
inventory_items	Wherefour inventory catalog — raw material and finished good items	Wherefour Item Sync	With caution	Force Re-Sync Items	Source data for `supplier_materials` seed linkage. Clearing may require re-running supplier identity resolution.
manufacturing_boms	Formula / BOM definitions from Wherefour — active and historical revisions	Wherefour BOM Sync	With caution	Force Re-Sync BOMs	ODS-003: active_flag governs current revision. Clearing loses active-state governance history.
manufacturing_bom_components	Line items for each BOM — ingredient / packaging components	Wherefour BOM Sync	Yes	Force Re-Sync BOMs	Fully rebuilt on every BOM sync. Safe to clear.
purchase_orders	Procurement transactions — PO number, date, status, supplier, totals	Wherefour PO Sync	With caution	Force Re-Sync POs	Requires `suppliers` to be populated first. Run Supplier Sync before PO Force Re-Sync.
purchase_order_lines	Line items for each PO — item, quantity, unit cost	Wherefour PO Sync	Yes	Force Re-Sync POs	Fully rebuilt on every PO sync. Safe to clear.
customer_orders	Sales orders from Wherefour — order number, dates, status, amounts, delivery method	Wherefour Customer Orders Sync	With caution	Force Re-Sync Customer Orders	FK parent for `customer_order_lines` and `customer_order_custom_lines`. Linked to `customers` via `wherefour_customer_id` — clearing requires clearing lines first.
customer_order_lines	Standard product line items for each order — SKU, product name, category, quantity, unit cost	Wherefour Customer Orders Sync	Yes	Force Re-Sync Customer Orders	Fully rebuilt on every order sync (delete/re-insert per order). Safe to clear.
customer_order_custom_lines	Custom / non-inventory line items for each order — name, description, quantity, unit price	Wherefour Customer Orders Sync	Yes	Force Re-Sync Customer Orders	Fully rebuilt on every order sync (delete/re-insert per order). Safe to clear.
v_customer_order_master	Order header with customer name (LEFT JOIN), line count, and custom line count	Derived (no sync)	View	—	Semantic view. Always reflects current customer_orders state.
v_customer_order_line	Order line detail with parent order context (order number, customer, dates)	Derived (no sync)	View	—	Semantic view. Always reflects current customer_order_lines state.

Sync Infrastructure

Table	Purpose	Populated By	Safe to Clear	Rebuilt By	Notes
source_record_map	Cross-system identity — maps source system IDs to canonical ODS record IDs	All sync operations	With caution	Force Re-Sync for each connector	Clearing causes the next sync to treat every record as new (full insert). Required step before rebuilding a canonical table from scratch.
sync_state	Watermark timestamps — records the last successful sync point per connector	All sync operations (on completion)	Yes	Next successful sync run	Clearing causes the next sync to perform a full scan (same as Force Re-Sync). Safe to clear if a full rescan is acceptable.
import_jobs	Audit log — one row per watermark-based entity sync run	All Wherefour and QB entity syncs	Yes	Next sync run	Historical record only. Clearing removes sync history but has no operational impact.
integration_report_runs	Audit log — one row per QB Report API import run (AR Balance, AR Open Items)	AR Balance Sync, AR Open Items Sync	Yes	Next report sync run	Distinct from import_jobs: report imports are point-in-time snapshots, not watermark-based. Clearing removes run history only.

Administrator Guidance

✓ Safe to clear anytime: import_jobs, sync_state, customer_addresses, manufacturing_bom_components, purchase_order_lines

⚠ Clear with caution: customers, inventory_items, manufacturing_boms, purchase_orders, source_record_map — clear only as part of a deliberate rebuild sequence

✗ Do not clear: suppliers without also clearing and rebuilding purchase_orders and supplier_materials

Rebuild sequence when clearing a connector: Clear source_record_map → Clear canonical table → Run Force Re-Sync

Supplier dependency: Always run Supplier Sync or Force Re-Sync Suppliers before Force Re-Sync POs — PO sync resolves supplier_id from the suppliers table at ingest time

Integrations Hub

Operational Control Center

Import Job Monitor

Sync State Watermarks

Sync

Wherefour — Suppliers

Wherefour — Inventory Items

Wherefour — BOMs

Wherefour — Purchase Orders

QuickBooks — Customers

QuickBooks — Accounts Receivable

Wherefour — Customer Orders

QuickBooks — Invoice History

Recovery — Force Re-Sync

Wherefour — Suppliers

Wherefour — Inventory Items

Wherefour — BOMs

Wherefour — Purchase Orders

QuickBooks — Customers

QuickBooks — Invoice History

Wherefour — Customer Orders

ODS Table Reference

QuickBooks Connector

Wherefour Connector

Sync Infrastructure

Administrator Guidance

Canonical Table Health

BOM CSV Import

Current BOM Status