[Old version updated] Sync Stripe with Database Version 2

[KazeroG]

This source code demonstrates how to synchronize data from Stripe, a payment processing platform, with a local database using Prisma, an ORM (Object Relational Mapping) tool. The script performs several key operations, including fetching data from Stripe, mapping Stripe customers to local users, updating user information with Stripe customer IDs, and seeding the local database with Stripe products, prices, and subscriptions. Below is a detailed breakdown of the code, including its structure, functions, and purpose.

Overview

• Prisma Setup: Initializes the Prisma client to interact with the database.
• Stripe Setup: Creates a Stripe instance configured with an API version and secret key.
• Synchronization Process: The sync asynchronous function orchestrates the entire synchronization process.
• Error Handling: Catches and logs errors that occur during the synchronization process.
• Data Fetching and Processing: Functions to fetch data from Stripe and process it for database insertion.
• Database Operations: Functions to clean up, seed, and log statistics about the database after synchronization.

Detailed Documentation

Prisma Client Initialization

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

• Imports the PrismaClient from the @prisma/client package.
• Initializes a new Prisma client instance for database operations.

Stripe Instance Creation

import Stripe from 'stripe';
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY, { apiVersion: '2023-10-16' });

• Imports the Stripe module.
• Creates a Stripe instance using the secret key stored in the environment variable STRIPE_SECRET_KEY and sets the API version to '2023-10-16'.

Synchronization Function (sync)

const sync = async () => {
  // Error handling and synchronization logic.
};

• An asynchronous function that orchestrates the synchronization of data from Stripe to the local database.
• Handles errors that might occur during the process and logs the success or failure of the operation.

Helper Functions

• getStripeInstance: Ensures that the Stripe secret key is set and returns the Stripe instance.
• fetchStripeData: Fetches active products, prices, subscriptions, and customers from Stripe.
• mapUsersToCustomers: Maps local user records to Stripe customers based on email.
• updateUsersWithStripeCustomerId: Updates local users with their corresponding Stripe customer IDs.
• performDatabaseOperations: Orchestrates database operations including cleaning up old data and seeding with new data from Stripe.
• cleanup, seedServices, seedPrices, seedSubscriptions: Helper functions to perform specific database seeding operations.
• printStats: Logs the count of products, prices, and subscriptions synced to the database.

Error Handling

process.on('uncaughtException', (error) => {
  console.error('Uncaught Exception:', error);
  process.exit(1);
});

• Listens for uncaught exceptions in the Node.js process.
• Logs the error and exits the process with a status code of 1 to indicate failure.

Complete New Source Code

import { PrismaClient } from '@prisma/client';
import Stripe from 'stripe';

const prisma = new PrismaClient();

const sync = async () => {
  try {
    console.log('Starting sync with Stripe');
    const stripe = getStripeInstance();
    const { products, prices, subscriptions, customers } = await fetchStripeData(stripe);

    let users = await prisma.user.findMany();
    const userMap = mapUsersToCustomers(users, customers.data);

    users = await updateUsersWithStripeCustomerId(users, userMap, prisma);
    await performDatabaseOperations(prices, products, subscriptions, userMap, prisma);

    console.log('Sync completed successfully');
  } catch (error) {
    console.error('Error syncing with Stripe:', error);
    process.exit(1);
  }
};
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY, { apiVersion: '2023-10-16' });
const getStripeInstance = () => {
  if (!process.env.STRIPE_SECRET_KEY) {
    throw new Error('STRIPE_SECRET_KEY environment variable not set');
  }
  return stripe
};

const fetchStripeData = async (stripe) => {
  const [products, prices, subscriptions, customers] = await Promise.all([
    stripe.products.list({ active: true }),
    stripe.prices.list({ active: true }),
    stripe.subscriptions.list({ status: 'active' }),
    stripe.customers.list(),
  ]);

  return { products, prices, subscriptions, customers };
};

const mapUsersToCustomers = (users, customers) => {
  return users.reduce((map, user) => {
    const customer = customers.find(c => c.email === user.email);
    if (customer) map[customer.id] = user;
    return map;
  }, {});
};

