budget schema

[dense fulcrum]

@coarse vigil create a schema for tracking a budget, savings, and expenses

[coarse vigil]

Hi @dense fulcrum. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

To create a schema for tracking a budget, savings, and expenses using Convex, we can define a structure using the defineSchema and defineTable functions. Here's an example of how you might structure this schema:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  budgets: defineTable({
    userId: v.id("users"),
    name: v.string(),
    amount: v.number(),
    startDate: v.number(), // Unix timestamp
    endDate: v.number(), // Unix timestamp
  }).index("by_user", ["userId"]),

  savings: defineTable({
    userId: v.id("users"),
    name: v.string(),
    targetAmount: v.number(),
    currentAmount: v.number(),
  }).index("by_user", ["userId"]),

  expenses: defineTable({
    userId: v.id("users"),
    budgetId: v.id("budgets"),
    description: v.string(),
    amount: v.number(),
    date: v.number(), // Unix timestamp
    category: v.string(),
  })
    .index("by_user", ["userId"])
    .index("by_budget", ["budgetId"])
    .index("by_date", ["date"]),
});

This schema defines three tables: budgets, savings, and expenses. Here's a breakdown of each table:

1. budgets: Tracks overall budget information.
2. savings: Manages savings goals.
3. expenses: Records individual expenses.

Each table includes a userId field to associate the data with specific users. The expenses table also includes a budgetId to link expenses to specific budgets.

Indexes are added to improve query performance. For example, you can quickly fetch all budgets, savings, or expenses for a specific user.

To use this schema, you would save it in a file like convex/schema.ts in your Convex project.

Remember to adjust the schema based on your specific requirements. You might want to add more fields or create additional tables depending on your application's needs.

For more information on defining schemas in Convex, you can refer to the Convex Schema documentation (https://docs.convex.dev/database/schemas).

Database > Schemas (https://docs.convex.dev/database/schemas)

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:

@dense fulcrum Kapa.ai is still learning and improving, please let me know how I did by reacting below.

[dense fulcrum]

@coarse vigil update this to support adding specific items to save for. the user must be able to allocate a certain amount of their savings towards each item, and it must add up to 100%

[coarse vigil]

Let me check the knowledge base for you again. One moment! :books:

---

Certainly! I'll update the schema to support adding specific items to save for, allowing users to allocate a percentage of their savings towards each item. Here's an updated version of the schema that incorporates this functionality:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  budgets: defineTable({
    userId: v.id("users"),
    name: v.string(),
    amount: v.number(),
    startDate: v.number(), // Unix timestamp
    endDate: v.number(), // Unix timestamp
  }).index("by_user", ["userId"]),

  savings: defineTable({
    userId: v.id("users"),
    name: v.string(),
    targetAmount: v.number(),
    currentAmount: v.number(),
  }).index("by_user", ["userId"]),

  savingsItems: defineTable({
    savingsId: v.id("savings"),
    name: v.string(),
    targetAmount: v.number(),
    currentAmount: v.number(),
    allocationPercentage: v.number(), // Percentage of total savings allocated to this item
  }).index("by_savings", ["savingsId"]),

  expenses: defineTable({
    userId: v.id("users"),
    budgetId: v.id("budgets"),
    description: v.string(),
    amount: v.number(),
    date: v.number(), // Unix timestamp
    category: v.string(),
  })
    .index("by_user", ["userId"])
    .index("by_budget", ["budgetId"])
    .index("by_date", ["date"]),
});

In this updated schema, I've added a new table called savingsItems. Here's an explanation of the changes:

1. The savingsItems table is introduced to store specific items that users want to save for.
2. Each savingsItem is linked to a savings record using the savingsId field.
3. The allocationPercentage field in the savingsItems table represents the percentage of the total savings allocated to this specific item.

To ensure that the allocation percentages add up to 100%, you'll need to implement this logic in your application code when creating or updating savingsItems. Here's an example of how you might structure a mutation to add or update a savings item:

import { mutation } from "./_generated/server";
import { v } from "convex/values";

export const addOrUpdateSavingsItem = mutation({
  args: {
    savingsId: v.id("savings"),
    name: v.string(),
    targetAmount: v.number(),
    allocationPercentage: v.number(),
  },
  handler: async (ctx, args) => {
    const { savingsId, name, targetAmount, allocationPercentage } = args;

    // Fetch all existing savings items for this savings record
    const existingItems = await ctx.db
      .query("savingsItems")
      .withIndex("by_savings", (q) => q.eq("savingsId", savingsId))
      .collect();

    // Calculate the total allocation percentage, including the new item
    const totalAllocation =
      existingItems.reduce((sum, item) => sum + item.allocationPercentage, 0) +
      allocationPercentage;

    // Ensure the total allocation doesn't exceed 100%
    if (totalAllocation > 100) {
      throw new Error("Total allocation percentage exceeds 100%");
    }

    // Add or update the savings item
    await ctx.db.insert("savingsItems", {
      savingsId,
      name,
      targetAmount,
      currentAmount: 0, // Initialize with 0
      allocationPercentage,
    });

    // You might want to update the main savings record here as well
  },
});

This mutation ensures that the total allocation percentage doesn't exceed 100% when adding or updating a savings item. You'll need to implement similar checks when updating or deleting savings items to maintain this constraint.

Remember to adjust your application logic to handle the distribution of savings across these items based on their allocation percentages.

