“Notify me when AAPL hits $250” or “tell me when this Amazon item drops below Rs 1,999” sounds like a WHERE price >= target query. Then you look at the numbers. There are 100M active alerts. Stock prices update millions of times a second across tens of thousands of instruments. If you run that WHERE on every price tick you are doing millions of full scans per second over a hundred million rows, and you melt. The whole problem is the inversion: it is not “given a price, which alerts match” as a database scan, it is “given a firehose of price changes, fire exactly the alerts that just got crossed, once, within a second, without scanning anything you do not have to.”

The core insight is that a price alert is a threshold that a moving value crosses, and crossings are rare relative to ticks. Most ticks cross nothing. So the job is to build an index that, for each tiny price move, touches only the alerts that actually fired and none of the others - and to do that at millions of ticks per second. Let me build it properly.

Functional Requirements (FR)

In scope:

  • Create an alert. A user sets {symbol, condition, target_price} where condition is above (fire when price rises to or past target) or below (fire when price falls to or past target). Works for stocks and for product prices (Amazon items).
  • Fire the alert when the threshold is crossed. When the live price of the symbol crosses the target in the specified direction, the user is notified within seconds.
  • Alert types. One-shot (fire once, then deactivate) and recurring (fire every time it crosses, with a cooldown so a price hovering at the threshold does not spam).
  • Manage alerts. List, edit, pause, and delete alerts. See which fired and when.
  • Multiple channels. Delivery goes out via push / email / SMS - we hand off to a notification system, we do not rebuild one.

Explicitly out of scope (own the scope):

  • The notification delivery pipeline itself (channel fan-out, provider adapters, retries). We produce a “fire this alert” event and hand it to an existing notification service. See a separate notification-system design for that half.
  • The market-data feed internals (exchange connectivity, FIX/ITCH parsing, licensing). We consume a normalized tick stream; we do not build the exchange gateway.
  • Price discovery for products. How Amazon knows an item’s price changed (crawlers, catalog events) is upstream; we consume price-change events.
  • Charting, portfolio, and technical-indicator alerts (“notify when the 50-day MA crosses the 200-day”). Those are a stateful streaming-analytics problem; here I handle simple threshold crossings and note the extension.

The decision that drives everything: the read side is a firehose and the alert set is huge and mostly idle. A price tick is cheap to produce and there are millions per second; an alert is a durable threshold and there are 100M of them, but on any given tick approximately none of them fire. The system must be organized so the cost of a tick is proportional to the number of alerts it actually triggers, not to the number of alerts that exist. That asymmetry is the whole shape of the design.

Non-Functional Requirements (NFR)

  • Scale: 100M active alerts. ~20M users, ~5 alerts each. Symbol universe: ~50K actively-traded instruments (equities, ETFs, major option underlyings) plus a long tail of low-volume products. Price updates: peak ~3M ticks/sec for stocks during market open, heavily concentrated in a few hundred hot symbols. Product price changes are far slower (millions/day, bursty during sales).
  • Latency: from a price crossing the threshold to the notification being handed off, p99 under ~2s. For stocks this matters - a stale “hit your target” alert 30s late is useless when the price already moved. Alert create/edit is a normal request path, p99 under 100ms.
  • Availability: 99.9%+ on the matching path during market hours. Missing a fire because a matcher crashed is a real, visible failure. Alert CRUD can tolerate slightly less.
  • Consistency: the alert store (source of truth) is strongly consistent for a user’s own edits (you delete an alert, it stops firing). The matching index is eventually consistent with the store but must converge within a second or two, because an alert created “just now” should fire on the next relevant tick.
  • Durability: an accepted alert must never be silently lost. Once we ack the create, it survives crashes. A fired alert must be recorded so we do not double-fire a one-shot.
  • Correctness of firing: exactly-once user-visible firing per crossing. At-least-once matching under the hood, deduplicated so the user is not notified twice for the same crossing. And no missed crossings even when a price spikes and retraces between two evaluations.

Back-of-the-Envelope Estimation (BoE)

Real numbers, with arithmetic, because they pick the architecture.

Alerts (the standing set):

20M users * 5 active alerts avg = 100M active alerts
Per alert index entry: alert_id(16) + user_id(8) + symbol_id(4)
  + target_price(8) + condition/type/flags(4) = ~40 bytes
