Actuarial Validation & Filing Automation

Automating NAIC Filing Deadline Alerts in Python

Automating NAIC filing deadline alerts means turning a fixed regulatory calendar — the March 1 Annual Statement and Actuarial Opinion, the May/August/November quarterly blanks, the April 1 VM-20 PBR Actuarial Report — into a deterministic Python service that fires a notification only when a filing is genuinely at risk, and records that it did so in a form an examiner can reproduce. The hard part is not the reminder; it is making the alert stateful and idempotent: it must survive process restarts, never double-dispatch, never leak policyholder data into a webhook payload, and only escalate a filing to alerted once its upstream model run has actually passed validation. This page builds that minimal service — a SQLite-backed deadline tracker with a timezone-correct alert window — and hardens it for the failure modes that show up in production. It is the filing-side technique that sits under Data Security & PII Boundaries for Filing Systems and the broader Regulatory Architecture & Compliance Mapping control plane.

Filing-deadline alert lifecycle: Pending, Validated, Alerted, Dispatched A left-to-right state diagram. A start dot flows into Pending; the "model run passes" transition goes to Validated; the "within alert window" transition goes to Alerted. From Alerted a solid "primary channel" transition reaches the filled Dispatched terminal state, which flows to a final end marker. A dashed "primary fails" transition drops from Alerted to a Fallback state below, and a dashed "secondary channel" transition rises from Fallback back up to Dispatched. passes in window primary channel primary fails secondary channel Pending awaiting model run Validated model run passed Alerted in window · unsent Fallback secondary channel Dispatched last_alert_sent set

A naive cron job that emails “VM-20 report due in 3 days” every morning fails on all four counts: it fires whether or not the report is ready, it re-sends after a restart, it has no record it ran, and it treats the deadline date as a naive local timestamp that silently drifts across a daylight-saving boundary. The service below replaces each of those with an explicit state transition.

Minimal Working Example

Two objects do the whole job. A frozen FilingDeadline describes one obligation — jurisdiction, filing type, the due instant in UTC, and the upstream model_run_status that gates it. A ComplianceScheduler persists deadlines to SQLite and answers one question on each tick: which validated filings have crossed into their alert window and have not yet been notified?

import sqlite3
import hashlib
import json
from dataclasses import dataclass, asdict
from datetime import datetime, timedelta, timezone
from pathlib import Path
from typing import Optional


@dataclass(frozen=True)
class FilingDeadline:
    jurisdiction: str            # NAIC state code, e.g. "NY", "IL"
    filing_type: str             # "annual_statement", "actuarial_opinion", "vm20_pbr"
    due_date_utc: datetime       # tz-aware, always UTC
    alert_window_hours: int = 72
    model_run_status: str = "pending"   # pending -> validated


class ComplianceScheduler:
    def __init__(self, db_path: Path = Path("naic_deadlines.db")):
        self.db_path = db_path
        self._init_db()

    def _init_db(self) -> None:
        with sqlite3.connect(self.db_path) as conn:
            conn.execute("PRAGMA journal_mode=WAL;")
            conn.execute("PRAGMA synchronous=FULL;")
            conn.execute("""
                CREATE TABLE IF NOT EXISTS deadlines (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    jurisdiction TEXT NOT NULL,
                    filing_type TEXT NOT NULL,
                    due_date_utc TEXT NOT NULL,
                    alert_window_hours INTEGER DEFAULT 72,
                    model_run_status TEXT DEFAULT 'pending',
                    last_alert_sent TEXT,
                    deadline_key TEXT UNIQUE
                );
            """)
            conn.execute("CREATE INDEX IF NOT EXISTS idx_due ON deadlines(due_date_utc);")

    def upsert(self, d: FilingDeadline) -> None:
        # Identity is jurisdiction + filing_type + due instant, NOT the mutable status,
        # so a re-run that flips status to 'validated' updates the row in place.
        key = hashlib.sha256(
            f"{d.jurisdiction}|{d.filing_type}|{d.due_date_utc.isoformat()}".encode()
        ).hexdigest()
        with sqlite3.connect(self.db_path) as conn:
            conn.execute("""
                INSERT INTO deadlines
                    (jurisdiction, filing_type, due_date_utc,
                     alert_window_hours, model_run_status, deadline_key)
                VALUES (?, ?, ?, ?, ?, ?)
                ON CONFLICT(deadline_key)
                DO UPDATE SET model_run_status = excluded.model_run_status;
            """, (d.jurisdiction, d.filing_type, d.due_date_utc.isoformat(),
                  d.alert_window_hours, d.model_run_status, key))

    def due_for_alert(self, now_utc: Optional[datetime] = None) -> list[dict]:
        now = now_utc or datetime.now(timezone.utc)
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            rows = conn.execute("""
                SELECT * FROM deadlines
                WHERE model_run_status = 'validated'
                  AND last_alert_sent IS NULL
                  AND julianday(due_date_utc) - julianday(?)
                        <= alert_window_hours / 24.0
                  AND julianday(due_date_utc) - julianday(?) >= 0
                ORDER BY due_date_utc ASC;
            """, (now.isoformat(), now.isoformat())).fetchall()
            return [dict(r) for r in rows]

    def mark_dispatched(self, deadline_id: int, when_utc: datetime) -> None:
        with sqlite3.connect(self.db_path) as conn:
            cur = conn.execute("""
                UPDATE deadlines SET last_alert_sent = ?
                WHERE id = ? AND last_alert_sent IS NULL;
            """, (when_utc.isoformat(), deadline_id))
            if cur.rowcount == 0:
                raise RuntimeError(f"deadline {deadline_id} already dispatched")

Wire it to a scheduled tick — APScheduler, a Kubernetes CronJob, or a bare while loop — and the alert body carries only filing metadata, never a reserve figure or a policy_id:

