Server (AWS EC2):

🔹 Instance: t3.large (2 vCPU, 8GB RAM) — ~$60/month

🔹 Elastic IP: 3.131.5.193

🔹 OS: Ubuntu 22.04 / PHP 8.1 / MySQL / Nginx

🔹 SSL: Let's Encrypt auto-renew via certbot

🔹 ffmpeg: Installed for re-judge video merging

🔹 Web root: /var/www/starz/public/

🔹 Config: /var/www/starz/config.php

🔹 DB: turnt_arena (shared with TURNT Arena, starz_ prefix tables)

Storage — S3:

🔹 Bucket: starz-media-vault (us-east-2)

🔹 CloudFront CDN: d1udxlxsuq202.cloudfront.net (backup only — NAS is primary)

🔹 All viewing AND downloads go through NAS. Uploads go direct to S3. PDFs use presigned S3 URLs.

🔹 S3 egress: near zero — only PDFs, uploads, admin QA checks

Storage — NAS (Synology DS925+):

🔹 Hardware: 4×4TB WD Red Plus (SHR ~12TB usable) + 32GB RAM + 1TB WD Black NVMe

🔹 NIC: 2.5GbE (confirmed 2500Mb/s via ethtool)

🔹 Location: Home with T-Mobile 2Gbps fiber (static IP)

🔹 DSM: 7.3.2 — Web Station (PHP 8.1 enabled, nginx backend)

🔹 Public IP: 66.33.15.170 (T-Mobile static — confirmed not CGNAT)

🔹 Local IP: 192.168.1.134 (DHCP reservation)

🔹 Domain: files.turnthat.com → 66.33.15.170

🔹 SSL: Let's Encrypt on NAS (renewed April 3, 2026)

🔹 CloudSync: S3 → NAS (download only, syncs automatically)

🔹 Port forwards: 22, 80, 443, 5001 TCP → 192.168.1.134

🔹 Router: T-Mobile gateway at 192.168.1.1 (admin/a1b5c1ae, SSID CXNK01D2ADBE)

🔹 SSH: ssh Twisted701@66.33.15.170

🔹 QuickConnect: quickconnect.to/twisted701

🔹 Web Station service: "files" (native script language, PHP 8.1, nginx)

🔹 Web Station portal: 740033ca-0448-4243-9c33-4c7a8510d68b

🔹 Web Station PHP service: 28b01abb-817b-4d4d-9e83-79253fe9d701

🔹 CORS config: /usr/local/etc/nginx/conf.d/28b01abb-817b-4d4d-9e83-79253fe9d701/user.conf

🔹 ZIP script: /volume1/media/zip.php — builds ZIPs from local disk, streams directly to browser

🔹 Document root: /volume1/media

🔹 Timeouts: connection 60s, send 3600s, read 3600s (for large ZIP downloads)

🔹 Benchmark: EC2→NAS 155MB video in 1.4s = ~920 Mbps (limited by EC2 network, NAS can do 2.5Gbps)

🔹 Purpose: Serve ALL media viewing + downloads + ZIP downloads. Zero EC2 media bandwidth.

🔹 ⚠️ CORS note: When Web Station service changes, CORS user.conf path changes. Check /var/tmp/nginx/test/plugin_config/ for current service ID.

DNS (GoDaddy):

🔹 media.turnthat.com → 3.131.5.193 (EC2)

🔹 files.turnthat.com → 66.33.15.170 (NAS)

🔹 play.turnthat.com → 3.131.5.193 (TURNT Arena)

🔹 book.turnthat.com → 3.131.5.193

⚠️ Costs (after NAS migration):

🔹 EC2 t3.large: ~$60/month

🔹 S3 Storage (~863GB): ~$20/month

🔹 EC2 EBS: ~$15/month

🔹 S3 egress: ~$0-2/month (PDFs + admin QA only)

🔹 Total: ~$95-100/month (was $150+ with CloudFront + S3 egress)

Pre-Setup:

📋 Admin → Generate Emails → save Studio.txt

📋 Program by Competition for Printer → save schedule PDF

In Admin:

1️⃣ Import Studios → upload Studio.txt, set event label & year

2️⃣ Event Setup → upload schedule PDF → configure livestream → set media visibility

3️⃣ Set event status to Active (enables overlay auto-detect)

4️⃣ Upload Media → desktop app uploads to S3

5️⃣ QA Inspector → Daily Media Report + Critique Validator + Temp Cleanup

6️⃣ Notify Studios → sends login email via SendGrid

During Event:

🎤 Scorer Station records critiques → uploads to S3

📡 Backstage advances routines → Now Performing overlay updates

📺 Production staff manages livestream embed URLs

Post-Event:

🔍 Run QA report → fix misfiled videos, missing photos, timestamp mismatches

🎤 Custom Judge Critiques → upload video, generate judge link

📧 Resend studio emails individually from Studio Activity

