nbadb

Imported from wyattowalsh/nbadb (AGENTS.md). Install upstream with npx skills add wyattowalsh/nbadb. Copyright stays with the author.

nbadb — Agent Instructions

Project Overview

nbadb is a comprehensive NBA database built around the current nba_api runtime surface with 162 registered extractors, 438 canonical staging routes, 261 transform outputs, and 261 schema-backed public tables. Two typed lossless staging tables are conditional: they are published only when the corresponding stats or live drift was observed. It follows an ELT pipeline: extract from NBA API → stage in DuckDB → transform into the analytics/star schema → export to SQLite/DuckDB/Parquet/CSV.

Tech Stack

• Python ≥3.12, uv (package manager), hatchling (build)
• nba_api 1.11.4 (exact runtime/docs/tools contract pin)
• Polars 1.43.2 (primary DataFrame engine)
• DuckDB 1.5.5 (staging engine, zero-copy Arrow interchange)
• Pandera[polars] 0.32.1 (3-tier schema validation: raw → staging → star)
• SQLModel 0.0.39
• ty 0.0.58 (type checker), ruff (lint/format)
• Docs: Fumadocs 16 + Next.js 16 + pnpm + Tailwind v4

Module Map

src/nbadb/
├── extract/          # 162 registered extractors wrapping nba_api endpoints and static datasets
│   ├── stats/        # Statistical endpoint extractors
│   ├── static/       # Static data extractors (players, teams, arenas, awards)
│   └── live/         # Live game data extractors
├── schemas/          # Pandera schema definitions
│   ├── raw/          # Raw extraction schemas
│   ├── staging/      # Staging schemas + STAGING_MAP-backed staging keys
│   └── star/         # Output table schemas for final analytics model
├── transform/        # 261 transform outputs (253 historical + 8 live snapshot outputs)
│   ├── dimensions/   # 18 dimension builders (dim_*)
│   ├── facts/        # 204 fact_* outputs + 6 bridge_* outputs
│   ├── derived/      # 19 aggregate builders (agg_* outputs live here)
│   └── views/        # 14 analytics_* builders
├── load/             # SQLite/DuckDB/Parquet/CSV loaders
├── orchestrate/      # Pipeline orchestration + staging map
├── cli/              # Typer CLI + Textual TUI surface
├── core/             # Config, database, logging, coverage helpers
├── agent/            # Natural-language query agent
├── kaggle/           # Kaggle download/upload integration
└── docs_gen/         # Auto-generates schema/data-dictionary/ER/lineage artifacts

Additional repo-owned surfaces:

chat/                # Canonical Chainlit app surface; `nbadb chat` uses it when launcher files are present
src/nbadb/chat/      # Shared chat launcher, notebook, runtime, tracing, SQL, catalog, and memory helpers
kb/                  # Companion Obsidian-native knowledge base for maintainers and agents

Key Conventions

Coverage Trust Floor

• Preserve and improve full historical nba_api coverage for every year available per endpoint.
• If an endpoint/year/season-type combination is unavailable upstream or blocked by a known contract gap, classify it explicitly in audits/support matrices instead of silently dropping it.

Naming

• dim_* — Dimension tables (18)
• fact_* — Fact tables (204)
• bridge_* — Bridge tables (6)
• agg_* — Pre-aggregated rollups (19 current outputs; code lives under transform/derived/)
• analytics_* — Analytics convenience tables/views (14)
• stg_* — DuckDB staging tables generated from STAGING_MAP
• raw_* — Raw extraction data

SqlTransformer Base Class

Most transformers (100+) extend SqlTransformer(BaseTransformer) — define _SQL as a ClassVar, no transform() override needed. The base class handles execution.

Schema Validation

3-tier Pandera validation:

• Raw: Validates extracted DataFrames before staging
• Staging: Validates after DuckDB load
• Star: Validates final output tables
• BaseSchema.Config.strict=False — hard-fail on missing/type errors; preserve additive raw/staging fields and project curated star outputs to their declared schema

Full Extraction Control Plane

