Skip to content

DAY_4_DATABASE_MIGRATION.md

Latest commit

 

History

History
752 lines (617 loc) · 19.7 KB

DAY_4_DATABASE_MIGRATION.md

File metadata and controls

752 lines (617 loc) · 19.7 KB

Day 4: Database Migration Plan

Date: October 18, 2025
Goal: Migrate from session-based storage to persistent PostgreSQL database
Estimated Time: 8 hours
Audit Impact: Database 0/10 → 7/10, Security 6.9/10 → 8/10

🎯 Objectives

Primary Goals

  1. ✅ Persistent data storage (survives server restarts)
  2. ✅ Multi-user support (currently limited to single session)
  3. ✅ Revenue protection (no localStorage hacks)
  4. ✅ Audit trail (track all user interactions)
  5. ✅ Analytics enhancement (historical data for PostHog)

Technical Requirements

  • PostgreSQL database (Supabase or self-hosted)
  • Prisma ORM for type-safe queries
  • Migration scripts for schema changes
  • Connection pooling for performance
  • Database health check endpoint

🏗️ Architecture Decision: Supabase vs Self-Hosted

Option A: Supabase (Recommended ✅)

Pros:

  • ✅ Free tier: 500MB database, 2GB bandwidth/month
  • ✅ Auto-generated REST API & TypeScript types
  • ✅ Built-in auth (can replace custom auth later)
  • ✅ Real-time subscriptions
  • ✅ Row-level security (RLS)
  • ✅ Automatic backups
  • ✅ Dashboard for data management
  • ✅ Edge functions (serverless)
  • ✅ No DevOps overhead

Cons:

  • ⚠️ Vendor lock-in (mitigated by standard PostgreSQL)
  • ⚠️ Cold starts on free tier
  • ⚠️ External dependency

Cost: $0/month (free tier) → $25/month (pro) when scaling

Option B: Self-Hosted PostgreSQL + Prisma

Pros:

  • ✅ Full control over infrastructure
  • ✅ No vendor lock-in
  • ✅ Prisma provides excellent TypeScript DX

Cons:

  • ⚠️ Requires hosting (Railway, Render, or local Docker)
  • ⚠️ Manual backup setup
  • ⚠️ DevOps overhead (monitoring, scaling, security)
  • ⚠️ Additional cost ($5-10/month minimum)

Cost: $5-10/month (Railway/Render)

Decision: Supabase ✅

Reasoning:

  • Free tier sufficient for alpha/MVP
  • Fastest setup (< 1 hour)
  • Production-ready out of the box
  • Can migrate to self-hosted later if needed
  • Standard PostgreSQL (no lock-in)

📊 Database Schema Design

Users Table

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT UNIQUE NOT NULL,
  stripe_customer_id TEXT UNIQUE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_users_stripe_customer ON users(stripe_customer_id);
CREATE INDEX idx_users_email ON users(email);

Purpose: Central user identity, links to Stripe

Subscriptions Table

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  tier TEXT NOT NULL CHECK (tier IN ('free', 'basic', 'pro', 'enterprise')),
  status TEXT NOT NULL CHECK (status IN ('active', 'canceled', 'past_due', 'trialing')),
  stripe_subscription_id TEXT UNIQUE,
  current_period_start TIMESTAMPTZ NOT NULL,
  current_period_end TIMESTAMPTZ NOT NULL,
  cancel_at_period_end BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX idx_subscriptions_stripe_id ON subscriptions(stripe_subscription_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);

Purpose: Track subscription state, billing periods

Usage Table

CREATE TABLE usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  period_start TIMESTAMPTZ NOT NULL,
  period_end TIMESTAMPTZ NOT NULL,
  interactions_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, period_start)
);

CREATE INDEX idx_usage_user_id ON usage(user_id);
CREATE INDEX idx_usage_period ON usage(period_start, period_end);

Purpose: Track monthly interaction counts per user

Messages Table

CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  deck_type TEXT NOT NULL CHECK (deck_type IN ('alpha', 'defense', 'medical')),
  role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_messages_user_id ON messages(user_id);
CREATE INDEX idx_messages_deck_type ON messages(deck_type);
CREATE INDEX idx_messages_created_at ON messages(created_at DESC);

Purpose: Chat history, analytics, audit trail

Decks Table

