Why Your Database Upsert Is Saving Zero Rows (And How I Fixed It in JarvisOS)

There is a very specific type of silence in software engineering that fills you with dread. It isn't a red stack trace crashing your server. It isn't a 500 Internal Server Error screaming in your logs. It's the silent, empty array returned by a database query that successfully ran, processed, and saved… absolutely nothing.

This week, while expanding G7, an outreach subsystem inside JarvisOS that generates consent-based prospecting drafts and tracks messaging angles, I ran right into a classic trap: a partial-index upsert mismatch that quietly discarded every single record.

Let’s talk about why these silent failures happen, how database constraints bite us when we move fast, and how we handle data integrity when building real-world production applications from East London, South Africa.

The Trap of the Partial Index Upsert

When you are building autonomous loops or background sync workers, like our new generic corpus worker that indexes 7 different Memoir, Review, and Deal Room sources, idempotency is your best friend. You want to run ingestion jobs repeatedly without spawning duplicate rows.

In PostgreSQL, ON CONFLICT DO UPDATE is the standard tool. But when you introduce a partial index (for instance, indexing only rows where status = 'active'), your upsert target must explicitly mirror that index definition. If your query runner doesn't match the constraint scope, PostgreSQL doesn't throw a tantrum; it simply bypasses the conflict clause, attempts a standard insert, and if error handling is overly permissive upstream, the operation resolves to a quiet, empty dataset.

Here is how we caught and patched it in our TypeScript data layer:

// The culprit: partial index on active prospects, but upsert targeting only the handle
// Result: 0 rows saved, 0 errors thrown.

// The fix: aligning the conflict target explicitly with the schema constraints
const { error } = await supabase
  .from('prospects')
  .upsert(payload, { onConflict: 'handle, status' });

When you are solo-architecting 8 production apps, from VarsityOS helping South African students budget through load shedding to AdminOS automating SME back offices, data bugs have real human costs. If your outreach engine saves zero prospects, your pipeline stalls. If your accounting integration fails to match QW3 Gmail receipts to transactions, your invoicing breaks.

Fixing UI Noise and Metric Drift

Data integrity problems weren't restricted to the database this week. In our chat layer, a frustrating bug caused raw JSON and uncleaned code blocks to leak into assistant responses. We overhauled the system prompts and wrote a backward-compatible markdown rendering sanitizer that cleaned up legacy messages across the board.

We also solved a hilarious physiological bug in the health tracking module: energy is not HRV.

A simple 1-10 daily energy rating was accidentally being cast as milliseconds, triggering a brutal -15 recovery penalty in my OS every morning. My code was treating a tired afternoon check-in like a cardiac anomaly. We decoupled the rating types, streamlined the daily check-in into a single entry form that populates Cycle, Recovery, and Body, and restored sanity to the telemetry.

Engineering for Real Life

Good software development isn't just about clean syntax; it's about building systems that honor human energy. Whether it's trimming our 'Today' view feeds to a maximum of 3 items per source (folding the rest into clean wing links), auto-suggesting consulting time entries from calendar events with a single tap, or bulk-decluttering Gmail spam senders in one recoverable action, every line of code written this week was about subtraction.

Code should get out of your way. If your database is whispering when it should be shouting, check your constraints.