The Stored Procedure Nobody Knew Existed

debugging databases consulting architecture

A client's discount calculations kept applying rules that weren't in the application code. After two days of searching, I found them — in a PostgreSQL trigger written by someone who left the company three years ago.

The client was an online wholesale distributor, mid-size, running a Node.js backend with PostgreSQL. I'd been brought in to help untangle some pricing logic before they rolled out a new tiered discount system. Straightforward work, or so the brief suggested.

On day one, the lead developer walked me through the codebase. The pricing service was clean — a set of functions that calculated discounts based on customer tier, order volume, and product category. The logic was well-tested, easy to follow. But there was a problem.

"Some customers are getting discounts we can't explain," she told me. "A customer on the Standard tier ordered 50 units last week and got a 12% discount. Our code caps Standard at 8%."

She'd already checked the obvious things. No overrides in the admin panel. No manual adjustments in the database. The order record just... had a 12% discount applied to it.

Searching in the wrong place

I spent the first day reading code. The pricing service called a calculateDiscount function, which looked up the customer's tier, checked the volume brackets, and returned a percentage. I added logging at every step. When I placed a test order for a Standard-tier customer with 50 units, the function returned 8%. Correct.

But the order that landed in the database had a 12% discount.

Something was modifying the data between the application writing it and the final stored value. I checked for event listeners, message queue consumers, background jobs — anything that might touch order records after creation. Nothing.

Then I looked at the database itself.

SELECT tgname, tgrelid::regclass, tgfoid::regproc
FROM pg_trigger
WHERE tgrelid = 'orders'::regclass
AND NOT tgisinternal;

Two triggers. One named trg_order_audit_log, which made sense — the team knew about that one. The other was called trg_apply_loyalty_discount.

Nobody on the team had heard of it.

The ghost in the schema

The trigger called a function named fn_apply_loyalty_discount. It was about 80 lines of PL/pgSQL that checked the customer's order history directly against the orders table and applied an additional loyalty discount on top of whatever the application had calculated.

CREATE OR REPLACE FUNCTION fn_apply_loyalty_discount()
RETURNS TRIGGER AS $$
DECLARE
  order_count INTEGER;
  loyalty_bonus NUMERIC(5,2);
BEGIN
  SELECT COUNT(*) INTO order_count
  FROM orders
  WHERE customer_id = NEW.customer_id
    AND created_at > NOW() - INTERVAL '12 months';
 
  IF order_count >= 10 THEN
    loyalty_bonus := 4.0;
  ELSIF order_count >= 5 THEN
    loyalty_bonus := 2.0;
  ELSE
    loyalty_bonus := 0;
  END IF;
 
  NEW.discount_pct := GREATEST(NEW.discount_pct, NEW.discount_pct + loyalty_bonus);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The customer in question had placed 11 orders in the past year. Standard tier gave them 8%, and the trigger quietly added 4% on top. Mystery solved.

A git log on the migration files turned up the original migration from 2023, committed by someone named Raj. Nobody on the current team had worked with Raj. He'd left the company before any of them joined. The commit message said "add loyalty discount logic" and the PR had one approval from another developer who'd also since left.

The real problem

The trigger worked correctly. That was almost the worst part. It had been faithfully applying loyalty discounts for three years without a single bug. But it was invisible to every quality gate the team had:

No test coverage. The application test suite tested the pricing service in isolation, mocking the database. The trigger never ran during tests.

No code review. When the team updated pricing logic six months ago to add a new volume bracket, they reviewed the Node.js code. Nobody thought to check for database-level overrides because nobody knew they existed.

No monitoring. The application logged the discount it calculated. The trigger modified the value silently. The logs showed one number; the database held another.

The team was about to launch a new tiered pricing system. If they'd gone live without finding the trigger, every loyal customer would have received a double discount — the application's new loyalty calculation stacked on top of the trigger's. For their top 200 customers, that would have meant margins dropping below cost on high-volume orders.

Warning

If you inherit a PostgreSQL database, run a trigger audit before you trust that the application code tells the whole story. SELECT * FROM information_schema.triggers WHERE trigger_schema = 'public'; takes five seconds and might save you from a ghost in the schema.

What we did about it

We had a brief debate about whether to keep the logic in the database. There's a legitimate school of thought that says business rules enforced at the database level are more reliable — the application can't bypass them. But the team couldn't maintain PL/pgSQL code they didn't know how to test, and having pricing logic in two places meant neither could be trusted as the source of truth.

We migrated the loyalty discount into the application's pricing service, added integration tests that checked the actual database values after writes, and dropped the trigger. The whole fix took a day and a half. Finding the problem took two.

I've since made it a habit to run a trigger and function audit in the first hour of any new engagement. Most of the time there's nothing interesting. But twice now, I've found logic that the team didn't know was there — and in both cases, it was about to collide with changes they were planning.

Your ORM shows you tables and columns. It doesn't show you what the database does when you're not looking.