100M * 40 bytes = 4 GB  (the in-memory matching index, sharded)
Durable record ~150 bytes -> 100M * 150 = ~15 GB source of truth

The standing alert set is small in bytes. The index fits in memory across a handful of matcher nodes. The challenge is never storage - it is the tick rate against it.

Price ticks (the firehose):

Peak stock ticks: ~3,000,000 ticks/sec
Per tick on the wire: symbol_id(4) + price(8) + ts(8) + seq(4) ~= 24-50 bytes
3M * ~50 bytes = ~150 MB/sec = ~1.2 Gbps ingest at peak

3M/sec is the number that kills naive designs. But note the concentration: of 50K symbols, the top ~500 hot symbols carry the large majority of ticks and of alerts. That concentration is both the problem (hot partitions) and the lever (conflation helps most exactly where it is hottest).

Conflation payoff (why we do not evaluate every tick):

We do not need per-tick evaluation. Per symbol, per ~100ms window, all that
matters for crossing is the running (min, max) since the last evaluation.
50K symbols * (1000ms / 100ms) = 500,000 evaluations/sec
vs 3,000,000 raw ticks/sec -> ~6x reduction, and each eval is a cheap
range query, not a full scan.

Conflation turns a 3M/sec firehose into 500K cheap range-queries/sec. For a symbol getting 50K ticks/sec, we collapse those into one (min,max,last) per 100ms and evaluate once - the crossing math is identical (see deep dive 1) but the work drops by orders of magnitude.

Fires (how many alerts actually trigger):

On a normal tick: ~0 alerts fire (thresholds are not being crossed).
On a big market move: a hot symbol crossing a popular round number
  (say AAPL through $250) can fire tens of thousands of alerts in a burst.
Design target: absorb bursts of ~100K-500K fires in a few seconds
  without dropping or double-firing.

Alert writes:

Create/edit/delete: bursty but modest. Say peak ~10K writes/sec
  (a viral stock draws a rush of new alerts). Trivial for a sharded store.

The headline: the standing data is tiny (4GB index), the firehose is enormous (3M ticks/sec), and fires are rare-but-bursty. So we spend our engineering on the tick-to-alert matching path and on absorbing fire bursts, not on storage.

High-Level Design (HLD)

Two independent write paths meet at the matcher. Path A is slow and durable: users create alerts, which land in the Alert Store (source of truth) and are streamed into the in-memory matching index. Path B is the firehose: normalized ticks are partitioned by symbol, conflated into per-symbol windows, and fed to matcher shards that hold every alert for their symbols in a sorted crossing index. When a window crosses thresholds, the matcher emits fire events, which are deduplicated and handed to the notification system.

   ┌─────────────┐   create/edit/delete        ┌──────────────────────┐
   │   Clients   │ ─────────────────────────▶   │   Alert API (CRUD)   │
   │ (app / web) │                              │   stateless, LB'd    │
   └─────────────┘                              └───────┬──────────────┘
                                                        │ write-through
                                          ┌─────────────▼─────────────┐
                                          │  Alert Store (sharded SQL/ │
                                          │  NoSQL) - source of truth  │
                                          └─────────────┬─────────────┘
                                                        │ CDC / change stream
                                                        │ (add/remove/update alert)
   ┌───────────────┐                        ┌───────────▼─────────────────────┐
   │ Price Sources │  raw feeds             │        Matcher Shards            │
   │ exchanges /   │ ─────────┐             │ per shard: in-memory crossing    │
   │ product price │          │             │ index for a slice of symbols:    │
   │ change events │          ▼             │   symbol -> { above: sorted ZSET │
   └───────────────┘  ┌────────────────┐    │              below: sorted ZSET }│
                      │ Ingest /       │    │ + last_price per symbol          │
                      │ Normalizer     │    └───────┬──────────────────────────┘
                      └───────┬────────┘            ▲   │ fire events
                              │ normalized ticks    │   │
                      ┌───────▼────────┐            │   │
                      │ Tick Stream    │  partition │   │
                      │ (Kafka, keyed  │  by symbol │   │
                      │  by symbol_id) │────────────┘   │
                      └───────┬────────┘                │
                              │ per-partition           │
                      ┌───────▼────────┐                │
                      │ Conflation     │  (min,max,last │
                      │ per symbol/100ms│  per window)  │
                      └───────┬────────┘                │
                              └─────────────────────────┘
                                                        │
                                          ┌─────────────▼─────────────┐
                                          │  Trigger / Dedup Service   │
                                          │  once-only per crossing,   │
                                          │  cooldown for recurring    │
                                          └─────────────┬─────────────┘
                                                        │ "fire" events (durable queue)
                                          ┌─────────────▼─────────────┐
                                          │   Notification System      │
                                          │ (push / email / SMS)       │
                                          └────────────────────────────┘

