Contact person: A web app with mobile app accompanying it

Phone:Show

Email:Show

Location: Fareham, United Kingdom

Budget: Recommended by industry experts

Time to start: As soon as possible

Project description:
"Overview

I'm looking for an experienced full-stack developer (preferably with expertise in Supabase, PostgreSQL, React/TypeScript, and real-time features) to complete and polish my cue sports (pool/snooker) tournament management application. The app helps organisers manage tournaments synced from [login to view URL], assign matches to tablet devices for shot clock timing, send push notifications to players, and provide live control dashboards. It's designed for multi-tenant use, where organizers have profiles, venues host tables, and tablets run sessions in real-time.

The backend is built on Supabase (PostgreSQL with RLS, Auth, Realtime), and the frontend uses React/TypeScript (inferred from component paths like src/components/dashboard/...). Core features are partially implemented via database migrations and some frontend hooks/components, but there are bugs (e.g., RLS policy issues preventing session creation), incomplete data flows (e.g., handling unallocated sessions), and missing polish (e.g., UI for admins, error handling).
The goal is to make the app fully functional, secure, scalable, and user-friendly. It should support real-world use cases like syncing CueScore data nightly, live scoring updates, and mobile tablet interfaces. Estimated scope: 2-4 weeks for an experienced dev, depending on testing.
Key Features and Components
The app revolves around organizers managing tournaments, with integration to CueScore for profiles, tournaments, and matches. Here's the breakdown:

User Roles and Authentication:

Organizers: Authenticated via Supabase Auth (email/password or OAuth). Linked to profiles with CueScore URLs/pages (multi-page support), credentials (username/password for scraping), and approval status.
Admins: Elevated users for impersonation and management (e.g., approve organizers).
Tablets: Device users (authenticated with access codes) for running shot clock sessions. Tablets are venue-scoped (e.g., "gpa-tbl001" for Gosport Pool Arena).
Players: Receive push notifications (via Expo/Firebase) for match assignments/tables.


Tournaments Management:

Sync/cache tournaments from CueScore (via cron jobs using pg_cron/pg_net to call Supabase edge functions for scraping).
Settings: Default shot time (e.g., 30s), extension time (30s), max extensions (1), venue, shortcode (auto-generated from name).
Status: Active/completed; linked to organizer.
Cache: tournament_cache for scraped JSONB data; organizer_tournament_counts for quick stats.


Venues and Tablets:

Venues: Organizer-owned (e.g., Gosport Pool Arena with 8 tables, Waterlooville Sports Bar with 12). Auto-seeded defaults on organizer approval.
Tablets: Reusable across tournaments at the same venue. Fields: table_name (e.g., "wsb-tbl005"), access_code, venue_id (FK), last_login for online status.
Assignment: Matches pushed to tablets via tablet_matches (JSONB for match data, status: pending/accepted/declined).


Shot Clock Sessions and Matches:

Sessions (table_sessions): Game state per table (time_left, extensions, player names, is_active). Created when match starts on tablet; includes overrides from tournament settings.
Matches: Real-time updates (match_updates) for scores, runouts, frame wins, lag_winner (A/B), via Realtime.
Controls: control_commands for actions like start/pause/reset (with JSONB payload for data).
Flow: Organizer pushes match (Tablets tab) → Tablet accepts/starts → Session created → Visible in Live Control tab.


Dashboards and UI:

Organizer Dashboard:

Tablets Tab: Assign matches to online tablets; clear pending/declined.
Controls Tab: Set global tournament settings (shot clock defaults, venue); auto-creates tables for venue.
Live Control Tab: Monitor active sessions (fetch from table_sessions); show table_number/name; control commands (e.g., via [login to view URL]).


Tablet Interface: Loads assigned match, runs shot clock ([login to view URL]), sends heartbeats ([login to view URL]), handles remote commands ([login to view URL]).
Notifications: push_subscriptions for players (tournament_id, player_name, JSONB data); history for auditing.


Integrations and Automation:

CueScore: Multi-page scraping (e.g., 'rgpick/tournaments'); credentials stored securely; daily refresh cron.
Real-time: Supabase Realtime on sessions, commands, matches, tablets for live updates.
Media: player_images for profiles.
Admin Tools: Impersonation views; separate admin_users table.



Tech Stack

Backend: Supabase (PostgreSQL with RLS policies, Auth, Realtime, Edge Functions for cron/scraping).
Frontend: React/TypeScript (components in src/components/... , hooks in src/hooks/...).
Database: Tables like organizers, tournaments, venues, tablets, table_sessions, control_commands, match_updates, tablet_matches, push_subscriptions, etc. (Full schema from migrations: UUID PKs, JSONB for flexible data, triggers for updated_at/shortcodes).
Other: Push notifications (Expo/Firebase), CueScore scraping (likely Node.js in edge functions).

Current State and Known Issues

What's Working:

Organizer profiles and CueScore syncing (caching, multi-pages).
Tournament creation/settings (shortcodes, venues auto-seed).
Match pushing to tablets (tablet_matches inserts).
Real-time features (e.g., heartbeats, commands).
Basic auth and RLS (but permissive in places).


Bugs/Gaps:

RLS on table_sessions: Inserts fail (403 violations) despite "Anyone can create" policies—needs [login to view URL]() checks or ownership subqueries.
Unallocated Sessions: When removing a match from a tablet, session persists but doesn't show as "Unallocated" in Live Control (add assigned_tablet_id column and logic).
Data Flows: Settings from Controls tab propagate to sessions, but overrides per match need better handling.
Testing Data: Hardcoded test matches/tournaments (e.g., CueScore IDs like 64398718); clean up and add seeds.
Security: Tighten permissive RLS (e.g., for inserts/updates); hash access_codes/credentials.
UI/UX: Live Control shows "No active sessions" if none; add reassign buttons, error toasts.
Scalability: Indexes/FKs are partial; add more for large tournaments.


Incomplete Features:

Full admin dashboard (impersonation UI).
Player image uploads/display.
Notification delivery/auditing.
Error handling (e.g., failed scrapes, offline tablets).
Mobile optimization for tablets (assuming web-based).



Requirements for the Freelancer

Scope of Work:

Fix bugs: RLS issues, session creation, unallocated status (implement proposed column/logic).
Complete features: Full data flows (e.g., propagate table names/settings reliably), admin tools, notifications.
Add Polish: UI improvements (e.g., responsive design, loading states), testing (unit/integration), documentation.
Optimize: Performance (indexes, partitioning), security (tighten RLS, encrypt sensitive data).
Deploy: Ensure works on Supabase hosting; add CI/CD if needed.


Deliverables:

Updated codebase (fork my repo or provide PRs).
Migration scripts for any schema changes.
Demo video/walkthrough.
Bug-free deployment to staging/prod.


Skills Needed:

Strong Supabase/PostgreSQL (RLS, Realtime, Functions).
React/TS frontend with hooks/state management (e.g., Zustand/Redux if used).
Experience with real-time apps, push notifications, web scraping.
Bonus: Cue sports knowledge or similar event management apps.


Budget/Timeline: $X-$Y (negotiable based on quote); 2-4 weeks. Provide your approach, timeline, and examples of similar projects." (client-provided description)