Database Migration Guide

Status: Ready for First Migration

The schema has been validated and is ready for initial database migration.

Schema Validation Results

✅ Prisma schema loaded from prisma/schema.prisma
✅ Schema validation passed
✅ No syntax errors
✅ All relationships valid
✅ All constraints valid
✅ Ready for deployment

Pre-Migration Checklist

Before creating the first migration, ensure:

Schema is validated and error-free
All 9 required models are defined
All 7 required enums are defined
Foreign keys and relationships are properly configured
Cascade delete strategies are implemented
Unique and composite constraints are in place
Strategic indices are defined
Team has reviewed the schema (see SCHEMA_DOCUMENTATION.md)
Database connection is configured (.env file exists)

Migration Steps

Step 1: Verify Database Connection

Ensure your .env file has a valid DATABASE_URL:

# Example for PostgreSQL (as configured in schema.prisma)
DATABASE_URL="postgresql://username:password@localhost:5432/stellaraid_dev"

Step 2: Create Initial Migration

Generate the first migration file:

cd /workspaces/stellarAid-api
npx prisma migrate dev --name init

This command will:

Create a migration file in prisma/migrations/
Apply the migration to your development database
Generate/update the Prisma Client

Step 3: Verify Migration

Check that the migration was successful:

npx prisma db push

Or view the database schema:

npx prisma studio  # Opens browser UI to inspect data

Step 4: Generate TypeScript Types

Ensure TypeScript types are generated from the schema:

npx prisma generate

This creates node_modules/@prisma/client/ with full type safety for your queries.

Production Deployment

For production environments:

1. Database Backup

# PostgreSQL backup
pg_dump stellaraid_prod > backup_$(date +%Y%m%d_%H%M%S).sql

2. Apply Migration

# Use Prisma migrations
npx prisma migrate deploy

