Database Schema

This document provides a comprehensive overview of the Stelo CMS database schema, built with Prisma and PostgreSQL.

Complete Schema

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

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

// =============================================================================
// USER MANAGEMENT
// =============================================================================

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String    @unique
  emailVerified DateTime?
  image         String?
  role          UserRole  @default(EDITOR)

  // Timestamps
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt
  lastLoginAt   DateTime?

  // Relations
  accounts      Account[]
  sessions      Session[]
  pages         Page[]
  collections   Collection[]
  mediaFiles    MediaFile[]

  @@map("users")
}

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?

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

  @@unique([provider, providerAccountId])
  @@map("accounts")
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("sessions")
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
  @@map("verification_tokens")
}

enum UserRole {
  ADMIN
  EDITOR
  VIEWER
}

// =============================================================================
// CONTENT ENTITIES
// =============================================================================

model Page {
  id          String   @id @default(cuid())
  slug        Json     // Localized slugs: { "en": "about", "fr": "a-propos" }
  title       Json     // Localized titles
  content     Json     // Localized rich content
  excerpt     Json?    // Localized excerpts
  metadata    Json?    // SEO and custom metadata

  // Status
  published   Boolean  @default(false)
  publishedAt DateTime?
  featured    Boolean  @default(false)

  // Timestamps
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations
  authorId    String?
  author      User?    @relation(fields: [authorId], references: [id])

  // Revisions
  revisions   PageRevision[]

  @@index([published])
  @@index([featured])
  @@index([authorId])
  @@index([createdAt])
  @@map("pages")
}

model Collection {
  id          String        @id @default(cuid())
  type        CollectionType
  slug        Json          // Localized slugs
  title       Json          // Localized titles
  content     Json          // Localized rich content
  excerpt     Json?         // Localized excerpts
  metadata    Json?         // Type-specific metadata

  // Status
  published   Boolean       @default(false)
  publishedAt DateTime?
  featured    Boolean       @default(false)
  sortOrder   Int?          // Manual ordering

  // Timestamps
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

  // Relations
  authorId    String?
  author      User?         @relation(fields: [authorId], references: [id])

  // Media relations
  mediaFiles  CollectionMedia[]

  // Revisions
  revisions   CollectionRevision[]

  @@index([type, published])
  @@index([featured])
  @@index([sortOrder])
  @@index([authorId])
  @@map("collections")
}

model Global {
  id        String   @id @default(cuid())
  key       String   @unique // header, footer, seo, contact
  title     Json     // Localized titles
  content   Json     // Localized content/settings
  metadata  Json?    // Additional configuration

  // Timestamps
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@map("globals")
}

enum CollectionType {
  SERVICE
  BLOG_POST
  TEAM_MEMBER
  TESTIMONIAL
  PRODUCT
  PROJECT
  FAQ
  CUSTOM
}

// =============================================================================
// MEDIA MANAGEMENT
// =============================================================================

model MediaFile {
  id          String   @id @default(cuid())
  originalName String
  fileName    String   @unique
  mimeType    String
  size        Int
  width       Int?
  height      Int?

  // Storage
  url         String
  thumbnailUrl String?

  // Metadata
  alt         Json?    // Localized alt text
  caption     Json?    // Localized captions
  metadata    Json?    // EXIF data, etc.

  // Organization
  folder      String?
  tags        String[]

  // Timestamps
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations
  uploadedById String?
  uploadedBy   User?   @relation(fields: [uploadedById], references: [id])

  // Usage tracking
  collections CollectionMedia[]

  @@index([mimeType])
  @@index([folder])
  @@index([createdAt])
  @@map("media_files")
}

model CollectionMedia {
  id           String      @id @default(cuid())
  collectionId String
  mediaFileId  String
  role         String      // featured, gallery, attachment
  sortOrder    Int?

  collection   Collection  @relation(fields: [collectionId], references: [id], onDelete: Cascade)
  mediaFile    MediaFile   @relation(fields: [mediaFileId], references: [id], onDelete: Cascade)

  @@unique([collectionId, mediaFileId, role])
  @@map("collection_media")
}

// =============================================================================
// REVISION HISTORY
// =============================================================================

model PageRevision {
  id        String   @id @default(cuid())
  pageId    String
  version   Int
  data      Json     // Complete page data snapshot
  changeLogs String? // Summary of changes

  // Timestamps
  createdAt DateTime @default(now())

  // Relations
  createdById String?
  createdBy   User?   @relation(fields: [createdById], references: [id])
  page        Page    @relation(fields: [pageId], references: [id], onDelete: Cascade)

  @@unique([pageId, version])
  @@map("page_revisions")
}

