Database Schema

Database Schema Overview

Zzyra uses PostgreSQL with Prisma ORM for data persistence. The schema is designed to support all platform features including user management, workflow execution, block system, and billing.

Schema Architecture

User Management

User

Profile

Team

TeamMember

Workflow System

Workflow

WorkflowExecution

NodeExecution

ExecutionLog

Block System

BlockLibrary

CustomBlock

BlockVersion

Billing & Notifications

Subscription

PricingTier

Notification

NotificationPreference

Core Models

User Management

User Model
Profile Model
Team Model

model User {
  id                String   @id @default(cuid())
  email             String   @unique
  magicDid          String?  @unique
  walletAddress     String?  @unique
  isActive          Boolean  @default(true)
  emailVerified     Boolean  @default(false)
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

// Relations
profile Profile?
teams TeamMember[]
workflows Workflow[]
executions WorkflowExecution[]
subscriptions Subscription[]
notifications Notification[]
notificationPrefs NotificationPreference[]

@@map("users")
}

model Profile {
  id          String   @id @default(cuid())
  userId      String   @unique
  firstName   String?
  lastName    String?
  avatar      String?
  bio         String?
  website     String?
  twitter     String?
  github      String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

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

  @@map("profiles")
}

model Team {
  id          String   @id @default(cuid())
  name        String
  description String?
  slug        String   @unique
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations
  members     TeamMember[]
  workflows   Workflow[]

  @@map("teams")
}

Workflow System

Workflow Model
WorkflowExecution Model
NodeExecution Model

model Workflow {
  id          String   @id @default(cuid())
  name        String
  description String?
  version     Int      @default(1)
  isActive    Boolean  @default(true)
  isPublic    Boolean  @default(false)
  config      Json     // Workflow configuration
  nodes       Json     // Workflow nodes and connections
  userId      String
  teamId      String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

// Relations
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
team Team? @relation(fields: [teamId], references: [id])
executions WorkflowExecution[]

@@map("workflows")
}

model WorkflowExecution {
  id          String   @id @default(cuid())
  workflowId  String
  userId      String
  status      ExecutionStatus @default(PENDING)
  startedAt   DateTime?
  completedAt DateTime?
  error       String?
  result      Json?
  metadata    Json?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations
  workflow    Workflow @relation(fields: [workflowId], references: [id], onDelete: Cascade)
  user        User     @relation(fields: [userId], references: [id])
  nodeExecutions NodeExecution[]

  @@map("workflow_executions")
}

model NodeExecution {
  id                String   @id @default(cuid())
  executionId       String
  nodeId            String
  nodeType          String
  status            ExecutionStatus @default(PENDING)
  startedAt         DateTime?
  completedAt       DateTime?
  input             Json?
  output            Json?
  error             String?
  metadata          Json?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  // Relations
  execution         WorkflowExecution @relation(fields: [executionId], references: [id], onDelete: Cascade)
  logs              ExecutionLog[]

  @@map("node_executions")
}

Block System

BlockLibrary Model
CustomBlock Model

model BlockLibrary {
  id          String   @id @default(cuid())
  name        String
  description String?
  category    String
  version     String   @default("1.0.0")
  config      Json     // Block configuration schema
  code        String?  // Block implementation code
  isActive    Boolean  @default(true)
  isBuiltin   Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

// Relations
versions BlockVersion[]

@@map("block_library")
}

model CustomBlock {
  id          String   @id @default(cuid())
  name        String
  description String?
  category    String
  version     String   @default("1.0.0")
  config      Json     // Block configuration
  code        String   // Block implementation
  userId      String
  isActive    Boolean  @default(true)
  isPublic    Boolean  @default(false)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

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

  @@map("custom_blocks")
}

Billing & Notifications

Subscription Model
PricingTier Model

model Subscription {
  id            String   @id @default(cuid())
  userId        String
  tierId        String
  status        SubscriptionStatus @default(ACTIVE)
  currentPeriodStart DateTime
  currentPeriodEnd   DateTime
  cancelAtPeriodEnd Boolean @default(false)
  canceledAt    DateTime?
  endedAt       DateTime?
  trialStart    DateTime?
  trialEnd      DateTime?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

// Relations
user User @relation(fields: [userId], references: [id], onDelete: Cascade)
tier PricingTier @relation(fields: [tierId], references: [id])

@@map("subscriptions")
}

model PricingTier {
  id          String   @id @default(cuid())
  name        String
  description String?
  price       Decimal  @db.Decimal(10, 2)
  currency    String   @default("USD")
  interval    BillingInterval
  features    Json     // Tier features and limits
  isActive    Boolean  @default(true)
  sortOrder   Int      @default(0)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // Relations
  subscriptions Subscription[]

  @@map("pricing_tiers")
}

Enums and Types

📋 Database Enums

ExecutionStatus

enum ExecutionStatus {
  PENDING
  RUNNING
  COMPLETED
  FAILED
  CANCELLED
  TIMEOUT
}

SubscriptionStatus

enum SubscriptionStatus {
  ACTIVE
  CANCELED
  PAST_DUE
  UNPAID
  TRIAL
}

BillingInterval

enum BillingInterval {
  MONTHLY
  YEARLY
  ONE_TIME
}

NotificationType

enum NotificationType {
  WORKFLOW_COMPLETED
  WORKFLOW_FAILED
  SYSTEM_ALERT
  BILLING_REMINDER
  SECURITY_ALERT
}

Database Relationships

🔗 Key Relationships

User Management

User1:1Profile

User1:NTeamMember

Team1:NTeamMember

Workflow System

User1:NWorkflow

Workflow1:NWorkflowExecution

WorkflowExecution1:NNodeExecution

Block System

User1:NCustomBlock

BlockLibrary1:NBlockVersion

