Database Management

Understanding the SAMS database schema and how to extend it for your needs.

Database Management

SAMS uses Prisma ORM with PostgreSQL to provide a robust, type-safe database layer. This guide covers the database schema, management, and customization.

Database Schema Overview

The SAMS database is designed around multi-tenancy with organizations as the primary isolation boundary.

Core Models

User

The main user account model:

model User {
  id                String    @id @default(cuid())
  email             String    @unique
  name              String?
  image             String?
  emailVerified     DateTime?
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt

  // Authentication
  accounts          Account[]
  sessions          Session[]

  // Organizations
  memberships       Member[]

  // Billing
  purchases         Purchase[]
}

Organization

Multi-tenant organization model:

model Organization {
  id          String   @id @default(cuid())
  name        String
  slug        String   @unique
  description String?
  image       String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Members
  members     Member[]

  // Billing
  purchases   Purchase[]
}

Member

Organization membership with roles:

model Member {
  id             String     @id @default(cuid())
  userId         String
  organizationId String
  role           MemberRole @default(MEMBER)
  createdAt      DateTime   @default(now())
  updatedAt      DateTime   @updatedAt

  user         User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@unique([userId, organizationId])
}

enum MemberRole {
  ADMIN
  MEMBER
}

Purchase

Subscription and billing tracking:

model Purchase {
  id               String            @id @default(cuid())
  userId           String?
  organizationId   String?
  planId           String
  status           PurchaseStatus
  provider         PaymentProvider
  externalId       String            @unique
  createdAt        DateTime          @default(now())
  updatedAt        DateTime          @updatedAt

  user         User?         @relation(fields: [userId], references: [id])
  organization Organization? @relation(fields: [organizationId], references: [id])
}

Database Commands

Essential Commands

# Navigate to database package
cd packages/database

# Generate Prisma client and Zod schemas
pnpm generate

# Push schema changes to database (development)
pnpm push

# Create and apply migrations (production)
pnpm migrate dev
pnpm migrate deploy

# Reset database (development only)
pnpm reset

# Open Prisma Studio
pnpm studio

Migration Workflow

Development

For development, use pnpm push for quick iteration:

# Make changes to schema.prisma
# Then push to database
pnpm push

# Regenerate client
pnpm generate

Production

For production, always use migrations:

# Create migration
pnpm migrate dev --name add_custom_field

# Deploy to production
pnpm migrate deploy

Extending the Schema

Adding Custom Fields

To add fields to existing models:

Update the Prisma schema:

model User {
  id       String @id @default(cuid())
  email    String @unique
  name     String?

  // Add your custom fields
  phone    String?
  company  String?
  timezone String? @default("UTC")

  // ... existing fields
}

Create migration:

pnpm migrate dev --name add_user_custom_fields

Regenerate client:

pnpm generate

Adding New Models

Create new models for your specific needs:

model CustomData {
  id           String   @id @default(cuid())
  userId       String
  name         String
  data         Json
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("custom_data")
}

// Add relation to User model
model User {
  // ... existing fields
  customData CustomData[]
}

Organization-Scoped Models

For multi-tenant data, always include organizationId:

model ProjectData {
  id             String   @id @default(cuid())
  organizationId String
  name           String
  description    String?
  data           Json
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@map("project_data")
}

Database Client Usage

Basic Queries

import { db } from '@repo/database'

// Get user with organizations
const user = await db.user.findUnique({
  where: { id: userId },
  include: {
    memberships: {
      include: {
        organization: true,
      },
    },
  },
})

// Get organization with members
const organization = await db.organization.findUnique({
  where: { slug: orgSlug },
  include: {
    members: {
      include: {
        user: true,
      },
    },
  },
})

Organization-Scoped Queries

Always filter by organizationId for multi-tenant data:

// Get data for specific organization
const projectData = await db.projectData.findMany({
  where: {
    organizationId: organizationId,
  },
})

// Create organization-scoped data
const newProject = await db.projectData.create({
  data: {
    organizationId: organizationId,
    name: "New Project",
    data: { custom: "data" },
  },
})

Transactions

Use transactions for data consistency:

const result = await db.$transaction(async (tx) => {
  // Create organization
  const organization = await tx.organization.create({
    data: {
      name: "New Org",
      slug: "new-org",
    },
  })

  // Add user as admin
  await tx.member.create({
    data: {
      userId: userId,
      organizationId: organization.id,
      role: "ADMIN",
    },
  })

  return organization
})

Data Validation

Zod Schemas

SAMS automatically generates Zod schemas from Prisma models:

