fogserv.cloud fogserv.cloud

CRM/CMS Database Architecture

Status: Active
Last Updated: January 30, 2026 03:35 AM
Session: Website rebuild - Phase 3 CRM/CMS Design
Tags: database, prisma, cms, crm, email-marketing, schema-design

Summary

Comprehensive database schema for fogserv.cloud combining Ghost-like content management, email marketing platform (Mailgun integration), and subscriber relationship management. Designed for SQLite development with PostgreSQL production via Prisma Accelerate.

Architecture Overview

The schema is organized into four major domains:

  1. User Management & Authentication - Role-based access control (RBAC) with 6 user roles
  2. Content Management System - Ghost-like publishing platform with posts, pages, tags, categories, comments
  3. Email Marketing & Subscribers - Newsletter management with double opt-in, segmentation, engagement tracking
  4. Analytics & Tracking - Page views, link clicks, campaign performance

Total Models: 21

User Management Domain

User Model

Core user table supporting both content creators and admin users.

Roles: OWNER > ADMIN > EDITOR > AUTHOR > CONTRIBUTOR > SUBSCRIBER

// Role hierarchy and permissions
OWNER       // Full system access, billing, user management
ADMIN       // Content & user management, no billing
EDITOR      // Full content creation & editing, scheduling
AUTHOR      // Create & edit own content only
CONTRIBUTOR // Submit drafts for review, no publishing
SUBSCRIBER  // Read-only, comment permissions

Status States: ACTIVE | INACTIVE | SUSPENDED | PENDING_VERIFICATION

Key Fields:

Indexes: email, role, status

Session Model

JWT-like session management for authentication persistence.

Features:

Content Management Domain

Post Model

Core content entity supporting multiple content types with full publishing workflow.

Content Types: POST | PAGE | SNIPPET | GUIDE

Publishing States: DRAFT > SCHEDULED > PUBLISHED | ARCHIVED

Key Features:

Relations:

Indexes: slug (unique), status, type, authorId, publishedAt

Tag & Category Models

Flexible content organization with flat tags and hierarchical categories.

Tags:

Categories:

Junction Tables: PostTag, PostCategory (many-to-many with timestamps)

Comment Model

Threaded comment system with moderation and guest support.

Features:

Indexes: postId, authorId, parentId, approved

Email Marketing Domain

Subscriber Model

Central table for newsletter/email list management with double opt-in.

Status Flow: PENDING → ACTIVE → [UNSUBSCRIBED | BOUNCED | COMPLAINED]

Verification:

Engagement Tracking:

Privacy & Compliance:

Relations:

Indexes: email (unique), status, subscribedAt

EmailList Model

Segmented mailing lists for targeted campaigns.

Features:

Use Cases:

SubscriberTag Model

Flexible tagging system for segmentation without formal lists.

Examples:

Composite Key: [subscriberId, tagName]

Campaign Model

Email campaign management with Mailgun integration.

States: DRAFT → SCHEDULED → SENDING → SENT | [PAUSED | CANCELLED]

Content:

Targeting:

Mailgun Integration:

Analytics:

Indexes: status, scheduledFor, mailgunId (unique)

CampaignLog Model

Per-recipient event log for detailed campaign tracking.

Event Types:

Event Timestamps:

Error Tracking:

Indexes: campaignId, subscriberId, sent, opened

EmailTemplate Model

Reusable email templates with variable substitution.

Features:

Variables Example:

["subscriberName", "unsubscribeUrl", "companyName", "currentYear"]

Usage: Campaigns reference templates, variables interpolated at send time

Analytics Domain

PageView Model

Simple analytics for content performance tracking.

Captured Data:

Indexes: path, timestamp

Use Cases:

LinkClick Model

Click tracking for email campaigns and external links.

Captured Data:

Indexes: url, campaignId, timestamp

Use Cases:

Relationships & Cascade Rules

Cascade Delete

Ensures referential integrity when entities are removed:

Set Null

Preserves historical data when non-critical relations are deleted:

Indexes Strategy

Purpose-Driven Indexing:

Compound Indexes: Junction tables use composite primary keys ([id1, id2])

Data Integrity Patterns

Email Uniqueness

Slug Uniqueness

Token Security

Migration Strategy

Phase 1: Foundation (Current)

✅ Schema defined in prisma/schema.prisma
✅ Prisma client generated
⏳ Initial migration pending

Phase 2: Seed Data

Phase 3: Production Migration

Phase 4: Data Import

Query Patterns

Common Queries

Published Posts with Tags:

const posts = await prisma.post.findMany({
  where: { status: 'PUBLISHED', type: 'POST' },
  include: {
    author: { select: { name: true, avatar: true } },
    tags: { include: { tag: true } },
    categories: { include: { category: true } }
  },
  orderBy: { publishedAt: 'desc' }
})

Active Subscribers for Campaign:

const subscribers = await prisma.subscriber.findMany({
  where: {
    status: 'ACTIVE',
    lists: { some: { listId: 'newsletter-id' } }
  },
  select: { email: true, name: true }
})

Campaign Performance:

const campaign = await prisma.campaign.findUnique({
  where: { id: campaignId },
  include: {
    logs: {
      where: { opened: true },
      select: { subscriber: { select: { email: true } } }
    },
    _count: {
      select: {
        logs: { where: { delivered: true } }
      }
    }
  }
})

Performance Considerations

N+1 Query Prevention:

Pagination:

const posts = await prisma.post.findMany({
  take: 20,
  skip: page * 20,
  where: { status: 'PUBLISHED' },
  orderBy: { publishedAt: 'desc' }
})

Aggregations:

const stats = await prisma.campaign.aggregate({
  where: { status: 'SENT' },
  _sum: { opened: true, clicked: true },
  _avg: { opened: true }
})

Security Considerations

Authentication

Authorization

Privacy

Rate Limiting

Next Steps

Immediate Actions

  1. Create initial migration: bun run db:push (dev) or bun run db:migrate (prod)
  2. Generate seed script: Create prisma/seed.ts with default data
  3. Build repository layer: Create src/lib/repositories/ with Prisma wrappers
  4. Document API endpoints: Map routes to database operations

Development Priorities

  1. User Authentication: Login/register flows with session management
  2. Admin Dashboard: Content management UI for posts/categories/tags
  3. Email Service: Mailgun integration layer (see Phase 4 tasks)
  4. Subscriber Forms: Double opt-in signup with confirmation emails
  5. Campaign Builder: UI for composing and scheduling email campaigns

Future Enhancements

Sources & Related

Change Log

2026-01-30 03:35 AM - Initial CRM/CMS schema design
Created comprehensive database architecture with 21 models covering user management, content publishing, email marketing, and analytics. Designed for Ghost-like CMS experience with integrated Mailgun email platform. Schema supports role-based access, threaded comments, hierarchical categories, double opt-in subscribers, campaign tracking, and engagement analytics. Generated Prisma client successfully.

Session Context: Website rebuild Phase 3 - Database architecture foundation

Choose Theme

Your selection is saved locally.

Neural Cacophony
Aperture v2
Flux v1
Mosaic Chaos
Nexus v1
Nexus Zest
Prism v2
Synapse