DEV Community: Pratham Dharawat

Giving a Language Model the Freedom to Interpret Without the Authority to Be Wrong

Pratham Dharawat — Sun, 12 Jul 2026 10:03:32 +0000

On building natural language search over a schema that changes without a deploy

The advanced filter screen is one of the oldest admissions of defeat in enterprise software. Pick a field from a dropdown. Pick an operator. Type a value. Add another condition if you need one. It works. It has always worked. And every person who has ever used one to find a specific set of cases in a fraud investigation has, at some point, wished they could just type what they were looking for and have the system understand it.

This system does that. A user types cases assigned to R. Iyer with the notice deadline past June thirtieth and gets back a real, filtered, paginated list of cases, the same list the dropdown screen would have produced, without touching a single dropdown. This is the story of how that works, why building it correctly is harder than it looks, and what I got wrong before I got it right.

The platform is a fraud case management tool used by banks and NBFCs for investigation and RBI regulatory reporting. Every case moves through nine or ten forms. Every form is not fixed. It is configured per client, per product type, through a forms engine that lets an administrator add, remove, or rename fields without a deployment. One client's investigation form might have three hundred fields. Another's might have four hundred, arranged differently, named differently, grouped into different forms entirely. A client opens the form builder on a Tuesday afternoon, adds a field, and from that moment the system has a shape it did not have an hour ago, with no migration, no release, and nobody on the engineering side aware it happened.

This is what makes the problem different from the one every natural language search tutorial is actually solving. Those are built over fixed schemas. Twenty columns, known at build time, stable forever. Natural language search over a fixed schema is a solved problem with good prior art and reasonable tooling. Natural language search over a schema that is a runtime value, different per tenant, reconfigurable at any hour by someone with no engineering background, is not the same problem in a different costume. It is a different problem, and the techniques that solve the first one break the second one in ways that are genuinely hard to diagnose.

The Reflex That Breaks It

The obvious implementation is obvious for a reason. Take the user's question. Hand it to a language model. Ask the model to produce a database query. Execute the query. Return the results.

This fails in a specific and dangerous way. The published failure modes of text-to-SQL are consistent across every generation of model: faulty joins, invented column names that sound plausible, aggregation logic that is subtly wrong but does not raise an error. In most systems, a wrong result is a minor inconvenience. In a fraud investigation platform used by regulated financial institutions, a query that runs, returns a result, and quietly filters on the wrong field is a compliance incident waiting to be discovered by someone downstream who trusted the output. The failure does not announce itself. It looks like an answer.

There is a second failure specific to this system. For the model to write a correct query, it would need to know which of three hundred to four hundred fields exist for this tenant, what they are named, how their values are stored, and how they relate to the case in front of the user. That is not context. That is the entire schema, restated per tenant, on every query, which is both enormous and still insufficient, because field names alone do not tell you what a field means.

But the deeper problem is not scale or context length. It is that handing a model a question and asking it to produce executable code collapses two problems that need to stay separate.

The first problem is interpretation: what does the investigator mean. The second is execution: what is true and safe to run against a live, regulated dataset. A language model is a reasonable instrument for the first. It has no business anywhere near the second. Collapse the two and every quiet misinterpretation becomes, silently, an incorrect result set, with no seam anywhere in between where the mistake could have been caught.

So the model in this system never produces a query. It never sees a table. It fills in a small, closed, typed object I define completely, an intent rather than a query, and a separate deterministic layer decides afterward whether that intent refers to anything real and whether it is safe to act on. Interpretation stays probabilistic. Execution stays deterministic. They never touch directly, and that separation is the spine everything else hangs off.

Three Problems, Not One

Once you commit to structured intent rather than raw query generation, a second realisation follows quickly: what looks like one problem from outside is actually three separate problems that need three different techniques. Collapse any two of them and the system breaks, usually silently, and usually in production rather than in testing.

The three problems are finding the right field, finding the right value, and preventing the model from acting on something it invented. Each one has a different shape, and the shape determines the tool. Applying the same technique to all three because it works for one of them is how systems end up confidently wrong about the cases that matter most.

Finding the Right Field Is a Meaning Problem

Say the field count out loud. Three to four hundred, per tenant, reconfigurable at runtime. Handing that entire catalog to a model on every question is not a viable starting point. It is expensive for no reason, since the overwhelming majority of those fields are irrelevant to any single question. More importantly, it is inaccurate. The more irrelevant options sit in front of a model, the more its attention is diluted across noise, and the higher the odds it settles on something plausible sounding rather than correct. A catalog that large is not context. It is noise with a small signal buried inside it.

