backend

Green fixtures prove nothing: a nested object, camelCase keys, and full datetimes only surfaced once 5,330 real rows hit the pipeline.

TL;DR: A 5,330-row blacklist dump mapped error-free yet left company and date columns empty, because real rows broke three assumptions: nested provider objects, camelCase keys, and full RFC 3339 datetimes. Fixes layered a fallback resolver, one shared field registry, and regex date extraction. All 5,330 rows then ingested cleanly, confirming the lesson: start from row zero of real data.

Indonesian month-name dates kept an event_date column empty. The stdlib locale is not the answer; an explicit month table is.

TL;DR: Date-range queries kept returning empty because Indonesian date strings silently failed ISO parsing. Locale-based fixes proved fragile, so the team chose a simple 13-entry month table with one shared field list in the domain layer. Fifty-three tests plus a live remap of 20 cases filled every date with zero errors.

Two constraints guarded one email identity rule. Dropping the redundant one is not cosmetics; it closes a duplicate-specification trap.

TL;DR: Migration 0005 dropped a redundant composite tenant-email constraint, leaving the global unique email index to enforce email identity. The key insight: two constraints guarding one rule are two specifications, and a forgotten duplicate can resurface during later refactors. Three checks validated the change: clean migration roundtrip, zero autogenerate drift, and a cross-tenant insert probe rejected at the database.

Three enum-like columns were guarded by the app alone. CHECK constraints move the guarantee into the database, and value evolution gets cheap.

TL;DR: DemandScope's Migration 0004 adds CHECK constraints to three enum-like columns that were only validated in the app. Database-level guards block rogue writes that bypass Pydantic, while avoiding native ENUMs that make evolving values painful. Small tables validate instantly, but the team documented a NOT VALID plus VALIDATE pattern to avoid locking once tables grow.

One success number for a whole batch hides partial failure. Per-item created/updated/unchanged/rejected status makes it visible.

TL;DR: The new POST endpoint replaces a single batch status with per-item outcomes plus a summary so partial failures are visible. Guards cap payload size and item count, rate-limit per source, and fail closed with proper status codes including constant-time auth. Each item is marked created, updated, unchanged or rejected via a content hash, with an audit row for every request.

The same company landed twice because the name was spelled two ways. A content hash over a canonical tuple closes that duplication gap.

TL;DR: Duplicate company rows appeared because the pipeline relied on raw text and sender references instead of semantic identity. The fix normalizes company names and builds a SHA-256 hash from core values, enforced by a database unique constraint with upsert logic. This stops same-source duplicates reliably but does not handle cross-source entity merging, which needs separate resolution.

A rebuild turned 20 healthy ledger rows into 77 UNKNOWN. The fix: a recovery-only contract that fills gaps and never rewrites healthy rows.

TL;DR: A rebuild tool corrupted a court-rulings ledger, inflating twenty healthy rows to seventy-seven, mostly tagged UNKNOWN. The cause was structural: per-tab raw files re-parsed as new records, proving raw-is-truth doesn't make re-parses safe. The redesign makes rebuild recovery-only: healthy rows stay verbatim, only missing cases are appended, sub-tab files are skipped, and any UNKNOWN aborts all writes.

Two local guards for scraping pipelines: an AGENTS.md rulebook for coding agents and a per-target SQLite audit log made idempotent with UPSERT.

TL;DR: When a harvest run died, git couldn't trace which commit produced the data—it records file changes, not execution context. The fix was two guards: an AGENTS.md rulebook forbidding writes to harvested data and secrets in commits, plus a per-target SQLite audit log. UPSERT on a unique commit-case-tab key keeps re-runs idempotent, turning provenance recovery into a single query.

Pagination URLs carry rotating session tokens that die within the hour. Drive the app's own search form instead, then flatten parsed values before comparing.

TL;DR: Apps that mint session-bound URLs make deep links unreliable, so drive the search form instead: type the exact document number, verify the detail page, and log the query rather than the link. Normalize parser output by flattening lists and lowercasing strings before status comparisons. Flag mismatches for human review instead of stopping the run.