第191回 欠番待ちの長期化を検知する:PythonとSQLiteの読み取り専用CLI

順番が入れ替わって届いたイベントをSQLiteへ保留しても、欠けた連番が届かなければ後続イベントは適用できません。今回は、未来のイベントを最初に保留した受信時刻から待ち時間を計算し、期限へ到達した欠番待ちを検出する読み取り専用CLIを作ります。

このCLIは欠番の取得、再送要求、保留行の削除、連番の読み飛ばしを行いません。待ち時間だけを理由にイベントを捨てると、後から欠番が届いた際に正しい適用順を復元できないためです。

監視できる時間の起点

欠けているイベント自体は受信していないため、その発生時刻は分かりません。代わりに、現在の適用済み連番より先のイベントを最初に保留した受信時刻を使います。

状態 判定 結果
適用済みが10、保留の最小連番が12 連番11が欠けている 最古の保留時刻から待ち時間を計算
待ち時間が閾値未満 waiting 終了コード0
待ち時間が閾値以上 overdue 終了コード3
保留の最小連番が期待する次の連番 適用可能な行が残っている 終了コード4
未来のイベントも届いていない 欠番を示す材料がない 検出できない

received_atには、受信側が最初に保留したUTC時刻を保存します。再送時にこの値を更新すると監視期限が先へ延びるため、初回値を維持します。

CLIの契約

項目 仕様
動作環境 Python 3.11以上、標準ライブラリのsqlite3
データベース mode=roで開き、さらにquery_onlyを有効にする
時刻形式 UTC秒精度のYYYY-MM-DDTHH:MM:SSZ
期限判定 age_seconds >= max_wait_seconds
標準出力 通常の検査結果と、想定済みの引数・DB・データエラーを1行JSONで出力
終了コード0 検出対象の不整合と期限到達がない。期限前の欠番はあり得る
終了コード1 ファイルまたはSQLiteへのアクセスに失敗
終了コード2 コマンドライン引数が不正
終了コード3 少なくとも一つの欠番待ちが期限へ到達
終了コード4 検査対象のスキーマ、データ整合性、件数上限に問題がある

--helpだけは引数エラーではなく、argparseのヘルプ文を表示して終了コード0を返します。また、Python自体の障害やコード上の未処理バグまでJSON化する契約ではありません。

再現用データベース

次をprepare_gap_demo.pyとして保存します。既存のgap-demo.dbは上書きしません。

from pathlib import Path
import sqlite3

path = Path('gap-demo.db')
if path.exists():
    raise SystemExit('gap-demo.db already exists')

with sqlite3.connect(path) as conn:
    conn.executescript('''
    PRAGMA foreign_keys = ON;

    CREATE TABLE sequence_state (
        stream_key TEXT NOT NULL PRIMARY KEY
            CHECK(typeof(stream_key) = 'text' AND length(stream_key) > 0),
        last_applied_sequence INTEGER NOT NULL
            CHECK(typeof(last_applied_sequence) = 'integer'
                  AND last_applied_sequence >= 0)
    );

    CREATE TABLE pending_event (
        stream_key TEXT NOT NULL,
        sequence INTEGER NOT NULL
            CHECK(typeof(sequence) = 'integer' AND sequence > 0),
        event_id TEXT NOT NULL UNIQUE,
        received_at TEXT NOT NULL
            CHECK(typeof(received_at) = 'text' AND length(received_at) = 20),
        PRIMARY KEY (stream_key, sequence),
        FOREIGN KEY (stream_key) REFERENCES sequence_state(stream_key)
    );
    ''')

    conn.executemany(
        'INSERT INTO sequence_state VALUES (?, ?)',
        [('SKU-A', 10), ('SKU-B', 20), ('SKU-C', 30)],
    )
    conn.executemany(
        'INSERT INTO pending_event VALUES (?, ?, ?, ?)',
        [
            ('SKU-A', 12, 'event-a12', '2026-11-18T00:00:00Z'),
            ('SKU-A', 13, 'event-a13', '2026-11-18T00:05:00Z'),
            ('SKU-B', 22, 'event-b22', '2026-11-18T00:10:00Z'),
        ],
    )

print('created gap-demo.db')

SKU-Aは連番11、SKU-Bは連番21を待っています。保留行のないSKU-Cは結果に現れません。

欠番待ちを検査するCLI

次をmonitor_sequence_gaps.pyとして保存します。

#!/usr/bin/env python3
from __future__ import annotations

import argparse
import json
import re
import sqlite3
import sys
from datetime import datetime, timezone
from pathlib import Path

STAMP_FORMAT = '%Y-%m-%dT%H:%M:%SZ'
STAMP_PATTERN = re.compile(
    r'[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2}Z'
)


class InvalidDatabase(Exception):
    pass


