How do you investigate a production incident as an analyst?

Start from the ticket and write down what you know, what you assume, and what you need to check. Build a timeline to the second from request logs. Confirm the state in the database with queries you keep. Use monitoring to explain timing. Then write the root cause as a chain: the trigger, the defect that let the trigger cause damage, and the condition that made it likely. Each link needs one piece of evidence.

What is the difference between a trigger and a root cause?

The trigger is the event that started the failure, often something outside the platform, such as a client retrying after a timeout. The root cause is the defect that allowed the trigger to do damage, such as a limit check that is not atomic. Fixing the trigger alone leaves the defect for the next trigger.

Why does a 504 lead to a duplicate?

A gateway 504 means the gateway stopped waiting, not that the backend stopped working. The backend often completes the request after the client has been told it failed. A client that retries without an idempotency key then submits the same operation twice, and the backend has no way to know it is a retry.

Labs Mission 03 Open 90 min

Mission 03: Investigate a Production Incident and Find the Root Cause of a Duplicate Refund

A hands-on lab for technical analysts: work a duplicate refund incident from the ticket through API responses, request logs, database records, and monitoring, and separate the trigger from the defect.

Developer Analyst Systems Analyst QA Analyst Incident investigationLog analysisSQL investigation

Mission card

Your role: You are the technical analyst on the Northline Pay incident bridge. A merchant says the platform refunded a customer twice. You have the ticket, read access to everything, and one hour before the incident review.
Deliverable: An incident report: the timeline to the second, the root cause chain with evidence for each link, the blast radius with the query that measured it, and the fixes in order of priority.
System: Northline Pay: A fictional payment service provider for marketplaces. Every lab is set inside it.
Self-assessment: 8 expected findings in the solution

The situation

Friday, 14:20 UTC. A merchant support ticket has been escalated to the incident bridge: a customer was refunded twice for one order, and the merchant’s own dashboard shows two refund records on one payment. The merchant is certain their system sent one refund. The customer service lead wants to know whether every refund today is affected. The incident review is at 15:30.

You have the ticket, the API, the request logs, the gateway logs, a read replica of the database, and the monitoring dashboards. Nobody has a theory yet, and the first theory in the room will be “the merchant double-clicked”. Your job is to replace theories with a timeline.

What you will practice

Writing down what you know versus what you assume before you look at data.
Building a timeline to the second from three log sources with different clocks and different vocabularies.
Confirming state with SQL against production-shaped tables and keeping the query as evidence.
Separating the trigger, the defect, and the condition, and writing fixes for all three in the right order.

How the mission works

Three steps, each revealing more evidence, the way an incident actually unfolds: first the merchant’s view through the API, then the request and gateway logs, then the database and the monitoring. Each step has a task. The report at the end is assembled from your three answers. Then you compare with the solution and score yourself on what you actually wrote down.

The steps

Start step 1

Read first

Platform artifacts

Available from the start. Mission-specific evidence arrives with each step.

Platform artifact openapi.yaml 470 lines download show

openapi: 3.1.0
info:
  title: Northline Pay Merchant API
  version: "1.4.0"
  description: |
    Create payments on behalf of buyers, capture, refund, and list them.
    All amounts are expressed in the payment currency.
    Authenticate with your secret API key as a bearer token.
  contact:
    name: Northline Pay API team
    email: api@northlinepay.example
servers:
  - url: https://api.sandbox.northlinepay.example/v1
    description: Sandbox
  - url: https://api.northlinepay.example/v1
    description: Production

security:
  - apiKey: []