import { UserSchema, OrganizationSchema } from '@repo/database'

// Validate user data
const userData = UserSchema.parse(input)

// Validate partial updates
const userUpdate = UserSchema.partial().parse(input)

// Use in API endpoints
app.post('/api/users', async (c) => {
  const data = UserSchema.parse(await c.req.json())
  // ... save user
})

Custom Validation

Add custom validation for business logic:

import { z } from 'zod'

const CreateOrganizationSchema = z.object({
  name: z.string().min(1).max(100),
  slug: z.string()
    .min(3)
    .max(50)
    .regex(/^[a-z0-9-]+$/, "Only lowercase letters, numbers, and hyphens"),
  description: z.string().max(500).optional(),
})

Performance Optimization

Database Indexes

Add indexes for commonly queried fields:

model CustomData {
  id             String   @id @default(cuid())
  organizationId String
  userId         String
  type           String
  createdAt      DateTime @default(now())

  // Add indexes
  @@index([organizationId])
  @@index([userId])
  @@index([type])
  @@index([organizationId, type])
  @@index([createdAt])
}

Query Optimization

Use proper includes and selects:

// Good: Only select needed fields
const users = await db.user.findMany({
  select: {
    id: true,
    name: true,
    email: true,
  },
  where: {
    memberships: {
      some: {
        organizationId: orgId,
      },
    },
  },
})

// Good: Use pagination
const users = await db.user.findMany({
  take: 20,
  skip: page * 20,
  orderBy: { createdAt: 'desc' },
})

Connection Pooling

Configure connection pooling for production:

# Use connection pooling URL
DATABASE_URL="postgresql://user:pass@host:6543/db?pgbouncer=true"

# Or configure pool size
DATABASE_URL="postgresql://user:pass@host:5432/db?connection_limit=10"

Backup and Restore

Automated Backups

Set up automated backups for production:

# PostgreSQL backup
pg_dump $DATABASE_URL > backup-$(date +%Y%m%d).sql

# Restore from backup
psql $DATABASE_URL < backup-20231101.sql

Data Export

Export specific data:

// Export organization data
const exportData = await db.organization.findUnique({
  where: { id: orgId },
  include: {
    members: {
      include: {
        user: {
          select: {
            id: true,
            name: true,
            email: true,
          },
        },
      },
    },
    // Include other related data
  },
})

Database Security

Row Level Security

Implement application-level RLS:

// Create middleware for organization access
export function withOrganizationAccess(handler: Handler) {
  return async (c: Context) => {
    const user = await getUser(c)
    const orgSlug = c.req.param('orgSlug')

    // Verify user has access to organization
    const member = await db.member.findFirst({
      where: {
        userId: user.id,
        organization: { slug: orgSlug },
      },
    })

    if (!member) {
      return c.json({ error: 'Access denied' }, 403)
    }

    // Add organization to context
    c.set('organization', member.organization)
    c.set('memberRole', member.role)

    return handler(c)
  }
}

Data Encryption

Encrypt sensitive data:

import { encrypt, decrypt } from '@/lib/encryption'

// Encrypt before saving
const encryptedData = encrypt(sensitiveData)
await db.sensitiveData.create({
  data: {
    userId: userId,
    data: encryptedData,
  },
})

// Decrypt after retrieval
const record = await db.sensitiveData.findFirst({
  where: { userId },
})
const decryptedData = decrypt(record.data)

Testing

Test Database Setup

Use a separate database for testing:

# .env.test
DATABASE_URL="postgresql://localhost:5432/supasecure_test"

Database Testing

import { db } from '@repo/database'

describe('User model', () => {
  beforeEach(async () => {
    // Clean database
    await db.$executeRaw`TRUNCATE TABLE "User" RESTART IDENTITY CASCADE`
  })

  it('creates user', async () => {
    const user = await db.user.create({
      data: {
        email: 'test@example.com',
        name: 'Test User',
      },
    })

    expect(user.email).toBe('test@example.com')
  })
})

Troubleshooting

Common Issues

Migration Errors:

Check for conflicting schema changes
Ensure database is accessible
Verify permissions for schema changes

Connection Issues:

Verify DATABASE_URL is correct
Check database server is running
Ensure firewall allows connections

Performance Issues:

Add indexes for slow queries
Use query optimization
Consider connection pooling

Debug Queries

Enable query logging:

// Enable in development
const db = new PrismaClient({
  log: ['query', 'info', 'warn', 'error'],
})

Next: Learn about Deployment to get your SAMS instance running in production.

Database Management

On this page