Build now
One SQLite database file on the Australian VPS, managed by migrations. One ingestion record per API refresh or manual import. A small normalized model for products, vendors, policies, inventory, demand, replenishment, and procurement.
Decision record | 15 August 2026
Small, durable data layer for Tim’s ordering workflow.
Use SQLite on the existing Australian VPS for the Derwent pilot. Keep MYOB and the data center authoritative for accounting facts. Let Reorderly own the normalized planning inputs, reorder proposals, approvals, and order history.
What we decided
Enough structure to be reliable
Designed for the pilot, without pretending to be the whole Reorderly platform.
Do not build now
No PostgreSQL cluster, no D1 deployment, no full MYOB mirror, no event-sourcing framework, no CRM, no BOM engine, no connector marketplace, and no generic multi-tenant provisioning UI.
!
Important boundary: a database is now required because the app must remember refreshes, edited quantities, approvals, open orders, and receipts. The current in-memory snapshot is only a temporary read cache.
System shape
Five steps from source to decision
1FetchRead products, stock, and sales from the data center. Import desktop files when required.
2RecordStore the run status, source timestamp, row counts, and raw-file reference.
3NormalizeMap source fields into stable Reorderly item, inventory, demand, and policy inputs.
4ProposeCalculate BUY, NO BUY, or REVIEW with frozen inputs and a calculation trace.
5ApproveTim edits quantities, approves an order draft, exports it, and records receipts later.
MYOB and desktop exports → data-center adapter → SQLite → reorder engine → browser workbench → approved export
Pilot data model
Tables we actually need
All organization-owned tables carry organization_id, even though the first deployment has one organization.
| Table | Purpose | Key fields |
|---|---|---|
organizations | Client boundary for the pilot and future Reorderly customers. | id, name, slug, timezone |
ingestion_runs | Every API refresh or manual import, including failed attempts. | id, organization_id, source_type, status, source_as_of, error, row_counts_json |
source_artifacts | Metadata for raw CSV/JSON exports stored outside the database. | ingestion_run_id, dataset, path, hash, record_count |
products | Stable normalized product projection. Not a replacement for an ERP. | source_product_id, sku, name, active |
vendors | Vendor records and default delivery assumptions. | name, default_lead_weeks, contact_email |
product_vendor_links | Vendor-specific SKU, order minimum, pack multiple, and lead-time override. | product_id, vendor_id, vendor_sku, minimum_order_qty, order_multiple |
planning_policies | Organization-level replenishment defaults and policy versions. | demand_window_weeks, safety_stock_weeks, default_lead_weeks, version |
inventory_snapshots | Time-stamped stock and incoming facts used by a run. | ingestion_run_id, product_id, on_hand, committed, incoming |
demand_snapshots | Derived demand window used by a run. | ingestion_run_id, product_id, window_weeks, units_sold, average_weekly_demand |
replenishment_runs | One complete calculation and its policy/source versions. | ingestion_run_id, policy_version, generated_at, source_status |
replenishment_lines | Frozen facts and decision for each product. | replenishment_run_id, product_id, coverage, target, suggested_qty, decision, trace_json, final_qty |
procurement_orders | Durable draft and export state for an approved procurement order. | vendor_id, replenishment_run_id, status, export_path |
procurement_order_lines | Approved quantities and receipt progress. | procurement_order_id, product_id, quantity, received_qty |
procurement_order_events | Small append-only history of approval, export, receipt, and cancellation. | procurement_order_id, event_type, payload_json, created_at |
Rules that protect trust
Data decisions are explicit
Unknown is not zero
Missing stock, demand, lead time, or order constraints produce REVIEW. We never turn an absent value into a safe-looking zero.
Inputs are frozen
A reorder line stores the actual facts and policy used at calculation time. Later MYOB changes cannot rewrite historical reasoning.
Incoming is reconciled
MYOB on-order, supplier pipeline, and manual tracker values are kept separate before a single overlap-safe incoming quantity is calculated.
Raw data stays traceable
SQLite stores file path, hash, run, and counts. Raw exports live in protected server storage rather than bloating the database.
Secrets stay outside
API keys and Cloudflare credentials stay in environment configuration or a secret manager. They never enter SQLite or the browser.
SQLite is operational storage
It stores what Reorderly needs to remember. MYOB remains the source of truth for accounting facts and the data center remains the integration boundary.
coverage = on_hand - committed + incoming target = average_weekly_demand × (lead_weeks + safety_stock_weeks) gap = max(ceil(target - coverage), 0) suggested_qty = max(gap, minimum_order_qty) decision = BUY | NO_BUY | REVIEW
Scope boundary
What we defer until evidence demands it
Possible next stages
D1 per organization
Selva policy pack and BOM relationships
Customer and sales analytics
Cloudflare or VPS deployment automation
Additional certified connectors
Not part of this migration
PostgreSQL cluster
Full ERP replacement
Generic integration marketplace
Autonomous AI-generated production connectors
Public AppSumo onboarding
Decision history
What was already decided
SQLite instead of PostgreSQL for Tim
Tim has one deployment, modest write concurrency, and an existing Australian VPS. SQLite minimizes operational work while keeping the model portable. PostgreSQL becomes a later decision if shared multi-client workloads or concurrent writes justify it.
MYOB is not copied wholesale into Reorderly
Reorderly keeps a normalized product projection and time-stamped source facts needed for planning. It does not become a competing accounting system.
D1 is a future option, not the pilot dependency
The schema stays SQLite-compatible so a future Cloudflare Workers and D1 deployment remains possible. We do not move the pilot deployment to D1 before data-location and operational requirements are proven.
Streamlit remains the fallback
The browser app is the new product-shaped runtime. Streamlit remains available while Tim validates the weekly ordering loop.