Oh Dear is the all-in-one monitoring tool for your entire website. We monitor uptime, SSL certificates, broken links, scheduled tasks and more. You'll get a notifications for us when something's wrong. All that paired with a developer friendly API and kick-ass documentation. O, and you'll also be able to create a public status page under a minute. Start monitoring using our free trial now.

Why and how you should remove inactive users and teams

Original – by Freek Van der Herten – 8 minute read

There are many SaaS applications that allow potential new customers to try out the service using a trial period. Of course, not everybody will convert. After the trial period is over, some people will not use the service anymore.

However, if nothing is being done about it, their email address and other pieces of (personal) data remain in the database. In this blog post you'll learn why you should delete this data. I'll also share how we go about this at Oh Dear.

Why you should remove inactive users and teams

There are two reasons why you want to delete old users and teams. First, you want to keep your database as small as possible. This has many small benefits. Your queries might execute faster. The backup process will be shorter. Less disk space is needed... Granted, this might be a micro-optimization, as databases are very optimized in performing queries in even large datasets, and the disk space used is probably not too much. Still, the less work a server needs to do, the better.

The second reason is more important: you want to keep only as little personal data as needed for privacy reasons. You should only collect and keep the absolute minimum of data needed to run your application. This keeps your user's privacy safe and minimizes the risks for you as a company if a security breach happens.

If people, a few months after a trial period, didn't subscribe, then it's unlikely that they'll ever get a subscription. You don't need their data anymore.

How we take care of deleting users and teams in Oh Dear

Oh Dear is the SaaS application where my buddy Mattias and I are working on. Our service has a trial that allows you to test out all the functionality. Since launch, a lot of people tried it. We're lucky that many people did subscribe, but there are a lot of people who didn't. I guess this is normal for any SaaS.

We've never cleaned up the old users and teams, and reading about privacy pushed me to take care of this. I didn't want this cleanup process to be a one time action but a continuous process.

Deleting unverified users

Let's start with the users. Before new users can even create a team, they should verify their email address. This verification is handled by Laravel. There are a lot of users that never verify their email address. I'm assuming that most of them are bots, together with a couple of people that changed their mind about using our service.

Those unverified users have no way of using the application, so it's safe to delete them.

Here's the command that will delete all unverified users ten days after they have been created.

namespace App\Domain\Team\Commands\Cleanup;

use App\Domain\Team\Models\User;
use Illuminate\Console\Command;

class DeleteOldUnverifiedUsers extends Command
{
    protected $signature = 'ohdear:delete-old-unverified-users';

    public function handle()
    {
        $this->info('Deleting old unverified users...');

        $count = User::query()
            ->whereNull('email_verified_at')
            ->where('created_at', '<', now()->subDays(10))
            ->delete();

        $this->comment("Deleted {$count} unverified users.");

        $this->info('All done!');
    }
}

The most straightforward way to test the command above would be to seed a user that should be deleted, seed another one that shouldn't be deleted call the command above verify if the command only deleted the right user

Instead of that approach, I prefer scenario-based tests that more closely mimic what happens in real life. In the test below, a user is seeded, and by using the spatie/test-time package, we modify the time.

namespace Tests\Feature\Domain\Team\Cleanup;

use App\Domain\Team\Commands\Cleanup\DeleteOldUnverifiedUsers;
use App\Domain\Team\Models\User;
use Spatie\TestTime\TestTime;
use Tests\TestCase;

class DeleteOldUnverifiedUsersTest extends TestCase
{
    public function setUp(): void
    {
        parent::setUp();

        TestTime::freeze('Y-m-d H:i:s', '2021-01-01 00:00:01');
    }

    /** @test */
    public function it_will_delete_unverified_users_after_some_days()
    {
        $user = User::factory()->create([
            'email_verified_at' => null,
        ]);

        TestTime::addDays(10);

        $this->artisan(DeleteOldUnverifiedUsers::class);
        $this->assertTrue($user->exists());

        TestTime::addSecond();

        $this->artisan(DeleteOldUnverifiedUsers::class);
        $this->assertFalse($user->exists());
    }

    /** @test */
    public function it_will_not_delete_verified_users()
    {
        $user = User::factory()->create([
            'email_verified_at' => now(),
        ]);

        TestTime::addDays(20);

        $this->artisan(DeleteOldUnverifiedUsers::class);
        $this->assertTrue($user->exists());
    }
}

Deleting inactive teams

We currently consider a team inactive when it has never subscribed, and the trial period ended more than six months ago. Deleting inactive teams is slightly more complicated. If a team exists, that means that a verified user, the team owner, has created and configured it.