starz-media-vault/

  _Deleted/ ← soft-deleted files (auto-cleaned after 7 days)

  2026/

    Des Moines/

      Studio Name(#1)/

        Media/

          0041/ ← routine photos folder

            IMG_101528.JPG

          1-041 - 07 March 2026 - 05-18-36 PM.mp4 ← video

        Critiques/

          Judge_1_RoutineName.mp3 ← audio

          Judge_1_RoutineName.mp4 ← merged video (480p, 15% original audio)

          scoring.pdf

          Custom/ ← custom judge critiques

            StudioNum/

              RoutineName.mp4 ← uploaded HD video

        _Deleted/

Daily Media Report:

🔹 Missing videos, missing photos, misfiled videos (wrong studio folder)

🔹 Timestamp mismatch detection (±60min window) with rename/keep options

🔹 Duplicate JPG detection across studios with overlap delete button

🔹 Wrong-date video detection (outside event dates ±1 day)

🔹 Photo/video preview, delete buttons per issue

🔹 Confirmed scratches from starz_scratches table (gold) vs likely scratched (gray)

🔹 Ignore system saved to audit log

🔹 "✅ This IS #X — Keep It Here" button for false positives

🔹 folderByRoutine prefers correct studio per schedule when duplicates exist

Critique Validator:

🔹 5-pass name matching + saved pairings

🔹 Skips confirmed scratches (loads from starz_scratches)

🔹 Per-routine list per judge with video-ready status

🔹 "⚠️ Upload video to enable rejudge" warnings

🔹 Generate rejudge links per judge with all missing routines

Video Checker:

🔹 Finds duplicate videos (original + -001 retry from ffmpeg)

🔹 Compare sizes, preview both, delete corrupt one

🔹 "Delete All Corrupt" bulk button

Temp File Cleanup:

🔹 Scan for .tmp files (including .tmp.mp4 from interrupted recordings)

🔹 Manual delete button + automatic hourly cron cleanup

Custom Judge Critique:

🔹 Select event → studio → type routine name → pick judge → upload HD video

🔹 Video uploads to S3: Year/EventLabel/Critiques/Custom/StudioNum/RoutineName.ext

🔹 Generates rejudge link (/rejudge/TOKEN)

🔹 Judge watches video, records critique → server merges via ffmpeg

🔹 Saves Judge_X_RoutineName.mp3 + .mp4 to studio's Critiques/ folder

🔹 Email notification via SendGrid when judge finishes

How It Works:

1️⃣ Admin runs Critique Validator → sees missing critiques per judge

2️⃣ Clicks "Generate Link for Judge X" → creates rejudge session in DB

3️⃣ Session contains: judge label, list of routines with videoKey references

4️⃣ Judge opens /rejudge/TOKEN on any device (phone, laptop, tablet)

Judge Flow:

🔹 Welcome screen → shows routine count, judge name

🔹 Mic Check → records 3 seconds, plays back for verification

🔹 Judging screen → video plays at FULL volume, mic records simultaneously

🔹 Review → listen to recording, redo or submit

🔹 Auto-advances to next routine

Server Processing (on submit):

🔹 Receives audio as WebM blob

🔹 Converts to MP3 via ffmpeg

🔹 Downloads video from S3 (uses precache — video downloaded while judge watches)

🔹 ffmpeg merges: original video (15% audio) + judge recording (100% audio) → 480p MP4

🔹 Uploads Judge_X_RoutineName.mp3 and .mp4 to Critiques/ folder on S3

🔹 Marks routine as completed in session

Custom Critique (single routine):

🔹 Admin uploads HD video via QA → Custom Judge Critique tool

🔹 Stored in starz_custom_critiques table (token, video key, studio, routine name)

🔹 Same judge flow — /rejudge/TOKEN works for both regular and custom

🔹 Info endpoint checks custom_critiques table first, then rejudge_sessions

🔹 Submit endpoint handles both — custom saves to studio's Critiques/ folder

🔹 Sends email via SendGrid when complete

Key Tables:

🔹 starz_rejudge_sessions: token, judge_label, routines (JSON), completed (JSON), status

🔹 starz_custom_critiques: token, event_id, studio_num, studio_name, routine_name, judge_num, video_s3_key, critique_mp3_key, critique_mp4_key, notify_email, status

Key Endpoints:

🔹 rejudge/TOKEN/info → session data (checks custom first, then regular)

🔹 rejudge/TOKEN/video-url → presigned S3 URL for video playback

🔹 rejudge/TOKEN/precache → downloads video to server cache while judge watches

🔹 rejudge/TOKEN/submit → receives audio, ffmpeg merge, upload to S3

Components:

🔹 /live/overlay.php — standalone overlay for vMix/OBS (1920×1080, transparent bg)

🔹 /live/embed.php — embeddable player with overlay for WordPress/external sites

🔹 /live page — overlay built into livestream viewer

Overlay Shows:

🔹 Now Performing: routine #, name, studio, division

🔹 Ahead/behind schedule timing

🔹 Up Next: next 2-3 routines

🔹 Custom text from backstage (e.g. "IMPROV")

🔹 Polls /api/public/now-performing every 3 seconds

Embed Page (embed.php):

🔹 Loads stream URL from event config (supports Rumble, YouTube)

🔹 Overlay sits below video (not on top — avoids blocking player controls)

🔹 Custom fullscreen button (top-right) → fullscreens video + overlay together

🔹 Hidden on mobile (mobile uses native Rumble fullscreen)

🔹 Auto-detects active event if no event_id specified

URLs:

🔹 /live/overlay.php?stage=A — vMix/OBS browser source

🔹 /live/embed.php?event_id=X&stage=A — WordPress embed

🔹 /live/embed.php?stage=A — auto-detect active event

Production Staff Login:

🔹 Username: production / Password: starz1 (desktop_role='production' in starz_staff)

🔹 Sees ONLY the livestream setup page — no admin, no QA, no files

🔹 Workflow: select event → paste Rumble/YouTube URL → save → copy embed code

🔹 Auto-converts Rumble page URLs and embed scripts to iframe URLs

🔹 WordPress instructions + copy button for iframe embed code

Event Status:

🔹 Events tab has status dropdown: Active / Upcoming / Completed / Cancelled

🔹 Active events show overlay links and embed code copy buttons

🔹 public/active-event endpoint returns the active event for auto-detect

Modes:

🔹 Upload Machine: uploads photos/videos/critiques from local folders to S3

🔹 Scorer Station: monitors judge critique recordings in real-time

🔹 Monitor: remote view of scorer status + scratch management

Upload Machine:

🔹 Select event → pick folder → scans files → S3 verification runs in BACKGROUND

🔹 UI is usable immediately — no blocking scan overlay

🔹 Status shows: "15,200 pending — verified 5,000/20,458"

🔹 Deleted files (via QA) return -1 from check-exists → skipped, not re-uploaded

🔹 Temp files (.tmp), files <5 seconds old, files with .tmp companion are skipped

🔹 Quarantine for small files: audio <500KB, video <2MB → play/upload/skip buttons

Scorer Station:

🔹 Maps 3 judge folders → monitors for new MP3/MP4 files every 10 seconds

🔹 Grid: Routine | Judge 1 | Judge 2 | Judge 3 (✅/❌/⚠️ per file type)

🔹 Smart alerts: 30-second timer before alerting missing judge files

🔹 Live activity ticker: color-coded per judge

🔹 Progress bar: "47 / 497 (499 scheduled, 2 scratched)"

🔹 Scratch system: dropdown from schedule, syncs to starz_scratches table

Build:

🔹 Electron app — npm start to run, npm run build for .exe

🔹 Source: src/index.html (UI), src/main.js (Node backend), src/preload.js (bridge)

/studio — Studio Portal:

🔹 Login with email/password → see events → media/critiques/codes

🔹 Photos grouped by routine with dancer names from schedule

🔹 Delete moves to _Deleted/ on S3, download via CloudFront/NAS

🔹 Access codes for parents with media/critiques/both filter

/gallery/CODE — Parent Access:

🔹 Same UI as studio but view/download only — no delete, no codes

🔹 Respects code expiration, use count, category filter

/live — Public Event Page:

🔹 QR code landing page: Photos, Videos, Livestream, Schedule, Find Routine, Notify Me

🔹 Livestream: single room goes straight to stream, multi-room shows picker

🔹 Only rooms with URLs configured are shown (empty URLs filtered out)

🔹 Now Performing overlay on livestream viewer

🔹 Stream iframe killed when navigating back (stops background audio)

🔹 Schedule PDF loaded on demand (presigned URL generated per-request)

🔹 Push notifications via web-push + SendGrid email

🔹 Browse S3 bucket with folder tree navigation

🔹 Search: search all S3 files by name (scans entire bucket, 30s timeout)

🔹 Move, rename, delete files (audit logged)

🔹 Bulk select + delete with progress

🔹 Delete moves to _Deleted/ (soft delete) — uploader skips deleted files

Hourly Cron (api/cron/cleanup?key=starz-cleanup-2026):

🔹 Deletes expired sessions (30-day lifetime)

🔹 Cleans rejudge video cache (/tmp/rejudge_cache/) older than 24 hours

🔹 Permanently deletes S3 _Deleted/ files older than 7 days

🔹 Deletes .tmp and .tmp.mp4 files from S3

🔹 Cleans old scorer status data (7+ days)

Server Crontab (root):

🔹 0 * * * * curl -s "https://media.turnthat.com/api/cron/cleanup?key=starz-cleanup-2026"

Email: SendGrid

🔹 API key in config.php (SMV_SENDGRID_KEY)

🔹 Used for: studio notifications, custom critique completion, push notification emails

🔹 From: [email protected] / Starz Dance Competition

Tables:

🔹 starz_events, starz_studios, starz_event_studios, starz_event_studio_numbers

🔹 starz_sessions, starz_staff, starz_staff_events

🔹 starz_access_codes, starz_schedule_entries

🔹 starz_file_audit, starz_media (legacy)

🔹 starz_rejudge_sessions, starz_custom_critiques

🔹 starz_scratches (event_id, routine_num, routine_name)

🔹 starz_scorer_status (event_id, status_json)

🔹 starz_stage_queue, starz_notify_*, starz_room_chat

New Endpoints (this session):

🔹 public/active-event — returns current active event (for overlay auto-detect)

🔹 public/schedule-url — generates presigned URL for single event's schedule PDF

🔹 admin/set-event-status — change event status (active/upcoming/completed/cancelled)

🔹 admin/create-custom-critique — upload video + create rejudge link

🔹 admin/resend-studio-welcome — resend login email to single studio (test_mode param)

🔹 admin/files/search — search entire S3 bucket by filename

🔹 admin/presign-upload — alias for admin/upload-url (web admin bulk upload)

🔹 cron/cleanup — maintenance endpoint (key=starz-cleanup-2026)

Overview:

🔹 4th button on /build home page — "📧 Email Studios"

🔹 Upload schedule PDF → pdfplumber parses all routines → groups by studio

🔹 Pulls studio emails from event-studio-map

🔹 Draft/Final version toggle, custom subject + message

🔹 Select all/none studios, send one at a time with progress bar

🔹 Test email sends to [email protected] with [TEST] prefix

🔹 Preview button opens popup with print-friendly styles

🔹 Session caching — remembers last parse on refresh

Per-Studio Email HTML:

🔹 Event header, studio name, routine count, version label

🔹 Routines grouped by Day/Room, sorted by time

🔹 Color-coded levels (S=gold, RS=green, NS=gray in email)

🔹 Award ceremony markers inline in schedule

🔹 TITLE badge (purple) for routines with + in category

🔹 Dancer names listed under each routine

🔹 Print styles: white bg, alternating gray rows, ink-friendly

Backend:

🔹 send_schedule_email action in /build backend (not /api/admin/)

🔹 SendGrid HTML email — no attachments, renders in inbox

🔹 Logs to starz_schedule_emails table

Category Parsing Fix:

🔹 COL_CATEGORY boundary set from "Grp" header right edge + 5px

🔹 Fixed "Contemporary" at x=268.6 being skipped by hard-coded x>270

🔹 cat_full field added as fallback — raw "Contemporary S 15-18" text

Overview:

🔹 Phone-based awards tracker for audience members during ceremonies

🔹 All data stored in localStorage — zero server load

🔹 Gold-bordered button on /live hub (only shows when schedule entries exist)

Selection Modes:

🔹 All Routines — track everything

🔹 Your Studio — pick studio from dropdown

🔹 Choose Routines — search/select specific routines

Two Tabs (matching award ceremony flow):

🔹 🏅 Adjudication — Judges Award, G/HG/P/DE buttons, Category placement X/total

🔹 🏆 High Points — routines grouped by HP pool in read order (youngest→oldest, Solo→Prod, NS→RS→S)

🔹 HP tab has level filter: All / New Starz / Rising Starz / Starz

Per-Routine Tracking:

🔹 Judges Award — themed modal prompt, "JA" badge on header

🔹 Adjudication — G/HG/P (+ DE for Starz only), badge on header

🔹 Category Placement — X / total (auto-filled from block count, level-separated)

🔹 High Point Placement — X / top-N (5/10/15/20 from /build thresholds: 24+=10, 49+=15, 75+=20)

🔹 Overall Placement — X / total (RS and S only, not NS)

UX:

🔹 Cards collapsed by default — tap to expand, auto-closes others

🔹 Badges update in real-time on collapsed headers

🔹 atSave() merges with existing data (doesn't wipe other tab's data)

🔹 Themed modals replace native prompt()/confirm()

🔹 Sorting: R# Order or By Room/Day. Filters: Day + Room

Share Results:

🔹 📤 Share button generates trophy-case HTML with medal chips

🔹 html2canvas renders to PNG image at 2x resolution

🔹 Mobile: native share sheet with image file (Instagram/text/email)

🔹 Desktop: downloads PNG file

🔹 Responsive: single column on phone, 2-col tablet, 3-col print/desktop

🔹 Light theme for share (white cards, dark header, color chips)

Level Colors (brand):

🔹 New Starz: pink #d94b7a

🔹 Rising Starz: yellow #f5d623

🔹 Starz: blue #2196F3

Level Column:

🔹 starz_schedule_entries.level — VARCHAR(10), stores NS/RS/S

🔹 pdf.js parser (admin) can't reliably extract level from text items

🔹 Solution: after pdf.js save, /build pdfplumber parser runs on same PDF

🔹 Pdfplumber extracts level reliably → admin/update-schedule-levels UPDATEs the DB

🔹 This happens automatically in setupUploadSchedule() step 4

Division String Format (from pdf.js):

🔹 "Solo 1 Contemporary S 15-18" or "D/T 2 Jazz T 12-14"

🔹 "Producti" — pdf.js splits "Production" across text items

🔹 All regex patterns must handle: Producti(?:on)?|Prod|Production

HP Key Format:

🔹 AGE-DIVISION-LEVEL (e.g. "T-Solo-RS", "S-D/T-S")

🔹 Category block key: division string + "|" + level (separates NS/RS/S)

What it does:

🔹 Shows per-studio balances for any competition — who owes, who paid, who has credit

🔹 Calculates fees from actual registration data (not line items) — routines × dancer count × pricing

🔹 Verified accurate against coding guy's site ($3,165 for 5-6-7-8 Dance Studio)

Pricing Formula (regionals, level=1):

🔹 Solo: $120/routine  ·  D/T: $150/routine (2-3 dancers)  ·  Groups: $50/dancer

🔹 Improv: $40/entry  ·  Title (Mr/Miss): $40/entry

🔹 Media Fee: $35 × unique dancers (per event)

🔹 Nationals (level=2): $130 solo, $170 D/T, $55/dancer groups, $50 improv/title — same media

Early Payment Discount:

🔹 Paid on/before Oct 1 prev year → 15% · 120+ days → 10% · 90-119 days → 5%

🔹 Applied to REGISTRATION only (not media fee)

🔹 Eligible portion capped at regTotal; excess covers media at full price

🔹 Our calc compared to coding guy's — flagged red if they differ (don't trust his blindly)