Stack > Stateful Online Migrations using Mutations (https://stack.convex.dev/migrating-data-with-mutations)

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon:

@dense fulcrum Kapa.ai is still learning and improving, please let me know how I did by reacting below.

[dense fulcrum]

@coarse vigil now add support for tracking salaried income automatically. A user should be able to configure their annual salary amount and how often they're paid. Their savings should automatically update using cron jobs

[coarse vigil]

Let me check the knowledge base for you again. One moment! :books:

---

Certainly! I'll update the schema to support tracking salaried income automatically and add a cron job to update savings based on the user's salary configuration. Here's the updated schema and the necessary functions to implement this feature:

First, let's update the schema:

import { defineSchema, defineTable } from "convex/server";
import { v } from "convex/values";

export default defineSchema({
  budgets: defineTable({
    userId: v.id("users"),
    name: v.string(),
    amount: v.number(),
    startDate: v.number(), // Unix timestamp
    endDate: v.number(), // Unix timestamp
  }).index("by_user", ["userId"]),

  savings: defineTable({
    userId: v.id("users"),
    name: v.string(),
    targetAmount: v.number(),
    currentAmount: v.number(),
  }).index("by_user", ["userId"]),

  savingsItems: defineTable({
    savingsId: v.id("savings"),
    name: v.string(),
    targetAmount: v.number(),
    currentAmount: v.number(),
    allocationPercentage: v.number(),
  }).index("by_savings", ["savingsId"]),

  expenses: defineTable({
    userId: v.id("users"),
    budgetId: v.id("budgets"),
    description: v.string(),
    amount: v.number(),
    date: v.number(), // Unix timestamp
    category: v.string(),
  })
    .index("by_user", ["userId"])
    .index("by_budget", ["budgetId"])
    .index("by_date", ["date"]),

  salaryConfigs: defineTable({
    userId: v.id("users"),
    annualSalary: v.number(),
    payFrequency: v.string(), // "weekly", "biweekly", "monthly"
    lastPaymentDate: v.number(), // Unix timestamp
  }).index("by_user", ["userId"]),
});

Now, let's create the necessary functions to handle salary configuration and automatic savings updates:

1. Function to set up or update salary configuration:

import { mutation } from "./_generated/server";
import { v } from "convex/values";

export const configureSalary = mutation({
args: {
userId: v.id("users"),
annualSalary: v.number(),
payFrequency: v.union(v.literal("weekly"), v.literal("biweekly"), v.literal("monthly")),
},
handler: async (ctx, args) => {
const { userId, annualSalary, payFrequency } = args;

const existingConfig = await ctx.db
  .query("salaryConfigs")
  .withIndex("by_user", (q) => q.eq("userId", userId))
  .first();

if (existingConfig) {
  await ctx.db.patch(existingConfig._id, {
    annualSalary,
    payFrequency,
  });
} else {
  await ctx.db.insert("salaryConfigs", {
    userId,
    annualSalary,
    payFrequency,
    lastPaymentDate: Date.now(),
  });
}

},
});

2. Function to update savings based on salary:

```typescript
import { internalMutation } from "./_generated/server";

export const updateSavingsFromSalary = internalMutation({
  handler: async (ctx) => {
    const now = Date.now();
    const salaryConfigs = await ctx.db.query("salaryConfigs").collect();

    for (const config of salaryConfigs) {
      const { userId, annualSalary, payFrequency, lastPaymentDate } = config;

      let paymentInterval;
      switch (payFrequency) {
        case "weekly":
          paymentInterval = 7 * 24 * 60 * 60 * 1000; // 7 days in milliseconds
          break;
        case "biweekly":
          paymentInterval = 14 * 24 * 60 * 60 * 1000; // 14 days in milliseconds
          break;
        case "monthly":
          paymentInterval = 30 * 24 * 60 * 60 * 1000; // Approximately 30 days in milliseconds
          break;
      }

      if (now - lastPaymentDate >= paymentInterval) {
        const paymentAmount = annualSalary / (52 / (paymentInterval / (7 * 24 * 60 * 60 * 1000)));

// Update user's savings
const savings = await ctx.db
.query("savings")
.withIndex("by_user", (q) => q.eq("userId", userId))
.collect();

    for (const saving of savings) {
      await ctx.db.patch(saving._id, {
        currentAmount: saving.currentAmount + paymentAmount,
      });
    }

    // Update last payment date
    await ctx.db.patch(config._id, { lastPaymentDate: now });
  }
}

},
});

Now, to set up the cron job to automatically update savings, we need to create a `crons.ts` file in your Convex project:

```typescript
import { cronJobs } from "convex/server";
import { internal } from "./_generated/api";

const crons = cronJobs();

crons.interval(
  "update savings from salary",
  { hours: 24 }, // Run daily
  internal.salaryUpdates.updateSavingsFromSalary
);

export default crons;

This cron job will run the updateSavingsFromSalary function daily to check and update savings based on the configured salaries.

To use this system:

1. Users can configure their salary using the configureSalary mutation.
2. The cron job will automatically run daily to update savings based on the salary configurations.
3. The updateSavingsFromSalary function checks if a payment is due based on the pay frequency and last payment date, then updates the savings accordingly.

Remember to adjust the cron job frequency or add error handling as needed for your specific use case.

Scheduling > Cron Jobs (https://docs.convex.dev/scheduling/cron-jobs)
Components > Crons > Usage Part 1 (https://www.convex.dev/components/crons#usage)

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: