What Happened Critical

At 04:07 UTC on April 3, 2026, a single process injected 10,461 rows into titan.jobs. Every row had created_at stamped at the ingestion minute, but scheduled_at spanning November 30, 2020 through April 2, 2026. All rows carried lead_source = "Imported Default Campaign". Of those, 10,444 were marked job_status = completed with a total invoice_total sum of $6,380,257.

Every downstream query that filtered by created_at BETWEEN '2026-04-03' AND '2026-04-04' saw six years of historical job data compressed into one fake minute. The math engine's weekly_revenue formula and /api/math/validate-revenue endpoint began returning the phantom number. The story API's revenue narrative inherited it. The Sacred HTML's revenue card inherited it. The causal validator's google_ads_to_revenue chain inherited it.

The Timeline

Proof of Injection

From apr3_phantom_sync_investigation.py output:

Why This Is A Pattern, Not A One-Off

After pinpointing the phantom to nexus_titan_migration.py line 249, a blast-radius search was run against /opt/nexus/titan and /opt/nexus/nexus/scripts for every INSERT into a titan table that does not explicitly set created_at. The grep returned 29 files with the same pattern, including two that write to titan.jobs (the phantom source) and seven that write to tables which are currently flagged as stale in the freshness scan.

This means the bug is not a one-off that can be fixed with a single patch. It is a codebase-wide convention where developers (including me, past me) relied on Postgres defaults for created_at instead of explicitly passing the authoritative timestamp from the source system. Every one of these writes is a future phantom waiting to happen.

The Blast Radius Inventory

Test Methodology

Every formula registered in the math engine was hit with a curl against /api/math/compute?formula=NAME&inputs=A,B,C using realistic production numbers pulled from the trusted sources (QuickBooks P&L, Google Ads live API, Big Sale Tracker). The output was captured verbatim along with any anomaly warnings. The goal was to verify that the engine itself is mathematically sound, regardless of whether the inputs fed into it by other systems are contaminated.

Formula Inventory Table

Key Proof: The Math Engine Correctly Fired An Anomaly

When ROAS was computed with $11,187 spend divided by $170,252 QB 30-day revenue (which is all revenue, not ad-attributed), the engine returned ROAS = 0.07 and flagged "ANOMALY: Return on Ad Spend = 0.07 is outside expected range [0.5, 100]". The anomaly is correct. 0.07 means 7 cents of revenue per dollar of ad spend, which would mean the ads are catastrophically failing. The real issue is that QB's $170K is not the right numerator (it includes all revenue, not just ad-attributed). The engine worked exactly as designed. The bug is upstream: there is no "ad-attributed revenue" source wired as the causal_validator yet.

Key Proof: Margin Formula Validates Profitability

When Margin was computed with QuickBooks actuals (income $573,182, COGS $230,906, OpEx $246,124), the engine returned Margin = 16.77%. This is within the expected range of -50% to +60%, and it matches the 13.9% net-income margin when you include Other Expenses ($16,410). The math engine proves that the business is profitable, and the number is defensible against any ST-contaminated claim. This is the number Kalen can trust.

The Missing Guardrails: Zero Metric Bounds Registered

Although the math engine formulas themselves have bounds, the 28 metrics registered in metrics_registry.yaml have zero bounds entries. None of them will fire an out-of-range warning when the underlying data source drifts. This is why the phantom $6.4M figure flowed through without being caught: no metric said "hey, $6.4M for a two-tech shop is outside the expected $20K-$200K range." The permanent fix is to add an expected_range to every metric in the YAML.

Why Stale Tables Are Dangerous

A stale table in the warehouse is not just a missing update. It is a time bomb. When someone eventually runs a backfill or a re-sync to "fix" the staleness, the same created_at = NOW() bug will re-trigger, compressing months of history into a single minute and creating a new phantom. Every stale table in the list below represents a potential future Apr 3 04:07 UTC incident.

The stale tables also break the graph brain and the freshness heatmap. graph_nodes and graph_edges are themselves stale (283h and 193h respectively), which means the graph brain's view of the ecosystem is describing Nexus as it was 11 days ago. seo_keywords_full has 40,847 rows that have been locked for 9 days. kalen_review_criteria has been locked for 8 days, which means Kalen's review framework is not updating.

