Skip to content

EMBERDOCS-DATABASE-SCHEMA.md

Latest commit

 

History

History
917 lines (684 loc) · 25.6 KB

EMBERDOCS-DATABASE-SCHEMA.md

File metadata and controls

917 lines (684 loc) · 25.6 KB

EmberDocs Database Schema

Version: 1.0
Database: PostgreSQL 14+
ORM: Prisma (recommended)

Overview

The database schema supports the premium hosted version of EmberDocs. The OSS version is fully static and requires no database. Premium features requiring persistent storage include:

  • User accounts and authentication
  • Hosted documentation sites
  • Team collaboration
  • Advanced analytics
  • API access tokens
  • Billing and subscriptions

Design Principles:

  • Multi-tenancy support (one DB, many sites)
  • Audit trails for compliance
  • Soft deletes for data recovery
  • Timestamps on all tables
  • UUID primary keys for security

Schema Diagram

┌─────────────┐
│    users    │──┐
└─────────────┘  │
                 │
┌─────────────┐  │  ┌──────────────┐
│   sites     │←─┴──│ team_members │
└─────────────┘     └──────────────┘
       │
       ├──→ ┌────────────────┐
       │    │  deployments   │
       │    └────────────────┘
       │
       ├──→ ┌────────────────┐
       │    │  page_views    │
       │    └────────────────┘
       │
       ├──→ ┌────────────────┐
       │    │  search_queries│
       │    └────────────────┘
       │
       └──→ ┌────────────────┐
            │   api_tokens   │
            └────────────────┘

┌─────────────────┐
│ subscriptions   │
└─────────────────┘
       │
       └──→ ┌────────────────┐
            │    invoices    │
            └────────────────┘

Table Definitions

users

Stores user account information.

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  email_verified BOOLEAN DEFAULT FALSE,
  name VARCHAR(255),
  avatar_url TEXT,
  password_hash VARCHAR(255),  -- Only for email/password auth
  
  -- OAuth fields
  oauth_provider VARCHAR(50),   -- 'github', 'google', etc.
  oauth_id VARCHAR(255),
  
  -- Account status
  status VARCHAR(20) DEFAULT 'active', -- 'active', 'suspended', 'deleted'
  role VARCHAR(20) DEFAULT 'user',     -- 'user', 'admin'
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  last_login_at TIMESTAMP,
  deleted_at TIMESTAMP,
  
  -- Constraints
  CONSTRAINT valid_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
  CONSTRAINT valid_status CHECK (status IN ('active', 'suspended', 'deleted')),
  CONSTRAINT valid_role CHECK (role IN ('user', 'admin'))
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_oauth ON users(oauth_provider, oauth_id);
CREATE INDEX idx_users_status ON users(status) WHERE deleted_at IS NULL;

sites

Documentation sites hosted on EmberDocs premium.

CREATE TABLE sites (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Site identity
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(100) UNIQUE NOT NULL,  -- subdomain: slug.emberdocs.com
  custom_domain VARCHAR(255),         -- Optional custom domain
  
  -- Git integration
  git_provider VARCHAR(50),           -- 'github', 'gitlab', 'bitbucket'
  git_repo_url TEXT,
  git_branch VARCHAR(100) DEFAULT 'main',
  git_webhook_secret VARCHAR(255),
  
  -- Build configuration
  docs_path VARCHAR(255) DEFAULT './docs',
  build_command TEXT,
  
  -- Site settings (JSON for flexibility)
  config JSONB DEFAULT '{}',
  
  -- Deployment
  deployment_status VARCHAR(50) DEFAULT 'pending',  -- 'pending', 'building', 'deployed', 'failed'
  last_deployed_at TIMESTAMP,
  last_build_duration_ms INTEGER,
  
  -- Analytics settings
  analytics_enabled BOOLEAN DEFAULT TRUE,
  search_analytics_enabled BOOLEAN DEFAULT TRUE,
  
  -- Status
  status VARCHAR(20) DEFAULT 'active',  -- 'active', 'paused', 'deleted'
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  deleted_at TIMESTAMP,
  
  -- Constraints
  CONSTRAINT valid_slug CHECK (slug ~* '^[a-z0-9-]+$'),
  CONSTRAINT valid_git_provider CHECK (git_provider IN ('github', 'gitlab', 'bitbucket') OR git_provider IS NULL),
  CONSTRAINT valid_deployment_status CHECK (deployment_status IN ('pending', 'building', 'deployed', 'failed'))
);

