Databases will refuse to store nonsense if you tell them what nonsense looks like — an order total that cannot be empty, an email that cannot repeat — but applications routinely never say, so a single forgotten check in one code path quietly corrupts production and stays hidden for an average of 19 months; this paper (UC San Diego) notices that the application code has been asserting those rules all along, just in the wrong place, and recovers 210 previously unknown missing constraints from eight real web applications by reading the code for what its authors already believed.
The argument in one glance
-
Problem
Bad data gets written, and nobody notices for a year and a half
Web applications hold production data whose correctness people depend on — orders, payments, accounts. Hardware faults and crashes are largely handled by now, but application bugs and operator mistakes still corrupt databases routinely and are far less studied. The damage is delayed and diffuse: an order with an empty total crashes an admin page months later, two accounts sharing an email lock both users out, and by the time anyone looks, the bug that wrote the bad row is long gone. In the applications studied here, missing rules took an average of 19 months to surface.
-
What people did
Check the data in the application before writing it
The natural defence is to validate in code: before creating a user, query whether that email already exists; before saving an order, confirm the total is set. Every web framework encourages this, the checks live next to the business logic that motivates them, and developers frequently argue that this makes database-level rules redundant. It is the obvious place to put a rule, because it is where you are thinking about the rule.
-
Why it fell short
One check protects one code path, and there are many
A real example from an e-commerce application: email uniqueness was checked when a user signed up and not checked when a registered user changed their email, so duplicates accumulated and logins broke. That is the general shape. Many code paths write the same table, batch scripts write it too, sometimes in another language or by another team, and staff turnover makes it impossible to be sure every site checks. Concurrency breaks the pattern outright, since two requests can both find nothing and both insert. And an administrator at a database console skips the application entirely.
-
This paper's idea
The code already says what the rules are
This paper (UC San Diego — Huang, Shen, Zhong and Zhou) observes that code which queries by a column before inserting is asserting that the column is unique, and code that calls a method on a field without a null check is asserting that the field is never empty. Those beliefs are already written down; they are simply recorded in a place the database cannot see. A static analysis can recover them from usage, compare them against the schema, and report the gap — which found 210 previously unknown missing rules across eight real applications, 75 of them since confirmed by their developers.
Why this problem is worth solving
The mechanism being neglected is simple, and its value comes entirely from where
it sits.
A
relational database can be told rules that any stored row must satisfy. The three
common ones are NOT-NULL, which forbids an empty value in a column; UNIQUE, which
forbids two rows sharing a value; and FOREIGN KEY, which requires a column to
point at a row that actually exists in another table. Declaring one costs a line
in the schema, and every modern web framework provides helpers for doing so. What
makes them qualitatively different from a check written in application code is
position: the constraint sits underneath every possible writer. Application code,
batch jobs, scripts in another language, a maintenance task written by a different
team, an administrator typing into a console — all of them go through the
database, and none of them can go around the rule. A check in code protects the
one path it was written on
The reasons application-level checking fails are structural rather than a matter
of diligence.
Three
independent failures. First, coverage: the same table is written from many places,
and the paper documents a real case where an application checked email uniqueness
when a user registered and simply had no check on the path where a registered user
changed their email, producing duplicate accounts and broken logins that took a
long time to diagnose and longer to repair. Second, CONCURRENCY: the usual pattern
is to query whether a value exists and then insert if it does not, which is a race
— two requests can both see nothing and both insert. A study of applications in
one framework found 13 percent of uniqueness and reference validations vulnerable
this way, the framework documentation warns about it, and wrapping the pair in a
transaction does not necessarily help because production databases typically
default to an isolation level that permits it. Third, the administrative console
bypasses application code altogether
What people did before, and where it broke
- Write validation checks in the application. Every framework encourages this, and the check sits next to the logic that motivates it.
- But it protects one code path and races under load. The paper finds that 87 percent of cases where a database rule was missing also lacked the corresponding check somewhere in the code.
- Declare the constraints in the schema. This is the correct answer and the frameworks all support it.
- But developers do not. Across five studied applications, between 10 and 72 rules per application were missing when the column was created and added much later, 82 percent of them only after an incident.
- Inspect the schema manually and add what is missing. In principle a database expert who also knows the business logic could just go and look.
- But the surface is enormous. Thousands of tables with hundreds of columns each, requiring expertise in both domains, which is tedious and error-prone even for specialists.
- Infer the rules from the production data. If a column is 99.99 percent non-empty, it probably should never be empty.
- But the data is evidence of what has happened, not of what is allowed. A rare path that legitimately writes an empty value may simply not have run yet, and newly created columns — where most missing rules live — have no data at all.
- The gap. The rule was known to somebody at some point, and every method of recovering it was looking somewhere the knowledge had not been recorded.
How you recover a rule nobody wrote down
Reading application code for the beliefs its authors already held
-
Validate the data in application code before writing it
Query whether a value already exists before inserting, or confirm a field is set before saving. This is what applications do today, it is what frameworks encourage, and it is the baseline the whole paper is arguing against as a sole defence.
-
Reconstruct how often rules go missing, from schema history new
Database migration files record every historical change to the schema, so a rule added long after its column was created is visibly an afterthought. Mining that history across five applications quantifies the problem and, more usefully, produces a labelled set of real missing rules to test any detector against.
-
Rule out inferring from the production data, with reasons new
Statistics over stored rows describe what has happened rather than what is permitted, so a rare legitimate write that has not occurred yet produces a false rule. Access to production data is also awkward for privacy reasons, and prior work reported that 95 percent of statistically valid uniqueness rules were wrong.
-
Catalogue the code shapes that assert a rule implicitly new
Querying by a column and inserting only if nothing came back asserts uniqueness. Calling a method on a field without checking for empty asserts it is never empty. Saving another table primary key into a column asserts a reference. Framework query helpers that throw when several rows match assert uniqueness just by being used.
-
Find those shapes with flow analysis rather than pattern matching new
Text search cannot do this, because identifying which table a column belongs to requires following the data. The analysis is flow-sensitive and treats each field of a model class separately, tracing values through the code to determine both the pattern and the column it applies to.
-
Compare the recovered rules against the schema and report the gap new
Every rule the code implies but the schema does not declare is a candidate. Because the database will refuse to apply a rule that existing data violates, a wrong candidate is caught by the migration rather than becoming corruption, which makes a mistaken suggestion cheap.
The idea
Put the rule where everything has to pass, not where you happen to be thinking about it. The case against application-level validation is not that developers are careless, and the paper takes some trouble to establish that. The failure is positional. A table is written by many code paths, and by batch jobs, and sometimes by software in a different language written by a different team, and staff turnover means nobody has the whole picture — so the question is not whether each check is correct but whether every writer has one, and the answer is routinely no. Their example is precise: an application validated email uniqueness when a user registered, and had no check at all on the path where an existing user changed their email. Concurrency breaks even the checks that exist, because query-then-insert is a race that a transaction does not necessarily fix given the isolation levels production databases actually run at. And an administrator at a console goes underneath the application entirely. A database constraint has none of these problems for one reason only: it sits below every writer, so it cannot be routed around. Choosing to enforce a property at the single point all paths must traverse, rather than at each of the many places where the property is relevant, is the same intervene-at-the-chokepoint argument covered elsewhere on this site where a guarantee becomes tractable precisely because it is moved to the one interface nothing can avoid.
The knowledge was written down, just not somewhere the database could read it. What makes the tool possible is a reframing of what it is doing. It is not inferring new facts about the business domain; it is relocating facts the developers already committed to paper. Code that queries by a column and inserts only when nothing comes back is a statement that the column is unique — that is what the code is for. Code that calls a method on a field without first checking for empty is a statement that the field is never empty, since otherwise it would throw. Using a framework helper that raises an exception when several rows match is a uniqueness assertion made by choosing that helper. Saving one table’s primary key into another table’s column is a declaration of a reference. In each case the belief exists, is load-bearing, and is invisible to the database. Recovering an invariant that a program’s structure implies but never states, and then making it explicit and enforceable, is the same the-code-already-knows discipline covered elsewhere on this site where a static analysis reconstructs a protocol from how objects are used rather than from any declaration of it.
Learn it from the data and you will learn the wrong thing, exactly where it matters most. The obvious alternative is to mine production tables: if a column is 99.99 percent non-empty, propose that it must never be empty. The paper rejects this and the reasoning is worth keeping. Data records what has happened, not what is permitted, so a rare but legitimate code path that writes an empty value and has not yet run produces a confidently wrong rule — and prior work found 95 percent of statistically valid uniqueness rules to be false positives. Worse, the approach is blindest precisely where the risk is highest: a newly created table or column has no data to generalise from, and new columns are exactly where missing rules originate. Source code has neither problem, because it encodes the intent rather than a sample of the outcomes. There is a pleasing consequence for the tool’s own errors, too. Because a database refuses to apply a rule that existing rows violate, a wrongly suggested rule fails loudly at migration time rather than silently rejecting valid future data — so if a suggestion can be applied at all, the data already conformed to it. Preferring the artefact that states intent over the artefact that samples behaviour, and designing so that mistakes are caught by the system rather than absorbed by it, is the same check-intent-not-observations instinct covered elsewhere on this site where a specification is mined from what a program declares rather than from traces of what it did.
Recovering one rule from one function
How a uniqueness rule gets found in code and added to a schema
Based on the paper worked examples from open-source e-commerce and team chat applications, with results across eight deployed applications.
Step 1 of 5
Something breaks, months after the cause
An administrator page crashes because an order in the database has an empty total. Three developers spend nine days finding the application bug that produced it. Their fix is not only the bug — they add the not-null rule to the database, in their own words to prevent future weird and hard to reproduce problems. Across the applications studied, such rules took an average of 19 months to surface.
the same pattern appears with duplicate emails locking users out and with a reference stored as a plain integer instead of a foreign key in every case the fix included adding the rule, which is an admission that it should have been there from the start
Why it works
- A database constraint sits underneath every writer of a table → no code path, batch job or console session can route around it → so it holds even when a check is missing from one of the many places that write.
- Code that queries by a column before inserting exists only because someone believed the column unique → that belief is recoverable from the code structure → turning an unstated assumption into a declarable rule.
- Source code encodes intent while production data samples outcomes → a legitimate rare write that has not happened yet cannot mislead the analysis → and new columns with no data at all are still covered.
- Identifying which column a pattern constrains requires following where values came from → flow-sensitive, field-sensitive analysis rather than text matching → which is what makes the table and column of each rule determinable.
- A database rejects any rule that existing rows violate → a wrong suggestion fails visibly at migration time → so the cost of a false positive is a developer’s attention rather than broken production behaviour.
What it costs (trade-offs and scope)
- Roughly a fifth of suggestions are wrong. Precision is 78 percent, and checking them is real work — two students spent about 40 hours on 158 reported rules, though the applications’ own developers were much quicker.
- It misses about a fifth of real omissions. Recall is 79 percent, and of the misses, 71 percent simply exhibit no general pattern, while others need cross-function tracing the analysis does not do.
- Some columns are unconstrainable by construction. A fifth of the missed cases are fields never used in application code at all, placeholders for legacy or future use, which no code-based method can say anything about.
- One language, one framework. Everything is Python on Django, and while the authors note other frameworks offer the same primitives, the patterns themselves are framework-flavoured.
- The analysis is not alias-aware. No false positives from aliasing appeared, but that is an observation about these codebases rather than a guarantee.
The evaluation
Two halves. First a study of five widely deployed applications, reconstructing from schema migration history how often rules were added long after their columns existed, why they were added, and whether the corresponding code checks were present — which both quantifies the problem and produces a labelled set of 117 genuine historical omissions. Then the tool itself, run on eight applications including one commercial product, measured for precision on what it newly reports, recall against that labelled set, and recall against rules already correctly declared as a check that the patterns generalise.
| Property | Validate in application code | Infer rules from production data | Infer rules from application code |
|---|---|---|---|
| What it can see | |||
| Behaviour on a new column | |||
| Accuracy | |||
| Survives concurrency | |||
| Cost of being wrong |
Check yourself
Check your understanding
1. Many developers argue that validating data in application code makes database constraints unnecessary. Predict the three independent reasons the paper gives for why that is wrong.
2. The obvious way to discover missing rules is to look at the data — a column that is 99.99 percent non-empty probably should never be empty. Predict why the paper rejects that.
3. The analysis recovers rules from code patterns. Predict what makes those patterns evidence, rather than guesses about the business domain.
4. The tool reports roughly one wrong suggestion in five. Predict why the paper argues that is acceptable.
Sources
- Haochen Huang, Bingyu Shen, Li Zhong and Yuanyuan Zhou. “Protecting Data Integrity of Web Applications with Database Constraints Inferred from Application Code.” ASPLOS 2023, Volume 2. University of California, San Diego. DOI 10.1145/3575693.3575699. The tool is named CFinder.
- Full text of the paper was used for this explainer, including the motivating real-world incidents and the argument against relying on application-level checks in Section 1, the study of missing constraints reconstructed from schema migration history in Section 2, the comparison of possible discovery approaches and the catalogue of code patterns and analysis design in Section 3, and the precision, recall, false negative breakdown, inspection effort and runtime measurements in Section 4.
- The authors released a reproduction artifact publicly.