City-County Sports: Local Sports Media Platform

Project Goals

• Local Sports Media Platform: Develop a platform to display articles and information related to sports in the Shenandoah Valley District.
• Advertisement Space: Implement functionality for displaying and managing advertisements.
• School-Specific Pages: Create individual pages for each school in the district, featuring relevant articles and scores.

Process

1. Design in Figma: Crafted a user-friendly layout, focusing on simplicity to cater to an older audience.
2. Next.JS Application: Developed a robust web application using Next.JS 13 App Router.
3. Database Management: Utilized Supabase and Prisma for efficient and effective database management.
4. Admin Console Development: Implemented an admin console for easy content management, including article uploads, score updates, and ad tracking.

Schema

generator client {
  provider = "prisma-client-js"
  previewFeatures = ["fullTextSearch", "fullTextIndex"]
}

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

model Match {
  id           Int      @id @default(autoincrement())
  homeSchool   School   @relation("HomeMatches", fields: [homeSchoolId], references: [id])
  homeSchoolId Int
  awaySchool   School   @relation("AwayMatches", fields: [awaySchoolId], references: [id])
  awaySchoolId Int
  homeScore    Int
  awayScore    Int
  matchDate    DateTime
  matchSlug    String?
  sport        Sport    @relation(fields: [sportId], references: [id])
  sportId      Int
}

model School {
  id            Int             @id @default(autoincrement())
  name          String          @unique
  homeMatches   Match[]         @relation("HomeMatches")
  awayMatches   Match[]         @relation("AwayMatches")
  ArticleSchool ArticleSchool[]
  AdSchool      AdSchool[]
}

model Sport {
  id      Int     @id @default(autoincrement())
  name    String  @unique
  matches Match[]
}

model Article {
  id                Int               @id @default(autoincrement())
  imageLink         String?
  imageAlt          String?
  status            ArticleStatus     @default(DRAFT)
  headline          String
  slug              String?           @unique
  authorId          Int
  author            Author            @relation(fields: [authorId], references: [id])
  publishDate       DateTime?
  lastUpdated       DateTime          @updatedAt
  content           String
  articleTags       ArticleTag[]
  articleCategories ArticleCategory[]
  articleSchools    ArticleSchool[]
  clickCount        Int               @default(0) 
}

enum ArticleStatus {
  DRAFT
  PUBLISHED
}

model Author {
  id        Int       @id @default(autoincrement())
  name      String
  imageLink String    @default("")
  imageAlt  String? // new alt text field
  articles  Article[]
}

model Category {
  id       Int               @id @default(autoincrement())
  name     String            @unique
  articles ArticleCategory[]
}

model Tag {
  id       Int          @id @default(autoincrement())
  name     String       @unique
  articles ArticleTag[]
}

// Pivot models for many-to-many relationships

model ArticleCategory {
  articleId  Int
  categoryId Int
  article    Article  @relation(fields: [articleId], references: [id])
  category   Category @relation(fields: [categoryId], references: [id])

  @@id([articleId, categoryId])
}

model ArticleTag {
  articleId Int
  tagId     Int
  article   Article @relation(fields: [articleId], references: [id])
  tag       Tag     @relation(fields: [tagId], references: [id])

  @@id([articleId, tagId])
}

model ArticleSchool {
  articleId Int
  schoolId  Int
  article   Article @relation(fields: [articleId], references: [id])
  school    School  @relation(fields: [schoolId], references: [id]) // Add this line

  @@id([articleId, schoolId])
}

model Ad {
  id           Int         @id @default(autoincrement())
  imageUrl     String      // Supabase storage location
  impressions  Int         @default(0)
  link         String      // outgoing link
  company      String
  schools      AdSchool[]  // Many-to-many relation through AdSchool model
  isActive     Boolean     @default(true)
  created_at   DateTime    @default(now())
  updated_at   DateTime    @updatedAt
  startDate    DateTime    // Ad display start date
  endDate      DateTime    // Ad display end date
  clicks       Int         @default(0)
}

model AdSchool {
  adId     Int
  schoolId Int
  ad       Ad     @relation(fields: [adId], references: [id])
  school   School @relation(fields: [schoolId], references: [id])

  @@id([adId, schoolId])
}

model LeaderboardAd {
  id          Int      @id @default(autoincrement())
  imageUrl    String // Supabase storage location
  impressions Int      @default(0)
  link        String // outgoing link
  company     String
  isActive    Boolean  @default(true)
  created_at  DateTime @default(now())
  updated_at  DateTime @updatedAt
  startDate   DateTime // Ad display start date
  endDate     DateTime // Ad display end date
  clicks      Int      @default(0)
}

Outcomes

• User-Friendly Design: Delivered a simple and intuitive website that is easy to navigate.
• Performance: Ensured fast and functional user experience.
• Custom Theming: Integrated school-specific theming, matching each school's colors.

Feedback

"The website looks good. I know you put in a lot of hard work and I do appreciate it. I was able to post recaps and scores with no difficulty."

- Steve Cox, Owner, City-County Sports

---