/ System Architecture
ShopTrack 360 · Technical Overview

Built for the shop floor.
Engineered for the cloud.

ShopTrack 360 is a custom-built, cloud-native operations portal designed specifically for aviation MRO and manufacturing environments. This document outlines the technical architecture, integrations, and security design of the platform.

36
Database Tables
MySQL source of truth
190+
API Endpoints
Fully typed & validated
25+
Reports & Exports
On-demand & scheduled
13
Tables Exported Nightly
Compliance archive
60s
BigQuery Sync Cadence
Delta-only pipeline
Data Flow

Two pipelines, one platform

Internal shop floor operations flow directly from MySQL to native dashboards — no sync lag, no external dependency. Company-wide client reporting flows through BigQuery to Looker Studio on a separate 60-second sync cadence.

Internal Operations — Real-Time
👷
Shop Floor
Technicians, QA, Receiving
→
🖥️
Portal
ShopTrack 360 web app
→
🗄️
MySQL Database
Source of truth
→
📊
Native Dashboards
G-Dash · Pulse Dash · KPIs
G-Dash & Pulse Dash query MySQL directly — no BigQuery hop. G-Dash polls every 50–70 seconds with per-monitor random jitter, designed for 10+ simultaneous overhead screens. Pulse Dash is fully mobile and portrait-responsive.
Client Reporting — Looker Studio
🗄️
MySQL Database
Source of truth
→
☁️
BigQuery
GCP analytics warehouse
→
📈
Looker Studio
Client reporting
Sync cadence: MySQL → BigQuery delta sync runs every 60 seconds automatically. Only new or updated records are transferred — no full table reloads. Looker Studio dashboards reflect data that is at most 60–90 seconds old at any given time.
Technology Stack

Purpose-built, not off-the-shelf

Every layer of the stack was chosen for reliability, performance, and long-term maintainability. No bloated SaaS middleware — just clean, direct integrations.

⚙️
Application Platform
Modern full-stack web application
  • ▸React 19 + TypeScript frontend — fast, type-safe, and maintainable
  • ▸Node.js / Express server — handles all API requests and business logic
  • ▸Server-side rendering (SSR) — pages load instantly, even on first visit
  • ▸Vite build pipeline — optimized production bundles with code splitting
  • ▸Tailwind CSS design system — consistent, responsive UI across all devices
🗄️
Data Storage
Managed relational database
  • ▸MySQL — industry-standard relational database, fully managed and backed up
  • ▸Drizzle ORM — type-safe query layer; no raw SQL in application code
  • ▸33 purpose-built tables covering every operational domain
  • ▸Automatic schema migrations on every deployment — zero manual DB work
  • ▸All data scoped by tenant — complete isolation between client accounts
☁️
Google Cloud Platform
Client-facing analytics pipeline
  • ▸BigQuery — Google's enterprise data warehouse for company-wide client reporting
  • ▸Automated sync every 60 seconds — operational data flows to BigQuery continuously
  • ▸Delta sync (watermark-based) — only new records are transferred; no full reloads
  • ▸18 tables mirrored to BigQuery — full operational coverage in the analytics warehouse
  • ▸Service account authentication — secure, credential-based GCP access
  • ▸Looker Studio dashboards — client-facing management reports built on BigQuery
📊
Native Dashboards
Real-time shop floor visibility — no Looker dependency
  • ▸G-Dash (/gdash) — full-width overhead monitor dashboard; 1080p/4K optimized for shop floor displays
  • ▸G-Dash polls MySQL directly every 50–70s with per-monitor jitter — designed for 10+ simultaneous screens
  • ▸Pulse Dash (/metrics) — management metrics dashboard; mobile and portrait-responsive
  • ▸KPIs Dash (/kpis-dash) — key performance indicators for leadership review
  • ▸All native dashboards query MySQL directly — zero latency from BigQuery sync lag
  • ▸25+ on-demand report and data export types available within the portal
