Checking Data Integrity After UI Operations

The previous lesson backed API assertions with database checks. This lesson does the same for UI flows — the form submissions, button clicks, drag-and-drop interactions a real user performs. The principle is identical (the database is the source of truth), but UI flows tend to touch several tables at once and exercise paths that pure API tests don't cover: special characters, calculations the UI does client-side, default values for fields the user left blank, and the cascading effects of one click on five related rows.

Why UI verification is its own thing

When a user clicks "Sign Up" with Müller as the surname, the data travels: browser → JSON serialization → HTTP → API server → ORM → database. Every hop is a chance for something to go wrong:

• Browser encodes Müller as UTF-8; some old servers expect Latin-1 and the ü becomes ??.
• The form has a hidden field with a default that the API doesn't echo back.
• The UI does the math (subtotal + tax = total) but the server stores total differently.
• A "Remove from cart" click triggers a DELETE that's supposed to cascade — does it?

Verifying the database directly catches these UI-layer bugs. The API alone cannot.

A canonical example — registering a user through the UI

A user fills the registration form and hits Submit. After the success page loads:

SELECT id, name, email, role, is_active, created_at
FROM users
WHERE email = 'newuser@test.com';

Expected: one row, with the values the form sent, not the values the UI happened to display. Five things to assert on:

• name matches what was typed.
• email matches (and has the case the form sent — check whether your UI lowercases or not).
• role is whatever the form picked. If the form had no role selector, the schema's default fired — and that default is part of the contract.
• is_active = TRUE (or whatever your default is).
• created_at is fresh — within seconds of the test's submit time.

Special characters and Unicode

Type Müller in the form. Click submit. Do not trust the UI's display of the saved name — it may render correctly even if the database stores junk. Query directly:

SELECT name FROM users WHERE email = 'unicode@test.com';

Expected: Müller, exactly as typed. If the database returns M??ller or Müller, the encoding broke somewhere on the way in. That's a real, reportable bug — and it can sit in production for months because the UI happily round-trips its own bad data.

Try the same with emojis:

SELECT name FROM users WHERE name LIKE '%🎯%';

Some older databases store emojis as four bytes that overflow a VARCHAR(N) defined for fewer characters. Test it — the bug only shows up under the unusual input.

Verifying calculations

A checkout page shows: Subtotal £100, Tax £20, Total £120. Verify the database agrees:

SELECT id, subtotal, tax, total FROM orders WHERE id = 42;

Expected:

| id | subtotal | tax   | total  |
|----|----------|-------|--------|
| 42 | 100.00   | 20.00 | 120.00 |

Two assertions: the values match the UI, and subtotal + tax = total. If the UI calculates client-side and the server recalculates server-side, the two numbers can drift apart — usually because of rounding rules or tax-by-region logic the client doesn't replicate. The mismatch is exactly the bug worth finding.

Cascading effects — one click, several tables

A single UI action often updates more than one row. Here's the data flow for "place an order from the cart":

A complete verification of "place order" hits all four tables:

-- 1 + 2: the order and its line items
SELECT o.id, o.total, COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 42
GROUP BY o.id, o.total;
 
-- 3: inventory was decremented
SELECT id, name, in_stock FROM products WHERE id IN (1, 2, 3);
 
-- 4: user activity field, if your schema has one
SELECT id, last_order_at FROM users WHERE id = 1;

Three queries, full visibility into what one click did. Run them after the UI flow, compare against expectations, and you've covered the parts of the checkout that no UI assertion would catch.

Default values

When a form leaves an optional field empty, the database fills it from the column default. Verify the default fired correctly:

-- Sign-up form left "role" blank
SELECT role FROM users WHERE email = 'minimal@test.com';
-- Expected: 'tester' (the schema default)

If the application should default differently in this context (admin-created users get role = 'admin', for example), the test is whether the right default fired. The schema default is one possibility; an application-side override is another.

Truncation — the silent bug

If a column is VARCHAR(100) and the user pastes a 500-character name, what happens? Three possible outcomes:

• The database rejects the INSERT — visible failure, easiest to handle.
• The database silently truncates to 100 characters — the user sees a different name from what they typed, and the test won't notice unless it checks.
• The database accepts the full 500 characters because the column is actually TEXT — no problem.

Find out which behaviour your stack has by trying it:

-- After submitting a 500-char name through the UI:
SELECT LENGTH(name), name FROM users WHERE email = 'long-name@test.com';
-- If LENGTH(name) = 100, the value was silently truncated.

Truncation is the kind of bug that survives for years because the UI doesn't surface it. SQL does.

QA use cases

A few patterns testers run after UI flows:

-- 1. After a profile update, confirm only the changed field moved
SELECT id, name, email, updated_at FROM users WHERE id = 1;
 
-- 2. After a "remove from cart" click, confirm the line item is gone
--    AND the order_items count went down by exactly one
SELECT COUNT(*) FROM order_items WHERE order_id = 42;
 
-- 3. After a "cancel order" UI click, verify the cascading effects
SELECT status FROM orders WHERE id = 42;             -- now 'cancelled'
SELECT in_stock FROM products WHERE id IN (1, 2, 3); -- inventory restored?
 
-- 4. Default values fired correctly when the form was minimal
SELECT role, is_active FROM users WHERE email = 'minimal@local';

Each of those is a verification an automated UI test can include after the click. The UI assertion ("success message appeared") confirms the user-facing behaviour. The SQL confirms the data.

⚠️ Common Mistakes

• Verifying only what the UI shows. The UI is good at displaying its own input — even when the input is silently corrupted before storage. Run the SELECT, compare bytes.
• Forgetting cascading rows. A "place order" click that creates orders but no order_items is technically a 200 OK with a half-broken result. Verify all the tables a single UI action should touch.
• Assuming defaults fired. Production apps sometimes override schema defaults from application code. The default in the database may not be the default for that flow. Test the actual outcome, not the assumed one.

🎯 Practice Task

25 minutes. Pretend each scenario is the database state after a UI action.

1. Pretend a user submitted a registration form with name Müller and email unicode@test.com. INSERT that row. Run a SELECT and verify the name returned exactly matches the byte sequence you sent. (Try copy-pasting the result back into a fresh INSERT to see if it survives a round trip.)
2. Pretend a checkout placed an order for user 2 with total £100, then created three line items totalling £100. Add the rows. Write the JOIN-with-SUM query that confirms the recorded total equals the line-item sum.
3. Pretend a user updated their email through a profile form. UPDATE the row. SELECT the user and assert that name, role, and is_active are unchanged from before — only email moved.
4. Try inserting a 250-character name. Is your database's VARCHAR(100) column rejecting, truncating, or accepting it? Find out empirically and write down the answer.
5. Pretend a "cancel order" click set order id 4 to 'cancelled'. UPDATE the row. Verify with a SELECT. Then think: in your real application, what other tables should this UI click have touched? Write the queries that would verify those side-effects.
6. Stretch: intentionally create a UI-vs-DB mismatch — e.g., set orders.total to £999 but leave the line items totalling £100. Now write a single query that finds every order in the table where the recorded total disagrees with the line-item sum. (Preview of the next lesson — but the query is reachable now with what you've learned.)

Next lesson: scaling these one-off checks into systematic data-integrity sweeps — finding duplicates, orphans, and totals that don't add up across the whole database.