model CollectionRevision {
  id           String     @id @default(cuid())
  collectionId String
  version      Int
  data         Json       // Complete collection data snapshot
  changeLogs   String?    // Summary of changes

  // Timestamps
  createdAt    DateTime   @default(now())

  // Relations
  createdById  String?
  createdBy    User?      @relation(fields: [createdById], references: [id])
  collection   Collection @relation(fields: [collectionId], references: [id], onDelete: Cascade)

  @@unique([collectionId, version])
  @@map("collection_revisions")
}

// =============================================================================
// ANALYTICS & LOGS
// =============================================================================

model PageView {
  id        String   @id @default(cuid())
  pageId    String?
  path      String
  locale    String?

  // Visitor info
  userAgent String?
  referer   String?
  ip        String?
  country   String?

  // Timestamps
  viewedAt  DateTime @default(now())

  @@index([pageId])
  @@index([path])
  @@index([viewedAt])
  @@map("page_views")
}

model ActivityLog {
  id        String      @id @default(cuid())
  action    String      // created, updated, deleted, published
  entityType String     // page, collection, user, media
  entityId  String

  // Changes
  oldData   Json?
  newData   Json?
  metadata  Json?

  // Timestamps
  createdAt DateTime    @default(now())

  // Relations
  userId    String?
  user      User?       @relation(fields: [userId], references: [id])

  @@index([entityType, entityId])
  @@index([action])
  @@index([createdAt])
  @@map("activity_logs")
}

// =============================================================================
// API & CACHE
// =============================================================================

model ApiKey {
  id          String   @id @default(cuid())
  name        String
  key         String   @unique
  permissions Json     // Allowed operations

  // Rate limiting
  rateLimit   Int      @default(1000) // Requests per hour

  // Status
  active      Boolean  @default(true)
  lastUsed    DateTime?

  // Timestamps
  createdAt   DateTime @default(now())
  expiresAt   DateTime?

  // Relations
  createdById String?
  createdBy   User?    @relation(fields: [createdById], references: [id])

  @@map("api_keys")
}

model CacheEntry {
  id        String   @id @default(cuid())
  key       String   @unique
  value     Json
  tags      String[]

  // TTL
  expiresAt DateTime
  createdAt DateTime @default(now())

  @@index([expiresAt])
  @@index([tags])
  @@map("cache_entries")
}

Schema Patterns

Localization Pattern

All content fields use JSON columns for multi-language support:

// Type-safe localization
type LocalizedString = Record<Locale, string>;
type LocalizedRichContent = Record<Locale, {
  body: string;
  excerpt?: string;
  featuredImage?: string;
}>;

// Example usage
const page = {
  title: {
    en: "Welcome",
    fr: "Bienvenue",
    es: "Bienvenido"
  },
  content: {
    en: {
      body: "<p>Welcome to our site</p>",
      excerpt: "A warm welcome"
    },
    fr: {
      body: "<p>Bienvenue sur notre site</p>",
      excerpt: "Un accueil chaleureux"
    }
  }
};

Metadata Pattern

Flexible metadata storage for SEO and custom fields:

// SEO metadata structure
const seoMetadata = {
  seo: {
    title: { en: "Custom SEO Title", fr: "Titre SEO" },
    description: { en: "Meta description", fr: "Description" },
    keywords: ["web", "design"],
    ogImage: "https://storage.com/og.jpg",
    canonicalUrl: "https://example.com/page",
    noindex: false
  },
  custom: {
    priority: 1,
    featured: true,
    category: "services",
    readingTime: 5
  }
};

Revision Pattern

Complete audit trail for all content changes:

// Creating a revision
async function createRevision(entityId: string, data: any, userId: string) {
  const lastRevision = await prisma.pageRevision.findFirst({
    where: { pageId: entityId },
    orderBy: { version: 'desc' }
  });

  await prisma.pageRevision.create({
    data: {
      pageId: entityId,
      version: (lastRevision?.version ?? 0) + 1,
      data: data,
      createdById: userId
    }
  });
}

Database Indexes

Performance Indexes

-- Content queries
CREATE INDEX idx_pages_published_featured ON pages(published, featured);
CREATE INDEX idx_collections_type_published ON collections(type, published);

-- Localized content search
CREATE INDEX idx_pages_title_gin ON pages USING gin (title);
CREATE INDEX idx_pages_content_gin ON pages USING gin (content);

-- Full-text search
CREATE INDEX idx_pages_search ON pages USING gin(
  to_tsvector('english',
    COALESCE(title->>'en', '') || ' ' ||
    COALESCE(content->'en'->>'body', '')
  )
);

