DEV Community: Ender Yentar

Your backfill is a photograph

Ender Yentar — Wed, 09 Sep 2026 03:24:07 +0000

We were getting ready to make a column NOT NULL. Standard preparation: count the rows that would violate it. The count was not zero.

Seven API keys had no owning organization. That looked like a key problem, so we went looking at how keys are created.

It was not a key problem.

The keys were innocent

All seven belonged to the same account. And that account had no organization either. Widening the query, two accounts were in that state. Both had signed up recently, within the same three week window.

That reframed the question. Keys read organization_id off the user who owns them. If the user has none, every key that user creates is born without one. The keys were downstream of something else.

The gap

Two weeks earlier we had run a migration that created an organization for every existing user and linked them up. It worked. Every row we had at that moment was correct.

Then we shipped it and moved on.

Nobody wired the signup path.

The proof took one command:

grep -rn "Organization(" backend/app/

Zero hits in application code. The only places that constructed an Organization were the migration itself and the tests. So from the moment the migration finished, every new account was born outside the ownership chain, and the data drifted a little further from correct with each signup.

Why nobody noticed for two weeks

Because nothing read the column yet.

The organization was groundwork for permission checks that had not shipped. No request failed. No error was logged. No user saw anything wrong. The system behaved exactly as it had before, because the broken part was not load bearing.

This is the uncomfortable shape of the bug: it produced no symptom, and the absence of a symptom is what let it grow. It only surfaced because we went looking for something else, and it had been quietly blocking that something else the whole time.

We had already written the lesson down

While reading the code, we found this in a helper module, in a comment above a completely different function:

# A backfill is a photograph. A dual write is keeping the photograph current.

Someone on this project learned that lesson, wrote it down, applied it to inboxes, and then did not apply it to users. The knowledge was in the repository. It just was not attached to the thing that needed it.

A backfill answers "what is true right now". It cannot answer "what stays true tomorrow". Those are two different jobs and shipping the first one feels like finishing.

The fix we did not make

The obvious fix is to create the organization at signup. We counted the paths that create a user:

Normal email signup
Google sign-in
Two separate billing paths
Admin seeding
The demo system user

Six call sites. Adding the same three lines to six places would have made today's bug six times more likely, not less, because the seventh path gets written next month by someone who never reads this post.

So we took the guarantee off the caller entirely. It is a session level hook that runs before every flush:

@event.listens_for(Session, "before_flush")
def _attach_personal_organization(session, flush_context, instances):
    for obj in session.new:
        if isinstance(obj, User) and obj.organization_id is None:
            # Assign the relationship, not the foreign key. See below.
            obj.organization = Organization()

It only looks at session.new, so flushing an existing user a second time does not open a second organization. If organization_id is already set, it leaves it alone, which is what lets the backfill migration and the tests do their own thing.

Now it does not matter which path creates the user, or whether that path knows organizations exist.

There is a real cost to this and it is worth saying out loud: grep -rn "Organization(" still finds nothing useful in the application code. We traded an explicit call for an invisible one. The mitigation is signposting: the model module says in its docstring that organizations are born in a before_flush event and that grepping will not find the call. If you use this pattern, write that sign, because the next person will grep first.

The test says the same thing out loud. It does not go through the signup endpoint, because that would only prove the endpoint works:

def test_guarantee_does_not_depend_on_the_caller(client):
    s = SessionLocal()
    u = User(email="raw-insert@test.com", username="rawinsert", password="x", plan="free")
    s.add(u)
    s.commit()
    assert u.organization_id is not None

If someone later moves the guarantee back into the signup handler, this test goes red. That is its entire job.

Two things that bit us on the way

You cannot flush inside before_flush. The first version added the organization, flushed to get its id, and assigned it to the foreign key. SQLAlchemy raises "Session is already flushing" and all four tests fail at once. The fix was to stop thinking in ids: assign the relationship, and let SQLAlchemy work out the insert order and the key.

The backfill migration passed locally and failed on Postgres. It used sa.table(), the lightweight construct, which has no primary key definition, so inserted_primary_key comes back empty and indexing it raises. sa.Table() with an explicit primary key column fixes it.

We never would have seen this locally, because our test suite runs on SQLite and Alembic does not run there at all. A small staging box with the production schema caught it. That is a separate story, and it is the same story.

What we do now

After any backfill, three questions before it counts as shipped:

What creates new rows of this kind, and does that path set the field?
If the answer is "nothing reads it yet", what will read it, and when?
Is the guarantee attached to a caller, or to the data?

The first question is the one we skipped. The third is the one that actually fixed it.

Production is clean now: no accounts without an organization, no keys without an owner, no broken links between the two. The proof was taken inside a transaction that was rolled back, so verifying the fix did not create a row to explain later.