Alert create flow (durable, must not lose):

  1. Client calls POST /alerts with {symbol, condition, target_price, type}.
  2. Alert API validates (symbol exists, target sane), writes the alert to the Alert Store sharded by user_id, returns 201 with the alert_id.
  3. The write is captured by a change stream (CDC) and published to a control topic keyed by symbol_id. The matcher shard that owns that symbol consumes the change and inserts the alert into its sorted crossing index (into the above set if it is an above-alert, the below set otherwise). Edits and deletes flow the same way and mutate the index.

Price match flow (the firehose, must be fast):

  1. Price sources push raw ticks into the Ingest/Normalizer, which maps symbols to internal symbol_id, normalizes price/currency, drops out-of-sequence ticks, and produces onto the Tick Stream partitioned by symbol_id.
  2. Conflation consumes each partition and, per symbol, maintains a running (min, max, last) over a small window (e.g. 100ms). At the end of the window it emits one evaluation event per symbol that moved.
  3. The Matcher Shard owning that symbol takes the window’s (min, max), compares against the symbol’s last_price and the two sorted threshold sets, and pops every alert whose target lies in the crossed range (see deep dive 1). Each popped alert becomes a fire event. It updates last_price.
  4. Fire events go to the Trigger/Dedup Service, which enforces once-only per crossing (idempotency key), applies cooldown for recurring alerts, marks one-shot alerts as fired in the Alert Store, and drops a durable “fire” message onto the notification queue.
  5. The Notification System delivers on the user’s channels.

The structural key: the alert set is indexed by threshold per symbol, and a tick is a range query, not a scan. A price move from 249 to 251 asks each threshold set “give me the alerts between 249 and 251” and touches only those. That is the difference between O(fires) and O(alerts) per tick, and it is why 3M ticks/sec against 100M alerts is tractable.

Component Deep Dive

The hard parts, naive-first then evolved: (1) the crossing-match engine, (2) ingesting and conflating a 3M/sec firehose, (3) hot symbols / hot partitions, (4) exactly-once firing and alert lifecycle.

1. The crossing-match engine

The job: given a symbol’s price moving from p_old to p_new, fire exactly the alerts whose target was crossed, and no others, at millions of ticks per second.

Naive approach: scan the alerts table on every tick

The obvious first cut: for each incoming tick, query the alert store.

on tick(symbol, price):
    rows = db.query(
      "SELECT * FROM alerts WHERE symbol=? AND active"
      " AND ((condition='above' AND ? >= target)"
      "  OR  (condition='below' AND ? <= target))",
      symbol, price, price)
    for r in rows: fire(r)

Where it breaks:

  • It re-fires forever. Once price is above the target, every subsequent tick still matches price >= target, so a one-shot alert would fire on every tick until deactivated, and a recurring one would fire thousands of times a second. The predicate tests “is it past the threshold,” not “did it just cross.” Crossing is the whole point and this loses it.
  • It scans a symbol’s whole alert set per tick. A hot symbol with 200K alerts ticking 50K times/sec is 200K * 50K = 10 billion comparisons/sec for one symbol. Multiply across symbols and it is hopeless.
  • It hammers the database at 3M queries/sec. No relational store survives that.

Testing “is price past target” per tick is both semantically wrong (re-fires) and computationally catastrophic (full scan per tick).

Evolved approach: per-symbol sorted threshold sets + crossing range query

Two fixes, together.

Fix the semantics: compare against last price, fire on crossing. Keep the symbol’s last_price in the matcher. An above-alert with target T fires only when last_price < T <= p_new - the price moved from below the threshold to at/above it. A below-alert fires when last_price > T >= p_new. This fires exactly once per crossing and never re-fires while the price sits past the target.