CREATE INDEX idx_sites_owner ON sites(owner_id) WHERE deleted_at IS NULL;
CREATE INDEX idx_sites_slug ON sites(slug) WHERE deleted_at IS NULL;
CREATE INDEX idx_sites_custom_domain ON sites(custom_domain) WHERE deleted_at IS NULL;
CREATE INDEX idx_sites_status ON sites(status, deployment_status);

team_members

Team collaboration on sites.

CREATE TABLE team_members (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Role and permissions
  role VARCHAR(20) NOT NULL,  -- 'owner', 'admin', 'editor', 'viewer'
  permissions JSONB DEFAULT '{}',  -- Custom permissions if needed
  
  -- Invitation
  invited_by UUID REFERENCES users(id),
  invited_at TIMESTAMP DEFAULT NOW(),
  accepted_at TIMESTAMP,
  
  -- Status
  status VARCHAR(20) DEFAULT 'active',  -- 'active', 'pending', 'removed'
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  -- Constraints
  UNIQUE(site_id, user_id),
  CONSTRAINT valid_role CHECK (role IN ('owner', 'admin', 'editor', 'viewer')),
  CONSTRAINT valid_status CHECK (status IN ('active', 'pending', 'removed'))
);

CREATE INDEX idx_team_members_site ON team_members(site_id, status);
CREATE INDEX idx_team_members_user ON team_members(user_id, status);

deployments

Deployment history and logs.

CREATE TABLE deployments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  
  -- Trigger
  triggered_by UUID REFERENCES users(id),  -- NULL for webhook triggers
  trigger_type VARCHAR(50) NOT NULL,  -- 'manual', 'webhook', 'schedule'
  
  -- Git information
  commit_sha VARCHAR(40),
  commit_message TEXT,
  branch VARCHAR(100),
  
  -- Build info
  status VARCHAR(20) NOT NULL,  -- 'pending', 'building', 'success', 'failed', 'cancelled'
  build_log TEXT,
  error_message TEXT,
  
  -- Performance metrics
  build_duration_ms INTEGER,
  pages_built INTEGER,
  assets_size_bytes BIGINT,
  
  -- Timestamps
  started_at TIMESTAMP DEFAULT NOW(),
  completed_at TIMESTAMP,
  
  -- Constraints
  CONSTRAINT valid_trigger_type CHECK (trigger_type IN ('manual', 'webhook', 'schedule')),
  CONSTRAINT valid_status CHECK (status IN ('pending', 'building', 'success', 'failed', 'cancelled'))
);

CREATE INDEX idx_deployments_site ON deployments(site_id, started_at DESC);
CREATE INDEX idx_deployments_status ON deployments(status, started_at DESC);

page_views

Analytics for documentation page views.

CREATE TABLE page_views (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  
  -- Page information
  page_path VARCHAR(500) NOT NULL,
  page_title VARCHAR(500),
  
  -- Session tracking (privacy-friendly)
  session_hash VARCHAR(64),  -- Hashed IP + User Agent (daily rotation)
  
  -- Referrer
  referrer_domain VARCHAR(255),
  referrer_path TEXT,
  utm_source VARCHAR(100),
  utm_medium VARCHAR(100),
  utm_campaign VARCHAR(100),
  
  -- Device info
  device_type VARCHAR(20),  -- 'desktop', 'mobile', 'tablet'
  browser VARCHAR(50),
  os VARCHAR(50),
  
  -- Geographic (country-level only)
  country_code CHAR(2),
  
  -- Engagement metrics
  time_on_page_seconds INTEGER,
  scroll_depth_percent INTEGER,
  
  -- Timestamp
  viewed_at TIMESTAMP DEFAULT NOW(),
  
  -- Constraints
  CONSTRAINT valid_device_type CHECK (device_type IN ('desktop', 'mobile', 'tablet'))
);

-- Partitioned by month for performance
CREATE TABLE page_views_2024_12 PARTITION OF page_views
  FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

CREATE INDEX idx_page_views_site_date ON page_views(site_id, viewed_at DESC);
CREATE INDEX idx_page_views_path ON page_views(site_id, page_path, viewed_at DESC);

search_queries

