---
name: jungle-mt-sync
description: Mariana Tek API sync for Jungle Studio - credentials, auth flow, report IDs, and sync architecture
tags: [jungle, mariana-tek, sync, api]
---

# Jungle Studio — Mariana Tek Sync

## MT API Credentials (also in PM2 env vars for jungle-mobile)
- MT_EMAIL: anazerian@exxircapital.com
- MT_PASSWORD: Jungle1234!
- MT_CLIENT_ID: QcTebKIlS9xrrU6Gj2r3gJxrponZVbUPjcbSlWwq
- MT_CLIENT_SECRET: AqDk9uwMsd8FagY4PlhI8WhpOzWSc8HX5vBNW4ALQ3RD0g5wRkC54ft0sHXViZv5aDtX4k3hbSKn7y66esbZI3Vf2PDV5ASkRbeuAXbWZi8YLFuiO4T0izWo2Jk3aEpt
- Note: current MT account works with OAuth password grant only after retrying WITHOUT client creds (`unauthorized_client` when client_id/client_secret are sent). jungle-mobile already retries this way, so leave the fallback logic in place.
- MT_REFRESH_TOKEN: JdlOMwXm4aAs46TWrxRsZOa3KKhFv7
- MT Base URL: https://junglestudiofitness.marianatek.com

## Auth Flow
1. OAuth password grant: POST /o/token/ with grant_type=password, username, password, client_id, client_secret
2. Returns access_token (7-day expiry) + refresh_token
3. Use Bearer token for API calls
4. Required header: X-Requested-With: XMLHttpRequest

## Report IDs
- Reservations: #341 (50 fields, ~1200 rows/day across 3 studios)
- Orders: #330
- Membership Summary: #337

## API Endpoints
- GET /api/table_reports — list all reports
- GET /api/table_report_data/{id}?start_date=YYYY-MM-DD&end_date=YYYY-MM-DD — sync fetch (max 500 rows)
- If >500 rows: response has max_results_exceeded=true, triggers async pipeline
- POST /api/table_report_data/{id} with async_report body — starts S3 export job
- GET /api/table_report_data/{id}?job={job_id} — poll async job status (poll every 3s, timeout 600s)
- Async job returns S3 presigned URL with full CSV data

## Sync Architecture (in jungle-studio-mobile)
- Source: /home/avalon/apps/jungle-studio-mobile/src/sync/
- Files: mt-client.ts, sync-reservations.ts, sync-orders.ts, sync-memberships.ts, sync-daily-metrics.ts, sync-customers.ts, constants.ts, index.ts (orchestrator)
- Admin API: POST /api/admin/sync (full), POST /api/admin/sync/{type}, GET /api/admin/sync/status, GET /api/admin/sync/log
- Schema additions: 16 new columns + 3 new tables (daily_metrics, membership_daily, sync_log)
- Migration file: src/sync/schema-migration.sql

## Class Session Sync — NOT YET IMPLEMENTED (reverted)
- No dedicated MT report for class schedules
- Approach: derive class_sessions from reservation report data (option 2 — no new API endpoints needed)
- Was implemented then REVERTED (commit c13301e) because deduplicating existing CSV-imported
  class_sessions broke 77 reservation FK links (class_session_id set to NULL), which caused
  the dashboard to show zero data
- The code worked mechanically (20 sessions upserted, 0 errors) but the prerequisite DB cleanup was destructive
- If re-implementing: DO NOT delete/dedup existing class_sessions rows — instead add the unique index
  only after verifying no duplicates, or handle dupes by merging (UPDATE the keeper row, re-point FKs, then delete)
- Key design: de-dup by (class_date, class_time, location_id), UPSERT with COALESCE(NULLIF(...))
- class_sessions.id has NO sequence — use MAX(id)+1
- Column is `class_time` NOT `start_time` (was a bug in findClassSessionId, fixed in revert)

## Exxir Reference
- Repo: github.com/Exxir/exxirapp (collaborator access, DO NOT modify)
- MT sync code: server/jungle/ directory (mt_client.py, sync_reservations.py, sync_orders.py, sync_memberships.py)
- Exxir runs sync cron 2x/day (4am + noon), pulls 5 days of data each time
- Deployed on Railway with same MT credentials as env vars