Fix the cost: index thresholds, query a range. For each symbol, keep two sorted structures keyed by target price (a balanced tree / skip list, or a Redis sorted set / ZSET):

  • above[symbol]: alerts waiting for the price to rise to their target, sorted ascending by target.
  • below[symbol]: alerts waiting for the price to fall to their target, sorted descending by target.

On a price move up from p_old to p_new, every above-alert with target in the half-open interval (p_old, p_new] just got crossed. That is a contiguous slice of the sorted set:

on price move up, symbol S, from p_old to p_new:      # p_new > p_old
    crossed = above[S].range( (p_old, p_new] )        # ZRANGEBYSCORE
    for a in crossed:
        fire(a); above[S].remove(a)                   # one-shot: gone
                                                       # recurring: re-arm (see dive 4)

on price move down, from p_old to p_new:              # p_new < p_old
    crossed = below[S].range( [p_new, p_old) )
    for a in crossed: fire(a); below[S].remove(a)

This is O(log n + k) where k is the number of alerts actually crossed - typically zero. A tick that moves the price a cent touches only the handful of alerts in that cent-wide band. The cost is proportional to fires, not to the standing alert count. That is the core trick of the whole system.

Handling spikes and retraces with (min, max). A price can spike up and fall back inside one conflation window (see deep dive 2). If we only compared last_price to last, we would miss a target that was touched in between. So conflation gives us the window’s (min, max), and we evaluate crossings against the extremes:

window(S) = (min=mn, max=mx, last=lst)
# anything the price rose through this window:
above[S].range( (last_price, mx] )    -> fire (rose to/through target)
# anything it fell through:
below[S].range( [mn, last_price) )    -> fire
last_price = lst

Using (min, max) guarantees no crossing is missed even if the tick that caused it was conflated away - because a crossing is exactly “the target lies between where we were and the furthest the price reached.” Then we set last_price to the window’s closing last.

Where the index lives. In memory in the matcher shards, materialized from the Alert Store via CDC, so a tick evaluation never touches a database. Each shard owns a slice of symbols and holds every alert for those symbols. A ZSET-in-Redis variant works too (and gives durability/replication for free) but an in-process skip list is faster for the hot path; either way the structure and the range query are the same. The matcher is periodically checkpointed and can be rebuilt from the Alert Store on restart.

2. Ingesting and conflating the 3M/sec firehose

The job: get millions of ticks/sec to the right matcher shard, in order, without drowning, and without doing a full evaluation for every single tick.

Naive approach: one stream, evaluate every tick, one consumer

Push every tick onto a single queue and have matchers pull and evaluate each one.

Where it breaks:

  • A single stream/partition is a throughput wall. 3M ticks/sec through one partition is far past what one consumer or one broker partition sustains. You need parallelism, which means partitioning.
  • Per-tick evaluation is wasteful. A hot symbol emits many ticks that move the price a fraction of a cent and cross nothing. Evaluating each one is pure overhead; only the net move over a short window can cross a threshold.
  • Out-of-order and duplicate ticks from redundant feeds cause phantom crossings (a stale low tick after a high one looks like a down-move). Nothing here handles sequencing.

One firehose into one consumer with per-tick work does not scale and does redundant matching.

Evolved approach: partition by symbol, sequence, then conflate per symbol per window

Partition by symbol_id. The Tick Stream (Kafka) is partitioned on symbol_id so all ticks for a symbol land on one partition in arrival order, and partitions spread the 3M/sec across many consumers. A symbol’s alerts live on the matcher shard that consumes that symbol’s partition, so ticks and the alert index are co-located - the matcher never makes a network call to evaluate. Partitioning by symbol also gives per-symbol ordering, which the crossing math needs.

Sequence and de-dupe at ingest. The Normalizer stamps a monotonically increasing per-symbol sequence (from the feed’s own sequence numbers) and drops out-of-order or duplicate ticks, so the matcher sees a clean monotone-in-time price series per symbol. Redundant A/B feeds are merged here.

