a confusion about schema design for storing store's profits and how to retrieve them in mongoose

Question

For displaying the (daily-weekly-monthly-annual) total profits for the store's dashboard. I want to store the data in an array for each store to not have multiple docs for the same store but different profit dates.

so, I'm confused between two approaches:

1- It'll be read-heavy and frequently updated since the data is going to be accessed by many stores, so a separate collection is what I should opt for. once a new payment operation succeeds I have to do the necessary updates/calculations in two collections (profits and invoices) after each other.

2- However, all the necessary pieces to calculate any stats are in my invoiceModel, all that I should do is to get the stats using aggregation, but then the invoiceModel will deal with two purposes: storing payments/invoices and retrieving the stats from. In addition, the aggregation process is expensive, never mention that it'll be done for all the stores!

my biggest concern is the performance, what do you think is better? updating two collections every time or doing an aggregation for one single collection?

Also what are your suggestions for the schema design in case your recommendation is the first approach?

this is the invoiceModel: import { Schema, model } from "mongoose";

const invoiceSchema = new Schema({
    purchaseId: {
        type:Number,
        required: [true, "the purchaseId field is required"],
        unique: true,
        default: crypto.randomUUID(), 
    },
    buyer: {
        type: Schema.Types.ObjectId,
        ref: "User",
        required: [true, "the buyer field is required"]
    },
    products:[{
        type: Schema.Types.ObjectId,
        ref: "Product",
        required: [true, "the products field is required"]
    }],
    total: {
        type: Number,
        required: [true, "the total field is required"]
    },
    paymentMethod: {},
    purchasedAt: {
        type: Data,
        default: Date.now(),
        required: [true, "the purchasedAt field is required"]
    },
    status: {
        type: String,
        required: [true, "the field is required"],
        enum: ["successful", "cancelled"] 
    },
    notes: {
        type: String,
        trim: true,
    }

});

invoiceSchema.index({user: 1});

const Invoice = model("Invoice", invoiceSchema);

export default Invoice;

Answer 1

Running an agg pipeline over and over again to create the annual bucket is tedious and just gets worse day after day.
I would have a collections stats with docs like this:

{                                                                                 
  _id: ObjectId('67c088a140b9e81bc2c1cde1'),                                      
  ts: ISODate('2025-02-25T00:00:00.000Z'),                                        
  store: "A",                                                                     
  daily: 23.4,                                                                    
  weekly: 163.80,                                                                 
  monthy: 702,                                                                    
  annual: 8541                                                                    
}                                                                                 

Every time a new transaction of profit P is captured for store X, update:

rc=db.stats.update({ts: ISODate("2025-02-25"), store:"X"},                          
                 {$inc: {daily:P, weekly:P, monthly:P, annual:P}}                 
                );                                                                

At midnight everyday, a job runs.
If the current day is Jan 1, insert new record year-01-01 and all 0.
else if the current day is first day of month, fetch prior entry and insert
as year-mon-01 and set all EXCEPT annual to 0
else if the current day is Monday, fetch prior entry and insert
as year-mon-day and set just daily and weekly to zero
else fetch prior entry and insert as year-mon-day and set only daily to 0.

This will give you very easy access to the stats and facilitate creative time-based analytics i.e. what stores saw the biggest change in weekly profits and when?