A structured document pipeline (user requirements → PDR → plan → expand → implement) turns vague product conversations into executable phase plans. The pipeline's value isn't the documents themselves — it's forcing decisions at the right time and preventing implementation from starting before the de...
DuckDB's `executemany` with parameterized INSERT statements can hang indefinitely at scale (10K+ rows). Replacing it with a PyArrow table and `INSERT INTO ... SELECT * FROM tbl` completes the same work in under a second. When DuckDB is your warehouse, bulk writes should go through its columnar inges...
When a database migration creates a table that new code writes to, the migration must be applied before the code runs — not just before the next CLI invocation. If the code path that triggers the write doesn't call `apply_migrations()`, the table won't exist at runtime, even though the migration fil...
When an embedded database (DuckDB, SQLite) serves both a batch pipeline and an interactive web app, the web layer should open the database in read-only mode. This avoids writer-lock conflicts entirely and makes the architecture self-documenting: the web app *cannot* mutate the warehouse, by construc...
When a CLI pipeline and a web API need the same data, import the query functions directly rather than duplicating SQL. Add the serialization layer (Pydantic models, JSON responses) at the API boundary, not in the query module. The query module returns plain Python objects (dataclasses, dicts, tuples...
The vision tagging pipeline uses Qwen2.5-VL (a 7B-parameter vision-language model) to classify image attributes. Running the real model requires a GPU, takes seconds per image, and produces non-deterministic outputs. The full pipeline — config loading, tagging, derived label computation, DB persiste...
When serving DuckDB through a multi-threaded web framework (FastAPI/uvicorn), never share a single connection object across concurrent request handlers. Instead, call `conn.cursor()` to create a per-request cursor. DuckDB's Python driver does not support concurrent queries on the same connection fro...
When a dashboard metric can be computed either from a mutable state flag or from an immutable record table, always derive it from the immutable source. Mutable flags reflect the *current* state, which may not be the state your metric is trying to describe. Immutable fact tables preserve the *histori...
Multiple pipeline stages in Artemis started with per-row INSERT or UPDATE patterns that worked fine during development (5-10 rows) but became bottlenecks at full scale (12,000+ rows). The per-row pattern appeared in three places:
The original thumbnail downloader processed 12,217 images sequentially. Each download created a new `httpx.Client` instance, which meant a fresh TCP connection and TLS handshake for every single request — all to the same Cloudflare R2 CDN endpoint. At 0.1s rate limiting plus ~50-200ms connection ove...
When investigating why multimodal clustering produced zero results, the breakthrough came from a simple query:
While developing the concurrent thumbnail downloader, the DuckDB warehouse file (`warehouse.duckdb`) became locked by the download process. Any attempt to check progress, run `artemis-pipeline status`, or open a second connection failed with:
Python scripts in the Artemis project span multiple roles: XML/JSON migration, schema validation, metadata enrichment, test harnesses, and lesson harvesting. Without a consistent style standard, each script drifts toward the author's (or AI assistant's) habits — camelCase here, inconsistent indentat...
The thumbnail download process was killed multiple times during development — once to change the rate limit, once to adjust the timeout, once at the user's request. Each time, the question was: how much progress was lost? Can we pick up where we left off?
When stripping a codebase down to a subset of its functionality, remove in dependency order — packages first, then CLI registrations, then migrations, then dependencies, then tests, then deployment artifacts. Each commit should leave the system runnable, not just compilable.
When a data pipeline has multiple interacting failure modes, writing a design document that catalogs all errors before fixing any of them produces better fixes than addressing errors one at a time. The design doc reveals which failures share root causes and which fixes would conflict.
Migrating an existing multi-page site to a design system is a page-by-page operation, not a big-bang rewrite. The design system (tokens + components) must be complete and proven on one reference page before touching others. The migration ends with deleting the old stylesheets — if the old CSS files...
When migrating a data format (XML to JSON) that feeds a rendering pipeline, the only way to prove the migration is correct is to run both formats through the pipeline and compare the outputs field-by-field. Unit tests of the new loader are necessary but insufficient — they prove the new code works,...
When migrating a live data format (XML to JSON), the key risk is not the conversion itself — it's proving that the new format produces identical behavior. The migration succeeded because the conversion was treated as a pipeline problem (convert, validate, prove equivalence) rather than a rewrite.
When game content is authored by designers in spreadsheets, build a one-way generator script that converts the spreadsheet into schema-validated data files. The spreadsheet stays authoritative; the generated files are artifacts. This separates content authoring from code and catches errors at genera...
Government data sources contain artifacts of their internal production processes — temp files in archives, renamed columns between releases, duplicate hierarchical rows, suppressed values that look like nulls but carry legal meaning, and CDN configurations that reject non-browser HTTP clients. Defen...
Data pipelines fail — downloads timeout, parsers hit unexpected formats, database connections drop. Idempotency (running the same operation twice produces the same result as running it once) must be designed into every layer: delete-before-insert for facts, check-before-insert for dimensions, and gr...
Once a warehouse holds multiple vintages of the same dataset, every query must explicitly decide whether it wants the latest snapshot or all history. Forgetting this decision produces silent data quality bugs — duplicate rows, empty columns, or misleading percentages — that look correct at the SQL l...
When building an analytical warehouse from multiple federal data products, the single most important architectural decision is identifying the stable external key that connects them. For labor data, that key is the Standard Occupational Classification (SOC) code — every design decision flows from tr...
When loading multiple vintages of the same dataset, dimension tables must deduplicate on business key alone — not on business key plus source release. Including the source release in dimension lookups gives the same real-world entity different surrogate keys in each vintage, making cross-vintage joi...
When a web framework dispatches synchronous endpoint handlers to a thread pool, a shared database connection will produce intermittent wrong results — not errors, but silently incorrect data. The fix is per-thread connections via `threading.local()`, with a global override path for test injection.