The fix is retrieval before generation. Every active field across every form gets embedded once, using its name, its admin authored tooltip, and the name of the form and page it lives on. That last part matters more than it might seem. Three hundred fields spread across nine forms produce real naming collisions. Two fields both called Date are indistinguishable by name alone. Date from the Notice Configuration Form and Date from the KYC Verification Form are not the same field, and embedding the surrounding context alongside the name is what makes them retrievable separately.

Every stored field embedding sits in a vector column in PostgreSQL, one table per tenant schema, indexed with an approximate nearest neighbour index so similarity search stays fast as the field count grows rather than scanning the entire table every time. When a question arrives, it gets embedded the same way, and a single similarity search returns a small, relevant shortlist, fifteen or so candidates instead of four hundred. That shortlist, and only that shortlist, is what the model ever sees.

The catalog is not treated uniformly, and that distinction matters more than the retrieval mechanism itself. Alongside the three to four hundred dynamic fields, the system also carries a small, fixed set of structural attributes, case stage, current assignment, active status, a dozen or so, identical across every tenant. Those get shown to the model in full, every time, no retrieval involved. Semantic search earns its cost against a genuinely large, variable catalog. It buys nothing against twelve fixed items. Reaching for it there anyway would be applying a technique because it was already built, not because the problem in front of it needed it. Matching the tool to the actual shape of each sub-problem, rather than to whichever tool happens to be nearby, was one of the more consequential small decisions in the whole system.

Finding the Right Value Is a Spelling Problem

Retrieval solves which field a question is about. It does not solve who R. Iyer is, or what the audit group refers to, and those are a genuinely different kind of lookup that deserves its own mechanism rather than an extension of the first one.

Cases assigned to R. Iyer and cases with the audit group both name values, not fields. Those values are real rows sitting in real tables: a user directory with hundreds of names, a groups table with a few dozen, and a handful of smaller reference tables for case stage, sub status, product type, incident category, and branch hierarchy. Finding the right field was a meaning problem. Finding the right person named R. Iyer, or R. Iyer with a middle initial, or misspelled by one letter, is a spelling problem. Spelling problems do not call for embeddings. They call for character overlap, and that is a far older and cheaper technique.

This half of the system runs on trigram similarity, comparing overlapping three character sequences between strings. It is not sophisticated. It is correct for the shape of the problem. A short extraction pass reads the question first and identifies anything that will need a value lookup, a person's name, a group, a stage, a category. The resolver then searches every relevant table in parallel, users, groups, stage, sub status, product type, incident category, hierarchy, because every one of those tables shares an identical shape and one parameterised method covers all seven instead of a bespoke implementation for each.

The interesting failure appeared after this resolver was widened from two tables to seven, and it broke two questions that had worked cleanly until that moment. The bug taught something I had not fully understood about retrieval systems before building this one.

With only users and groups in scope, the top scoring match was almost always the only serious candidate, and a bare similarity floor was sufficient. Adding five more tables changed the statistics of the problem without anyone touching the threshold. A short name began weakly matching branch names it shared almost no real relationship with, purely on incidental letter overlap, at a score that still cleared the same floor that had been fine a moment earlier. The system was checking whether a candidate was good enough in isolation. It was never checking whether that candidate was good enough relative to everything else that also happened to match.

This is the distinction that changed how I think about retrieval. A similarity score tells you a match exists. It does not tell you whether that match is the only one that matters. Those are different questions, and a system that only answers the first one will eventually be confidently wrong in ways that pass every individual test.

The fix reframes what confidence means. A candidate only counts as trustworthy if it clears an absolute floor and stands meaningfully apart, within a defined margin, from the next best option. Two candidates within that margin are not one confident answer and one noise. They are a genuine tie, and the right response to a genuine tie is to say so rather than silently pick one.

There is a real limit here that no amount of threshold tuning removes. Strings three or four characters long simply do not carry enough information for character overlap to be reliable. Loosening the floor enough to correctly resolve a genuinely short name also lets short meaningless coincidences back in. That is not a bug waiting for a better number. It is where the technique runs out. The honest fix is architectural: let a person disambiguate a short or ambiguous reference explicitly, through the interface, rather than asking string similarity to divine intent from three letters. I have not built that yet. Naming the boundary precisely is worth more than pretending a cleverer threshold exists somewhere unexplored.

Preventing the Model From Acting on Something It Invented Is a Trust Problem

