Sponsor Ads Schema Deep Dive

Overview

The sponsor ads module enables users to pay for promoted placement of items on the site. It implements a full lifecycle: submission, payment, admin review, active display, and expiration/cancellation. The system integrates with the payment provider infrastructure and supports both weekly and monthly sponsorship intervals.

Source file: template/lib/db/schema.ts

---

Table: sponsor_ads

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
User/Submitter
userId	user_id	text	No	-	FK -> users.id (CASCADE)
Item
itemSlug	item_slug	text	No	-	Item being sponsored
Sponsorship Details
status	status	text (enum)	No	'pending_payment'	See status enum
interval	interval	text (enum)	No	-	weekly, monthly
Pricing
amount	amount	integer	No	-	In dollars
currency	currency	text	No	'usd'	ISO currency code
Payment
paymentProvider	payment_provider	text	No	-	Provider name
subscriptionId	subscription_id	text	Yes	-	External subscription ID
customerId	customer_id	text	Yes	-	External customer ID
Subscription Period
startDate	start_date	timestamp	Yes	-	-
endDate	end_date	timestamp	Yes	-	-
Admin Review
reviewedBy	reviewed_by	text	Yes	-	FK -> users.id (SET NULL)
reviewedAt	reviewed_at	timestamp	Yes	-	-
rejectionReason	rejection_reason	text	Yes	-	-
Cancellation
cancelledAt	cancelled_at	timestamp	Yes	-	-
cancelReason	cancel_reason	text	Yes	-	-
Timestamps
createdAt	created_at	timestamp	No	now()	-
updatedAt	updated_at	timestamp	No	now()	-

Foreign Keys

Column	References	On Delete
user_id	users.id	CASCADE
reviewed_by	users.id	SET NULL

Indexes

Name	Columns	Type
sponsor_ads_user_id_idx	userId	B-tree
sponsor_ads_item_slug_idx	itemSlug	B-tree
sponsor_ads_status_idx	status	B-tree
sponsor_ads_interval_idx	interval	B-tree
sponsor_ads_provider_subscription_idx	(paymentProvider, subscriptionId)	Unique
sponsor_ads_start_date_idx	startDate	B-tree
sponsor_ads_end_date_idx	endDate	B-tree
sponsor_ads_created_at_idx	createdAt	B-tree

---

Status Enum

export const SponsorAdStatus = {
    PENDING_PAYMENT: 'pending_payment', // User submitted, waiting for payment
    PENDING: 'pending',                 // User paid, waiting for admin review
    REJECTED: 'rejected',               // Admin rejected
    ACTIVE: 'active',                   // Admin approved, displaying on site
    EXPIRED: 'expired',                 // Subscription period ended
    CANCELLED: 'cancelled'              // Cancelled by user or admin
} as const;

export type SponsorAdStatusValues =
    (typeof SponsorAdStatus)[keyof typeof SponsorAdStatus];

Interval Enum

export const SponsorAdInterval = {
    WEEKLY: 'weekly',
    MONTHLY: 'monthly'
} as const;

export type SponsorAdIntervalValues =
    (typeof SponsorAdInterval)[keyof typeof SponsorAdInterval];

---

TypeScript Types

export type SponsorAd = typeof sponsorAds.$inferSelect;
export type NewSponsorAd = typeof sponsorAds.$inferInsert;

---

Status Workflow

---

Relations Diagram

---

Key Design Decisions

Two Foreign Keys to users

The table has two references to the users table:

1. userId (CASCADE): The user who submitted and pays for the sponsorship. Deleting the user deletes all their sponsor ads.
2. reviewedBy (SET NULL): The admin who approved/rejected. If the admin is deleted, the review record is preserved with a null reviewer.

External Payment Integration

The paymentProvider + subscriptionId combination has a unique index, mirroring the pattern in the subscriptions table. This allows looking up sponsor ads by their external subscription reference during webhook processing.

Amount in Dollars

Unlike the subscriptions table which stores amounts in cents, the sponsor_ads.amount column stores values in whole dollars. Be mindful of this difference when integrating with payment providers.

---

Query Examples

Create a sponsor ad request

import { db } from '@/lib/db/drizzle';
import { sponsorAds, SponsorAdStatus } from '@/lib/db/schema';

await db.insert(sponsorAds).values({
    userId,
    itemSlug: 'my-tool-slug',
    status: SponsorAdStatus.PENDING_PAYMENT,
    interval: 'monthly',
    amount: 49,
    currency: 'usd',
    paymentProvider: 'stripe',
});

Confirm payment (update status)

await db
    .update(sponsorAds)
    .set({
        status: SponsorAdStatus.PENDING,
        subscriptionId: stripeSubscription.id,
        customerId: stripeCustomer.id,
        updatedAt: new Date(),
    })
    .where(eq(sponsorAds.id, sponsorAdId));

Admin approves a sponsor ad

await db
    .update(sponsorAds)
    .set({
        status: SponsorAdStatus.ACTIVE,
        reviewedBy: adminUserId,
        reviewedAt: new Date(),
        startDate: new Date(),
        endDate: new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // 30 days
        updatedAt: new Date(),
    })
    .where(eq(sponsorAds.id, sponsorAdId));

Admin rejects a sponsor ad

await db
    .update(sponsorAds)
    .set({
        status: SponsorAdStatus.REJECTED,
        reviewedBy: adminUserId,
        reviewedAt: new Date(),
        rejectionReason: 'Content does not meet advertising guidelines.',
        updatedAt: new Date(),
    })
    .where(eq(sponsorAds.id, sponsorAdId));

Get active sponsor ads for display

import { and, eq, lte, gte } from 'drizzle-orm';

const now = new Date();
const activeAds = await db
    .select()
    .from(sponsorAds)
    .where(
        and(
            eq(sponsorAds.status, SponsorAdStatus.ACTIVE),
            lte(sponsorAds.startDate, now),
            gte(sponsorAds.endDate, now)
        )
    );

Find sponsor ad by Stripe subscription ID

const ad = await db
    .select()
    .from(sponsorAds)
    .where(
        and(
            eq(sponsorAds.paymentProvider, 'stripe'),
            eq(sponsorAds.subscriptionId, stripeSubscriptionId)
        )
    )
    .limit(1);

Get pending reviews for admin dashboard

const pendingReviews = await db
    .select()
    .from(sponsorAds)
    .where(eq(sponsorAds.status, SponsorAdStatus.PENDING))
    .orderBy(asc(sponsorAds.createdAt));

Expire sponsor ads past their end date

await db
    .update(sponsorAds)
    .set({
        status: SponsorAdStatus.EXPIRED,
        updatedAt: new Date(),
    })
    .where(
        and(
            eq(sponsorAds.status, SponsorAdStatus.ACTIVE),
            lte(sponsorAds.endDate, new Date())
        )
    );

---

Design Notes

• Item identification by slug. Like all item-referencing tables, itemSlug stores the item's content slug, not a database foreign key.
• Unique provider+subscription index. Ensures no duplicate subscription records and enables fast webhook lookups.
• Soft status transitions. Status changes are tracked via the status column itself. For a full audit trail, consider pairing with the activityLogs table.
• Manual expiration. There is no automatic expiration mechanism at the database level. A cron job or webhook handler should periodically check for and expire past-due sponsor ads.