Conflate per symbol per window. This is the load-shedding lever. Per symbol, maintain a small in-memory window (e.g. 100ms) tracking (min, max, last). Ticks update these three numbers in O(1); at window close we emit one evaluation event. A symbol taking 50K ticks/sec produces ~10 evaluations/sec instead of 50K, and because we carry (min, max) we lose no crossing (deep dive 1). The window length is the latency/CPU trade-off: 100ms keeps p99 fire latency well under our 2s budget while cutting evaluation load ~6x overall and far more on hot symbols.

Tick stream (partitioned by symbol_id)
        │
   ┌────▼─────────────────────────────────────────┐
   │ per partition, per symbol, rolling window:     │
   │   min = min(min, price)                         │
   │   max = max(max, price)                         │
   │   last = price                                  │
   │ every 100ms -> emit (symbol, min, max, last)    │
   └────┬───────────────────────────────────────────┘
        │ one eval per moving symbol per window
   ┌────▼──────┐
   │  Matcher  │ range-query the two threshold sets
   └───────────┘

Product prices (Amazon) come as discrete change events, not a firehose - each one is directly an evaluation against that item’s threshold sets; no conflation needed. The same matcher and index handle both; stocks just need the conflation stage in front because of tick volume.

3. Hot symbols and hot partitions

The job: a handful of symbols (a meme stock, an earnings surprise) carry a wildly disproportionate share of both ticks and alerts. Partition-by-symbol puts all of one symbol on one shard, which becomes a hot spot.

Naive approach: one partition per symbol, one shard owns it

Symbol-keyed partitioning with a fixed hash; whichever shard owns AAPL owns all of AAPL.

Where it breaks:

  • Tick hotness: AAPL alone might be 100K+ ticks/sec. Even after conflation the owning shard does far more work than its peers - a single hot partition pins one CPU while others idle. Kafka cannot split one key’s traffic across partitions.
  • Alert hotness: a viral symbol can accumulate millions of alerts clustered around a few round-number targets. When the price sweeps through that cluster, one shard must pop and fire hundreds of thousands of alerts in a burst, stalling every other symbol it owns.
  • Skew is unpredictable and shifts - today’s hot symbol is not tomorrow’s - so a static hand-tuned assignment does not hold.

A pure hash-by-symbol placement collapses under skew because one symbol cannot be spread.

Evolved approach: sub-shard hot symbols by target range, replicate the tick

Detect hot symbols and split their alert set across multiple matcher shards by target-price range, broadcasting the (conflated) price update to all of them.

  • Alert sub-sharding by target range. For a hot symbol, partition its above/below sets into disjoint price bands - shard 0 holds targets in [0, 100), shard 1 [100, 200), and so on. Each sub-shard holds only its band’s alerts. A price move from 249 to 251 is a range query only on the shard(s) whose band overlaps (249, 251]; the others do nothing. This spreads both the standing alert memory and the fire burst: a sweep through a target cluster is handled by whichever band-shards contain the cluster, in parallel.
  • Tick fan-out. The conflated (min, max, last) for a hot symbol is broadcast to all of that symbol’s band-shards (cheap - one small message replicated N ways). Each shard independently range-queries its band. Because bands are disjoint by target, no alert is double-evaluated.
  • Dynamic promotion/demotion. A lightweight monitor tracks per-symbol tick rate and alert count; when a symbol crosses a threshold it is promoted to sub-sharded mode (and demoted when it cools). Assignment lives in a coordination service (a control topic / etcd) that shards watch, so rebalancing is a config change, not a redeploy.
Hot symbol AAPL, alerts split by target band:
   conflated (min,max,last)
        │  broadcast
   ┌────┼─────────────┬─────────────┐
   ▼    ▼             ▼             ▼
 shard band[0,100) band[100,200) band[200,300) ...
   each: range-query only its band -> fires in parallel

Why this is right: the two axes of hotness (ticks and alerts) are handled by the two moves - broadcasting the tick removes the tick bottleneck (each shard sees one small message per window), and splitting alerts by target band removes the fire-burst bottleneck (a cluster sweep parallelizes). Cold symbols stay in the simple one-shard-per-symbol regime; only the hot few pay the fan-out cost, exactly where it is worth it.

4. Exactly-once firing and alert lifecycle

The job: notify the user once per crossing, never twice, never zero times; handle one-shot vs recurring; keep the durable store and the in-memory index in agreement across crashes.