CREATE TABLE decks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  deck_type TEXT NOT NULL CHECK (deck_type IN ('alpha', 'defense', 'medical')),
  state JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, deck_type)
);

CREATE INDEX idx_decks_user_id ON decks(user_id);
CREATE INDEX idx_decks_type ON decks(deck_type);

Purpose: Persist deck state (case details, tasks, etc.)

🚀 Implementation Steps

Step 1: Supabase Project Setup (30 min)

  1. Create Supabase Project:

    # Go to https://supabase.com
# Sign up / login
# Create new project: "darkdeck-alpha"
# Region: Choose closest to users (US East recommended)
# Database password: Generate strong password

  2. Get Connection Details:

    # Project Settings → Database
# Copy:
# - Connection string (postgres://...)
# - Supabase URL (https://xxx.supabase.co)
# - Anon public key
# - Service role key (keep secret!)

  3. Add Environment Variables:

    # .env.local
DATABASE_URL="postgresql://postgres:[password]@db.[project-ref].supabase.co:5432/postgres"
NEXT_PUBLIC_SUPABASE_URL="https://[project-ref].supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="eyJ..."
SUPABASE_SERVICE_ROLE_KEY="eyJ..." # Server-side only

Step 2: Install Dependencies (10 min)

pnpm add @supabase/supabase-js
pnpm add -D prisma
pnpm add @prisma/client

Step 3: Prisma Setup (30 min)

  1. Initialize Prisma:

    npx prisma init

  2. Create Schema (prisma/schema.prisma):

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

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

model User {
  id                String         @id @default(uuid())
  email             String         @unique
  stripeCustomerId  String?        @unique @map("stripe_customer_id")
  createdAt         DateTime       @default(now()) @map("created_at")
  updatedAt         DateTime       @updatedAt @map("updated_at")
  subscriptions     Subscription[]
  usage             Usage[]
  messages          Message[]
  decks             Deck[]

  @@map("users")
}

model Subscription {
  id                   String   @id @default(uuid())
  userId               String   @map("user_id")
  tier                 String
  status               String
  stripeSubscriptionId String?  @unique @map("stripe_subscription_id")
  currentPeriodStart   DateTime @map("current_period_start")
  currentPeriodEnd     DateTime @map("current_period_end")
  cancelAtPeriodEnd    Boolean  @default(false) @map("cancel_at_period_end")
  createdAt            DateTime @default(now()) @map("created_at")
  updatedAt            DateTime @updatedAt @map("updated_at")
  user                 User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([stripeSubscriptionId])
  @@index([status])
  @@map("subscriptions")
}

model Usage {
  id                 String   @id @default(uuid())
  userId             String   @map("user_id")
  periodStart        DateTime @map("period_start")
  periodEnd          DateTime @map("period_end")
  interactionsCount  Int      @default(0) @map("interactions_count")
  createdAt          DateTime @default(now()) @map("created_at")
  updatedAt          DateTime @updatedAt @map("updated_at")
  user               User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([userId, periodStart])
  @@index([userId])
  @@index([periodStart, periodEnd])
  @@map("usage")
}

model Message {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  deckType  String   @map("deck_type")
  role      String
  content   String
  metadata  Json     @default("{}")
  createdAt DateTime @default(now()) @map("created_at")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@index([deckType])
  @@index([createdAt(sort: Desc)])
  @@map("messages")
}

model Deck {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  deckType  String   @map("deck_type")
  state     Json     @default("{}")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([userId, deckType])
  @@index([userId])
  @@index([deckType])
  @@map("decks")
}

  3. Generate Migration:

    npx prisma migrate dev --name initial_schema

  4. Generate Prisma Client:

    npx prisma generate

Step 4: Create Database Client (30 min)

lib/db.ts:

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

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined;
};

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    log: process.env.NODE_ENV === 'development' ? ['query', 'error', 'warn'] : ['error'],
  });

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma;

lib/supabase-server.ts (for server-side):

import { createClient } from '@supabase/supabase-js';

export const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false,
    },
  }
);

lib/supabase-client.ts (for client-side):

import { createClient } from '@supabase/supabase-js';

export const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

Step 5: Update Subscription System (2 hours)

Refactor lib/subscription.ts to use database:

import { prisma } from './db';
import type { User, Subscription as PrismaSubscription } from '@prisma/client';