Search analytics (privacy-friendly).

CREATE TABLE search_queries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  
  -- Query information
  query_text VARCHAR(500) NOT NULL,
  results_count INTEGER,
  
  -- User action
  clicked_result_path VARCHAR(500),  -- Which result was clicked
  clicked_position INTEGER,          -- Position in results
  
  -- Session (privacy-friendly)
  session_hash VARCHAR(64),
  
  -- Performance
  search_duration_ms INTEGER,
  
  -- Timestamp
  searched_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_search_queries_site_date ON search_queries(site_id, searched_at DESC);
CREATE INDEX idx_search_queries_text ON search_queries(site_id, query_text, searched_at DESC);

api_tokens

API access tokens for programmatic access.

CREATE TABLE api_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Token
  name VARCHAR(255) NOT NULL,
  token_hash VARCHAR(255) UNIQUE NOT NULL,  -- Never store plaintext
  token_prefix VARCHAR(10) NOT NULL,        -- Show user: "ed_prod_abc..."
  
  -- Permissions
  scopes TEXT[] DEFAULT '{}',  -- ['read:docs', 'write:docs', 'deploy']
  
  -- Usage tracking
  last_used_at TIMESTAMP,
  usage_count INTEGER DEFAULT 0,
  
  -- Expiration
  expires_at TIMESTAMP,
  
  -- Status
  status VARCHAR(20) DEFAULT 'active',  -- 'active', 'revoked'
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  revoked_at TIMESTAMP,
  
  -- Constraints
  CONSTRAINT valid_status CHECK (status IN ('active', 'revoked'))
);

CREATE INDEX idx_api_tokens_site ON api_tokens(site_id, status);
CREATE INDEX idx_api_tokens_user ON api_tokens(user_id, status);
CREATE INDEX idx_api_tokens_hash ON api_tokens(token_hash) WHERE status = 'active';

subscriptions

Premium subscriptions and billing.

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  
  -- Plan details
  plan_type VARCHAR(50) NOT NULL,  -- 'hobby', 'pro', 'team', 'enterprise'
  billing_interval VARCHAR(20) NOT NULL,  -- 'monthly', 'yearly'
  
  -- Pricing
  amount_cents INTEGER NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD',
  
  -- Stripe integration
  stripe_customer_id VARCHAR(255),
  stripe_subscription_id VARCHAR(255),
  stripe_payment_method_id VARCHAR(255),
  
  -- Limits
  max_sites INTEGER,
  max_page_views_monthly INTEGER,
  max_team_members INTEGER,
  
  -- Status
  status VARCHAR(20) NOT NULL,  -- 'active', 'trialing', 'past_due', 'cancelled', 'expired'
  
  -- Trial
  trial_ends_at TIMESTAMP,
  
  -- Billing dates
  current_period_start TIMESTAMP,
  current_period_end TIMESTAMP,
  cancel_at_period_end BOOLEAN DEFAULT FALSE,
  cancelled_at TIMESTAMP,
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  -- Constraints
  CONSTRAINT valid_plan_type CHECK (plan_type IN ('hobby', 'pro', 'team', 'enterprise')),
  CONSTRAINT valid_billing_interval CHECK (billing_interval IN ('monthly', 'yearly')),
  CONSTRAINT valid_status CHECK (status IN ('active', 'trialing', 'past_due', 'cancelled', 'expired'))
);

CREATE INDEX idx_subscriptions_user ON subscriptions(user_id, status);
CREATE INDEX idx_subscriptions_stripe ON subscriptions(stripe_subscription_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status, current_period_end);

invoices

Billing invoices.

CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  subscription_id UUID NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id),
  
  -- Invoice details
  invoice_number VARCHAR(50) UNIQUE NOT NULL,
  amount_cents INTEGER NOT NULL,
  currency VARCHAR(3) DEFAULT 'USD',
  
  -- Stripe
  stripe_invoice_id VARCHAR(255),
  stripe_payment_intent_id VARCHAR(255),
  
  -- Status
  status VARCHAR(20) NOT NULL,  -- 'draft', 'open', 'paid', 'void', 'uncollectible'
  paid_at TIMESTAMP,
  
  -- Dates
  period_start TIMESTAMP NOT NULL,
  period_end TIMESTAMP NOT NULL,
  due_date TIMESTAMP,
  
  -- PDF
  pdf_url TEXT,
  
  -- Timestamps
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  -- Constraints
  CONSTRAINT valid_status CHECK (status IN ('draft', 'open', 'paid', 'void', 'uncollectible'))
);