Because there is a chance that the team owner might want to reactive the team at some point in the future, we don't want to delete the team immediately. Instead, we're going to mail the team owners and give them a chance to cancel the automatic deletion. If no response comes in after a week, we'll delete the team.

Let's take a look at the code. On our teams table, we added two columns.

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

class AddDeletionMarkerColumnsToTeamsTable extends Migration
{
    public function up()
    {
        Schema::table('teams', function (Blueprint $table) {
            $table->timestamp('deleting_soon_mail_sent_at')->nullable();
            $table->timestamp('automatically_delete_at')->nullable();
        });
    }
}

deleting_soon_mail_sent_at will contain the date-time when we mailed the automatic deletion notice to the team. automatically_delete_at will contain the date on which the team is scheduled to be deleted.

Here's the shouldBeMarkedForDeletion function that was added to the Team model.

Here's the command that sends out the automatic deletion notices.

namespace App\Domain\Team\Commands\Cleanup;

use App\Domain\Team\Actions\Cleanup\MarkInactiveTeamForDeletionSoonAction;
use App\Domain\Team\Models\Team;
use Illuminate\Console\Command;

class MarkInactiveTeamsForDeletionCommand extends Command
{
    protected $signature = 'ohdear:mark-inactive-teams-for-deletion';

    public function handle()
    {
        $this->info('Starting marking inactive teams for deletion');

        $markedAsDeletionCount = 0;

        Team::each(function (Team $team) use (&$markedAsDeletionCount) {
            if (! $team->shouldBeMarkedForDeletion()) {
                return;
            }
            $this->comment("Marking team {$team->name} ({$team->id}) for deletion");

            (new MarkInactiveTeamForDeletionSoonAction())->execute($team);

            $markedAsDeletionCount++;
        });

        $this->info("Marked {$markedAsDeletionCount} teams for deletion!");
        $this->info('All done!');
    }
}

Here's the code of the shouldBeMarkedForDeletionmethod that will determine whether a team should be mailed an automatic deletion notice.

// on the Team model

public function shouldBeMarkedForDeletion(): bool
{
   // if we've already sent the mail we don't want to resend it
    if ($this->deleting_soon_mail_sent_at !== null) {
        return false;
    }

    if ($this->hasActiveSubscription()) {
        return false;
    }

    if ($this->wasSubScribedAtSomePointInTime()) {
        return false;
    }

    return $this->created_at->addMonths(6)->isPast();
}

Here's the code of the MarkInactiveTeamForDeletionSoonAction used in the command. If you want to know more about Action classes in general, consider picking up our Laravel Beyond CRUD course where this pattern is explained.

namespace App\Domain\Team\Actions\Cleanup;

use App\Domain\Team\Mails\TeamMarkedForDeletionMail;
use App\Domain\Team\Models\Team;
use Illuminate\Support\Facades\Mail;

class MarkInactiveTeamForDeletionSoonAction
{
    public function execute(Team $team)
    {
        $team->update([
            'deleting_soon_mail_sent_at' => now(),
            'automatically_delete_at' => now()->addDays(7),
        ]);


        Mail::to($team->owner->email)->queue(new TeamMarkedForDeletionMail($team));
    }
}

Here's the mail that gets sent to inactive teams.

Mail

To cancel the deletion, team owners should subscribe. Let's take a look at that delete cancellation code.

We have an event listener that executes when a team subscribes. It will set deleting_soon_mail_sent_at and automatically_delete_at to null so that the automatic deletion is effectively cancelled.