🏢
Multi-Tenant SaaS Architecture
Built to scale across multiple client sites
  • ▸Every client account is a fully isolated tenant — data never crosses boundaries
  • ▸Subdomain routing: each client gets their own URL (client.shoptrack360.com)
  • ▸Custom domain support — clients can use their own domain (portal.acme.com)
  • ▸Three plan tiers: Starter, Professional, Enterprise — features gate by plan
  • ▸Self-service onboarding wizard — new tenants can be provisioned in minutes
  • ▸Super-admin portal for platform management — separate from shop-floor access
🔐
Security & Compliance
Enterprise-grade security posture — FAA, AS9100, ITAR ready
  • ▸Role-based access control — 8 distinct roles (Tech, QA, Receiving, Stockroom, Helpdesk, Admin, System, Owner)
  • ▸bcrypt password hashing (cost factor 12) — industry-standard credential storage
  • ▸WebAuthn / Passkey login — biometric authentication (Face ID, Touch ID, Windows Hello)
  • ▸Account lockout after 5 failed attempts — 15-minute automatic lock
  • ▸Session tokens (256-bit, 8-hour TTL) — stored client-side, never in cookies
  • ▸10-minute inactivity timeout — automatic session lock with visible countdown warning
  • ▸Full HTTP security headers — CSP, X-Frame-Options, HSTS, referrer policy
  • ▸Rate limiting on all endpoints — auth endpoints strictly throttled (10 req/15 min)
  • ▸Immutable audit log — every write action recorded with user, timestamp, and IP
  • ▸FAA traceability — every operation tied to a stamp number, aircraft S/N, and work order
  • ▸AS9100 audit trail — NCR workflow, WO sign-off chain, and training records support AS9100 Rev D requirements
  • ▸ITAR access controls — role and module gating prevents unauthorized access to controlled technical data
  • ▸Visitor read-only enforcement — hardened at the API layer, not just the UI
Operational Modules

11 integrated modules, one portal

Each module is role-gated, fully audited, and feeds data into the BigQuery analytics pipeline.

⏱️
Task Timesheet Tracker
Clock in/out by task and aircraft S/N; toggle between task time and indirect hours
📋
WO Timesheet Tracker
Work order time tracking with rework flagging
📝
WO Planner
7-step MRO / 6-step Mfg sign-off workflow with PDF generation; ST-XXX and MFG-XXXX WO numbering
🔍
QA Tracker
Inspection queue, approvals, and photo capture; three tabs: Queue, Search, Inp Log
⚠️
NCR Process Tracker
6-step non-conformance workflow with re-auth on closure steps and compliance email on Step 6
📦
Receiving Inspection
Inbound parts inspection and Rec-NCR logging
🏭
Inventory & Stockroom
Two-stage inventory workflow: receiving inspection → stockroom movement
🔧
Tooling Cert Tracker
Tool certification status and expiry tracking
🎓
Employee Training Tracker
Training records, task qualification status, bulk import, and deactivate/activate support
📐
Planning Dashboard
WO planning history, hours analysis, and reporting
📊
KPIs Dashboard
Performance, Workforce Attrition, and Safety & OSHA tabs; Performance tab live with real-time MySQL data
⚙️
Admin Panel
8 tabs: Records, Users, Audit Log, Import (9 modes), Support, Instructions, System Status, Tenants; owner-only PinGate
SaaS Architecture

Multi-tenant by design

The platform is architected as a true multi-tenant SaaS product. Each client organization is a fully isolated tenant — their data, users, and configuration are completely separate from every other account on the platform.

🔒
Data Isolation
Every database record is scoped to a tenant ID. A query from one tenant can never return data belonging to another — enforced at the query layer, not just the UI.
🏭
Multi-Location Support
A single client organization can run ShopTrack 360 across multiple manufacturing or MRO locations — each as its own fully isolated tenant. Users, timesheets, WOs, NCRs, and reports are completely separate per site. Owner-level accounts retain cross-tenant visibility for corporate oversight.
🌐
Custom Domains
Each tenant can operate on their own subdomain (client.shoptrack360.com) or a fully custom domain (portal.client.com). DNS routing is handled automatically — no configuration required from the client.
📦
Plan Tiers
Starter, Professional, and Enterprise plans gate access to features at the API level — not just the UI. Upgrading a tenant's plan instantly unlocks the corresponding capabilities.
Scalability & Performance

