stripe-graphile-example.md

SQL Trigger to Create Function

CREATE OR REPLACE FUNCTION app_private.tg__user_stripe_customer_sync() RETURNS trigger AS
$$
DECLARE
    stripe_customer app_private.stripe_customers;
BEGIN
    SELECT *
    INTO stripe_customer
    FROM app_private.stripe_customers
    WHERE user_id = new.id;

    IF stripe_customer IS NULL
    THEN
        PERFORM graphile_worker.add_job('stripe__sync_customer', JSON_BUILD_OBJECT('id', new.id), 'stripe__customer_sync');
    END IF;

    RETURN new;
END;
$$ LANGUAGE plpgsql VOLATILE
                    SECURITY DEFINER
                    SET search_path TO pg_catalog, public, pg_temp;

DROP TRIGGER IF EXISTS _600_stripe_customer_sync ON app_public.users;
CREATE TRIGGER _600_stripe_customer_sync
    BEFORE INSERT OR UPDATE
    ON app_public.users
    FOR EACH ROW
EXECUTE PROCEDURE app_private.tg__user_stripe_customer_sync();

Worker

import { Task } from "graphile-worker";
import { stripe } from "../utils";

interface StripeSyncCustomerPayload {
  /**
   * user id
   */
  id: string;
}

const task: Task = async (inPayload, { addJob, withPgClient }) => {
  const payload: StripeSyncCustomerPayload = inPayload as any;
  const { id: userId } = payload;
  const {
    rows: [user],
  } = await withPgClient((pgClient) =>
    pgClient.query<{ name: string }>(
      `SELECT name
           FROM app_public.users
           WHERE id = $1`,
      [userId]
    )
  );
  if (!user) {
    throw "A user was not found";
  }

  const {
    rows: [userEmail],
  } = await withPgClient((pgClient) =>
    pgClient.query<{ email: string }>(
      ` SELECT email
            FROM app_public.user_emails
            WHERE user_id = $1
            ORDER BY user_emails.is_verified DESC, -- Prefer verified email
                     user_emails.created_at ASC -- Failing that, prefer the first registered (unverified users _should_ verify before logging in)
          `,
      [userId]
    )
  );
  const createStripeCustomerParams: {
    name: string;
    email?: string;
    metadata: Record<string, string>;
  } = {
    name: user.name,
    metadata: { userId },
  };
  if (userEmail) {
    createStripeCustomerParams.email = userEmail.email;
  }
  const {
    rows: [stripeCustomer],
  } = await withPgClient((pgClient) =>
    pgClient.query(
      "SELECT * FROM app_private.stripe_customers WHERE user_id=$1",
      [userId]
    )
  );
  if (!stripeCustomer) {
    const customer = await stripe.customers.create(createStripeCustomerParams);
    await withPgClient((pgClient) =>
      pgClient.query(
        `INSERT INTO app_private.stripe_customers (user_id, stripe_id)
             VALUES ($1, $2)`,
        [userId, customer.id]
      )
    );
  } else {
    console.log("Stripe update customer not implemented yet");
  }
};

module.exports = task;

Extended Stripe GraphQL schema

import { gql, makeExtendSchemaPlugin } from "graphile-utils";
import { OurGraphQLContext } from "../middleware/postgraphile";
import stripe from "../utils/stripe";

const StripePlugin = makeExtendSchemaPlugin((build) => ({
  typeDefs: gql`
    extend type User {
      """
      Returns the stripe customers secret
      """
      stripeClientSecret: String @requires(columns: ["id"])
    }
  `,
  resolvers: {
    User: {
      async stripeClientSecret(
        user,
        _,
        context: OurGraphQLContext,
        resolveInfo
      ) {
        // console.log("user", user, context.pgClient);
        const { rows: stripeCustomers } = await context.rootPgPool.query<{
          stripe_id: string;
        }>(
          "SELECT stripe_id FROM app_private.stripe_customers WHERE  user_id = $1",
          [user.id]
        );
        const stripeCustomer = stripeCustomers?.[0];
        if (stripeCustomer) {
          const setupIntent = await stripe.setupIntents.create({
            customer: stripeCustomer.stripe_id,
          });
          return setupIntent.client_secret;
        }
      },
    },
  },
}));

export default PassportLoginPlugin;