Data Integrity & Gap Management

Version: 2.2
Last Updated: May 31, 2026
Status: Production (integrity-monitor era — Findings #258/#290/#289.a; watchdog deleted #290, prices-backfill decommissioned #289)

This document describes the comprehensive data integrity system that ensures database completeness and enables full trade audit trails.

Table of Contents

1. Overview
2. Integrity Monitor (watchdog deleted #290; replaced by polymarket-integrity-monitor)
3. Backfill Services
4. Trade Recording System
5. Database Schema
6. Monitoring & Alerts
7. Operations

1. Overview

Core Principles

System Components

┌─────────────────────────────────────────────────────────────────┐
│                    DATA INTEGRITY SYSTEM                        │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ┌──────────────┐   ┌──────────────┐   ┌──────────────┐       │
│  │  Integrity   │   │   Gap        │   │  Backfill   │        │
│  │  Monitor     │──▶│   Detection  │──▶│  Scheduler  │        │
│  │  (5 min)     │   │   Engine     │   │             │        │
│  └──────────────┘   └──────────────┘   └──────────────┘        │
│         │                   │                  │                │
│         ▼                   ▼                  ▼                │
│  ┌──────────────────────────────────────────────────┐          │
│  │              PostgreSQL Database                  │          │
│  │  ┌─────────┐ ┌─────────┐ ┌─────────────────┐    │          │
│  │  │data_gaps│ │trades   │ │trade_audit_log  │    │          │
│  │  └─────────┘ └─────────┘ └─────────────────┘    │          │
│  └──────────────────────────────────────────────────┘          │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Note: the legacy Watchdog Service (5 min) box above is now the integrity monitor (watchdog deleted #290; replaced by polymarket-integrity-monitor.service). See §2.

2. Integrity Monitor (Apr 27, 2026 — replaces v2/v3 watchdogs)

Phase 3 of Database Integrity Audit (Finding #258). The previous v2 watchdog (watchdog.py) and the never-properly-deployed v3 watchdog (watchdog_v3.py) were both decommissioned. The data_gaps table is retained for historical reference but is no longer written to (all 804 rows were tombstones with status='deleted' / gap_type='incorrect_source').

The current integrity story is in src/data_tracker/integrity_monitor.py writing to integrity_status table, surfaced by /api/integrity on the Database tab (Phase 4 of the audit, Finding #259).

2.1 Architecture

2.2 Rule categories

The monitor evaluates summary.total rules (live via GET /api/integrity; src/data_tracker/integrity_monitor.py — 85 as of Sep 26, 2026 probe; history: was ~51 before later rule additions) per cycle organised into 6 categories (historical progression: was 37 before May 10 LATE morning — Finding #289.a added 3 new rules: systemd_timer:polymarket-api-health.timer, systemd_last_run:polymarket-api-health.service, and artifact:api_health_latest_json; was 36 before #291's artifact:trades_backfill_state; was 34 before #269 (May 9 added trades-backfill timer + last_run); was 28 before Apr 27 evening when #268 added 6 systemd_last_run:* rules). Subsequent additions (3 etfstack freshness:* rules + etfstack-signal / etfstack-execute / ibgateway-paper-weekly-reconnect / polymarket-strategy-analysis systemd_last_run:* rules brought it to ~47; Aug 21 2026 the 4 archive:orderbook_* rules of #383 bring the current total to ~51).

Rules are declared in _build_rules() in integrity_monitor.py. Add new rules by appending a Rule(name, category, severity, description, evaluator).

Why three systemd rule patterns instead of one:

• systemd:<service> — checks is-active only. Right for always-on services (earnings-trader, polymarket-dashboard, etc.) that should always be running.
• systemd_timer:<timer> — checks LastTriggerUSec against an expected cadence. Right for "did the timer fire recently?" — but does not catch failed runs.
• systemd_last_run:<service> — checks Result + ExecMainStatus of the last invocation. Right for batch services where is-active is inactive(dead) between timer fires. This is the one that catches OOM kills, exit-code≠0, signal-kill, timeout, etc. — added Apr 27, 2026 evening (Finding #268) after polymarket-backup.service was OOM-killed three nights running with zero alerts.

2.3 Severity levels and alerting policy

Per operator decision Q3 (Phase 1 + Phase 3 alert policy): Telegram pings only on HIGH/CRITICAL transitions. WARNING/INFO are dashboard-only.

2.4 Status transitions

ok → fail/warn   ⇒  Telegram "TRIGGERED" alert (HIGH/CRITICAL only)
fail/warn → ok   ⇒  Telegram "RESOLVED" alert (HIGH/CRITICAL only)
unknown → any    ⇒  silent (rule has insufficient data, e.g. small post-fix window)
ok → unknown     ⇒  silent (transient evaluator failure)

The unknown status exists specifically to handle "post-fix forward-only window has 0-2 rows yet, can't evaluate" — graceful fallback that doesn't fire a false alarm.

2.5 Long-running-no-op service defense — state-file mtime rules (Finding #291, May 10, 2026)

The §E blind spot from Finding #287. Three Dome-era backfill services (polymarket-orderbook-backfill, polymarket-prices-backfill, polymarket-dome-prices-backfill) were enabled+active+running for 12 days post-Apr-28 Dome EOL while accomplishing zero useful work — their state files stayed frozen at 5,735/5,735 and the cycle loop just exited "all done" every hour. Existing freshness:* rules caught the DATA-side problem (table not being written) for SOLE-writer services within 10-60 min, but not the COMPUTE-side waste (service active but producing nothing) when REDUNDANT writers existed (Polymarket-native consumers were independently populating the same target tables, so freshness rules stayed clean).

The defense: any long-running daemon collector that maintains a state file MUST also have an artifact:<state-file> rule. Frozen state file = silent compute waste, even when the service appears active+running.

Currently in scope (May 10, 2026):

Currently NOT in scope and why: the 3 always-on daemon collectors (polymarket-collector.service, polymarket-live-trades.service, polymarket-websocket.service) do NOT maintain state files; each is the SOLE writer of its target table(s), so the existing freshness:* rules already alarm 10-60 min after they'd silently die. A progress:* rule attempting to attribute writes to a specific service was considered and rejected as duplicating freshness coverage in the current architecture (see Finding #291 RESOLVED entry for the design discussion).

Checklist for adding new long-running daemon collectors: see the services=() array comment in deploy/deploy_from_github.sh.

3. Backfill Services

3.1 Available Backfill Sources

3.2 Prices Backfill Service — DECOMMISSIONED (Finding #289, May 10, 2026)

This service (prices_backfill_service.py + polymarket-prices-backfill.service) was decommissioned during the Dome → Polymarket migration. The Gamma endpoint it used (gamma-api.polymarket.com/markets/{cid}/prices) is not in the new Polymarket API docs and was logging "Saved: 0 prices" cycling. Live price capture continues via polymarket-collector.service (continuous_collector — gamma + clob) and polymarket-live-trades.service (data-api /trades poller), with scripts/backfill_post_event_snapshots_from_trades.py (Finding #246) covering the post-event historical fill-in. price_snapshots table freshness is monitored by freshness:price_snapshots integrity rule (10-min threshold).

If a future need for historical-price backfill arises, the Polymarket replacement is clob.polymarket.com/prices-history?market=<token>&startTs=...&endTs=... (rate limit 1,000 req/10s — 100 q/s).

3.3 Trades Backfill Service

Location: src/data_tracker/collectors/backfill_trades.py

Systemd Service: polymarket-trades-backfill.service

Reliability Notes: - Backfill inserts into trade_dedupe first for global idempotency - Backfill state file writes are atomic (temp + fsync + rename)

Uses Polymarket data-api /trades?market=<cid> to backfill historical trade data (Finding #288 migration; formerly Dome /orders until Apr 28, 2026 EOL).

4. Trade Recording System

4.1 Overview

Every trade executed through the system is recorded with comprehensive data from:

1. IBKR - Account, order, execution, and fill details
2. Polymarket - Market conviction, orderbook state
3. System - Strategy context, risk parameters, portfolio state

4.2 Data Categories

IBKR Order & Execution Data

Strategy Context

Market State at Entry

Outcome Data

4.3 Slippage Analysis

5. Database Schema

Note (core topology): the two tables detailed below (data_gaps, trade_audit_log) are only part of the schema. The current core tables also include earnings_markets, trading_positions, polymarket_opportunity_log, equity_bars_1m (~1.9M bars / 419 symbols), orderbook_snapshots, price_snapshots, markets, and integrity_status. (data_gaps is retained for historical reference but no longer written — see §2.)

5.1 Gap Tracking Table

CREATE TABLE data_gaps (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(100) NOT NULL,
    condition_id VARCHAR(100),
    gap_start TIMESTAMPTZ NOT NULL,
    gap_end TIMESTAMPTZ NOT NULL,
    gap_type VARCHAR(50) DEFAULT 'collection_outage',
    description TEXT,
    records_affected INTEGER,
    status VARCHAR(20) DEFAULT 'pending',
    backfill_attempts INTEGER DEFAULT 0,
    last_backfill_attempt TIMESTAMPTZ,
    backfill_error TEXT,
    corrected_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

5.2 Trade Audit Log Table

See migration: src/data_tracker/migrations/005_enhanced_trade_audit.sql

Key additions in v2: - Full IBKR execution details - Fill details as JSONB - Portfolio state snapshots - Risk parameters - Slippage analysis fields - Earnings outcome data

5.3 Running Migrations

# From project root
cd [hidden]

# Run migration
source .env
psql $DATABASE_URL -f src/data_tracker/migrations/005_enhanced_trade_audit.sql

6. Monitoring & Alerts

6.1 Integrity Monitor Logs (replaces v2/v3 watchdog logs — Finding #258 / #290)

# View real-time
journalctl -u polymarket-integrity-monitor -f

# Check for failed rules
journalctl -u polymarket-integrity-monitor | grep -E "STATUS_FAIL|alert"

# Check rule-evaluation cycle health
journalctl -u polymarket-integrity-monitor | grep "rule cycle"

# Live API view (preferred)
curl -s http://127.0.0.1:8080/api/integrity | python3 -m json.tool

Pre-Finding-#258 (Apr 27, 2026) the equivalent commands targeted polymarket-watchdog.service (v2) and polymarket-watchdog-v3.service. Both services were decommissioned per Finding #260c; the unit files and src/data_tracker/watchdog{,_v3}.py source were deleted per Finding #290 (May 10, 2026). The data_gaps table is retained for historical reference but no longer written to.

6.2 Gap Dashboard Query

-- Recent gaps
SELECT table_name, gap_start, gap_end, status, description
FROM data_gaps
WHERE created_at > NOW() - INTERVAL '7 days'
ORDER BY gap_start DESC;

-- Pending backfills
SELECT * FROM data_gaps WHERE status = 'pending';

-- Gap statistics
SELECT 
    status,
    COUNT(*) as count,
    AVG(EXTRACT(EPOCH FROM (gap_end - gap_start))/60) as avg_duration_min
FROM data_gaps
GROUP BY status;

6.3 Trade Recording Verification

-- Check trade completeness
SELECT 
    symbol,
    entry_time,
    CASE WHEN ibkr_perm_id IS NOT NULL THEN '✓' ELSE '✗' END as has_ibkr_id,
    CASE WHEN entry_fill_details IS NOT NULL THEN '✓' ELSE '✗' END as has_fills,
    CASE WHEN portfolio_state_at_entry IS NOT NULL THEN '✓' ELSE '✗' END as has_portfolio,
    CASE WHEN position_sizing_details IS NOT NULL THEN '✓' ELSE '✗' END as has_sizing
FROM trade_audit_log
ORDER BY entry_time DESC
LIMIT 20;

7. Operations

7.1 Services Overview

7.2 Installation

Note: polymarket-prices-backfill.service was DECOMMISSIONED (Finding #289 — see §3.2). The example below shows the generic install pattern using the current integrity-monitor unit instead.

# Copy service files
sudo cp deploy/systemd/polymarket-integrity-monitor.service /etc/systemd/system/

# Reload and enable
sudo systemctl daemon-reload
sudo systemctl enable polymarket-integrity-monitor
sudo systemctl start polymarket-integrity-monitor

7.3 Recovery Procedures

After Server Reboot

1. Services auto-start
2. Integrity monitor (polymarket-integrity-monitor.service) detects startup staleness via freshness:* rules (watchdog deleted #290; replaced by polymarket-integrity-monitor)
3. Failing rules surface in /api/integrity + dashboard (HIGH/CRITICAL → Telegram)
4. Backfill scheduled if possible
5. NEW (Jan 11): Position reconciliation runs automatically

After Collector Crash

1. Integrity monitor detects stale data via freshness:* rules (watchdog deleted #290; replaced by polymarket-integrity-monitor)
2. systemd Restart=always restarts the collector process
3. Failing rule recorded in integrity_status (data_gaps no longer written — see §2)
4. Fires HIGH/CRITICAL Telegram alert

Position Sync Issues (Added Jan 11, 2026)

1. Orphaned DB Position (in DB but not IBKR):
2. Likely closed externally or via manual intervention
3. Mark as reconciliation_status = 'orphaned'

4. Close position in DB with exit_reason = 'orphaned_reconciliation'

5. External IBKR Position (in IBKR but not DB):

6. Likely manual trade from different clientId
7. Logged to external_trades table

8. Requires manual investigation and resolution

9. Position Mismatch (share counts differ):

10. DB and IBKR have different share counts
11. May indicate partial fill or manual adjustment
12. Logged for investigation

# Check position reconciliation log
psql $DATABASE_URL -c "SELECT * FROM position_reconciliation_log ORDER BY run_time DESC LIMIT 5"

# Check external trades
psql $DATABASE_URL -c "SELECT * FROM external_trades WHERE resolved = FALSE"

# Check for orphaned positions
psql $DATABASE_URL -c "SELECT symbol, reconciliation_status FROM trading_positions WHERE status = 'open'"

Manual Gap Backfill

# Check pending gaps
psql $DATABASE_URL -c "SELECT * FROM data_gaps WHERE status = 'pending'"

# Mark gap as backfilled (after manual intervention)
psql $DATABASE_URL -c "UPDATE data_gaps SET status = 'backfilled', corrected_at = NOW() WHERE id = <gap_id>"

7.4 Validation Queries

-- Data freshness
SELECT 
    'orderbook_snapshots' as table_name,
    MAX(time) as latest,
    NOW() - MAX(time) as age
FROM orderbook_snapshots
UNION ALL
SELECT 
    'price_snapshots',
    MAX(time),
    NOW() - MAX(time)
FROM price_snapshots
UNION ALL
SELECT 
    'trades',
    MAX(time),
    NOW() - MAX(time)
FROM trades;

-- Coverage by hour (last 24h)
SELECT 
    DATE_TRUNC('hour', time) as hour,
    COUNT(*) as records
FROM price_snapshots
WHERE time > NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

Appendix: Quick Reference

File Locations

Status Values