def emit(payload: dict[str, object]) -> None:
    print(json.dumps(payload, ensure_ascii=False, separators=(',', ':')))


class JsonArgumentParser(argparse.ArgumentParser):
    def error(self, message: str) -> None:
        emit({
            'status': 'error',
            'reason': 'invalid_arguments',
            'message': message,
        })
        raise SystemExit(2)


def parse_utc(value: str) -> datetime:
    if not isinstance(value, str) or STAMP_PATTERN.fullmatch(value) is None:
        raise ValueError('timestamp must use YYYY-MM-DDTHH:MM:SSZ')
    try:
        parsed = datetime.strptime(value, STAMP_FORMAT)
    except ValueError as exc:
        raise ValueError('timestamp contains an invalid date or time') from exc
    if parsed.strftime(STAMP_FORMAT) != value:
        raise ValueError('timestamp is not canonical')
    return parsed.replace(tzinfo=timezone.utc)


def format_utc(value: datetime) -> str:
    return value.astimezone(timezone.utc).strftime(STAMP_FORMAT)


def canonical_utc(value: object) -> str | None:
    try:
        return format_utc(parse_utc(value))
    except (TypeError, ValueError):
        return None


def utc_argument(value: str) -> datetime:
    try:
        return parse_utc(value)
    except ValueError as exc:
        raise argparse.ArgumentTypeError(str(exc)) from exc


def positive_integer(value: str) -> int:
    try:
        number = int(value)
    except ValueError as exc:
        raise argparse.ArgumentTypeError('must be an integer') from exc
    if number <= 0:
        raise argparse.ArgumentTypeError('must be greater than zero')
    return number


def open_read_only(path: Path) -> sqlite3.Connection:
    if not path.is_file():
        raise FileNotFoundError(path)
    uri = path.resolve().as_uri() + '?mode=ro'
    conn = sqlite3.connect(uri, uri=True, timeout=5.0)
    try:
        conn.row_factory = sqlite3.Row
        conn.execute('PRAGMA query_only = ON')
        return conn
    except Exception:
        conn.close()
        raise


def table_info(conn: sqlite3.Connection, table: str) -> list[sqlite3.Row]:
    return conn.execute(f'PRAGMA table_info("{table}")').fetchall()


def validate_table(
    conn: sqlite3.Connection,
    table: str,
    columns: dict[str, str],
    primary_key: list[str],
) -> None:
    rows = table_info(conn, table)
    by_name = {row['name']: row for row in rows}

    missing = sorted(set(columns) - set(by_name))
    if missing:
        raise InvalidDatabase(
            f'{table} is missing columns: {",".join(missing)}'
        )

    for name, declared_type in columns.items():
        row = by_name[name]
        actual_type = str(row['type']).strip().upper()
        if actual_type != declared_type:
            raise InvalidDatabase(
                f'{table}.{name} must be declared as {declared_type}'
            )
        if row['notnull'] != 1:
            raise InvalidDatabase(f'{table}.{name} must be NOT NULL')

    actual_key = [
        row['name']
        for row in sorted(rows, key=lambda item: item['pk'])
        if row['pk'] > 0
    ]
    if actual_key != primary_key:
        expected = ','.join(primary_key)
        raise InvalidDatabase(
            f'{table} primary key must be ({expected})'
        )


def validate_schema(conn: sqlite3.Connection) -> None:
    validate_table(
        conn,
        'sequence_state',
        {'stream_key': 'TEXT', 'last_applied_sequence': 'INTEGER'},
        ['stream_key'],
    )
    validate_table(
        conn,
        'pending_event',
        {
            'stream_key': 'TEXT',
            'sequence': 'INTEGER',
            'received_at': 'TEXT',
        },
        ['stream_key', 'sequence'],
    )


QUERY = '''
WITH pending_by_stream AS (
    SELECT
        stream_key,
        MIN(sequence) AS lowest_pending_sequence,
        MIN(monitor_utc(received_at)) AS oldest_received_at,
        COUNT(*) AS pending_count,
        SUM(CASE
            WHEN typeof(sequence) = 'integer' AND sequence >= 1 THEN 0
            ELSE 1
        END) AS invalid_sequence_count,
        SUM(CASE
            WHEN monitor_utc(received_at) IS NULL THEN 1
            ELSE 0
        END) AS invalid_timestamp_count
    FROM pending_event
    GROUP BY stream_key
)
SELECT
    p.stream_key,
    s.last_applied_sequence,
    p.lowest_pending_sequence,
    p.oldest_received_at,
    p.pending_count,
    p.invalid_sequence_count,
    p.invalid_timestamp_count
FROM pending_by_stream AS p
LEFT JOIN sequence_state AS s
    ON s.stream_key = p.stream_key
ORDER BY p.stream_key
LIMIT ?
'''


