Database constraints vs code: where domain rules belong

Why this gets messy fast

Most teams do not start with a clean split between database constraints and application code. A rule appears in a form check. Later, someone adds the same check in an API handler. Then a migration adds a SQL constraint just to be safe.

That feels fine for a while. Then one version changes and the others do not. The product team decides that canceled accounts can reuse an old username. The app accepts it, but an old unique index still rejects it in production.

The reverse happens too. A developer tightens a database rule after bad records show up, but the app still shows the old error message and still retries requests that can never succeed. Users see random failures. They are not random at all.

Bad data also slips in through side doors. Imports, admin scripts, background jobs, retry workers, and one-off fixes do not always pass through the same validation code as the main app. If a rule lives only in code, one shortcut can undo a lot of careful work.

Teams also waste time arguing about ownership instead of writing the rule clearly. One person says, "business logic belongs in the app." Another says, "the database is the final guard." Both can be right. The real problem is simpler: nobody named the invariant in a way that code, SQL, and tests can share.

Drift starts quietly. A migration lands on Friday, a service change ships on Monday, and the docs stay frozen in a ticket. A month later, the team has three versions of the same rule and no one trusts any of them.

This gets worse as a product grows. More scripts appear. More services touch the same tables. More retries hit edge cases. If the rule is fuzzy, the data will be fuzzy too.

What counts as an invariant

An invariant is a rule that must stay true every time data is stored, updated, or read. If the rule can be false for a while during normal work, it is probably not an invariant. It is a workflow step, a business process, or a side effect.

Write each rule as a plain sentence. "A user email must be unique." "An invoice total cannot be negative." "A subscription cannot belong to a deleted account." Simple sentences expose weak rules fast.

Workflow steps sound different. "Send a welcome email after signup" is not an invariant. The system can still work if that email is delayed or retried. "A paid subscription must have a plan_id" is different. If that rule breaks, the data itself stops making sense.

Separate permanent rules from process steps

A simple test helps: if this rule fails, do you have bad data or just an incomplete process? Bad data points to an invariant. An incomplete process points to application logic, jobs, or event handling.

It also helps to ask who can break the rule and how. A bug in one API route might create a missing status. A bulk import might bypass validation. A manual SQL update might skip a safety check. Once you name the failure path, it becomes easier to decide where protection belongs.

Some rules are local and easy to enforce near the data. Others depend on timing, outside systems, or user actions, so code has to manage them.

Scope matters

Group each rule by scope before you decide where it lives. Some rules affect a single row, like price >= 0. Some affect a whole table, like unique emails or one active subscription per account. Others cross system boundaries and depend on Stripe, an email service, or another API.

Row and table rules usually belong in SQL because every write path hits the same checks. Cross-system rules usually belong in code because the database cannot see the full picture.

A short rule list beats a giant spec. If a team can read ten plain sentences and agree on what must never break, most later arguments disappear.

Rules the database should own

The safest rules live where every write must pass. If a mobile app, admin panel, background job, and import script all touch the same tables, the database is the only place that sees everything.

Start with facts that must stay true for every row. If a user record always needs an email, use NOT NULL. If every public slug must be different, use a UNIQUE index. If a discount must stay between 0 and 100, use a CHECK constraint. These rules are small, strict, and boring. That is exactly why SQL should own them.

Foreign keys belong here too. If an invoice must belong to a customer, or a subscription must point to an existing account, the database should reject orphaned rows. Code checks help, but they miss edge cases under retries, concurrent requests, or old scripts that bypass newer logic.

A simple rule works well here: put identity, ownership, and money safety close to the data. If breaking the rule would corrupt data, charge the wrong amount, or lose track of who owns what, keep it in the database.

That usually means required fields, uniqueness, numeric bounds, plain state checks, and parent-child relationships. Money needs extra care. If your system stores prices, balances, refunds, or credits, the database should block impossible values. A negative quantity might be valid in one table and a bug in another. Decide once, then lock it down.

Ownership rules matter for the same reason. If only one active subscription can exist for a workspace on a given plan, a database constraint is stronger than an app-level promise. Two requests can arrive at the same moment. The app might see "no active subscription" twice. A unique constraint will still stop the second insert.

Rules the application should own

Some rules change too often, depend on too much context, or touch too many moving parts to live safely in SQL. Workflow rules usually belong in code. If the product team might change the rule next week, do not bury it in a trigger.

Drafts, approvals, grace periods, and scheduled actions fit this pattern. A record might start as a draft, move to pending approval, expire after 72 hours, then reopen if someone uploads a missing document. The database can store the status and timestamps. Your application should decide when each step starts and ends.