Retrieval decides what the model gets to see. It does not decide what happens when the model is wrong anyway, and on that question the most useful public account I found of anyone building this same shape of system is Netflix's writeup on rebuilding their internal Graph Search around natural language. Their system converts questions into a custom filter language rather than raw queries, validates generated output against real schema metadata, and ran into exactly the problem this system also ran into. Even with carefully assembled context, they found the model "sometimes hallucinates both fields and available values in the generated filter statement."

That sentence is not describing a flaw specific to their implementation. It is describing something structural about how these models work. No prompt instruction makes hallucination impossible. Instructions reduce frequency. They do not provide a guarantee, and a system that only has a reduction in frequency is asking you to trust a probability.

The answer is two genuinely different guarantees, stacked. They are not the same guarantee reinforced twice. They fail in different registers, and both are necessary.

The first is what the model is shown. Only the fields retrieval actually surfaced, only the values resolution actually found, and an explicit instruction to choose only from what is in front of it. This reduces how often the model invents something. It does not reduce that number to zero.

The second happens entirely outside the model, after generation, before anything is trusted. Every field the model names gets checked against the exact candidate list it was handed. Every value gets checked against the exact set of real rows the resolver already found. Anything that fails either check is discarded, logged as a likely fabrication, and never reaches a query, regardless of how plausible it reads. Netflix notes from their side that this validation costs almost nothing extra, since the data being checked against was already loaded to build the context in the first place. The same is true here. Validation is cheap precisely because the ground truth was never fetched twice.

The first layer is probabilistic and makes bad output rare. The second is deterministic and makes bad output, on the rare occasion it still occurs, structurally incapable of being acted on. A system is only as honest as the weaker of the two guarantees it actually has, not the stronger one it advertises.

One category of value earned a third, narrower form of grounding. Whether a case is open or closed is a boolean underneath, but nobody types false. They type pending, resolved, still active. Rather than trust the model to infer that mapping correctly on every query, which is exactly the kind of quiet inference that goes right ninety times and wrong on the hundredth in a way nobody notices until an audit, the accepted vocabulary and its synonyms are stated directly in what the model is shown. Closed maps to false because the system says so, not because the model guessed correctly and happened to keep guessing correctly.

What This Actually Claims

A person can type a plain question into a system carrying four hundred fields they have never seen named, against a schema an administrator reshaped last week without telling anyone, and get back a real, filtered, paginated list. The model that produced it never wrote a query, never touched a table, and was never taken at its word for anything that mattered. Every field it referenced was one it was shown and none other. Every person and group it named was a row already confirmed to exist before generation happened at all. Everything it produced was checked again, by code that owes the model nothing, before being allowed to run. The validated conditions never spawn a new query path. They become additional clauses on the same query builder every other screen in the product already runs through, because correctness belongs at whichever layer has already earned it.

That is a narrower claim than most natural language search demonstrations are implicitly making. Most of them are showing you that a model can sound like it understood a question. That is not hard anymore. Building a system that gives a model the freedom to interpret language while never once extending it the authority to be wrong is a different exercise, and it is the one that was actually worth doing here.

The dropdown filter is still there. It does not need to disappear. What changed is that an investigator no longer needs it to answer most of what they actually ask.

Caveats

This describes one system built against one set of constraints. A per tenant schema in the low hundreds of fields, lookup tables with hundreds rather than thousands of entries, and a domain where an incorrect result is a compliance exposure rather than an inconvenience. At a meaningfully larger field count, the retrieval step would need a reranking pass on top of raw similarity, which this version does not have. Past the low hundreds of users or groups, trigram matching alone begins to strain, and the disambiguation interface currently absent from this system stops being a future improvement and becomes a requirement. Every threshold value here was set against this system's real data by testing, and would be revised the moment testing said otherwise. None of them are constants. They are the record of specific tradeoffs made deliberately, which is the only kind of number worth publishing.

Netflix's writeup on rebuilding Graph Search around natural language is the closest public account I found of anyone solving this same shape of problem. Their system runs at a scale this one may never reach, against a custom filter language with an AST parser rather than a schema-native structured format, and they arrived at the same validation discipline independently. Convergent answers to the same problem, found separately, are usually a sign the answer is closer to correct than either team could tell alone. Their writeup is worth reading directly.

Nothing in the individual pieces here is novel. Embeddings, trigram matching, structured generation over raw query generation, none of it is new. What is less common is holding all of it together against a schema that refuses to sit still, changed by someone with no engineering background, at any hour, with no warning, and building a system that remains correct anyway, not because it trusts the model to get things right, but because it was designed from the start to survive the model getting things wrong.