Naive approach: fire from the matcher, mark done in memory

When a range query pops an alert, call the notification service and delete it from the in-memory set.

Where it breaks:

  • Crash after fire, before mark. If the matcher sends the notification then crashes before persisting “fired,” on restart it rebuilds the index from the store (where the alert is still active) and, on the next crossing, fires again. In-memory-only state is lost on crash.
  • At-least-once everywhere. The tick stream redelivers on consumer restart; a replayed window re-crosses the same thresholds and re-fires. Without an idempotency key the user gets duplicates.
  • Recurring spam. A price hovering exactly at the target crosses up, down, up, down within seconds and fires a recurring alert dozens of times. “Recurring” must not mean “fire on every micro-oscillation.”
  • Lost fires. If we mark the alert fired before the notification is durably enqueued and the enqueue fails, the user never hears about it.

Firing straight from volatile matcher state, with no idempotency and no durable state transition, produces duplicates, spam, and lost fires.

Evolved approach: idempotent fire events, durable state transition, cooldown

Idempotency key per crossing. Each fire event carries fire_id = hash(alert_id, crossing_epoch), where crossing_epoch is a monotonic counter incremented each time the alert transitions from armed to fired (so re-processing the same window yields the same fire_id, but a genuine later crossing yields a new one). The Trigger/Dedup service does SET fire_id NX in a dedup store; a duplicate loses the race and is dropped. This makes matcher redelivery and window replay safe.

Durable state transition before delivery. The order is: matcher emits fire -> Trigger service writes the durable “fired” state and enqueues the notification in one atomic step (or writes fired-pending, enqueues, then confirms), then the notification goes out. The Alert Store is the source of truth for status:

armed --crossing--> firing --(dedup passes, persist)--> fired
                                                          │
                       one-shot: status=fired, removed from index (via CDC)
                       recurring: status=armed again after cooldown, re-inserted

Because the state transition is persisted before we consider the alert done, a crash-and-rebuild reads fired from the store and does not re-fire a one-shot.

One-shot vs recurring.

TypeOn crossingAfter firing
One-shotfire oncemark fired, CDC removes it from the matcher index permanently
Recurringfireenter cooldown (e.g. 15 min or “must retrace by X% and re-cross”); re-arm and re-insert into the index only after cooldown, so oscillation at the threshold cannot spam

The cooldown is the anti-spam mechanism for recurring alerts: after firing, the alert is not re-inserted into the crossing index until either a time window passes or the price has retraced meaningfully away from the target and come back. That converts a jittery oscillation into a single fire.

Keeping index and store in agreement. The Alert Store is authoritative; the in-memory index is a materialized view kept current by CDC. Every lifecycle change (create, edit, delete, fired, re-armed) is a store write that flows to the index. On matcher restart, the index is rebuilt by scanning the store’s active alerts for the shard’s symbols and replaying any CDC changes since the last checkpoint. The dedup store (short TTL, keyed by fire_id) covers the small window where redelivery could double-fire before the state transition lands.

The combination - idempotent fire_id, persist-fired-before-done, and cooldown-gated re-arm - gives exactly-once user-visible firing on top of an at-least-once matching pipeline, survives crashes, and keeps recurring alerts from becoming spam.

API Design & Data Schema

API

Alert management (called by the app):

POST /api/v1/alerts
Body:
{
  "symbol": "AAPL",              // or product_id for Amazon items
  "asset_type": "stock",         // stock | product
  "condition": "above",          // above | below
  "target_price": 250.00,
  "type": "one_shot",            // one_shot | recurring
  "cooldown_sec": 900            // recurring only
}
Response 201:
{ "alert_id": "a_9f3...", "status": "armed", "created_at": "2026-07-18T09:00:00Z" }

GET    /api/v1/alerts?status=armed        -> list the caller's alerts (paginated)
GET    /api/v1/alerts/{id}                -> one alert + last fire time
PATCH  /api/v1/alerts/{id}                -> edit target/condition/pause (status=paused)
DELETE /api/v1/alerts/{id}                -> deactivate (CDC removes from index)

Errors:
  400  unknown symbol / nonsensical target (below-alert with target above current price is allowed but flagged)
  401  unauthenticated
  404  not the caller's alert
  429  too many alerts for this user (per-user cap, e.g. 200 active)