Billing System

User1:NSubscription

PricingTier1:NSubscription

Database Operations

🗄️ Common Operations

User Management
Workflow Operations
Block Operations
Analytics Queries

// Create user with profile
const user = await prisma.user.create({
  data: {
    email: '[email protected]',
    magicDid: 'did:magic:...',
    profile: {
      create: {
        firstName: 'John',
        lastName: 'Doe'
      }
    }
  },
  include: {
    profile: true
  }
});

// Get user with all relations
const userWithData = await prisma.user.findUnique({
where: { id: userId },
include: {
profile: true,
teams: {
include: {
team: true
}
},
workflows: true,
subscriptions: {
include: {
tier: true
}
}
}
});

// Create workflow
const workflow = await prisma.workflow.create({
  data: {
    name: 'My Automation',
    description: 'Automated trading strategy',
    config: { /* workflow config */ },
    nodes: { /* workflow nodes */ },
    userId: userId
  }
});

// Execute workflow
const execution = await prisma.workflowExecution.create({
  data: {
    workflowId: workflow.id,
    userId: userId,
    status: 'PENDING'
  }
});

// Update execution status
await prisma.workflowExecution.update({
  where: { id: execution.id },
  data: {
    status: 'RUNNING',
    startedAt: new Date()
  }
});

// Create custom block
const customBlock = await prisma.customBlock.create({
  data: {
    name: "Price Alert",
    description: "Monitor cryptocurrency prices",
    category: "Trading",
    config: {
      /* block config */
    },
    code: "/* block implementation */",
    userId: userId,
  },
});

// Get block library
const blocks = await prisma.blockLibrary.findMany({
  where: {
    isActive: true,
    category: "Trading",
  },
  orderBy: {
    name: "asc",
  },
});

// Get user statistics
const userStats = await prisma.user.aggregate({
  _count: {
    workflows: true,
    executions: true,
  },
  _sum: {
    executions: {
      select: {
        // Add execution metrics
      },
    },
  },
  where: {
    id: userId,
  },
});

// Get workflow execution history
const executionHistory = await prisma.workflowExecution.findMany({
  where: {
    workflowId: workflowId,
  },
  include: {
    nodeExecutions: {
      orderBy: {
        createdAt: "desc",
      },
    },
  },
  orderBy: {
    createdAt: "desc",
  },
  take: 10,
});

Migration Management

🔄 Database Migrations

Create Migration

# Create new migration
cd packages/database
pnpm prisma migrate dev --name add_user_profile

# Apply migrations

pnpm prisma migrate deploy

# Reset database

pnpm prisma migrate reset

Schema Management

# Generate Prisma client
pnpm prisma generate

# Push schema changes
pnpm prisma db push

# Pull schema from database
pnpm prisma db pull

Best Practices

Always create migrations for schema changes
Test migrations on development database first
Use descriptive migration names
Backup production database before migrations
Review generated SQL before applying

Performance Optimization

⚡ Performance Tips

Indexing Strategy

users(email) - Unique index for email lookups
users(magicDid) - Unique index for Magic authentication
workflows(userId, isActive) - Composite index for user workflows
workflow_executions(workflowId, status) - Composite index for execution queries
node_executions(executionId, status) - Composite index for node execution queries

Query Optimization

Use select to limit returned fields
Use include for related data instead of separate queries
Implement pagination for large result sets
Use database-level filtering instead of application filtering
Consider read replicas for analytics queries

Connection Pooling

Configure appropriate connection pool size
Monitor connection usage and performance
Use connection pooling in production
Implement connection retry logic
Monitor query performance with slow query logs

Next Steps

Prisma Studio

Visual database browser and management

Repository Pattern

Data access layer implementation

Migrations Guide

Database migration strategies

The database schema is designed to be scalable and maintainable. Always use migrations for schema changes and test thoroughly before applying to production. For complex queries or performance optimization, consider using database views or stored procedures.

Getting Started

Database

Advanced

Database Schema Overview

Schema Architecture

User Management

Workflow System

Block System

Billing & Notifications

Core Models

User Management

Workflow System

Block System

Billing & Notifications

Enums and Types

📋 Database Enums

ExecutionStatus

SubscriptionStatus

BillingInterval

NotificationType

Database Relationships

🔗 Key Relationships

User Management

Workflow System

Block System

Billing System

Database Operations

🗄️ Common Operations

Migration Management

🔄 Database Migrations

Create Migration

Schema Management

Best Practices

Performance Optimization

⚡ Performance Tips

Next Steps

Prisma Studio

Repository Pattern

Migrations Guide

Getting Started

Database

Advanced

​Database Schema Overview

​Schema Architecture

​User Management

​Workflow System

​Block System

​Billing & Notifications

​Core Models

​User Management

​Workflow System

​Block System

​Billing & Notifications

​Enums and Types

​📋 Database Enums

ExecutionStatus

SubscriptionStatus

BillingInterval

NotificationType

​Database Relationships

​🔗 Key Relationships

​User Management

​Workflow System

​Block System

​Billing System

​Database Operations

​🗄️ Common Operations

​Migration Management

​🔄 Database Migrations

Create Migration

Schema Management

​Best Practices

​Performance Optimization

​⚡ Performance Tips

​Next Steps

Prisma Studio

Repository Pattern

Migrations Guide

Database Schema Overview

Schema Architecture

User Management

Workflow System

Block System

Billing & Notifications

Core Models

User Management

Workflow System

Block System

Billing & Notifications

Enums and Types

📋 Database Enums

Database Relationships

🔗 Key Relationships

User Management

Workflow System

Block System

Billing System

Database Operations

🗄️ Common Operations

Migration Management

🔄 Database Migrations

Best Practices

Performance Optimization

⚡ Performance Tips

Next Steps