-- Analytics
CREATE INDEX idx_page_views_date ON page_views(viewed_at);
CREATE INDEX idx_page_views_path_date ON page_views(path, viewed_at);

-- Media queries
CREATE INDEX idx_media_mime_folder ON media_files(mime_type, folder);
CREATE INDEX idx_media_size ON media_files(size) WHERE size > 1000000; -- Large files

-- Activity tracking
CREATE INDEX idx_activity_entity ON activity_logs(entity_type, entity_id);
CREATE INDEX idx_activity_user_date ON activity_logs(user_id, created_at);

Unique Constraints

-- Prevent duplicate slugs per locale
CREATE UNIQUE INDEX idx_pages_slug_unique ON pages((slug->>'en'));
CREATE UNIQUE INDEX idx_pages_slug_fr_unique ON pages((slug->>'fr'));
CREATE UNIQUE INDEX idx_pages_slug_es_unique ON pages((slug->>'es'));

-- API keys
CREATE UNIQUE INDEX idx_api_keys_key ON api_keys(key) WHERE active = true;

-- Global settings
CREATE UNIQUE INDEX idx_globals_key ON globals(key);

Migrations Strategy

Schema Evolution

// Migration example: Adding new locale
-- Add German translations to existing pages
UPDATE pages
SET title = title || jsonb_build_object('de', title->>'en'),
    content = content || jsonb_build_object('de', content->'en')
WHERE title ? 'en' AND NOT title ? 'de';

-- Update collections
UPDATE collections
SET title = title || jsonb_build_object('de', title->>'en'),
    content = content || jsonb_build_object('de', content->'en')
WHERE title ? 'en' AND NOT title ? 'de';

Data Seeding

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

const prisma = new PrismaClient();

async function main() {
  // Create admin user
  const admin = await prisma.user.create({
    data: {
      email: 'admin@example.com',
      name: 'Admin User',
      role: 'ADMIN'
    }
  });

  // Create default pages
  await prisma.page.createMany({
    data: [
      {
        slug: { en: 'home', fr: 'accueil', es: 'inicio' },
        title: {
          en: 'Home',
          fr: 'Accueil',
          es: 'Inicio'
        },
        content: {
          en: { body: '<h1>Welcome</h1>' },
          fr: { body: '<h1>Bienvenue</h1>' },
          es: { body: '<h1>Bienvenido</h1>' }
        },
        published: true,
        authorId: admin.id
      }
    ]
  });

  // Create global settings
  await prisma.global.createMany({
    data: [
      {
        key: 'header',
        title: { en: 'Header Settings', fr: 'Paramètres d\'en-tête' },
        content: {
          logo: 'https://storage.com/logo.png',
          navigation: [
            { en: 'Home', fr: 'Accueil', href: '/' },
            { en: 'About', fr: 'À propos', href: '/about' }
          ]
        }
      }
    ]
  });
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Query Patterns

Common Queries

// Get published pages in specific locale
const publishedPages = await prisma.page.findMany({
  where: { published: true },
  select: {
    id: true,
    slug: true,
    title: true,
    excerpt: true,
    publishedAt: true
  },
  orderBy: { publishedAt: 'desc' }
});

// Full-text search across content
const searchResults = await prisma.$queryRaw`
  SELECT id, title, content, ts_rank(search_vector, query) as rank
  FROM pages, websearch_to_tsquery('english', ${searchTerm}) query
  WHERE search_vector @@ query AND published = true
  ORDER BY rank DESC
  LIMIT 10
`;

// Get page with author and media
const pageWithRelations = await prisma.page.findUnique({
  where: { id: pageId },
  include: {
    author: {
      select: { name: true, email: true }
    },
    revisions: {
      orderBy: { version: 'desc' },
      take: 5
    }
  }
});

Analytics Queries

// Page view statistics
const pageViews = await prisma.pageView.groupBy({
  by: ['path'],
  _count: { id: true },
  where: {
    viewedAt: {
      gte: new Date(Date.now() - 30 * 24 * 60 * 60 * 1000) // Last 30 days
    }
  },
  orderBy: { _count: { id: 'desc' } }
});

// Content performance
const contentStats = await prisma.$queryRaw`
  SELECT
    p.id,
    p.title->>'en' as title,
    COUNT(pv.id) as views,
    AVG(extract(epoch from (pv.viewed_at - p.published_at))) as avg_time_to_view
  FROM pages p
  LEFT JOIN page_views pv ON pv.page_id = p.id
  WHERE p.published = true
  GROUP BY p.id, p.title
  ORDER BY views DESC
`;

This database schema provides a solid foundation for Stelo CMS while maintaining flexibility for future growth and customization.