>_ Analyst Engineering
Labs / Investigate a production incident Step 3 of 3

03Database and monitoring

The refund, ledger, and payment rows, plus the dashboards for the window. Confirm the state, explain the timing, measure the blast radius, and order the fixes.

Confirm the state before you explain it

The database is the only source that says what is true now. Read the refund rows, the ledger entries, and the payment row, and confirm every claim from the previous steps: two succeeded refunds, two ledger entries, a refunded amount above the captured amount. Keep each query. In the review, “I checked” is not evidence; the query and its result are.

Explain the timing with monitoring

The timeline showed one request that took far longer than normal. Monitoring tells you why. Look at the latency of the refund endpoint across the day, not just the incident minute, and look for what else was happening on the database at the same time. A latency spike with a regular shape has a regular cause.

The root cause chain

Write the cause as three links, each with one piece of evidence:

  • The trigger. What started it. Often outside the platform.
  • The defect. What in the platform let the trigger do damage. This is the bug to fix.
  • The condition. What made the trigger likely at that moment. This is what made it happen today and not last month.

Fixing only the trigger leaves the defect. Fixing only the defect leaves a slow endpoint that will time out again. The report needs all three.

Blast radius and fixes

Write the query that finds every payment affected the same way, run it in your head against the extracts, and state the count. Then list the fixes in the order you would ship them, each with the link it addresses and what it prevents. A database constraint that makes over-refunding impossible is a different kind of fix from a request to the merchant to send idempotency keys. Say which is which.

Your task

Write the incident report: timeline, root cause chain (trigger, defect, condition) with one evidence item per link, the blast radius with the query you would run and its result, and the fixes in priority order with what each one prevents. Then compare with the solution.

Evidence for this step

Read it in the page or download it and open it in your own tools.

Mission artifact database-extracts.md 90 lines download show 
# Database extracts (read replica, 2026-09-12 14:31 UTC)

## Query 1: the refunds on the payment

```sql
select id, amount, status, idempotency_key, request_id, created_at, updated_at
from refunds
where payment_id = 'pay_2Fw6hT9jK3sR8vN1'
order by created_at;
```

| id | amount | status | idempotency_key | request_id | created_at | updated_at |
|---|---|---|---|---|---|---|
| re_A4kT7wQ2mL9sP8xN | 8900 | succeeded | NULL | req_7Hn2kQ9wT4sL8mP3 | 2026-09-12 14:03:12.031+00 | 2026-09-12 14:03:51.601+00 |
| re_C8pW3mK6tR2sL5qY | 8900 | succeeded | NULL | req_3Wq8mL2tK7pR9sN4 | 2026-09-12 14:03:43.089+00 | 2026-09-12 14:03:51.778+00 |

## Query 2: the payment row

```sql
select id, status, amount, captured_amount, refunded_amount, updated_at
from payments
where id = 'pay_2Fw6hT9jK3sR8vN1';
```

| id | status | amount | captured_amount | refunded_amount | updated_at |
|---|---|---|---|---|---|
| pay_2Fw6hT9jK3sR8vN1 | captured | 8900 | 8900 | 17800 | 2026-09-12 14:03:51.778+00 |

## Query 3: the ledger entries

```sql
select id, kind, amount, source_event, posted_at
from ledger_entries
where payment_id = 'pay_2Fw6hT9jK3sR8vN1'
order by posted_at;
```

| id | kind | amount | source_event | posted_at |
|---|---|---|---|---|
| led_2Wq7kM3sT9pL4rN8 | capture | 8900 | evt_capture_pay_2Fw6 | 2026-09-11 16:44:11.902+00 |
| led_5Kw9mT2sQ7pL4rN8 | refund | -8900 | req_7Hn2kQ9wT4sL8mP3 | 2026-09-12 14:03:51.588+00 |
| led_8Tm2kW7sQ4pL9rN3 | refund | -8900 | req_3Wq8mL2tK7pR9sN4 | 2026-09-12 14:03:51.769+00 |

## Query 4: any payment refunded above its capture, today

```sql
select id, merchant_id, captured_amount, refunded_amount, updated_at
from payments
where refunded_amount > captured_amount
  and updated_at >= '2026-09-12 00:00+00'
order by updated_at;
```