The model interprets. The system decides what that interpretation is allowed to mean. That boundary, held precisely, is the only thing that makes any of this trustworthy.

When Notifications Are a Legal Obligation: The Architecture Behind Rule-Driven Event Pipelines Across Three Regulated Products

Pratham Dharawat — Tue, 30 Jun 2026 08:48:43 +0000

In most systems, a notification is a convenience. It tells you something happened. If it arrives late, or not at all, the user refreshes the page and moves on.

That is not the class of problem I was solving.

Fraud Sentinel is a fraud case management and RBI FMR reporting platform used by NBFCs and banks. When a fraud case is created, updated, or breaches its turnaround time, the right people must be notified, not as a courtesy, but as a process requirement embedded in compliance workflows. Whistle Sentinel is an enterprise whistleblowing portal. When an investigator sends a status update, or a reporter submits additional evidence through the case chat, the involved parties must receive that signal with the same reliability as a transaction confirmation. TrueScale is a legal metrology compliance tracker. Deadlines matter. Breaches matter. Silence is not an acceptable delivery state.

Three regulated products. Three distinct event vocabularies. One shared infrastructure underneath all of them. They share one fact: when something happens, somebody must be told, and the telling cannot be lost.

This is the story of how that infrastructure was designed, where the standard approaches broke down, and what it actually took to build notifications that cannot be lost.

The Event Surface Is Wider Than You Think

The first instinct when building notification systems is to enumerate the events. Case created. Case updated. That is usually where the list ends.

In practice, the event surface across these three products is significantly wider.

For fraud case management: a case can be created, updated, assigned to a new investigator, reassigned to a new department, have a form submission added to it, reach its TAT deadline, or breach it entirely. Each of these is a distinct moment that may or may not require a notification, depending on rules configured per tenant.

For the whistleblowing portal: an investigator sending a case status update is an event. A reporter submitting a new message in the case chat is an event. A reporter submitting additional supporting details is an event. These are not the same. The recipients differ, the message templates differ, the urgency differs.

For legal metrology compliance: a deadline approaching is different from a deadline breached. Both require different notification postures.

What this surface reveals is that hardcoding event types is a losing strategy from the start. The system needs to be configurable at runtime, where tenant administrators can define rules, conditions, and actions without a deployment. This led to a rules engine with three distinct rule types. Creation rules fire when a case or incident is first created. Update rules fire when a case or incident is updated, with trigger conditions scoped to specific update events such as form submissions, assignments, or status changes. Time-trigger rules fire based on TAT deadlines, calculated against configurable date fields or system-tracked timestamps, with configurable pre-offset and post-offset windows.

Each rule has conditions and actions. Conditions determine whether this particular case, at this particular moment, qualifies for the rule to fire. Actions determine what happens: which channels to notify, which recipients to resolve, which template to render. The engine is shared across all three products. Adding a new event type to any product is a matter of defining a new rule trigger, not modifying the pipeline.

The Rules Engine, and the Insight About Loading

Before the outbox, a word about what produces these notifications, because it shaped everything downstream.

Notifications are not hardcoded. They are emitted by a rules engine that clients configure at runtime. Each rule has conditions and actions. Conditions are predicates over static case fields, over submitted form-field values, and over entity hierarchy mappings: whether a case belongs to a department, a region, or a reporting line. Actions are notification actions: send an email, raise an in-app system notification.

This is the classic rules-engine shape, conditions and actions externalised from code. Martin Fowler's warning about rules engines is worth quoting here because I think it is correct: the central pitch, that "it will allow the business people to specify the rules themselves," he writes, "can sound plausible but rarely works out in practice," and the implicit chaining behaviour of a general engine "can easily end up being very hard to reason about and debug." His conclusion is to keep rules "within a narrow context" and prefer "a more domain specific approach." That is exactly what this is. There is no chaining. A rule's action never mutates state that another rule's condition reads, so the entire class of debugging nightmares Fowler describes simply does not exist here. The engine evaluates, collects matching rules, and emits notification intents. Nothing more.

The non-obvious engineering decision is in how conditions are evaluated. The obvious approach is to load the case, the whole aggregate with its form submissions and hierarchy mappings, and then run each rule's conditions against the in-memory object. This is what almost everyone does, and it is wasteful in a way that compounds. A case with dozens of form fields and a deep hierarchy is an expensive load, and most rules touch two or three fields.

