Credit card system implementation?

Question

My site is going to have a credit system that basically works a lot like a credit card. Each user has an unlimited credit limit, but at the end of each week, they have to pay it off. For example, a user might make several purchases between March 1st and 7th, and then at the end of March 7th, they would be emailed an invoice that lists all their purchases during the week and a total that is due by the 14th. If they don't pay it off, their account is simply deactivated until they do. I'm just trying to wrap my head around how to implement this.

I have a list of all their purchases, that's not a problem, but I'm just trying to figure out what to do with it. On the end of the 7th day, I could set up a cronjob to generate an invoice, which would basically have an id, and due date, and then I would need another many-to-many table to link all the purchases to the invoice. Then when a user adds money to their account, I guess it's applied against their current outstanding invoice? And what if they don't pay off their invoice by the time a new invoice rolls around, so now they have 2 outstanding ones, how do I know which to apply it against? Or do I make the cronjob check for any previous outstanding invoices, cancel them, and add a new item to the new invoice as "balance forward (+interest)"? How would you apply the money against an invoice? Would each payment have to be linked to an invoice, or could I just deposit it to their account credit, and then somehow figure out whats been paid and what hasn't? What if they pay in advance, before their invoice has been generated? Do I deduct it from their credit from the invoice upon generation, or at the end of the week when its due? There are so many ways to do this...

Can anyone describe what approach they would take?

---

If anyone's interested, my Invoice model presently looks as follows (in Django). The InvoiceItems are linked to actual "products" by a reverse ID (the FK is on the product, not the invoice item to allow different item types (different tables)), but I think I'm going to switch that around.

class Invoice(models.Model):
    user = models.ForeignKey(User, related_name='invoices')
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)
    closed_date = models.DateTimeField(null=True, blank=True)
    due_date = models.DateTimeField(default=_next_weekday())
    payment_date = models.DateTimeField(null=True, blank=True) # date the invoice was fully paid
    total_payments = CurrencyField(default=0)
    interest_charges = CurrencyField(default=0)

    @property
    def days_overdue(self):
        dt = self.due_date - datetime.date.today()
        return dt.days if dt.days > 0 else 0

    @property
    def item_total(self):
        return self.items.filter(amount__gt=0).aggregate(t=Sum('amount'))['t'] or Decimal('0.00')

    @property
    def daily_interest(self):
        return _round((self.item_total - self.total_payments) * settings.INTEREST_RATE/Decimal('365.242199'))

    @property
    def subtotal(self):
        return self.item_total + self.interest_charges

    @property
    def tax(self):
        return _round(self.subtotal * settings.GST)

    @property
    def total(self):
        return self.subtotal + self.tax

    @property
    def balance_owing(self):
        return self.total - self.total_payments

    @property
    def is_paid(self):
        return self.payment_date != None

    @property
    def is_open(self):
        return self.closed_date == None

    @property
    def is_overdue(self):
        return not self.is_paid and self.due_date < datetime.date.today()

class InvoiceItem(models.Model):
    invoice = models.ForeignKey(Invoice, related_name='items')
    created = models.DateTimeField(auto_now_add=True)
    updated = models.DateTimeField(auto_now=True)
    description = models.CharField(max_length=200)
    trans_date = models.DateTimeField(verbose_name='transaction date')
    amount = CurrencyField()

I have a crontab set up to run at midnight every night to add interest charges to all overdue items, and invoices are mailed out every Friday morning.

Answer 1

What you are describing here is basically a decision between open item accounting and balance forward accounting.

In open item accounting, each invoice is kept "open" while it has a balance owing and payments are attributed to the individual invoices that they are paying. This makes it easier to work out things like interest - for example if you only charge interest on balances owing older than 30 days then you need to know what invoices have outstanding balances more than 30 days old.

Balance forward accounting is similar to credit card payments, when there is one lump sum balance that is carried forwards and payments are made against that total balance rather than individual invoices.

Update for clarification

Open item accounting is good for complex billing processes, for example when only some of the products are attached to certain invoices. For example, a builder buys a few dozen products but they are to be invoiced separately to three different building projects.

Open item accounting is also used when you need to track each individual invoice for payment and dispute resolution purposes. For example, a building supply has a builder for a client. Sometimes the goods supplied are wrong or faulty so the builder pays all the other invoices (including more recent invoices) except that one, which is tracked and dealt with separately - perhaps getting paid when replacement goods are supplied or perhaps getting a credit note against the invoice.

In balance forward accounting, you would deal with this situation by simply applying a credit against the account as a whole, then re-adding the transaction when the replacement goods are supplied. Any interest charged on the balance can also be reversed against the account.

Simplistically, here's a way you could set these up in your database:

Open Item Accounting

You need the following tables:

Client       [ClientId, Name, AccountBalance]
Product      [ProductId, Description, Cost]
Invoice      [InvoiceId, ClientId, Date, TotalAmount, Outstanding]
InvoiceItem  [InvoiceId, ProductId, Quantity, Amount]
Receipt      [ReceiptId, Date, TotalAmount]
ReceiptItem  [ReceiptId, InvoiceId, Amount]

Client gets an invoice created when buying products. For each product bought, an invoice item is created for that product showing the quantity bought and the amount. When the invoice is updated, the invoice's outstanding balance is the total of the invoice and client account balance is updated (can be calculated on the fly but easier and quicker if maintained automatically). When the client pays one or more invoices, a receipt is created and receipt items are allocated to each invoice being paid. The invoice's outstanding balance is updated as with the client's account balance. Note that overpayments need to be dealt with separately. Interest charges are raised on the next invoice (or a separate invoice) as an invoice item (this can be a custom product).

Balance Forward Accounting

You need the following tables:

Client       [ClientId, Name, AccountBalance]
Product      [ProductId, Description, Cost]
Invoice      [InvoiceId, ClientId, Date, Amount]
Transaction  [TransactionId, ClientId, InvoiceId, ProductId, Date, Quantity, Amount]

When a product is purchased, a transaction is made on the client's account showing the quantity of the product and the amount and the client's account balance is updated. When a receipt is made, again a transaction is made on the client's account and the client's account balance is updated. Overpayments and interest payments are just a transaction as well. At invoicing time, you simply grab all the transactions (purchases, receipts, interest payments, etc) that aren't allocated to an invoice and add them to your new invoice. The invoice doesn't appear in the transaction list, it is just for convenience in tracking transactions that have been invoiced and for giving your clients a reference number when paying. You might also want to track receipts in this model.

Other considerations

• This doesn't take into account the general ledger posting which will be an entirely different set of tables. This is just for the management accounting, not the financial accounting.
• In practice, there may be a projects table between the client and the invoice to track each of the clients individual projects with you.
• This is really simplistic just to give you an idea. More tables and fields would no doubt be required for a full implementation.

With regards to what happens if they don't pay of their invoice when it comes time for billing, in either system it doesn't matter - you do not go back in time and change previous accounting entries. Anything that happens now goes on a new invoice. Of course, it is entirely appropriate to have a line on the new invoice saying "Previously outstanding balance" which shows the amount they already owe (or are in credit for overpayments).

You will also want to be able to produce a Statement of Account, which isn't an invoice, its just like a letter reminding them they have outstanding balances. This is used when there is no new invoice to be generated but the client needs a reminder about their account balance.

I prefer open item accounting for my systems but from your description balance forward accounting sounds like a good fit.