export async function getSubscriptionFromDB(userId: string): Promise<SubscriptionData> {
  const subscription = await prisma.subscription.findFirst({
    where: {
      userId,
      status: 'active',
    },
    orderBy: {
      currentPeriodEnd: 'desc',
    },
  });

  if (!subscription) {
    return {
      tier: 'free',
      status: 'active',
      periodEnd: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000),
    };
  }

  return {
    tier: subscription.tier as 'free' | 'basic' | 'pro' | 'enterprise',
    status: subscription.status as 'active' | 'canceled' | 'past_due',
    periodEnd: subscription.currentPeriodEnd,
    stripeSubscriptionId: subscription.stripeSubscriptionId || undefined,
  };
}

export async function getUsageFromDB(userId: string): Promise<UsageData> {
  const now = new Date();
  const periodStart = new Date(now.getFullYear(), now.getMonth(), 1);
  const periodEnd = new Date(now.getFullYear(), now.getMonth() + 1, 0);

  let usage = await prisma.usage.findUnique({
    where: {
      userId_periodStart: {
        userId,
        periodStart,
      },
    },
  });

  if (!usage) {
    usage = await prisma.usage.create({
      data: {
        userId,
        periodStart,
        periodEnd,
        interactionsCount: 0,
      },
    });
  }

  return {
    count: usage.interactionsCount,
    periodStart,
    periodEnd,
  };
}

export async function incrementUsageDB(userId: string): Promise<void> {
  const now = new Date();
  const periodStart = new Date(now.getFullYear(), now.getMonth(), 1);
  const periodEnd = new Date(now.getFullYear(), now.getMonth() + 1, 0);

  await prisma.usage.upsert({
    where: {
      userId_periodStart: {
        userId,
        periodStart,
      },
    },
    update: {
      interactionsCount: {
        increment: 1,
      },
    },
    create: {
      userId,
      periodStart,
      periodEnd,
      interactionsCount: 1,
    },
  });
}

Step 6: Update Stripe Webhook (1 hour)

Modify app/api/stripe/webhook/route.ts to write to database:

case 'customer.subscription.created':
case 'customer.subscription.updated': {
  const subscription = event.data.object as Stripe.Subscription;
  const customerId = subscription.customer as string;

  // Find or create user
  let user = await prisma.user.findUnique({
    where: { stripeCustomerId: customerId },
  });

  if (!user) {
    // Create user from Stripe customer data
    const customer = await stripe.customers.retrieve(customerId);
    user = await prisma.user.create({
      data: {
        email: (customer as Stripe.Customer).email || `customer_${customerId}@stripe.com`,
        stripeCustomerId: customerId,
      },
    });
  }

  // Upsert subscription
  await prisma.subscription.upsert({
    where: { stripeSubscriptionId: subscription.id },
    update: {
      tier: getTierFromPriceId(subscription.items.data[0].price.id),
      status: subscription.status,
      currentPeriodStart: new Date(subscription.current_period_start * 1000),
      currentPeriodEnd: new Date(subscription.current_period_end * 1000),
      cancelAtPeriodEnd: subscription.cancel_at_period_end,
    },
    create: {
      userId: user.id,
      stripeSubscriptionId: subscription.id,
      tier: getTierFromPriceId(subscription.items.data[0].price.id),
      status: subscription.status,
      currentPeriodStart: new Date(subscription.current_period_start * 1000),
      currentPeriodEnd: new Date(subscription.current_period_end * 1000),
      cancelAtPeriodEnd: subscription.cancel_at_period_end,
    },
  });
  break;
}

Step 7: Update Authentication (1 hour)

Modify app/api/auth/login/route.ts to create/retrieve user:

// After successful password validation
const user = await prisma.user.upsert({
  where: { email: 'admin@darkdeck.local' }, // Or derive from auth system
  update: {},
  create: {
    email: 'admin@darkdeck.local',
  },
});

// Store user ID in session token payload
const payloadObj = { 
  v: 1, 
  iat: now, 
  exp,
  userId: user.id, // Add user ID to JWT
};

Step 8: Persist Chat Messages (1 hour)

Add to app/terminal/TerminalClientPage.tsx:

// After AI response received
await fetch('/api/messages/save', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    deckType: activeMode,
    role: 'user',
    content: userMessage,
  }),
});

