Database Schema Design for a Basic Social Media Software using Prisma

Recently I've been spending a great amount of time in the DB world. My most recent challenge has been dealing with time-based or temporal data. In several real-world scenarios, timing matters, particularly when dealing with transaction-based systems, such as fin-tech, payroll, or HR.

Creating a DB schema design for a social media product also presents these challenges, below is a suggested Prisma schema design concept for a basic social media software with given rules to the system.

Prisma is an open-source ORM for Node.js and TypeScript. It's a query builder that's tailored to your schema and is designed to be intuitive for both SQL veterans and developers new to databases. 

Prisma provides an abstraction layer that makes developers more productive compared to writing SQL. 

It integrates easily with modern technology stacks, databases, and frameworks. 

Prisma can be used as an alternative to writing plain SQL, or using another database access tool such as SQL query builders or ORMs. 

Prisma may also refer to a cloud security suite that provides four different services that use rule-based security policies and machine learning to protect cloud services. It can be used on Amazon Web Services (AWS), Azure, and Google Cloud Platforms.

Database Relationships and Rules

• Each User can have two Accounts (one-to-many relationship).
• Each User can create Posts. (one-to-many relationship).
• Each User can Like Posts two times (one-to-many relationship).
• Each Post can only be seen for two hours. (⌛️)
• A User can Comment on a Post (one-to-many relationship).
• If a User is Verified they can make a Comment.

model User {
  id       Int      @id @default(autoincrement())
  userName String
  verified Boolean  @default(false)
  accounts Account[]
  posts    Post[]
  likes    Like[]
  comments Comment[]
}

model Account {
  id           Int    @id @default(autoincrement())
  userId       Int
  accountName  String
  user         User   @relation(fields: [userId], references: [id])
}

model Post {
  id              Int       @id @default(autoincrement())
  userId          Int
  content         String
  timestamp       DateTime  @default(now())
  expiryTimestamp DateTime  @default(now() + interval("2") hour())
  user            User      @relation(fields: [userId], references: [id])
  likes           Like[]
  comments        Comment[]
}

model Like {
  id       Int      @id @default(autoincrement())
  userId   Int
  postId   Int
  timestamp DateTime @default(now())
  user     User     @relation(fields: [userId], references: [id])
  post     Post     @relation(fields: [postId], references: [id])
}

model Comment {
  id       Int      @id @default(autoincrement())
  userId   Int
  postId   Int
  content  String
  timestamp DateTime @default(now())
  user     User     @relation(fields: [userId], references: [id])
  post     Post     @relation(fields: [postId], references: [id])
}

// Optional: Table for Verified Users
model VerifiedUser {
  userId Int @id
  user   User @relation(fields: [userId], references: [id])
}        

This is a basic schema design that can be expanded and optimized based on the specific needs and complexity of the social media platform. Additionally, we need to consider the scalability and performance of the application as it grows.