TL;DR / Key Takeaways

Kalshi's /portfolio/settlements endpoint returns historical settlement results but your local DB won't update itself automatically when the bot misses a cycle.
A missed settlement write leaves trades stuck in open status forever, which breaks P&L reporting and confuses the open-trade counter.
A simple Python backfill script can pull settlement history, match it against your local DB, and patch the gap in under a minute.
The Predict & Profit Weather Bot and Econ Bot both ship with this backfill utility and the fixed settle_trade() function that caused the original gap.

I found the bug because my P&L report was lying to me.

The bot had been running for a few weeks. I had a handful of trades that I knew had settled. Kalshi's UI showed the outcomes. But my local SQLite database still had them marked as open. The settlement writer was failing silently, and every open-trade check the bot ran was counting stale rows instead of real positions.

This is the kind of thing that kills bots slowly. Not a crash. Just drift between what the bot thinks is true and what is actually true.

Here is how it happens, and here is how to fix it.

Why Settlements Go Missing

The bot's normal flow is: place trade, monitor market, detect settlement, write result to DB. That last step depends on the bot being alive at the right time and the settle_trade() function working correctly.

Two things can break that chain.

First, the bot can be down. Restarts, deployments, cron gaps, network hiccups. If the bot is not running when Kalshi settles a market, the settlement event never gets captured in-process.

Second, even when the bot is running, the DB write can fail silently. In my case, settle_trade() in db/connection.py was missing status = 'settled' in the UPDATE statement. The function ran without error. The trade stayed open. No exception, no log line, nothing. It just didn't work.

The fix to settle_trade() is one line:

def settle_trade(conn, trade_id, settled_price, profit_loss):
    cursor = conn.cursor()
    cursor.execute("""
        UPDATE trades
        SET settled_price = ?,
            profit_loss = ?,
            status = 'settled',
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    """, (settled_price, profit_loss, trade_id))
    conn.commit()

That status = 'settled' was missing. Simple. Expensive in its absence.

But fixing the function going forward doesn't fix the trades already stuck in limbo. That's what the backfill script is for.

The Kalshi Settlements Endpoint

Kalshi exposes /portfolio/settlements under the authenticated REST API. It returns a paginated list of all markets you've held positions in that have settled, including the settlement value and your profit or loss.

The response structure looks like this:

{
  "settlements": [
    {
      "market_ticker": "KXHIGHNYC-25MAY31-B78",
      "settled_price": 100,
      "profit_loss": 12.00,
      "settled_time": "2025-05-31T19:00:00Z",
      "no_count": 0,
      "yes_count": 3
    }
  ],
  "cursor": "some_pagination_cursor"
}

settled_price is 100 if Yes resolved, 0 if No resolved. profit_loss is your net in dollars after fees. market_ticker is the key you'll use to match back to your local DB.

The endpoint supports cursor-based pagination. You keep fetching pages until cursor comes back empty.

The Backfill Script

This is the full utility I use. It pulls all settlements from Kalshi, checks each one against the local DB, and patches anything that's still showing open.

#!/usr/bin/env python3
"""
backfill_settlements.py
Reconciles local trade DB against Kalshi /portfolio/settlements.
Usage: python backfill_settlements.py --db trades.db --dry-run
"""

import argparse
import json
import sqlite3
import time
from datetime import datetime

import requests

from auth import build_auth_headers  # your RSA-PSS auth helper


KALSHI_BASE = "https://trading-api.kalshi.com/trade-api/v2"
SLEEP_BETWEEN_PAGES = 0.3  # seconds, avoid 429s


def fetch_all_settlements(headers: dict) -> list[dict]:
    settlements = []
    cursor = None

    while True:
        params = {"limit": 100}
        if cursor:
            params["cursor"] = cursor

        resp = requests.get(
            f"{KALSHI_BASE}/portfolio/settlements",
            headers=headers,
            params=params,
            timeout=10,
        )
        resp.raise_for_status()
        data = resp.json()

        batch = data.get("settlements", [])
        settlements.extend(batch)

        cursor = data.get("cursor")
        if not cursor or not batch:
            break

        time.sleep(SLEEP_BETWEEN_PAGES)

    return settlements


