Thoughts

Session 40 Retro (ENN Pipeline): Full codebase audit completed. 21 issues fixed across security (hardcoded DB password in 5 scripts), bugs (Hedra FRAMINGS rotation using Close instead of Medium), fragility (shared lib modules for DB/R2/validation/preflight, shot type normalization, manifest validation), and code quality (removed 362 lines of dead code). Key learnings: Railway DATABASE_URL is internal-only (postgres.railway.internal), local scripts need DATABASE_PUBLIC_URL (switchyard.proxy.rlwy.net). Password regeneration is at Database > Config > Regenerate, not Settings tab. TypeScript union narrowing caught stale string comparisons during shot type refactor. 10 existing shorts have Hedra clips with Close framing bug (cosmetic, may not need re-render). Deferred: streaming upload for large files, process-batch DB status tracking.

ENN Pipeline Session 34 (2026-03-24): Phase 9 complete. All database + queue infrastructure built and deployed to Railway Postgres. Migrations applied (003-006): flux_asset_library table, blog_posts updated with long_form_video_id, long_form_videos table created, queue fields (rating_score, queue_position, tiktok_video_id) added to shorts. Key discovery: blog_posts table already existed from the exo-news-network website project with different column names (body vs content, companion_short_id vs short_id, character_slug vs character_id). Pipeline scripts writing blog posts need to use the website's column names. Migration adapted to ALTER TABLE instead of CREATE. Built queue-manage.mjs (assign/check/next/cap commands with --dry-run) and queue-status.mjs (dashboard with backlog counts, per-character breakdown, headroom). Backlog caps: 10 shorts, 3 long-form. Also fixed Gemini date hallucination issue: Gemini outputs wrong dates (2023/2024 instead of 2026) in SESSION and DATE fields. Removed DATE field from both Gem instructions entirely. SESSION now uses topic-slug format (e.g., underground-bases-1) instead of dates. Process-scripts skill generates real session_id from processing date + topic slug. Dave ran first Gemini sessions with new ENN Gem instructions. Results are good quality. Task 8.6 (test run) effectively validated. Status: Phases 0-9 complete. Phase 8 has one remaining task (8.6 formal test) but effectively done since Dave tested the Gems. Next up: Phase 10 (Shorts Production Pipeline Update).

Tat-Tally: Scoring Race Condition Fix (2026-03-16) Fixed a race condition in the update_entry_final_score() Postgres trigger that caused NULL final_score when multiple judges scored the same entry simultaneously. Root cause: The trigger fires per-row on score insert. Each trigger invocation runs in its own transaction and counts existing scores to decide if all judges have finished. When two judges insert at nearly the same time, each transaction only sees its own row plus previously committed rows. Neither sees the other's uncommitted insert, so neither reaches the expected count threshold, and final_score never gets calculated. Fix: Added pg_advisory_xact_lock keyed on the entry_id (first 8 bytes of UUID cast to bigint). This serializes trigger execution per entry. The second judge's trigger waits for the first to commit, then sees the accurate count and calculates the final score. The lock is transaction-scoped (released automatically on commit/rollback), so there's no deadlock risk and no cleanup needed. This pattern (advisory lock in a trigger to serialize concurrent writes that need consistent counts) is worth remembering for any trigger that checks "have all expected rows arrived." Applied to live Supabase DB, updated infrastructure/schema.sql, and created migration file infrastructure/migrations/session48_advisory_lock_scoring.sql. Committed as 5876f1a. Combined with the Session 48 stress test results (Realtime latency p50=161ms/p95=282ms, entry code redemption 5/5, concurrent scoring verified), all known issues from the simulation and stress testing phases are now resolved.