A rule also belongs in code when you must ask something outside the database before saving the result. Think about checking a payment method, asking a fraud service for a score, or verifying a tax number. SQL should store the answer your service received. It should not own the conversation.

User messages belong in the application too. The database can reject bad data, but it should not decide whether someone sees "Please add a work email" or "Your manager must approve this request first." Those messages change with the product, the screen, and the audience.

A rule is usually app-owned when it depends on time, roles, state changes, external APIs, or several writes in a certain order. The same is true for anything that reaches beyond the database into queues or third-party tools.

Keep multi-step flows in one service layer. If signup creates an account, starts a trial, sends an email, and pushes a job onto a queue, one part of the app should coordinate that work. Retries, logging, and tests become much easier.

The same applies to rules that cross systems. If a change in your app must match a queue consumer, a billing provider, and a CRM update, SQL cannot keep all of that in sync by itself. Put the rule where you can see the whole flow, test it end to end, and change it without a risky schema migration.

A simple test for each rule

Teams often argue about very different things as if they were the same. A tax calculation, an email format check, and a hard "must never happen" data rule do not belong in the same place.

Start with one blunt question: does this rule need to hold for every single write, no matter who sends it? If the answer is yes, the database is usually the safer owner. Unique emails, required foreign keys, positive quantities, and valid status values fit this pattern because bad data should never land in the table.

Then ask who can bypass the app. Many teams think their API protects everything, but data also arrives through admin scripts, one-off fixes, CSV imports, background jobs, and migrations. If any of those paths can write rows, app-only checks leave a gap.

A third question keeps the decision practical: can SQL express the rule clearly, without strange hacks? A simple CHECK, UNIQUE, NOT NULL, FOREIGN KEY, or exclusion rule is usually a good fit. If the rule needs outside services, long workflows, or context from several steps, keep it in application code.

Put the rule in the lowest layer that can enforce it cleanly. That reduces repetition and lowers the chance that one write path forgets the check.

One last step saves a lot of future confusion. For each rule, write a one-line note in your schema docs, migration comment, or engineering notes with the rule, the owner, and the reason. For example: "A user can have only one active subscription - owned by SQL with a partial unique index." Tiny notes like that make drift easier to spot during reviews.

Example: a subscription signup

A signup flow makes the split easy to see. Say a user picks a plan, enters a coupon, and starts a trial. Some rules should live in SQL because they protect the record no matter which app, job, or admin script writes to the table.

The database should reject a subscription row if it has no user_id, no plan_id, or a status outside the allowed set such as pending, trialing, active, or canceled. Foreign keys make sure the user and plan exist. A check constraint keeps status sane. If your product allows only one active subscription per user, a partial unique index can block duplicates even when two requests hit at the same time.

That matters more than many teams expect. One slow payment callback and one impatient double click can create two active rows. App code often misses that race. SQL does not.

Other rules change more often or depend on outside systems. Code should decide whether a trial lasts 7 or 14 days, whether a coupon applies to this plan, and whether the payment provider approved the charge. Those rules use pricing policy, dates, and third-party responses. They belong near the checkout logic, where your team can test and change them without a migration.

A clean flow is simple. Code validates the request, the coupon, and the trial window. Code asks the payment service to create or confirm the charge. SQL inserts or updates the subscription under strict constraints. Then code handles follow-up work such as email, receipts, and billing retries.

Keep that last part outside the main transaction. If the welcome email fails, retry the job. If billing needs another attempt, queue it again. Do not weaken data rules just to keep signup moving. The subscription record should stay correct even when email, webhooks, or background workers have a bad day.

How to stop drift between code and SQL

Drift starts when the same rule gets described three different ways. The app says active_subscription_required, the migration says chk_sub_status, and the doc says "paid users only." A month later, nobody knows whether those are the same rule or three separate rules.

Pick one clear name for each rule and keep it everywhere: code, migrations, tests, and docs. If a user must have a unique email, call it users_email_unique in every place that matters. That small habit saves real time during reviews and incident fixes.

Treat schema changes like product changes

Manual edits in a database feel fast, but they create silent damage. One person adds a check in production, another updates the app later, and now your system has rules that exist in only one place.

Put every constraint change through a migration, even if the change looks tiny. Review migration files with the same care as application code. A bad schema change can block signups, reject valid orders, or let bad data in for weeks before anyone notices.

You do not need a huge process. Give each rule one shared name, change constraints only through migrations, review migration diffs in pull requests, and update the docs in the same change.

Test the app and the database separately

Many teams test only the happy path through the app. That misses a common problem: direct SQL imports, admin scripts, and background jobs can bypass app checks.

Test failure cases from both sides. First, send invalid input through the app and confirm the user gets a clear error. Then try the same bad data with direct SQL and confirm the database blocks it. If both fail for the same reason, the rule is much less likely to drift.

