Building MainTrac from Scratch

Before the detail, here is the recommended stack at a glance. Every choice below is justified in Section 4 with explicit tradeoffs considered.

Every tech stack choice is downstream of what you are building for. Before evaluating options, lock in the constraints and values that will govern tradeoffs throughout the project.

MainTrac's most significant user-experience problem — and the one most likely to make or break adoption of a standalone product — is the mobile experience. The current RecTrac implementation has documented failures here: work order email deep links break on mobile, there is no staff-based filtering in the field view, and there is no logging wizard. Customers have explicitly called these out.

Understanding why the current system fails on mobile is the starting point for making better architecture decisions.

PWA vs. Native App — The Core Decision

The verdict: The customer-reported pain points (camera photos, push notifications, deep-link from email, smooth work order listing) all point to native. PWA is a reasonable supplement — a lightweight web view for situations where the app isn't installed — but it should not be the primary mobile delivery vehicle for field maintenance workers.

Offline-First Architecture for Field Workers

A maintenance technician in a park or utility tunnel cannot depend on connectivity. The mobile app must treat offline as the default state, not an exception to handle.

Mobile UX Patterns to Build In From Day One

4.1 Architecture Shape — Modular Monolith

The recommended shape is a Turborepo monorepo with clear internal module boundaries:

# Repository structure
maintrac/
├── apps/
│   ├── web/          # Next.js management UI
│   ├── mobile/       # Expo React Native app
│   └── api/          # Hono REST API server
├── packages/
│   ├── core/         # Domain types, business rules, validation (shared)
│   ├── db/           # Drizzle schema, migrations, query helpers
│   ├── auth/         # Clerk helpers, permission helpers (shared)
│   └── ui/           # shadcn/ui components for web (shared between web screens)
└── turbo.json

This gives you the benefits of a monorepo (shared types, single version lock, one CI pipeline) while preserving the ability to deploy apps/api independently as the API grows.

4.2 Frontend Framework — Management UI

Component Library: shadcn/ui + Tailwind CSS

Don't use a heavyweight component library like MUI or Ant Design for a greenfield build. Use shadcn/ui — a collection of unstyled, accessible, copy-paste components built on Radix UI primitives, styled with Tailwind. You own the source code; nothing is locked in a dependency you can't modify. This matters for a maintenance product where you'll have bespoke UI patterns (work order cards, inspection checklist forms, asset trees) that no off-the-shelf library handles well.

4.3 Mobile Framework

4.4 Backend API Framework

4.5 Database

ORM: Drizzle over Prisma

Multi-Tenancy via Row-Level Security

Every table in the MainTrac schema gets a tenant_id column. PostgreSQL Row-Level Security policies enforce isolation at the database layer — even if application code has a bug, one tenant cannot see another's data. The API sets a session variable (SET app.current_tenant = '...') after authentication, and all queries are automatically filtered.

-- Example RLS policy (applied to every MT table)
CREATE POLICY tenant_isolation ON work_orders
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

ALTER TABLE work_orders ENABLE ROW LEVEL SECURITY;

Schema Design Notes for the MainTrac Domain

• Use UUIDs (v7 for sortability) as primary keys — safe for distributed IDs, works across sync scenarios
• Work order status transitions enforced via a CHECK constraint or a state machine table — don't rely solely on application logic
• All log entries (labor, material, equipment, combo) use an append-only pattern — never UPDATE a log row; INSERT a reversal record instead (matches the existing MTLogReverse.p pattern)
• Add pgvector extension from day one — enables semantic search on work order descriptions and AI-assisted task suggestions later at no migration cost

4.6 API Design

Decision: REST + OpenAPI 3.1, generated automatically from Hono route definitions + Zod schemas. Use Hono's typed client internally between web/mobile apps and the API (giving tRPC-like type safety), while publishing the OpenAPI spec externally for third-party integrations and for NextRec/RecDesk to consume.

4.7 Authentication & Authorization