# Or manually with SQL files in prisma/migrations/
psql -U username -d stellaraid_prod < prisma/migrations/*/migration.sql

3. Verify Deployment

# Check database schema
psql -U username -d stellaraid_prod -c "\d"

# Verify migration applied
SELECT * FROM _prisma_migrations;

Migration Files Generated

Once you run npx prisma migrate dev --name init, the following files will be created:

prisma/
├── migrations/
│   ├── _MigrationLock.toml
│   └── 20240528000000_init/
│       └── migration.sql

The migration.sql file will contain the SQL to create all tables, indices, constraints, and enums.

Sample SQL (autogenerated)

-- CreateEnum: UserRole
CREATE TYPE "UserRole" AS ENUM ('DONOR', 'CREATOR', 'ADMIN');

-- CreateEnum: CampaignStatus
CREATE TYPE "CampaignStatus" AS ENUM (
  'DRAFT',
  'PENDING_APPROVAL',
  'ACTIVE',
  'COMPLETED',
  'CANCELLED',
  'REJECTED'
);

-- ... more enums ...

-- CreateTable: users
CREATE TABLE "users" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "email" TEXT NOT NULL UNIQUE,
  "name" TEXT,
  "role" "UserRole" NOT NULL DEFAULT 'DONOR',
  "walletAddress" TEXT UNIQUE,
  "bio" TEXT,
  "isActive" BOOLEAN NOT NULL DEFAULT true,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" TIMESTAMP(3) NOT NULL,
  CONSTRAINT "users_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "users_email_idx" ON "users"("email");
CREATE INDEX "users_role_idx" ON "users"("role");
CREATE INDEX "users_isActive_idx" ON "users"("isActive");

-- ... more tables and indices ...

Rollback Procedures

Rollback Last Migration (Development)

npx prisma migrate resolve --rolled-back 20240528000000_init

Rollback to Specific Migration (Development)

npx prisma migrate reset

This will:

Drop the database
Re-create it
Re-run all migrations
Re-seed if seed script exists

Manual Rollback (Production - Use Caution)

DROP TABLE IF EXISTS audit_logs CASCADE;
DROP TABLE IF EXISTS newsletters CASCADE;
DROP TABLE IF EXISTS disputes CASCADE;
DROP TABLE IF EXISTS notifications CASCADE;
DROP TABLE IF EXISTS "updates" CASCADE;
DROP TABLE IF EXISTS milestones CASCADE;
DROP TABLE IF EXISTS donations CASCADE;
DROP TABLE IF EXISTS campaigns CASCADE;
DROP TABLE IF EXISTS users CASCADE;

-- Drop enums
DROP TYPE IF EXISTS "AuditActionType";
DROP TYPE IF EXISTS "DisputeStatus";
DROP TYPE IF EXISTS "NotificationType";
DROP TYPE IF EXISTS "MilestoneStatus";
DROP TYPE IF EXISTS "DonationStatus";
DROP TYPE IF EXISTS "CampaignStatus";
DROP TYPE IF EXISTS "UserRole";

Testing the Schema

Using Prisma Studio (Visual Tool)

npx prisma studio

Opens browser interface at http://localhost:5555 to:

View all records
Create test data
Inspect relationships

Using Prisma Client (Programmatic)

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Create a user
const user = await prisma.user.create({
  data: {
    email: 'donor@example.com',
    name: 'John Donor',
    role: 'DONOR',
  },
});

// Create a campaign
const campaign = await prisma.campaign.create({
  data: {
    title: 'Help Build a Library',
    description: 'Fund our community library project',
    goalAmount: new Decimal('10000.00'),
    status: 'DRAFT',
    creatorId: user.id,
  },
});

// Make a donation
const donation = await prisma.donation.create({
  data: {
    amount: new Decimal('100.00'),
    donorId: user.id,
    campaignId: campaign.id,
    status: 'PENDING',
  },
});

console.log(donation);

Seeding the Database (Optional)

Create prisma/seed.ts for test data:

import { PrismaClient, UserRole, CampaignStatus } from '@prisma/client';
import { Decimal } from '@prisma/client/runtime/library';

const prisma = new PrismaClient();

async function main() {
  // Create test users
  const donor = await prisma.user.create({
    data: {
      email: 'donor1@test.com',
      name: 'Alice Donor',
      role: UserRole.DONOR,
      walletAddress: 'GXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX',
    },
  });

  const creator = await prisma.user.create({
    data: {
      email: 'creator1@test.com',
      name: 'Bob Creator',
      role: UserRole.CREATOR,
      walletAddress: 'GYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY',
    },
  });

  // Create test campaign
  const campaign = await prisma.campaign.create({
    data: {
      title: 'Community Education Fund',
      description: 'Funding quality education for underprivileged youth',
      goalAmount: new Decimal('50000.00'),
      status: CampaignStatus.ACTIVE,
      creatorId: creator.id,
      category: 'education',
      startDate: new Date(),
      endDate: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // 30 days
    },
  });

  console.log('Seeded database with test data');
}

main()
  .catch((e) => {
    console.error(e);
    process.exit(1);
  })
  .finally(async () => {
    await prisma.$disconnect();
  });

Run seed:

npx prisma db seed

Common Issues & Solutions

Issue: "PrismaClientValidationError" in production

Solution: Ensure Prisma Client is generated:

npx prisma generate

Issue: Migration fails with "relation already exists"

Solution: Verify database is clean or check for existing migrations:

npx prisma migrate reset  # CAUTION: Drops all data

Issue: Foreign key constraint violation

Solution: Ensure parent records exist before inserting child records

Issue: Type errors in TypeScript

Solution: Regenerate types:

npx prisma generate
npx tsc --noEmit

Next Steps After Migration

✅ Run migration: npx prisma migrate dev --name init
✅ Generate types: npx prisma generate
✅ Create Prisma service: NestJS module to inject PrismaClient
✅ Implement repositories: Data access layer patterns
✅ Add seed data: Populate test data
✅ Write tests: Unit and integration tests
✅ Document API: OpenAPI/Swagger docs

Resources

Approval Sign-Off

This schema is ready for first migration once approved:

Schema Definition: ✅ Complete
Validation: ✅ Passed
Documentation: ✅ Complete
Review: ⏳ Pending team approval

Approved by: _________________
Date: _________________
Comments: ___________________________________________________________

Once approved, execute:

npx prisma migrate dev --name init

FilesExpand file tree

MIGRATION_GUIDE.md

Latest commit

History