scheduler = ComplianceScheduler()
scheduler.upsert(FilingDeadline(
    jurisdiction="NY",
    filing_type="vm20_pbr",
    due_date_utc=datetime(2026, 4, 1, 5, 0, tzinfo=timezone.utc),  # Apr 1, 00:00 ET
    model_run_status="validated",
))

for row in scheduler.due_for_alert():
    payload = {
        "jurisdiction": row["jurisdiction"],
        "filing_type": row["filing_type"],
        "due_date_utc": row["due_date_utc"],
        "hours_remaining": round(
            (datetime.fromisoformat(row["due_date_utc"])
             - datetime.now(timezone.utc)).total_seconds() / 3600, 1),
    }
    # dispatch(payload) ... then, only on success:
    scheduler.mark_dispatched(row["id"], datetime.now(timezone.utc))

How the Alert Loop Works

The design decisions here are all defensive, and each maps to a way the naive version fails.

Identity is the deadline, not the status. deadline_key hashes jurisdiction | filing_type | due_date_utc and nothing else. That is what makes upsert idempotent: the nightly job that recomputes the calendar and re-runs the model can call upsert a hundred times, and a row flips from pending to validated in place rather than accumulating duplicates. This is the same pending → validated gate the state diagram shows — an alert can never fire for a filing whose upstream actuarial model run has not passed, because due_for_alert filters on model_run_status = 'validated'.

The window is computed in UTC, in the database. due_date_utc is stored as an ISO-8601 string and compared with SQLite’s julianday, so “within 72 hours of the deadline” is julianday(due) - julianday(now) <= alert_window_hours / 24.0. A New York VM-20 report due at midnight Eastern is stored as 05:00Z (or 04:00Z under EDT) — never as a naive local timestamp — which is the only way the window stays correct across a daylight-saving boundary. The lower bound >= 0 suppresses alerts for filings that are already past due; those belong on a separate escalation path, not the pre-deadline reminder channel.

Dispatch is guarded by the row itself. mark_dispatched updates WHERE id = ? AND last_alert_sent IS NULL and raises if rowcount == 0. Two workers that both pull the same row from due_for_alert cannot both mark it: the second UPDATE matches zero rows and fails loudly. Combined with WAL mode and synchronous=FULL, the “sent” flag is the single source of truth for whether a notification has left the building — so a crash between dispatch and flag-write is the only window for a duplicate, and that window is a single statement wide.

Edge Cases and Production Hardening

A channel is down when the window opens. SMTP throttling and webhook 5xxs are routine, and a dropped alert during the last 72 hours before a statutory deadline is an operational-risk event in its own right. Wrap the dispatch in bounded exponential backoff, then escalate to a second channel before giving up. The retry delay for attempt nn is delayn=b⋅2n\text{delay}_n = b \cdot 2^{n} seconds, capped so a slow primary cannot consume the whole window:

import time
import requests


def dispatch_with_fallback(payload: dict, primary_url: str, fallback_url: str,
                           max_retries: int = 3, base_delay: float = 2.0) -> str:
    for attempt in range(max_retries):
        try:
            requests.post(primary_url, json=payload, timeout=10).raise_for_status()
            return "primary"
        except requests.RequestException:
            time.sleep(min(base_delay * (2 ** attempt), 30.0))
    # Primary exhausted -> escalate. Preserve the original alert, do not mutate it.
    requests.post(fallback_url, json=payload, timeout=10).raise_for_status()
    return "fallback"

Only call mark_dispatched after this returns; a raised exception leaves last_alert_sent null so the next tick retries cleanly.

The clock jumps or the model re-fails after validation. If a filing is re-opened — an assumption is corrected and the model is re-run — its status may revert from validated to pending. Because due_for_alert re-reads status on every tick, a reverted filing simply drops out of the result set; it does not carry a stale “validated” alert forward. Guard the reverse case too: assert that due_date_utc.tzinfo is not None on ingestion, so a naive datetime from a hand-edited calendar row is rejected at the boundary rather than silently compared as if it were UTC.

Duplicate suppression across restarts. The failure mode a cron reminder cannot avoid is re-sending after a redeploy. Here the last_alert_sent column is the durable idempotency key: on restart, due_for_alert still filters last_alert_sent IS NULL, so already-notified filings never re-enter the queue. If you need periodic re-reminders (say, T-72h, T-24h, T-4h), model them as distinct alert_window tiers with their own sent-flags rather than clearing the single flag — clearing it reintroduces the double-dispatch bug. For portfolios where the calendar itself is generated from thousands of policy-level obligations, precompute the deadline set with the batch patterns in Async Batch Processing for Large Models.

Compliance Note

A deadline alert only counts as a control if it is evidence. NAIC VM-20 Section 8 requires the PBR Actuarial Report to be delivered on the statutory timeline, and the OSFI Model Risk Management Guidelines (E-23 Principle 4) expect the operational processes around a model — including the controls that ensure timely regulatory submission — to be documented and independently verifiable. That means the alert service must emit a tamper-evident record: serialize each dispatch to a canonical JSON payload, bind it to a SHA-256 checksum, and chain each entry’s hash to the previous one so the sequence of “who was alerted, for which filing, at which instant” cannot be silently rewritten before an examination. That append-only, hash-chained ledger is exactly the structure detailed in Actuarial Audit Trail Architecture, and the filing package those records ultimately support is described in NAIC VM-20 Compliance Frameworks. Keeping policyholder identifiers and reserve figures out of the alert payload — as the example does — is the data-minimization half of the same obligation.

Up a level: Data Security & PII Boundaries for Filing Systems — the containment methodology this alerting technique lives under — part of Regulatory Architecture & Compliance Mapping.