Features:

🔹 Sortable, searchable, filterable studio table with per-row fee/payment breakdown

🔹 📧 Remind / ⚠️ Dancers / ➕ Payment buttons per row

🔹 🔄 Payment reconciliation (finds missing Authorize.net payments)

🔹 🧪 Test email buttons before bulk sends

What it is:

🔹 Full daily clone of coding guy's SQL Server (starz1) into local MySQL (starz1_mirror)

🔹 118 tables, 5.1 million rows, 139 seconds to sync, 755MB on disk

🔹 3-layer protection: live mirror + S3 dumps + NAS CloudSync

🔹 If coding guy pulls the plug, you have every routine, dancer, payment, score already

Script Locations:

🔹 /var/www/starz/mssql_mirror.php — sync script

🔹 /var/log/mssql_mirror.log — run log

🔹 s3://starz-media-vault/backups/starz1/latest.sql.gz (67MB compressed)

🔹 Cron: 0 2 * * * (daily at 2am)

Table Naming:

🔹 dbo.Payments_Made → dbo__Payments_Made (schemas flattened with __)

🔹 build.Program → build__Program · auth.Transactions → auth__Transactions

Check Commands (SSH into EC2):

Verified Working:

🔹 First sync April 14, 2026: 118/118 tables, 5,123,077 rows, 0 errors

