Add Supabase Integration

[JamesSingleton]

I decided that Supabase would be the DB of choice between the Web app and Desktop app.

To start off, here is what I am thinking table wise

-- Users Table
CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255),
    role VARCHAR(50), -- Assuming roles like 'coach', 'athlete', etc.
    date_of_birth DATE,
    contact_info VARCHAR(255)
);

-- Teams Table
CREATE TABLE Teams (
    team_id SERIAL PRIMARY KEY,
    team_name VARCHAR(255),
    coach_id INTEGER REFERENCES Users(user_id),
    description TEXT
);

-- Athletes_Teams Table (Many-to-Many Relationship)
CREATE TABLE Athletes_Teams (
    athlete_id INTEGER REFERENCES Users(user_id),
    team_id INTEGER REFERENCES Teams(team_id),
    join_date DATE,
    leave_date DATE,
    PRIMARY KEY (athlete_id, team_id)
);

-- Workouts Table
CREATE TABLE Workouts (
    workout_id SERIAL PRIMARY KEY,
    workout_name VARCHAR(255),
    coach_id INTEGER REFERENCES Users(user_id),
    team_id INTEGER REFERENCES Teams(team_id),
    date DATE,
    duration INTERVAL,
    description TEXT
);

-- Swim Meets Table
CREATE TABLE Swim_Meets (
    meet_id SERIAL PRIMARY KEY,
    meet_name VARCHAR(255),
    meet_date DATE,
    location VARCHAR(255),
    description TEXT
);

-- Attendance Table
CREATE TABLE Attendance (
    attendance_id SERIAL PRIMARY KEY,
    athlete_id INTEGER REFERENCES Users(user_id),
    meet_id INTEGER REFERENCES Swim_Meets(meet_id),
    attendance_status VARCHAR(50),
    date DATE,
    notes TEXT
);

-- Messages Table
CREATE TABLE Messages (
    message_id SERIAL PRIMARY KEY,
    sender_id INTEGER REFERENCES Users(user_id),
    recipient_id INTEGER REFERENCES Users(user_id),
    message_content TEXT,
    timestamp TIMESTAMP
);

-- Performance Metrics Table
CREATE TABLE Performance_Metrics (
    performance_id SERIAL PRIMARY KEY,
    athlete_id INTEGER REFERENCES Users(user_id),
    workout_id INTEGER REFERENCES Workouts(workout_id),
    metric_type VARCHAR(100),
    value FLOAT,
    date DATE,
    notes TEXT
);

-- Files Table
CREATE TABLE Files (
    file_id SERIAL PRIMARY KEY,
    file_name VARCHAR(255),
    file_type VARCHAR(50),
    file_size BIGINT,
    file_url TEXT,
    uploader_id INTEGER REFERENCES Users(user_id),
    upload_date TIMESTAMP,
    description TEXT
);

-- Swim Meet Events Table
CREATE TABLE Swim_Meet_Events (
    event_id SERIAL PRIMARY KEY,
    meet_id INTEGER REFERENCES Swim_Meets(meet_id),
    event_name VARCHAR(255),
    event_type VARCHAR(100), -- e.g., freestyle, breaststroke, relay, etc.
    event_distance INTEGER, -- distance
    distance_unit VARCHAR(10), -- meters or yards
    event_gender VARCHAR(10), -- gender category (optional)
    event_age_group VARCHAR(20) -- age group category (optional)
);

-- Swimmers_Events Table (Many-to-Many Relationship)
CREATE TABLE Swimmers_Events (
    swimmer_event_id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES Swim_Meet_Events(event_id),
    athlete_id INTEGER REFERENCES Users(user_id),
    swim_time INTERVAL -- the time taken by the swimmer in the event
);

