Bugs Live at the Seams: Six Classes of Defects at Persistence Boundaries, Learned from HivePlane in Production

Unit tests all green, in-memory storage round-trips perfectly, and the logic itself has no problems at all—but swap in a real PostgreSQL and the system crashes at one boundary after another. This is the core finding from HivePlane v0.2.0's live testing: the bug isn't in the logic, it's at the seam where data crosses the persistence boundary.

Six Classes of Seam Defects: A Taxonomy Table

During live testing, the HivePlane team kept a defect log (D-1 through D-11), and five representative defects happen to cover all six seam types:

Defect	Value Crossing the Seam	Where It Breaks
D-5	Ciphertext stored as bytes	Postgres JSON column rejects binary
D-7	desired_specs rows keyed by id	Second data source triggers primary key conflict
D-8	kill-switch records exist	GET /tools/disabled returns already-enabled tools
D-9	Workload with run records gets deleted	Raw foreign key violation returns 500 instead of 409
#573	Backup manifest pinned to alembic_head=0034	Actual schema head is 0035, restoring the wrong version

This table is itself the taxonomy: the root cause of every defect is not an algorithm or business logic, but a value whose form changes at the boundary.

When bytes Collide with a JSON Column

D-5 is the cleanest case. The encrypted key vault stores ciphertext as bytes. In in-memory storage, it round-trips perfectly; every unit test passes. But the corresponding Postgres column is JSON, and bytes cannot be serialized to JSON. The key vault crashes right at the persistence boundary—the very spot the in-memory tests never reached.

The fix isn't in the model layer but at the boundary: base64-encode the ciphertext when writing to Postgres and decode it on read. The model keeps bytes unchanged; the seam learns to translate. That unit test using in-memory storage still passes—and still proves nothing about the seam.

Practical tip: In-memory storage and Postgres share an interface, not a serialization protocol. A value that round-trips in memory can absolutely die at a JSON column. Trace how the value crosses the boundary, because that boundary is exactly what in-memory tests will never cross.

Background: Why can't a JSON column hold bytes?

JSON is a text format, and its spec (RFC 8259) defines only six data types: string, number, boolean, null, array, and object. Binary data isn't among them, so bytes cannot be serialized directly to JSON. PostgreSQL's json and jsonb columns follow the same constraint—they accept valid JSON text, and inserting bytes triggers a type error outright. To store binary data in a JSON column, the common approach is to encode the bytes into an ASCII string with base64 first, then decode on read. That's the fix logic for D-5: bytes itself is fine; the problem is that it must cross a boundary that only accepts a text contract.

A notable alternative is PostgreSQL's bytea type, designed specifically for binary and able to store bytes as-is. But the scenario here chose to keep the JSON column and base64-encode the ciphertext, which suggests the schema choice was already settled and changing the column type would have a much wider blast radius—which is exactly why "translate at the seam" is more pragmatic than "change the model."

The Second Data Source Breaks Through the First

D-7 is a confusion between id and uniqueness constraints. The reconcile loader builds desired_specs keyed by id. Everything works fine with a single data source; but when a second data source applies the same desired state, a primary key conflict erupts—because the row already exists, and that primary key assumes there's only one data source. The uniqueness constraint and the primary key are the same field, yet they carry different semantics.

The fix is a composite key with an idempotent upsert: (source_id, workload_id). The second data source produces a second row instead of a collision. This bug only surfaces in the dual-data-source scenario, which explains why single-source live testing never catches it.

Background: Primary keys, uniqueness constraints, and idempotent upserts

In SQL, both primary keys and uniqueness constraints prevent duplicates, but their semantics differ: a primary key uniquely identifies a row, while a uniqueness constraint constrains the combination of a specific set of business fields. D-7's crux is that desired_specs treats id both as the row's identifier and as the business guarantee that "one workload should have exactly one desired state." Two semantics are crammed into one field, so the legitimate duplicate introduced by the second data source immediately becomes a primary key conflict.