When you replace a weak rule with a stronger one, remove the old check. Old checks pile up fast. They confuse new developers and sometimes reject data for the wrong reason. If a foreign key now protects a relationship, delete the older app-only guard that no longer adds anything.

Teams that stay strict here usually see fewer strange data bugs. They do not rely on memory. They make the rule visible, reviewable, and testable.

Mistakes that create bad data

Bad data rarely starts with one big failure. It usually slips in through small shortcuts that feel harmless at the time. A form checks one rule, an admin tool skips it, a background job forgets it, and now the same record can exist in three different states.

UI validation is the most common trap. It helps users, but it does not protect the database. People import CSV files, call APIs directly, run scripts, and edit records from internal tools. If a rule matters for every row, the database should enforce it.

Teams also create drift when they copy the same rule into every service. One service trims spaces, another does not. One rejects duplicate emails, another allows them during bulk import. After a few months, nobody knows which version is right. Put shared invariants in one place, then let the application handle messaging and workflow around them.

Triggers create a different kind of mess. They can solve real problems, but they are a poor choice for plain checks like "value must be positive" or "status must be one of these four values." A CHECK constraint, FOREIGN KEY, or UNIQUE index is easier to see and easier to test. When a rule is simple, write it where every developer can spot it fast.

Old nullable columns are another quiet source of bad rows. A team decides that every account now needs a billing_country, but the column stays nullable because a migration feels risky. Six months later, half the new code assumes the field is always present, while old paths still write NULL. That split creates bugs that look random.

Temporary exceptions deserve extra suspicion. A migration bypass that never gets removed, a support script that inserts incomplete records, a one-off status value kept "just for now," or an API flag that skips validation in production can all turn into permanent design mistakes.

Startups hit this a lot when they move fast or lean on AI tools to generate code. Speed helps, but loose rules pile up fast. If a temporary hole stays open after the emergency ends, it is no longer temporary.

Quick checks before release

A rule is not ready just because one screen works in development. Release is when other paths hit your data: scripts, imports, retries, admin tools, and concurrent requests. That is where weak rules break.

Start with the bypass test. If someone runs a direct INSERT in the database, can they create a row your app would reject? If the answer is yes, the rule is too easy to dodge. Put that invariant in SQL, or back up the app check with a constraint.

Background jobs need the same scrutiny. Teams often validate user input in the web app, then forget that a worker, migration, or import script writes to the same table later. Those paths do not care about your controller logic. They only care about what the database accepts.

Before release, do a short pass. Try the write outside the normal request flow, such as a script or manual SQL statement. Send the same request twice at nearly the same time and see if duplicates appear. Read the failure message and ask whether another developer would know what broke. Test one valid case that should save cleanly. Test one invalid case that should fail for the exact reason you expect.

Race conditions deserve special attention. If two requests can create the same account, coupon, or subscription at once, app logic alone is usually not enough. Add a unique constraint, or use a transaction pattern that blocks the duplicate before it lands.

Error messages matter more than teams think. "Validation failed" is almost useless at 2 a.m. "Email must be unique" or "end_date must be after start_date" saves time and stops guesswork.

If code, tests, and SQL all reject the same bad data, you are in good shape. If one layer accepts it and another rejects it, fix that mismatch before release. That is how drift begins.

Next steps for your team

Start with the rules that cause real damage when they break. Write down ten of them. Pick the ones that create bad money data, duplicate records, broken ownership, or states you cannot repair by hand in five minutes.

Then sort each rule by how long it should stay true. If a rule must hold for every write, no matter which app, script, admin tool, or import touches the database, put it near the data. That usually means a constraint, foreign key, unique index, check, or transaction rule in SQL.

Keep rules in code when they depend on timing, workflow, or outside systems. Payment gateway checks, email verification, rate limits, approval flows, and feature flags belong in the application because the database cannot see the full situation.

A simple working plan is enough: list the ten rules that hurt most when they fail, mark each one as permanent, workflow-based, or third-party dependent, move the permanent rules into SQL first, leave workflow and outside checks in code, and record the final owner of each rule in one place.

That last part matters more than it seems. A short rule register is enough. One page works. For each rule, note the plain-language statement, where it lives, how you test it, and who changed it last. Review that page whenever you discuss schema changes or new features.

Do not try to move everything at once. Pick two or three painful rules, fix the split, and watch what happens in production. Teams usually see fewer edge-case bugs, fewer support fixes, and less debate during reviews.

If the split still feels fuzzy, an outside review can help before the confusion hardens into tech debt. Oleg Sotnikov at oleg.is works with startups and smaller companies on architecture, infrastructure, and Fractional CTO work, including practical decisions about what belongs in SQL and what should stay in application code.