-- Event Results Table
CREATE TABLE Event_Results (
    result_id SERIAL PRIMARY KEY,
    meet_id INTEGER REFERENCES Swim_Meets(meet_id),
    event_id INTEGER REFERENCES Swim_Meet_Events(event_id),
    athlete_id INTEGER REFERENCES Users(user_id),
    time_result INTERVAL, -- time taken by the swimmer in the event
    placement INTEGER,
    points_earned INTEGER
);

[Codehagen]

Attached is a schema for prisma:

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  user_id             Int                   @id @default(autoincrement())
  username            String
  email               String                @unique
  password            String
  role                String
  date_of_birth       DateTime
  contact_info        String?
  coached_teams       Team[]                @relation("TeamCoach")
  workouts            Workout[]
  athlete_teams       Athletes_Teams[]
  attendance          Attendance[]
  performance_metrics Performance_Metrics[]
  uploaded_files      Files[]
  event_results       Event_Results[]
  swimmer_events      Swimmers_Events[]
  sent_messages       Messages[]            @relation("MessageSender")
  received_messages   Messages[]            @relation("MessageRecipient")
}

model Team {
  team_id     Int              @id @default(autoincrement())
  team_name   String
  coach_id    Int
  description String?
  coach       User             @relation("TeamCoach", fields: [coach_id], references: [user_id])
  athletes    Athletes_Teams[]
  workouts    Workout[]
}

model Athletes_Teams {
  athlete_id Int
  team_id    Int
  join_date  DateTime?
  leave_date DateTime?
  athlete    User      @relation(fields: [athlete_id], references: [user_id])
  team       Team      @relation(fields: [team_id], references: [team_id])

  @@id([athlete_id, team_id])
}

model Workout {
  workout_id          Int                   @id @default(autoincrement())
  workout_name        String
  coach_id            Int
  team_id             Int?
  date                DateTime
  duration            String 
  description         String?
  coach               User                  @relation(fields: [coach_id], references: [user_id])
  team                Team?                 @relation(fields: [team_id], references: [team_id])
  performance_metrics Performance_Metrics[]
}

model Swim_Meet {
  meet_id       Int                @id @default(autoincrement())
  meet_name     String
  meet_date     DateTime
  location      String
  description   String?
  attendance    Attendance[]
  events        Swim_Meet_Events[]
  event_results Event_Results[]
}

model Attendance {
  attendance_id     Int       @id @default(autoincrement())
  athlete_id        Int
  meet_id           Int
  attendance_status String
  date              DateTime
  notes             String?
  athlete           User      @relation(fields: [athlete_id], references: [user_id])
  meet              Swim_Meet @relation(fields: [meet_id], references: [meet_id])
}

model Messages {
  message_id      Int       @id @default(autoincrement())
  sender_id       Int
  recipient_id    Int
  message_content String
  timestamp       DateTime  @default(now()) // Use `now()` to automatically set the message timestamp
  read_at         DateTime? // Nullable, set when the message is read
  deleted_at      DateTime? // Nullable, set when the message is 'deleted'
  sender          User      @relation("MessageSender", fields: [sender_id], references: [user_id])
  recipient       User      @relation("MessageRecipient", fields: [recipient_id], references: [user_id])

  @@index([sender_id])
  @@index([recipient_id])
}

model Performance_Metrics {
  performance_id Int      @id @default(autoincrement())
  athlete_id     Int
  workout_id     Int
  metric_type    String
  value          Float
  date           DateTime
  notes          String?
  athlete        User     @relation(fields: [athlete_id], references: [user_id])
  workout        Workout  @relation(fields: [workout_id], references: [workout_id])
}

model Files {
  file_id     Int      @id @default(autoincrement())
  file_name   String
  file_type   String
  file_size   BigInt
  file_url    String
  uploader_id Int
  upload_date DateTime
  description String?
  uploader    User     @relation(fields: [uploader_id], references: [user_id])
}

model Swim_Meet_Events {
  event_id        Int               @id @default(autoincrement())
  meet_id         Int
  event_name      String
  event_type      String
  event_distance  Int
  distance_unit   String
  event_gender    String?
  event_age_group String?
  meet            Swim_Meet         @relation(fields: [meet_id], references: [meet_id])
  swimmers        Swimmers_Events[]
  results         Event_Results[]
}

