This content originally appeared on DEV Community and was authored by Yoshi
I’m in charge of the IT department for a volunteer organization with about 500 members. One day, I was shocked to learn that all the fee collection was being done manually. Even though it’s an annual membership system, the team was relying on manpower at the start of each year, and even mid-year registrations were handled by hand. A quaint, old-fashioned operation manual still existed. So, I decided to build a system that automates all collection, reminders, and aggregation.
The end users are the treasurers, who change every year and have varying levels of IT skills. Therefore, I made the interface as simple as possible. To issue invoices, they just need to enter the member ID into a spreadsheet. The system automatically generates a list of unpaid members, including their names, amounts, email addresses, and payment URLs.
In other words, the only touchpoint is the spreadsheet — everything else happens automatically.
System Architecture
Below is an overview diagram of the payment system architecture.
- A trigger is issued from the invoice issuance list.
- Through GAS, invoice data is stored in the BigQuery “invoice” table.
- A timer sends emails containing invoice URLs and updates the unpaid list.
- The user clicks the payment URL.
- Cloud Run verifies the secret and issues the invoice via the Stripe API.
- The user completes the payment on the Stripe invoice page.
- Stripe sends a payment-completed webhook to the Cloud Run endpoint.
- The system updates the payment status in both BigQuery and the spreadsheet.
System Components
Cloud Run (Checkout Service)
All sensitive information, such as Stripe secret keys, is managed through environment variables and securely accessed via Secret Manager.
Payment links are protected using HMAC signatures and time-limited URLs to prevent unauthorized access.
Additionally, hashed audit data (such as IP addresses and idempotency keys) are stored in BigQuery, enabling both re-execution prevention and traceability.
GAS Pipeline
The GAS operates from within a spreadsheet, reading environment settings (test/live) and terms directly from the sheet.
It embeds signed links into template emails and sends them, while all sent records are logged in the comms dataset.
When a payment deadline passes, webhook alerts are triggered and automatically recorded in the designated spreadsheet — effectively acting as a “watchdog for unpaid invoices.”
BigQuery
BigQuery functions as both an invoice ledger and a payment tracking system, generating invoice data from existing user records.
When a link is clicked, payment history is checked using the corresponding invoice_id.
With the MERGE statement, it performs “update if exists, insert if not”, ensuring that only one invoice record exists per user.
Separate datasets are used for each environment, allowing seamless switching without code modification.
Lessons Learned
Because of its sensitive nature, the payment system required special attention in several areas. The main points are summarized below.
HMAC Signatures and Time-Limited Links
Since payment links include user identification data, tampering could redirect a payment to another person.
To prevent this, Cloud Run adds an HMAC signature when generating each link.
HMAC (Hash-based Message Authentication Code) uses a shared secret to sign message content.
The invoice_id, user_id, and expiration timestamp are bundled and signed, then verified by Cloud Run.
If any part of the URL is modified, it becomes invalid immediately.
Naturally, the signing key is securely stored in Secret Manager.
Difference Between Stripe Session and Invoice
Initially, invoices were created on the Stripe side, and payment URLs were emailed to users.
However, these links expired after 24 hours — an unexpected limitation.
Further research revealed that Stripe provides two types of payment objects: Session and Invoice.
-
**Using Invoice
- Payment links never expire.
- Payment status updates automatically and is visible on the dashboard.
- Stripe can automatically send reminders to customers.
- However, external integrations and custom signing are limited.
-
**Using Session
- A lightweight, temporary payment page object (typically valid for 24 hours).
- Allows full developer control, including HMAC-signed links and BigQuery integration.
While Invoice offers strong management features, it leads to duplicate bookkeeping between Stripe’s customer DB and the internal ledger — and it also incurs extra fees.
Therefore, this project chose the Session-based approach for its flexibility and integration capability.
Stripe serves as the payment gateway, while BigQuery and Cloud Run handle billing management — a clear division of responsibilities.
Unique Payment Links Generated by Cloud Run
Each time a user opens the link, Cloud Run requests Stripe to create a new Session.
To prevent duplicate charges, it includes an idempotency key formatted as stripe_idempotency_prefix:invoice_id.
Stripe returns the same result for repeated requests with the same key, ensuring that the user is never charged more than once, even if the button is clicked multiple times.
BigQuery as the “Billing Ledger”
The invoice_id serves as the unique key in BigQuery for managing payment states.
Before completion, the status is “Unpaid.” Once the Stripe webhook sends a payment notification, the status updates to “Paid.”
Even if the process runs multiple times, only the existing record is updated — no duplicates occur.
Thanks to the use of the MERGE statement, the ledger always reflects the most accurate and up-to-date state.
Link Reissue and Expiration
Since Stripe Sessions cannot be extended, Cloud Run regenerates a new Session using the same billing information.
Technically, it’s a “recreation,” but in practice, it works as a “reissued payment link” for the same invoice.
This approach ensures that even expired links always lead users to a valid, active payment flow.
Cloud Run Webhook Reception and Automation
Cloud Run also serves as the webhook endpoint for Stripe.
When Stripe sends a payment completion event, Cloud Run receives it and updates BigQuery using the invoice_id and family_id included in the metadata.
As a result, the Google Workspace side can monitor payment status in real time.
Unpaid lists, reminder emails, and even chat notifications are fully automated — the tedious task of manually checking “who hasn’t paid yet” is now a thing of the past.
Test Environment
Stripe provides a sandbox environment for testing, allowing separate api_key and webhook_secret for production and testing.
The GAS side is designed to switch environments easily.
By toggling a single script property (live/test), the environment can be changed instantly.
BigQuery is divided into two datasets — finance and finance_test — with identical schemas for safe testing.
Cloud Run uses the same container image, switching only the keys via Secret Manager.
This design enables environment switching without any code modification.
Performance Verification
Since this system operates in a fully serverless Cloud Run environment, cold-start latency may occur on the first access.
Therefore, an actual load test was conducted using a Python (aiohttp) script under the following conditions:
- Spike test: 50 concurrent requests
- Distributed test: 30 requests over 20 seconds (with jitter)
All requests — including those under cold-start conditions — were successfully processed, with no timeouts or 5xx errors.
Although Cloud Run’s free tier charges for requests triggered from a cold start, the expected scale of about 500 members poses no operational issues in practice.
Conclusion
The system operates in a three-layer structure:
BigQuery serves as the ledger, Cloud Run handles control and integration, and Stripe acts as the payment processor.
The invoice_id ensures uniqueness on the ledger side, while the idempotency_key guarantees uniqueness on the Stripe side.
Additionally, HMAC-signed links secure every transaction.
From unpaid checks to session creation and webhook synchronization, the entire flow runs automatically, ensuring accurate and safe one-time payments, even if the same link is opened multiple times.
Many parts of this implementation were built with the help of generative AI, and the experience of working with payment architecture — including the differences between Stripe Session and Invoice and API idempotency — has been highly valuable.
References
This content originally appeared on DEV Community and was authored by Yoshi