Multi-Location Studio Ops Platform
Central warehouse, supplier-to-store distributions, reorder forecasting, and equipment scheduling for a fitness studio franchise — the operational layer Mariana Tek POS doesn't natively provide.
A web platform built for a multi-location fitness studio franchise (active in Arizona, expanding into Texas) that adds the operational layer Mariana Tek's POS doesn't natively provide — central warehouse management, supplier-to-store distribution tracking, automated reorder forecasting, replacement scheduling for studio equipment, and role-scoped dashboards for franchise admins and individual location owners.
Problem
Fitness franchises sell retail products through Mariana Tek, the dominant POS in boutique fitness. Mariana Tek handles individual transactions and per-store inventory well, but breaks down once you have multiple locations and a warehouse — no centralized supply-chain management, no multi-location consolidation, no reorder forecasting, no equipment replacement scheduling, and limited cross-location analytics. Without this layer, franchise admins fall back to spreadsheets, group chats, and gut feel to decide what to order, what to ship where, and when to send invoices.
What it does
- Inventory sync
- Real-time stock decrements via Mariana Tek webhooks; daily reconciliation cron; manual sync for diagnostics
- Three-tier inventory
- Supplier → warehouse → individual stores, with audit logs at every transition
- Distributions
- State machine (draft → approved → shipped → delivered) with side effects in Postgres so inventory and invoicing stay in sync
- Invoicing
- Auto-generated on delivery using snapshot pricing — editing the catalog never rewrites historical invoices
- Reorder report
- Sales-velocity priority tiers (urgent / soon / monitor / ok) with suggested order quantities computed from lead time + safety margin
- Best sellers
- Variant-rolled-up product rankings with time-range filtering
- Cross-location analytics
- Revenue, top products, by-category breakdowns, daily revenue trends
- Equipment scheduling
- Parallel track for studio operational items (mats, weights, balls) with per-location replacement cycles, auto-generated draft distributions, and stock-availability-aware notifications
- Notifications
- In-app and email alerts for low/out-of-stock, replacement due, and request status changes; daily cron for threshold checks
- Barcode
- Scanner-based stock lookup and adjustment for franchise owners
Approach
Built a Next.js 16 + Supabase platform with role isolation enforced at the database layer (Postgres Row-Level Security). Three-tier inventory model — supplier → warehouse → store — with audit logs at every transition. Distribution and equipment-replacement state machines enforced at three layers (Postgres CHECK constraints, server actions, side-effect transactions). Mariana Tek integration is intentionally read-only; webhooks decrement stock in real time, a daily cron reconciles, and a manual sync covers diagnostic re-runs.
Notable engineering decisions
Multi-tenant isolation at the database layer
Every table enforces tenant isolation via Postgres Row-Level Security policies. Helper functions (get_user_role(), get_user_location_id()) marked SECURITY DEFINER avoid the circular policy dependencies RLS commonly hits when policies need to read the same tables they protect. Policies use the (SELECT helper()) subquery pattern so the planner evaluates each helper function once per query rather than once per row — a meaningful difference on tables with thousands of rows. A franchise owner literally cannot read another store's data, regardless of how the API is called.
Push aggregation into Postgres
The first analytics implementation fetched raw rows and aggregated in JavaScript. At full historical scale (25k+ orders, 200k+ line items), each page load was transferring tens of megabytes through Vercel functions, eating function memory, and threatening Supabase egress limits. Refactored every analytics page to call SQL stored procedures returning pre-aggregated rows — KPIs collapse 25k+ orders to one row, daily revenue collapses to ≤365 day-buckets, the reorder report replaces a multi-query JS loop with a single CTE. Indexed the hot paths and added URL-driven offset pagination. Page payloads dropped from megabytes to kilobytes; analytics pages render in under 500ms at production volume.
State machines enforced at multiple layers
Distribution status transitions and equipment-replacement cycles use state machines enforced at three layers: Postgres CHECK constraints prevent illegal status values from ever being written. Server actions validate the source → target transition is allowed (draft → approved yes, delivered → draft no). Side effects — warehouse decrement, invoice creation, replacement-cycle reset — all run inside the same server action that performs the transition, so a partial failure can't leave the system in an inconsistent state. There is exactly one way to mutate distribution status; the UI and the cron scheduler both go through the same transition() function.
Snapshot pricing pattern
Distributions and invoices store price values at write-time rather than dereferencing the catalog at read-time. This mirrors how POS systems treat order line items: editing an item's price in the catalog tomorrow doesn't rewrite the numbers on a shipment that already went out today. Three discriminated columns (unit_cost, unit_sale_price, line_total) are captured on every distribution item so historical accounting stays correct as catalog data drifts.
Equipment tracking as a parallel inventory track
Studio operational items — yoga mats, ankle weights, exercise balls, grip mats — aren't sold through Mariana Tek but still need replacement on a per-location cadence (every 6 months, every 12 months, etc.). Built a separate equipment_* schema rather than overloading the retail product tables, with per-location requirements, a SQL function computing next-due dates and status entirely in-database, and a daily scheduler that auto-creates draft distributions when warehouse stock is sufficient and notifies admins with stock-availability context. Invoices integrate via a kind discriminator column with a CHECK constraint enforcing exactly-one-source.
Webhook + cron + manual sync triangulation
Real-time inventory updates come from Mariana Tek webhooks (order.completed decrements store inventory), but webhooks are best-effort — they can be missed during outages or rate-limiting. The platform layers in two more sources of truth: a daily cron re-fetches recent orders and reconciles inventory counts, and a manual sync is exposed as an admin action for diagnostic re-runs. Three layers of redundancy that converge on the same canonical state in Postgres.
Architecture
The integration with Mariana Tek is intentionally read-only. The platform is the source of truth for all inventory state — Mariana Tek provides the catalog and sales events; everything downstream (warehouse, distributions, invoices, equipment) lives in Postgres.
Outcome
Currently in production across 3 active locations, syncing 25k+ orders and 200k+ line items from Mariana Tek. Analytics pages render in under 500ms at production data volume after pushing aggregation into Postgres stored procedures. Franchise owners get a fully scoped view of their location only; admins see the whole network with reorder priorities, top sellers, and equipment due dates in one place.
What I’d build next
- Demand forecasting with seasonal adjustments rather than the current trailing-window moving average
- Materialized views for the heaviest analytics queries once data volume hits the next order of magnitude
- Real-time dashboards via Supabase Realtime subscriptions instead of refresh-on-load
- Mobile-first scanner flows for store-floor stock counts and incoming-shipment receiving
- Predictive stockout alerts that combine sales velocity with in-flight distribution ETAs