model Swimmers_Events {
  swimmer_event_id Int              @id @default(autoincrement())
  event_id         Int
  athlete_id       Int
  swim_time        String 
  event            Swim_Meet_Events @relation(fields: [event_id], references: [event_id])
  athlete          User             @relation(fields: [athlete_id], references: [user_id])
}

model Event_Results {
  result_id     Int              @id @default(autoincrement())
  meet_id       Int
  event_id      Int
  athlete_id    Int
  time_result   String 
  placement     Int
  points_earned Int
  meet          Swim_Meet        @relation(fields: [meet_id], references: [meet_id])
  event         Swim_Meet_Events @relation(fields: [event_id], references: [event_id])
  athlete       User             @relation(fields: [athlete_id], references: [user_id])
}

Under is a explanation of what is one to many etc:

1. User

• coached_teams: A one-to-many relationship from User to Team through coach_id. A user can coach multiple teams.
• workouts: A one-to-many relationship from User to Workout through coach_id. A user can create multiple workouts.
• athlete_teams: A many-to-many relationship between User and Team via Athletes_Teams. Represents the teams that a user (athlete) belongs to.
• attendance: A one-to-many relationship from User to Attendance. A user (athlete) can have multiple attendance records.
• performance_metrics: A one-to-many relationship from User to Performance_Metrics. A user (athlete) can have multiple performance metrics.
• uploaded_files: A one-to-many relationship from User to Files through uploader_id. A user can upload multiple files.
• event_results: A one-to-many relationship from User to Event_Results. A user (athlete) can have multiple results in different events.
• swimmer_events: A many-to-many relationship between User and Swim_Meet_Events via Swimmers_Events. Represents events in which a user (swimmer) participates.
• sent_messages and received_messages: Represent the one-to-many relationships for messages sent and received by a user.

2. Team

• coach: A many-to-one relationship from Team to User. Each team has one coach.
• athletes: The other side of the many-to-many relationship with User via Athletes_Teams.
• workouts: A one-to-many relationship from Team to Workout. A team can have multiple workouts scheduled.

3. Workout

• coach: A many-to-one relationship linking each workout to a specific coach (User).
• team: An optional many-to-one relationship linking each workout to a team. Some workouts might not be team-specific.
• performance_metrics: A one-to-many relationship linking workouts to their associated performance metrics.

4. Swim_Meet

• attendance: A one-to-many relationship from Swim_Meet to Attendance. Each meet can have multiple attendance records.
• events: A one-to-many relationship from Swim_Meet to Swim_Meet_Events. Each swim meet can include multiple events.
• event_results: A one-to-many relationship, indicating the results recorded at a specific meet.

5. Attendance

• athlete and meet: Many-to-one relationships linking each attendance record to a specific athlete and meet.

6. Messages

• sender and recipient: Many-to-one relationships from Messages to User, distinguishing between the sender and recipient of each message.

7. Performance_Metrics, Files, Event_Results

• These models link back to User indicating which user the records are associated with, and in the case of Performance_Metrics and Event_Results, they also link to specific workouts or events.

8. Swim_Meet_Events and Swimmers_Events

• Swim_Meet_Events: Links each event to a swim meet.
• Swimmers_Events: The many-to-many relationship through which athletes are connected to specific events in a swim meet.

9. Event_Results

• Links results to their respective meet, event, and athlete.

Could you look at this @JamesSingleton to see if the explanation for it is what you are thinking of ✨

[JamesSingleton]

@Codehagen I apologize that I am now just getting to this. For the Team, a swim team could have multiple coaches, for example, if you look at Arizona State University's swim team they have 1 Head Coach, 5 Assistant Coaches and 1 Diving Coach. Other than that, I think everything looks good. I guess I won't know until I start to wire everything together 😅