ARCHITECTURE
A unified relational core — one SQL query extracts the complete operational and financial history of any asset. No intermediate APIs. No latency.
Core Philosophy
OPS Companion is built on a single, non-negotiable architectural principle: eliminate every layer that adds latency without adding value. There are no intermediate REST APIs between the client and the database. Row-Level Security enforces access control directly in the SQL engine. WebSockets push changes to all connected clients simultaneously. The result is a system that feels instantaneous even under heavy operational load.
The entire system is built on 54 SQL tables, protected by 168 RLS policies, delivering results in under 2.5 seconds — always.
The 54-Table Data Model
The relational core is organized into eight functional table groups, all cross-referenced to allow a single query to retrieve the complete history of any asset — mechanical state, service records, GPS trajectory, parts consumed, communications, and financial transactions — without any joins to external services.
Equipment registry, makes/models/engines, attachments, specs, GPS pins, wear state, service manuals
Work order lifecycle, assignments, status tracking, completion records, linked inspections and parts
User accounts, role assignments, certifications, training records, punch history, performance data
Multi-warehouse stock, parts catalog, supplier records, order history, parts-to-machine linkage
Quote pipeline, line items, client signatures, billing records, revenue tracking
Real-time position events, route history, community odometer records, geofence zones and violations
Chat messages, document vault, read receipts, push notifications, radio logs
Immutable audit trail, sealed electronic reports, compliance records, activity timestamps
168 Row-Level Security Policies
Row-Level Security (RLS) is enforced directly inside the SQL engine — not at the application layer. This means that even if every other layer of the system is compromised, the database itself will refuse to deliver a single row of data without a validated digital fingerprint from the authenticated user.
Every SELECT, INSERT, UPDATE, and DELETE passes through the RLS layer. A technician cannot see another technician's work orders. A driver cannot access financial data. A sales rep cannot see internal costs.
WebSocket Real-Time Layer
All eight role dashboards maintain persistent WebSocket connections. When any record changes — a work order is assigned, a GPS position updates, a chat message is sent — all subscribed clients receive the change in under 500ms.
The dispatcher's 3D map, the technician's work order list, and the administrator's KPI dashboard are all live simultaneously. No polling. No refresh. The system behaves like a living, breathing operations center.
Offline-First Architecture
Network disconnection is treated as a normal operating state, not an error condition. Every data modification made offline is stamped with a vectorized temporal signature — not just a timestamp, but a precise causal marker that allows intelligent merge on reconnect.
When connectivity is restored, the system performs an intent-aware merge: it understands that a technician who logged a parts consumption at 14:32 while offline and a dispatcher who assigned a new work order at 14:33 online are not in conflict. Both changes are applied cleanly, in the correct causal order.
The 2.5-Second Rule (Jour 05)
Enforced through three mechanisms working in concert:
- ▸Dynamic Temporal Partitioning (Jour 04): The 54 tables are sliced into surgical time windows asynchronously. Historical searches across millions of rows return instantly because the relevant partition is pre-indexed.
- ▸Async Indexing: Index maintenance runs asynchronously, never blocking user queries. The system pre-computes the most likely next queries based on the user's role and current context.
- ▸Direct SQL→RLS→Client: There are no intermediate API servers to add network hops. The query goes from the client directly to the database engine, through RLS, and back. Total network roundtrips: one.
Hexa-SQL Proprietary Injector (Jour 03)
For organizations with physical telemetry hardware on their machines, OPS Companion includes a proprietary low-level binary frame decoder. It intercepts the binary frames emitted by telematics modules — CAN bus, OBDII, proprietary manufacturer protocols — and injects them directly into the SQL data model.
Zero cloud gateway. Zero third-party middleware. The hardware talks directly to the SQL engine in the language of the metal. This eliminates an entire layer of latency, security exposure, and subscription costs typically associated with IoT data pipelines.
The 30-Day Build
OPS Companion was built publicly, one architectural invention per day, over 20 days in May 2026. Each day, one core component of the system was designed, implemented, and documented publicly — from the unified 54-table schema (Jour 01) to the hardware-software symbiosis layer (Jour 20).
This transparent build process is documented in full in the Changelog.