feat: added checkout session #172

Niki2k1 · 2025-08-04T20:34:06Z

What kind of change does this PR introduce?

This PR adds sync for the checkout sessions (and line items)

Additional context

I created two new migrations (one for the checkout sessions and one for the line items.

I am not yet using this in production, so I am a little bit cautious. My tests worked fine though.

jackkru69 · 2025-08-06T21:18:33Z

We also need this feature, can someone watch this PR?

revoks · 2025-08-06T21:32:30Z

This feature is something we urgently need on our side as well.
Would love to see this merged - thanks so much for your work!

kevcodez

Thanks for the contribution!

Can you please

Add the webhooks to the README as supported
Clarify whether line items are present in webhooks
Add an appropriate webhook test

kevcodez · 2025-08-07T03:07:42Z

packages/sync-engine/src/stripeSync.ts

+    )
+
+    // Upsert line items into separate table
+    const lineItemsPromises = checkoutSessions


This should be a single upsert and not one upsert per line item ideally

Are line items sent via the webhook by default? Otherwise the checkout session line items would never be there for a regular webhook

Niki2k1 · 2025-08-07T05:45:45Z

Thank you for the Review.

I inspected a checkout session event and indeed there are no line items, so either I would've to fetch them via the api or ignore them and only sync checkout sessions. What do you think?

For my use-case it would be really nice to have the line items directly when syncing

kevcodez · 2025-08-07T06:01:47Z

@Niki2k1 yeah makes sense, i think a check session without persisting the line items themselves would not add too much value.

I think it would be best to call stripe.checkout.sessions.listLineItems (and set a high limit of 100) if no line items is provided - we also have an expansion feature that can be used (see expandEntity).

So

If line items is already present, use expandEntity
If line items are not present, pull from API using listLineItems

jackkru69 · 2025-08-19T13:28:37Z

@Niki2k1 Hi, are you going to refine this PR?

Niki2k1 · 2025-08-19T13:38:18Z

@jackkru69 yes, I will implement the changes and update the pr, but at earliest next week. Can't promise anything though.

Niki2k1 · 2025-08-27T20:51:52Z

I updated the PR and added/changed the following items:

Updated README
Added tests with anonymized webhook payload from my production app
Improved LineItems Handling, by adding a custom fill function that retrieves items with listLineItems function and upserts them into the db

feat: handle custom checkout.session tableName feat: added syncTimestamp logic for checkout sessions

Niki2k1 · 2025-08-27T21:32:35Z

I merged main and resolved some conflicts and also added the new lastSyncedAt logic

jackkru69 · 2025-08-28T21:40:59Z

@kevcodez Could you check it, pls?

andrey-utkin · 2025-08-29T22:47:43Z

Hi @Niki2k1 , thanks for your very useful contribution!

I have just tried a container image built from your feature branch, with database containing products of backfill by mainline codebase. I also retried from scratch. I use backfill with POST /sync. I got this error. Maybe the reason is that we might encounter more than one event about the same session, and see the same (by id) line item more than once?

stripe-sync-1  | {"level":"info","time":"2025-08-29T22:12:15.976Z","pid":1,"hostname":"e92d0932b284","msg":"Syncing checkout sessions"}
stripe-sync-1  | {"level":"info","time":"2025-08-29T22:12:15.976Z","pid":1,"hostname":"e92d0932b284","msg":"Fetching items to sync from Stripe"}
stripe-sync-1  | {"level":"info","time":"2025-08-29T22:12:58.862Z","pid":1,"hostname":"e92d0932b284","msg":"Upserting 1819 items"}
postgres-1     | 2025-08-29 22:12:59.458 UTC [54] ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification
postgres-1     | 2025-08-29 22:12:59.458 UTC [54] STATEMENT:  INSERT INTO "stripe"."checkout_session_line_items" (
postgres-1     |                "id","object","adjustable_quantity","amount_subtotal","amount_total","currency","description","discounts","price","quantity","taxes","checkout_session", "last_synced_at"
postgres-1     |              )
postgres-1     |              VALUES (
postgres-1     |                $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12, $13
postgres-1     |              )
postgres-1     |              ON CONFLICT (id) DO UPDATE SET
postgres-1     |                "id" = EXCLUDED."id","object" = EXCLUDED."object","adjustable_quantity" = EXCLUDED."adjustable_quantity","amount_subtotal" = EXCLUDED."amount_subtotal","amount_total" = EXCLUDED."amount_total","currency" = EXCLUDED."currency","description" = EXCLUDED."description","discounts" = EXCLUDED."discounts","price" = EXCLUDED."price","quantity" = EXCLUDED."quantity","taxes" = EXCLUDED."taxes","checkout_session" = EXCLUDED."checkout_session",
postgres-1     |                last_synced_at = $14
postgres-1     |              WHERE "checkout_session_line_items"."last_synced_at" IS NULL
postgres-1     |                 OR "checkout_session_line_items"."last_synced_at" < $15;

