T
Sign in

PLATFORM.md — the scalable platform architecture

All docs GitHub source

PLATFORM.md — the scalable platform architecture

The system is not "an app for one mill" — it is the company platform: multi manufacturing setups, accounting/CRM integration, the base for the company's IoT products, real-time (runtime) dashboards, and analytics that drive decisions. This document defines the shape everything else builds on.

0. Architecture goals (the invariants)

  1. Multi setup — one platform instance serves every manufacturing site and every company (trading / job-work / future lines), with strict data isolation between them.
  2. Runtime picture — the system shows the current state, live. The floor entry / IoT event appears on the right screen immediately. Reports are not "after data entry" artefacts; they are frozen snapshots of the same live picture. "Runtime, not periodic" is the core promise.
  3. Data at point of generation — entry happens on the floor, in the moment, by the person/device doing the work (mobile-first + IoT).
  4. One event backbone — every fact (human entry, device telemetry, weaver-ERP import, Tally/CRM exchange) enters as a typed, idempotent event through one ingestion boundary and lands in one event log. All analytics and dashboards read from this.
  5. Integrations are adapters — Tally, CRMs, weaver ERPs, IoT products: each is an adapter speaking the platform's contracts (file bridge or API), never bespoke point-to-point code in the core.
  6. Smart decisions live on top — decision services (procurement suggestion, allocation risk, delay alerts, quality drift, machine health) run on aggregates; ML can be added later on exported event data without touching the platform.

1. High-level shape

SOURCES
  mobile floor apps (staff, point of creation)
  IoT devices & gateways (loom counters, scales, sensors) — the company's products
  weaver ERPs (outsourced production)
  Tally, CRM, other external systems (adapters)

        │  everything speaks the same event contract (JSON, typed, idempotent)
        ▼
INGESTION BOUNDARY  ──  POST /api/events  (validate → dedupe → persist → fanout)
        │
        ▼
EVENT LOG  (append-only, partitioned by time, tenant-scoped)          [PostgreSQL]
        │                    │
        ▼                    ▼
DOMAIN SERVICES        AGGREGATES / READ MODELS
(stock, orders, beams,  (continuous rollups: meters, efficiencies, stock
 allocations, costing)   cover, checking stats)   →  fast live reads
        │                    │
        └────────┬───────────┘
                 ▼
         REAL-TIME HUB (SSE/WebSockets)
                 │  pushes deltas to subscribed screens
                 ▼
MANAGEMENT DASHBOARDS (top & middle management) + floor screens, all live
                 │
                 ▼
DECISION SERVICES + ANALYTICS (rules now, ML later on exported event data)

2. Tenancy: company × setup (multi manufacturing setups)

Two dimensions on every row:

Dimension Meaning Example
tenant_id (company) legal/profit-centre entity — hard isolation boundary "Trading Co.", "Job-Work Co."
setup_id (site) physical manufacturing setup "Bhiwandi Unit 1", "Unit 2"
  • tenant_id is the isolation boundary: queries are always scoped; PostgreSQL row-level security enforces it at the database layer, so a bug in one screen can never leak another company's data.
  • setup_id scopes operational views (godown, units, machines) and rolls UP for company-level analytics (a top-management dashboard aggregates across setups within its tenant).
  • Auth: Better Auth sessions carry tenant/setup/role; one middleware injects the context; roles (owner, planner, godown, warper, sizer, weaver, checker, dispatcher, management) gate screens and actions.
  • Adding a setup later = inserting a row + config, not a new deployment.

3. Stores & dialect — scale-appropriate staging (start small, shift by triggers)

The guiding rule: use the smallest structure that fully serves the current reality — build, stabilise, and only then shift to the bigger structure when a concrete trigger fires. A big-scale structure bought early costs in ops, complexity, and slower iteration; a shift made on time with seams in place costs days, not months.

Stage 1 — now: bun:sqlite (single file, single server)