## Sync Hardening (April 2026)
- Request throttle: 1.5s minimum between MT API calls (lastRequestTime tracking in MTClient.request())
- 429 rate limit detection: exponential backoff, 3 retries, reads retry-after header
- Async timeout increased from 300s to 600s (fetchReportAsync maxWait param)
- Silent truncation REMOVED: fetchReport() now throws if async fails after sync hit row cap (no fallback to 500-row truncated data)
- logSync() fixed: INSERT now includes sync_type column (uses report_name for both report_name and sync_type)

## Chunked Backfill System
- runBackfillSync(pool, startDate, endDate, delayBetweenChunksMs) in src/sync/index.ts
- Processes 1 day at a time to stay under 500-row sync cap
- Configurable delay between day chunks (default 5s for backfill, 3s for cron)
- Single MT client reused across all days (one auth token)
- After all days: runs membership snapshot + customer metrics refresh once
- Per-day: fetches reservations + orders, syncs both, aggregates daily metrics
- Errors per day don't stop the pipeline — logs failure and continues to next day

## Cron Schedule (system crontab on VPS)
- 3x daily: 8am CT (13:00 UTC), 2pm CT (19:00 UTC), 8pm CT (01:00 UTC)
- Script: /home/avalon/apps/jungle-studio-mobile/sync-cron.sh
- Calls POST http://localhost:4011/api/admin/sync/cron (pulls last 2 days + 14 future days, 3s chunk delay)
- Log: /home/avalon/apps/jungle-studio-mobile/sync-cron.log
- Note: April is CDT (UTC-5). Adjust cron times if DST changes.

## Spot Data Verification Workflow

After fixing spot extraction from MT Report 341, verify that `reservations.spot` and `reservations.spot_type` are actually populated:

### Check spot data exists for future dates
```sql
SELECT class_start_date, COUNT(*) as cnt
FROM reservations
WHERE spot IS NOT NULL AND spot != ''
GROUP BY class_start_date
ORDER BY class_start_date DESC
LIMIT 10;
```

### Check pick-a-spot reformer classes have taken spots
```sql
SELECT cs.id, cs.class_name, cs.layout, cs.class_time,
       COUNT(r.id) as res_count,
       COUNT(r.id) FILTER (WHERE r.spot IS NOT NULL AND r.spot ~ '^[0-9]+$') as spot_count
FROM class_sessions cs
LEFT JOIN reservations r ON r.class_session_id = cs.id
WHERE cs.class_date = 'YYYY-MM-DD'
  AND cs.class_name ILIKE '%reformer%'
GROUP BY cs.id
ORDER BY spot_count DESC;
```

### API verification
```bash
# List future classes
curl -s 'https://jungle-mobile.apps.poofc.com/api/schedule/classes?start_date=2026-05-05'

# Get taken spots for a specific reformer class
curl -s 'https://jungle-mobile.apps.poofc.com/api/schedule/classes/{id}/spots'
# Should return: {"taken": [1,2,3,...]}
```

### Understanding zero spots on non-reformer classes
A Mat Pilates class (e.g., id 26259) may show 46 total reservations but `spot_count: 0`. This is **correct** — reformers use numbered spots; mat/HIIT classes use first-come-first-serve with no assigned spot numbers. The `/spots` endpoint returning `{"taken": []}` for non-reformer classes is expected behavior.

Always verify with a **reformer class** (`class_name ILIKE '%reformer%'`, `layout` contains "Pick A Spot" or "Reformer") when testing spot data.

## Future Reservation Sync (fixed Apr 2026)
**Problem:** The original cron only pulled `today-2 → today`, so future reservations in MT never reached our DB. Future `class_sessions` created by on-demand hydration had `reservation_count: 0` because `hydrateFutureClassSessionsFromMt()` never synced the actual reservation rows.

**Fixes applied:**
1. **Extended cron window** in `server.ts` `/api/admin/sync/cron` from `today-2 → today` to `today-2 → today+14`. The backfill already processes 1 day at a time, so this stays safely within rate limits while pre-populating two weeks of bookings.
2. **Hydration now triggers reservation sync** — `server.ts` imports `syncReservations` from `src/sync/sync-reservations.ts` and calls it immediately after creating `class_sessions` inside `hydrateFutureClassSessionsFromMt()`. This ensures any on-demand schedule fetch also pulls the associated reservation rows.
3. **Spot data extraction** — MT Report 341 includes `Spot` and `Spot Type` columns. Updated `parseReservationRow()` in `src/sync/sync-reservations.ts` to extract them, and added `spot` + `spot_type` to the INSERT/ON CONFLICT DO UPDATE query so taken spots are persisted for pick-a-spot classes.