• The workflow_guard is the first admission boundary for every full extraction. It observes a stable exact-title workflow inventory, requires a fresh primary or partial workflow_dispatch with run_attempt == 1, and rejects gh run rerun and gh run rerun --failed. Inline manifests, receipt-bound lane_manifest_run_id handoffs, and operation=continue recovery are mutually exclusive. A continuation dispatch supplies the exact five-field source authority — source run id, source run attempt, manifest artifact name, manifest artifact id, and manifest artifact digest (ContinuationDispatchInputs); the attempt is never inferred from an artifact name or any latest-style lookup. Source recovery requires the original explicit chain_id and a distinct completed source run; do not treat a prior attempt of the current run as recoverable state.
• Fresh runs default to the standard chunk profile. Within each matrix wave, the scheduler targets a 5:3:1:1 rotation across fresh, partial-progress, retry, and infrastructure lanes when every queue has work, then applies priority and a ceiling-rounded 25% endpoint-family cap when alternatives are available. When another endpoint identity is available, no six-lane scheduling window may remain endpoint-homogeneous. This is endpoint-pressure diversity across six runner slots, not evidence that the runners have unique VPN exit IPs.
• Durable lane identity comes from the semantic lane contract (lane_id, pattern/endpoints, parameter scope, and coverage hash), not lane_index. The scheduler may renumber or reorder lanes between attempts; lane_index remains required nonnegative attempt-local routing metadata but must never invalidate otherwise matching completed or resumable state.
• Full-extraction manifests contain only executable parameter contracts. player_vs_player, team_vs_player, canonical team_and_players_vs, and its extractor-only team_and_players_vs_players alias remain schema-backed but are explicitly contract_not_modeled_yet for historical fan-out because player-team affiliations cannot prove comparison pairs or opposing lineups. Do not synthesize Cartesian matchup requests; restored manifests containing excluded endpoints must fail during planning before VPN allocation.
• league_game_log is discovery-seed-owned and must not receive a separate extraction lane. Coverage rows that canonicalize alternate wrappers must be projected through their staging keys to every concrete runtime endpoint/pattern pair before scheduling. This preserves distinct alias staging surfaces such as player_game_logs/player_game_logs_v2 and player_game_streak_finder/player_streak_finder without zero-work canonical lanes.
• The strict TeamYears canary and public nbadb.core.NBA_HEADERS copy must match the exact ordered STATS_HEADERS mapping from the pinned nba_api runtime. Update the dependency, standalone connector copy, public export, and parity tests atomically.
• Every VPN tunnel must pass route and changed-exit-IP checks, a bounded GitHub control-plane reachability probe, a strict TeamYears JSON probe, and installed-stack common_all_players plus league_game_log canaries. The player canary requires positive player/team membership, matching workload discovery. A server that severs GitHub reachability is disconnected and replaced before the Actions runner can be stranded behind the tunnel. NBA-rejected servers are recorded, excluded across fallback technologies, disconnected, and replaced before discovery or extraction begins. Authentication rejections are capacity/account evidence rather than server-health evidence, so they never poison the chain quarantine. Before each configured-credential wave, a concurrent admission gate proves the requested active tunnel count: every probe publishes a run-attempt marker while connected, waits for every peer marker, then rechecks its process, route, and exit IP before disconnecting. Any probe, marker, barrier, or recheck failure blocks the extraction matrix. Capacity slot zero is preferred-only on the just-proven discovery/preflight anchor and has no fresh or protocol fallback; failure blocks the gate immediately. The remaining probes partition the complete fresh recommendation pool across capacity - 1 explicit slots. An explicit single fresh slot owns the full pool and uses Nord's current recommendation rank as the tie-breaker after network and city diversity, so a connected anchor never strands half of the candidates in an unused hash bucket. A separate fail-closed gate downloads every exact successful capacity marker, validates its chain/source/run/attempt/lane/auth/tunnel provenance, merges preflight and discovery failures with the successful probes' failed hosts, and publishes a run-attempt-scoped effective-quarantine report. A probe that cannot complete contributes diagnostics only; because the matrix and child dispatch are blocked, its unvalidated failed-host list is not promoted. Extraction lanes consume the validated union, and lane control persists it into child manifests. Each extraction connector reserves one complete initial attempt and cleanup, one five-minute cooldown, and one complete follow-up attempt and cleanup before starting an authentication-capacity probe; it stops the initial sweep on the first rejection and then allows up to three rejections in one recovery sweep inside a 12-minute connector budget. Preflight and discovery retain their shorter fail-closed deadlines. vpn_network_error, vpn_auth_failure, and vpn_connect_timeout are bounded vpn_egress failures, never one-shot application failures. Preflight attests the credential source used by downstream jobs and fails closed on missing/unknown attestations. Serial discovery tries the preflight-proven host first. Discovery failures are quarantined before capacity admission. The servers proven by preflight and discovery are then passed to the capacity gate and extract lanes as a verified pool. Logical lane N may use only preferred host N; assignment never wraps modulo. Every planned matrix row also carries a bounded vpn_slot: for N rows and S requested slots, the manifest uses min(N, S) contiguous slots in deterministic row-index round-robin order, preserves exact unique lane membership/order, requires integer in-range values, and rejects loads that differ by more than one or exceed ceil(N / S). Later logical lanes may reuse a slot only through its non-cancelling named per-slot job concurrency group (cancel-in-progress: false; GitHub Actions job concurrency exposes only group and cancel-in-progress), so no two live jobs own one slot. Fresh recommendation hostnames are mapped by a run-attempt-seeded hash to exactly one live slot; shared preferred/quarantined hosts are removed before partitioning, local attempt failures are removed after partitioning, and additive pool expansion never rebases another slot's ownership. These rules prevent concurrent host overlap but do not attest distinct exit IPs across runners. Token-derived extraction runs at most one simultaneous matrix job, disables parallel recommendation partitioning, and skips the configured-credential capacity gate without shrinking the already planned batch; configured-credential runs use vpn_parallelism, which defaults to two simultaneous tunnels. Both network_mode=vpn and network_mode=auto planning cap each iteration at vpn_parallelism * 32 jobs before credential mode is resolved; actual simultaneous matrix concurrency is the smaller admitted configured capacity, one job for token-derived auth, or the direct-mode setting. Fast configured-credential production launches explicitly pass vpn_parallelism=6 and must pass the six-tunnel admission gate. Keep the global default at two because planning happens before credential mode is resolved; changing it to six would make token-derived runs plan 192 jobs that later execute serially. VPN/auto full-extraction workflows share one non-cancelling repository concurrency group, while direct chains remain independently scoped.
• Installed-stack failure attestations may expose only allowlisted exception class names, never messages, response bodies, parameters, or credentials. A recognized root class governs transport-versus-contract routing: transport roots may rotate and quarantine that server, while response-contract roots fail host-independently and must not enter the failed-server inventory. Missing or invalid roots fall back to the validated outer class.
• Configured-credential extraction admits the complete current matrix behind the bounded non-cancelling per-slot concurrency groups. The slot groups, not a vpn_parallelism-sized matrix admission window, enforce the live tunnel count; otherwise a queued job for one slow slot can consume the admission credit needed to keep another slot active. Direct mode retains direct_parallelism, and token-derived VPN auth retains max-parallel=1.
• A connector that returns exact vpn_auth_failure publishes one immutable run-attempt circuit artifact. The check runs inside each admitted matrix job, so siblings already past it can each receive one rejection before the first marker is visible. Later queued lanes must query that exact marker before authenticating and trust it only after exact artifact name/id/size/download URL/digest, workflow run/source, archive SHA-256, single safe JSON member, and marker schema/provenance validation. GitHub may finalize concurrent first-writer uploads under the same exact artifact name; in that case the resolver validates every changed inventory across a bounded three-snapshot observation window and requires the final two snapshots to agree. The lowest positive artifact ID in that stable inventory is selected deterministically. Any malformed or unstable inventory fails closed. Every lane runs this guard after checkout and again immediately before connector authentication. A verified marker emits vpn_auth_circuit_open retry-neutral metadata. An unavailable or invalid artifact lookup fails closed before authentication but emits vpn_auth_circuit_check_failed as a bounded runner_infrastructure retry; it must not increment circuit-open counts or suppress a healthy redispatch. Circuit-deferred metadata is retry-neutral only after exact schema, chain, source SHA, coverage hash, lane identity, scope, zero-progress, and unattested-state validation. Valid deferred lanes preserve attempt counts, failure streaks, progress baselines, and durable-state pointers; already-admitted rejecting lanes consume their bounded vpn_egress retry. Lane control treats either a rejecting lane or a valid circuit-open sibling as circuit-open, checkpoints completed work, and suppresses automatic redispatch. vpn_connect_timeout, vpn_network_error, and control-plane lookup failure must not open the authentication circuit.
• Each extract job records a 350-minute internal deadline before checkout inside the 360-minute Actions cap and dynamically caps the lane extraction timeout to retain at least 20 minutes for final status, DuckDB checkpoint/attestation, metadata, artifact upload and retry, diagnostics, and VPN disconnect. Each extraction connector separately stops attempts before a 120-second finalization reserve for process cleanup, work-directory readability, and action outputs, with an outer supervisor margin. Do not raise a lane timeout or connector window without preserving both finalization reserves and the separate job-level margin.
• The centralized discovery_seed job derives exact current-matrix season/season-type scopes for game/date and player-team-season discovery, restores the exact prior canonical or run/attempt-scoped recovery bundle selected through lane_manifest_run_id or the exact five-field continuation source (source run id, run attempt, manifest artifact name, artifact id, artifact digest), and publishes the cumulative discovery artifact for extract lanes. Cross-run provenance distinguishes semantic source S=WORKFLOW_SOURCE_SHA from producing-run head H: manifests/checkpoints remain bound to S, artifact REST ownership remains bound to the exact run and H, and the owner is admissible only when S == H or S is an ancestor of H with byte-identical .github/workflows/full-extraction.yml content. Current-run artifacts bind ownership to GITHUB_SHA while retaining semantic S. The source must be a distinct completed workflow run with matching chain, workflow identity, artifact inventory, and receipt provenance. An absent, expired, ambiguous, unrelated, workflow-drifted, or otherwise invalid explicit source fails closed; it never falls back to current-run prior attempts or a scratch reseed. Restored legacy v1 data is promoted only for validated exact single-season player/game scopes; aggregate v1 bundles are never promoted. Active-season player, game, team, and workload evidence is refreshed while stable historical scopes remain reusable. Sparse missing player-team pairs are grouped without widening them into a Cartesian product. Typed zero-row game/workload coverage is valid; empty player-ID coverage, absent artifacts, and partial required scopes are not.
• Discovery seeding is fail-closed and bounded: every request has a hard timeout, and both transport-transient failures and response-contract/validation failures, including wrapped causes, consume the bounded retry budget. Broad homogeneous transport/response outages use a three-attempt bounded canary, a 90-minute in-process deadline checkpoints partial state, and a 95-minute process watchdog leaves upload headroom before the 120-minute job cap. Discovery and player/team workload Parquet use immutable content-addressed generations; atomic manifest pointers bind exact scope/pairs, schema, row counts, sentinel semantics, and SHA-256. Valid fixed-name workload v3 pairs are promoted explicitly; invalid/partial legacy state is rejected. The command checkpoints an atomic schema-v2 summary bound to the exact lane-manifest digest. A standalone verifier independently derives required units from that manifest and reloads every scope/generation before canonical upload and after each extract lane installs the bundle. Only a verified complete seed receives the canonical name; incomplete state uses a run/attempt-scoped recovery name and must pass the full seed plus verifier gates before later canonical publication. Failed, cancelled, or timed-out discovery cannot run lane_control, consume lane retry budgets, create checkpoints, or dispatch a child iteration.
• Checkpoint roll-forward is copy-plus-delta. A new generation must use a different output path, copies the prior checkpoint before merging newly completed lane databases, and leaves the prior artifact unchanged; a zero-delta generation is still a distinct physical copy. Prior/current lane artifacts and prior checkpoints are accepted only when exact chain, source, run, artifact, generation, and coverage provenance match. Reject a prior checkpoint inventory containing any lane outside the current manifest. Prior generations are also accepted only when the report's database SHA-256 and per-lane coverage hashes match. Player-team-season lanes additionally require an included_lane_workload_contracts entry whose generation-independent exact-scope identity matches the active cumulative workload; append-only growth outside the lane is valid, but pair/count/base-identity drift inside it fails closed. A lane state is resumable only after DuckDB CHECKPOINT, WAL removal, regular-file and journal validation, exact source/chain/lane/coverage/database attestation, state-attestation schema v3 binding to that exact scope when applicable, and a successful artifact upload whose positive ID and SHA-256 receipt are finalized into metadata. Canonical metadata schema v3 uploads even for unattested restore/VPN failures; only an explicit state_artifact.attested=true, state_artifact.uploaded=true pointer with valid receipt fields may survive into a retry. Active partial pointers use extraction-lane-recovery-<chain>-<lane>-run-<run>-attempt-<attempt> with matching positive run/attempt identities; canonical complete-lane names are not partial-resume pointers. Both the lane-state snapshot and canonical metadata uploads get one exact-name overwrite retry before diagnostics, avoiding re-extraction when either first upload attempt fails. Attested partial lanes that add calls or rows retry in place, including transport-class needs_resume outcomes; timeout-class lanes split only after the latest attempt adds no durable progress. Split descendants clear parent state pointers and progress baselines, manifest/checkpoint entry points reject duplicate lane IDs, and unexpected workload identities fail restore and checkpoint validation. A rejected or unuploaded new snapshot is diagnostics-only and never replaces an older canonical recovery pointer or its counters. Without prior durable state, the pointer and progress baselines remain clear; in either case, unreceipted reported counter growth increments the cumulative no-progress streak. Failed checkpoint builds may upload only attempt-scoped diagnostics; upload the canonical checkpoint artifact only after validation succeeds. Reported contract_blocked evidence must exactly match recomputed support rules, and its canonical rows/digest must match the independent current/previous manifest chain-state commitment. Cancellation/source resume stores newly classified rows in a digest-bound pending commitment that the next checkpoint merges and clears. It lives in a separate artifact-bound blocked-evidence inventory rather than the effective checkpoint coverage used by terminal assured identity. Staging overlap removal preserves the maximum legitimate duplicate multiplicity with EXCEPT ALL.
• Checkpoint publication is a four-state transaction: candidate reserves generation/name and exact semantic coverage, built binds the validated report, DuckDB digest, and lane inventory, uploaded_verified binds the immutable GitHub artifact receipt, and committed is the only state allowed into chain_state.latest_checkpoint_*. The next-manifest pointer is written only after REST verification of the exact artifact ID, run, run attempt, name, digest, size, producing head, semantic source, database hash, and report hash; the checkpoint contract is schema v3 and its receipt-level artifact_run_attempt is always explicit, never inferred from an artifact name. Cross-run restore consumes the exact owner attestation, rechecks attempt one and the S/H ancestry plus workflow bytes, selects from a stable complete inventory, directly rechecks the positive artifact ID, and downloads that ID with digest mismatch set to error. A bounded exact-name selection path exists only for older persisted manifests without a transaction receipt; the name is never download authority.
• video_details and video_details_asset classify 1946-47 through 2003-04 as contract_blocked and schedule all 78 context measures from 2004-05 onward. Run 29195221754 discovery artifact 8260784820 covered all 290 season/type scopes in the blocked range and found 89,722 LeagueGameLog rows representing 44,861 games with VIDEO_AVAILABLE=0; its first three video_details_asset lanes also completed 8,902 zero-row calls. Both endpoints still recursively parse every returned result set, retain endpoint/result-set/request provenance, and group at most three measures per lane. Pre-2019 PlayIn, pre-1950-51 All Star, and cancelled 1998-99 All Star remain upstream_unavailable. Reject non-2xx responses before JSON parsing; classify HTTP 429/5xx and equivalent JSON error envelopes as transient, and reject malformed success payloads as extraction-contract failures. video_details_asset also uses ten-call chunks, two-call concurrency, a 15-second request timeout, zero in-call retries, abort after a fully failed newly attempted chunk, and a 600-second no-completed-chunk watchdog. Successful empty chunks must be persisted before journal success.
• win_probability uses a ten-call persistence/zero-progress boundary and a pattern-local response-contract circuit. Three consecutive identical upstream response-contract failures open the circuit for the rest of that pattern run. Every later call that reaches the open circuit is recorded as failed/resumable without an upstream request. Execution stops after the first fully failed newly attempted chunk, leaving the remaining calls explicitly unattempted. Neither state is success or coverage. A valid response resets the consecutive-failure signature, and full coverage remains required on resume.
• Self-chaining preserves literal max_iterations=auto while enforcing one fixed numeric iteration_budget derived from remaining matrix-wide dispatch credits, retry depth, and possible split descendants. Child runs never extend it. Cumulative zero-progress retries remain bounded even when the observed failure class alternates; class-specific streaks do not reset the global no-progress budget. VPN/auto workflow concurrency is a repository-wide FIFO queue; direct workflow concurrency is scoped by chain and iteration. The pinned semantic source S must be an ancestor of its trusted branch, and publish runs additionally require default-branch ancestry. Committed next-manifest artifacts use run/attempt-unique names and no overwrite. dispatch_next blocks active or successful exact-title children, permits recovery after action_required, cancelled, failed, or timed-out history, then posts an exact workflow_dispatch body containing only {ref, inputs} and forwards the exact manifest artifact ID and digest. The dispatch response must contain exactly workflow_run_id, run_url, and html_url; the parent re-reads that run to prove its URLs, workflow identity, title, event, branch, run_attempt == 1, producing head H, and semantic source before acknowledgement. It no longer infers the new child from title polling. The child REST-verifies the manifest artifact's ID, name, digest, size, owner run/head, and semantic source, applies the S/H attestation, then downloads by ID with digest mismatch set to error. Run/name-only handoff is a bounded legacy path allowed only when both receipt fields are absent and still requires exact producing owner, semantic-source ancestry, workflow-byte identity, stable unique inventory, a direct artifact-ID recheck, digest verification when supplied, and exactly one safe expected manifest member. If the parent stops after dispatch but before acknowledgement, its trap attempts to cancel that exact returned child ID. Manual workflow cancellation prevents queued network jobs from launching, and the lane wrapper makes a bounded signal-driven finalization attempt, but GitHub may terminate an ephemeral runner before artifact-upload steps; resume only from the latest attested artifact or committed checkpoint.
• Terminal assurance is a read-only job. The terminal run merges, transforms, live-snapshots, exports, and then runs scan --fail-on error --full-publication with the checkpoint report, manifest, database directory, chain ID, and expected semantic source SHA, without write permission or Kaggle secrets. Full scan mode invokes the canonical checkpoint verifier, independently recomputes checkpoint staging/journal row counts, requires the lane/run/coverage inventory to account for every manifest lane as either durably complete or canonically contract-blocked with no missing, skipped, attestation, or workload errors, requires every declared silver/gold game, roster, team, box-score, play-by-play, shot, standings, draft, aggregate, and analytics anchor to be populated, and enforces declared row-preserving silver-to-gold cardinality pairs. The assured identity is built only after that scan and uses validated effective checkpoint coverage; valid contract-blocked lanes remain in their separate artifact-bound evidence inventory. Publication is decoupled from extraction: there is no publish input. The terminal run uploads the checkpoint, next manifest, private evidence, and sanitized public candidate, re-reads each exact artifact identity, and seals TerminalPublicationHandoffV1; a separate handoff-bound publish dispatch consumes that sealed receipt in a writer job, and publisher jobs share the named non-cancelling nbadb-kaggle-publish concurrency group. Before fetching archive bytes, the publisher directly verifies the artifact's exact ID, name, canonical sha256:<hex> digest, size, unexpired state, archive URL, producing attempt one, and producing GITHUB_SHA, including a final current-owner re-read. A later current owner attempt is admissible only through the dedicated publication-recovery roles — exact cross-run pending takeover (record_pending_takeover binding an uploaded takeover receipt and artifact member durably, claim_pending_takeover claiming it exactly once after the origin executor is proven durably terminal, then mark_resolved re-verifying that origin live) or no-upload in_progress reconciliation — and may reuse only that immutable attempt-one authority; it cannot upload or substitute assured data. Only then may the publisher download, verify the archive digest and complete safe layout, reject empty archives, duplicate normalized paths, absolute or traversing paths, symlinks, special files, and destination collisions before consuming any member, and revalidate the assured data manifest before Kaggle staging. A zero-active source resume binds its selected resume-source-input-manifest.json and schema-v1 resume-source-selection.json receipt into the uploaded plan artifact. Terminal replay REST-verifies that current-run plan artifact's exact ID, name, digest, size, unexpired state, archive URL, owner run at exact attempt one, and producing GITHUB_SHA after a final owner re-read, separately validates its selected semantic source and any cross-run S/H attestation, downloads it by ID, and validates the selected manifest/report/database trio instead of reselecting a same-name source artifact. Current-run committed-manifest and replay-output authorities receive the same direct receipt, exact-attempt-one, final-owner, and GITHUB_SHA checks before terminal merge consumes them. If cancellation occurred after complete lane uploads but before checkpoint promotion, checkpoint recovery enumerates the manifest's exact chain_state.artifact_run_ids plus the current run, consumes each historical owner attestation, selects exact lane/database and metadata receipts from stable inventories, directly rechecks their IDs, and downloads by ID with archive-digest enforcement. It keeps each GitHub archive digest separate from the corresponding DuckDB database SHA-256, rejects ambiguity or mismatched pairs, and rebuilds the cumulative checkpoint. Ordinary logical artifacts use overwrite semantics so their bounded in-job upload retry can replace the same name; that does not make gh run rerun a valid recovery path. Each lane's initial capacity-marker upload is immutable and uses no overwrite; only after that lane-unique first upload reports failure may the same lane make one exact-name overwrite retry. Auth-circuit markers remain shared first-writer, no-overwrite artifacts. Collision recovery either validates an exact sibling or makes one additional no-overwrite publish attempt only after bounded sibling polling reports absent_timeout; invalid, unstable, or API-failure evidence cannot trigger that retry, and recovery never overwrites an existing marker. operation=targeted_smoke is a one-lane VPN-only exception: it requires a manual manifest, max_iterations=1, and retry_pipeline_failures=false; skips merge and redispatch; and passes only when lane control plus checkpoint attestation prove one complete terminal lane. Never present it as full-dataset assurance.