CREATE INDEX idx_invoices_subscription ON invoices(subscription_id, created_at DESC);
CREATE INDEX idx_invoices_user ON invoices(user_id, created_at DESC);
CREATE INDEX idx_invoices_status ON invoices(status, due_date);

audit_logs

Audit trail for compliance.

CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Actor
  user_id UUID REFERENCES users(id),
  ip_address INET,
  user_agent TEXT,
  
  -- Action
  action VARCHAR(100) NOT NULL,  -- 'site.created', 'deployment.triggered', etc.
  resource_type VARCHAR(50),     -- 'site', 'deployment', 'user'
  resource_id UUID,
  
  -- Changes
  old_values JSONB,
  new_values JSONB,
  
  -- Context
  metadata JSONB DEFAULT '{}',
  
  -- Timestamp
  created_at TIMESTAMP DEFAULT NOW()
);

-- Partitioned by month
CREATE TABLE audit_logs_2024_12 PARTITION OF audit_logs
  FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');

CREATE INDEX idx_audit_logs_user ON audit_logs(user_id, created_at DESC);
CREATE INDEX idx_audit_logs_resource ON audit_logs(resource_type, resource_id, created_at DESC);
CREATE INDEX idx_audit_logs_action ON audit_logs(action, created_at DESC);

Prisma Schema

// 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
  emailVerified Boolean   @default(false) @map("email_verified")
  name          String?
  avatarUrl     String?   @map("avatar_url")
  passwordHash  String?   @map("password_hash")
  
  oauthProvider String?   @map("oauth_provider")
  oauthId       String?   @map("oauth_id")
  
  status        String    @default("active")
  role          String    @default("user")
  
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")
  lastLoginAt   DateTime? @map("last_login_at")
  deletedAt     DateTime? @map("deleted_at")
  
  sites         Site[]
  teamMembers   TeamMember[]
  deployments   Deployment[]
  apiTokens     ApiToken[]
  subscriptions Subscription[]
  invoices      Invoice[]
  auditLogs     AuditLog[]
  
  @@map("users")
}

model Site {
  id          String    @id @default(uuid())
  ownerId     String    @map("owner_id")
  owner       User      @relation(fields: [ownerId], references: [id], onDelete: Cascade)
  
  name        String
  slug        String    @unique
  customDomain String?  @map("custom_domain")
  
  gitProvider String?  @map("git_provider")
  gitRepoUrl  String?  @map("git_repo_url")
  gitBranch   String   @default("main") @map("git_branch")
  gitWebhookSecret String? @map("git_webhook_secret")
  
  docsPath    String   @default("./docs") @map("docs_path")
  buildCommand String? @map("build_command")
  
  config      Json     @default("{}")
  
  deploymentStatus String @default("pending") @map("deployment_status")
  lastDeployedAt   DateTime? @map("last_deployed_at")
  lastBuildDurationMs Int? @map("last_build_duration_ms")
  
  analyticsEnabled Boolean @default(true) @map("analytics_enabled")
  searchAnalyticsEnabled Boolean @default(true) @map("search_analytics_enabled")
  
  status      String   @default("active")
  
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
  deletedAt   DateTime? @map("deleted_at")
  
  teamMembers TeamMember[]
  deployments Deployment[]
  pageViews   PageView[]
  searchQueries SearchQuery[]
  apiTokens   ApiToken[]
  
  @@map("sites")
}

model TeamMember {
  id         String    @id @default(uuid())
  siteId     String    @map("site_id")
  site       Site      @relation(fields: [siteId], references: [id], onDelete: Cascade)
  userId     String    @map("user_id")
  user       User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  role       String
  permissions Json    @default("{}")
  
  invitedBy  String?  @map("invited_by")
  invitedAt  DateTime @default(now()) @map("invited_at")
  acceptedAt DateTime? @map("accepted_at")
  
  status     String   @default("active")
  
  createdAt  DateTime @default(now()) @map("created_at")
  updatedAt  DateTime @updatedAt @map("updated_at")
  
  @@unique([siteId, userId])
  @@map("team_members")
}