Internal, produced by the matcher (not a public endpoint - a durable event):

FireEvent {
  fire_id:      "hash(alert_id, crossing_epoch)",   // idempotency key
  alert_id:     "a_9f3...",
  user_id:      "42",
  symbol:       "AAPL",
  target_price: 250.00,
  crossed_price:250.13,        // window max/min that triggered
  fired_at:     "2026-07-18T13:32:10.140Z"
}
-> Trigger/Dedup -> notification queue

Data stores

1. Alert Store - source of truth. SQL (sharded) or wide-column NoSQL; I lean SQL here. Alerts are structured, need per-user consistency (you delete it, it stops firing), support user-facing list/filter queries, and the write volume (~10K/sec peak) is easily within a sharded relational cluster. We want the strong read-your-writes guarantee for a user editing their own alerts, which relational gives cleanly. Shard by user_id for the CRUD/list path; a secondary index (or a CDC-fed copy) is organized by symbol_id for the matcher to load.

Table: alerts
  alert_id      UUID       PRIMARY KEY
  user_id       BIGINT     -- shard key for CRUD
  symbol_id     INT        -- indexed; how the matcher loads a symbol's alerts
  asset_type    SMALLINT   -- stock | product
  condition     SMALLINT   -- above | below
  target_price  DECIMAL(18,4)
  type          SMALLINT   -- one_shot | recurring
  status        SMALLINT   -- armed | paused | firing | fired | cooldown
  crossing_epoch INT       -- bumped each fire, feeds fire_id idempotency
  cooldown_sec  INT
  last_fired_at TIMESTAMP  NULL
  created_at    TIMESTAMP
  updated_at    TIMESTAMP
  INDEX (symbol_id, status)           -- matcher load path
  INDEX (user_id, status)             -- user list path

2. Matching index - in-memory, per matcher shard (optionally Redis ZSETs). Not a table so much as a structure: per symbol, two sorted sets keyed by target price. This is the hot path; it is a materialized view of alerts WHERE status=armed, fed by CDC. Rebuildable from the Alert Store.

per symbol_id:
  above[symbol]: SortedSet< target_price -> alert_id >   (ascending)
  below[symbol]: SortedSet< target_price -> alert_id >   (descending)
  last_price[symbol]: DECIMAL