Kaggle Publication Verification

• nbadb upload --verify-remote requires an exact positive dataset version returned by the publication-marker resolver; cache directory names are not version evidence. It paginates the exact version's complete API file inventory and streams every listed resource through SHA-256 readback one file at a time. nbadb upload --full-publication implies that exact readback and additionally requires a valid assured-artifact-manifest.json plus a declared, schema-valid terminal-assurance-report.json with matching chain, source, complete lane/run/coverage inventory, checkpoint identity, and blocked-evidence digest. The current base contract is 695 tables and 1,394 resources; each actually observed typed lossless staging table adds one table plus CSV and Parquet resources, up to 697 tables and 1,398 resources when both stats and live drift are present. Paths and counts are derived from the runtime registries, not hard-coded. Publication requires table inventory, ordered schema, and row-count parity across DuckDB, SQLite, CSV, and Parquet; DuckDB and Parquet remain the logical-type authorities, while SQLite and CSV are convenience projections. Upload without remote verification is reported as submitted/unverified rather than complete.
• Only a marker-specific Kaggle HTTP 404 enters bootstrap handling. Every downloaded marker and persisted publication record is validated before a decision. For every marker-present baseline, the client requires its exact version to equal the current dataset metadata version and rechecks both marker and version immediately before upload. The marker-missing bootstrap path receives the same just-in-time stabilization. The client permits at most one unresolved upload for the dataset.
• Baseline timeouts, authentication failures, non-404 HTTP errors, invalid versions, and unreadable reconciliation evidence fail closed before upload and are recorded as baseline_reconciliation_failed.
• If any upload remains unresolved, a later run blocks every new bundle until exact marker evidence resolves the prior publication. A matching prior marker is reconciled first; a missing or nonmatching marker fails closed. Inspect logs/kaggle/kaggle-upload-manifest.json before retrying.
• The client serializes same-process uploads and uses fcntl or msvcrt for same-host advisory locking. Automated full, daily, and monthly publication additionally requires actions: read, deployments: write, GH_TOKEN, NBADB_KAGGLE_PUBLICATION_SOURCE_SHA, default-head enforcement, the exact job concurrency stanza group: nbadb-kaggle-publish with cancel-in-progress: false (GitHub Actions job concurrency exposes only group and cancel-in-progress; serialization comes from the named non-cancelling group plus these durable release-control receipts), and --publication-ledger github-deployment --require-durable-intent; full extraction elevates to actions: write only because it explicitly dispatches and verifies metadata-closeout CI. The dataset-specific GitHub Deployment ledger is the crash- and cross-host-durable write-ahead source: it discovers the newest two deployments with GraphQL CREATED_AT DESC, verifies their direct REST receipts and complete at-most-two-status sets across three observations, ignores status transport order and sorts chronology locally, allows at most one unresolved current head, records pending before mutation, verifies in_progress immediately before the Kaggle call, and records success only after exact remote readback. Direct success resolution captures a fresh current executor, requires the resolving execution's run ID, run attempt, publisher job, admission digest, deployment origin, nonce, claim, and latest status to match, brackets the final exact claim observation with active-executor checks, and raises PublicationLedgerPendingError without a success write on observed drift. The FIFO publisher mutex is the serialization authority for repository-sanctioned writers; GitHub exposes no cross-resource conditional append, so the sequential handshake must not be described as atomic exclusion of arbitrary out-of-band status writers after its final claim observation. A later attempt of the same run may use only reconciliation. A distinct run may recover an unresolved intent exactly two ways: exact cross-run pending takeover — record_pending_takeover binds an uploaded takeover receipt and artifact member durably while the intent is still pending with no statuses and a single unresolved head, claim_pending_takeover claims it exactly once with that durable status still the ledger head and the origin executor proven durably terminal, and mark_resolved resolves with the origin re-verified live — or no-upload in_progress reconciliation, which resolves an already-claimed intent from exact remote marker/version/inventory/readback evidence without a second Kaggle mutation. Executor admission fully paginates up to 1,000 jobs for the exact attempt, requires one uniquely named publisher job, and direct-verifies its ID; the parent run may still be pending while the publisher job is in progress. Executor proof binds GITHUB_WORKFLOW to the active workflow definition and immutable content SHA, while historical status executors validate against their own workflow definitions; a dynamic run-name is not the workflow name. Git's request URL /git/ref/heads/<branch> and canonical response URL /git/refs/heads/<branch> are validated separately. Full-extraction cache restore/save keys are scoped to the current github.run_id, allowing same-run attempt recovery without accepting another run's state. After exact upload or reconciliation, the same publisher resolves metadata head M as the source or one direct non-merge, byte-reproducible dataset-metadata.json-only child with the fixed subject. A child M requires an explicit .github/workflows/ci.yml dispatch using exact {ref, inputs}, a directly verified workflow_dispatch run at M, a fully paginated inventory containing exactly one successful workflow-lint, lint, metadata, typecheck, docs, and test job, direct verification of every job receipt, and a final default-ref recheck at M; an unchanged source skips CI but still writes closeout. The cache and publication artifact copy of logs/kaggle/kaggle-publication-state.json remain secondary reconciliation evidence, not the distributed admission primitive.
• A terminal full extraction writes assured-artifact-manifest.json after export and full-publication scan. Its sorted SHA-256 inventory binds the exact data files to the source commit, chain ID, and validated effective checkpoint coverage; blocked-lane evidence remains separately artifact-bound. The publisher must recompute the assured identity after downloading the exact artifact ID and verifying the archive SHA-256. Kaggle metadata includes this manifest as a resource, is generated from all 261 runtime transform outputs, and publication marker schema v2 repeats its provenance. Publication uses hardlinks when safe, enforces disk/headroom and a hard remote deadline, and never retries local permanent errors such as ENOSPC. Immediately before upload, require the default branch to equal the pinned source; a publication reconciliation execution may also accept its single exact, byte-identical metadata-only publication child through NBADB_KAGGLE_EXPECTED_DEFAULT_HEAD_SHA. After a durable intent reaches verified in_progress, any crash or exception is reconciliation-only and no later execution may call Kaggle until the exact marker/version/inventory/hash readback resolves it. Refresh and push checked-in metadata as the final repository mutation only after exact remote inventory/digest verification and receipt artifacts succeed, then complete the exact metadata-head CI/closeout gate above.