def load_open_trades(db_path: str) -> dict[str, dict]:
    conn = sqlite3.connect(db_path)
    conn.row_factory = sqlite3.Row
    cursor = conn.cursor()
    cursor.execute("""
        SELECT id, market_ticker, status, profit_loss
        FROM trades
        WHERE status = 'open'
    """)
    rows = {row["market_ticker"]: dict(row) for row in cursor.fetchall()}
    conn.close()
    return rows


def patch_settlement(db_path: str, trade_id: int, settled_price: float,
                     profit_loss: float, dry_run: bool) -> None:
    if dry_run:
        print(f"  [DRY RUN] Would patch trade_id={trade_id} "
              f"settled_price={settled_price} profit_loss={profit_loss:.2f}")
        return

    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("""
        UPDATE trades
        SET settled_price = ?,
            profit_loss = ?,
            status = 'settled',
            updated_at = CURRENT_TIMESTAMP
        WHERE id = ?
    """, (settled_price, profit_loss, trade_id))
    conn.commit()
    conn.close()
    print(f"  Patched trade_id={trade_id} -> settled, P&L={profit_loss:.2f}")


def run_backfill(db_path: str, dry_run: bool) -> None:
    print(f"[{datetime.utcnow().isoformat()}] Starting settlement backfill")
    print(f"DB: {db_path} | dry_run={dry_run}\n")

    headers = build_auth_headers("GET", "/trade-api/v2/portfolio/settlements")

    print("Fetching settlements from Kalshi...")
    settlements = fetch_all_settlements(headers)
    print(f"  {len(settlements)} total settlements returned\n")

    print("Loading open trades from local DB...")
    open_trades = load_open_trades(db_path)
    print(f"  {len(open_trades)} trades currently marked 'open'\n")

    matched = 0
    skipped = 0

    for s in settlements:
        ticker = s.get("market_ticker")
        if ticker not in open_trades:
            skipped += 1
            continue

        local = open_trades[ticker]
        trade_id = local["id"]
        settled_price = s.get("settled_price", 0)
        profit_loss = s.get("profit_loss", 0.0)

        print(f"Match: {ticker}")
        patch_settlement(db_path, trade_id, settled_price, profit_loss, dry_run)
        matched += 1

    print(f"\nDone. Matched={matched} | Skipped={skipped} "
          f"(already settled or not in local DB)")


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Backfill Kalshi settlements to local DB")
    parser.add_argument("--db", required=True, help="Path to SQLite DB file")
    parser.add_argument("--dry-run", action="store_true",
                        help="Print what would change without writing to DB")
    args = parser.parse_args()
    run_backfill(args.db, args.dry_run)

Run it dry first. Always.

python backfill_settlements.py --db trades.db --dry-run

When the output looks right, drop --dry-run and let it write.

A Few Things Worth Knowing

The market_ticker field in the Kalshi settlements response matches the ticker stored in your local DB, assuming you captured it correctly at order time. If your schema uses a different field as the join key, adjust the lookup accordingly.

Kalshi's settlements endpoint only returns markets where you held a position. It won't return every market that settled on the platform, just yours. That's what you want for reconciliation.

The profit_loss field in the settlements response is net of Kalshi's fee. So if you won $10 and Kalshi took $0.90, you'll see $9.10. Don't recalculate it from settled_price and contract count. Use what the API returns.

Pagination matters. If you've been running for a while, you may have hundreds of settlements. The script handles this with cursor-based looping and a 0.3 second sleep between pages to stay under the rate limit. I lifted that sleep value from the fix I applied to the Econ Bot after getting 429 errors on paginated position fetches.

Reconciliation as a Habit

I run this script manually after any deployment restart. Takes about 10 seconds.

At some point I'll wire it into a weekly cron job as a sanity check. Even with the fixed settle_trade() function, there are edge cases: network timeout right at settlement window, the bot restarting mid-cycle, Kalshi taking longer than expected to publish settlement results.

The backfill script is not a workaround for a broken system. It's a reconciliation layer, the same thing any competent data pipeline has. You don't trust your cache. You verify against the source of truth.

In this case the source of truth is Kalshi. Treat it that way.

The full backfill utility ships with the v2.1 source code alongside the corrected settle_trade() function. If your DB has phantom open trades, run the reconciliation first. Then check your settler logic before assuming the bot is healthy.

A P&L report is only as good as the data behind it. Get the data right.

How I Backfill Missing Settlement Data from the Kalshi API

Why Settlements Go Missing

The Kalshi Settlements Endpoint

The Backfill Script

A Few Things Worth Knowing

Reconciliation as a Habit