The engine inverts this. It first inspects the conditions across the rules that could fire and determines which types of data are actually referenced: static fields, specific form fields, particular hierarchy levels. Only then does it issue queries, in parallel, for exactly those fields. If no rule references form submissions, the form-submission query never runs. This is targeted loading, the same principle that the rules-engine literature calls lazy fact loading: the recognition that performance "degrades with very large rule sets or expensive fact lookups" and that the cure is to not load facts you will not test. In a multi-tenant system where the same evaluation runs across many schemas under load, the difference between loading the whole case and loading three fields is the difference between a pipeline that keeps up and one that falls behind.

How the Design Actually Evolved

The right design is not the one I started with. This section is the honest account of how the notification system was built, then broken in a specific and instructive way, and then rebuilt properly.

The standard industry approach to this problem is straightforward: write the business entity to the database, commit the transaction, and then enqueue a job or call a messaging system to trigger downstream processing. Most teams do this and most of the time it works. The failure mode is silent and rare, which is the worst combination. The database commits, something goes wrong between the commit and the enqueue, and the event is gone. No error surfaces, because the HTTP response already returned success. The system has no record that the event happened, so there is nothing to retry and nothing to alert on. In most domains, a dropped notification is a minor inconvenience. In a regulated compliance system, it is an incident.

Stage one: EventEmitter only. The original implementation used NestJS's EventEmitter2. When a case was created, the creation service emitted a case.created event after committing the transaction. A listener picked it up and ran the rules engine, which triggered notifications directly. There was no outbox at this stage. The failure mode was silent and I encountered it firsthand: the database transaction committed, the notification never fired, and nothing in the system recorded that it had been missed. There was nothing to retry, nothing to alert on. The case existed. The notification did not. Nobody knew.

That is the moment I understood what the requirement actually was. Lost notifications are not an edge case to handle. They are the central problem to design around.

Stage two: EventEmitter with outbox bolted on. After learning about the transactional outbox pattern, I introduced a system_outbox_events table and had the rules engine write to it. But the EventEmitter architecture remained. The sequence was: commit the transaction, emit the event, listener picks it up, listener writes to the outbox. The problem was that the outbox write was happening inside the EventEmitter handler, which ran after commitTransaction() had already been called. The case was committed. The outbox write was now outside any transaction. If that write failed, for any reason, the case existed with no intent ever recorded. The atomicity I thought I had was an illusion. The outbox was present, but it was not transactional. Adding a table to the system is not the same as adopting the pattern.

Stage three: The refactor, and the proper pipeline. During the full codebase refactor, I removed EventEmitter from the notification path entirely. The pipeline was rebuilt in proper layers. Rule evaluation and the outbox write were moved inside the same database transaction as the case write. A BullMQ processor reads from the outbox, not from an event. A separate poller manages the outbox lifecycle across all tenant schemas. A dispatcher routes by product code. Preparation processors resolve recipients and placeholders before delivery. Each layer has one responsibility and hands off to the next. The architecture was not arrived at in one step. It was the result of understanding, through production failures, what "durable" actually means in a regulated system.

The rest of this article describes the final architecture.

Why an Outbox, and Which Outbox

The transactional outbox, as catalogued by Chris Richardson, is the pattern where you "store the message in the database as part of the transaction that updates the business entities. A separate process then sends the messages to the message broker." The outbox row is an intent, not a rendered message. It records that something happened and which rule fired, with enough context to produce a notification later. It does not yet know who will receive an email or what the email will say. That deferral is deliberate, and the reason for it comes later.

A separate process reads committed outbox rows and dispatches them. There are two well-known ways to build that reader, and the distinction is the single most consequential one in the whole design. Richardson catalogues them as the Polling Publisher, "publish messages by polling the database's outbox table," and Transaction Log Tailing, "tail the database transaction log and publish each message/event inserted into the outbox." Log tailing, implemented with a tool like Debezium reading PostgreSQL's write-ahead log, is the more scalable and lower-latency choice at very high throughput. It imposes no query load on the database and, because the WAL is in commit order, it delivers events in commit order.

I chose polling deliberately, and the multi-tenancy is the reason. Log-tailing tools read the WAL at the database level. Mapping a single physical WAL stream back onto per-tenant schemas, and operating Debezium and Kafka as additional infrastructure for a solo-maintained platform, is a large operational commitment. Polling is, in Richardson's words, the pattern that "works with any SQL database," and it kept the entire pipeline inside the stack I already ran: PostgreSQL, Redis, NestJS, with no new moving parts to operate. The honest cost is twofold. Polling has a latency floor set by the poll interval, and it imposes constant query load. For deadline notifications measured in days, a five-second floor is irrelevant. That judgment was made explicitly rather than discovered later, which is the difference between a tradeoff and a bug.