paths:
  /payments:
    post:
      operationId: createPayment
      summary: Create a payment
      description: |
        Creates a payment for a buyer. With `capture_method: automatic` the
        payment is authorized and captured in one call. With `manual`, call
        the capture endpoint later.
      parameters:
        - $ref: "#/components/parameters/IdempotencyKey"
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/CreatePaymentRequest"
      responses:
        "201":
          description: Payment created
          headers:
            Northline-Request-Id:
              $ref: "#/components/headers/RequestId"
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Payment"
        "400":
          $ref: "#/components/responses/BadRequest"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "402":
          description: The payment method was declined
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
    get:
      operationId: listPayments
      summary: List payments
      description: Returns the merchant's payments, most recent first.
      parameters:
        - name: limit
          in: query
          description: Number of payments to return.
          schema:
            type: integer
            minimum: 1
            maximum: 100
            default: 20
        - name: status
          in: query
          schema:
            $ref: "#/components/schemas/PaymentStatus"
      responses:
        "200":
          description: A page of payments
          content:
            application/json:
              schema:
                type: object
                required: [object, data, has_more]
                properties:
                  object:
                    type: string
                    const: list
                  data:
                    type: array
                    items:
                      $ref: "#/components/schemas/Payment"
                  has_more:
                    type: boolean
        "401":
          $ref: "#/components/responses/Unauthorized"

  /payments/{payment_id}:
    get:
      operationId: getPayment
      summary: Retrieve a payment
      parameters:
        - $ref: "#/components/parameters/PaymentId"
      responses:
        "200":
          description: The payment
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Payment"
        "404":
          $ref: "#/components/responses/NotFound"

  /payments/{payment_id}/capture:
    post:
      operationId: capturePayment
      summary: Capture an authorized payment
      parameters:
        - $ref: "#/components/parameters/PaymentId"
        - $ref: "#/components/parameters/IdempotencyKey"
      requestBody:
        content:
          application/json:
            schema:
              type: object
              properties:
                amount:
                  type: number
                  description: Amount to capture. Defaults to the authorized amount.
      responses:
        "200":
          description: The captured payment
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Payment"
        "400":
          $ref: "#/components/responses/BadRequest"
        "404":
          $ref: "#/components/responses/NotFound"
        "409":
          description: The payment is not in a capturable state
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /refunds:
    post:
      operationId: createRefund
      summary: Refund a captured payment
      description: Refunds all or part of a captured payment.
      parameters:
        - $ref: "#/components/parameters/IdempotencyKey"
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              required: [payment_id]
              properties:
                payment_id:
                  type: string
                amount:
                  type: number
                  description: Amount to refund. Defaults to the full captured amount.
                reason:
                  type: string
                  enum: [requested_by_customer, duplicate, fraudulent]
      responses:
        "201":
          description: Refund created
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Refund"
        "400":
          $ref: "#/components/responses/BadRequest"
        "404":
          $ref: "#/components/responses/NotFound"

  /refunds/{refund_id}:
    get:
      operationId: getRefund
      summary: Retrieve a refund
      parameters:
        - name: refund_id
          in: path
          required: true
          schema:
            type: string
      responses:
        "200":
          description: The refund
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Refund"
        "404":
          $ref: "#/components/responses/NotFound"

webhooks:
  payment.authorized:
    post:
      summary: A payment was authorized
      requestBody:
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/WebhookEvent"
      responses:
        "200":
          description: Acknowledge with any 2xx status
  payment.captured:
    post:
      summary: A payment was captured
      requestBody:
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/WebhookEvent"
      responses:
        "200":
          description: Acknowledge with any 2xx status
  payment.failed:
    post:
      summary: A payment failed or was declined
      requestBody:
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/WebhookEvent"
      responses:
        "200":
          description: Acknowledge with any 2xx status
  refund.succeeded:
    post:
      summary: A refund was completed
      requestBody:
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/WebhookEvent"
      responses:
        "200":
          description: Acknowledge with any 2xx status

