DunningTracker Model

The DunningTracker model tracks failed billing attempts for subscription contracts and manages the dunning process to recover failed payments.

Overview

Dunning trackers record each failed billing attempt and prevent duplicate dunning actions. This model handles:

Recording failed billing attempts
Preventing duplicate dunning for the same failure
Tracking dunning completion status
Managing billing cycle failure reasons

Prisma Schema

model DunningTracker {
  id                Int       @id @default(autoincrement())
  shop              String
  contractId        String
  billingCycleIndex Int
  failureReason     String
  completedAt       DateTime?
  completedReason   String?

  @@unique([shop, contractId, billingCycleIndex, failureReason], name: "uniqueBillingCycleFailure")
  @@index([completedAt])
}

Show Schema Field Details

Int

Auto-incrementing primary key

shop

String

Shop domain (e.g., example.myshopify.com)

contractId

String

Subscription contract ID (e.g., gid://shopify/SubscriptionContract/123)

billingCycleIndex

Int

The billing cycle number where the failure occurred

failureReason

String

The error code or reason for the billing failure (e.g., PAYMENT_METHOD_DECLINED, INSUFFICIENT_INVENTORY)

completedAt

DateTime

Timestamp when the dunning process was completed (null if still active)

completedReason

String

Reason why the dunning was completed (e.g., payment_successful, customer_updated_payment, contract_cancelled)

Indexes and Constraints:

Unique Constraint: uniqueBillingCycleFailure ensures one tracker per shop + contract + cycle + failure reason
Index on completedAt: Optimizes queries for active (uncompleted) dunning trackers

Core Methods

findOrCreateBy

Finds an existing dunning tracker or creates a new one for a specific billing failure.

shop

string

required

The shop domain

contractId

string

required

The subscription contract ID

billingCycleIndex

number

required

The billing cycle index where the failure occurred

failureReason

string

required

The error code or failure reason

Returns: Promise<DunningTracker> TypeScript Signature:

interface UpsertArgs {
  shop: string;
  contractId: string;
  billingCycleIndex: number;
  failureReason: string;
}

async function findOrCreateBy({
  shop,
  contractId,
  billingCycleIndex,
  failureReason,
}: UpsertArgs): Promise<DunningTracker>

Usage Example:

import { findOrCreateBy } from '~/models/DunningTracker/DunningTracker.server';

const tracker = await findOrCreateBy({
  shop: 'example.myshopify.com',
  contractId: 'gid://shopify/SubscriptionContract/123',
  billingCycleIndex: 5,
  failureReason: 'PAYMENT_METHOD_DECLINED',
});

if (tracker.completedAt) {
  console.log('Dunning already completed for this failure');
  console.log('Completed reason:', tracker.completedReason);
} else {
  console.log('Dunning tracker created, proceeding with dunning process');
}

Behavior:

Uses Prisma upsert operation
If tracker exists, returns existing record unchanged
If tracker doesn’t exist, creates new record
Prevents duplicate dunning actions for the same failure

Prisma Operation:

return await prisma.dunningTracker.upsert({
  where: {
    uniqueBillingCycleFailure: {
      shop,
      contractId,
      billingCycleIndex,
      failureReason,
    },
  },
  create: {
    shop,
    contractId,
    billingCycleIndex,
    failureReason,
  },
  update: {}, // No updates on existing records
});

markCompleted

Marks a dunning tracker as completed with the current timestamp.

tracker

DunningTracker

required

The dunning tracker to mark as completed

Returns: Promise<void> TypeScript Signature:

async function markCompleted(tracker: DunningTracker): Promise<void>

Usage Example:

import { findOrCreateBy, markCompleted } from '~/models/DunningTracker/DunningTracker.server';

// Find tracker for a failed billing attempt
const tracker = await findOrCreateBy({
  shop: 'example.myshopify.com',
  contractId: 'gid://shopify/SubscriptionContract/123',
  billingCycleIndex: 5,
  failureReason: 'PAYMENT_METHOD_DECLINED',
});

// Process dunning (send emails, notifications, etc.)
await sendDunningEmail(tracker.contractId);

// Check if payment was successful after dunning
const paymentSuccessful = await checkPaymentStatus(tracker.contractId);

if (paymentSuccessful) {
  await markCompleted(tracker);
  console.log('Dunning completed successfully');
}

Implementation:

export async function markCompleted(tracker: DunningTracker): Promise<void> {
  const now = DateTime.utc().toJSDate();

  await prisma.dunningTracker.update({
    where: { id: tracker.id },
    data: { completedAt: now },
  });
}

Note: This method only sets the completedAt timestamp. To also set a completion reason, use direct Prisma operations.

TypeScript Interfaces

DunningTracker

interface DunningTracker {
  id: number;
  shop: string;
  contractId: string;
  billingCycleIndex: number;
  failureReason: string;
  completedAt: Date | null;
  completedReason: string | null;
}

UpsertArgs

interface UpsertArgs {
  shop: string;
  contractId: string;
  billingCycleIndex: number;
  failureReason: string;
}

Common Patterns

Dunning Workflow Integration

import { findOrCreateBy, markCompleted } from '~/models/DunningTracker/DunningTracker.server';
import { findSubscriptionContractWithBillingCycle } from '~/models/SubscriptionContract/SubscriptionContract.server';

export async function processDunningForFailedBilling(
  shop: string,
  contractId: string,
  billingCycleDate: string,
) {
  // Get contract and billing cycle details
  const { subscriptionContract, subscriptionBillingCycle } = 
    await findSubscriptionContractWithBillingCycle({
      shop,
      contractId,
      date: billingCycleDate,
    });

  // Get the latest failed billing attempt
  const failedAttempt = subscriptionBillingCycle.billingAttempts.edges
    .find(({ node }) => node.errorCode);

  if (!failedAttempt) {
    console.log('No failed billing attempts found');
    return;
  }

  // Track or retrieve existing dunning tracker
  const tracker = await findOrCreateBy({
    shop,
    contractId,
    billingCycleIndex: subscriptionBillingCycle.cycleIndex,
    failureReason: failedAttempt.node.errorCode,
  });

  if (tracker.completedAt) {
    console.log('Dunning already completed, skipping');
    return;
  }

  // Perform dunning actions
  await sendDunningNotification(subscriptionContract.customer.id, {
    errorCode: failedAttempt.node.errorCode,
    contractId,
  });

  console.log('Dunning notification sent');
}

Finding Active Dunning Trackers

import prisma from '~/db.server';

export async function getActiveDunningTrackers(shop: string) {
  return await prisma.dunningTracker.findMany({
    where: {
      shop,
      completedAt: null,
    },
    orderBy: {
      id: 'desc',
    },
  });
}

const activeTrackers = await getActiveDunningTrackers('example.myshopify.com');
console.log(`Found ${activeTrackers.length} active dunning trackers`);

activeTrackers.forEach(tracker => {
  console.log(`Contract: ${tracker.contractId}`);
  console.log(`Cycle: ${tracker.billingCycleIndex}`);
  console.log(`Reason: ${tracker.failureReason}`);
});

Completing with Reason

import prisma from '~/db.server';
import { DateTime } from 'luxon';

export async function completeDunningWithReason(
  trackerId: number,
  reason: string,
) {
  const now = DateTime.utc().toJSDate();

  return await prisma.dunningTracker.update({
    where: { id: trackerId },
    data: {
      completedAt: now,
      completedReason: reason,
    },
  });
}

// Usage
await completeDunningWithReason(tracker.id, 'payment_successful');

Querying Dunning History

import prisma from '~/db.server';

export async function getDunningHistory(
  contractId: string,
  limit: number = 10,
) {
  return await prisma.dunningTracker.findMany({
    where: { contractId },
    orderBy: {
      billingCycleIndex: 'desc',
    },
    take: limit,
  });
}

const history = await getDunningHistory(
  'gid://shopify/SubscriptionContract/123'
);

console.log('Dunning History:');
history.forEach(tracker => {
  console.log(`Cycle ${tracker.billingCycleIndex}: ${tracker.failureReason}`);
  if (tracker.completedAt) {
    console.log(`  Completed: ${tracker.completedAt}`);
    console.log(`  Reason: ${tracker.completedReason}`);
  } else {
    console.log('  Status: Active');
  }
});

Cleaning Up Old Completed Trackers

import prisma from '~/db.server';
import { DateTime } from 'luxon';

export async function cleanupOldDunningTrackers(daysOld: number = 90) {
  const cutoffDate = DateTime.utc().minus({ days: daysOld }).toJSDate();

  const result = await prisma.dunningTracker.deleteMany({
    where: {
      completedAt: {
        lt: cutoffDate,
      },
    },
  });

  console.log(`Deleted ${result.count} old dunning trackers`);
  return result.count;
}

// Clean up trackers completed more than 90 days ago
await cleanupOldDunningTrackers(90);

Reporting on Failure Reasons

import prisma from '~/db.server';

export async function getDunningStatsByFailureReason(shop: string) {
  const trackers = await prisma.dunningTracker.findMany({
    where: { shop },
    select: {
      failureReason: true,
      completedAt: true,
    },
  });

  const stats = trackers.reduce((acc, tracker) => {
    if (!acc[tracker.failureReason]) {
      acc[tracker.failureReason] = { total: 0, completed: 0 };
    }
    acc[tracker.failureReason].total++;
    if (tracker.completedAt) {
      acc[tracker.failureReason].completed++;
    }
    return acc;
  }, {} as Record<string, { total: number; completed: number }>);

  return stats;
}

const stats = await getDunningStatsByFailureReason('example.myshopify.com');

Object.entries(stats).forEach(([reason, { total, completed }]) => {
  const activeCount = total - completed;
  console.log(`${reason}:`);
  console.log(`  Total: ${total}`);
  console.log(`  Completed: ${completed}`);
  console.log(`  Active: ${activeCount}`);
});

Common Failure Reasons

Typical failureReason values from Shopify Subscription Billing Attempts:

PAYMENT_METHOD_DECLINED - Customer’s payment method was declined
INSUFFICIENT_INVENTORY - Not enough inventory to fulfill the order
INVALID_PAYMENT_METHOD - Payment method is invalid or expired
CUSTOMER_NOT_FOUND - Customer associated with contract not found
INVENTORY_ALLOCATIONS_NOT_FOUND - Inventory allocation issues
AUTHENTICATION_ERROR - Payment authentication failed

Database Operations

import prisma from '~/db.server';

// Create
const tracker = await prisma.dunningTracker.create({
  data: {
    shop: 'example.myshopify.com',
    contractId: 'gid://shopify/SubscriptionContract/123',
    billingCycleIndex: 5,
    failureReason: 'PAYMENT_METHOD_DECLINED',
  },
});

// Read
const tracker = await prisma.dunningTracker.findUnique({
  where: { id: 1 },
});

// Update
const tracker = await prisma.dunningTracker.update({
  where: { id: 1 },
  data: {
    completedAt: new Date(),
    completedReason: 'payment_successful',
  },
});

// Delete
await prisma.dunningTracker.delete({
  where: { id: 1 },
});

// Find many
const trackers = await prisma.dunningTracker.findMany({
  where: {
    shop: 'example.myshopify.com',
    completedAt: null,
  },
});

Models

Jobs

Webhooks

Overview

Prisma Schema

Core Methods

findOrCreateBy

markCompleted

TypeScript Interfaces

DunningTracker

UpsertArgs

Common Patterns

Dunning Workflow Integration

Finding Active Dunning Trackers

Completing with Reason

Querying Dunning History

Cleaning Up Old Completed Trackers

Reporting on Failure Reasons

Common Failure Reasons

Database Operations

Build docs developers (and LLMs) love

Models

Jobs

Webhooks

​Overview

​Prisma Schema

​Core Methods

​findOrCreateBy

​markCompleted

​TypeScript Interfaces

​DunningTracker

​UpsertArgs

​Common Patterns

​Dunning Workflow Integration

​Finding Active Dunning Trackers

​Completing with Reason

​Querying Dunning History

​Cleaning Up Old Completed Trackers

​Reporting on Failure Reasons

​Common Failure Reasons

​Database Operations

​Related Resources

Build docs developers (and LLMs) love

Overview

Prisma Schema

Core Methods

findOrCreateBy

markCompleted

TypeScript Interfaces

DunningTracker

UpsertArgs

Common Patterns

Dunning Workflow Integration

Finding Active Dunning Trackers

Completing with Reason

Querying Dunning History

Cleaning Up Old Completed Trackers

Reporting on Failure Reasons

Common Failure Reasons

Database Operations

Related Resources