Halcyon Pay: Picking a Store for High-Rate Fraud Events
Halcyon Pay processes card authorisations for 4,000 merchants. Its scoring service must return a decision in under 200 ms at p99, peaking at 2,500 events per second during the holiday quarter and averaging 700. Each event carries a variable set of device and merchant attributes whose shape changes every few months as new fraud signals are added. Investigators need to query the last 90 days of events by card, by merchant and by time window; older events go to Object Storage for seven years. Reference data - merchant risk tiers and card status - lives in an on-premises Oracle 19c core and changes about 50,000 rows a day. The current stack is Kafka plus a self-managed Cassandra cluster the team wants to retire. Leadership has asked whether Autonomous JSON Database, Oracle NoSQL Database, or Autonomous Transaction Processing should own the event records, and how reference data should reach the cloud.
1. Which of the three Oracle services should own the event records, and what would change your answer?
Read the worked answer
Oracle NoSQL Database Cloud Service should own the events.
- The attributes change shape every few months; a schema-less document store absorbs that without migrations.
- Scoring is a single key lookup inside a 200 ms p99 budget. NoSQL's provisioned-capacity model delivers predictable single-digit-millisecond reads without a per-request connection-pool handshake.
- The 90-day retention is a TTL on the table, so expiry is not a scheduled job someone forgets.
- Capacity scales by adding throughput units, which matches a 700-to-2,500 events-per-second holiday curve.
Autonomous JSON Database is the serious alternative and would win if the scoring logic needed multi-document transactions or a SQL join to reference data inside the same statement - it offers JSON with full SQL and ACID. The cost is an Oracle connection model and per-ECPU concurrency limits under a 2,500 events-per-second fan-out.
ATP is the weakest fit: variable fraud signals would force constant schema changes against a relational model built for stable entities.
2. Design the path from the on-premises Oracle 19c core to the cloud. Where does GoldenGate fit for each consumer?
Read the worked answer
There are two consumers with different requirements, and pretending they are one is where these designs fail.
Scoring needs the merchant risk tier and card status at decision time. Replicate those reference tables into NoSQL so scoring performs one lookup instead of a cross-system call. Fifty thousand changed rows a day is well within GoldenGate's comfort zone, and GoldenGate has a NoSQL target handler. The decision path must never block on the source database.
Investigation needs SQL joins over 90 days of events plus reference history. Replicate the same reference data with GoldenGate into ADW (or Autonomous JSON) so fraud analysts work against a consistent snapshot with history rather than today's row images.
If only one path can be funded first, build the NoSQL one - it protects the latency SLO. Be explicit that investigators then cannot join reference data in-database, which is usually unacceptable for fraud review, so plan the ADW copy to be fed by the same extract rather than rebuilding it later.
3. Investigators query by card, by merchant and by time window. How would you key the NoSQL table, and what breaks?
Read the worked answer
Design around the dominant access path, then buy back the others deliberately.
- Primary key (cardId, eventTime) makes the most common investigation - 'everything on this card in the last 90 days' - a single-shard range scan, which is what the latency budget permits.
- Merchant-centric queries do not share that shard key. A secondary index on merchantId works, but it becomes a fan-out across shards. That is fine for an investigator waiting on a screen and not fine if merchant lookup ever becomes real-time.
- If merchant lookup is on the hot path, write a second table keyed (merchantId, eventTime) from the stream consumer. Duplicating on write is cheaper and more predictable than a cross-shard index at 2,500 events per second.
- Watch for hot shards. Hashing on cardId spreads load well; hashing on a low-cardinality attribute such as merchant region does not.
- Set the TTL on eventTime, not ingest time, so late-arriving events are not deleted early and investigators keep the full 90-day window.