components:
  securitySchemes:
    apiKey:
      type: http
      scheme: bearer
      description: Your secret key, `sk_test_...` in sandbox and `sk_live_...` in production.

  parameters:
    PaymentId:
      name: payment_id
      in: path
      required: true
      schema:
        type: string
        pattern: "^pay_[A-Za-z0-9]{16}$"
    IdempotencyKey:
      name: Idempotency-Key
      in: header
      required: false
      description: A unique key you generate to safely retry the request.
      schema:
        type: string
        maxLength: 64

  headers:
    RequestId:
      description: Identifier of this request, for support tickets.
      schema:
        type: string

  responses:
    BadRequest:
      description: The request was invalid
      content:
        application/json:
          schema:
            $ref: "#/components/schemas/Error"
    Unauthorized:
      description: The API key is missing or invalid
      content:
        application/json:
          schema:
            $ref: "#/components/schemas/Error"
    NotFound:
      description: No such resource for this merchant
      content:
        application/json:
          schema:
            $ref: "#/components/schemas/Error"

  schemas:
    PaymentStatus:
      type: string
      enum: [pending, authorized, captured, failed, canceled]

    CreatePaymentRequest:
      type: object
      required: [amount, currency, payment_method]
      properties:
        amount:
          type: number
          description: The payment amount.
        currency:
          type: string
          description: Three-letter currency code.
        capture_method:
          type: string
          enum: [automatic, manual]
          default: automatic
        payment_method:
          $ref: "#/components/schemas/PaymentMethodInput"
        customer:
          type: object
          properties:
            email:
              type: string
              format: email
        description:
          type: string
          maxLength: 200
        split:
          type: array
          description: How the amount is distributed across seller accounts.
          items:
            type: object
            required: [account_id, amount]
            properties:
              account_id:
                type: string
              amount:
                type: number
        metadata:
          type: object
          additionalProperties:
            type: string

    PaymentMethodInput:
      type: object
      required: [type, token]
      properties:
        type:
          type: string
          enum: [card, sepa_debit, interac]
        token:
          type: string
          description: A payment method token created with Northline.js.

    Payment:
      type: object
      required: [id, object, status, amount, currency, capture_method, payment_method, created_at]
      properties:
        id:
          type: string
        object:
          type: string
          const: payment
        status:
          $ref: "#/components/schemas/PaymentStatus"
        amount:
          type: number
        currency:
          type: string
        capture_method:
          type: string
          enum: [automatic, manual]
        payment_method:
          type: object
          properties:
            type:
              type: string
            card:
              type: object
              properties:
                brand:
                  type: string
                last4:
                  type: string
                exp_month:
                  type: integer
                exp_year:
                  type: integer
        customer:
          type: object
          properties:
            email:
              type: string
        split:
          type: array
          items:
            type: object
            properties:
              account_id:
                type: string
              amount:
                type: number
        failure_code:
          type: string
        failure_message:
          type: string
        metadata:
          type: object
          additionalProperties:
            type: string
        created_at:
          type: string
          format: date-time

    Refund:
      type: object
      required: [id, object, payment_id, amount, currency, status, created_at]
      properties:
        id:
          type: string
        object:
          type: string
          const: refund
        payment_id:
          type: string
        amount:
          type: number
        currency:
          type: string
        status:
          type: string
          enum: [pending, succeeded, failed]
        reason:
          type: string
        created_at:
          type: string
          format: date-time

    WebhookEvent:
      type: object
      required: [id, type, created_at, data]
      properties:
        id:
          type: string
        type:
          type: string
        created_at:
          type: string
          format: date-time
        data:
          type: object
          description: The payment or refund resource, as returned by the API.

    Error:
      type: object
      required: [type, code, message]
      properties:
        type:
          type: string
          enum: [invalid_request_error, authentication_error, card_error, api_error]
        code:
          type: string
          description: |
            One of: invalid_request, authentication_failed, payment_not_found,
            payment_not_capturable, card_declined, rate_limited.
        message:
          type: string
        param:
          type: string
          description: The request field the error refers to, when applicable.

Platform artifact schema.sql 105 lines download show

-- Northline Pay operational schema (PostgreSQL 16), read replica.
-- Analysts on the delivery team get SELECT on these tables during an
-- investigation. Timestamps are timestamptz stored in UTC.