🔹 Biggest tables: build.dance_dancers_routines (1.26M), build.dance_dancers (921K), build.dance_teachers (649K)

🎯 THE VISION

Build a single unified, multi-tenant, configurable competition management platform that replaces DanceCompetition.exe, the coding guy's registration site, and every janky spreadsheet in the pipeline. Copyright it. Eventually license it to other competition companies (dance, horse shows, gymnastics, baseball, swimming — any event with categories, judges, scoring, scheduling, awards).

Core principle: EVERYTHING IS CONFIGURABLE

🔹 Terminology: "routines" → user sets to "acts", "horses", "entries", "bouts"

🔹 Categories: user creates their own (Ballet, Jazz, Hip Hop → or Western, English, Dressage)

🔹 Pricing: per-category rules, early-payment discounts, bundles — all user-defined

🔹 Group sizes: user defines (Solo=1, Duet=2, etc)

🔹 Judge count: 2, 3, 5, 10 — user picks

🔹 Scoring ranges: user defines (0-100, 0-10, tier-based)

🔹 Age groups: user defines (Mini, Pre-teen, Teen, Adult — or 10U, 12U, 14U)

🔹 Reports: USER BUILDS THEM. Pick fields, sort order, grouping, filters, name it, save it, reuse it

❌ THE PAIN WE'RE SOLVING

