The Foreign Key That Broke the Promise

The Invariant and the Schema Disagreed

The architecture document said it plainly: saved articles are permanent, and the retention function is the only thing that deletes an article. The retention function was written to match — careful, explicit, sparing exactly the right rows. The schema said something else. The article table's reference to its source carried ON DELETE CASCADE.

Nothing connected the two facts until someone asked what happens when a shelf is deleted. Deleting a shelf deletes the sources no other shelf holds — reasonable, and something the settings screen does on the reader's behalf. The cascade then took those sources' articles with them. Saved ones included. The careful retention function was never called.

Prove It Before You Fix It

The temptation with a bug like this is to change the constraint immediately, because the fix is obvious. Reproducing it first is worth the ten minutes, for a reason that is not about confidence in the diagnosis: a reproduction tells you the size of the problem, and the size decides what else has to change.

On a temporary copy of the database, one source deletion emptied the archived shelf. That result is what surfaced the second half of the bug — because once articles could survive without a source, the query that renders them had to survive that too.

The Second Half: An Inner Join Hides the Rescue

Changing the constraint to SET NULL means a surviving article can have no source at all. The shelf query joined articles to sources to get the outlet label — an inner join, which drops rows with no match. So the articles rescued from the cascade would have disappeared from every shelf anyway: the same failure, one layer up, and much harder to notice because the rows would still be sitting in the table looking fine.

The join had to become a left join with a placeholder label. Constraint and query are one change. Fixing either alone leaves the promise broken while making it look repaired, which is worse than the original bug.

Detect From the World, Not From a Flag

SQLite cannot alter a constraint in place, so the repair is the documented table rebuild — create, copy, drop, rename. The interesting decision is how a running installation knows it needs one. A schema-version flag would work until someone's flag disagreed with their actual schema. Reading the live foreign-key definition and rebuilding only if the cascade is genuinely present makes the migration idempotent and self-verifying: it asks the database what is true instead of asking a number what should be true.

An invariant enforced in one code path is not enforced. The question is never "does my function respect this rule?" but "can anything reach these rows without going through my function?" — and constraints, cascades, and triggers are code paths that never appear in a search for the word delete.

Code

The constraint and the query are one fix, not two·python

def _detach_articles_from_source_cascade(con) -> None:
    """Rebuild `articles` so deleting a source can never delete one.

    The original FK was ON DELETE CASCADE, which quietly made the
    permanence invariant a lie: deleting a shelf deletes the sources no
    other shelf holds, and the cascade took the saved articles with them
    -- the one thing the product promises never to lose.
    """
    # Detect from the LIVE definition, not from a version flag: the
    # database is asked what is true rather than told what should be.
    fks = con.execute("PRAGMA foreign_key_list(articles)").fetchall()
    if not any(r["from"] == "source_id"
               and str(r["on_delete"]).upper() == "CASCADE"
               for r in fks):
        return                      # already correct; idempotent

    con.commit()                    # PRAGMA is a no-op inside a txn
    con.execute("PRAGMA foreign_keys=OFF")
    try:
        con.execute("BEGIN")
        con.execute("""CREATE TABLE articles_rebuild (
            id        INTEGER PRIMARY KEY,
            -- SET NULL, never CASCADE: an article's permanence must not
            -- depend on the plumbing that delivered it.
            source_id INTEGER REFERENCES sources(id) ON DELETE SET NULL,
            ...
        )""")
        con.execute("INSERT INTO articles_rebuild SELECT * FROM articles")
        con.execute("DROP TABLE articles")
        con.execute("ALTER TABLE articles_rebuild RENAME TO articles")
        con.commit()
    finally:
        con.execute("PRAGMA foreign_keys=ON")


# The other half of the same fix. An INNER join here would drop exactly
# the orphans the rebuild just rescued -- the same disappearance, one
# layer up, and far harder to see.
_ARTICLE_SELECT = """
    SELECT a.*, COALESCE(s.name, '(source removed)') AS source_name
      FROM articles a
      LEFT JOIN sources s ON s.id = a.source_id
"""

Exercise

Write down one durability promise your system makes. Then list every foreign key that points at the table holding the protected rows, and read the ON DELETE clause of each. For any cascade you find, construct the parent deletion that would reach your protected rows, and run it against a throwaway copy. Finally — and this is the step people skip — check whether the read path would still return the rows if the parent were null.

Hint

The last step is where the second bug lives. Changing a cascade to SET NULL creates a row shape the queries have never seen: a child with no parent. Any inner join, any NOT NULL assumption in the application, and any code doing parent.name without a guard will now drop or crash on exactly the rows you just protected.

The Invariant and the Schema Disagreed

Prove It Before You Fix It

The Second Half: An Inner Join Hides the Rescue

Detect From the World, Not From a Flag

Code

External links

Exercise

Progress

Comments 0