It is also worth being clear about what the outbox does not guarantee. Because the poller reads committed rows and claims them by status rather than reading from a commit-ordered write-ahead log, strict global ordering of notifications is not guaranteed. For this domain, that is acceptable. Notifications are individually meaningful events, not a stream that must be replayed in exact order.

The Multi-Tenant Wrinkles, Where the Real Lessons Are

A schema-per-tenant system routes queries by setting the PostgreSQL search path, the ordered list of schemas PostgreSQL searches to resolve an unqualified table name. The way you set it is not a detail. It is the single most consequential decision in the entire codebase, and getting it wrong leaks one client's data to another.

The trap is SET search_path versus SET LOCAL search_path. Plain SET search_path changes the setting for the session. With a connection pool, that means it persists on the physical connection after the request finishes and the connection returns to the pool. The next request to acquire that connection inherits the previous tenant's search path. In a multi-tenant fintech platform, that is cross-tenant data bleed: a query intended for tenant B silently reads tenant A's schema. The PgBouncer literature is unambiguous about this: "this connection is reused also for other services, so the services influence each other," and the fix is precisely SET LOCAL search_path. SET LOCAL scopes the change to the current transaction. When the transaction ends, PostgreSQL reverts it automatically, and the connection returns to the pool clean. Every tenant-scoped operation in this system runs inside a transaction with SET LOCAL search_path. There is no exception to that rule, because the cost of an exception is a confidentiality breach.

That correct decision then breaks something else, which is how I learned the next lesson. The outbox writer needs an idempotent insert: a dedupe key with "insert if not present, otherwise do nothing," so that a retried job cannot create a second copy of a notification. TypeORM exposes .orIgnore() for exactly this purpose, an ON CONFLICT DO NOTHING wrapper. But with the search path set via SET LOCAL, TypeORM's query builder did not reliably target the tenant schema for the conflict-handling insert. The fix was raw SQL: an explicit INSERT ... ON CONFLICT (dedupe_key) DO NOTHING, run on the same transaction-scoped connection that holds the SET LOCAL. The dedupe key encodes the case, the action, the channel, and the event type, making it deterministic and collision-resistant across retries.

The dedupe key is what makes the whole at-least-once pipeline safe. A polling relay provides at-least-once delivery, not exactly-once. Richardson states it plainly: the relay "might publish a message more than once. It might, for example, crash after publishing a message but before recording the fact that it has done so. When it restarts, it will then publish the message again. As a result, a message consumer must be idempotent." This is the Idempotent Consumer pattern, and the dedupe key is its concrete implementation. You do not fight duplicate delivery. You design so that a duplicate changes nothing.

There is a smaller, sharper trap, from Redis. The poller and dispatcher coordinate through Redis, and I run ioredis with a keyPrefix so that keys are namespaced. The footgun: ioredis's keyPrefix is applied to ordinary key commands but not to KEYS, SCAN, or EVAL. This is a long-standing documented behaviour, with open issues going back years. If you rely on the prefix being universal, your SCAN silently matches nothing and your Lua locking script operates on the wrong key. I prepend the prefix manually for those commands. It is not elegant. It is correct, which matters more.

The Pipeline

With the outbox as the foundation, the full pipeline has five distinct layers, each with a well-scoped responsibility.

The rules processor. When a case is created or updated, a BullMQ job is enqueued for the rules processor. The processor evaluates active rules against the case and, for each matched rule with notification actions, writes an intent row into system_outbox_events. Both the rule evaluation and the outbox write happen within the same database transaction. That is the entire point.

The rules processor does not load the entire case data. A case can have dozens of form field submissions, entity hierarchy mappings, and static field values. Loading all of them to evaluate a rule that references two fields is wasteful in a way that compounds under load. Instead, the processor inspects the conditions across all candidate rules first, determines which fields are actually referenced, and fires targeted parallel queries only for those fields. Static case fields, form field submissions, and entity hierarchy mappings are three separate queries, each scoped to the IDs present in the conditions. If no rule references entity mappings, that query does not run.

For update rules, the processor applies a deliberate four-phase sequence before loading any case data. First, it filters by trigger type at the database query level, so a rule configured for form submissions is never evaluated when the trigger was an assignment change. Second, it filters by metadata in memory, eliminating rules that reference a specific form when a different form triggered the update. Third, it evaluates trigger conditions against current case state using targeted loading. Fourth, it evaluates filter criteria against the survivors. Each phase eliminates candidates before the more expensive next phase runs. Trigger type filtering costs a query predicate. Metadata filtering costs nothing. Case data loading only happens for rules that survived both.

