You might try thinking about using a pure KV solution leveraging Couchbase eventing. I think you will find that this real time solution essentially coding up a lambda or a trigger will be very performant across 10's of millions of documents. To this end I will give a concrete example below:
You seem to have two types of documents
First a sales rep authorization list here is essentially your data as JSON docs
KEY auth:1003
{
"Products": [
{
"EndDt": "9999-12-25",
"ProductId": 1,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 2,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 8,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 9,
"StartDt": "2022-05-15"
}
],
"id": 1003,
"type": "auth"
}
KEY auth:1002
{
"Products": [
{
"EndDt": "9999-12-25",
"ProductId": 1,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 2,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 5,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 6,
"StartDt": "2022-05-15"
}
],
"id": 1002,
"type": "auth"
}
KEY auth:1001
{
"Products": [
{
"EndDt": "9999-12-25",
"ProductId": 1,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 2,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 3,
"StartDt": "2022-05-15"
},
{
"EndDt": "9999-12-25",
"ProductId": 4,
"StartDt": "2022-05-15"
}
],
"id": 1001,
"type": "auth"
}
Second a bunch of orders coming in that you want to validate here is essentially your data as JSON docs (I took the liberty of adding one more to get a success)
KEY: order:1234
{
"ProductIds": [
1,2,3,4,5
],
"RepID": 1001,
"id": 1234,
"type": "order"
}
KEY: order:1111
{
"ProductIds": [
1,2,3,4
],
"RepID": 1003,
"id": 1111,
"type": "order"
}
KEY: order:2222
{
"ProductIds": [
8,9
],
"RepID": 1003,
"id": 2222,
"type": "order"
}
KEY: order:100
{
"ProductIds": [
1,2,3
],
"RepID": 1002,
"id": 100,
"type": "order"
}
Now here is an Eventing function (it will run in 6.X and 7.X mode although 7.X will be much faster if you take advantage of bucket backed caching)
// Need two buckets (if 7.0+ keyspaces of _default._default)
// "eventing"
// "data"
// Need one bucket binding
// alias = src_col bucket = data mode = r+w
// For performance set workers to 2X VCPUs for large data sets
// or for very fast mutation rates.
function OnUpdate(doc, meta) {
// only process and validate orders (might add more filters here).
if (doc.type !== "order") return;
// level 1 is what you want, else to look at issue just raise the #
var DEBUG = 1;
// Use bucket backed caching to speed up loading of check document by 25X
var VERSION_AT_702 = false;
if (DEBUG > 1) log("checking order", meta.id);
// load the rep's authorized products fromthe bucket binding.
var auths;
if (VERSION_AT_702 == false) {
auths = src_col["auth:" + doc.RepID];
} else {
// use bucket backed caching. Will only read KV at most once per
// second per each Eventing node. Costs just 1/25th of a std. Bucket Op.
var result = couchbase.get(src_col,{"id": "auth:" + doc.RepID}, {"cache": true});
if (!result.success) {
auths = null;
} else {
auths = result.doc;
}
}
if (!auths) {
if (DEBUG > 0) log("no auth record found for RepID", doc.RepID);
return;
}
if (DEBUG > 4) log(auths);
// since I save the lists this isn't an optimal check
var is_authed = [];
var is_not_authed = [];
// now make sure the rep is authorized to sell all products
for (var k = 0; k < doc.ProductIds.length; k++){
var prod = doc.ProductIds[k];
if (DEBUG > 1) log("checking product",prod);
var okay = false;
for (var j = 0; j < auths.Products.length; j++){
var auth = auths.Products[j];
if (DEBUG > 6) log("\t1.",auth);
if (auth.ProductId == prod) {
if (DEBUG > 8) log("\t\t2.",auth.ProductId," === ", prod, "GOOD");
okay = true;
} else {
if (DEBUG > 8) log("\t\t2.",auth.ProductId," === ", prod, "BAD");
}
}
if (okay === false) {
is_not_authed.push(prod);
} else {
is_authed.push(prod);
}
if (DEBUG > 5) log("prod",prod,"authed",okay);
}
// =====================================================
// we have an issue id is_not_authed.length > 0
//======================================================
if (is_not_authed.length > 0) {
if (DEBUG > 0) log("BAD illegal order", meta.id, "rep", doc.RepID, "can sell products", is_authed, "but can't sell products", is_not_authed);
} else {
if (DEBUG > 0) log("VALID legal order", meta.id, "rep", doc.RepID, "can sell products", is_authed);
}
// =====================================================
// we could move the document or modify it but that's
// you business logic. Typically we might do something like:
// 1. update the document with a new tag.
// doc.verify_status = (is_not_authed.length == 0)
// src_col[meta.id] = doc;
// 2. at the top of the Function add another filter to
// prevent redoing the same work.
// if (doc.verify_status) return;
//======================================================
}
Running the above Eventing function against the above data I get the following log messages.
2022-08-03T19:14:50.936+00:00 [INFO] "BAD illegal order" "order:1111" "rep" 1003 "can sell products" [1,2] "but can't sell products" [3,4]
2022-08-03T19:14:50.848+00:00 [INFO] "BAD illegal order" "order:100" "rep" 1002 "can sell products" [1,2] "but can't sell products" [3]
2022-08-03T19:14:50.812+00:00 [INFO] "VALID legal order" "order:2222" "rep" 1003 "can sell products" [8,9]
2022-08-03T19:14:50.797+00:00 [INFO] "BAD illegal order" "order:1234" "rep" 1001 "can sell products" [1,2,3,4] "but can't sell products" [5]
Of course you want to do something other than log a message perhaps you want to move the document, add or update a property in the document, or do other actions after all your working with pure JavaScript with KV (or Data Service) access to your data in Couchbase.
Note in the above code I kept lists of what "can" and "cannot" be sold, but if you don't need that you can optimize the loop via breaks (JavaScript v8 is fast) but I do see that at your scale efficiency is key.
Maybe break out the Products into three arrays then you could do the following:
KEY auth:1001
{
"id": 1001,
"type": "auth",
"Product": [ 1, 2, 3, 4 ],
"StartDt": [ "2022-05-15", "2022-05-15", "2022-05-15", "2022-05-15" ],
"StartDt": [ "9999-12-25", "9999-05-15", "9999-12-25", "9999-12-25" ]
}
The eliminate the for loops:
const includesAll = (arr, values) => values.every(v => arr.includes(v));
log(meta.id,includesAll(auths.Product, doc.ProductIds));
If the intersection "work" is too lengthy look into things like FastBitSet.js to lower the analysis time.
The easiest way to increase the performance is to enable the bucket backed cache (requires version 7.0.2 or greater), however if you don't have reuse this won't help. BTW emitting log messages will also slow things down too so avoid that.
IMHO you should be able to process 100K docs/second on a small cluster and up to 1M docs/sec on a large tuned cluster.
If you are not familiar with the Eventing Service you should run a few "step by step" examples first to get a basic understanding.
If for some reason you need more performance (I don't think you will) there are a few advanced Eventing tricks that I can share to speed things up even no - just DM me and we'll schedule some time to talk.