def inspect(
    conn: sqlite3.Connection,
    as_of: datetime,
    max_wait_seconds: int,
    max_streams: int,
) -> dict[str, object]:
    conn.create_function(
        'monitor_utc', 1, canonical_utc, deterministic=True
    )
    rows = conn.execute(QUERY, (max_streams + 1,)).fetchall()
    if len(rows) > max_streams:
        raise InvalidDatabase(
            'pending stream count exceeds --max-streams; '
            'no partial health result was produced'
        )

    gaps: list[dict[str, object]] = []
    issues: list[dict[str, object]] = []

    for row in rows:
        raw_key = row['stream_key']
        key = raw_key if isinstance(raw_key, str) else repr(raw_key)

        if not isinstance(raw_key, str) or raw_key == '':
            issues.append({'stream_key': key, 'reason': 'invalid_stream_key'})
            continue

        last = row['last_applied_sequence']
        if last is None:
            issues.append({'stream_key': key, 'reason': 'missing_state'})
            continue
        if type(last) is not int or last < 0:
            issues.append({
                'stream_key': key,
                'reason': 'invalid_state_sequence',
            })
            continue

        if row['invalid_sequence_count'] != 0:
            issues.append({
                'stream_key': key,
                'reason': 'invalid_pending_sequence',
            })
            continue
        if row['invalid_timestamp_count'] != 0:
            issues.append({
                'stream_key': key,
                'reason': 'invalid_received_at',
            })
            continue

        lowest = row['lowest_pending_sequence']
        expected = last + 1
        if lowest <= last:
            issues.append({
                'stream_key': key,
                'reason': 'stale_pending_sequence',
            })
            continue
        if lowest == expected:
            issues.append({
                'stream_key': key,
                'reason': 'ready_event_not_drained',
            })
            continue

        oldest_text = row['oldest_received_at']
        oldest = parse_utc(oldest_text)
        if oldest > as_of:
            issues.append({
                'stream_key': key,
                'reason': 'received_at_after_as_of',
                'oldest_pending_received_at': oldest_text,
            })
            continue

        age_seconds = int((as_of - oldest).total_seconds())
        state = (
            'overdue'
            if age_seconds >= max_wait_seconds
            else 'waiting'
        )
        gaps.append({
            'stream_key': key,
            'last_applied_sequence': last,
            'missing_from': expected,
            'missing_to': lowest - 1,
            'lowest_pending_sequence': lowest,
            'oldest_pending_received_at': oldest_text,
            'age_seconds': age_seconds,
            'pending_count': row['pending_count'],
            'state': state,
        })

    overdue = sum(gap['state'] == 'overdue' for gap in gaps)
    status = 'invalid' if issues else ('overdue' if overdue else 'ok')
    return {
        'status': status,
        'as_of': format_utc(as_of),
        'max_wait_seconds': max_wait_seconds,
        'summary': {
            'pending_streams': len(rows),
            'gap_streams': len(gaps),
            'overdue_gap_streams': overdue,
            'waiting_gap_streams': len(gaps) - overdue,
            'invalid_streams': len(issues),
        },
        'gaps': gaps,
        'issues': issues,
    }


def load_report(
    database: Path,
    as_of: datetime,
    max_wait_seconds: int,
    max_streams: int,
) -> dict[str, object]:
    conn = open_read_only(database)
    try:
        validate_schema(conn)
        return inspect(conn, as_of, max_wait_seconds, max_streams)
    finally:
        conn.close()


def build_parser() -> argparse.ArgumentParser:
    parser = JsonArgumentParser()
    parser.add_argument('database', type=Path)
    parser.add_argument(
        '--max-wait-seconds', required=True, type=positive_integer
    )
    parser.add_argument('--as-of', type=utc_argument)
    parser.add_argument(
        '--max-streams', type=positive_integer, default=1000
    )
    return parser


def main() -> int:
    args = build_parser().parse_args()
    as_of = args.as_of or datetime.now(timezone.utc).replace(microsecond=0)

    try:
        report = load_report(
            args.database,
            as_of,
            args.max_wait_seconds,
            args.max_streams,
        )
    except InvalidDatabase as exc:
        emit({
            'status': 'invalid',
            'reason': 'invalid_database',
            'message': str(exc),
        })
        return 4
    except (OSError, sqlite3.Error) as exc:
        emit({
            'status': 'error',
            'reason': 'database_error',
            'message': str(exc),
        })
        return 1

    emit(report)
    if report['status'] == 'invalid':
        return 4
    if report['status'] == 'overdue':
        return 3
    return 0


if __name__ == '__main__':
    raise SystemExit(main())

スキーマ検査の範囲