Redis variant: ZADD alerts:above:{symbol} <target> <alert_id>
               ZRANGEBYSCORE alerts:above:{symbol} (p_old p_new   -> crossed

3. Symbol / price cache - KV. symbol_id -> {last_price, currency, tick_size} and the symbol-name-to-id map used by the Normalizer and the Alert API. Read-heavy, tiny, cached in every node.

4. Dedup store - Redis. fire_id -> 1 with a short TTL (minutes), the idempotency backstop for exactly-once firing. Small.

5. Fire log - append-only, TTL’d. A record per fire for the “which of my alerts fired and when” view and for audit. Wide-column / time-series store, sharded by user_id, TTL ~90 days.

Why this split: the alert set is small, structured, edited by users, and needs consistency and rich queries - relational fits and its indexes serve both the user-list and the matcher-load access patterns. The hot matching path deliberately lives in memory (never a DB per tick) and is a rebuildable view. Dedup and price caches are Redis because they are pure key lookups on the latency-critical path. Nothing on the tick path touches durable storage synchronously, which is exactly why 3M ticks/sec is survivable.

Bottlenecks & Scaling

Where it breaks first, in order, and the fix for each.

1. The tick firehose vs a scan (breaks first). 3M ticks/sec against 100M alerts is the thing that kills the naive design. Fix: per-symbol sorted threshold sets turn each evaluation into an O(log n + k) range query touching only crossed alerts, and conflation (per symbol, (min,max,last) per 100ms) cuts 3M ticks/sec to ~500K cheap evaluations/sec with no missed crossings. Cost becomes proportional to fires, not to the alert count.

2. Hot symbols / hot partitions. A meme stock concentrates both ticks and alerts on one shard. Fix: sub-shard hot symbols by target-price band across matcher shards and broadcast the conflated tick to all bands. Ticks stop bottlenecking (each shard sees one small message per window) and fire bursts parallelize across bands. Dynamic promotion/demotion via a coordination service handles shifting skew.

3. Fire bursts. A sweep through a popular round number fires 100K-500K alerts in seconds. Fix: fire events go onto a durable buffered queue, not synchronous notification calls. The Trigger/Dedup service and the notification system drain the burst at their own rate; the matcher is never blocked on delivery. Band sub-sharding spreads the burst across shards.

4. Exactly-once under at-least-once plumbing. Redelivery and window replay would double-fire. Fix: idempotent fire_id = hash(alert_id, crossing_epoch) with a Redis SET NX dedup backstop, and persist the fired state before considering delivery done, so a crash-and-rebuild does not re-fire a one-shot.

5. Index / store divergence and matcher restarts. A shard crash must not lose alerts or double-fire. Fix: the Alert Store is authoritative; the in-memory index is a CDC-fed materialized view, checkpointed and rebuildable by scanning alerts WHERE symbol_id in shard AND status=armed. The dedup store covers the replay window during recovery.

6. Alert store write/read load. 100M alerts, 10K writes/sec, plus the matcher’s bulk load. Fix: shard by user_id for CRUD, keep a symbol_id-organized secondary index (or CDC-built copy) for the matcher load path, and cache the user-list reads. Write volume is modest; this scales horizontally.

7. Conflation window latency vs cost. Too long a window adds fire latency; too short loses the CPU savings. Fix: tune per class - 100ms for liquid stocks keeps p99 fire latency well under 2s while cutting load; product price events skip conflation entirely (they are discrete). Hot symbols can use a shorter window because their bands are parallelized.

8. Out-of-order / duplicate feed data. Redundant A/B feeds cause phantom crossings. Fix: sequence and de-dupe at the Normalizer using the feed’s per-symbol sequence numbers; the matcher only ever sees a clean monotone-in-time series, so last_price comparisons are valid.

9. Single points of failure. Every stateful piece (Kafka, Alert Store, Redis, dedup) is replicated; matcher shards are stateless-over-a-rebuildable-index and run with standby replicas that can take over a partition from the last checkpoint + CDC replay. The notification handoff queue is durable so no fire is lost if delivery is briefly down.

Shard keys, stated plainly: Tick Stream -> partition by symbol_id (co-locates ticks with the symbol’s alert index, preserves per-symbol order). Matcher shards -> own a slice of symbols; hot symbols additionally split by target-price band. Alert Store -> user_id for CRUD, symbol_id secondary for matcher load. Dedup -> fire_id. Fire log -> user_id. Never shard the tick stream by time or by a random key - you would break per-symbol ordering and scatter a symbol’s alerts, defeating the co-location that makes the range query local.

Wrap-Up

The trade-offs that define this design:

  • Index the thresholds, make a tick a range query. The single most important move: per-symbol sorted above/below sets so each price move fires only the alerts in the crossed band, O(fires) not O(alerts). We trade a bit of index-maintenance work on every alert edit for cheap evaluation on every one of millions of ticks.
  • Conflate the firehose, carry (min, max) so nothing is missed. We do not evaluate every tick; we collapse a window into extremes and range-query once. We trade up to ~100ms of latency for a multiple-x reduction in evaluation cost, and the (min,max) guarantees a spike-and-retrace still fires.
  • Compare against last price - crossings, not comparisons. Firing on last_price < T <= p_new instead of price >= T is what stops infinite re-fires and makes “fire once when it crosses” correct.
  • Split hot symbols two ways. Broadcast the tick (kills tick-hotness) and shard alerts by target band (kills fire-burst hotness), only for the hot few, so cold symbols stay simple.
  • Exactly-once via idempotent fire ids + persisted state. At-least-once plumbing underneath, deduplicated by fire_id and made crash-safe by persisting fired before delivery, with cooldown so recurring alerts never spam on oscillation.

One-line summary: a durable alert store feeds a CDC-materialized, per-symbol sorted crossing index in memory; a symbol-partitioned tick firehose is conflated into (min,max,last) windows and range-queried against that index so each price move fires exactly the alerts it crossed; hot symbols are split by target band and broadcast, fires are deduplicated by an idempotent id and persisted before delivery, and the whole thing hands off to a notification system - so 100M alerts and millions of ticks per second resolve to the right fire, once, within a couple of seconds.