bun:sqlite is the right store for the current reality: one server, one live setup, a handful of users, floor entry bursts (well within SQLite WAL's capability: tens of thousands of events/day are trivial), zero ops (no service, no apt install, backup = copy the file), and it is already proven running. All Phase 0–1 work happens here.

What must be true in the code even at this stage (the seams that keep the later shift cheap):

  • All tables carry tenant_id (and setup_id where relevant) — columns now, enforcement later; no table is written tenant-blind.
  • All queries go through Drizzle (already true) — the dialect is a config, not a rewrite. No hand-written SQL in feature code (raw SQL is allowed only in migration files).
  • Every fact enters via the event backbone (§4) — the event log schema is identical in both stores, so moving the log is a copy, not a redesign.
  • Time columns stored epoch-based, ids stable, no sqlite-special features in the domain layer (JSON columns stay JSON-compatible).

Stage 2 — trigger-gated: PostgreSQL (on the Pi)

Shift the platform store to Postgres when one or more of these fires:

  1. Multi-setup/multi-company goes live — the second tenant or second site becomes real; RLS isolation is worth it (columns are already there; the dialect flips, data migrates, RLS policies activate).
  2. Concurrent write load exceeds SQLite's comfort (measured, not guessed: sustained > ~5–10k events/minute, or write contention on dashboards while floor entry is active).
  3. Integration adapters need real transactions (Tally/CRM/ERP bridges that must commit atomically with platform state) and audit-grade consistency.
  4. Event volume & retention demand partitioning (monthly event partitions) and point-in-time backups.

Until a trigger fires, stay on bun:sqlite — the triggers are re-checked at each roadmap milestone, and the decision is a standing agenda item, not a one-time call. (Postgres remains the declared Stage-2 store precisely so the seams are built toward it; decision documented, nothing built ahead of need.)

Stage 3 — later, measured: read replicas / dedicated analytics store

Only when dashboards or analytics are blocked by measurement: read replica for dashboard reads; analytics read models moved to a dedicated store; per-tenant sharding if one tenant outgrows the box. Each lever sits behind the same API/event boundary (PLATFORM.md §8).

Edge mirrors stay bun:sqlite at every stage — floor/site gateways with poor connectivity buffer locally (mobile app queues; gateway mirrors) and sync into the event log idempotently via client event ids. The edge is always the small store; the centre stages up.

4. The event backbone (the runtime core)

Every fact is an event:

{ id, clientEventId?, tenantId, setupId,
  type,            // e.g. yarn-in, beam-issued, weave-production, check-grade, lot-allocated, iot-counter, tally-voucher
  payload,         // versioned JSON per type
  tsDevice,        // when it happened (device/floor clock)
  tsReceived,      // when the platform got it
  source,          // mobile:<user> | iot:<device> | import:<adapter> | manual-backfill
  version }
  • One ingestion boundary: POST /api/events — mobile apps, IoT gateways, adapter bridges all use it. Validate → idempotent dedupe (by clientEventId/external ids) → append → post-commit fanout.
  • Append-only, partitioned by month (events are the analytics ore; they are never edited, only corrected by a counter-event).
  • Domain state (stock, orders, beams) is derived from events through the domain services — corrections are new events, so history stays truthful.
  • Heavy analytics never touches the hot write path; it reads aggregates.

5. Runtime picture & the real-time hub

  • After an event commits, the real-time hub (Bun WebSocket / SSE) pushes the delta to subscribed screens: production meters tick up on the management board the moment the weaver's phone saves them; a stock move updates godown and procurement views instantly; checking grades flow into dispatch readiness live.
  • Dashboards = read models (continuous aggregates) + live deltas — no screen waits for a nightly batch. Reports become downloads of the live picture (same data, stamped with as-of time), and that is documented as the anti-pattern to avoid: periodic reports are a view, not a source.
  • The management dashboards (top: company P&L position, order book, capacity, cash-in/out view; middle: per-setup floors — machines, beams, stock, checking, dispatch queues) are just another subscriber of the hub
    • aggregates. Floor screens and management screens show the same truth.

6. Analytics & smart decisions

  • Metrics layer: continuous aggregates over the event log (meters/setup/day, machine efficiency, stock cover days per yarn, conversion rates, checking grade drift, PO/SO delay) materialized as read models; scheduled reconcile guards against drift.
  • Decision services (rules first — ML later on exported event data): procurement suggestions (demand − free stock − in-transit), allocation conflict warnings, delay alerts on PO/SO, quality drift alerts from checking, machine-health alerts from IoT telemetry.
  • Export hooks: event/aggregate exports (their IoT products, ML notebooks, external BI) use the same adapter contracts — analytics access never embeds into the core.

7. Integration adapters (Tally, CRM, weaver ERPs, IoT)

  • Adapter registry: each external system = adapter + versioned contract (file bridge per BRIDGE.md, or API) + direction + cadence + status.
  • Tally: file/XML bridge (Tally import/remote) — vouchers out (goods receipt, dispatch), ledgers in; reconciliation screen per BRIDGE rules.
  • CRM: leads/samples sync (API if available, else file).
  • Weaver ERPs: production pickup by file/API.
  • Loomgate (machine telemetry): the existing loomgate_analytics utility (machine vendor's SQL Server → their Supabase) → our adapter polls Supabase REST (idempotent by machine serial + event seq) and writes loom events into our event log; manual entry eliminated.
  • IoT products: the company's devices send through the ingestion endpoint (HTTP JSON now; an MQTT→bridge connector when device fleet/volumes justify it). The endpoint contract IS the IoT integration surface (loomgate = first adapter of this kind).
  • Every integration event lands in the same event log with source: import:<adapter> — integration is just another source, audited like every other event.

8. Deployment & scaling path (honest — staged, not over-built)

Stage What When
Now One platform instance (Pi server); bun:sqlite — Postgres only when a Stage-2 trigger fires (§3); Cloudflare-Tunnel + Caddy door (already set up); all companies/setups as tenants Stage 1 (Phase 0–1)
Next Site gateways for offline floors (sqlite mirror + idempotent sync); MQTT bridge if the device fleet grows after Phase 3 of ROADMAP
Later Read replica(s) for dashboards; analytics read models split to a dedicated store if volumes dictate; container images for non-Pi hosts only when measurements demand it

Every later lever sits behind the same API/event boundary — nothing in the core changes when a lever is pulled.

9. What changes in this repo (and when)

Layer Today (demo) Stage 1 (Phase 0–1) Stage 2+ (trigger-gated)
Dialect bun:sqlite bun:sqlite (seams: tenancy cols, event log, Drizzle-only SQL) postgresql (drizzle) + RLS per tenant
Tenancy — tenant_id/setup_id columns + session context (enforcement after multi-tenant goes live) RLS policies active
Events form actions /api/events ingestion + event log tables monthly partitioning, retention
Realtime — SSE/WS hub + dashboard delta push read-replica fanout
Aggregates — continuous rollup job + reconcile (same DB) dedicated analytics store
Adapters — BRIDGE contracts for Tally/CRM/weaver ERPs per adapter, on demand
Dashboards demo page live operations board (per setup) + management views custom role boards

Migration step (only when a Stage-2 trigger fires — see §3): install PostgreSQL on the Pi (sudo apt install postgresql), create platform DB + roles, flip the Drizzle dialect, run schema migration, seed/migrate data, compare reconciliation totals, then cut over. Until then nothing blocks on it — the demo and Phase 0–1 run on bun:sqlite.

10. Decisions needed from you

  1. Staging triggers — confirm the §3 Stage-2 trigger list (multi-setup goes live / sustained write load / integration transactions / event partitioning), or add the ones you foresee (e.g. a hard date for a second site). Until then bun:sqlite is the store and no Postgres is installed.
  2. Tenancy granularity — confirm: tenant = company (trading co., job-work co.), setup = physical site. Any future company/site to keep in mind? (Columns are added in Phase 0 regardless — enforcement only when multi-tenant goes live.)
  3. IoT protocol — what do the IoT products currently emit (HTTP JSON, MQTT, BLE gateway, custom)? Anything today, or design-time only?
  4. Tally — which Tally (version), which machine (LAN Windows?), and whether the XML/file import path is available (Tally Integrator/TDL).
  5. First dashboards — the top-3 screens management wants live first (e.g. order book vs capacity, per-setup production board, stock cover).