Auth is the #1 coupling blocker from the RecTrac decomp analysis. It must be a first-class standalone capability, not bolted on. The choices are:

4.8 Real-Time & Notifications

MainTrac needs real-time updates in two scenarios: a supervisor watching work order statuses change during the day, and a technician being notified of a new assignment. These have different requirements.

4.9 File & Photo Storage

Photos are a central part of the mobile workflow — before/after shots of work, inspection photo documentation. The wrong architecture here creates server bottlenecks.

Pattern: direct-to-S3 upload via presigned URLs.

1. Mobile app requests a presigned upload URL from the API (POST /uploads/presign)
2. API generates and returns a short-lived S3 presigned URL
3. Mobile app uploads directly to S3 — the photo bytes never touch your API server
4. On upload completion, mobile app calls POST /work-orders/:id/photos with the S3 key to save the reference in the DB

This pattern keeps your API server stateless and avoids saturating it with multi-megabyte photo uploads during busy inspection runs. Cloudflare R2 is a cost-effective S3-compatible alternative (no egress fees) if cost is a concern.

4.10 Reporting & Analytics

4.11 Cloud & Hosting

The previous document identified eight coupling dependencies. Here is exactly how the recommended stack resolves each one.

The migration from the current Progress-based implementation to the new stack must be done without a "big bang" cutover. Customers need continuity. The recommended approach is the Strangler Fig pattern — new functionality goes into the new stack while the old system continues to run, and screens are migrated one by one until the old system can be decommissioned.

Assuming a small team (3–5 engineers), sequence the work to deliver value to users as early as possible while managing technical risk.

1. 1

   Weeks 1–4: Foundation

   Monorepo scaffold (Turborepo), Postgres schema + Drizzle ORM, Clerk auth wired up, Hono API skeleton with health check, Next.js shell deployed, Expo app bootstrapped with Expo Router. CI/CD pipeline running. No feature work — just infrastructure that everything else builds on.

2. 2

   Weeks 5–10: Core Domain (API-first)

   Work Orders CRUD, Asset management (all 5 types), Task codes, PM Plans. Build as API endpoints first with full OpenAPI spec. Integration tests against a test Postgres DB. This is the skeleton — all later UI is built on these endpoints.

3. 3

   Weeks 11–18: Mobile App (Field Priority)

   Expo mobile app: work order list (with staff filtering from day one), work order detail + status update, logging wizard (labor + materials in one flow), photo capture + S3 upload, offline sync (WatermelonDB). Push notifications for assignment. Deep link from email. This is the flagship differentiator — ship it before the web redesign.

4. 4

   Weeks 19–28: Web Management UI

   Next.js screens: Work Order management board, Asset registry, PM Plan scheduler, Combination Log review, supervisor dashboard with charts. shadcn/ui component library established. SSE for live status updates.

5. 5

   Weeks 29–36: Reporting + Inspections

   Work Order Analysis Report (PDF + CSV), Project Results Report, Facility Task Report. Mobile inspection forms with offline capture. Inspection Results Report. Metabase embedded for ad-hoc analytics.

6. 6

   Weeks 37+: Gap Closure + AI Features

   Contract Processing as a first-class workflow (fix the hybrid approach from 10.2 migration). Public API launch (VSXORG-246 delivered). Natural language work order search. AI-suggested PM plan schedules based on historical completion data. Predictive cost alerts.

The previous sections assume a modern shared SaaS deployment but don't directly address the question of how MainTrac handles ~80 customer organizations — each with multiple employees — in a single running application. This section covers the multi-tenancy model options, what the scale numbers actually mean in practice, the connection pooling requirement that the rest of the document omits, and how to offer both a shared SaaS tier and a dedicated tier for customers who need it.

8.1 Multi-Tenancy Models — Three Options

There are three standard approaches to multi-tenancy in a PostgreSQL-backed SaaS. The right choice for MainTrac depends on how you weight operational simplicity against customer isolation guarantees.

How RLS Works in Practice