For time-trigger rules, the evaluation is different. A TAT cron runs on a schedule. It loads active time-trigger rules, evaluates each rule's TAT type against its configured offset window, finds the matching cases, and for each writes notification intents to the outbox. Each rule gets its own QueryRunner. A failure in one rule's evaluation should not roll back the work done by other rules. Isolation at the rule level is the right boundary.

The outbox poller. The poller wakes every five seconds. Five seconds is a deliberate compromise: it is the latency floor, traded against database query load, and for deadline notifications measured in days it is fast enough while keeping the query rate modest.

The poller has four properties that took the most design thought, and each exists because of a specific failure mode I either hit or refused to risk.

A distributed Redis lock ensures that when the service runs multiple instances, only one poller processes a given schema at a time. This is a single-instance Redis lock, not the multi-node Redlock algorithm, and that is the appropriate choice here. The lock is a performance optimisation to prevent two workers from contending on the same rows, not the sole guarantee of correctness. The correctness guarantee lives in the database, in row-level claiming and the dedupe key. A lost lock at worst causes a duplicate attempt, which the dedupe key absorbs. The lock makes the common case efficient. The database makes every case correct.

A circuit breaker ensures that when something is systematically wrong, the database is unreachable or an entire schema's processing is failing, the poller stops hammering it. When failures exceed a threshold, the breaker trips to open and rejects calls immediately rather than "waiting for the operation to time out or never return," then probes for recovery. Without it, a poller waking every five seconds against a failing dependency becomes a load amplifier exactly when the system can least afford it.

Round-robin rotation through tenant schemas is the property that is invisible until it bites. The poller does not process tenants in a fixed order. It keeps a cursor and rotates, so each cycle starts where the last left off. With a fixed order and a bounded amount of work per cycle, under load the poller spends its entire budget on the first N schemas every single time. The schemas at the tail of the list are never reached. They do not fail loudly. They simply never receive their notifications, which in this domain means a tenant silently stops receiving regulatory deadline warnings while every other tenant is fine. Round-robin guarantees that every schema is reached in bounded time regardless of its position in the list.

Lease reclaim for crashed workers addresses the last failure mode. When the poller claims an outbox row it marks it PROCESSING and stamps a lease timestamp. When the dispatcher forwards it, the row moves to DISPATCHED with its own lease. Both leases exist to answer one question: what happens when a worker crashes mid-job? Without leases, a row marked PROCESSING by a worker that then dies is stuck in that state permanently. It is claimed but will never complete. The notification is lost as surely as in the naive design, just by a different route. The lease makes the claim expire. A later poll cycle sees a PROCESSING row whose lease has elapsed, concludes the worker is gone, and reclaims it. The lease duration is the maximum time a notification can be stuck behind a dead worker, and you tune it accordingly.

The dispatcher. The dispatcher is a BullMQ processor that receives a claimed outbox event and routes it to the correct preparation queue. The routing key is product_code. Fraud Sentinel cases route to case preparation queues. Whistle Sentinel incidents route to incident preparation queues. The same dispatcher handles both, and adding a third or fourth product is a matter of adding a routing case.

This layer is defined by what it refuses to do. The products do not know about each other at the queue level. There is no shared queue that all three drain. Instead there is one shared mechanism, the outbox, the poller, the dispatcher, and cleanly separated channels per product. A backlog or a poison message in one product's preparation queue cannot stall another product's notifications. This is the seam that lets the platform add a product without touching the others.

The preparation processors. Each channel has its own processor per product. A preparation processor picks up a DISPATCHED outbox event and does the work the outbox intent deliberately left undone. It resolves recipients, which may be the case's assigned users, the heads of a department, specific user IDs, or custom email addresses configured on the rule. It resolves the notification template, substituting placeholders with real values drawn from static case fields, entity hierarchy values, and form field submissions. Then, and only then, it enqueues the actual delivery job.

The most important decision in the whole design lives here: recipient resolution happens in the preparation processor, not in the rules engine at the moment the rule fires. It would be simpler to resolve recipients when the rule matches and store the list in the outbox row. That would be wrong. Recipients are not stable between the moment a rule fires and the moment a notification is delivered. A department head changes. A user is deactivated. An assignment is reassigned. If you bind recipients at rule-evaluation time and the notification is delivered minutes or hours later, entirely possible with TAT rules, retries, and circuit-breaker pauses, you deliver to a stale audience: emailing someone who has left, or failing to email the person now responsible. Resolving recipients at preparation time, as late as possible before delivery, binds to the current truth. This is late binding applied to people, and in an organisation where responsibility moves between humans it is the difference between a notification that reaches the right person and one that reaches a ghost.