🔹 20+ hours/week of manual corrections (Dance Unlimited style emails)

🔹 Schedule breaks when categories change (lyrical → jazz = renumber nightmare)

🔹 Download → edit offline → re-import → duplicates and lost assignments

🔹 Studios can't self-correct; they email you spelling errors

🔹 Three databases (Access local, SQL Express judges, MSSQL online) that fall out of sync

🔹 14-year-old VB.NET code with empty Differences button (literally unfinished)

🔹 SQL injection everywhere in the current code

🔹 Dependent on coding guy who forgets his own code

📋 THE 5-PHASE WORKFLOW (What We Build Around)

Phase 1: REGISTRATION OPEN (July/Aug → 4 weeks out)

🔹 Studios reserve routines, add placeholders (no dancers yet)

🔹 Admin sets max per event (350, 450, etc) — system enforces hard caps

🔹 Admin can whitelist specific studios over the cap

🔹 State: OPEN

Phase 2: REGISTRATION CLOSED / BUILDING (4 weeks out)

🔹 Registration locks — no studio changes allowed

🔹 Admin builds schedule: sort by day, time, category, keep categories together

🔹 NO NUMBERING YET (this is critical)

🔹 State: LOCKED

Phase 3: CORRECTIONS WINDOW (4 → 2 weeks out)

🔹 Studios submit corrections via portal: name spellings, dancer swaps, category fixes

🔹 Admin approves/rejects each one in a queue

🔹 Approved changes update schedule, still no numbers

🔹 System distinguishes cosmetic (always OK) vs schedule-affecting (warning)

🔹 State: CORRECTIONS_OPEN

Phase 4: FIRST DRAFT PUBLISHED (2 weeks out)

🔹 Schedule numbered, sent individually to each studio

🔹 Wait 3-7 days for studio review

🔹 Studios submit final corrections via portal

🔹 Admin approves/rejects

🔹 State: DRAFT_PUBLISHED

Phase 5: FINALIZED (1 week out → event)

🔹 Final schedule published

🔹 Any late changes get SQUEEZE-IN numbering (334A, 334B, 334C)

🔹 Don't renumber everything — announcer can handle 334 → 334A → 335

🔹 State: FINALIZED → IN_PROGRESS (event day) → COMPLETE

✅ THE CORRECTION APPROVAL QUEUE

Every studio change request becomes a record:

🔹 Admin sees queue of pending requests

🔹 Batch approve/reject

🔹 Every change logged forever (legal receipt if studio disputes)

