sql

A validation that only rejects self-parenting looks sufficient until a re-parent to your own descendant quietly forms a cycle in the category tree.

TL;DR: Moving A under B seemed fine but created a loop because B was already A's child. The old validation only blocked self-parenting, so the fix now walks up ancestors until it hits a root, a missing parent, or the original category. It uses the standard wrapped-error check and limits the walk to ten levels to handle legacy cycles safely.

Shipping nested categories with zero tests worked until the QA checklist asked for proof: the boot env is a contract, and constraint edges need a real MySQL.

TL;DR: Nested categories were live with no test coverage, and even the health check was broken since it missed required env vars. I patched the env setup and added three MySQL-backed integration tests for tricky deletes that mocks would hide. They run with the integration tag and -p 1 to avoid collisions on the shared test database.

Flipping db-push into db-pull is more than reversing arrows: local backs up first, the dump gets inspected before trusted, backtick rules change over SSH.

TL;DR: Reversing the sync direction puts your local database at risk, so the guard sequence has to flip. The script first backs up local data for rollback, then takes a consistent staging dump and checks size, tables and completion before wiping anything. It avoids SSH backtick issues and strictly compares row counts afterward, preferring a hard failure over a half-finished import.

WP_HOME only fools new pages; old URLs live in the database. WP-CLI search-replace with dry-run and skip-columns=guid is the fix.

TL;DR: Moving a WordPress production dump locally means changing WP_HOME and WP_SITEURL won't fix URLs already stored across posts, meta, and serialized options. Raw SQL REPLACE() is risky because it breaks PHP serialized length prefixes and can overflow column limits. Instead, use wp search-replace with a --dry-run first, always skipping the guid column since those permalinks must never change.

A backfilled next_review went stale in three days. The fix: compute derived fields at read time and keep manual overrides.

TL;DR: A hand-run backfill for next_review went stale the moment one new file hit the index, exposing it as a derived field that shouldn't be stored. The real fix computes it at read time, only filling blanks so manual overrides survive. SQLite and PostgreSQL settled this long ago: derive on read, store only for overrides or heavy read performance.

Copying an active SQLite file is not a backup. Lessons from a light backup script: hot backup, verified atomic tar, and layered pruning.

TL;DR: The script backs up SQLite databases using the official .backup API instead of raw cp, since copying active WAL files can yield corrupt snapshots. Archives are staged, verified with tar, then atomically renamed, with flock preventing overlap. Pruning keeps 14 days plus a floor of 3 newest archives, so failures can't silently destroy valid backups.

Hand-typed knowledge entries never reached the VPS. A repo-tracked seed file, upserted on every container start, keeps both databases converged.

TL;DR: Hand-typed knowledge entries lived only in the local database, so the VPS was missing four. The fix is a seed file plus a small seeder running on every container start, using INSERT ... ON DUPLICATE KEY UPDATE keyed on slug.

The time chip and tags in the chat popup were never a CSS problem. Metadata had to travel from SQL up to the DTO before the UI could dress up.

TL;DR: The popup rows looked flat because the API never sent published_at, tags, or categories—the fix started at the database, lifting metadata through repositories, DTOs, and SSE until the frontend had data to render. The existing TimelineRow component was reused across all three popup contexts instead of copying markup. Total cost: seven files, 214 lines added.

One tiny field on the list page nearly cost a query per article. The fix is one batch IN query: two queries per page, not 1 + N.