Build a Referral System with Laravel
This episode is for members only

Sign up to access "Build a Referral System with Laravel" right now.

Get started
Already a member? Sign in to continue
Playing
18. Referral payments schema

Episodes

0%
Your progress
  • Total: 2h 39m
  • Played: 0m
  • Remaining: 2h 39m
Join or sign in to track your progress

Transcript

00:00
So anytime a payment comes into our system with a Stripe webhook we want to log it because that is
00:06
going to first of all allow us to show the payments to the user but more importantly calculate what needs to be paid out and then send us as an admin an email with a spreadsheet in with all of the payments that we need to make. So this is a really really important table and it's something we need to make sure we get right and include all of the relevant information.
00:28
Okay so let's just go ahead and create this out. We're going to create this as a model with a migration so let's go ahead and make out a model here and we're going to call this referral payment. So let's create that out and we're going to really focus on the schema here to see what we need to include. So create referral payments table and let's think about
00:47
what we need in here. So this is going to belong to a user because we obviously need to know who to pay that's really important we need to grab their PayPal email address so we can include that in the spreadsheet. So we're going to have a user id in here which is pretty obvious but we're also going to have the referred user id so the user who was referred so we know if we need to include
01:09
them at all. So we need to constrain that to users just because we're not using the standard so we need to actually pass the table in because it won't be able to work this out. Okay so next up is going to be the payment id from Stripe so we can easily look that up if we need to. So we'll go ahead and include the payment id for that this is also really important because when we get to the
01:31
point where we're recreating or we're listening to our webhook and we're actually creating this record we do not want to create duplicate records and the Stripe payment id is going to be the unique thing in this entire table so we only want to create this once. Like I said if the webhook gets fired more than once and this creates an additional record in the database we might end
01:53
up with having to pay $40 to a user instead of $20 and of course we could remove that later but we just want to make sure it doesn't get in there in the first place. Okay so the next thing is going to be just the payment total that comes through from Stripe so we'll set this as an unsigned integer again we're going to store this in cents so that's going to be the payment total and then we're going
02:16
to have the amount that we're actually going to pay out to the user so of course you can change the names of these if they make more sense. Okay so the really important thing is a timestamp to for when this can actually be paid so we're going to call this available at now basically the way this is going to work is when this payment rolls in we're going to set available at to the
02:36
current date and time but we're going to add maybe a month on to account for any refunds or anything that comes in between that so we want to make sure that we don't pay this out instantly of course it's not going to work we want to add a month on to the time and you can increase this or decrease it it's entirely up to you. Okay so the next thing is going to be marking this as paid so once the
02:58
whole spreadsheet has been generated and sent to us we're going to mark this as paid obviously it's up to us to then actually pay the money out to the user but this is really important so we're just going to have a date in here to say when this is paid at so the reason that we're not including a timestamp is because we're going to pay on a day not necessarily on a day in time
03:19
so i just want this to be the day date that it was paid at not we don't really care about the time here so that's going to be nullable because of course it's not always going to be paid out and that's pretty much it for the schema so let's go and migrate what we have here and then we'll just hook this relationship up to the user so we'll add this into has referrals and let's create
03:42
a referral payments relationship and just say that this has many and referral payment now we're not really going to use this until we start looking at the stats because when we pull this in from our webhook all we really need to do is just create all this stuff out we're not going to look at creating this via relationships it kind of gets a little bit too complicated so
04:08
we'll just dump this information into the database when a payment comes in make sure we're adjusting the timestamps properly and we should be good to go so now that we've got the schema down hopefully all that makes sense but let's go over and actually look at receiving the webhook and creating this in the database

Episode summary

In this episode, we're designing the schema for our referral payments system – basically the database table and model that tracks all the referral payouts we need to make when users bring in new customers.

First, we talk about why it's so important to log every payment that comes in from Stripe via webhook. This isn't just so users can see them, but also so we know exactly how much has been earned and what needs to be paid out (plus, it helps generate a spreadsheet for the admin).

We walk step-by-step through creating the migration and model for the referral_payments table. Here's what we cover:

  • Why the table needs references to both the referring user and the user who was referred
  • Storing Stripe payment IDs to prevent duplicates if a webhook fires twice
  • Keeping track of the payment amount (in cents) and the actual payout amount
  • Introducing an available_at column to delay payments for things like potential refunds (usually a one month delay)
  • Adding a paid_at field (just the date, not the time!) so we can track when each payment was sent

We set up the necessary relationships in the user model for completeness, but explain that, for now, we'll be mostly focused on just inserting these records when a webhook comes in rather than getting too fancy with Eloquent relationships.

By the end, you'll understand why we're tracking all this info, what goes in the table, and the rationale behind every field. Next up, we'll actually wire up listening to Stripe webhooks and persisting referral payments based on real events!

Episode discussion

No comments, yet. Be the first!