**Verification:** Trigger `/api/admin/sync/cron` manually and query the DB:
```sql
SELECT class_start_date, COUNT(*) 
FROM reservations 
WHERE class_start_date >= CURRENT_DATE 
GROUP BY class_start_date ORDER BY class_start_date;
```
Future dates should now show non-zero counts matching MT.

**Shared DB reminder:** Both jungle-studio dashboard (port 4010) and jungle-mobile (port 4011) connect to the same Postgres DB (`jungle_studio` on port 5433). Any schema change or dedup operation must be tested against both apps.

## Admin Endpoints (all on jungle-mobile port 4011)
- POST /api/admin/sync — full sync (original). Body: { days?, start_date?, end_date? }
- POST /api/admin/sync/reservations — reservations only
- POST /api/admin/sync/orders — orders only
- POST /api/admin/sync/memberships — membership snapshot only
- POST /api/admin/sync/backfill — chunked day-by-day backfill. Body: { start_date (required), end_date?, delay_seconds? }. Returns immediately, runs async.
- POST /api/admin/sync/cron — pulls last 2 days with 3s delays. Used by cron job.
- GET /api/admin/sync/log — sync history. Query: { limit?: 20 }

## Dashboard Sync Status (jungle-studio port 4010)
- GET /api/sync/last-updated — returns { lastUpdated: ISO timestamp } from sync_log table
- FilterBar.tsx dynamically fetches and displays last sync time (no longer hardcoded)

## Status
- Full sync pipeline works end-to-end including async S3 for >500 row reports
- Backfill from 3/13 to 4/3 completed successfully (22/22 days, 0 failures)
- ~1000-1250 reservations/day, ~150-270 orders/day, all processed correctly
- sync_log table now populated with all sync runs
- Orders sync fix (Apr 2026): replace per-order line items via DELETE+INSERT inside a SAVEPOINT; do NOT use `ON CONFLICT (order_id, product_name, COALESCE(variant_id, 0))` unless the DB actually has a matching unique index. Also null MT product/variant IDs that don't exist in local `products` / `product_variants` tables to avoid FK failures.
- Customer metrics fix (Apr 2026): update existing customer columns `total_checkins_all`, `first_checkin`, `last_checkin`, `membership_status` instead of non-existent `total_visits`, `first_visit_date`, `last_visit_date`, `has_membership`.
- Class session extraction still reverted for the cron/backfill sync pipeline (needs full safe re-implementation inside sync), BUT the mobile API now has an on-demand fallback: when `/api/schedule/classes` is requested for a future date missing from `class_sessions`, the server fetches the MT reservations report live for that date, groups rows into classes, upserts `class_sessions` + `class_session_instructors`, and then serves the schedule from DB. This fixes empty future dates in the mobile schedule without destructive dedup.
- Mobile schedule/class-detail instructor JSON must include `image_url` in the `json_build_object(...)` subquery. If omitted, uploaded instructor photos exist in `employees.image_url` but never render on the mobile schedule/detail cards.
- Check PM2 logs: pm2 logs jungle-mobile --lines 100

## Pitfalls
- Sync endpoint caps at 500 rows — async S3 pipeline needed for real data volumes
- ~1200 reservations per day across 3 studios, so even 1 day exceeds sync cap
- NEVER silently fall back to truncated 500-row data — this was the root cause of incomplete syncs. Always throw on async failure.
- For backfill over multiple days: use 1-day chunks with 5s delays. Single-day requests keep row counts manageable and avoid async pipeline entirely for orders (~150-270/day).
- Reuse single MTClient instance across chunked backfill to avoid re-authenticating per day
- Tokens are short-lived, need fresh password grant for each sync session
- Dashboard (port 4010, path /home/avalon/jungle-studio-dashboard/) needs DB_PORT=5433 env var
- Dashboard Postgres password can get out of sync — reset via: sudo docker exec jungle-studio-dashboard_db_1 psql -U jungle -d jungle_studio -c "ALTER USER jungle WITH PASSWORD 'jungle_dev';"
- class_sessions column is `class_time` NOT `start_time` — findClassSessionId had wrong column name
- DO NOT delete/deduplicate class_sessions rows without first re-linking reservations.class_session_id — 77 reservations got orphaned (set to NULL) when 5 duplicate class_sessions were deleted, breaking the dashboard display
- If re-implementing class session extraction from reservations: use INSERT ON CONFLICT DO UPDATE (not delete+reinsert), and add unique index on (class_date, class_time, location_id) AFTER cleaning dupes
- Dashboard nginx MUST have `location /api/ { auth_basic off; }` — without it, frontend fetch() silently gets 401 on every API call, showing all zeros
- class_sessions table column is `class_time` NOT `start_time` — findClassSessionId had this bug
- class_sessions.id has NO auto-increment sequence — IDs were imported from MT CSV, use MAX(id)+1
- reservations.id ALSO had no sequence — fixed Apr 2026: CREATE SEQUENCE reservations_id_seq START WITH {MAX(id)+1}; ALTER TABLE SET DEFAULT nextval(). Without this, sync INSERT fails with "null value in column id violates not-null constraint"
- CSV import created 5 duplicate class_sessions rows (same date/time/location) — NEVER delete them
  without first re-pointing all FK references (reservations.class_session_id, class_session_instructors)
  or the dashboard/mobile app will show zero data
