Notifications Schema Deep Dive

Overview

The notifications module provides an in-app notification system for users. Notifications are typed, support read/unread tracking, and can carry arbitrary data payloads. The system also includes a newsletterSubscriptions table for email subscription management.

Source file: template/lib/db/schema.ts Relations file: template/lib/db/migrations/relations.ts

---

Table: notifications

In-app notification records delivered to users.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
userId	user_id	text	No	-	FK -> users.id (CASCADE)
type	type	text (enum)	No	-	See notification types
title	title	text	No	-	Notification headline
message	message	text	No	-	Notification body
data	data	text	Yes	-	JSON payload
isRead	is_read	boolean	No	false	Read status
readAt	read_at	timestamp	Yes	-	When marked as read
createdAt	created_at	timestamp	No	now()	-
updatedAt	updated_at	timestamp	No	now()	-

Foreign Keys

Column	References	On Delete
user_id	users.id	CASCADE

Indexes

Name	Columns	Type
notifications_user_idx	userId	B-tree
notifications_type_idx	type	B-tree
notifications_is_read_idx	isRead	B-tree
notifications_created_at_idx	createdAt	B-tree

---

Notification Type Enum

// Defined inline in the schema
type: text('type', {
    enum: [
        'item_submission',    // A new item was submitted
        'comment_reported',   // A comment was reported
        'item_reported',      // An item was reported
        'user_registered',    // A new user registered
        'payment_failed',     // A payment attempt failed
        'system_alert'        // System-level notification
    ]
}).notNull()

Type	Description	Typical Recipient
item_submission	New item submitted for review	Admin users
comment_reported	A comment was flagged by a user	Admin users
item_reported	An item was flagged by a user	Admin users
user_registered	New user created an account	Admin users
payment_failed	A payment attempt failed	Affected user
system_alert	System-level alerts and announcements	All users or specific users

---

TypeScript Types

export type Notification = typeof notifications.$inferSelect;
export type NewNotification = typeof notifications.$inferInsert;

---

Relations

// From relations.ts
export const notificationsRelations = relations(notifications, ({ one }) => ({
    user: one(users, {
        fields: [notifications.userId],
        references: [users.id]
    }),
}));

// Users have many notifications
export const usersRelations = relations(users, ({ many }) => ({
    // ... other relations
    notifications: many(notifications),
}));

---

Table: newsletterSubscriptions

Email newsletter subscription management, separate from in-app notifications.

Columns

Column	DB Name	Type	Nullable	Default	Constraints
id	id	text	No	crypto.randomUUID()	Primary Key
email	email	text	No	-	Unique
isActive	is_active	boolean	No	true	Active subscription flag
subscribedAt	subscribed_at	timestamp	No	now()	-
unsubscribedAt	unsubscribed_at	timestamp	Yes	-	-
lastEmailSent	last_email_sent	timestamp	Yes	-	-
source	source	text	Yes	'footer'	footer, popup, etc.

Constraints

Name	Columns	Type
newsletterSubscriptions_email_unique	email	Unique

TypeScript Types

export type NewsletterSubscription = typeof newsletterSubscriptions.$inferSelect;
export type NewNewsletterSubscription = typeof newsletterSubscriptions.$inferInsert;

---

Relations Diagram

---

Notification Flow

---

The data Column

The data column stores a JSON string (not JSONB) with arbitrary context for the notification. Structure varies by notification type:

// item_submission
{ "itemSlug": "new-tool", "itemName": "New Tool", "submittedBy": "user-id" }

// comment_reported
{ "commentId": "comment-uuid", "itemSlug": "my-item", "reportId": "report-uuid" }

// payment_failed
{ "subscriptionId": "sub-uuid", "amount": 1999, "currency": "usd" }

// system_alert
{ "severity": "info", "actionUrl": "/admin/settings" }

---

Query Examples

Create a notification

import { db } from '@/lib/db/drizzle';
import { notifications } from '@/lib/db/schema';

await db.insert(notifications).values({
    userId: targetUserId,
    type: 'item_submission',
    title: 'New Item Submitted',
    message: 'A new tool "Acme Editor" has been submitted for review.',
    data: JSON.stringify({
        itemSlug: 'acme-editor',
        itemName: 'Acme Editor',
        submittedBy: submitterUserId,
    }),
});

Get unread notifications for a user

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

const unread = await db
    .select()
    .from(notifications)
    .where(
        and(
            eq(notifications.userId, userId),
            eq(notifications.isRead, false)
        )
    )
    .orderBy(desc(notifications.createdAt));

Get unread count

import { sql } from 'drizzle-orm';

const [{ count }] = await db
    .select({ count: sql<number>`count(*)` })
    .from(notifications)
    .where(
        and(
            eq(notifications.userId, userId),
            eq(notifications.isRead, false)
        )
    );

Mark notification as read

await db
    .update(notifications)
    .set({
        isRead: true,
        readAt: new Date(),
        updatedAt: new Date(),
    })
    .where(eq(notifications.id, notificationId));

Mark all notifications as read

await db
    .update(notifications)
    .set({
        isRead: true,
        readAt: new Date(),
        updatedAt: new Date(),
    })
    .where(
        and(
            eq(notifications.userId, userId),
            eq(notifications.isRead, false)
        )
    );

Get paginated notification history

const page = 1;
const limit = 20;

const history = await db
    .select()
    .from(notifications)
    .where(eq(notifications.userId, userId))
    .orderBy(desc(notifications.createdAt))
    .limit(limit)
    .offset((page - 1) * limit);

Subscribe to newsletter

import { newsletterSubscriptions } from '@/lib/db/schema';

await db
    .insert(newsletterSubscriptions)
    .values({
        email: 'user@example.com',
        source: 'footer',
    })
    .onConflictDoUpdate({
        target: newsletterSubscriptions.email,
        set: {
            isActive: true,
            subscribedAt: new Date(),
            unsubscribedAt: null,
        },
    });

Unsubscribe from newsletter

await db
    .update(newsletterSubscriptions)
    .set({
        isActive: false,
        unsubscribedAt: new Date(),
    })
    .where(eq(newsletterSubscriptions.email, 'user@example.com'));

Get active newsletter subscribers

const subscribers = await db
    .select()
    .from(newsletterSubscriptions)
    .where(eq(newsletterSubscriptions.isActive, true));

---

Design Notes

• data is text, not JSONB. The notification data column is stored as a plain text field containing serialized JSON, not a jsonb column. This means you must JSON.stringify() when writing and JSON.parse() when reading. Queries cannot filter on fields within data.
• No notification preferences table. The current schema does not include a user notification preferences table. All notification types are delivered to all applicable users. To add per-user notification preferences, a new notification_preferences table would need to be created.
• Cascade deletion. When a user is deleted, all their notifications are automatically removed via the CASCADE foreign key.
• Newsletter is independent. The newsletterSubscriptions table is not linked to the users table by foreign key. This is intentional -- newsletter subscriptions can exist for non-registered visitors who only provide an email address.
• Source tracking. The source field on newsletter subscriptions tracks where the subscription originated (footer form, popup, etc.) for analytics purposes.