| id | merchant_id | captured_amount | refunded_amount | updated_at |
|---|---|---|---|---|
| pay_2Fw6hT9jK3sR8vN1 | mer_B2X9L4M7 | 8900 | 17800 | 2026-09-12 14:03:51.778+00 |
| pay_6Ht4kW9mS2qL8rN3 | mer_F6G1H8J3 | 24500 | 49000 | 2026-09-12 14:03:59.131+00 |
| pay_3Kw8mT2sQ9pL7rN4 | mer_K2L7M4N9 | 5200 | 10400 | 2026-09-12 14:05:45.302+00 |

## Query 5: the same check for the previous 30 days

```sql
select count(*) from payments
where refunded_amount > captured_amount
  and updated_at between '2026-08-13 00:00+00' and '2026-09-12 00:00+00';
```

| count |
|---|
| 0 |

## Query 6: the reconciliation job history

```sql
select started_at, finished_at, duration_ms, rows_reconciled
from job_runs
where job = 'ledger_daily_reconciliation'
order by started_at desc limit 5;
```

| started_at | finished_at | duration_ms | rows_reconciled |
|---|---|---|---|
| 2026-09-12 14:00:00.214+00 | 2026-09-12 14:03:51.402+00 | 231188 | 27640 |
| 2026-09-11 14:00:00.198+00 | 2026-09-11 14:01:12.077+00 | 71879 | 6104 |
| 2026-09-10 14:00:00.221+00 | 2026-09-10 14:01:08.910+00 | 68689 | 5877 |
| 2026-09-09 14:00:00.205+00 | 2026-09-09 14:01:10.343+00 | 70138 | 5932 |
| 2026-09-08 14:00:00.187+00 | 2026-09-08 14:01:05.662+00 | 65475 | 5610 |

Note from the platform DBA on the bridge: the 12 September run reconciled the 1,842 captures replayed from the ledger dead letter queue this morning (incident NLP-4821) plus four days of backlog, hence eight times the usual rows.
Mission artifact monitoring.md 49 lines download show 
# Monitoring: 2026-09-12, 13:30 to 14:30 UTC

Dashboards: `merchant-api / refunds`, `postgres / locks`, `scheduler / jobs`. Values read from the panels at 14:35 UTC.

## Panel: POST /v1/refunds latency (service side, ms)

| Window | p50 | p95 | p99 | max |
|---|---|---|---|---|
| 13:30 to 13:40 | 390 | 620 | 880 | 1,240 |
| 13:40 to 13:50 | 402 | 640 | 910 | 1,310 |
| 13:50 to 14:00 | 388 | 615 | 870 | 1,190 |
| 14:00 to 14:10 | 1,840 | 31,200 | 39,100 | 39,612 |
| 14:10 to 14:20 | 380 | 610 | 910 | 1,220 |
| 14:20 to 14:30 | 395 | 625 | 890 | 1,260 |

Annotation on the panel at 14:00:00: `job ledger_daily_reconciliation started`. Annotation at 14:03:51: `job finished`.

## Panel: edge gateway, status codes for /v1/refunds

| Window | 2xx | 4xx | 504 |
|---|---|---|---|
| 13:50 to 14:00 | 204 | 3 | 0 |
| 14:00 to 14:10 | 185 | 0 | 27 |
| 14:10 to 14:20 | 196 | 2 | 0 |

Gateway upstream timeout: 30,000 ms (configured). Merchant API request timeout: 60,000 ms (configured).

## Panel: PostgreSQL lock waits (primary)

| Window | sessions waiting on lock (max) | longest wait (s) | relation |
|---|---|---|---|
| 13:50 to 14:00 | 0 | 0 | |
| 14:00 to 14:10 | 41 | 39.5 | ledger_entries |
| 14:10 to 14:20 | 0 | 0 | |

## Panel: same time window, previous 7 days (POST /v1/refunds p99, 14:00 to 14:10)

| Day | p99 (ms) | 504s |
|---|---|---|
| 2026-09-05 | 6,400 | 0 |
| 2026-09-06 | 5,900 | 0 |
| 2026-09-07 | 6,100 | 0 |
| 2026-09-08 | 6,300 | 0 |
| 2026-09-09 | 6,800 | 0 |
| 2026-09-10 | 6,500 | 0 |
| 2026-09-11 | 7,100 | 0 |

Note: a daily p99 bump at 14:00 has been visible on this panel for weeks. No alert is configured on refund latency. The 5xx alert fires at 5% of requests over 15 minutes; 27 of 212 in a 10 minute window did not trigger it.
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);
Compare with the solution