MOCK DATACC

Phase 2 architecture proposal

For your review and approval before any service is connected.

Proposal only · not implemented

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.