Database Schema v1.0 - Official Release

[mohit-nagaraj]

Overview

We are officially establishing our MongoDB database schema v1.0 as the foundation for KubeOrch. This schema provides a flexible, extensible structure that will grow with our platform's needs.

Current Schema Structure

Collections

1. users

{
  _id: ObjectId,
  email: String,  // Unique, used for login
  password: String,  // Hashed with bcrypt
  name: String,
  role: "admin" | "user",  // First user becomes admin

  // JWT tokens
  refresh_token: String,
  refresh_token_expiry: Date,

  // Profile
  organization: String,
  avatar_url: String,
  bio: String,

  // Settings
  default_cluster_id: ObjectId,
  preferences: {
    theme: String,
    notifications: Boolean
  },

  // Security
  last_login: Date,
  login_attempts: Number,
  locked_until: Date,

  // Timestamps
  created_at: Date,
  updated_at: Date,
  deleted_at: Date  // Soft delete
}

2. workflows

{
  _id: ObjectId,
  name: String,
  description: String,
  cluster_id: String,
  status: "draft" | "published" | "archived",

  // Current workflow definition
  nodes: [
    {
      id: String,
      type: "deployment" | "service" | "condition" | "parallel",
      position: { x: Number, y: Number },
      data: {
        // For deployment nodes:
        id: String,
        templateId: String,  // e.g., "core/deployment"
        parameters: {
          image: String,
          replicas: Number,
          port: Number,
          env: Object,
          resources: {
            limits: { cpu: String, memory: String },
            requests: { cpu: String, memory: String }
          },
          labels: Object
        }
      }
    }
  ],
  edges: [
    {
      id: String,
      source: String,  // Source node ID
      target: String,  // Target node ID
      type: "default" | "success" | "failure" | "parallel"
    }
  ],

  // Version control
  versions: [
    {
      version: Number,
      nodes: Array,  // Snapshot of nodes
      edges: Array,  // Snapshot of edges
      description: String,
      created_at: Date,
      created_by: ObjectId
    }
  ],
  current_version: Number,

  // Ownership & permissions
  owner_id: ObjectId,
  team_id: String,

  // Timestamps
  created_at: Date,
  updated_at: Date,
  deleted_at: Date,
  last_run_at: Date,

  // Statistics
  run_count: Number,
  success_count: Number,
  failure_count: Number
}

3. workflow_runs

{
  _id: ObjectId,
  workflow_id: ObjectId,
  version: Number,
  status: "running" | "completed" | "failed" | "cancelled",

  // Execution timeline
  started_at: Date,
  completed_at: Date,
  duration: Number, // milliseconds

  // Execution state
  node_states: Object,  // State of each node
  output: Object,       // Execution results
  logs: Array<String>,  // Execution logs
  error: String,

  // Trigger information
  triggered_by: String,  // manual, schedule, webhook
  trigger_data: Object
}

4. clusters

{
  _id: ObjectId,
  name: String,
  display_name: String,
  description: String,
  server: String,  // Kubernetes API server URL
  auth_type: "kubeconfig" | "serviceaccount" | "token" | "certificate" | "oidc",

  // Authentication (encrypted in DB)
  credentials: {
    kubeconfig: String,      // Full kubeconfig content
    token: String,           // Bearer token
    client_cert_data: String,
    client_key_data: String,
    ca_data: String,

    // OIDC fields
    oidc_issuer_url: String,
    oidc_client_id: String,
    oidc_client_secret: String,
    oidc_refresh_token: String,
    oidc_scopes: Array<String>,

    namespace: String,
    context: String,
    insecure: Boolean
  },

  // Status & Health
  status: "connected" | "disconnected" | "unknown" | "error",
  last_check: Date,
  default: Boolean,  // Is this the default cluster for the user

  // Metadata
  labels: Object,
  annotations: Object,
  metadata: {
    version: String,         // Kubernetes version
    platform: String,        // EKS, GKE, AKS, etc.
    provider: String,
    region: String,
    node_count: Number,
    cpu_capacity: String,
    memory_capacity: String,
    storage_classes: Array<String>,
    namespaces: Array<String>,
    capabilities: Array<String>,
    last_updated: Date
  },

  // Ownership & Sharing
  user_id: ObjectId,
  org_id: ObjectId,
  shared_with: Array<ObjectId>,

  // Timestamps
  created_at: Date,
  updated_at: Date
}

5. cluster_access (For shared clusters)

{
  _id: ObjectId,
  cluster_id: ObjectId,
  user_id: ObjectId,
  role: String,
  namespaces: Array<String>,
  permissions: Array<String>,
  granted_by: ObjectId,
  granted_at: Date,
  expires_at: Date
}

6. cluster_connection_logs

{
  _id: ObjectId,
  cluster_id: ObjectId,
  user_id: ObjectId,
  action: String,
  success: Boolean,
  error: String,
  details: Object,
  timestamp: Date
}

Database Configuration

Indexes

• users.email - Unique index for fast login lookup
• workflows.owner_id - For user's workflow queries
• workflow_runs.workflow_id - For workflow history
• clusters.user_id - For user's clusters
• cluster_connection_logs - TTL index (30 days retention)

Security

• Passwords - Bcrypt hashed (never stored in plain text)
• Cluster credentials - AES-256-GCM encrypted at rest
• JWT tokens - Signed with HS256, 24h access / 30d refresh
• Soft deletes - Data marked as deleted, not removed

Key Design Decisions

1. Flexibility First

• Schema uses flexible document structure
• Can add fields without migration
• Supports nested objects for complex data

2. Version Control Built-in

• Every workflow maintains version history
• Snapshots created before each run
• Easy rollback capabilities

3. Soft Deletes

• deleted_at field for recovery
• Maintains data integrity
• Audit trail preservation

4. Extensibility

• Ready for additional collections (deployments, services, etc.)
• Supports custom metadata fields
• Plugin-friendly structure

Collection Summary

Currently in production:

• 6 collections actively used
• users - Authentication and user management
• workflows - Workflow definitions with version control
• workflow_runs - Execution history and logs
• clusters - Kubernetes cluster configurations
• cluster_access - Shared cluster permissions
• cluster_connection_logs - Cluster activity audit trail

Future Additions (Planned)

• deployments - Track individual deployments outside workflows
• audit_logs - Comprehensive audit trail for all actions
• templates - Marketplace for reusable workflow templates
• teams - Team management and role-based permissions
• schedules - Cron-based workflow triggers
• secrets - Encrypted secret management
• notifications - Alert and notification preferences

Migration Strategy

As we add new fields or collections:

1. New fields will be optional with defaults
2. Background migrations for data transformation
3. Zero-downtime upgrades
4. Backward compatibility maintained

Action Items

• ✅ Schema v1.0 is now in production
• 📝 Document any custom fields before adding
• 🔄 Weekly reviews for schema optimization needs
• 📊 Monitor collection growth and indexing

---

This schema provides the foundation for KubeOrch's growth while maintaining flexibility for future features.