Niki2k1 · 2025-08-30T08:54:09Z

@andrey-utkin Awesome! Thank you so much for taking the time to test this.
I just tested with my Stripe Test Environment. I will also test it with prod, to try if I can reproduce and fix the error.

andrey-utkin · 2025-08-30T21:21:18Z

I guess ON CONFLICT (id) would work if checkout_session_line_items id itself was a primary key.

I am not even sure what this primary key specification means: primary key ("checkout_session", "id")

Indexes/keys end up this way:

Indexes:
    "checkout_session_line_items_pkey" PRIMARY KEY, btree (checkout_session, id)
    "stripe_checkout_session_line_items_price_idx" btree (price)
    "stripe_checkout_session_line_items_session_idx" btree (checkout_session)
Foreign-key constraints:
    "checkout_session_line_items_checkout_session_fkey" FOREIGN KEY (checkout_session) REFERENCES checkout_sessions(id)

kevcodez · 2025-09-08T05:36:30Z

packages/sync-engine/src/database/migrations/0036_checkout_session_line_items.sql

@@ -0,0 +1,26 @@
+create table if not exists "stripe"."checkout_session_line_items" (
+  "id" text,


These should be unique and therefore this should have a primary key to make the upsert work

kevcodez · 2025-09-08T05:37:23Z

Given checkout line item has a unique id, was there a reason not marking this as primary key in the migration? This is leading to the insert failing

Also, can you please add an integration test that fully covers this including the upsert of the line items? We should catch an error like this during an integration test and not a manual one ideally

…primary key on id

Niki2k1 · 2025-09-08T17:12:42Z

I replaced the composite key with a primary key just on the id field.
With my production stripe data it works now. I'll look into the integration tests.

fix: listLineItems mock fix: lineItems migration and schema to include correct fields

Niki2k1 · 2025-09-14T11:26:34Z

I added a test (was a good idea because I found multiple issues like missing fields)
For now, it's only to test the insert. Do we need other cases as well?

I also added on delete cascade on the lineItems reference fields.
So when a checkout session is deleted, the line items are getting deleted as well. For the prices reference, that's not really needed, as prices and checkout_sessions can't be deleted in stripe. But was useful in testing (for cleanup) and I don't see what would speak against it.

kevcodez

Overall LGTM with the tests, two small things I noticed that would have to be addressed so we can merge this 👍

kevcodez · 2025-09-15T06:29:25Z

packages/sync-engine/src/stripeSync.ts

      return this.stripe.refunds.retrieve(stripeId).then((it) => this.upsertRefunds([it]))
+    } else if (stripeId.startsWith('cs_')) {
+      return this.stripe.checkout.sessions
+        .retrieve(stripeId)


Missing { expand: ['line_items'] } here?

The line_items are getting filled in the upsert itself via their own endpoint. They are not available in the /v1/checkout/sessions/:id endpoint.
correct me if I am wrong

Aren't you doing the expansion in here too? https://github.com/supabase/stripe-sync-engine/pull/172/files#diff-95fc8d6564ba293c6ce4a37ea33f73126fab928a0311b74c1a56b5b2d19d0bdeR122

I will remove it there then as well 😅
was left over from when I started.
I moved over to the fillCheckoutSessionsLineItems which uses the /v1/checkout/sessions/:id/line_items endpoint

Will also remove it here: https://github.com/supabase/stripe-sync-engine/pull/172/files/380d7b6106ac8b77421c7c61533afed6ae2255e4#diff-95fc8d6564ba293c6ce4a37ea33f73126fab928a0311b74c1a56b5b2d19d0bdeR919

is all covered by the fillCheckoutSessionsLineItems inside upsertCheckoutSessions

kevcodez · 2025-09-15T06:32:45Z

packages/sync-engine/src/stripeSync.ts


+  async fillCheckoutSessionsLineItems(checkoutSessionIds: string[], syncTimestamp?: string) {
+    for (const checkoutSessionId of checkoutSessionIds) {
+      const lineItemResponses = await this.stripe.checkout.sessions.listLineItems(


Stripe supports auto-pagination in the node sdk, see https://docs.stripe.com/api/pagination/auto?lang=node

You should likely be using for await to ensure that this iterates over all pages, i.e. if this has 101 items, you wouldn't get the last item

You can use expandEntity - example usage

await this.expandEntity(charges, 'refunds', (id) => this.stripe.refunds.list({ charge: id, limit: 100 }) )

This will paginate through all items and replace the property, then you can just do your upsert afterwards

fix: backfill prices before inserting lineItems feat: auto pagination for lineItems

fix(checkoutSessions test): race condition

Niki2k1 · 2025-09-15T10:13:22Z

removed line_items expands
backfill prices (needed for the new price table reference) to ensure prices are there before insertion of lineItems
using auto pagination feature
fix tests

also tested again with my live data

kevcodez · 2025-09-15T10:26:26Z

Nice! Thanks for the contribution - will trigger a release now

Niki2k1 · 2025-09-15T10:27:30Z

Thank you!
Also for the reviews.
I learned cool new stuff.

github-actions · 2025-09-15T10:28:21Z

🎉 This PR is included in version 0.42.0 🎉

The release is available on GitHub release

Your semantic-release bot 📦🚀

Niki2k1 added 2 commits August 4, 2025 22:27

feat: added checkout session

856f230

fix: remove console.log

5daac5d

kevcodez reviewed Aug 7, 2025

View reviewed changes

Niki2k1 added 3 commits August 27, 2025 20:16

docs: show checkout.session events as supported

bb7f403

feat(checkout_session): added (anonymized) test stripe payload

e9369c7

refactor: fill line_items as they are not present in webhook

0dd9799

Niki2k1 added 2 commits August 27, 2025 22:54

Merge branch 'main' into feat/checkout-sessions

7bef589

fix: renamed migrations

c7ce44d

feat: handle custom checkout.session tableName feat: added syncTimestamp logic for checkout sessions

Niki2k1 marked this pull request as ready for review August 27, 2025 21:32

kevcodez reviewed Sep 8, 2025

View reviewed changes

fix(checkout_session_line_items): replace composite primary key with …

0de1cdb

…primary key on id

Niki2k1 added 5 commits September 8, 2025 19:14

Merge branch 'main' into feat/checkout-sessions

fec3b2c

fix: use getSyncTimestamp

50ec151

feat: checkout sessions test

c749de0

fix: listLineItems mock fix: lineItems migration and schema to include correct fields

Merge branch 'main' into feat/checkout-sessions

1830278

fix: use new refetched logic

380d7b6

kevcodez reviewed Sep 15, 2025

View reviewed changes

Niki2k1 added 4 commits September 15, 2025 10:19

fix: remove unused line_items expand

97f6390

feat: backfillPrices function

8d0b285

fix: backfill prices before inserting lineItems feat: auto pagination for lineItems

fix: implement asyncIterator for mock listLineItems Stripe

254589b

fix(mockStripe): added price retrieve endpoint

12a7a6f

fix(checkoutSessions test): race condition

kevcodez approved these changes Sep 15, 2025

View reviewed changes

kevcodez merged commit 7fbaab6 into supabase:main Sep 15, 2025
2 checks passed

github-actions bot added the released label Sep 15, 2025

		@@ -0,0 +1,26 @@
		create table if not exists "stripe"."checkout_session_line_items" (
		"id" text,

Uh oh!

feat: added checkout session #172

feat: added checkout session #172

Uh oh!

Conversation

Niki2k1 commented Aug 4, 2025

What kind of change does this PR introduce?

Additional context

Uh oh!

jackkru69 commented Aug 6, 2025

Uh oh!

revoks commented Aug 6, 2025

Uh oh!

kevcodez left a comment

Choose a reason for hiding this comment

Uh oh!

kevcodez Aug 7, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Niki2k1 commented Aug 7, 2025

Uh oh!

kevcodez commented Aug 7, 2025

Uh oh!

jackkru69 commented Aug 19, 2025

Uh oh!

Niki2k1 commented Aug 19, 2025

Uh oh!

Niki2k1 commented Aug 27, 2025

Uh oh!

Niki2k1 commented Aug 27, 2025

Uh oh!

jackkru69 commented Aug 28, 2025

Uh oh!

andrey-utkin commented Aug 29, 2025

Uh oh!

Niki2k1 commented Aug 30, 2025

Uh oh!

andrey-utkin commented Aug 30, 2025

Uh oh!

Choose a reason for hiding this comment

Uh oh!

kevcodez commented Sep 8, 2025

Uh oh!

Niki2k1 commented Sep 8, 2025

Uh oh!

Niki2k1 commented Sep 14, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

kevcodez left a comment

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Niki2k1 commented Sep 15, 2025

Uh oh!

Uh oh!

kevcodez commented Sep 15, 2025

Uh oh!

Niki2k1 commented Sep 15, 2025

Uh oh!

github-actions bot commented Sep 15, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

kevcodez Aug 7, 2025 •

edited

Loading

Niki2k1 commented Sep 14, 2025 •

edited

Loading