The composite key (source_id, workload_id) separates the two semantics: row uniqueness is guaranteed by both columns together, and each data source holds its own desired-state row. Combined with an idempotent upsert (PostgreSQL's INSERT ... ON CONFLICT ... DO UPDATE), re-applying the same desired state only updates rather than inserts, satisfying both correctness and replayability. This pattern is practically standard in event-driven reconcile loops.

The Endpoint Reports Records, Not State

D-8 is a state-versus-record confusion that still feels absurd in hindsight. The list returned by GET /tools/disabled actually includes already-enabled tools. The kill-switch record does exist in the table, but the tool isn't actually disabled. The endpoint reports the record itself, not the current state. A "disabled list" that contains enabled tools is worse than no list at all—it trains operators to ignore it outright.

The fix: the endpoint reports state, derived from the record combined with the tool's current status, rather than reading the record alone. A disable record that was later revoked is history, not a current fact.

Foreign Key Violations Return 500

D-9 is the database giving the API design a lesson. When deleting a workload that has run records, the endpoint returns 500—a raw foreign key violation thrown as an internal error. The caller receives a server error, and the operator gets no explanation.

The fix is to return 409 with "workload has N run records; delete the run records first or cascade the delete." A constraint violation is a user-facing condition, not a server fault. The seam between the database and the API is exactly where 500 becomes 409.

Background: "Whose fault is it?" in HTTP status codes

HTTP 4xx means the client's request has a problem; 5xx means the server has failed. A foreign key constraint violation is an integrity error at the database layer, but from the API's perspective, it's "you tried to delete an entity that's still referenced"—a condition the caller can understand and correct, which falls under 4xx. In this specific scenario, 409 Conflict indicates the request conflicts with the current state of the resource, more apt than 400 Bad Request, because it implies a dependency between resources rather than a malformed request.

Bubbling raw database exceptions straight up as 500 is a common seam failure: the database's "violation" and the API's "fault" are two different vocabularies, with a missing layer of translation in between. The mature approach is to catch the specific exception type in the persistence layer (e.g., psycopg's ForeignKeyViolation), map it to a domain error, and let the API layer render it into an appropriate status code and readable message.

The Manifest Lies About the Schema Head

#573 is a potentially catastrophic defect. A backup manifest pins alembic_head to 0034, while the live schema head is already 0035. The restore operation succeeds—because the manifest claims the schema matches—but the restore target is a database that's a full migration version ahead. The restore process verifies the manifest's claim, not the world's truth.

The fix: during restore, validate the actual schema head against the manifest and refuse if they don't match. A manifest that lies about its own head is a backup that restores you to the wrong version of the system.

Background: Alembic and the schema head

Alembic is the migration tool in the SQLAlchemy ecosystem. Each migration script has its own version identifier, forming a directed migration chain whose endpoint is called the head. alembic_head records "the last migration currently applied to the database." When restoring a backup, the database's actual head must match the head written in the backup manifest; otherwise the data may not match the schema the code assumes—for instance, a column added later simply doesn't exist in the old backup.

What makes #573 dangerous is that the manifest only validated its own claim, never reading the alembic_version table in the database. Once the claim separates from the fact, the restore "successfully" drags the system back to the wrong migration version, and every subsequent code path that depends on the new schema fails at runtime in hard-to-diagnose ways. The correct approach is to query the actual head before restore and compare it against the manifest; if they don't match, refuse, so the error surfaces at the earliest, most controllable moment.

Engineering Lessons Learned from the Seams

The HivePlane team summarized six types of seams that need to be traced one by one:

• Binary vs JSON: bytes passes in memory but not through a JSON column
• Global key vs tenant scope: single-tenant tests mask multi-tenant conflicts
• id vs uniqueness: when the same field carries two semantics, multi-data-source scenarios inevitably collide
• State vs record: a record is history, a state is the current fact
• Foreign key vs 409: a constraint violation is a user condition, not a server fault
• Manifest head vs schema head: backups must verify actual state, not their own claims

The core conclusion: unit tests that stop before the seam will pass, and the bug lives one boundary further out. What truly catches these problems is Postgres-gated tests—the ones in CI that actually connect to a database. HivePlane v0.2.0's live testing enforced a zero-skip policy against the real stack (including real PostgreSQL), achieving 36/36 scenarios, 67/67 container tests, and the defect log D-1 through D-11 is itself the evidence: the seam, not the logic, is where the real work lies.

To verify this, the method is simple: pick a value in your system and trace it from the model to the Postgres column to the API response. Where does it change form? That's where your next bug is hiding—and that's exactly where your unit tests stopped walking.

One-line Summary

HivePlane's live testing reveals: bugs mostly hide at the seams where data crosses persistence boundaries, not in business logic.

Paragraph Summary

HivePlane v0.2.0's live testing found that unit tests were all green and in-memory storage round-tripped perfectly, but once a real PostgreSQL was swapped in, the system crashed repeatedly at boundaries—the bug isn't in the logic, but at the seam where data crosses the persistence boundary. Through the defect log D-1 through D-11, the team distilled six classes of seam defects: bytes that can't be written to a JSON column, id and uniqueness-constraint semantics confused into multi-data-source primary key conflicts, endpoints reporting records rather than current state, foreign key violations surfaced as 500 instead of 409, and backup manifests lying about the schema head, causing restores of the wrong version. The conclusion: in-memory storage and Postgres share an interface but not a serialization protocol, unit tests that stop before the seam are doomed to miss these issues, and only real integration tests gated on Postgres can catch them.