Built to grow with your operation

The database layer is engineered to maintain consistent query performance as data volume grows across multiple tenants and locations. Compound indexes on the highest-volume tables ensure reports and dashboards stay fast whether you have one site or ten.

⚡
Compound Query Indexes
The four highest-volume tables — task timesheets, audit log, and receiving log — carry compound (tenantId + date/stamp) indexes. Report queries that filter by location and date range hit an index instead of scanning the full table, keeping response times consistent as row counts grow.
🏗️
Idempotent Schema Migrations
All database changes — including index additions — run automatically at server startup and check for existence before executing. Adding a new location or upgrading the schema never requires manual intervention or a maintenance window.
🔌
Configurable Connection Pool
The MySQL connection pool size is controlled by a single secret (DB_POOL_SIZE) with no code deploy required. For a single-location deployment the default of 25 connections is sufficient. Multi-location deployments can scale to 50–75 connections to handle concurrent users across all sites simultaneously.
📡
G-Dash Jitter Design
The overhead dashboard poll interval is randomized ±10 seconds around a 50–70 second base. When multiple locations run G-Dash simultaneously, their queries are naturally staggered — preventing synchronized database spikes and keeping overhead screen latency smooth across all sites.
🗂️
Delta-Only BigQuery Sync
The MySQL → BigQuery pipeline transfers only new or changed records on each 60-second cycle — never a full table reload. As data volume grows across tenants, sync overhead stays proportional to activity, not total row count.
📦
Tenant-Scoped Everything
Every query in the system is scoped to a tenantId at the ORM layer before it reaches the database. Adding a new location adds rows to existing tables — no new tables, no schema changes, no redeployment. The platform scales horizontally to new tenants with zero infrastructure changes.
Audit & Compliance

Every action is recorded. Every record is preserved.

The platform maintains an immutable audit log of every write operation — who did what, when, from which IP address, and what changed. All production records are retained per regulatory requirements and delivered automatically to designated compliance recipients.

Regulatory Alignment
FAA Part 145Repair Station Records
FAA Part 21Production Approval
AS9100 Rev DClause 7.5 — Documented Information
ITARAccess Control & Data Protection
Client QA ManualTraceability & NCR Workflow
🗄️
Nightly Compliance Export & Records Retention
AUTOMATED · NIGHTLY

Every night at midnight UTC, the platform automatically exports the complete dataset for every active tenant — all 13 operational tables — to individual CSV files and a single multi-sheet Excel workbook. The workbook is emailed to the designated compliance inbox and the client QA lead. A 90-day rolling archive is maintained on-platform in encrypted persistent storage. This process requires no manual intervention and runs continuously in the background.