Test Patterns

• 7451 tests collected across 301 test files
• conftest.py autouse fixture calls get_settings.cache_clear() (settings use @lru_cache)
• Use --import-mode=importlib — the nbadb/ root dir shadows src/nbadb/

Commands

# Setup
uv sync                          # Install dependencies
uv sync --extra dev              # Install with dev extras

# Quality
uv run ruff check src/ tests/    # Lint
uv run ruff format src/ tests/   # Format
uv run ty check src/             # Type check
uv run pytest --import-mode=importlib tests/unit  # Unit tests
uv run pytest --import-mode=importlib tests/      # All tests

# Pipeline
uv run nbadb init --season-start 1946             # Full rebuild (resume-safe)
uv run nbadb daily                                # Incremental update
uv run nbadb monthly                              # Recent-season refresh
uv run nbadb backfill run                          # Targeted gap backfill
uv run nbadb full                                 # Fill gaps (deprecated — use backfill)
uv run nbadb status --output-format json          # Machine-readable pipeline status
uv run nbadb scan --fail-on error --report-path artifacts/health/local/scan-report.json  # Hard assurance gate
uv run nbadb export --data-dir data/nbadb       # Export sqlite/duckdb/csv/parquet by default
uv run nbadb extract-completeness --require-full  # CI coverage gate
uv run nbadb extract-completeness --require-full --endpoint-analysis-docs-root /path/to/nba_api  # Full upstream docs/tools + runtime live contract gate
uv run nbadb migrate                              # Create/migrate pipeline tables
uv run nbadb schema                               # Star schema info + lineage
uv run nbadb ask "Who led scoring last season?"   # Catalog-matched read-only Q&A
uv run nbadb ask "..." --strict                   # Non-zero exit on unsupported/error
uv run nbadb chat                                 # Chainlit catalog Q&A UI (injects abs NBADB_DUCKDB_PATH)
uv run nbadb audit-models                          # nba_api endpoint coverage audit
uv run nbadb lint-sql                              # SQLFluff lint on transformer SQL
uv run nbadb metadata --data-dir data/nbadb --output dataset-metadata.json  # Generate Kaggle metadata JSON
uv run nbadb journal-summary                       # Pipeline telemetry for docs admin
uv run nbadb download                             # Pull latest Kaggle dataset
uv run nbadb upload --data-dir data/nbadb -m "Automated update" --verify-remote  # Validate, push, and read back any Kaggle update
uv run nbadb upload --data-dir data/nbadb -m "Full extraction" --full-publication  # Require terminal extraction provenance and exact readback
uv run nbadb upload --data-dir data/nbadb -m "Full extraction" --full-publication --publication-ledger github-deployment --require-durable-intent  # Actions-only crash-durable release

