Nexus_Mat/README_MODEL.md

Data Model & Database Schema

Overview

This document outlines the MySQL database schema for the NEXUS platform. It is designed for enterprise scalability, utilizing foreign keys, proper indexing, and efficient data types.

1. Prisma Schema (Recommended for Next.js)

If using Prisma ORM, place this in prisma/schema.prisma:

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

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

enum UserRole {
  USER
  ADMIN
  CREATOR
}

enum UserStatus {
  ACTIVE
  BANNED
  FLAGGED
}

enum MaterialType {
  CODE
  ASSET_ZIP
  VIDEO
}

model User {
  id        String   @id @default(uuid())
  username  String   @unique @db.VarChar(50)
  password  String   @db.VarChar(255) // Hashed
  avatarUrl String?  @db.VarChar(500)
  role      UserRole @default(USER)
  status    UserStatus @default(ACTIVE)
  createdAt DateTime @default(now())
  lastLogin DateTime @updatedAt

  materials Material[]
  comments  Comment[]
  favorites Favorite[]

  @@index([username])
}

model Material {
  id          String       @id @default(uuid())
  title       String       @db.VarChar(100)
  description String       @db.Text
  type        MaterialType
  contentUrl  String?      @db.VarChar(500)
  codeSnippet String?      @db.LongText
  language    String?      @db.VarChar(20)
  
  // Stats (Denormalized for performance, or calculated via aggregation)
  views       Int          @default(0)
  downloads   Int          @default(0)

  authorId    String
  author      User         @relation(fields: [authorId], references: [id], onDelete: Cascade)

  createdAt   DateTime     @default(now())
  updatedAt   DateTime     @updatedAt

  comments    Comment[]
  favorites   Favorite[]
  tags        Tag[]

  @@index([type])
  @@index([authorId])
}

model Comment {
  id         String   @id @default(uuid())
  content    String   @db.VarChar(1000)
  createdAt  DateTime @default(now())
  
  authorId   String
  author     User     @relation(fields: [authorId], references: [id])
  
  materialId String
  material   Material @relation(fields: [materialId], references: [id], onDelete: Cascade)

  @@index([materialId])
}

model Favorite {
  userId     String
  materialId String
  user       User     @relation(fields: [userId], references: [id])
  material   Material @relation(fields: [materialId], references: [id])

  @@id([userId, materialId]) // Composite Primary Key
}

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

2. Raw MySQL SQL Script

Use this if initializing the database manually via mysql -u root -p < schema.sql:

CREATE DATABASE IF NOT EXISTS nexus_db;
USE nexus_db;

-- Users Table
CREATE TABLE users (
    id CHAR(36) NOT NULL PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    role ENUM('USER', 'ADMIN', 'CREATOR') DEFAULT 'USER',
    status ENUM('ACTIVE', 'BANNED', 'FLAGGED') DEFAULT 'ACTIVE',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username)
) ENGINE=InnoDB;

-- Materials Table
CREATE TABLE materials (
    id CHAR(36) NOT NULL PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    type ENUM('CODE', 'ASSET_ZIP', 'VIDEO') NOT NULL,
    content_url VARCHAR(500),
    code_snippet LONGTEXT,
    language VARCHAR(20),
    views INT DEFAULT 0,
    downloads INT DEFAULT 0,
    author_id CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_type (type)
) ENGINE=InnoDB;

-- Comments Table
CREATE TABLE comments (
    id CHAR(36) NOT NULL PRIMARY KEY,
    content VARCHAR(1000) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    author_id CHAR(36) NOT NULL,
    material_id CHAR(36) NOT NULL,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Favorites (Join Table)
CREATE TABLE favorites (
    user_id CHAR(36) NOT NULL,
    material_id CHAR(36) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, material_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
) ENGINE=InnoDB;