Tables Exported Nightly
13
Task Timesheets · Work Orders · QA Inspections · QA Task Approvals · NCR Log · NCR Workflow · Rec NCR Forms · Receiving Inspection · Tooling Certifications · Employee Training · Task Planning · Stockroom Movement · Inventory Tracker
Delivery
2 Recipients
Compliance inbox (primary archive) + Client QA lead (secondary). Multi-sheet .xlsx workbook attached. Delivered by 12:05 AM UTC every night.
On-Platform Retention
90 Days
Daily snapshots stored at /private/exports/{tenantId}/YYYY-MM-DD/. Folders older than 90 days are pruned automatically. BigQuery provides unlimited long-term analytical retention.
Designed for:
FAA Part 145 — Repair Station Records RetentionFAA Part 21 — Production Approval RecordsAS9100 Rev D Clause 7.5 — Documented Information Protection
📝
Immutable Audit Log
All create, update, and delete operations are logged with user identity, timestamp, IP address, and before/after values. Logs cannot be edited or deleted by any role — including system and owner.
📧
Compliance Email Notifications
NCR closures, Rec-NCR completions, and WO Planning sign-offs automatically trigger compliance email notifications to designated recipients — in addition to the nightly full-dataset export.
🔑
Role-Based Access Control
Eight distinct roles — Tech, QA, Receiving, Stockroom, Helpdesk, Admin, System, and Owner — with strict module gating. Users can only access the modules their role and the Module Access Matrix permit.
⏱️
Session Management
Portal sessions expire after 8 hours. A 10-minute inactivity timeout locks the session automatically with a visible 60-second countdown warning. Module-level secondary authentication adds an additional layer for sensitive operations.
✈️
FAA Traceability Chain
Every time entry, inspection, NCR, and WO action is tied to a technician stamp number, aircraft serial number, and work order — providing the complete traceability chain required for FAA Part 145 and Part 21 regulated operations.
📋
AS9100 Rev D Quality System
The NCR 6-step workflow, WO sign-off chain, employee training records, and tooling certification tracking are designed to support AS9100 Rev D quality management system requirements and client QA manual procedures.
🛡️
ITAR Access Controls
Role and module gating prevents unauthorized access to controlled technical data. Visitor accounts are enforced read-only at the API layer — not just the UI — ensuring no controlled data can be written or exported by unauthorized users.
👁️
Visitor Read-Only Enforcement
The visitor role is blocked from all write operations at the server level. Clock-in, WO start, and all state-changing endpoints validate the portal token and return 403 for this role regardless of UI state.
Deployment & Reliability

Always on, always current

🚀
Zero-Downtime Deployments
Schema migrations run automatically on startup and are fully idempotent — safe to run repeatedly with no data loss or service interruption.
🔄
Automatic Data Sync
The BigQuery sync scheduler runs continuously in the background. No manual exports, no scheduled jobs to manage — data flows automatically.
🛡️
Fail-Safe Design
The tenant resolution middleware fails open to the primary tenant if the database is temporarily unavailable — the portal stays accessible even during brief DB hiccups.
📱
Mobile Responsive
The portal is fully responsive and accessible from any device — desktop workstations, tablets on the shop floor, or mobile phones for QR-based login.
Client Onboarding Roadmap

From signed agreement to fully operational

A new client can be provisioned, trained, and live on the platform in a single session. The following seven steps cover everything from tenant creation through go-live handoff.

01
Agreement & Preparation
Agreement signed — prepare the account
  • ▸Confirm the client's company name, primary contact, and preferred subdomain slug (e.g. acme → acme.shoptrack360.com)
  • ▸Decide on plan tier: Starter, Professional, or Enterprise — this determines which modules are available
  • ▸Collect the list of users who will need access and their intended roles (Tech, QA, Receiving, Stockroom, Helpdesk, Admin)
  • ▸Confirm whether the client wants a custom domain (portal.acme.com) in addition to the subdomain
02
Tenant Provisioning
Create the tenant via the onboarding wizard
  • ▸Navigate to shoptrack360.com/onboarding — this is the self-service 4-step wizard
  • ▸Enter the company name, choose a unique slug, and set the plan tier
  • ▸Create the owner account: username and passcode for the first admin user
  • ▸Submit — the system creates the tenant and owner account in a single transaction
  • ▸Verify: the new tenant appears in the Super-Admin portal at /tenant-admin
03
Domain & Access Setup
Configure the domain and verify access
  • ▸The subdomain (slug.shoptrack360.com) is active immediately — no DNS work required
  • ▸For a custom domain: go to /tenant-admin → Domains tab → add the client's domain
  • ▸Share the portal URL with the client and confirm they can reach the login screen
  • ▸Log in with the owner account to verify the workspace loads correctly
04
User Accounts
Create all user accounts in the Admin panel
  • ▸Log in to the portal at the client's URL and navigate to /admin
  • ▸Use the Users tab → Add User form to create each team member
  • ▸Assign the correct role: Tech, QA, Receiving, Stockroom, Helpdesk, or Admin
  • ▸Tech and Inspector roles require a stamp number — collect these in advance
  • ▸Set a temporary passcode for each user — they can register a biometric passkey after first login
  • ▸Use the Module Access Matrix to grant or restrict specific module access per user