model Deployment {
  id          String    @id @default(uuid())
  siteId      String    @map("site_id")
  site        Site      @relation(fields: [siteId], references: [id], onDelete: Cascade)
  
  triggeredBy String?   @map("triggered_by")
  trigger     User?     @relation(fields: [triggeredBy], references: [id])
  triggerType String    @map("trigger_type")
  
  commitSha   String?   @map("commit_sha")
  commitMessage String? @map("commit_message")
  branch      String?
  
  status      String
  buildLog    String?   @map("build_log")
  errorMessage String?  @map("error_message")
  
  buildDurationMs Int?  @map("build_duration_ms")
  pagesBuilt   Int?     @map("pages_built")
  assetsSizeBytes BigInt? @map("assets_size_bytes")
  
  startedAt   DateTime  @default(now()) @map("started_at")
  completedAt DateTime? @map("completed_at")
  
  @@map("deployments")
}

model PageView {
  id          String    @id @default(uuid())
  siteId      String    @map("site_id")
  site        Site      @relation(fields: [siteId], references: [id], onDelete: Cascade)
  
  pagePath    String    @map("page_path")
  pageTitle   String?   @map("page_title")
  
  sessionHash String?   @map("session_hash")
  
  referrerDomain String? @map("referrer_domain")
  referrerPath String?   @map("referrer_path")
  utmSource   String?   @map("utm_source")
  utmMedium   String?   @map("utm_medium")
  utmCampaign String?   @map("utm_campaign")
  
  deviceType  String?   @map("device_type")
  browser     String?
  os          String?
  
  countryCode String?   @map("country_code")
  
  timeOnPageSeconds Int? @map("time_on_page_seconds")
  scrollDepthPercent Int? @map("scroll_depth_percent")
  
  viewedAt    DateTime  @default(now()) @map("viewed_at")
  
  @@map("page_views")
}

model SearchQuery {
  id          String    @id @default(uuid())
  siteId      String    @map("site_id")
  site        Site      @relation(fields: [siteId], references: [id], onDelete: Cascade)
  
  queryText   String    @map("query_text")
  resultsCount Int?     @map("results_count")
  
  clickedResultPath String? @map("clicked_result_path")
  clickedPosition Int?    @map("clicked_position")
  
  sessionHash String?   @map("session_hash")
  
  searchDurationMs Int?  @map("search_duration_ms")
  
  searchedAt  DateTime  @default(now()) @map("searched_at")
  
  @@map("search_queries")
}

model ApiToken {
  id          String    @id @default(uuid())
  siteId      String    @map("site_id")
  site        Site      @relation(fields: [siteId], references: [id], onDelete: Cascade)
  userId      String    @map("user_id")
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  name        String
  tokenHash   String    @unique @map("token_hash")
  tokenPrefix String    @map("token_prefix")
  
  scopes      String[]  @default([])
  
  lastUsedAt  DateTime? @map("last_used_at")
  usageCount  Int       @default(0) @map("usage_count")
  
  expiresAt   DateTime? @map("expires_at")
  
  status      String    @default("active")
  
  createdAt   DateTime  @default(now()) @map("created_at")
  revokedAt   DateTime? @map("revoked_at")
  
  @@map("api_tokens")
}

model Subscription {
  id          String    @id @default(uuid())
  userId      String    @map("user_id")
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  
  planType    String    @map("plan_type")
  billingInterval String @map("billing_interval")
  
  amountCents Int       @map("amount_cents")
  currency    String    @default("USD")
  
  stripeCustomerId String? @map("stripe_customer_id")
  stripeSubscriptionId String? @map("stripe_subscription_id")
  stripePaymentMethodId String? @map("stripe_payment_method_id")
  
  maxSites    Int?      @map("max_sites")
  maxPageViewsMonthly Int? @map("max_page_views_monthly")
  maxTeamMembers Int?   @map("max_team_members")
  
  status      String
  
  trialEndsAt DateTime? @map("trial_ends_at")
  
  currentPeriodStart DateTime? @map("current_period_start")
  currentPeriodEnd DateTime? @map("current_period_end")
  cancelAtPeriodEnd Boolean @default(false) @map("cancel_at_period_end")
  cancelledAt DateTime? @map("cancelled_at")
  
  createdAt   DateTime  @default(now()) @map("created_at")
  updatedAt   DateTime  @updatedAt @map("updated_at")
  
  invoices    Invoice[]
  
  @@map("subscriptions")
}

