Convex Database Schema

Complete reference for the Dashtray database schema.

Overview

Dashtray uses Convex as its serverless database and backend. The schema is defined in src/convex/schema.ts and includes tables for projects, integrations, metrics, dashboards, alerts, and more.

Schema Design Principles

Type-safe: All fields are strongly typed with Convex validators
Indexed: Common query patterns are optimized with indexes
Normalized: Related data is properly linked with IDs
Timestamped: All tables include creation timestamps
Soft deletes: Status fields used instead of hard deletes where appropriate

Core Tables

projects

Stores project information and subscription details.

{
  _id: Id<"projects">,
  name: string,                    // Project display name
  slug: string,                    // URL-friendly unique identifier
  ownerId: string,                 // References betterAuth user._id
  subscriptionTier: "free" | "pro" | "scale" | "agency",
  subscriptionStatus: "active" | "canceled" | "past_due" | "trialing" | "expired" | "payment_failed",
  trialEndsAt?: number,            // Unix timestamp
  subscriptionEndsAt?: number,     // Unix timestamp
  subscriptionStartDate?: number,  // Unix timestamp
  subscriptionEndDate?: number,    // Unix timestamp
  subscriptionCancelledAt?: number,// Unix timestamp
  lastPaymentDate?: number,        // Unix timestamp
  nextPaymentDate?: number,        // Unix timestamp
  dodoCustomerId?: string,         // DodoPayments customer ID
  dodoSubscriptionId?: string,     // DodoPayments subscription ID
  customLogo?: string,             // Storage URL for custom logo (Scale+)
  customDomain?: string,           // Custom domain (Agency only)
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_slug: Fast lookup by slug
by_owner: Find all projects for a user
by_subscription_tier: Query by tier
by_subscription_status: Query by status

Usage:

// Get project by slug
const project = await ctx.db
  .query("projects")
  .withIndex("by_slug", q => q.eq("slug", "my-project"))
  .unique();

// Get all projects for a user
const projects = await ctx.db
  .query("projects")
  .withIndex("by_owner", q => q.eq("ownerId", userId))
  .collect();

projectMembers

Stores team member relationships and roles.

{
  _id: Id<"projectMembers">,
  projectId: Id<"projects">,
  userId: string,                  // References betterAuth user._id
  role: "owner" | "editor" | "viewer",
  invitedBy: string,               // References betterAuth user._id
  invitedAt: number,               // Unix timestamp
  acceptedAt?: number,             // Unix timestamp (undefined if pending)
  status: "pending" | "active" | "removed"
}

Indexes:

by_project: Get all members for a project
by_user: Get all projects for a user
by_project_and_user: Check membership
by_status: Query by status

Usage:

// Get all members for a project
const members = await ctx.db
  .query("projectMembers")
  .withIndex("by_project", q => q.eq("projectId", projectId))
  .filter(q => q.eq(q.field("status"), "active"))
  .collect();

// Check if user is a member
const membership = await ctx.db
  .query("projectMembers")
  .withIndex("by_project_and_user", q =>
    q.eq("projectId", projectId).eq("userId", userId)
  )
  .unique();

connections

Stores integration connections and credentials.

{
  _id: Id<"connections">,
  projectId: Id<"projects">,
  service: string,                 // "stripe", "github", etc.
  serviceName: string,             // "Stripe", "GitHub", etc.
  category: "payment" | "development" | "analytics" | "marketing" | "communication",
  encryptedCredentials: string,    // AES-256-GCM encrypted JSON
  status: "active" | "error" | "disconnected",
  lastSyncedAt?: number,           // Unix timestamp
  nextSyncAt?: number,             // Unix timestamp
  lastError?: string,              // Error message if status is "error"
  healthScore: number,             // 0-100
  accountInfo?: any,               // Service-specific metadata
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_project: Get all connections for a project
by_service: Query by service type
by_status: Query by status
by_project_and_service: Check if service is connected

Usage:

// Get all active connections for a project
const connections = await ctx.db
  .query("connections")
  .withIndex("by_project", q => q.eq("projectId", projectId))
  .filter(q => q.eq(q.field("status"), "active"))
  .collect();

// Check if Stripe is connected
const stripeConnection = await ctx.db
  .query("connections")
  .withIndex("by_project_and_service", q =>
    q.eq("projectId", projectId).eq("service", "stripe")
  )
  .unique();

metrics

Stores time series metric data from integrations.

{
  _id: Id<"metrics">,
  projectId: Id<"projects">,
  connectionId: Id<"connections">,
  service: string,                 // "stripe", "github", etc.
  metricId: string,                // "stripe_mrr", "github_commits", etc.
  metricName: string,              // "Monthly Recurring Revenue", "Commits", etc.
  value: number,                   // Numeric value
  unit?: string,                   // "USD", "count", "%", etc.
  timestamp: number,               // Unix timestamp of the metric
  metadata?: any,                  // Additional metric-specific data
  createdAt: number                // Unix timestamp when stored
}

Indexes:

by_project: Get all metrics for a project
by_connection: Get metrics for a connection
by_timestamp: Query by time
by_project_and_metric: Get specific metric for a project
by_project_and_timestamp: Time-based queries for a project
by_metric_and_timestamp: Time series for a specific metric

Usage:

// Get latest MRR value
const latestMRR = await ctx.db
  .query("metrics")
  .withIndex("by_project_and_metric", q =>
    q.eq("projectId", projectId).eq("metricId", "stripe_mrr")
  )
  .order("desc")
  .first();

// Get metrics for last 30 days
const thirtyDaysAgo = Date.now() - 30 * 24 * 60 * 60 * 1000;
const recentMetrics = await ctx.db
  .query("metrics")
  .withIndex("by_project_and_timestamp", q =>
    q.eq("projectId", projectId).gte("timestamp", thirtyDaysAgo)
  )
  .collect();

dashboards

Stores dashboard configurations.

{
  _id: Id<"dashboards">,
  projectId: Id<"projects">,
  name: string,                    // Dashboard display name
  slug: string,                    // URL-friendly identifier
  type: "custom" | "category" | "overview",
  category?: "payment" | "development" | "analytics" | "marketing" | "communication",
  isDefault: boolean,              // Is this a default dashboard?
  createdBy: string,               // References betterAuth user._id
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_project: Get all dashboards for a project
by_slug: Lookup by slug
by_type: Query by type
by_project_and_type: Get dashboards of a specific type for a project

Usage:

// Get all custom dashboards for a project
const customDashboards = await ctx.db
  .query("dashboards")
  .withIndex("by_project_and_type", q =>
    q.eq("projectId", projectId).eq("type", "custom")
  )
  .collect();

// Get dashboard by slug
const dashboard = await ctx.db
  .query("dashboards")
  .withIndex("by_slug", q => q.eq("slug", "executive-summary"))
  .unique();

widgets

Stores widget configurations for dashboards.

{
  _id: Id<"widgets">,
  dashboardId: Id<"dashboards">,
  projectId: Id<"projects">,
  type: "metric" | "chart" | "table" | "text",
  title: string,                   // Widget display title
  position: {
    x: number,                     // Grid column (0-11)
    y: number,                     // Grid row
    w: number,                     // Width in columns (1-12)
    h: number                      // Height in rows
  },
  visible: boolean,                // Is widget visible?
  config: {
    metricId?: string,             // Metric to display
    connectionId?: Id<"connections">,
    chartType?: "line" | "bar" | "area" | "pie",
    timeRange?: "24h" | "7d" | "30d" | "90d" | "1y" | "all",
    aggregation?: "sum" | "avg" | "min" | "max" | "count",
    filters?: any,                 // Custom filters
    markdown?: string              // For text widgets
  },
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_dashboard: Get all widgets for a dashboard
by_project: Get all widgets for a project

Usage:

// Get all widgets for a dashboard
const widgets = await ctx.db
  .query("widgets")
  .withIndex("by_dashboard", q => q.eq("dashboardId", dashboardId))
  .filter(q => q.eq(q.field("visible"), true))
  .collect();

bookmarks

Stores external service bookmarks.

{
  _id: Id<"bookmarks">,
  projectId: Id<"projects">,
  title: string,                   // Bookmark display title
  url: string,                     // External URL
  favicon?: string,                // Favicon URL
  createdBy: string,               // References betterAuth user._id
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_project: Get all bookmarks for a project

alerts

Stores alert configurations.

{
  _id: Id<"alerts">,
  projectId: Id<"projects">,
  name: string,                    // Alert display name
  enabled: boolean,                // Is alert active?
  condition: {
    metricId: string,              // Metric to monitor
    connectionId: Id<"connections">,
    operator: "<" | ">" | "<=" | ">=" | "==" | "!=",
    threshold: number,             // Value to compare against
    timeRange: "1h" | "24h" | "7d" | "30d"
  },
  notifications: {
    inApp: boolean,                // Always true
    slack?: { webhookUrl: string },
    discord?: { webhookUrl: string },
    teams?: { webhookUrl: string }
  },
  lastTriggeredAt?: number,        // Unix timestamp
  createdBy: string,               // References betterAuth user._id
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_project: Get all alerts for a project
by_enabled: Query enabled alerts

Usage:

// Get all enabled alerts
const enabledAlerts = await ctx.db
  .query("alerts")
  .withIndex("by_enabled", q => q.eq("enabled", true))
  .collect();

alertLogs

Stores alert trigger history.

{
  _id: Id<"alertLogs">,
  alertId: Id<"alerts">,
  projectId: Id<"projects">,
  triggeredAt: number,             // Unix timestamp
  value: number,                   // Metric value that triggered alert
  threshold: number,               // Threshold that was crossed
  notificationsSent: string[],     // ["inApp", "slack", "discord"]
  acknowledged: boolean,           // Has alert been reviewed?
  acknowledgedBy?: string,         // References betterAuth user._id
  acknowledgedAt?: number          // Unix timestamp
}

Indexes:

by_alert: Get logs for an alert
by_project: Get all logs for a project
by_triggered_at: Query by time

auditLogs

Stores audit trail for Scale+ users.

{
  _id: Id<"auditLogs">,
  projectId: Id<"projects">,
  userId: string,                  // References betterAuth user._id
  action: string,                  // "dashboard.create", "connection.delete", etc.
  resource: string,                // "dashboard", "connection", etc.
  resourceId: string,              // ID of the affected resource
  metadata?: any,                  // Additional context
  ipAddress?: string,              // User's IP address
  userAgent?: string,              // User's browser/client
  createdAt: number                // Unix timestamp
}

Indexes:

by_project: Get all logs for a project
by_user: Get logs for a user
by_created_at: Query by time
by_project_and_created_at: Time-based queries for a project

aiConversations

Stores AI chat conversations.

{
  _id: Id<"aiConversations">,
  projectId: Id<"projects">,
  userId: string,                  // References betterAuth user._id
  messages: Array<{
    role: "user" | "assistant",
    content: string,
    timestamp: number              // Unix timestamp
  }>,
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_project: Get all conversations for a project
by_user: Get conversations for a user
by_project_and_user: Get user’s conversations in a project

userSettings

Stores user-specific settings (AI keys, preferences).

{
  _id: Id<"userSettings">,
  userId: string,                  // References betterAuth user._id
  aiApiKey?: string,               // Encrypted AI API key
  aiProvider?: "openai" | "anthropic",
  createdAt: number,               // Unix timestamp
  updatedAt: number                // Unix timestamp
}

Indexes:

by_user: Get settings for a user

apiKeys

Stores API keys for Scale+ users.

{
  _id: Id<"apiKeys">,
  projectId: Id<"projects">,
  name: string,                    // User-friendly name
  keyHash: string,                 // SHA-256 hash of the key
  keyPrefix: string,               // First 8 chars for display (e.g., "pp_live_")
  lastUsedAt?: number,             // Unix timestamp
  expiresAt?: number,              // Unix timestamp
  createdBy: string,               // References betterAuth user._id
  createdAt: number,               // Unix timestamp
  revokedAt?: number,              // Unix timestamp
  revokedBy?: string               // References betterAuth user._id
}

Indexes:

by_project: Get all keys for a project
by_key_hash: Authenticate API requests
by_created_by: Get keys created by a user

apiUsage

Tracks API usage for rate limiting.

{
  _id: Id<"apiUsage">,
  projectId: Id<"projects">,
  apiKeyId: Id<"apiKeys">,
  endpoint: string,                // "/v1/metrics"
  method: string,                  // "GET", "POST", etc.
  statusCode: number,              // HTTP status code
  timestamp: number,               // Unix timestamp
  responseTime: number             // Milliseconds
}

Indexes:

by_project: Get usage for a project
by_api_key: Get usage for a key
by_timestamp: Query by time
by_project_and_timestamp: Time-based queries for a project

Better Auth Tables

Authentication tables are defined in src/convex/betterAuth/schema.ts:

user: User accounts
session: Active sessions
account: OAuth accounts
verification: Email verification tokens

DodoPayments Tables

Billing tables are managed by the DodoPayments Convex component:

Customers
Subscriptions
Payments
Invoices

Data Retention

Free Tier

Metrics: 3 days
Alert logs: 3 days
AI conversations: 3 days
Audit logs: Not available

Pro+ Tiers

Metrics: Unlimited
Alert logs: Unlimited
AI conversations: Unlimited
Audit logs: Unlimited (Scale+ only)

Best Practices

Querying

Use indexes: Always use the appropriate index for your query
Filter efficiently: Apply filters after index queries
Limit results: Use .first() or .take(n) when appropriate
Paginate: Use cursor-based pagination for large result sets

Writing

Validate input: Use Convex validators in function args
Check permissions: Verify user has access before mutations
Update timestamps: Always update updatedAt on modifications
Soft delete: Use status fields instead of deleting records

Performance

Batch operations: Group related mutations
Avoid N+1 queries: Fetch related data efficiently
Cache when appropriate: Use Convex’s built-in caching
Monitor query performance: Use Convex dashboard

Migration Strategy

When schema changes are needed:

Add new fields as optional: Use v.optional() for new fields
Backfill data: Write migration scripts if needed
Deprecate old fields: Mark as optional, remove later
Test thoroughly: Verify in development before deploying

Need Help?

Convex Documentation: docs.convex.dev
Schema Best Practices: docs.convex.dev/database/schemas
Indexes Guide: docs.convex.dev/database/indexes

Developer

Convex schema

Convex Database Schema

Overview

Schema Design Principles

Core Tables

projects

projectMembers

connections

metrics

dashboards

widgets

bookmarks

alerts

alertLogs

auditLogs

aiConversations

userSettings

apiKeys

apiUsage

Better Auth Tables

DodoPayments Tables

Data Retention

Free Tier

Pro+ Tiers

Best Practices

Querying

Writing

Performance

Migration Strategy

Need Help?

Developer

​Convex Database Schema

​Overview

​Schema Design Principles

​Core Tables

​projects

​projectMembers

​connections

​metrics

​dashboards

​widgets

​bookmarks

​alerts

​alertLogs

​auditLogs

​aiConversations

​userSettings

​apiKeys

​apiUsage

​Related Schemas

​Better Auth Tables

​DodoPayments Tables

​Data Retention

​Free Tier

​Pro+ Tiers

​Best Practices

​Querying

​Writing

​Performance

​Migration Strategy

​Need Help?

Convex Database Schema

Overview

Schema Design Principles

Core Tables

projects

projectMembers

connections

metrics

dashboards

widgets

bookmarks

alerts

alertLogs

auditLogs

aiConversations

userSettings

apiKeys

apiUsage

Related Schemas

Better Auth Tables

DodoPayments Tables

Data Retention

Free Tier

Pro+ Tiers

Best Practices

Querying

Writing

Performance

Migration Strategy

Need Help?