Phase 2 architecture proposal
For your review and approval before any service is connected.
Phase 1 uses illustrative local data, mock role switching and browser-only saves. No production database, real login, historical import, AI extraction, notifications or public deployment has been activated.
Proposed PostgreSQL structure
UUID primary keys, explicit foreign keys, timestamps and team-scoped access
Accounts & roles
auth.users: identity only. user_roles(id, user_id, team_id, role), unique(user_id, team_id, role); team_memberships maps coaches and players to a team. Roles are never stored on editable profiles.
Player profiles
teams(id, name); player_profiles(id, team_id, user_id, display_name, archived_at). No performance record is imported in Phase 1.
Practice versions
practices(id, team_id, created_by); practice_versions(id, practice_id, version, title, date, time, location, instructions, state, published_at), unique(practice_id, version). Published versions are immutable.
Assignments
practice_assignments(id, practice_version_id, player_id); assigned_drill_instances(id, assignment_id, protocol_version_id, sort_order, target, tolerance, instruction_snapshot). Each player has isolated copied instances.
Drills & protocols
drill_definitions(id, team_id, category, name, archived_at); drill_protocol_versions(id, drill_id, version, instructions, equipment, duration, input_method, scoring_spec, units, target_distances), unique(drill_id, version). Existing versions cannot be overwritten.
Results & metrics
result_submissions(id, assigned_instance_id, player_id, state, submitted_at, supersedes_id); metric_values(id, submission_id, metric_key, numeric_value, unit, protocol_version_id); shot_records(id, submission_id, shot_number, carry, ball_speed, launch, spin, lateral_miss). Numeric constraints and protocol checks enforced server-side.
Goals & baselines
goals(id, player_id, metric_key, protocol_version_id, target, tolerance, due_date, visibility); baselines(id, player_id, protocol_version_id, metric_key, approved_submission_id). Goals link only to compatible metrics.
Files & extraction reviews
screenshot_uploads(id, submission_id, owner_id, storage_path, mime_type, checksum); extraction_reviews(id, upload_id, proposed_values, reviewed_values, status, reviewed_by, reviewed_at). Unreviewed output never becomes an approved metric.
Coaching notes
coaching_notes(id, player_id, coach_id, body, visibility), where visibility is private_coach or shared_player. Shared notes are visible only to that player and authorized team coaches.
Audit & imports
audit_events(id, team_id, actor_id, action, entity_type, entity_id, timestamp, before, after), append-only; import_batches(id, team_id, source, checksum, mapping, validation_status, approved_by); import_rows(id, batch_id, source_row, raw_values, validated_values, errors). Imported rows remain staged until reviewed.
Authorization plan
Default deny
No anonymous table access. Grant only intended operations to authenticated users, then apply row-level security on every table.
Coach access
Server-validated coach role and team membership allow team practice management, reviews, analytics and coaching notes. A user cannot grant or edit their own role.
Player access
Players read their own published assignments, approved results, goals and explicitly shared notes. They write only their own result drafts and submissions for valid assignments. No teammate results, draft practices, or private coach notes are returned.
Private storage
Screenshots stay in private storage with owner/team checks and short-lived signed URLs. File paths are scoped to team, player and submission. Validate MIME type, size and ownership on upload and download.
Historical integrity
Published plans, protocol versions, baselines and approved submissions are immutable. Corrections append a superseding version and audit event; no destructive replacement.
Assumptions & approval gates
Prototype assumptions
Dates use Indianapolis time. Groups are illustrative. Names come from your brief; all scores, goals, assignments and notes are invented examples. Local results persist on this browser; goals and coaching notes last for this visit. Role switching is not a security boundary.
Analytics
Charts use fixed mock fixtures; saving a result does not recalculate the seeded trends. Production analytics must use only approved numeric results with identical protocol versions, units and distances. No AI-generated statistics.
Future extraction
AI may propose values only. A designated reviewer must confirm each value and its protocol before approval. No paid AI service is connected.
Historical import
Fall 2026 Google Sheets data will first enter a validation staging area. Review mappings, units, duplicates and protocol compatibility before approving an import batch. No import has occurred.
Next phase
Approve this relational structure and access plan before enabling Lovable Cloud. Real logins, private files, transactional publishing, durable history, authorized exports and notifications remain unimplemented.