# Docs
uv run nbadb docs-autogen --docs-root docs/content/docs   # Regenerate docs artifacts
uv run python -m nbadb.docs_gen --docs-root docs/content/docs
cd docs && pnpm dev              # Dev server
cd docs && pnpm lint             # Docs lint
cd docs && pnpm format:check     # Docs formatting check

Docs Workflow

• Hand-edit authored docs pages such as cli-reference.mdx, guides, and architecture pages.
• Do not hand-edit generated docs artifacts; regenerate them instead:
  • docs/content/docs/schema/{raw,staging,star}-reference.mdx
  • docs/content/docs/data-dictionary/{raw,staging,star}.mdx
  • docs/content/docs/diagrams/er-auto.mdx
  • docs/content/docs/lineage/lineage-auto.mdx
  • docs/lib/generated/{raw,staging,star}-reference.json
  • docs/lib/generated/{raw,staging,star}-dictionary.json
  • docs/lib/generated/{schema,lineage,schema-coverage,agent-catalog}.json
  • docs/lib/site-metrics.generated.ts
  • docs/table-profile.generated.json when data/nba.duckdb exists
• nbadb docs-autogen prints updated: / unchanged: lines for each generated artifact.
• The docs site lives in docs/ and uses Fumadocs 16 + Next.js 16 via pnpm.

