Design Backend Database Schema for SkillCert

[Jeffrey0522]

Design Relational/Document Database Schema

• Name / Title: Design Backend Database Schema for SkillCert
• Complexity: Medium
• Task Type: Architecture / Database
• Description: The NextJS backend already uses TypeORM and PostgreSQL, with User (src/users/entities/user.entity.ts) and Course (src/courses/entities/course.entity.ts) entities defined. However, since we are moving data off-chain, we need to extend this existing schema. Specifically, the User entity is missing a walletAddress field to uniquely identify them on the Stellar blockchain, and Course needs mapping fields for the Soroban contract IDs.
• Technical Requirement: TypeORM, PostgreSQL, Database Design.
• Estimated time to deliver: 4 hours
• Acceptance criteria:
  • Complete schema definition document mapping the required additions to the existing User and Course entities.
  • A clear mapping of how on-chain wallet addresses connect to off-chain User entities.
• MUST DOs:
  • Explicitly define the walletAddress field as unique and nullable (for non-web3 users) in User.
  • Accommodate blockchain transaction hashes for tracking issued certificates.
  • Review existing relations (courses, enrollments, reviews) to ensure they don't conflict with off-chain needs.
• References:
  • Repo: skillcert-backend-server
  • Files:
    • src/users/entities/user.entity.ts
    • src/courses/entities/course.entity.ts

[Benjtalkshow]

Hi @Jeffrey0522

I am a Full Stack Developer specializing in Next.js, TypeScript, Node.js, Cairo and Rust . With over 120 contributions across projects in web3 and specifically stellar ecosystem, I’ve developed strong proficiency in delivering high-quality solutions and resolving complex issues within tight deadlines. My experience spans frontend, backend, smart contracts, and the optimization and maintenance of scalable codebases.

I can handle this task. Kindly assign.