makrdevM
Convex Community•8mo ago•
3 replies
makrdev

Scalable Design for Transactions

Hey team, I’d love to get your opinion on our transaction table design and its scalability using the Aggregate component. Here’s the schema:

export const transactionSchema = v.object({
  amount: v.number(), // Positive for additions, negative for usage/deductions
  type: v.union(
    // Credit additions
    v.literal("trial"),
    v.literal("subscription"),
    v.literal("topup"),
    v.literal("gift"),
    // Usage
    v.literal("usage"),
    // Adjustments
    v.literal("adjustment"),
    v.literal("refund")
  ),
  periodStart: v.string(), // "YYYY-MM" format - billing period when credits were allocated
  expiresAt: v.optional(v.string()), // ISO string - when credits expire
  // Relations
  workspaceId: v.id("workspaces"),
  customerId: v.id("customers"),
  createdBy: v.optional(v.id("users")),
  subscriptionId: v.optional(v.id("stripeSubscriptions")),
  // System fields
  reason: v.optional(v.string()), // Human readable reason for the credit transaction
  metadata: v.optional(v.record(v.string(), v.any())), // Additional data
  idempotencyKey: v.optional(v.string()), // For preventing duplicate transactions
  updatedAt: v.string(), // ISO string - last update time
});


We’re planning to use the Aggregate component to calculate the current balance, filtering by workspace (using namespaces) and leveraging periodStart and expiresAt as keys to sum the current period’s transactions.

Each workspace averages 5 seats, with 500 credits per user, resulting in about 2,500 transactions per month per workspace (each transaction being a document).

Do you think this is scalable? Since each namespace isolates its data and lookups are O(log(n)), it feels like it should handle this load fine, but I’d really appreciate your thoughts!
Was this page helpful?