create table merchants (
  id            text primary key,              -- mer_XXXXXXXX
  name          text not null,
  country       char(2) not null,
  webhook_url   text,
  webhook_secret text,                         -- HMAC secret, never shown in full
  created_at    timestamptz not null default now()
);

create table payments (
  id               text primary key,           -- pay_XXXXXXXXXXXXXXXX
  merchant_id      text not null references merchants(id),
  status           text not null check (status in ('pending','requires_action','authorized','captured','failed','canceled','expired')),
  amount           bigint not null check (amount > 0),        -- minor units
  currency         char(3) not null,
  captured_amount  bigint not null default 0,
  refunded_amount  bigint not null default 0,
  capture_method   text not null check (capture_method in ('automatic','manual')),
  idempotency_key  text,                        -- null when the merchant sent none
  failure_code     text,
  created_at       timestamptz not null default now(),
  authorized_at    timestamptz,
  captured_at      timestamptz,
  updated_at       timestamptz not null default now()
);
create index payments_merchant_created_idx on payments (merchant_id, created_at desc);
create unique index payments_idempotency_idx on payments (merchant_id, idempotency_key) where idempotency_key is not null;

create table refunds (
  id               text primary key,           -- re_XXXXXXXXXXXXXXXX
  payment_id       text not null references payments(id),
  merchant_id      text not null references merchants(id),
  amount           bigint not null check (amount > 0),
  currency         char(3) not null,
  status           text not null check (status in ('pending','succeeded','failed')),
  reason           text,
  idempotency_key  text,
  request_id       text,                        -- the API request that created it
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now()
);
create index refunds_payment_idx on refunds (payment_id, created_at);
create unique index refunds_idempotency_idx on refunds (merchant_id, idempotency_key) where idempotency_key is not null;

create table ledger_entries (
  id            text primary key,              -- led_XXXXXXXXXXXXXXXX
  payment_id    text not null references payments(id),
  merchant_id   text not null,
  kind          text not null check (kind in ('capture','refund')),
  amount        bigint not null,               -- positive for capture, negative for refund
  currency      char(3) not null,
  source_event  text not null,                 -- evt_ id of the Kafka event that produced it
  posted_at     timestamptz not null default now()
);
create index ledger_entries_payment_idx on ledger_entries (payment_id);

create table settlements (
  id            text primary key,              -- set_XXXXXXXXXXXX
  merchant_id   text not null references merchants(id),
  currency      char(3) not null,
  total_amount  bigint not null,
  status        text not null check (status in ('open','paid')),
  settles_on    date not null,
  created_at    timestamptz not null default now()
);

create table settlement_items (
  settlement_id text not null references settlements(id),
  entry_id      text not null references ledger_entries(id),
  primary key (settlement_id, entry_id)
);

create table webhook_deliveries (
  id              text primary key,
  merchant_id     text not null references merchants(id),
  event_id        text not null,
  event_type      text not null,
  payment_id      text,
  status          text not null check (status in ('pending','delivered','failed','exhausted')),
  attempts        int not null default 0,
  last_status_code int,
  last_attempt_at timestamptz,
  created_at      timestamptz not null default now()
);
create index webhook_deliveries_payment_idx on webhook_deliveries (payment_id);

-- Every request to the Merchant API, retained 90 days. duration_ms is
-- measured inside the API service, after the gateway.
create table api_requests (
  request_id       text primary key,           -- req_XXXXXXXXXXXXXXXX
  merchant_id      text,
  method           text not null,
  path             text not null,
  status_code      int not null,
  duration_ms      int not null,
  idempotency_key  text,
  resource_id      text,                       -- pay_ or re_ id the request created or touched
  created_at       timestamptz not null default now()
);
create index api_requests_merchant_created_idx on api_requests (merchant_id, created_at desc);

Free account

Save your progress on the Labs

A free account, no password: an email link signs you in. Your steps and your self-assessment are saved, your missions show on a dashboard, and the solutions unlock.

Your email is used to sign you in. Nothing else, unless you ask. Privacy.