const updateUsersWithStripeCustomerId = async (users, userMap, prisma) => {
  return Promise.all(users.map(user => {
    const customer = userMap[user.email];
    if (customer) {
      return prisma.user.update({
        where: { id: user.id },
        data: { stripeCustomerId: customer.id },
      });
    }
    return user;
  }));
};

const performDatabaseOperations = async (prices, products, subscriptions, userMap, prisma) => {
  await prisma.$transaction(cleanup(prisma));
  const services = await prisma.$transaction(seedServices(products.data, prisma));
  await prisma.$transaction(seedPrices(prices.data, prisma, services));
  await prisma.$transaction(seedSubscriptions(subscriptions.data, prisma, userMap));
  await printStats(prisma);
};

const cleanup = (prisma) => {
  return [
    prisma.price.deleteMany({}),
    prisma.service.deleteMany({}),
    prisma.subscription.deleteMany({}),
  ];
};

const seedServices = (products, prisma) => {
  return products.map(data =>
    prisma.service.create({
      data: {
        id: data.id,
        description: data.description || '',
        features: (data.features || []).map(a => a.name),
        image: data.images.length > 0 ? data.images[0] : '',
        name: data.name,
        created: new Date(data.created * 1000),
      },
    })
  );
};

const seedPrices = (prices, prisma, services) => {
  return prices.map(data => {
    const service = services.find(service => service.id === data.product);
    if (service) {
      return prisma.price.create({
        data: {
          id: data.id,
          billingScheme: data.billing_scheme,
          currency: data.currency,
          serviceId: service.id,
          amount: data.unit_amount ? data.unit_amount / 100 : undefined,
          metadata: data.recurring,
          type: data.type,
          created: new Date(data.created * 1000),
        },
      });
    }
  }).filter(Boolean);
};

const seedSubscriptions = (subscriptions, prisma, userMap) => {
  return subscriptions.map(data => {
    const user = userMap[data.customer];
    if (user) {
      return prisma.subscription.create({
        data: {
          id: data.id,
          customerId: data.customer,
          priceId: data.items.data[0].price.id,
          active: data.status === 'active',
          startDate: new Date(data.start_date * 1000),
          endDate: new Date(data.current_period_end * 1000),
          cancelAt: data.cancel_at ? new Date(data.cancel_at * 1000) : null,
          userId: user.id,
        },
      });
    }
  }).filter(Boolean);
};

const printStats = async (prisma) => {
  const [productCount, priceCount, subscriptionCount] = await Promise.all([
    prisma.service.count(),
    prisma.price.count(),
    prisma.subscription.count(),
  ]);

  console.log('Products synced:', productCount);
  console.log('Prices synced:', priceCount);
  console.log('Subscriptions synced:', subscriptionCount);
};

process.on('uncaughtException', (error) => {
  console.error('Uncaught Exception:', error);
  process.exit(1);
});

export default sync;

[KazeroG]

We have to update the Prisma Schema to make it work

The documentation comparison between the old and new source code focuses on the Prisma schema for a database application. This comparison highlights the structural changes made to the database schema and their implications.

Overview of Schema

Both versions of the source code define a database schema using Prisma, aimed at a PostgreSQL database. The schema includes definitions for various models such as Account, Session, VerificationToken, User, Team, TeamMember, Invitation, PasswordReset, ApiKey, Subscription, Service, and Price. These models are designed to manage user accounts, authentication sessions, user roles, team memberships, and service subscriptions.

Key Changes

1. Addition of stripeCustomerId in the User Model:

   • The new code introduces a stripeCustomerId field in the User model. This addition suggests an integration with Stripe to manage customer information for billing and subscriptions directly within the user records.

2. Association of Subscription with User:

   • In the new schema, the Subscription model explicitly includes a userId field along with a relation to the User model. This change solidifies the relationship between users and their subscriptions, allowing for direct queries of a user's subscriptions and vice versa.

Implications of the Changes

1. Stripe Integration:

   • By adding the stripeCustomerId to the User model, the application can now directly link Stripe customer records with application user records. This facilitates easier management of subscription billing and payments, enhancing the application's e-commerce capabilities.

2. Enhanced Subscription Management:

   • The direct linking of subscriptions to users through the userId field in the Subscription model simplifies the management of user subscriptions. It allows for easier tracking of which services a user is subscribed to, improving the application's ability to manage access to paid features or content.