05
Data Seeding
Seed reference data for the client's operation
  • ▸Add aircraft tail numbers (S/Ns) and work order templates relevant to the client's fleet
  • ▸Import or enter tooling certification records into the Tooling Cert Tracker
  • ▸Enter employee training records and qualification data into the Training Tracker
  • ▸Verify all reference data appears correctly in the portal before going live
06
Dry-Run & Training
Walk through every module with the client
  • ▸Timesheet: have a tech clock in and out on a real task — confirm the record saves
  • ▸NCR: create a test non-conformance, walk through all 6 steps, verify compliance email fires
  • ▸Receiving: log a test inbound part and confirm it appears in the inspection queue
  • ▸WO Planner: create a test work order, complete all 7 sign-off steps, verify the PDF
  • ▸QA Tracker: submit a test inspection with an approval photo — confirm photo saves and displays
  • ▸Reports: open the Reports panel and confirm report types load correctly
  • ▸Admin: confirm the audit log captured every action taken during the dry-run
07
Go-Live
Client is operational — hand off and monitor
  • ▸Delete or archive any test records created during the dry-run
  • ▸Share the System Architecture page (shoptrack360.com/system-status) with the client's IT contact
  • ▸Confirm the BigQuery sync is running — check the Looker Studio dashboards show live client data
  • ▸Confirm G-Dash and Pulse Dash are displaying live shop floor data from MySQL
  • ▸Provide the client with the Admin panel URL and confirm they can manage their own users
  • ▸Schedule a 30-day check-in to review usage, address questions, and confirm all modules are in active use
Product Line Architecture

Multi-program operations from a single tenant

Product lines segment a tenant's operation by aircraft program (e.g. TF-200, TF-500). Each aircraft, task, work order, and user can be assigned to a product line — enabling program-level filtering across modules, dashboards, and reports without requiring separate tenant accounts.

🏭
tenant_product_lines
Canonical product line config per tenant. Drives all dropdowns across the platform — Users tab chip picker, import templates, and module filters. Managed by owners via the System Status tab in the Admin Panel.
✈️
product_line on aircraft, task_planning, work_orders, task_timesheets
VARCHAR(50) NULL column on four tables. Null = unassigned (visible to all users). Non-null = scoped to that program. Non-breaking migration — existing records default to null.
👤
product_lines on users
VARCHAR(200) NULL column on the users table. Stores a comma-separated list of assigned product lines. Null = all lines (cross-program roles: QA, admin, owner). Managed via the Users tab chip picker.
📥
Import template enrichment
Aircraft (Mode 1), Task Planning (Mode 5), and WO & Rev (Mode 6) import templates include an optional productLine column. Workforce Baseline (Mode 10) and Safety Baseline (Mode 11) are new import modes for KPI data load.
7-Phase Rollout
LIVE
Phase 1 — Schema migration
product_line on 4 tables · product_lines on users · tenant_product_lines table
LIVE
Phase 2 — Admin infrastructure
Product Lines config panel · Users tab chip picker · 3 API endpoints
LIVE
Phase 3 — Import template enrichment
Optional productLine column on task-planning, wo-rev, aircraft · 2 new import modes
LIVE
Phase 4 — Admin KPI Data tab
Workforce + Safety event logs · Performance metrics · 9th admin tab
LIVE
Phase 5 — KPI dashboard live wiring
All 3 tabs live · Performance + Workforce + Safety · 60s poll
LIVE
Phase 6 — Module filtering
Clock-in dropdowns · Planning + Reports filters · ?line= param on monitors
PENDING
Phase 7 — GCP Datastream stream update
Add workforce_events + safety_events to stream · 15-min GCP console step
🛠️

Built by ShopTrack Soft

ShopTrack 360 is a custom-developed platform by ShopTrack Soft, designed from the ground up for aviation MRO and precision manufacturing operations. Every feature was built to solve real problems on the shop floor — not adapted from a generic template.

All actions are audit-logged  ·  ShopTrack 360 v3.3.0  ·  Nov 8, 2026  ·  Engineered by ShopTrack Soft  ·  Confidential