class CancelAutomaticDeletion
{
    public function handle(SubscriptionCreated $event)
    {
        /** @var \App\Domain\Team\Models\Team $team */
        $team = $event->billable;

        $team->update([
           'deleting_soon_mail_sent_at' => null,
           'automatically_delete_at' => null,
        ]);

Now let's take a look at the command that does the actual deletion if team owners don't subscribe. It's pretty simple; it only has to consider the value of automatically_delete_at on a team.

namespace App\Domain\Team\Commands\Cleanup;

use App\Domain\Team\Actions\Cleanup\DeleteInactiveTeamAction;
use App\Domain\Team\Models\Team;
use Illuminate\Console\Command;

class DeleteInactiveTeamsCommand extends Command
{
    protected $signature = 'ohdear:delete-inactive-teams';

    public function handle()
    {
        $this->info('Start deleting old teams...');

        Team::query()
            ->where('automatically_delete_at', '<', now())
            ->each(function (Team $team) {
                $this->comment("Deleting team {$team->id}");

                (new DeleteInactiveTeamAction())->execute($team);
            });

        $this->info('All done!');
    }
}

Here's the code of the DeleteInactiveTeamAction class used in the command above. In the action, we'll delete the team. We'll also delete the owner if it has no other teams.

namespace App\Domain\Team\Actions\Cleanup;

use App\Domain\Team\Models\Team;

class DeleteInactiveTeamAction
{
    public function execute(Team $team)
    {
        $teamOwner = $team->owner;

        $team->delete();

        /** @var \App\Domain\Team\Models\User $teamOwner */
        $teamOwner = $teamOwner->refresh();

        if ($teamOwner->allTeams()->count() === 0) {
            $teamOwner->delete();
        }
    }
}

Let's now test that all the code above is working as intended. Again, we're going to use the TestTime class to create a "scenario" test where we move forward in time.

namespace Tests\Feature\Domain\Team\Cleanup;

use App\Domain\Team\Commands\Cleanup\DeleteInactiveTeamsCommand;
use App\Domain\Team\Commands\Cleanup\MarkInactiveTeamsForDeletionCommand;
use App\Domain\Team\Mails\TeamMarkedForDeletionMail;
use App\Domain\Team\Models\Team;
use Illuminate\Support\Facades\Mail;
use Spatie\TestTime\TestTime;
use Tests\Factories\TeamFactory;
use Tests\TestCase;

class AutomaticTeamDeletionTest extends TestCase
{
    public function setUp(): void
    {
        parent::setUp();

        TestTime::freeze('Y-m-d H:i:s', '2020-01-01 00:00:00');

        Mail::fake();
    }

    /** @test */
    public function it_will_delete_a_team_when_it_has_never_subscribed_and_is_older_than_6_months()
    {
        /** @var \App\Domain\Team\Models\Team $teamWithoutSubscription */
        $teamWithoutSubscription = Team::factory()->create();

        TestTime::addMonths(6);
        $this->artisan(MarkInactiveTeamsForDeletionCommand::class);
        $this->assertFalse($teamWithoutSubscription->markedForDeletion());
        Mail::assertNothingQueued();

        TestTime::addSecond();
        $this->artisan(MarkInactiveTeamsForDeletionCommand::class);
        $this->assertTrue($teamWithoutSubscription->refresh()->markedForDeletion());
        Mail::assertQueued(TeamMarkedForDeletionMail::class);
        $this->assertEquals(now()->addDays(7), $teamWithoutSubscription->refresh()->automatically_delete_at);

        TestTime::addDays(7);
        $this->artisan(DeleteInactiveTeamsCommand::class);
        $this->assertTrue($teamWithoutSubscription->exists());

        TestTime::addSecond();
        $this->artisan(DeleteInactiveTeamsCommand::class);
        $this->assertFalse($teamWithoutSubscription->exists());
    }

    /** @test */
    public function it_will_not_mark_teams_that_have_subscription_for_deletion()
    {
        $teamWithSubscription = TeamFactory::createSubscribedTeam();

        TestTime::addMonths(6)->addSecond();

        $this->artisan(MarkInactiveTeamsForDeletionCommand::class);
        $this->assertFalse($teamWithSubscription->refresh()->markedForDeletion());
        Mail::assertNothingQueued();

        TestTime::addDays(7)->addSecond();

        $this->artisan(DeleteInactiveTeamsCommand::class);
        $this->assertTrue($teamWithSubscription->exists());
    }
}

In closing

I hope you like this little tour of how we delete inactive users and teams at Oh Dear. If you work on a similar application, I highly encourage you to make sure the old user data is being deleted.

Stay up to date with all things Laravel, PHP, and JavaScript.

You can follow me on these platforms:

On all these platforms, regularly share programming tips, and what I myself have learned in ongoing projects.

Every month I send out a newsletter containing lots of interesting stuff for the modern PHP developer.

Expect quick tips & tricks, interesting tutorials, opinions and packages. Because I work with Laravel every day there is an emphasis on that framework.

Rest assured that I will only use your email address to send you the newsletter and will not use it for any other purposes.

Comments

Sebastian Stigler avatar

Hi Freek,

thanks for sharing your thoughts on this topic. Could you please also explain what is happening when a user cancels her subscription. Do you have a deletion workflow for "active" (users who has verified their email addresses) users too? Thanks Sebastian

Comments powered by Laravel Comments
Want to join the conversation? Log in or create an account to post a comment.