- CRITICAL: any DB schema changes (dedup, index adds, FK changes) must be tested against BOTH apps:
  jungle-studio dashboard (port 4010) AND jungle-mobile (port 4011) — they share the same Postgres DB
- class_sessions derived from reservations won't have class_type_id or classroom_id — those came from CSV only
- MT reservation data has DUPLICATE rows per customer per class — same customer can have multiple
  reservation records with different statuses (e.g. 3x 'check in' + 2x 'standard cancel' for one person).
  Always use COUNT(DISTINCT customer_name) not COUNT(*) for reservation counts. For reservation lists,
  use SELECT DISTINCT ON (customer_name) ... ORDER BY customer_name, id DESC to get latest record per person.
- Active reservation statuses are: 'check in', 'pending'. All others are inactive: 'standard cancel',
  'penalty cancel', 'removed', 'penalty no show', 'graced no show', 'graced cancel', 'class cancelled'.
  Always filter to active statuses when showing current reservations/counts.
- customer_id is mostly NULL in reservations (84/88 null in tested sample) — use customer_name for dedup
- Express 302 redirects DON'T work with browser fetch() for API routes — fetch follows the redirect but
  can fail silently or return HTML. Always use direct query endpoints, not redirects, for frontend API calls.
- Locations page (/) now respects global date filter (fixed: commit 964272f). Backend accepts ?days= param,
  frontend uses useFilterStore + getDays pattern (same as Dashboard.tsx, Sales.tsx, InstructorPayroll.tsx).
- Dashboard frontend global filter pattern: useFilterStore from '../store/filters', getDays from '../lib/filters'.
  FilterBar.tsx reads dateRange/location from store. Pages pass ?days=${getDays(dateRange)} to API calls.
- Dashboard frontend build: `npx vite build` (tsc errors are non-blocking, vite build still succeeds).
- CRITICAL: jungle-studio nginx config MUST have `auth_basic off` for `/api/` routes — without this,
  ALL frontend fetch() calls get 401'd silently and every page shows zeros/empty. Fixed in
  /etc/nginx/sites-available/jungle-studio.apps.poofc.com (separate location /api/ block).
- Dashboard LocationDashboard.tsx (/locations/:id) needs these backend endpoints:
  GET /api/locations/:id/kpis?days=N (per-location KPIs)
  GET /api/locations/:id/upcoming-classes?date=YYYY-MM-DD (classes for a specific date, defaults to today)
  GET /api/locations/:id/classes/today?date=YYYY-MM-DD (direct query, NOT a redirect — same as upcoming-classes)
  LocationDashboard has day navigation (back/forward arrows + date label) using useState for selectedDate
- Dashboard ClassDetail.tsx (/classes/:id) needs these backend endpoints:
  GET /api/classes/:id (class session with instructor, location, reserved_count)
  GET /api/classes/:id/reservations (reservation list with status, booking_source, is_new_client)
  GET /api/classes/:id/waitlist (placeholder — no waitlist table yet)
  GET /api/classes/:id/attendance-log (placeholder)
  IMPORTANT: /api/classes/:id must be registered BEFORE sub-routes won't conflict in Express
  (Express path segments are distinct — `:id` only matches one segment, not `:id/reservations`)
- Jungle Studio brand colors (from mobile app, applied to dashboard):
  Gold accent: #A98F63, Beige: #EBE5DA, Charcoal sidebar: #424242,
  Warm header: #CEC9C0, Surface alt: #F5F0EB, Gold light: #C4A97D
  Dashboard CSS vars in src/index.css @theme block (Tailwind v4, no tailwind.config file)