Database adapters

import { memoryAdapter } from "@notifykitjs/core"

const notify = createNotifyKit({
  // ...
  database: memoryAdapter(),
})

npm install @notifykitjs/drizzle drizzle-orm better-sqlite3

import Database from "better-sqlite3"
import { drizzle } from "drizzle-orm/better-sqlite3"
import { createSqliteTables, drizzleSqliteAdapter } from "@notifykitjs/drizzle"

const sqlite = new Database("app.db")
const db = drizzle(sqlite)

// Create tables (run once, or use drizzle-kit migrations in production)
await createSqliteTables(db)

export const notify = createNotifyKit({
  // ...
  database: drizzleSqliteAdapter(db),
})

npm install @notifykitjs/drizzle drizzle-orm postgres

import postgres from "postgres"
import { drizzle } from "drizzle-orm/postgres-js"
import { createPgTables, drizzlePostgresAdapter } from "@notifykitjs/drizzle"

const client = postgres(process.env.DATABASE_URL!)
const db = drizzle(client)

// Create tables (run once, or use drizzle-kit migrations)
await createPgTables(db)

export const notify = createNotifyKit({
  // ...
  database: drizzlePostgresAdapter(db),
})

// postgres.js — set pool size explicitly
const client = postgres(process.env.DATABASE_URL!, {
  max: 10, // connections in the pool
  idle_timeout: 20, // seconds before idle connections close
  connect_timeout: 10, // seconds to wait for a connection
})

# Generate a migration from the NotifyKit schema
npx drizzle-kit generate

# Apply it (same as your app migrations)
npx drizzle-kit migrate

import { notifyKitSchema } from "@notifykitjs/drizzle"
import { eq } from "drizzle-orm"

// Get all inbox items for a user with their notification details
const items = await db
  .select()
  .from(notifyKitSchema.inboxItems)
  .where(eq(notifyKitSchema.inboxItems.recipientId, user.id))
  .orderBy(notifyKitSchema.inboxItems.createdAt)

import { notifyKitSchema } from "@notifykitjs/drizzle"
import { eq, isNull, gt, sql, and, count } from "drizzle-orm"

const { inboxItems, deliveries, recipients } = notifyKitSchema

// Unread count for a specific user (server-side, outside hooks)
const [{ unread }] = await db
  .select({ unread: count() })
  .from(inboxItems)
  .where(and(
    eq(inboxItems.recipientId, userId),
    isNull(inboxItems.readAt),
    isNull(inboxItems.archivedAt),
  ))

// Failed deliveries in the last hour (incident detection)
const oneHourAgo = new Date(Date.now() - 60 * 60_000)
const failures = await db
  .select()
  .from(deliveries)
  .where(and(
    eq(deliveries.status, "failed"),
    gt(deliveries.failedAt, oneHourAgo),
  ))

// Delivery success rate by channel (last 24h)
const oneDayAgo = new Date(Date.now() - 24 * 60 * 60_000)
const stats = await db
  .select({
    channel: deliveries.channel,
    total: count(),
    sent: sql`count(*) filter (where ${deliveries.status} = 'sent')`,
  })
  .from(deliveries)
  .where(gt(deliveries.createdAt, oneDayAgo))
  .groupBy(deliveries.channel)

// Run daily: npx tsx scripts/prune-notifykit.ts
// Or schedule via cron: 0 3 * * * npx tsx scripts/prune-notifykit.ts
import { notify } from "@/lib/notifykit"
import { notifyKitSchema } from "@notifykitjs/drizzle"
import { lt, and, eq, isNotNull } from "drizzle-orm"
import { db } from "@/lib/db"

const { deliveries, inboxItems, timelineEvents } = notifyKitSchema

const RETENTION = {
  timeline: 14,            // days — fastest-growing table
  sentDeliveries: 30,      // days — keep failures longer
  archivedInbox: 30,       // days — user already dismissed these
}

async function prune() {
  const now = Date.now()

  // 1. Timeline events (3-8 rows per send — biggest growth)
  const timelineCutoff = new Date(now - RETENTION.timeline * 86_400_000)
  const timelineResult = await db
    .delete(timelineEvents)
    .where(lt(timelineEvents.createdAt, timelineCutoff))
  console.log(`Timeline: pruned ${timelineResult.rowCount ?? 0} rows (>${RETENTION.timeline}d)`)

  // 2. Successful deliveries (keep failures for debugging)
  const deliveryCutoff = new Date(now - RETENTION.sentDeliveries * 86_400_000)
  const deliveryResult = await db
    .delete(deliveries)
    .where(and(
      eq(deliveries.status, "sent"),
      lt(deliveries.createdAt, deliveryCutoff),
    ))
  console.log(`Deliveries (sent): pruned ${deliveryResult.rowCount ?? 0} rows (>${RETENTION.sentDeliveries}d)`)

  // 3. Archived inbox items (user already read + archived)
  const archiveCutoff = new Date(now - RETENTION.archivedInbox * 86_400_000)
  const archiveResult = await db
    .delete(inboxItems)
    .where(and(
      isNotNull(inboxItems.archivedAt),
      lt(inboxItems.archivedAt, archiveCutoff),
    ))
  console.log(`Archived inbox: pruned ${archiveResult.rowCount ?? 0} rows (>${RETENTION.archivedInbox}d)`)
}

prune().catch(err => {
  console.error("Prune failed:", err)
  process.exit(1)
})

import type { DatabaseAdapter } from "@notifykitjs/core"

export function customAdapter(): DatabaseAdapter {
  return {
    recipients: createRecipientStore(),
    notifications: createNotificationStore(),
    inbox: createInboxStore(),
    deliveries: createDeliveryStore(),
    preferences: createPreferenceStore(),
    digests: createDigestStore(),
    rateLimits: createRateLimitStore(),
    scheduledSends: createScheduledSendStore(),
    dedupe: createDedupeStore(),
    timeline: createTimelineStore(), // optional
  }
}

import { memoryAdapter } from "@notifykitjs/core"
import { drizzleSqliteAdapter } from "@notifykitjs/drizzle"
import { drizzlePostgresAdapter } from "@notifykitjs/drizzle"

function getAdapter() {
  if (process.env.NODE_ENV === "test") return memoryAdapter()
  if (process.env.DATABASE_URL) return drizzlePostgresAdapter(pgDb)
  return drizzleSqliteAdapter(sqliteDb)
}

export const notify = createNotifyKit({
  notifications: [...] as const,
  database: getAdapter(),
})

-- Inbox queries (list by recipient, filter archived, sort by time)
CREATE INDEX idx_inbox_recipient_active
  ON notifykit_inbox_items (recipient_id, created_at DESC)
  WHERE archived_at IS NULL;

-- Delivery queries (find failures for incident investigation)
CREATE INDEX idx_deliveries_status_time
  ON notifykit_deliveries (status, created_at DESC)
  WHERE status = 'failed';

-- Timeline pruning (prune by age without full table scan)
CREATE INDEX idx_timeline_created
  ON notifykit_timeline_events (created_at);

-- Rate limit counting (sliding window lookups)
CREATE INDEX idx_rate_limits_key_time
  ON notifykit_rate_limit_events (key, created_at);