Every table in the MainTrac schema carries a tenant_id column. The API middleware sets a Postgres session variable immediately after authenticating the request. All subsequent queries in that session are automatically filtered — no application-level WHERE clause required.

-- 1. Every table has tenant_id
CREATE TABLE work_orders (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id  uuid NOT NULL,           -- always present, never nullable
  title      text NOT NULL,
  status     work_order_status NOT NULL DEFAULT 'new',
  ...
);

-- 2. Enable RLS and add the policy
ALTER TABLE work_orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON work_orders
  AS RESTRICTIVE
  USING (tenant_id = current_setting('app.current_tenant', true)::uuid);

-- 3. API middleware sets the session variable on every request
-- (Hono middleware — runs before any route handler)
app.use('*', async (c, next) => {
  const tenantId = getTenantFromJWT(c)          // extracted from Clerk JWT
  await db.execute(sql`SELECT set_config('app.current_tenant', ${tenantId}, true)`)
  await next()
})

With this in place, a query like SELECT * FROM work_orders automatically returns only the current tenant's rows — even if a developer forgets to add a WHERE clause. The database enforces it.

8.2 Scale Analysis — What 80 Customers Actually Means

Before over-engineering for scale, it's worth putting concrete numbers against the workload. MainTrac is a maintenance management product, not a consumer app. The usage patterns are predictable and modest.

8.3 Connection Pooling — The Gap in the Original Analysis

This is the one infrastructure requirement the rest of this document omits, and it matters at 80 customers. PostgreSQL allocates memory per connection — typically ~5–10MB each. The default max_connections on RDS is 80–170 depending on instance size. With 400 concurrent users each holding a connection from the API server, you will exceed this limit without a pooler.

8.4 Deployment Tiers — Shared SaaS vs Dedicated

The recommended production architecture has two tiers. Most customers land on the Shared tier automatically; the Dedicated tier exists for the handful of customers with compliance or contractual requirements.

Infrastructure Diagram — Shared Tier

  ┌─────────────────────────────────────────────────────────────┐
  │                     AWS (or Azure)                          │
  │                                                             │
  │   CloudFront CDN                                            │
  │        │                                                    │
  │        ▼                                                    │
  │   S3 (Next.js static)      Expo / App Store                 │
  │        │                        │                           │
  │        └────────────┬───────────┘                           │
  │                     ▼                                       │
  │              ECS Fargate                                     │
  │         ┌────────────────────┐                              │
  │         │  Hono API (N tasks)│ ◄── auto-scale on CPU/req    │
  │         └────────┬───────────┘                              │
  │                  │                                          │
  │                  ▼                                          │
  │           RDS Proxy (connection pooler)                     │
  │                  │                                          │
  │                  ▼                                          │
  │         RDS PostgreSQL 16                                   │
  │         ├── primary (writes)                                │
  │         └── read replica (reports, analytics)               │
  │                                                             │
  │   ElastiCache Redis  ──►  rate limiting, job queues         │
  │   S3 bucket          ──►  photos (per-tenant key prefix)    │
  │   SQS + Lambda       ──►  PM plan auto-generation, emails   │
  └─────────────────────────────────────────────────────────────┘

8.5 Moving Existing Customers from Per-Instance to Shared SaaS

The 80 existing RecTrac customers running MainTrac today are each on their own instance. Migrating them into the shared SaaS model is a one-time data migration per customer — not a technical blocker, but it needs a process.

Summary: What Changes in the Recommended Stack

Adding these deployment considerations refines the stack in three specific ways:

1. Add RDS Proxy between ECS and RDS — required to handle concurrent connections safely at 80+ customers. Without it you will hit Postgres connection limits under normal load.
2. Use RESTRICTIVE RLS policies (not just USING — the default is permissive) and set session variables before the first query in every request, not lazily. This is a correctness requirement, not a performance optimization.
3. Design a tenants table and a deployment-tier flag from day one. The column tenants.deployment_tier ENUM('shared', 'dedicated') drives which DATABASE_URL the API resolves at request time. Retrofitting this after launch is expensive.