Setup Prisma ORM Schema & PostgreSQL Initial Database Migrations

[Luluameh]

• Labels: backend, database, prisma

Description

We need to design and initialize the database schema for the off-chain portion of SkillSphere. This database will store expert profiles, categories, session logs, review metadata, and ledger transaction cache. We will use Prisma ORM and PostgreSQL.

Technical Scope & Steps

1. Install and initialize Prisma (npm i prisma @prisma/client and npx prisma init).
2. Design the schema in prisma/schema.prisma with the following models:
   • Expert: Public key, bio, categories, skills, hourly rate, rating, last heartbeat.
   • Session: Session ID (matching on-chain UUID), seeker address, expert address, status (active, paused, completed, refunded), escrow amount, start/end time.
   • Review: Rating, content, timestamp, linked to Seeker and Expert.
   • Transaction: Tx Hash, session ID, amount, type, timestamp.
3. Configure connection pooling using environmental variables.
4. Generate the initial SQL migration and verify database client code.

Acceptance Criteria

• Prisma schema compiles successfully with zero validation errors.
• Successful execution of npx prisma migrate dev initializes the PostgreSQL tables.
• Prisma client is exported as a singleton for use across services.

---