Stale Table Inventory (>24h since last update)

The Three Numbers

Why The Numbers Diverge

• Big Sale $134K vs QB $170K = $36K gap: QuickBooks includes receivables collected from prior-period work, cash tips, AR catch-up, and any non-sale income (e.g. tool rentals to partner shops). Big Sale Tracker only captures what Ashton records in the daily log, which is a strict "sold today" snapshot. A $9K/week gap is consistent with QB catching trailing collections that Big Sale misses.
• QB $170K vs Clean ST $93K = $77K gap: This is the real operational leak. The anomaly detector has been firing zero_invoice CRITICAL at 76% rate since April 7. Nineteen out of every twenty-five completed jobs in ST are closed with zero invoice_total because the invoice webhook never fires or because the tech never finalized the invoice in the ST app. This is Pipe 1 from the April 8 Battle Plan. Fix the invoice closure workflow and the $77K reappears.
• ST Dirty $6.4M: The phantom. 10,461 historical rows with created_at stamped at Apr 3 04:07 UTC. Already quarantined, do not use.

Reconciliation Verdict

The true 30-day revenue is somewhere between Big Sale's $134K (conservative floor) and QB's $170K (full cash recognition). The ST clean number of $93K is too low because of the zero-invoice closure problem, not because of the phantom. Report Big Sale as the "this week / this month" number and QB as the "profit and cash" number. Never report the ST number until Pipe 1 (invoice closure) is fixed.

Step 1: Fix nexus_titan_migration.py line 249

Step 2: Add warehouse_ingested_at column to titan.jobs

The schema change separates "when ST created this job" from "when our warehouse pulled it in." Both are useful, but they must not be conflated. After this migration, created_at always means ST's authoritative time and never defaults to NOW(). Every query that previously filtered by created_at will continue to work because the semantics are preserved.

Step 3: Patch all 29 INSERT sites systematically

A single patch script (patch_created_at_omission.py) walks through every file in the blast radius inventory. For each file:

1. Parse the INSERT statement column list.
2. If created_at is missing, add it as the last column.
3. Find the VALUES tuple and add a new %s.
4. Find the Python tuple being passed to execute() and add the source-system timestamp (or datetime.now(timezone.utc) if no source-system time exists).
5. Write a backup of the original file before modification.
6. Print a diff summary per file for Robert's review.
7. Never auto-deploy. Always stop at the diff stage.

Step 4: Add a pg trigger as a safety net

Even after the patch, a future developer could write a new INSERT and forget created_at. The safety net is a Postgres trigger that raises an exception if created_at would fall back to DEFAULT NOW() without being explicitly set.

Week 1 (Apr 12 - Apr 18): Code Fix and Schema Migration

• Day 1 (today): Fix nexus_titan_migration.py line 249. Add created_at to the INSERT column list and pass job.get("createdOn") from the ST payload. Commit with reference to this document.
• Day 1: Make the script non-executable (chmod -x) so it can never be run by accident again. Move to scripts/one_time_migrations/.
• Day 2: Add warehouse_ingested_at column to titan.jobs. Run the migration SQL from Step 2 above. Verify via SELECT created_at, warehouse_ingested_at FROM titan.jobs WHERE id IN (SELECT job_id FROM titan.quarantined_jobs LIMIT 10).
• Day 2: Install the enforce_explicit_created_at() trigger. Verify it fires by attempting an INSERT with created_at = NOW() and scheduled_at = 2020-01-01. It should raise.
• Day 3: Build patch_created_at_omission.py that walks the 29-file blast radius and prints the diff. Run in dry-run mode. Robert reviews every diff before applying.
• Day 3: Apply the patch to the top 5 critical files (titan_sync_daemon.py, api/portal.py, api/estimates.py, api/threecx.py, nexus_st_webhook_listener.py). Restart affected services.
• Day 4: Add expected_range to every metric in metrics_registry.yaml. Use realistic bounds based on the last 90 days of data. Revenue metrics get [$20K, $200K] for 7d and [$80K, $800K] for 30d. Spend metrics get [$500, $50K]. Etc.
• Day 5: Rewrite the math engine weekly_revenue formula to accept a pluggable source argument. The default source becomes Big Sale Tracker. ST is wired as a fallback only.
• Day 5: Rewrite the math engine avg_ticket and revenue_per_tech formulas to read from the same pluggable source layer.
• Day 6: Fix the anomaly detector's zero_invoice check to page Ashton in Slack (not just log), since it has been firing CRITICAL for 5 days without escalation.
• Day 7: Write the memory entry and update MEMORY.md. Deploy this HTML to the playbooks directory.