この実装は、集計の前提となる列の宣言型とNOT NULL、および次の主キーを検査します。

  • sequence_stateの主キーが(stream_key)であり、同じストリームの状態が複数行にならないこと
  • pending_eventの主キーが(stream_key, sequence)であり、同じ連番の保留行が重複しないこと

そのため、状態行の重複によって一つのストリームが複数の監視結果へ分かれるスキーマは終了コード4になります。集計も、保留行をストリーム単位でまとめてから一意な状態行へ結合します。

一方、全てのCHECK制約、外部キー、追加列、索引設計まで一般的に監査するものではありません。「スキーマの問題を全て検出する」のではなく、このCLIが明示的に必要とする構造を満たさない場合に終了コード4を返します。時刻や連番の実データは別途、各行の検査で確認します。

期限到達と期限前を確認する

データベースを作成してから、基準時刻を固定して実行します。

python prepare_gap_demo.py
python monitor_sequence_gaps.py gap-demo.db \
  --max-wait-seconds 900 \
  --as-of 2026-11-18T00:15:00Z
code=$?
printf 'exit=%s\n' "$code"

このシェル操作の標準出力は2行です。1行目はCLIが出すJSON、2行目は終了コード確認用のprintfが追加する文字列です。

{"status":"overdue","as_of":"2026-11-18T00:15:00Z","max_wait_seconds":900,"summary":{"pending_streams":2,"gap_streams":2,"overdue_gap_streams":1,"waiting_gap_streams":1,"invalid_streams":0},"gaps":[{"stream_key":"SKU-A","last_applied_sequence":10,"missing_from":11,"missing_to":11,"lowest_pending_sequence":12,"oldest_pending_received_at":"2026-11-18T00:00:00Z","age_seconds":900,"pending_count":2,"state":"overdue"},{"stream_key":"SKU-B","last_applied_sequence":20,"missing_from":21,"missing_to":21,"lowest_pending_sequence":22,"oldest_pending_received_at":"2026-11-18T00:10:00Z","age_seconds":300,"pending_count":1,"state":"waiting"}],"issues":[]}
exit=3

SKU-Aは待ち時間が900秒ちょうどなのでoverdueです。SKU-Bは300秒なのでwaitingです。基準時刻を2026-11-18T00:14:59Zへ変えると両方とも期限前になり、終了コードは0になります。

エラー出力を確認する

必須引数を省略すると、JSONを標準出力へ1行だけ出して終了コード2を返します。通常のargparseのエラー文を標準エラーへ出す動作は、JsonArgumentParser.errorで置き換えています。

python monitor_sequence_gaps.py gap-demo.db
code=$?
printf 'exit=%s\n' "$code"
{"status":"error","reason":"invalid_arguments","message":"the following arguments are required: --max-wait-seconds"}
exit=2

存在しないDBファイルやSQLiteアクセスエラーも、reasonをdatabase_errorとして標準出力へ返します。これにより、文書化した終了コード1から4の経路では、呼び出し側が同じ方法で1行JSONを読み取れます。

不整合を期限警告より優先する

検出理由 意味
missing_state 保留イベントに対応する状態行がない
stale_pending_sequence 適用済み連番以前のイベントが残っている
ready_event_not_drained 次に適用できるイベントが保留されたまま
invalid_received_at 受信時刻が指定形式でない、または実在しない日時
received_at_after_as_of 保留時刻が監視基準時刻より未来

不整合が一つでもあれば、別のストリームに期限到達があっても終了コード4を優先します。壊れたデータを含む結果を通常の期限警告として扱わないためです。

組み込み時の制約

  1. 初回のreceived_atをイベントの保留と同じトランザクションで保存する。
  2. 重複受信では既存のreceived_atを更新しない。
  3. 実際のテーブル構造に合わせて、SQLだけでなくvalidate_schemaも変更する。
  4. 業務上許容できる待ち時間を決め、監視側で終了コード3を処理する。

--max-streamsの既定値は1000です。上限を超える場合は部分的な正常結果を返さず、終了コード4にします。ただし、上限判定までの集計では保留行を走査します。保留件数が多い環境では、実データ量を使って実行時間を確認する必要があります。

このCLIが解決しないこと

  • 未来イベントが一件も届かなければ、欠番の存在を推測できない。
  • 送信元から欠番を取得したり、再送を要求したりしない。
  • 期限到達後も保留イベントを削除せず、連番を自動的に読み飛ばさない。
  • 受信側の時計がずれたり巻き戻ったりすると、待ち時間の信頼性が下がる。
  • 古いDBコピーを監視した場合、実際の受信状態とは一致しない。

このCLIが示すのは、未来イベントが既に届いているストリームについて、どの連番をどれだけ待っているかです。その情報を欠番の再配送や送信元との照合へつなぐ処理は、監視とは分けて設計します。