* Upsert invoice data to local cache (org_invoices table). * Called from Stripe webhook handlers to keep invoice data in sync.
( pool: ReturnType<typeof getPool>, invoice: Stripe.Invoice, workosOrgId: string | null, productName: string | null = null )
| 342 | * Called from Stripe webhook handlers to keep invoice data in sync. |
| 343 | */ |
| 344 | async function upsertInvoiceCache( |
| 345 | pool: ReturnType<typeof getPool>, |
| 346 | invoice: Stripe.Invoice, |
| 347 | workosOrgId: string | null, |
| 348 | productName: string | null = null |
| 349 | ): Promise<void> { |
| 350 | try { |
| 351 | await pool.query( |
| 352 | `INSERT INTO org_invoices ( |
| 353 | stripe_invoice_id, |
| 354 | stripe_customer_id, |
| 355 | workos_organization_id, |
| 356 | status, |
| 357 | amount_due, |
| 358 | amount_paid, |
| 359 | currency, |
| 360 | invoice_number, |
| 361 | hosted_invoice_url, |
| 362 | invoice_pdf, |
| 363 | product_name, |
| 364 | customer_email, |
| 365 | created_at, |
| 366 | due_date, |
| 367 | paid_at, |
| 368 | voided_at, |
| 369 | stripe_updated_at |
| 370 | ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, NOW()) |
| 371 | ON CONFLICT (stripe_invoice_id) DO UPDATE SET |
| 372 | status = EXCLUDED.status, |
| 373 | amount_due = EXCLUDED.amount_due, |
| 374 | amount_paid = EXCLUDED.amount_paid, |
| 375 | invoice_number = EXCLUDED.invoice_number, |
| 376 | hosted_invoice_url = EXCLUDED.hosted_invoice_url, |
| 377 | invoice_pdf = EXCLUDED.invoice_pdf, |
| 378 | product_name = COALESCE(EXCLUDED.product_name, org_invoices.product_name), |
| 379 | customer_email = EXCLUDED.customer_email, |
| 380 | paid_at = EXCLUDED.paid_at, |
| 381 | voided_at = EXCLUDED.voided_at, |
| 382 | stripe_updated_at = NOW()`, |
| 383 | [ |
| 384 | invoice.id, |
| 385 | invoice.customer as string, |
| 386 | workosOrgId, |
| 387 | invoice.status, |
| 388 | invoice.amount_due, |
| 389 | invoice.amount_paid, |
| 390 | invoice.currency, |
| 391 | invoice.number || null, |
| 392 | invoice.hosted_invoice_url || null, |
| 393 | invoice.invoice_pdf || null, |
| 394 | productName, |
| 395 | typeof invoice.customer_email === 'string' ? invoice.customer_email : null, |
| 396 | new Date(invoice.created * 1000), |
| 397 | invoice.due_date ? new Date(invoice.due_date * 1000) : null, |
| 398 | invoice.status === 'paid' && invoice.status_transitions?.paid_at |
| 399 | ? new Date(invoice.status_transitions.paid_at * 1000) |
| 400 | : null, |
| 401 | invoice.status === 'void' ? new Date() : null, |