We hit this while building MailFlat, where every API key belongs to an inbox, which belongs to an account, which belongs to an organization. A chain is only as good as the day someone forgets to attach the next link.

SQLite doesn't enforce foreign keys by default, and it cost us three bugs

Ender Yentar — Tue, 08 Sep 2026 21:17:38 +0000

Our test suite runs on SQLite. Production runs on PostgreSQL. That is a common setup and for a long time it felt free: tests were fast, isolated, and green.

Over about three weeks we hit three separate bugs that all came from the same gap. None of them were caught by 1200+ passing tests, because the tests were running on an engine that quietly forgives what production rejects.

Here they are, in the order we found them.

1. Two tests that were red in production and green locally

We were making a schema change, so we ran the suite against a real Postgres instance for once. Two tests failed. On SQLite the same commit was 1241/1241 green.

First reaction: today's change broke something. We stashed it and re-ran against a fresh Postgres database. The same two tests failed. So they had been broken for a while and nobody had seen it.

Both were test bugs, not product bugs:

# To simulate "an inbox owned by someone else"
other_user_id = box.user_id + 999   # a user id that does not exist

SQLite accepts that row. Postgres rejects it with inboxes_user_id_fkey. And the test was weak on top of being broken: it wanted to prove "another person's inbox is not accessible", but what it actually created was "an inbox owned by nobody".

The second one was subtler:

owner = session.query(User).filter_by(is_admin=True).first()   # no ORDER BY

An unordered .first(). SQLite happened to return the admin the test needed. Postgres returned a different admin, and the test failed looking for an inbox that belonged to someone else.

Neither bug was in the product. Both were in tests that had been passing for weeks while measuring the wrong thing.

2. A migration that could not fail locally, because it never ran locally

Later we wrote a data migration to backfill some rows. It looked fine. It passed review. Then we ran it against Postgres and it blew up:

organizations = sa.table("organizations", sa.column("name"), sa.column("region"))
res = conn.execute(organizations.insert().values(...))
org_id = res.inserted_primary_key[0]   # IndexError: tuple index out of range

sa.table() is SQLAlchemy's lightweight table construct. It has no primary key definition, so the driver never asks for the generated id and inserted_primary_key comes back empty. The fix is a real sa.Table() with an explicit primary key column.

The point is not the API detail. The point is this: our test suite does not run Alembic at all. Tests create the schema directly. So migration code the code that runs against production data, once, with no undo was the least tested code in the repository. Not under-tested. Zero lines executed.

We caught this one on a staging box with real Postgres. If we had not had one, we would have found it in production.

3. Deleted rows that came back to life

The third one was the strangest.

We changed a uniqueness check to ask the api_keys table instead of a legacy column:

if session.query(ApiKey).filter_by(inbox_id=inbox.id, revoked=False).first():
    return {"error": "This inbox already has a key."}

Suddenly, brand new inboxes started reporting that they already had a key.

The chain took a while to see:

api_keys.inbox_id has ondelete="CASCADE". In production Postgres, deleting an inbox deletes its key rows.
SQLite does not enforce foreign keys unless you turn them on. So in tests, deleting an inbox left its key rows behind as orphans.
SQLite reuses row ids. A newly created inbox could take the id of a deleted one, and inherit its orphaned key rows.

So a fresh inbox "already had a key" a key belonging to an inbox that had been deleted.

The old code never saw this, because the old check read a column on the inbox itself, and on a new inbox that column is NULL. The bug was not new. It became visible.

The fix, and what it does not fix

One line, in the engine setup:

@event.listens_for(engine, "connect")
def _sqlite_pragmas(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

SQLite ships with foreign key enforcement off for backwards compatibility. It has been that way for years and it is documented, but the default is silence: no warning, no error, just a database that accepts rows Postgres would refuse.

Turning it on made our tests behave like production. Case 3 disappeared. Case 1 would have been caught the day it was written.

But be clear about what one pragma does not do:

It does not make SQLite run your migrations. Case 2 is still invisible locally.
It does not change id allocation. SQLite still reuses ids; Postgres sequences do not.
It does not make the two engines equivalent. It closes one specific gap.

What we changed beyond the pragma

Migrations get a real Postgres before they run anywhere else. Not the full suite, just the migration, against a database that has the production schema. Ours runs on a small staging machine. Twice now, that box caught something that SQLite structurally could not: once the migration above, once a duplicate revision id that had forked the migration tree.

"Green" now has a scope. A green suite means "green on SQLite". That sentence used to be implicit and it was doing a lot of quiet damage. Writing it down changed how we read the result.

The uncomfortable version of the lesson: for three weeks our tests were measuring a different database than the one our users touch. They were not lying. We were reading them as if they said more than they did.

We hit these while building MailFlat, an email API where agents and test suites get real inboxes. The bug journal these came from is kept per-incident, symptom first, which is why three separate weeks turned out to be one story.