Chat + KB Workflow

• Product mode (Hybrid thin Q&A): nbadb ask / nbadb chat match curated catalog routes — not free-form LLM NL→SQL, sandbox code exec, or multi-provider agents. Routes are explicitly type-capable, year-only, or seasonless. A missing year on a year-capable route executes that route's immutable visible-rowset maximum probe once per request (with type only when supported), never through a shared/persistent cache; an unavailable/invalid probe uses the calendar season with the fixed fallback warning.
• Treat chat/ as the canonical Chainlit app surface; apps/chat/ is retired and should not be reintroduced. The CLI and notebook launchers should fail closed if chat/chainlit_app.py or chat/pyproject.toml are absent.
• Keep reusable chat logic in src/nbadb/chat/ (catalog, runtime, SQL models, memory, artifacts, MCP helpers). nbadb chat must inject absolute NBADB_DUCKDB_PATH / NBADB_DATA_DIR before spawning Chainlit with cwd=chat/; the notebook must anchor configured relative paths to the checkout root before the same cwd transition.
• ReadOnlyGuard + DuckDB read_only + enable_external_access=false remain mandatory on the live path. Catalog sql_templates are the sole SQL source; never interpolate raw user text into SQL.
• Malformed season candidates and unsupported season dimensions must return needs_params without SQL, SQL hash, season values, or season-source metadata. Accept only original adjacent ASCII YYYY-YY; reject season-like Unicode digits/dashes, slash separators, separator whitespace, wrong-width/nonconsecutive suffixes, and mixed valid-plus-malformed prompts before probing, while excluding valid full calendar dates, standalone years, and adjacent alphanumeric identifiers from malformed-season detection. nbadb ask and nbadb chat require an existing warehouse and fail closed when it is missing.
• Preference mutations are serialized with BEGIN IMMEDIATE. Scoped writes/deletes require exact nonempty session ownership, null/malformed ownership never becomes free-for-all, and unscoped administrator updates preserve the existing owner, notes (unless explicitly replaced), and created_at. Existing JSON and SQL updated_at copies are parsed as aware timestamps after authorization, healed from their valid maximum, and advanced monotonically by at least one UTC microsecond with identical stored copies; this preference-only rule must not change trajectory timestamp precision.
• Prefer uv run python -m nbadb.chat.source_inventory so packages do not ship orphan bytecode without matching regular .py sources or revive apps/chat/. The live-tree test must assert both conditions. The gate scans both canonical roots, maps immediate __pycache__ entries with Python's cache contract and legacy adjacent .pyc to sibling sources, reports malformed/escaping/symlinked mappings deterministically, and proves source presence rather than bytecode freshness.
• Treat chat/skills/nba-data-analytics/ as offline analytics helpers, not another Q&A entry point. Its season conversion utility accepts only exact consecutive ASCII YYYY-YY and exact 2YYYY identifiers.
• Treat kb/ as intentional repo content, not scratch output. Rich-stack / multi-provider claims in historical KB notes are not product truth unless Epic R restores that stack.
• The KB is companion material: additive-first, Obsidian-native, and subordinate to repo canon such as README.md, AGENTS.md, docs/, and src/nbadb/.
• Keep project-safe shared vault surfaces tracked under kb/.obsidian/templates/ and kb/.obsidian/snippets/, but do not commit volatile editor-local workspace state if it appears later.

