> ## Content Index > Fetch the complete content index at: https://bitstales.eu/llms.txt > Use this file to discover other available public pages before exploring further. # Ottavio's inheritance: why application logic doesn't belong in the database - URL: https://bitstales.eu/en/business-logic-in-database-inheritance/ - Published: 2025-09-25T17:15:00.000Z - Updated: 2025-09-25T17:15:00.000Z - Description: You inherit a system with 412 stored procedures, 97 triggers and a table that sends emails. A tour of the house: the UPDATE that says "1" and lies, the promo you can only test on Mondays, per-core licences, the impossible migration. And the foundations that do stay in the database. - Author: Vittorio - Tags: Databases, Software Engineering, Architecture, #en *Thursday, 25 September 2025, 9:30 a.m. PostgreSQL 18 comes out today. You, on the other hand, are receiving an inheritance.* Meeting room, third floor of Pastificio Bortolin, a pasta maker. The CTO opens a Word document and clears her throat. The file is called `HANDOVER_final_FINAL(2).docx` and it's the last will and testament of Ottavio. Ottavio wrote the company's management system between 2004 and 2019 and now grows bonsai trees on the Italian Riviera. > "I, Ottavio, being of sound mind (more or less), leave to the new developer the GESTIX system, with all of its 412 stored procedures, its 97 triggers and the `EMAILS_TO_SEND` table, which must never be emptied. There isn't much Java, don't worry: **all the logic is in the database**. It's safer there." The CTO closes her laptop. "Congratulations," she says. "It's all yours." You've just inherited a house. Lovely façade, great location. Inside, though, every room hides a surprise, and the walls move when you're not looking. Let's take the tour. ## The hallway: the UPDATE that says "1" and lies 10:15 a.m. First ticket of your new life: "Change the price of rigatoni from 1.80 to 2.10". Easy. One line of SQL, one minute of work, then coffee. ```sql UPDATE products SET price = 2.10 WHERE id = 1; -- UPDATE 1 ``` `UPDATE 1`. One row. Like when you say "I'll just do one quick thing" and get home at nine in the evening. At 10:40 Trattoria Da Rina phones, furious. Monday's quote said 180 euros and the invoice says 210\. You go and look at what happened and find three triggers, written in three different years, passing the baton like a relay team: ```sql -- 2009, Ottavio: "keeps open orders in line with the price list" CREATE FUNCTION align_lines() RETURNS trigger AS $$ BEGIN UPDATE lines l SET price = NEW.price FROM orders o WHERE o.id = l.order_id AND o.status = 'open' AND l.product_id = NEW.id; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER trg_price_list AFTER UPDATE OF price ON products FOR EACH ROW EXECUTE FUNCTION align_lines(); -- 2011, Ottavio: "temporary" CREATE FUNCTION recompute_total() RETURNS trigger AS $$ BEGIN UPDATE orders SET total = (SELECT sum(qty * price) FROM lines WHERE order_id = NEW.order_id) WHERE id = NEW.order_id; RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER trg_total AFTER UPDATE ON lines FOR EACH ROW EXECUTE FUNCTION recompute_total(); -- 2014, Ottavio: "DO NOT TOUCH" CREATE FUNCTION write_history() RETURNS trigger AS $$ BEGIN INSERT INTO history(what) VALUES ('order ' || NEW.id || ' for ' || NEW.customer || ' is now ' || NEW.total); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER trg_history AFTER UPDATE OF total ON orders FOR EACH ROW EXECUTE FUNCTION write_history(); ``` You replay it on a copy of the database (PostgreSQL 17, in Docker) to see what that `UPDATE 1` really did: ``` id | customer | status | total -----+-------------------+---------+-------- 101 | Trattoria Da Rina | open | 210.00 102 | Company Canteen | open | 420.00 103 | Bar Centrale | shipped | 90.00 what ----------------------------------------------- order 101 for Trattoria Da Rina is now 210.00 order 102 for Company Canteen is now 420.00 ``` You touched **one** row. The database changed seven, across four tables, and rewrote two orders already confirmed to customers. None of these effects is visible in the application code: opening the Java files is pointless, because the rule "open orders follow the price list" doesn't live there. It lives in a trigger, which lives in a schema, which lives on a server, which lives in the room nobody looks in. That's the first problem with logic in the database: **it's invisible**. Application code you can read, search and follow in your IDE. A trigger just fires, like the neighbour's toilet flushing at 3 a.m.: you know something happened, but not where. ## The kitchen: the table that sends emails 11:20 a.m. In the kitchen you find the famous `EMAILS_TO_SEND`. Here's how it works: a trigger on orders prepares a "Thank you for your order!" email, and a procedure sends it straight from the database. The problem is that an email **can't be undone with a ROLLBACK**. If the transaction fails after sending (a stock check, a payment error), the order disappears but the email is already in the customer's inbox. So the Company Canteen receives a warm thank-you for an order that doesn't exist. The next day they call to ask when their 200 kg of rigatoni will arrive. Next to it there's another procedure that, to work out shipping, calls the courier's web service. From inside a transaction. With rows locked. When the courier answers in 200 milliseconds, all is well. When it takes 30 seconds, every other order queues up behind those locks and the system "is slow". It is: it's waiting for the courier, with the kitchen door locked. ## The living room: the promo you can only test on a Monday 12:00\. In the living room, on the sofa, sits the discount rule. Here it is, exactly as it runs in production: ```sql -- 2012: volume discount. 2013: gold customers. 2016: "Monday promo, we'll remove it later" CREATE FUNCTION compute_discount() RETURNS trigger AS $$ DECLARE t text; BEGIN SELECT tier INTO t FROM customers WHERE id = NEW.customer_id; NEW.discount := CASE WHEN NEW.subtotal >= 100 THEN 0.10 ELSE 0 END + CASE WHEN t = 'gold' THEN 0.05 ELSE 0 END; IF extract(isodow FROM now()) = 1 THEN NEW.discount := NEW.discount + 0.02; END IF; NEW.total := round(NEW.subtotal * (1 - NEW.discount), 2); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER trg_discount BEFORE INSERT ON invoices FOR EACH ROW EXECUTE FUNCTION compute_discount(); ``` It works: a 200-euro invoice for a gold customer, on a Thursday, comes out with a 0.15 discount and a total of 170.00\. Now try writing a test for it. - You need a running database with the `customers` and `invoices` tables, plus everything they depend on (in GESTIX, that's forty tables). - To insert an invoice you need a customer, who needs a price list, which needs a sales agent, who needs a region… - And to test the Monday promo… you wait until Monday. The trigger reads `now()`, the server clock. Or you move the production server's clock, in which case you'd better update your CV. Now the **same rule**, moved into the application. Python here, but any language will do: ```python from datetime import date from decimal import Decimal MONDAY = 0 def discount(subtotal: Decimal, tier: str, day: date) -> Decimal: d = Decimal("0") if subtotal >= 100: d += Decimal("0.10") # volume discount (2012) if tier == "gold": d += Decimal("0.05") # gold customers (2013) if day.weekday() == MONDAY: d += Decimal("0.02") # Monday promo (2016, "we'll remove it later") return d def total(subtotal: Decimal, tier: str, day: date) -> Decimal: return (subtotal * (1 - discount(subtotal, tier, day))).quantize(Decimal("0.01")) ``` And at last the tests: no database, no container, no waiting for Monday: ```python from datetime import date from decimal import Decimal as D from pricing import total THURSDAY = date(2025, 9, 25) MONDAY = date(2025, 9, 22) def test_gold_customer_over_100(): assert total(D("200.00"), "gold", THURSDAY) == D("170.00") def test_monday_promo(): assert total(D("200.00"), "gold", MONDAY) == D("166.00") def test_no_discount_under_100(): assert total(D("99.99"), "regular", THURSDAY) == D("99.99") ``` ``` $ python -m pytest -q ... [100%] 3 passed in 0.01s ``` One hundredth of a second. Monday is now a parameter, not a day of the week you have to wait for. The rule can be read in a file, has a history in git, shows up in code reviews and can be refactored with the IDE. The database just receives the result and stores it. It's brilliant at that. ## The cellar: the real version is the one in production 2:30 p.m. You go down to the cellar and compare the procedures in the repository with the ones in production. The repository has an `sql/` folder, last commit April 2014\. Production has: - `trg_fix`, `trg_fix2`, `trg_fix2_final` and `trg_fix2_final_REAL`, all enabled; - a procedure with the comment `-- urgent fix 23:47, will commit later`, dated 2017; - the function `new_compute_vat`, which is the oldest one in the database. This happens because in PostgreSQL, as in many other databases, `CREATE OR REPLACE FUNCTION` simply replaces the function. There's no history, no "who changed it and why". The previous version isn't archived anywhere: it's just overwritten. Anyone with the database password can change business logic in production at 11:47 p.m. from an SQL client. Seven people have the GESTIX password, including an intern from 2015. Logic in the application follows the same road as all other code: commit, review, tests, release. You can keep SQL in git with migrations too, of course. But the database doesn't force you to, and in fifteen years of real life nobody did. ## The attic: the one thing you can't photocopy 3:15 p.m. The attic is where the bills are. When the system slows down, application servers multiply easily: you start two more and split the traffic, like opening more checkouts at the supermarket. The primary database doesn't. There's usually only one that accepts writes, and it's the most expensive and most delicate machine in the whole setup. Every CPU cycle the database spends computing discounts, generating emails and redoing totals is a cycle taken from the one job only it can do: looking after the data. And if the database is Oracle Enterprise Edition, there are licences too: under the "Processor" metric you count cores, multiplied by a factor of 0.5 for common Intel Xeon and AMD EPYC chips. Translation: the Monday promo runs on the company's most expensive hardware, and it's paid for per core. Trigger load is a real problem, not a hypothesis. In 2016 GitHub introduced **gh-ost**, its tool for changing the schema of MySQL tables, deliberately built *without triggers*. Trigger-based tools had caused near or complete lockdowns in production, with the master brought down by the load of the triggers. On top of that, triggers couldn't be paused: removing them meant losing data. That was about schema migrations, but the principle is the same as in GESTIX: every trigger is code that runs inside every single write, whether you like it or not. ## The garage: "What if we moved to PostgreSQL 18?" 4:00 p.m. The CTO comes by your desk, phone in hand. "Did you see? PostgreSQL 18 is out. Let's drop Oracle and save a fortune on licences, right?" You open the garage. There are 412 procedures in PL/SQL, and PL/SQL isn't PL/pgSQL. They're as alike as Italian and Spanish: you think you understand everything until you order at a restaurant. Conversion tools exist, but they don't perform miracles. The assessment report of AWS Schema Conversion Tool, for example, has a dedicated tab, "Action items", listing precisely the objects that **can't be converted automatically**, with suggestions for rewriting them by hand. Martin Fowler wrote it back in 2003, in an article called "Domain Logic and SQL": *"If you want portability keep logic out of SQL."* Twenty-two years later, your garage is the practical proof. Migrating the database would be a house move. With the logic inside, it's a house move where you also have to translate every book on the shelves. ## The foundations: what stays in the database (hands off) 5:00 p.m. By now you might get carried away: rip everything out, the database should only store and read. No. Before demolishing, go down and look at the foundations, because some things do belong in the database. And they aren't "logic": they're **hygiene**. Try this. The sign-up form checks in code that the email doesn't exist yet, then inserts it. Logic in the application, all clean. Then Rina from the trattoria clicks "Sign up" twice, because the Wi-Fi is slow and patience is short: ```python def sign_up(email): with psycopg.connect(DSN) as conn: exists = conn.execute("SELECT count(*) FROM users WHERE email = %s", (email,)).fetchone()[0] start.wait() # both clicks land at the same instant if exists == 0: try: conn.execute("INSERT INTO users (email) VALUES (%s)", (email,)) print("signed up") except psycopg.errors.UniqueViolation as e: print("rejected:", e.diag.message_primary) ``` Run with two threads in parallel, first with no constraint, then with a `UNIQUE` on the column: ``` == no constraint signed up signed up rows: 2 == with UNIQUE signed up rejected: duplicate key value violates unique constraint "users_email_key" rows: 1 ``` Without the constraint, both clicks see "doesn't exist" and the same person is registered twice. The check in the application isn't enough, because between "check" and "insert" there's an instant, and anything can happen in that instant. Only the database sees all transactions at once, so only the database can guarantee that a value is unique. So here's the honest rule: - **In the database:** primary and foreign keys, `NOT NULL`, `UNIQUE`, simple `CHECK`s (for example "the discount is between 0 and 0.5"), proper types, transactions. The database decides *what valid data is*. - **In the application:** discounts, promotions, emails, courier calls, what happens when a price list changes. The application decides *what happens*. - **Heavy queries** stay with the database. In the same 2003 article, Fowler measured the single-SQL-query version as much faster than the in-memory ones. A report aggregating a million rows is done in SQL, not by pulling a million rows into Java. Asking the database for data is perfectly fine. Letting it make the decisions isn't. ## Back to the will 6:30 p.m. You go back to the CTO with a sheet of paper. "I accept the inheritance," you say. "But with the benefit of inventory." She looks at you the way you look at someone who has spent too long in the cellar. But the phrase is exact: in Italian law, accepting an inheritance "with the benefit of inventory" means any debts don't become yours, and the first step is making an inventory. Here's yours: 412 procedures, 97 triggers, 4 versions of `trg_fix2`, a table that emails imaginary customers, and a bonsai that arrived this morning from the Riviera as a welcome gift. The plan is simple and not remotely heroic: no big rewrite. One trigger moves per sprint, with tests written before you touch it. First to move out is the Monday promo. Then the email, before the Company Canteen orders more imaginary rigatoni. The constraints stay where they are; in fact you add two more. At 6:45 p.m. a digital postcard arrives from the Riviera. A bonsai, a sunset and a single line: > "Remember: `trg_history` DO NOT TOUCH IT. — O." You open `trg_history`. It's the only one of the 97 that decides nothing: it just keeps a diary of what the others get up to. You leave it where it is. Out of respect, and because in this house it's the only one that tells the truth. Sources - [PostgreSQL Global Development Group — "PostgreSQL 18 Released!" (25 September 2025)](https://www.postgresql.org/about/news/postgresql-18-released-3142/?ref=bitstales.eu) - [PostgreSQL 17 documentation — CREATE FUNCTION (CREATE OR REPLACE)](https://www.postgresql.org/docs/17/sql-createfunction.html?ref=bitstales.eu) - [PostgreSQL 17 documentation — Trigger functions in PL/pgSQL](https://www.postgresql.org/docs/17/plpgsql-trigger.html?ref=bitstales.eu) - [GitHub Blog — Shlomi Noach, "gh-ost: GitHub's online schema migration tool for MySQL" (1 August 2016)](https://github.blog/news-insights/company-news/gh-ost-github-s-online-migration-tool-for-mysql/?ref=bitstales.eu) - [Martin Fowler — "Domain Logic and SQL" (February 2003)](https://martinfowler.com/articles/dblogic.html?ref=bitstales.eu) - [AWS Schema Conversion Tool — Assessment report action items](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP%5FAssessmentReport.ActionItems.html?ref=bitstales.eu) - [Oracle — Processor Core Factor Table](https://www.oracle.com/assets/processor-core-factor-table-070634.pdf?ref=bitstales.eu) - [Italian Civil Code, art. 484 — acceptance with the benefit of inventory (Brocardi, in Italian)](https://www.brocardi.it/codice-civile/libro-secondo/titolo-i/capo-v/sezione-ii/art484.html?ref=bitstales.eu)