[feat] Emails retention periods/cleanup

[jnettome]

Hey everyone,

I'm thinking about implementing a cleanup process for old email bodies, since our database is growing significantly due to storing all message content and attachments.

Do you have any recommendations for how to approach this? Or maybe a better solution based on typical project structures?

At the moment, I'm running a SQL query via cron just to get things working in my environment, but I plan to eventually turn this into a proper feature.

Thanks again!

async function cleanupEmailBodies() {
  const result = await db.$executeRaw`
    UPDATE "Email"
    SET "text" = NULL,
        "html" = NULL
    WHERE "createdAt" < NOW() - INTERVAL '90 days'
    AND ("text" IS NOT NULL OR "html" IS NOT NULL);
  `;

  console.log(`[Cleanup] Emails cleaned: ${result}`);
}

[KMKoushik]

been thinking about this as well, this needs to be a cron job from the app itself.

can see a example job here: https://github.com/unsend-dev/unsend/blob/main/apps/web/src/server/jobs/usage-job.ts

if you're interested you can make a pr.

[KMKoushik]

@jnettome can i know how emails you're sending per day/month. just want to see how unsend holds up! thanks

[tpraxedes]

Not necessarily about that theme, but in the future, can be a way for download the EML file of the mail?

[KMKoushik]

yh, you can add it in as an issue so someone can pick it up

[jnettome]

@KMKoushik yeah, some stats:

[KMKoushik]

@jnettome if you don't mind, is it all marketing or transactional? or at what ratio?

[jnettome]

@KMKoushik np! It's 90+% transactional - we're using unsend mainly for our ticketing platform.