Storing a date modifier in a table?

Depending on how much flexibility you want to offer, there are a few options.

If you want restricted intervals, have an Enum column with something like (daily, weekly, monthly) and let the user choose one of those.

You could also define an interval as a number of days between invoices and a cycle counter, then just add the days using Carbon (`$recurring_date = $starts_at->addDays($interval * $cycles).

Alternatively, have an interval and a next_date column, initialize it as $start_at->addDays($interval) and $next_date->addDays($interval) every cycle.

Thank you for that.

I'm not sure the days option would work as I think there may be cases when I want an invoice to recur on the 1st of April and the 1st of October every year for example.

This is where I'm getting a bit stuck.

I could perhaps have two columns, one being the interval type (eg. day, month, year, etc) and then an integer column for the interval to work with the type.

Is that the cleanest way of doing this?

How about having a next invoice column and when the client is invoiced you update it based on the interval?

It would probably work and be easy enough to decipher a few years from now when you look back and try to figure out what those database columns are there for.

Another approach would be to store a date string to be parsed (such as first day of next month or next month +14 days) but there's a lot of room for error there and if parse logic changes you're screwed.

The important thing is to be able to determine when the subscription started, and when it is scheduled for renewal. Any solution would be equally valid:

start_date, days_interval, cycle_count; //next_renewal = start_date + days_interval * cycle_count
start_date, next_renewal, interval; //next_renewal = next_renewal + interval after each renewal
start_date, interval_type, interval_count, cycle_count; // next_renewal = start_date + (interval_type * interval_count * cycle_count)
start_date, interval, last_renewal; // next_renewal = last_renewal + interval

Bit messy but this is what I came up with.

Recurring invoices migration

        Schema::create('invoice_recurring', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('user_id')->unsigned();
            $table->integer('invoice_id')->unsigned();
            $table->string('interval_type');
            $table->integer('interval');
            $table->date('next_invoice');
            $table->date('ends_at')->nullable();
            $table->text('data')->nullable();
            $table->timestamps();
        });

GenerateRecurringInvoices job

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        $recurring_invoices = InvoiceRecurring::invoiceDue()->get();

        foreach ($recurring_invoices as $recur) {

            $invoice = $recur->invoice;

            $new_invoice = $invoice->replicate();
            $new_invoice->paid_at = null;
            $new_invoice->number = $invoice->invoiceGroup->next_number;
            $new_invoice->save();

            $new_invoice->users()->sync($invoice->users);

            $interval = 'add' . $recur->interval_type;

            $recur->next_invoice = $recur->next_invoice->$interval($recur->interval);
            $recur->save();
        }
    }

RecurringInvoice eloquent model

<?php

namespace App;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;

class InvoiceRecurring extends Model
{
    /**
     * The table associated with the model
     *
     * @var  string
     */
    protected $table = 'invoice_recurring';

    /**
     * The attributes that should be cast to native types.
     * 
     * @var array
     */
    protected $casts = [
        'data' => 'array'
    ];

    /**
     * The attributes that should be mutated to dates.
     * 
     * @var array
     */
    protected $dates = ['next_invoice','ends_at'];

    /**
     * A recurring invoice belongs to an owner.
     */
    public function owner()
    {
        return $this->belongsTo('App\User', 'user_id');
    }

    /**
     * A recurring invoice belongs to an invoice.
     */
    public function invoice()
    {
        return $this->belongsTo('App\Invoice');
    }

    /**
     * Scope a query to only include the next invoice due.
     * 
     * @param \Illuminate\Database\Eloquent\Builer $query
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeInvoiceDue($query)
    {
        return $query->where('next_invoice', '<=', Carbon::now());
    }
}

The above job is executed daily at midnight.

Created a console command to manually run the job as well and so far so good.

Possible caveat with your solution, if a subscription starts, for example, on the 31st and you set a 1-month interval, the second invoice will be charged on the 1st of the month after the next, and every subsequent invoice will be charged on the first of the next month.

>>> $start = Carbon::now();
=> Carbon\Carbon {#1062
     +"date": "2017-10-31 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2017-12-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-01-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-02-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-03-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-04-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-05-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-06-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-07-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-08-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-09-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }
>>> $start->addMonths(1);
=> Carbon\Carbon {#1062
     +"date": "2018-10-01 16:09:04.423699",
     +"timezone_type": 3,
     +"timezone": "UTC",
   }

Good catch.

I guess creating a custom date helper to check for this and prevent this could be a solution?

That could work. Make sure you handle leap years as well if you offer yearly renwals (does starting on the 29th of february renew on the 28th or the 1st of march next year?)

The solution here really depends on the flexibility you want to offer. I guess if I were doing it, I'd let users choose a day of the month to get their monthly renewals from a small list (maybe 1, 10, 15, 20, as long as it's between 1 and 28) and have that along with the rest of the data.