There is one failure-handling pattern here that is easy to get wrong. A preparation processor does its work inside a transaction. If that transaction fails and rolls back, you naturally want to mark the outbox event as FAILED so it is visible and retriable. But you cannot do that on the rolled-back transaction's query runner. It is poisoned. Any further statement on it is part of the aborted transaction and will not persist. The fix is to mark the failure on a fresh query runner, in its own separate transaction, deliberately outside the failed one. It feels redundant the first time you write it. It is the only way the failure record actually commits.

The delivery queues. The final delivery queues are deliberately dumb. They receive a fully resolved subject, body, and recipient list. Email delivery sends the email. In-app notification delivery writes the notification record. Neither knows anything about cases, rules, or tenants beyond the schema context passed in the job payload. That simplicity is the point. Complexity belongs upstream, where it can be reasoned about. By the time a job reaches this layer, every decision has already been made.

The Shared-Infrastructure Dividend

Step back and the architecture is one pipeline: rule evaluation writes intents to a shared outbox inside the business transaction; one poller, with its lock, breaker, rotation, and leases, drains the outbox safely across all tenants; one dispatcher routes by product code; per-product preparation processors resolve and deliver. The same machinery serves all three products.

The dividend is concrete. When a fourth product is added to the platform, it does not build a notification system. It defines its rules, its templates, and its preparation processors for the channels it needs, and it inherits for free: durable intent capture, crash-safe delivery, tenant isolation, fairness across schemas, idempotency, and failure visibility. The hardest parts of "tell someone, and never lose the message" are solved once, at the platform layer, and amortised across every product that will ever live on it. That is the entire argument for building it this way rather than as a feature inside one product. The second and third products paid almost nothing for notifications, and the fourth will pay less.

What I Would Tell Someone Building This

Take the dropped-message failure seriously first, before you design anything, because it is the requirement that forces the outbox and everything the outbox implies. If you can tolerate lost notifications, you do not need most of this and you should not build it. If you cannot, and in a regulated domain you cannot, then the outbox is not optional and the dual-write shortcut is a latent incident.

Put correctness in the database and use Redis and queues for speed, never the reverse. The lock, the leases, the circuit breaker, the round-robin: these make the system fast and fair and resilient, but every one of them can fail without losing a notification, because the durable intent and the dedupe key live in PostgreSQL under transactional guarantees. When you find yourself depending on Redis for correctness, you have made a mistake you will pay for during a network partition.

And resolve as late as you can. The outbox stores intent, not a rendered message, and recipients are bound at delivery time, because the world changes between cause and effect. Most of the subtle correctness in this system comes from deferring decisions until the last moment when the information is freshest, which is, in the end, the same discipline as the outbox itself: do not commit to an irreversible action until you are certain it will actually happen.

Caveats

This article describes one system as built, with its specific constraints: a solo maintainer, a stack of PostgreSQL, Redis, and NestJS, a regulated domain where lost messages are incidents, and a tenant count served comfortably by schema-per-tenant isolation. Several decisions are right for those constraints and would be wrong for others.

At very high throughput, log tailing beats polling and the five-second floor becomes intolerable. Past a few thousand tenants, schema-per-tenant strains PostgreSQL's system catalogs and a different isolation model is warranted. The single-instance Redis lock is sufficient only because correctness does not rest on it. A system that leaned on the lock for correctness would need Redlock and, even then, would inherit the unresolved debate about Redlock's safety under clock skew.

The pipeline described here handles three products, an arbitrary number of tenant schemas, and a configurable event surface that operators can extend without code changes. It has been running in production across fraud case management, enterprise whistleblowing, and legal metrology compliance workflows.

None of the individual techniques here are novel. The outbox pattern is well-documented. Distributed locking with Redis is standard. Schema-per-tenant is a known multi-tenancy strategy. The contribution, if there is one, is in the specific combination of constraints: regulatory stakes, schema isolation, shared infrastructure across distinct products, and the precise places where the standard approaches break down under those constraints. Those are the places worth examining carefully.

None of these are universal prescriptions. They are the record of a specific set of tradeoffs, made deliberately, under constraints I could name. That is the only kind of engineering writing I trust.