model Invoice {
  id            String    @id @default(uuid())
  subscriptionId String   @map("subscription_id")
  subscription  Subscription @relation(fields: [subscriptionId], references: [id], onDelete: Cascade)
  userId        String    @map("user_id")
  user          User      @relation(fields: [userId], references: [id])
  
  invoiceNumber String    @unique @map("invoice_number")
  amountCents   Int       @map("amount_cents")
  currency      String    @default("USD")
  
  stripeInvoiceId String? @map("stripe_invoice_id")
  stripePaymentIntentId String? @map("stripe_payment_intent_id")
  
  status        String
  paidAt        DateTime? @map("paid_at")
  
  periodStart   DateTime  @map("period_start")
  periodEnd     DateTime  @map("period_end")
  dueDate       DateTime? @map("due_date")
  
  pdfUrl        String?   @map("pdf_url")
  
  createdAt     DateTime  @default(now()) @map("created_at")
  updatedAt     DateTime  @updatedAt @map("updated_at")
  
  @@map("invoices")
}

model AuditLog {
  id          String    @id @default(uuid())
  
  userId      String?   @map("user_id")
  user        User?     @relation(fields: [userId], references: [id])
  ipAddress   String?   @map("ip_address")
  userAgent   String?   @map("user_agent")
  
  action      String
  resourceType String?  @map("resource_type")
  resourceId  String?   @map("resource_id")
  
  oldValues   Json?     @map("old_values")
  newValues   Json?     @map("new_values")
  
  metadata    Json      @default("{}")
  
  createdAt   DateTime  @default(now()) @map("created_at")
  
  @@map("audit_logs")
}

Data Retention Policies

Analytics Data:

  • Page views: Retained for 24 months
  • Search queries: Retained for 12 months
  • Aggregated data: Retained indefinitely

Audit Logs:

  • Retained for 7 years (compliance)
  • Partitioned monthly for performance

Soft Deletes:

  • User accounts: Permanently deleted after 30 days
  • Sites: Permanently deleted after 30 days
  • Backups retained for 90 days post-deletion

GDPR Compliance:

  • User data export API
  • Complete data deletion on request
  • No PII in analytics (hashed sessions only)

Performance Optimizations

Indexes:

  • Primary keys on all tables (UUID)
  • Foreign key indexes
  • Query-specific composite indexes
  • Partial indexes for active records

Partitioning:

  • page_views partitioned by month
  • audit_logs partitioned by month
  • Automatic partition creation

Caching Strategy:

  • Redis cache for frequently accessed data
  • Cache site configs (1 hour TTL)
  • Cache user permissions (5 minutes TTL)
  • Invalidate on write

Query Optimization:

  • Use SELECT specific columns (no SELECT *)
  • Batch inserts for analytics
  • Prepared statements
  • Connection pooling

Security Measures

Authentication:

  • Bcrypt for password hashing (12 rounds)
  • JWT tokens for session management
  • OAuth 2.0 for social login
  • MFA support (TOTP)

API Security:

  • HMAC-SHA256 for webhook signatures
  • Rate limiting per user/IP
  • API token rotation
  • Scope-based permissions

Data Security:

  • Encryption at rest (AES-256)
  • Encryption in transit (TLS 1.3)
  • Database credentials in secrets manager
  • Regular security audits

Privacy:

  • No PII in logs
  • Hashed session identifiers
  • IP address truncation (last octet removed)
  • GDPR-compliant data handling

Backup Strategy

Automated Backups:

  • Daily full backups
  • Hourly incremental backups
  • Point-in-time recovery (7 days)
  • Geographic replication

Disaster Recovery:

  • RPO (Recovery Point Objective): 1 hour
  • RTO (Recovery Time Objective): 4 hours
  • Failover to replica in different region
  • Regular recovery drills

Monitoring & Alerts

Database Metrics:

  • Connection pool usage
  • Query performance (slow query log)
  • Disk space utilization
  • Replication lag

Application Metrics:

  • API response times
  • Error rates
  • User activity
  • Resource utilization

Alerts:

  • Failed deployments
  • Payment failures
  • Unusual activity patterns
  • System degradation

This database schema supports all premium features while maintaining strong security, privacy, and performance standards.