🔹 Changes classified:

   - COSMETIC (name spelling, dancer add/remove) — auto-approve option

   - CATEGORY CHANGE (lyrical→jazz) — blocked after LOCKED state

   - AGE/LEVEL CHANGE (Pre-teen→Mini) — warning, requires admin approval

   - SQUEEZE-IN (new routine after Final) — gets letter suffix (334A)

🛠️ THE 6-MONTH BUILD PLAN (Off-season 2026)

Month 1-2 (June-July 2026): Foundation

🔹 Decompile & document DanceCompetition.exe (DONE — source saved)

🔹 Design new unified MySQL schema (companies, events, studios, routines, dancers, corrections, scores, etc)

🔹 Build auth system (studios log in — migrate existing credentials from dbo__dance_studios)

🔹 Build admin dashboard at portal.starzdancecomp.com (read-only viewer first)

Month 3-4 (Aug-Sep 2026): Portal & Corrections

🔹 Studio-facing portal: see events, routines, balances, submit corrections

🔹 Admin correction queue with approval/rejection

🔹 State machine per event (OPEN → LOCKED → CORRECTIONS_OPEN → etc)

🔹 Reservation system with caps + whitelist (your $5/routine pre-reserve idea)

Month 5 (Oct 2026): Schedule Builder

🔹 Drag-and-drop routines between days/rooms