Unchanged Aspects

• The overall structure and the majority of the models (Account, Session, VerificationToken, Team, TeamMember, Invitation, PasswordReset, ApiKey, Service, and Price) remain unchanged. This indicates that the core functionality related to account management, session handling, and team collaboration remains stable.
• The database configuration, including the use of PostgreSQL and the prisma-client-js client, remains consistent. This stability ensures that the changes are backward compatible and do not require alterations to the database setup or connection logic.

Conclusion

The modifications from the old to the new source code represent a focused enhancement of the application's user and subscription management capabilities, specifically through the integration of Stripe and a clearer association between users and their subscriptions. These changes are indicative of an application's evolution to incorporate more complex billing and subscription management features directly within its data model, improving overall functionality and user experience.

Complete New Source Code

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

enum Role {
  ADMIN
  OWNER
  MEMBER
}

model Account {
  id                String  @id @default(uuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(uuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

model User {
  id                     String    @id @default(uuid())
  name                   String
  email                  String    @unique
  emailVerified          DateTime?
  password               String?
  image                  String?
  createdAt              DateTime  @default(now())
  updatedAt              DateTime  @default(now())
  invalid_login_attempts Int       @default(0)
  lockedAt               DateTime?
  stripeCustomerId       String? // Ajoutez cette ligne

  teamMembers   TeamMember[]
  accounts      Account[]
  sessions      Session[]
  invitations   Invitation[]
  subscriptions Subscription[]
}

model Team {
  id              String       @id @default(uuid())
  name            String
  slug            String       @unique
  domain          String?      @unique
  defaultRole     Role         @default(MEMBER)
  billingId       String?
  billingProvider String?
  createdAt       DateTime     @default(now())
  updatedAt       DateTime     @default(now())
  members         TeamMember[]
  invitations     Invitation[]
  apiKeys         ApiKey[]
}

model TeamMember {
  id        String   @id @default(uuid())
  teamId    String
  userId    String
  role      Role     @default(MEMBER)
  createdAt DateTime @default(now())
  updatedAt DateTime @default(now())

  team Team @relation(fields: [teamId], references: [id], onDelete: Cascade)
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([teamId, userId])
  @@index([userId])
}

model Invitation {
  id             String   @id @default(uuid())
  teamId         String
  email          String?
  role           Role     @default(MEMBER)
  token          String   @unique
  expires        DateTime
  invitedBy      String
  createdAt      DateTime @default(now())
  updatedAt      DateTime @default(now())
  sentViaEmail   Boolean  @default(true)
  allowedDomains String[] @default([])

  user User @relation(fields: [invitedBy], references: [id], onDelete: Cascade)
  team Team @relation(fields: [teamId], references: [id], onDelete: Cascade)

  @@unique([teamId, email])
}

model PasswordReset {
  id        Int      @id @default(autoincrement())
  email     String
  token     String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  expiresAt DateTime
}

model ApiKey {
  id         String    @id @default(uuid())
  name       String
  teamId     String
  hashedKey  String    @unique
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @default(now())
  expiresAt  DateTime?
  lastUsedAt DateTime?

  team Team @relation(fields: [teamId], references: [id], onDelete: Cascade)
}

model Subscription {
  id         String    @id
  customerId String
  priceId    String
  active     Boolean   @default(false)
  startDate  DateTime
  endDate    DateTime
  cancelAt   DateTime?
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @default(now())
  userId     String
  User       User?     @relation(fields: [userId], references: [id])

  @@index([customerId])
}

model Service {
  id          String   @id @default(uuid())
  description String
  features    String[]
  image       String
  name        String
  created     DateTime
  createdAt   DateTime @default(now())
  updatedAt   DateTime @default(now())
  Price       Price[]
}

model Price {
  id            String   @id @default(uuid())
  billingScheme String
  currency      String
  serviceId     String
  amount        Int?
  metadata      Json
  type          String
  created       DateTime

  service Service @relation(fields: [serviceId], references: [id], onDelete: Cascade)
}

[ukrocks007]

Thank you @KazeroG
We will review and revert on your suggestions.

[pankaj9296]

+1 much needed update

[greeneggsandyaml]

Any update on this?