Stripe payments logging – Design for storing payment logs

So payment lands in Stripe, our order screen updates, and then we get the requirement: “Store payment IP, logs, etc, when payment is received from Stripe.”

That sentence sounds small. It is not. IP could mean the customer, Stripe, our load balancer, or all three. Logs could mean raw webhook JSON, application log lines, a new audit table, or exports into Datadog. When payment is received might mean charge.succeeded, checkout.session.completed, invoice.paid, or the moment finance marks an invoice paid in the admin UI.

This post is how we translate that kind of ask into something we can build, test, and defend in a retro without over-collecting data or under-documenting money.

References we keep open:

• Stripe webhook basics: Receive Stripe events in your webhook endpoint.
• Event object shape: Event API reference.
• Idempotent handling: Handle duplicate events.

What we will learn

• Questions that turn “IP and logs” into testable requirements.
• What Stripe actually gives us on webhooks vs what we must capture ourselves.
• A three-layer design (persist payload, audit row, structured logs) that scales without bloating the payments table.
• How we verify the pipeline in staging without faking production card data.

Prerequisites

• We already have (or plan) a webhook endpoint with signature verification (Stripe-Signature header).
• We can run database migrations and ship a config change to staging.
• Someone on the product or finance side can answer “what dispute are we preparing for?” in one sentence.

Step 1: Translate the vague ask (before we touch code)

We send back a short checklist. Not to be difficult. Because “IP and logs” without scope becomes scope creep the week before launch.

Opinion: if we cannot get written answers, we still ship webhook audit + raw payload and explicitly document what we cannot capture (customer IP on hosted Checkout) so nobody assumes we did.

Step 2: Know what Stripe gives us for free

Facts (Stripe-side)

On a verified webhook POST we reliably get:

• Event envelope: id, type, created, livemode, api_version.
• Object payload: Charge, Invoice, PaymentIntent, etc., depending on event type.
• Delivery metadata only if we read our HTTP request: remote address, X-Forwarded-For, User-Agent, timestamp we record.

The TCP connection to our webhook URL is from Stripe’s infrastructure (or a proxy in front of us). That IP is useful for proving “this POST came through our edge,” not for proving “this customer was in city X.”

Facts (customer IP)

• Hosted Checkout / Invoice pay links: payment happens on Stripe’s domain. We do not get payer IP in the standard Charge webhook fields we expected.
• Radar Review objects can expose IP-related fields when a charge enters review. That is a narrow path, not a general ledger feature.
• Our own checkout (Elements, custom flow): we can capture client IP at session start and attach metadata to the PaymentIntent or Customer. That is a product decision, not a webhook patch.

We say this out loud in the design doc so “store payment IP” does not silently become “we promise customer geo in every row.”

Step 3: A design we have shipped variants of

We like three layers. Each layer has one job.

Layer A – Persist the webhook payload (compliance floor)

Goal: reconstruct “what did Stripe tell us at time T?”

• Store raw JSON (or gzip-compressed JSON) keyed by event.id.
• If our payments table already has a webhook_payload column that is always NULL, fix the INSERT path before adding new tables. Empty columns create false confidence.

Fields we usually want on the payment or sibling row:

Request IP extraction (typical behind a load balancer):

// Pseudocode - adapt to our stack (JAX-RS, Spring, etc.)
String forwarded = request.getHeader("X-Forwarded-For");
String ip = (forwarded != null && !forwarded.isBlank())
    ? forwarded.split(",")[0].trim()
    : request.getRemoteAddr();

Opinion: trust X-Forwarded-For only from our edge, not from arbitrary clients.

Layer B – Payment webhook audit table (recommended)

Goal: append-only trail without widening every payments query.

Example shape:

-- Illustrative only - tune names to our schema
CREATE TABLE payment_webhook_audit (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  stripe_event_id VARCHAR(255) NOT NULL UNIQUE,
  stripe_event_type VARCHAR(128) NOT NULL,
  charge_id VARCHAR(255) NULL,
  webhook_received_at TIMESTAMP NOT NULL,
  webhook_request_ip VARCHAR(45) NULL,
  raw_payload LONGTEXT NOT NULL,
  processing_status VARCHAR(32) NOT NULL, -- received | processed | failed
  processed_at TIMESTAMP NULL,
  error_message TEXT NULL
);

Flow:

1. Verify signature -> insert audit row (processing_status = received).
2. Run business logic inside a transaction where possible.
3. Update audit row to processed or failed with error_message.

Duplicates hit the unique constraint on stripe_event_id and exit early. That matches Stripe’s idempotency guidance.

Layer C – Structured application logs (operations)

Goal: grep-friendly lines for on-call, without opening MySQL.

One JSON log per accepted event:

{
  "event": "stripe_webhook_received",
  "stripe_event_id": "evt_...",
  "stripe_event_type": "charge.succeeded",
  "charge_id": "ch_...",
  "amount": 9900,
  "currency": "usd",
  "webhook_request_ip": "54.x.x.x",
  "livemode": false
}

We avoid logging full card numbers or unnecessary PII. Stripe’s object usually keeps PAN out; still treat payload as sensitive.

Step 4: Wire it at the webhook boundary

Regardless of framework, the pattern is the same:

1. Read raw body as bytes before JSON parsing (signature verification needs the raw body).
2. Construct event with Stripe SDK + webhook secret.
3. Capture event.id, event.type, raw body string, request IP, Instant.now().
4. Persist audit then dispatch to existing handlers (handleCharge, handleInvoice, …).
5. Return 2xx quickly. Heavy work belongs in a queue if we already timeout webhooks.

We pass payload + IP into persistence methods that today might only insert amount and charge ID. That was a common gap in legacy codebases: column existed, INSERT list did not.

Step 5: Testing without production dread

This task feels hard to test until we split Stripe’s behavior from our persistence.

Opinion: a single integration test that triggers one event and asserts three things (audit row, payment side effect, log field) pays for itself the first time finance asks “what happened on the 14th?”

Pitfalls we plan for upfront

• Assuming customer IP is in the webhook. It usually is not on hosted flows. Metadata or Radar-only paths need their own product sign-off.
• Logging only to stdout. Logs rotate; disputes want DB or object storage with retention policy.
• Mutating without idempotency. Stripe retries; we will double-ship orders without event.id dedupe.
• Trusting client-supplied IP headers on unauthenticated routes. Webhook routes should still verify signature first; IP is supplementary.
• GDPR / retention debt. Raw JSON may contain email and billing address. Name an owner for deletion and retention up front.

What we take back to the manager

A one-page summary in plain language:

We will store every Stripe payment webhook with event ID, timestamp, request IP from our edge, and full JSON for N months. That IP is Stripe’s delivery path, not the customer’s browser, unless we add a separate checkout capture project. Support and finance can search by charge ID; engineering can replay failed events.

That turns “IP and logs” into a bounded deliverable with explicit gaps.

Closing

Vague payment asks are normal. Our job is not to nod and bolt on a VARCHAR called ip. Our job is to name the evidence each stakeholder needs, map it to Stripe’s actual surfaces, and build an audit trail that survives retries, re-reads, and someone asking questions six months later.