await fetch('/api/messages/save', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    deckType: activeMode,
    role: 'assistant',
    content: fullText,
  }),
});

Create app/api/messages/save/route.ts:

export async function POST(req: Request) {
  const session = await getSession(req);
  if (!session?.userId) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  const { deckType, role, content, metadata } = await req.json();

  await prisma.message.create({
    data: {
      userId: session.userId,
      deckType,
      role,
      content,
      metadata: metadata || {},
    },
  });

  return NextResponse.json({ ok: true });
}

Step 9: Database Health Check (30 min)

Create app/api/health/route.ts:

import { prisma } from '@/lib/db';

export async function GET() {
  try {
    await prisma.$queryRaw`SELECT 1`;
    return NextResponse.json({ 
      status: 'healthy',
      database: 'connected',
      timestamp: new Date().toISOString(),
    });
  } catch (error) {
    return NextResponse.json(
      { 
        status: 'unhealthy',
        database: 'disconnected',
        error: (error as Error).message,
      },
      { status: 503 }
    );
  }
}

Step 10: Testing & Validation (1 hour)

# Test database connection
curl http://localhost:3000/api/health

# Test Prisma Studio
npx prisma studio

# Run migrations
npx prisma migrate deploy

# Generate types
npx prisma generate

# Seed test data
npx prisma db seed

🔄 Migration Strategy

Phase 1: Parallel Operation (Week 1)

  • ✅ New users → Database
  • ✅ Existing sessions → Keep using session storage
  • ✅ No data loss

Phase 2: Gradual Migration (Week 2)

  • ✅ Prompt users to re-login
  • ✅ Copy session data to database
  • ✅ Monitor for issues

Phase 3: Full Cutover (Week 3)

  • ✅ Disable session storage
  • ✅ All operations use database
  • ✅ Remove fallback code

📈 Success Metrics

Metric Target Validation
Database uptime 99.9% Health check endpoint
Query response time <100ms Prisma logging
Connection pool size 10-20 Monitor Supabase dashboard
Data consistency 100% No orphaned records
Zero data loss ✅ All writes confirmed

🔐 Security Considerations

Row-Level Security (RLS)

-- Enable RLS on all tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE usage ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;
ALTER TABLE decks ENABLE ROW LEVEL SECURITY;

-- Users can only read their own data
CREATE POLICY "Users can view own data" ON users
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can view own subscriptions" ON subscriptions
  FOR SELECT USING (auth.uid() = user_id);

-- Admin service role can do everything (for Next.js API routes)
CREATE POLICY "Service role full access" ON users
  FOR ALL USING (auth.jwt() ->> 'role' = 'service_role');

Environment Variable Security

# Never commit these
DATABASE_URL="..." # Contains password
SUPABASE_SERVICE_ROLE_KEY="..." # Full admin access

# Safe to commit (public)
NEXT_PUBLIC_SUPABASE_URL="https://..."
NEXT_PUBLIC_SUPABASE_ANON_KEY="eyJ..." # RLS-protected

🎯 Acceptance Criteria

  • Supabase project created and configured
  • Prisma schema matches requirements
  • All migrations run successfully
  • Users table populated from auth
  • Subscriptions sync from Stripe webhooks
  • Usage tracking persists to database
  • Messages stored after each interaction
  • Deck state persists across sessions
  • Health check endpoint returns 200
  • Zero errors in production logs
  • All tests pass
  • Documentation updated

📚 Documentation Updates Required

  1. README.md: Add database setup instructions
  2. DAY_4_COMPLETE.md: Document migration process
  3. .env.example: Add Supabase variables
  4. DEPLOYMENT.md: Add database deployment steps

🚨 Rollback Plan

If issues arise:

  1. Disable database writes (feature flag)
  2. Revert to session storage (keep fallback code)
  3. Analyze error logs in Sentry
  4. Fix issues in dev environment
  5. Re-deploy after testing

Next Actions:

  1. Create Supabase project (30 min)
  2. Install dependencies (10 min)
  3. Set up Prisma (30 min)
  4. Implement database client (30 min)
  5. Update subscription system (2 hours)
  6. Update Stripe webhook (1 hour)
  7. Update authentication (1 hour)
  8. Persist messages (1 hour)
  9. Add health check (30 min)
  10. Test & validate (1 hour)

Total Estimated Time: 8 hours
Break into: 2 sessions of 4 hours each