Internal Pipeline Tables (10)

These DuckDB tables track pipeline state — do not modify directly:

• _pipeline_watermarks — Load high-water marks (last_load on output tables after transform+load)
• _extraction_journal — Extraction run history
• _pipeline_metadata — Per-table row counts, schema hashes, and last-updated timestamps
• _pipeline_metrics — Per-extraction-endpoint timing and row counts
• _transform_checkpoints — Resume support for interrupted transforms
• _transform_metrics — Transform execution metrics
• _schema_versions — Column hash snapshots for drift detection
• _schema_version_history — Schema change history
• _lane_metrics — Full-extraction lane timing and success/failure totals
• _staging_chunk_journal — Durable staging-batch chunk hashes for resume-safe extraction persistence

Gotchas

• Import shadowing: nbadb/ directory at repo root shadows src/nbadb/ — always use --import-mode=importlib for pytest
• Settings caching: get_settings() uses @lru_cache — tests must call cache_clear() via autouse fixture
• Chat surface split: chat/ holds the app package and assets, while src/nbadb/chat/ holds shared runtime helpers used by the CLI, notebooks, and focused validation jobs
• Pipeline UI: init, daily, monthly, and full use the Textual TUI when stdout is a TTY and --verbose is not set; CI/non-interactive runs get plain output
• Graceful stop: first Ctrl+C during pipeline commands cancels cleanly and preserves journal/checkpoint state; second Ctrl+C forces exit
• ReadOnlyGuard: Strips SQL comments, normalizes Unicode (NFKC), always wraps queries in LIMIT, uses word-boundary keyword matching
• Season params: season_type param format varies by endpoint; DraftBoard uses season_year (int), PlayoffPicture uses season_id (str "2YYYY")
• Coverage messaging: public trust-floor language should promise full-history preservation only where nba_api exposes it, and should require explicit classification of upstream-unavailable or contract-blocked combinations
• SCD2 dimensions: dim_player and dim_team_history use SCD Type 2 (surrogate keys, valid_from/valid_to/is_current); all other dimensions use Type 1
• Transform naming: fact_box_score_* is team-level, fact_player_game_* is player-level — intentional
• fact_rotation: Depends on [stg_rotation_away, stg_rotation_home] (UNION of both)
• Quality checks: --quality-check on pipeline commands is informational; empty-table warnings do not fail the command
• scan assurance gate: use nbadb scan --fail-on error for hard assurance; run-quality is deprecated and no longer the gate
• full deprecated: use backfill for targeted gap-filling instead
• CI: All GitHub Actions are SHA-pinned, all workflows have permissions blocks, timeout-minutes, and concurrency groups