Week 2 (Apr 19 - Apr 25): Reconnect Stale Tables and Ship Sacred v2

• Day 8: Investigate each stale table and restart its ingestion path. graph_nodes and graph_edges first because the graph brain depends on them.
• Day 8: Fix the broken nexus-dashboard-integrity.timer which last ran on Apr 2 and has been "active waiting" ever since.
• Day 9: Reconnect call_logs ingestion. Both 3CX and SMS paths write to it. The 437-hour staleness means both paths are broken.
• Day 9: Reconnect price_book and xact_pricebook. Kalen's estimate workflow depends on fresh pricing.
• Day 10: Rebuild Sacred HTML v2 with the new source hierarchy. Big Sale card on top, QB margin card, Ramp COGS card, Google Ads efficiency card, 3CX demand card, ST counts card at the bottom labeled "operational only".
• Day 11: Wire the causal validator google_ads_to_revenue chain to Big Sale Tracker as the revenue endpoint. Currently it terminates at an ST node.
• Day 12: Walk Kalen through this document. Show him the pinpoint, the fix, and the hierarchy. Get his signoff on Big Sale as the weekly SoT.
• Day 13: Ship the Sacred v2 draft to Stephanie. Keep v1 accessible for rollback.
• Day 14: Post-mortem. What would have caught the phantom on Apr 3? Were the existing protocols enough? Update this document with anything the post-mortem surfaces.

22 APIs Wired

Gaps (APIs Not Yet Wired)

• Ahrefs: Zero files. Robert has access but no ingestion path. Would add backlink intelligence and competitive SEO data.
• Plaid (for QuickBooks): Blocked on Stephanie authorization. Once wired, QB P&L refreshes automatically instead of on manual pull.
• GSC warehouse table: API is wired (13 files) but no titan.gsc_* table exists. Organic rankings are queried live, never cached.
• GA4 warehouse table: Same as GSC. API wired (66 files) but no warehouse table.

💥 Root Cause: Auto-Repair Agent

The nexus_repair_agent.py auto-repair timer injected a 3-line SLACK_ENABLED = False block into 19 Python files at wrong indentation, causing IndentationError cascade across 20 timers. The "self-healing" systems were the disease, not the cure. Error logs: immune system 12,657, repair agent 13,799, sentinel 9,712 errors.

Fix: All 19 files repaired (14 from .bak backups, 5 by removing injected blocks). Auto-repair agent quarantined to /opt/nexus-staging/quarantine/. Permanently disabled.

📜 The NEXUS Treaty (3 Gates)

• Gate 1: Git Mandate — /opt/nexus/.git/ initialized. Pre-commit hook blocks syntax errors. 2 commits baseline the clean state.
• Gate 2: Truth Service — nexus_truth_service.py is the SINGLE source for all revenue numbers. Every script must import from it. No more 8 scripts competing.
• Gate 3: Verification Gate — verify_sacred_math.py checks Sacred v2 against Truth Service before deploy. Blocks phantom numbers, stale values, unrendered templates. 14/14 checks passing.

🛠️ Infrastructure Deployed

• Staging: /opt/nexus-staging/ with scripts, titan, test, quarantine directories
• Deploy Gate: deploy_gate.sh — syntax + safety + diff review before production promotion
• ALCOA+ Test: test_idempotency.py — double-run verification per Google SRE book
• Production Lock: 14 core scripts set to chmod 444 (read-only)
• Quarantine: 5 dangerous scripts isolated (auto_repair, immune_system, self_healer, homeostasis, auto_expansion)
• Null-Revenue Shield: DataUnavailableError + validate_revenue() + validate_input() in Math Engine
• RAG Decontamination: 152 error-pattern chunks purged from pgvector (IndentationError, SyntaxError patterns)
• RUNBOOK: 238-line operations manual at /opt/nexus/RUNBOOK.md

📊 Before vs After