Affiliate/Referral Management System with MLM support

affiliate.server.ts

import { relations, sql } from "drizzle-orm"
import { index, integer, sqliteTable, text } from "drizzle-orm/sqlite-core"
import { ulid } from "ulidx"

export const affiliatesTable = sqliteTable(
	"affiliate",
	{
		id: text("id")
			.primaryKey()
			.$defaultFn(() => ulid()),
		first_name: text("first_name").notNull(),
		last_name: text("last_name").notNull(),
		email: text("email").unique().notNull(),
		password: text("password").notNull(),
		via: text("via").unique(),
		ref: text("ref").unique(),
		parentRef: text("parentRef").unique(),
		paypal: text("paypal"),
		btc: text("btc"),
	},
	(affiliate) => {
		return {
			affiliateAffiliateIdIdx: index("affiliate_affiliate_id_idx").on(affiliate.id),
		}
	}
)

export const sessionsTable = sqliteTable(
	"session",
	{
		id: text("id")
			.primaryKey()
			.$defaultFn(() => ulid()),
		expiresAt: text("expires_at").notNull(),
		affiliateId: text("affiliate_id")
			.notNull()
			.references(() => affiliatesTable.id, {
				onUpdate: "cascade",
				onDelete: "cascade",
			}),
	},
	(session) => {
		return {
			sessionAffiliateIdIdx: index("session_affiliate_id_idx").on(session.affiliateId),
		}
	}
)

export const friendsOfAffiliatesTable = sqliteTable(
	"friends_of_affiliates",
	{
		id: text("id")
			.primaryKey()
			.$defaultFn(() => ulid()),
		friendsEmail: text("friends_email"),
		affiliateId: text("affiliate_id")
			.notNull()
			.references(() => affiliatesTable.id, {
				onUpdate: "cascade",
				onDelete: "cascade",
			}),
		referredBy: text("referred_by").references(() => affiliatesTable.ref, {
			onUpdate: "cascade",
			onDelete: "cascade",
		}),
	},
	(friendsOfAffiliates) => {
		return {
			friendsOfAffiliateAffiliateIdIdx: index("friends_of_affiliate_affiliate_id_idx").on(
				friendsOfAffiliates.affiliateId
			),
		}
	}
)

export const commissionTable = sqliteTable(
	"commission",
	{
		id: text("id")
			.primaryKey()
			.$defaultFn(() => ulid()),
		createdAt: integer("created_at", { mode: "timestamp" }).default(sql`(strftime('%s', 'now'))`),
		commission: integer("commission"),
		btcCommission: integer("btc_commission"),
		friendsOfAffiliateId: text("friends_of_affiliate_id")
			.notNull()
			.references(() => friendsOfAffiliatesTable.id, {
				onUpdate: "cascade",
				onDelete: "cascade",
			}),
	},
	(commission) => {
		return {
			commissionFriendsOfAffiliateIdIdx: index("commission_friends_of_affiliate_id_idx").on(
				commission.friendsOfAffiliateId
			),
		}
	}
)

export const cashoutTable = sqliteTable(
	"cash_out",
	{
		id: text("id")
			.primaryKey()
			.$defaultFn(() => ulid()),
		payment: integer("payment"),
		cashedOutAt: integer("cashed_out_at", { mode: "timestamp" }),
		affiliateId: text("affiliate_id")
			.notNull()
			.references(() => affiliatesTable.id, {
				onUpdate: "cascade",
				onDelete: "cascade",
			}),
	},
	(cashout) => {
		return {
			cashoutAffiliateIdIdx: index("cashout_affiliate_id_idx").on(cashout.affiliateId),
		}
	}
)

// affiliates - 1 -> many - sessions
// affiliates - 1 -> many - friends_of_affiliates
// affiliates - 1 -> many - cashout
export const affiliatesRelations = relations(affiliatesTable, ({ many }) => ({
	sessions: many(sessionsTable),
	friends_of_affiliates: many(friendsOfAffiliatesTable),
	cashouts: many(cashoutTable),
}))

// sessions - many -> 1 - affiliates
export const sessionsRelations = relations(sessionsTable, ({ one }) => ({
	affiliates: one(affiliatesTable, {
		fields: [sessionsTable.affiliateId],
		references: [affiliatesTable.id],
	}),
}))

// friends_of_affiliates - many -> 1 - affiliates
// friends_of_affiliates - 1 -> many - commission
export const friendsOfAffiliatesRelations = relations(
	friendsOfAffiliatesTable,
	({ one, many }) => ({
		affiliates: one(affiliatesTable, {
			fields: [friendsOfAffiliatesTable.affiliateId],
			references: [affiliatesTable.id],
		}),
		commissions: many(commissionTable),
	})
)

// commission - many -> 1 - friends_of_affiliates
export const commissionRelations = relations(commissionTable, ({ one }) => ({
	friends_of_affiliates: one(friendsOfAffiliatesTable, {
		fields: [commissionTable.friendsOfAffiliateId],
		references: [friendsOfAffiliatesTable.id],
	}),
}))

// cashout - many -> 1 - affiliates
export const cashoutRelations = relations(cashoutTable, ({ one }) => ({
	affiliates: one(affiliatesTable, {
		fields: [cashoutTable.affiliateId],
		references: [affiliatesTable.id],
	}),
}))