makrdev•3mo ago

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
});

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!

3 Replies

Convex Bot•3mo ago

Thanks for posting in <#1088161997662724167>. Reminder: If you have a Convex Pro account, use the Convex Dashboard to file support tickets. - Provide context: What are you trying to achieve, what is the end-user interaction, what are you seeing? (full error message, command output, etc.) - Use search.convex.dev to search Docs, Stack, and Discord all at once. - Additionally, you can post your questions in the Convex Community's <#1228095053885476985> channel to receive a response from AI. - Avoid tagging staff unless specifically instructed. Thank you!

makrdevOP•3mo ago

Up??

ian•2mo ago

Yes I think your analysis is good - it'll work well and given that writes to each namespace are < 1/s I wouldn't expect contention, and it should be very efficient at those sums over those time periods

Scalable Design for Transactions

Did you find this page helpful?