🔹 Auto-detect quick changes (highlight, don't just show on button click like old code)

🔹 Sort by division/category/age/level

🔹 Insert breaks, recalc times automatically

🔹 Squeeze-in support (334A, 334B numbering)

Month 6 (Nov 2026): Judge App + Reports

🔹 Electron desktop app for judges (offline-capable, LAN-syncable)

🔹 SQLite local cache, syncs to EC2 MySQL when online

🔹 USER-DEFINED REPORT BUILDER: pick fields, order, grouping, name it, save, reuse

🔹 Default report templates: Program, Scoring, High Points, Awards, Standout Dancer, Title Winner

Parallel Run (Dec 2026 - Dec 2027)

🔹 Run NEW system alongside old for 2027 season

🔹 Use both, compare outputs, find bugs, fix

🔹 Keep coding guy's system as fallback until full confidence

Industry Launch (Jan 2028)

🔹 Approach 2-3 friendly competitor companies as beta testers

🔹 Refine, polish, fix onboarding

🔹 Public launch mid-2028

🏗️ THE TECH STACK

🔹 Backend: PHP on EC2 (matches existing stack), REST API + WebSocket server

🔹 Database: MySQL (new unified starz database, multi-tenant with company_id)

🔹 Studio Portal: React SPA at portal.starzdancecomp.com

🔹 Admin Dashboard: React SPA at admin.starzdancecomp.com

🔹 Judge Desktop App: Electron wrapper around React components + SQLite local cache

🔹 Mobile: Capacitor wrapper around studio portal → App Store + Play Store

🔹 File storage: S3 (already have starz-media-vault)

🔹 Payments: Stripe (tokenized, stored methods, auto-charge for discount tiers)

🔹 Email: SendGrid (already configured)

🔹 Real-time updates: WebSocket (admin sees studio corrections flow in live)

🎨 MULTI-TENANT FROM DAY 1

🔹 Every table has company_id — Starz is company_id = 1, others are 2, 3, 4...

🔹 Subdomain routing: starz.dancecomppro.com, abc.dancecomppro.com

🔹 White-label branding per company (logo, colors, email from address)

🔹 Self-serve onboarding with CSV import wizard

🔹 Per-company configuration: terminology, pricing, categories, age groups, judges

💰 BUSINESS MODEL (Industry SaaS)

🔹 Target: ~500+ regional dance competition companies in US

🔹 Plus: horse shows, gymnastics, figure skating, martial arts, cheer, baseball tournaments — anything with categories/judges/scoring/scheduling/awards

🔹 Pricing: $500-$2,000/month per company (scales with event count)

🔹 100 companies × $5k/mo = $6M/year ARR

🔹 Exit strategy: sell to industry consolidator (Dance Informa, DanceWeek) in 5-7 years

🔹 Alternative: keep as $1-5M/yr side business with small team

🎁 FEATURES THAT MAKE COMPETITORS CRY

🔹 Studio self-service corrections with approval workflow (NOBODY has this)

🔹 Hard caps + whitelisting per event

🔹 Squeeze-in numbering (334A, 334B) — real pro move

🔹 Live parent notifications: "Your daughter is up in 5 min, Stage 2"

🔹 Photos, videos, awards all in one portal (most use 3 vendors)

🔹 AI correction suggestions based on learned patterns

🔹 Reservation system with early bird pricing

🔹 Fully configurable terminology (works for any sport/activity)

🔹 User-built report engine (not locked to predefined reports)

⚠️ CRITICAL SUCCESS FACTORS

🔹 Build for Starz first, industry second — battle-test at your events

🔹 Parallel run for full 2027 season before trusting solo

🔹 Multi-tenant architecture from day 1 (impossible to bolt on later)

🔹 Copyright/trademark the name before public launch

🔹 Document everything (code, workflows, pricing logic) for future sale

🔹 LLC the product separately from Turn That (or Starz) for clean sale path

🔹 Judge app reliability is PARAMOUNT — zero downtime at events

📚 SOURCE CODE ARCHIVE

🔹 DanceCompetition v5.2.3.4 decompiled (ILSpy, April 14, 2026)

🔹 121,083 lines of VB.NET (decompiled to C#)

🔹 Key files: Functions.cs (22k lines), Reporting.cs (18k), ManageCompetition.cs (10k), JudgeCompetition.cs (7.5k), Import_Data.cs (5.9k)

🔹 Three DBs discovered: MSSQL (starz1), MySQL (starzcompinfo, starzexport, ielaa)

🔹 Owner: MWSDC Corporation / Tyler Tomesh (paid to develop over 14 years)

🔹 Legal right to decompile/modify/rebuild confirmed

🎯 NEXT STEPS (Resume Here)

1️⃣ Finish spring 2026 season on current system (5-6 events left)

2️⃣ June 2026: Start schema design session for new unified database

3️⃣ Investigate starzcompinfo and ielaa GoDaddy MySQL databases (unknown contents)

4️⃣ Register product trademark (suggested names: StageCast, CompPilot, DanceOps, EventRunner)

5️⃣ Form separate LLC for the SaaS product (keep clean from Starz operations)

6️⃣ Begin building portal.starzdancecomp.com as read-only mirror viewer

📥 New Tab: Recent Uploads

🔹 Where: Admin → 📥 Recent Uploads tab (between File Manager and QA Inspector)

🔹 Purpose: Find misfiled uploads. Drag a box around them. Mass delete or move.

🔹 Use case: Photographer downloaded Sioux City photos into Sioux Falls (~1000 misfiled JPGs). Or videos from Monticello uploaded into Sioux City studio folders.

🔹 How: Pick event → time window (24h/3d/7d/14d/30d/all) → file type filter → 🔍 Scan

🔹 Shows only files modified in window, grouped by Studio/Folder/Day, sortable by date/path/size

🔹 Selection: click row to toggle, Shift+Click for range, drag empty space to box-select rows (rubber band), Ctrl/Cmd+drag = additive

🔹 Bulk ops: Delete (soft-deletes to /_Deleted/, 7-day auto-purge) or Move To... (reuses File Manager move dialog)

🔹 Parallelized 8 deletes at a time. Status bar shows live progress.

🔹 Backend: walks S3 via recursive admin/files/browse calls (admin/files/list-all returns no timestamps). 8 parallel workers, prunes _Deleted/ branches. Logs scan summary to console as window._ruDebug.

🔹 Date parsing: uses ruParseDate() helper — converts S3 PHP format ('2026-05-01 20:12:40') to ISO before Date.parse(), prevents browser timezone drift bug.

⏰ TIMEZONE BUG FIXED — Top Shot + Critique Expiry + Access Codes

⚠️ ROOT CAUSE: /var/www/starz/config.php:47 has `date_default_timezone_set('America/Chicago')`

🔹 MySQL stores datetime columns in UTC (because forms send ISO/UTC strings)

🔹 PHP-FPM reads with Chicago tz → strtotime() interprets stored '20:00:00' as 8 PM CDT = 1 AM UTC tomorrow → comparisons against time() (always UTC) fail

🔹 Symptom: Set Top Shot start to 3 PM CDT, saved correctly as 20:00 UTC, but is_open returned 0 because PHP read 20:00 as Chicago time

🔹 Detection method: php-r CLI test showed tz=UTC (no config.php loaded), but FPM-served endpoint showed end_ts off by exactly 18000 seconds (5 hours = CDT offset)

FIX PATTERN — when reading datetime FROM DB, force UTC interpretation:

$ts = (new DateTime($cfg->start_at, new DateTimeZone("UTC")))->getTimestamp();

Patches deployed (May 1, 2026):

🔹 /var/www/starz/public/index.php line 4803-4804: public/topshot/config — start_ts/end_ts UTC parse

🔹 /var/www/starz/public/index.php line 4874-4875: public/topshot/charge — start/end check UTC parse (CRITICAL: was rejecting customers at payment)

🔹 /var/www/starz/public/index.php line 1941: access code expires_at check (gallery code expiry)

🔹 /var/www/starz/public/critiques/download0.php + download2.php: all download_expires checks (5 lines each file)

🔹 Backups created: .bak-topshot-tz-*, .bak-charge-tz-*, .bak-tz-* in same directories

🔹 Verify count: grep -rn 'DateTimeZone("UTC")' /var/www/starz/public/ should show 11+ hits

🚨 STILL VULNERABLE — strtotime() on DB datetime is broken everywhere this pattern remains. Audit before each new feature:

grep -rn "strtotime(\$" /var/www/starz/public/ --include="*.php"

🏆 Top Shot Setup — Required Steps for Each Event

🔹 Where: Admin → Events tab → click event → scroll to Top Shot section

🔹 Required fields: Enable, Public Visible (uncheck for testing only), Start datetime, End datetime, Price (cents), Prize Label, S3 Entries Prefix

🔹 Times in form are LOCAL (Central) — form converts to UTC on save automatically

🔹 Test mode: uncheck Public Visible, browse /live?topshot=1, button only appears with that query param

🔹 Top Shot button location: inside the photo lightbox — only visible after tapping a photo, NOT on main /live page

🔹 Hidden when: photo is already a TopShot entry copy (in /TopShot/ folder), is_open is 0, public_visible is 0 without ?topshot=1, photo already entered

🔹 Public flow: tap photo → lightbox → Top Shot button → buyer info → Square card or Apple Pay → photo copied to s3_entries_prefix folder, entry recorded as paid+copied

🔹 Tables: starz_topshot_config (one row per event), starz_topshot_entries (paid/copied/refunded), starz_topshot_corrections (buyer notes about wrong dancer info)

🔹 Manual entry: Admin → Events → Top Shot section → Add Manual Entries panel (for cash sales / comps)

🔹 Refund: admin/topshot/refund endpoint — refunds Square charge AND deletes entry copy from S3

🐛 Boot Sequence Race Fix

🔹 admin/index.php boot order changed: loadDashboard().then(() => ruInit if recent tab active)

🔹 Before fix: reload while on Recent Uploads tab → events dropdown empty (because restoreTab() ran ruInit before loadDashboard finished populating window._events)

🔹 After fix: ruInit always rebuilds dropdown, falls back to admin/dashboard fetch if window._events empty

🔹 Same race fix pattern useful elsewhere — any tab whose init depends on dashboard data should re-fire after loadDashboard resolves

🛡️ Backstage Notification Guard — Completed Events Locked

⚠️ INCIDENT: May 2, 2026 ~6:52 AM CDT — Employee accidentally typed "GOOD MORNING!" into Monticello backstage queue (event ended April 26, status=completed) instead of Sioux City. Push notification fired to all subscribed parents from Monticello. Tammy Christopherson reported it.

🔹 Root cause: Backstage app event picker remembered last-used event (Monticello). Operator opened backstage in the morning, typed habitual greeting, hit Send. Backend had no state guard — accepted writes on any event regardless of status.

🔹 Diagnostic SQL used to trace:

SELECT id, event_id, stage, status, custom_text, started_at FROM starz_stage_queue WHERE custom_text LIKE '%GOOD MORNING%' ORDER BY id DESC LIMIT 20;

🔹 Showed event_id=14 (Monticello, completed) had a row created at 11:52 UTC today — confirmed mistake, not data leak.

FIX: Added state-check guard to backstage write endpoints that broadcast notifications:

$ev_status = DB::fetch('SELECT status FROM starz_events WHERE id = ?', [$event_id]);
if ($ev_status && in_array($ev_status->status, ['completed','cancelled','draft']))
    json_error('Event is '.$ev_status->status.' — queue is locked');

🔹 Patched in /var/www/starz/public/index.php:

🔹 Line 3219: backstage/queue-add (custom text + routine adds)

🔹 Line 3246: backstage/queue-next (advance queue — this is what fires the broadcast loop)

🔹 Backup: /var/www/starz/public/index.php.bak-completed-event-guard-*

🔹 Verify: grep -c "queue is locked" /var/www/starz/public/index.php → should return 2

🔹 NOT patched (intentionally): backstage/queue-remove and backstage/queue-reorder. Different signature (no event_id in POST), don't trigger notifications, low harm. Skipped to avoid unnecessary DB lookups.

🧪 How to test the guard manually

// In admin browser console after login:
const fd = new FormData();
fd.append('token', localStorage.getItem('smv_admin_token'));
fd.append('event_id', '14'); // any completed event ID
fd.append('stage', 'Room A');
fd.append('custom_text', 'TEST GUARD');
const r = await fetch('/api/backstage/queue-add', {method:'POST', body:fd}).then(r=>r.json());
console.log(r); // should be {success:false, error:"Event is completed — queue is locked"}

📦 Bulk Studio Removal (Studios Map)

🔹 Where: Admin → Studios tab → Studio Map → pick event

🔹 Use case: Nationals Wisconsin Dells imported with wrong studio number set (49 studios mismapped). Need to wipe and re-import.

🔹 New UI: Per-row checkbox + header select-all checkbox + bulk action bar (X selected · Remove Selected · 💥 Remove ALL N studios)

🔹 Behavior: Parallelized 8 deletes at a time. Live progress bar. Removed rows fade and strike through.

🔹 Bug fix bundled: single-row removeStudioMapping no longer calls loadDashboard() on success — only refreshes the studio map. This eliminates the "second click does nothing" bug where dashboard refetch (~800ms) was clobbering the events dropdown mid-click.

🔹 Endpoint: admin/remove-studio-mapping (existing, unchanged) — accepts event_id + studio_id, deletes from starz_event_studio_numbers and starz_event_studios.

🔹 Already-uploaded S3 files in studio folders are NOT deleted — they remain accessible via File Manager. Only the number→studio mapping and event link are cleared.

🔍 Pattern Audit — State Guards on Time-Bounded Writes

🔹 The notification leak revealed a broader pattern: operations that broadcast to subscribers must check parent resource state.

🔹 Audit candidates for similar guards:

🔹 admin/notify-studios — sends welcome emails. Should refuse if event status='completed'.

🔹 admin/schedule-email-studio — sends schedule emails. Same risk.

🔹 admin/scorer-push (and the desktop scorer station) — silent if event over, but check.

🔹 critique notify_at queue — should skip if event is far enough in the past.

🔹 General principle: any write that triggers email/push to humans should validate the parent event/resource is in an active state, not just that the foreign key resolves.