Data validation developer: MQL recipes

Recipes

Supplier onboarding validation report
Validate a webhook payload
Infer SQL column types from CSV samples
Sign-up form: dates, hours and password
Validate an IoT device registration
Validate an API payload against its contract
French company VAT number and social security key
Password policy with an entropy estimate
A shared catalogue of data quality rules
Cross-field rules of a hotel booking

executed on MentDB

Supplier onboarding validation report

Before the first payment, the accounting team checks a new supplier: IBAN, BIC, SIREN, SIRET, card number, email, phone and website. The script runs one check per field and builds a JSON report listing the failing fields.

Pipeline

01 · bankIBAN and BICtype is_iban→02 · companySIREN and SIRETtype is_siret→03 · cardLuhn checksumtype is_luhn→04 · contactEmail, phone, URLtype is_email→05 · reportErrors arrayjson iarray

Script

admin

#1. A supplier record to check before the first payment;
json load "rx_sup" "{\"name\": \"ACME Réunion\", \"iban\": \"FR76 3000 6000 0112 3456 7890 189\", \"bic\": \"BNPAFRPPXXX\", \"siren\": \"732 829 320\", \"siret\": \"732 829 320 00075\", \"card\": \"4111 1111 1111 1111\", \"email\": \"compta@acme-reunion.re\", \"phone\": \"+262 692 12 34 56\", \"site\": \"https://www.acme-reunion.re\"}";
json load "rx_rep" "{\"errors\": []}";
#2. One check per field, the failing fields go in the errors array;
-> "[checks]" "iban,bic,siren,siret,card,email,phone,site";
-> "[res]" (concat (type is_iban (json select "rx_sup" "/iban")) (type is_bic (json select "rx_sup" "/bic")) (type is_siren (json select "rx_sup" "/siren")) (type is_siret (json select "rx_sup" "/siret")) (type is_luhn (json select "rx_sup" "/card")) (type is_email (json select "rx_sup" "/email")) (type is_phone_fr (json select "rx_sup" "/phone")) (type is_url (json select "rx_sup" "/site")));
for (-> "[i]" 1) (<= [i] 8) (++ "[i]") {
  -> "[f]" (atom get [checks] [i] ",");
  json iobject "rx_rep" / [f] (string mid [res] (- [i] 1) [i]) BOOL2;
  if (== (string mid [res] (- [i] 1) [i]) 0) {json iarray "rx_rep" /errors [f] STR;};
};
#3. Final status of the supplier;
json iobject "rx_rep" / valid (== (json count "rx_rep" /errors) 0) BOOL2;
-> "[out]" (json doc "rx_rep");
json unload "rx_sup";
json unload "rx_rep";
[out]

Result

mentdb

{
  "valid": false,
  "site": true,
  "siren": true,
  "phone": true,
  "iban": true,
  "bic": true,
  "siret": false,
  "errors": [
    "siret"
  ],
  "card": true,
  "email": true
}

Go further

A SIRET starts with the SIREN: compare string left of 9 digits as well
Store the report in a node with the supplier id to keep the history

executed on MentDB

Validate a webhook payload

An API receives a payment webhook with an HMAC signature. The script checks the signature, that the body is strict JSON, then each field: UUID id, known event, boolean flag, currency code length and the IP version.

Pipeline

01 · signHMAC signaturestring hmac_sha256→02 · jsonStrict JSONtype is_json→03 · idUUIDtype is_uuid→04 · enumKnown eventtype is_enum→05 · ipIPv4 or IPv6type is_ipv4

Script

admin

#1. A webhook received by the API with its signature header;
-> "[body]" "{\"id\": \"3f2b8c1e-6a0d-4e5b-9c7a-1d2e3f4a5b6c\", \"event\": \"order.paid\", \"live\": \"true\", \"currency\": \"EUR\", \"ip\": \"203.0.113.25\"}";
-> "[sig]" (string hmac_sha256 [body] "whsec_test_42");
#2. Validate the envelope then each field;
-> "[json_ok]" (type is_json [body]);
json load "rx_wh" [body];
-> "[ip]" (json select "rx_wh" "/ip");
-> "[out]" (concat "{\"json\": " [json_ok] ", \"signature\": " (string equals_ignore_case [sig] (string hmac_sha256 [body] "whsec_test_42")) ", \"id_uuid\": " (type is_uuid (json select "rx_wh" "/id")) ", \"event_known\": " (type is_enum (json select "rx_wh" "/event") "order.paid,order.refunded,order.cancelled") ", \"live_bool\": " (type is_bool (json select "rx_wh" "/live") "true" "false") ", \"currency_3_chars\": " (type is_char (json select "rx_wh" "/currency") 3) ", \"event_max_32\": " (type is_varchar (json select "rx_wh" "/event") 32) ", \"ip_v4\": " (type is_ipv4 [ip]) ", \"ip_v6\": " (type is_ipv6 [ip]) ", \"ipv6_sample\": " (type is_ipv6 "2001:db8::25") ", \"broken_json\": " (type is_json "{\"id\": 1,}") "}");
json unload "rx_wh";
[out]

Result

mentdb

{"json": 1, "signature": 1, "id_uuid": 1, "event_known": 1, "live_bool": 1, "currency_3_chars": 1, "event_max_32": 1, "ip_v4": 1, "ip_v6": 0, "ipv6_sample": 1, "broken_json": 0}

Go further

Reject the call with an HTTP 400 when one check fails and log the field name
Compare the signature in constant time on the sender side

executed on MentDB

Infer SQL column types from CSV samples

A CSV file must be loaded in a new SQL table. For each column, the script tests sample values from the widest to the smallest integer type, or float and decimal, and suggests the smallest SQL type that fits.

Pipeline

01 · numberNumeric or texttype is_number→02 · integerInteger digitstype is_integer→03 · sizeTINYINT to BIGINTtype is_small_int→04 · decimalFLOAT or DECIMALtype is_decimal→05 · walkLoop over the columnsjson parse_obj

Script

admin

#1. Sample values of the columns of a CSV file to load in SQL;
json load "rx_cols" "{\"qty\": [\"12\", \"7\", \"120\"], \"stock\": [\"12000\", \"31000\"], \"visits\": [\"250000\", \"1800000\"], \"bytes\": [\"5000000000\", \"7\"], \"ratio\": [\"0.000125\", \"3.5\"], \"price\": [\"19.90\", \"1250.5\"], \"zip\": [\"97400\", \"97490\"], \"city\": [\"Saint-Denis\", \"Le Port\"]}";
json load "rx_types" "{}";
#2. For each column, the smallest SQL type that fits both sample values;
json parse_obj "rx_cols" "/" "[col]" "[vals]" {
  -> "[v0]" (json select "rx_cols" (concat "/" [col] "[0]"));
  -> "[v1]" (json select "rx_cols" (concat "/" [col] "[1]"));
  -> "[t]" "VARCHAR(255)";
  if (== (concat (type is_number [v0]) (type is_number [v1])) "11") {
    if (== (concat (type is_integer [v0] 19) (type is_integer [v1] 19)) "11") {
      -> "[t]" "BIGINT";
      if (== (concat (type is_int [v0]) (type is_int [v1])) "11") {-> "[t]" "INT";};
      if (== (concat (type is_medium_int [v0]) (type is_medium_int [v1])) "11") {-> "[t]" "MEDIUMINT";};
      if (== (concat (type is_small_int [v0]) (type is_small_int [v1])) "11") {-> "[t]" "SMALLINT";};
      if (== (concat (type is_byte [v0]) (type is_byte [v1])) "11") {-> "[t]" "TINYINT";};
      if (== (concat (type is_big_int [v0]) (type is_big_int [v1])) "00") {-> "[t]" "DECIMAL(20,0)";};
    } {
      -> "[t]" "DOUBLE";
      if (== (concat (type is_float [v0]) (type is_float [v1])) "11") {-> "[t]" "FLOAT";};
      if (== (concat (type is_decimal [v0] 6 2) (type is_decimal [v1] 6 2)) "11") {-> "[t]" "DECIMAL(8,2)";};
    };
  };
  json iobject "rx_types" / [col] [t] STR;
};
-> "[out]" (concat (json doc "rx_types") " double_ok=" (type is_double "0.000125"));
json unload "rx_cols";
json unload "rx_types";
[out]

Result

mentdb

{
  "zip": "MEDIUMINT",
  "visits": "MEDIUMINT",
  "city": "VARCHAR(255)",
  "bytes": "BIGINT",
  "price": "DECIMAL(8,2)",
  "qty": "TINYINT",
  "stock": "SMALLINT",
  "ratio": "FLOAT"
} double_ok=1

Go further

Test all the rows, not two samples, before creating the table
Columns like zip codes are numbers but must stay CHAR: decide with the column name

executed on MentDB

Sign-up form: dates, hours and password

A sign-up form sends a birth date, a French date, an appointment timestamp, opening hours and a password. The script checks formats and real calendar validity (29 February 1990 does not exist), stores a bcrypt hash and verifies the login.

Pipeline

01 · formatDate and timestamp formatstype is_date→02 · validReal calendar datetype is_valid_date→03 · hoursHours with or without secondstype is_hour_without_sec→04 · hashbcrypt hashstring bcrypt_hash→05 · loginVerify the passwordstring bcrypt_verify

Script

admin

#1. A sign-up form with dates, opening hours and a password;
-> "[birth]" "1990-02-29";
-> "[birth_fr]" "18/06/1980";
-> "[rdv]" "2026-11-05 14:30:00";
-> "[open]" "08:30";
-> "[close]" "18:00:00";
-> "[pwd]" "Corr3ct-Horse!";
#2. Store only the bcrypt hash of the password;
-> "[hash]" (string bcrypt_hash [pwd] 10);
#3. Validation report;
concat "{\"birth_iso\": " (type is_date [birth]) ", \"birth_exists\": " (type is_valid_date [birth]) ", \"birth_fr\": " (type is_valid_date [birth_fr] "dd/MM/yyyy") ", \"rdv_format\": " (type is_timestamp [rdv]) ", \"rdv_valid\": " (type is_valid_timestamp [rdv]) ", \"rdv_iso_t\": " (type is_valid_timestamp "2026-11-05T14:30" "yyyy-MM-dd'T'HH:mm") ", \"open_hh_mm\": " (type is_hour_without_sec [open]) ", \"close_hour\": " (type is_hour [close]) ", \"close_time\": " (type is_time [close]) ", \"close_valid\": " (type is_valid_time [close]) ", \"hash_prefix\": \"" (string left [hash] 7) "\", \"login_ok\": " (string bcrypt_verify [pwd] [hash]) ", \"login_wrong\": " (string bcrypt_verify "corr3ct-horse!" [hash]) "}"

Result

mentdb

{"birth_iso": 0, "birth_exists": 0, "birth_fr": 1, "rdv_format": 1, "rdv_valid": 1, "rdv_iso_t": 1, "open_hh_mm": 1, "close_hour": 1, "close_time": 1, "close_valid": 1, "hash_prefix": "$2a$10$", "login_ok": 1, "login_wrong": 0}

Go further

Compute the age with date age once the birth date is valid
A strength of 12 for string bcrypt_hash is slower but safer

executed on MentDB

Validate an IoT device registration

A provisioning tool registers a cold room sensor. Before the insert in the device table, the script checks the serial and MAC formats with regexes, both IP addresses, the label length of the column, the integer sizes, the boolean flag and the registration timestamp, and reports each rule.

Pipeline

01 · formatsSerial and MAC regexestype is_matches_regex→02 · networkIPv4 and IPv6type is_ipv6→03 · columnsVARCHAR, BIGINT, TINYINTtype is_varchar→04 · flagsBoolean flagtype is_bool→05 · timestampFormat and calendartype is_valid_timestamp

Script

admin

#1. A device registration sent by the provisioning tool of an IoT fleet;
json load "rx_dev" "{\"serial\": \"SN-2026-004817\", \"mac\": \"3C:71:BF:4A:9E:02\", \"ipv4\": \"10.12.0.48\", \"ipv6\": \"2001:db8:85a3::8a2e:370:7334\", \"label\": \"Cold room sensor, building C\", \"firmware_build\": \"4294967296\", \"channel\": \"11\", \"secure_boot\": \"yes\", \"registered_at\": \"2026-10-31 25:10:00\"}";
#2. One rule per field, as in the device table of the database;
-> "[r_serial]" (type is_matches_regex (json select "rx_dev" "/serial") "SN-[0-9]{4}-[0-9]{6}");
-> "[r_mac]" (type is_matches_regex (json select "rx_dev" "/mac") "([0-9A-F]{2}:){5}[0-9A-F]{2}");
-> "[r_ip]" (and (type is_ipv4 (json select "rx_dev" "/ipv4")) (type is_ipv6 (json select "rx_dev" "/ipv6")));
-> "[r_label]" (type is_varchar (json select "rx_dev" "/label") 24);
-> "[r_build]" (type is_big_int (json select "rx_dev" "/firmware_build"));
-> "[r_channel]" (type is_byte (json select "rx_dev" "/channel"));
-> "[r_boot]" (type is_bool (json select "rx_dev" "/secure_boot") "true" "false");
-> "[r_ts]" (and (type is_timestamp (json select "rx_dev" "/registered_at")) (type is_valid_timestamp (json select "rx_dev" "/registered_at") "yyyy-MM-dd HH:mm:ss"));
#3. Report;
-> "[out]" (concat "{\"serial\": " [r_serial] ", \"mac\": " [r_mac] ", \"ip\": " [r_ip] ", \"label_24\": " [r_label] ", \"firmware_bigint\": " [r_build] ", \"channel_byte\": " [r_channel] ", \"secure_boot_bool\": " [r_boot] ", \"timestamp\": " [r_ts] "}");
json unload "rx_dev";
[out]

Result

mentdb

{"serial": 1, "mac": 1, "ip": 1, "label_24": 0, "firmware_bigint": 1, "channel_byte": 1, "secure_boot_bool": 0, "timestamp": 0}

Go further

type is_timestamp only checks the shape: 25:10:00 passes the shape test of some tools, type is_valid_timestamp rejects it as an impossible hour
Turn the 0 results into an errors array with json iarray, as an API would return them

executed on MentDB

Validate an API payload against its contract

An order posted to the API is checked against a contract written as data: each rule is a JSON path, an MQL rule run with eval and a message. The lines of the items array get their own rules, and the API answers 422 with every error and its path instead of stopping at the first one.

Pipeline

01 · readValue at a JSON pathjson select→02 · ruleRule stored as texteval→03 · enumAllowed codestype is_enum→04 · itemsFormat of each SKUtype is_matches_regex→05 · errorsErrors with their pathjson iarray

Script

admin

#1. An order received by the API, with errors at several levels;
json load "rx_p4l_in" "{\"order_id\": \"ORD-2026-0042\", \"customer\": {\"email\": \"buyer@shop\", \"country\": \"FR\"}, \"currency\": \"EURO\", \"items\": [{\"sku\": \"BK-0001\", \"qty\": 2, \"unit_price\": 19.9}, {\"sku\": \"bk 2\", \"qty\": 0, \"unit_price\": 5}, {\"sku\": \"BK-0003\", \"qty\": 1}], \"coupon\": null}";
json load "rx_p4l_err" "[]";
#2. Rules of the contract: path, MQL rule with [v], message;
-> "[rules]" "/order_id~type is_matches_regex [v] \"ORD-[0-9]{4}-[0-9]{4}\"~bad order id|/customer/email~type is_email [v]~invalid e-mail|/customer/country~type is_enum [v] \"FR,BE,CH,RE\"~country not served|/currency~type is_enum [v] \"EUR,USD\"~ISO 4217 code expected";
-> "[n]" (atom size [rules] "|");
for (-> "[i]" 1) (<= [i] [n]) (++ "[i]") {
	-> "[rule]" (atom get [rules] [i] "|");
	-> "[path]" (atom get [rule] 1 "~");
	-> "[v]" (json select "rx_p4l_in" [path]);
	#3. eval runs the rule written as text on the value;
	if (or (is null [v]) (not (eval (concat (atom get [rule] 2 "~") ";")))) {
		json iarray "rx_p4l_err" "/" (concat "{\"path\": \"" [path] "\", \"value\": \"" (string json_escape [v]) "\", \"error\": \"" (atom get [rule] 3 "~") "\"}") OBJ;
	};
};
#4. Rules on each line of the items array;
for (-> "[j]" 0) (< [j] (json count "rx_p4l_in" "/items")) (++ "[j]") {
	-> "[p]" (concat "/items[" [j] "]");
	if (not (type is_matches_regex (json select "rx_p4l_in" (concat [p] "/sku")) "[A-Z]{2}-[0-9]{4}")) {json iarray "rx_p4l_err" "/" (concat "{\"path\": \"" [p] "/sku\", \"error\": \"format AA-9999\"}") OBJ;};
	if (not (and (type is_integer (json select "rx_p4l_in" (concat [p] "/qty")) 3) (> (json select "rx_p4l_in" (concat [p] "/qty")) 0))) {json iarray "rx_p4l_err" "/" (concat "{\"path\": \"" [p] "/qty\", \"error\": \"integer from 1 to 999\"}") OBJ;};
	if (is null (json select "rx_p4l_in" (concat [p] "/unit_price"))) {json iarray "rx_p4l_err" "/" (concat "{\"path\": \"" [p] "/unit_price\", \"error\": \"required\"}") OBJ;};
};
#5. The answer of the API: 422 with every error, not only the first;
-> "[nb]" (json count "rx_p4l_err" "/");
-> "[out]" (concat "{\"code\": " (if (== [nb] 0) {200} {422}) ", \"errors\": " (string replace (string replace (json doc "rx_p4l_err") [_n_] "") "  " "") "}");
json unload "rx_p4l_in";
json unload "rx_p4l_err";
string replace [out] "}," (concat "}," [_n_]);

Result

mentdb

{"code": 422, "errors": [{"path": "/customer/email","error": "invalid e-mail","value": "buyer@shop"},
{"path": "/currency","error": "ISO 4217 code expected","value": "EURO"},
{"path": "/items[1]/sku","error": "format AA-9999"},
{"path": "/items[1]/qty","error": "integer from 1 to 999"},
{"path": "/items[2]/unit_price","error": "required"}]}

Go further

Keep the rules in a node per API version so the contract changes without a new deployment.
Use script show params of the service to generate the first rules from the typed parameters.

executed on MentDB

French company VAT number and social security key

Check the identifiers of a supplier and an employee: the SIREN and the SIRET with Luhn (a typo in the last digit is caught), the intra-community VAT number recomputed from the SIREN with its mod 97 key, and the key of a NIR social security number, then decode what the NIR says.

Pipeline

01 · cleanKeep only the digitsstring regexp_replace→02 · sirenLuhn of the SIRENtype is_siren→03 · vatKey 12 + 3 x (SIREN mod 97)math mod→04 · siretLuhn of the SIRETtype is_siret→05 · nirNIR key 97 - n mod 97bi-

Script

admin

#1. Identifiers typed in a supplier and an employee form;
-> "[siren]" (string regexp_replace "732 829 320" "[^0-9]" "");
-> "[vat_typed]" "FR44 732829320";
-> "[nir]" "2 84 05 75 123 456 72";
#2. SIREN: Luhn check, then the French VAT key: 12 + 3 x SIREN mod 97, mod 97;
-> "[siren_ok]" (type is_siren [siren]);
-> "[key]" (math mod (+ 12 (* 3 (math mod [siren] 97))) 97);
-> "[vat]" (concat "FR" (string lpad [key] "0" 2) [siren]);
-> "[vat_clean]" (string upper (string regexp_replace [vat_typed] "[^A-Za-z0-9]" ""));
#3. SIRET = SIREN + 5 digits, Luhn again: a typo on the last digit is caught;
-> "[siret_typo]" (concat [siren] "00075");
-> "[siret]" (concat [siren] "00074");
#4. Social security number NIR: key = 97 - the 13 digits mod 97, big integers;
-> "[n13]" (string left (string regexp_replace [nir] " " "") 13);
-> "[nir_key]" (bi- 97 (math mod [n13] 97));
-> "[nir_typed_key]" (string right (string regexp_replace [nir] " " "") 2);
#5. What the digits of the NIR say;
-> "[sex]" (if (equal (string left [n13] 1) "1") {"man"} {"woman"});
-> "[birth]" (concat "19" (string substring [n13] 1 3) "-" (string substring [n13] 3 5));
concat "{\"siren\": \"" [siren] "\", \"siren_luhn\": " [siren_ok] ", \"vat_computed\": \"" [vat] "\", \"vat_typed\": \"" [vat_clean] "\", \"vat_ok\": " (equal [vat] [vat_clean]) ", \"siret\": \"" [siret] "\", \"siret_ok\": " (type is_siret [siret]) ", \"siret_typo_ok\": " (type is_siret [siret_typo]) ", \"nir_key_computed\": " [nir_key] ", \"nir_key_typed\": " [nir_typed_key] ", \"nir_ok\": " (== [nir_key] [nir_typed_key]) ", \"nir_says\": \"" [sex] " born " [birth] " in department " (string substring [n13] 5 7) "\"}";

Result

mentdb

{"siren": "732829320", "siren_luhn": 1, "vat_computed": "FR44732829320", "vat_typed": "FR44732829320", "vat_ok": 1, "siret": "73282932000074", "siret_ok": 1, "siret_typo_ok": 0, "nir_key_computed": 72, "nir_key_typed": 72, "nir_ok": 1, "nir_says": "woman born 1984-05 in department 75"}

Go further

Check the VAT number online with the VIES service of the EU, through rest https get, once the key is right.
For Corsica, the NIR department 2A and 2B are replaced by 19 and 18 before the mod 97.

executed on MentDB

Password policy with an entropy estimate

At sign-up, each proposed password is checked against a policy: at least 12 characters, not in a list of common passwords, not containing the login, no 3 identical characters in a row, and at least 60 bits of entropy estimated from the character classes. Only the bcrypt hash of the accepted one is kept.

Pipeline

01 · classesLower, upper, digits, othersstring regexp_count→02 · entropylength x log2 of the alphabetmath log_base→03 · loginLogin inside the passwordstring contains_ignore_case→04 · repeatSame char 3 timesstring regexp→05 · hashStore the hash onlystring bcrypt_hash

Script

admin

#1. Passwords proposed at sign-up, and the login of the user;
-> "[login]" "marie.dupont";
-> "[candidates]" "azerty123|Marie.Dupont2026!|Tr0ub4dor&3|aaaaBBBB1111!!|correct-horse-battery-staple-42";
-> "[common]" "123456,password,azerty,azerty123,qwerty,admin,iloveyou";
-> "[report]" "";
for (-> "[i]" 1) (<= [i] (atom size [candidates] "|")) (++ "[i]") {
	-> "[p]" (atom get [candidates] [i] "|");
	-> "[problems]" "";
	#2. Character classes and size of the alphabet used;
	-> "[lower]" (string regexp_count [p] "[a-z]");
	-> "[upper]" (string regexp_count [p] "[A-Z]");
	-> "[digit]" (string regexp_count [p] "[0-9]");
	-> "[other]" (- (string length [p]) (+ (+ [lower] [upper]) [digit]));
	-> "[pool]" (+ (+ (if (> [lower] 0) {26} {0}) (if (> [upper] 0) {26} {0})) (+ (if (> [digit] 0) {10} {0}) (if (> [other] 0) {33} {0})));
	#3. Entropy in bits = length x log2 of the alphabet;
	-> "[bits]" (math round (* (string length [p]) (math log_base [pool] 2)) 0);
	#4. Policy rules;
	if (< (string length [p]) 12) {-> "[problems]" (concat [problems] "under 12 chars, ");};
	if (> (atom find [common] (string lower [p]) ",") 0) {-> "[problems]" (concat [problems] "in the common list, ");};
	if (string contains_ignore_case (string regexp_replace [p] "[^A-Za-z]" "") (string regexp_replace [login] "[^A-Za-z]" "")) {-> "[problems]" (concat [problems] "contains the login, ");};
	if (string regexp [p] ".*(.)\1\1.*") {-> "[problems]" (concat [problems] "3 identical chars in a row, ");};
	if (< [bits] 60) {-> "[problems]" (concat [problems] "entropy under 60 bits, ");};
	-> "[verdict]" (if (is empty [problems]) {"ACCEPTED"} {concat "REFUSED: " (string substring [problems] 0 (- (string length [problems]) 2))});
	-> "[report]" (concat [report] (string rpad (string truncate [p] 20) " " 21) (string lpad [bits] " " 3) " bits  " [verdict] [_n_]);
};
#5. Only the bcrypt hash of an accepted password is stored;
-> "[hash]" (string bcrypt_hash "correct-horse-battery-staple-42" 10);
concat [report] "stored: " (string left [hash] 7) "... (" (string length [hash]) " chars), verify: " (string bcrypt_verify "correct-horse-battery-staple-42" [hash]);

Result

mentdb

azerty123             47 bits  REFUSED: under 12 chars, in the common list, entropy under 60 bits
Marie.Dupont2026!    112 bits  REFUSED: contains the login
Tr0ub4dor&3           72 bits  REFUSED: under 12 chars
aaaaBBBB1111!!        92 bits  REFUSED: 3 identical chars in a row
correct-horse-bat... 189 bits  ACCEPTED
stored: $2a$10$... (60 chars), verify: 1

Go further

Load a real list of the 10,000 most common passwords with file load and test it with atom find.
Return all the problems at once so the user fixes the password in one try.

executed on MentDB

A shared catalogue of data quality rules

Register reusable rules in the data quality catalogue of the server (Reunion zip code, Reunion mobile number, positive amount with 2 decimals), then score the columns of a customer extract: each column reads its rule from the catalogue and runs it on every value with eval. The tutorial rules are removed at the end.

Pipeline

01 · setStore a ruledq algorithm set→02 · getRead the rule of a columndq algorithm get→03 · runRun it on each valueeval→04 · ruleDecimal with 2 digitstype is_decimal→05 · cleanCheck and removedq algorithm remove

Script

admin

#1. Register reusable rules in the data quality catalogue, [VAR] is the value to test;
dq algorithm set "tuto_p4l_zip_re" (mql {type is_matches_regex [VAR] "974[0-9]{2}";});
dq algorithm set "tuto_p4l_mobile_re" (mql {type is_matches_regex (string regexp_replace [VAR] "[ .]" "") "(\+262|0)69[23][0-9]{6}";});
dq algorithm set "tuto_p4l_amount" (mql {and (type is_decimal [VAR] 8 2) (>= [VAR] 0);});
#2. Columns of a customer extract and the rule of each column;
json load "rx_p4l_cols" "{\"zip\": [\"97400\", \"97490\", \"9740\", \"75011\"], \"mobile\": [\"0692 12 34 56\", \"+262693001122\", \"0612345678\", \"06.92.40.50.60\"], \"balance\": [\"120.50\", \"-15\", \"1e3\", \"99.999\"]}";
-> "[map]" "zip=tuto_p4l_zip_re,mobile=tuto_p4l_mobile_re,balance=tuto_p4l_amount";
-> "[report]" "";
for (-> "[c]" 1) (<= [c] (atom size [map] ",")) (++ "[c]") {
	-> "[col]" (atom get (atom get [map] [c] ",") 1 "=");
	-> "[algo]" (atom get (atom get [map] [c] ",") 2 "=");
	#3. Read the rule from the catalogue and run it on every value;
	-> "[code]" (dq algorithm get [algo]);
	-> "[ok]" 0;
	-> "[bad]" "";
	-> "[n]" (json count "rx_p4l_cols" (concat "/" [col]));
	for (-> "[i]" 0) (< [i] [n]) (++ "[i]") {
		-> "[VAR]" (json select "rx_p4l_cols" (concat "/" [col] "[" [i] "]"));
		if (eval [code]) {++ "[ok]";} {-> "[bad]" (concat [bad] " '" [VAR] "'");};
	};
	-> "[report]" (concat [report] (string rpad [col] " " 8) (math round (* (/ [ok] [n]) 100) 0) "% valid, rule " [algo] ", rejected:" [bad] [_n_]);
};
json unload "rx_p4l_cols";
#4. The catalogue is shared: check then remove the tutorial rules;
-> "[had]" (dq algorithm exist "tuto_p4l_amount");
dq algorithm remove "tuto_p4l_zip_re";
dq algorithm remove "tuto_p4l_mobile_re";
dq algorithm remove "tuto_p4l_amount";
concat [report] "rule existed: " [had] ", after cleanup: " (dq algorithm exist "tuto_p4l_amount");

Result

mentdb

zip     50% valid, rule tuto_p4l_zip_re, rejected: '9740' '75011'
mobile  75% valid, rule tuto_p4l_mobile_re, rejected: '0612345678'
balance 50% valid, rule tuto_p4l_amount, rejected: '-15' '99.999'
rule existed: 1, after cleanup: 0

Go further

dq algorithm show lists the rules already in the catalogue: reuse them before writing a new one.
Run the same scoring on a SQL table with dq analyse to get the report per column.

executed on MentDB

Cross-field rules of a hotel booking

Every field of a hotel booking is valid alone, but the fields contradict each other: departure before arrival, a 19-year-old counted as a child, more guests than the room holds, a payment split of 99.99 %. The script checks the rules between the fields and returns all the errors for the form.

Pipeline

01 · orderDeparture after arrivaldate is_after→02 · nightsNumber of nightsdate nb_day→03 · capacityCapacity of the roomswitch→04 · promoPromo perioddate between→05 · splitExact sum of the splitbd+

Script

admin

#1. A hotel booking: each field is valid alone, the errors are between the fields;
json load "rx_p4l_bk" "{\"today\": \"2026-11-01\", \"arrival\": \"2026-12-20\", \"departure\": \"2026-12-18\", \"room\": \"double\", \"adults\": 2, \"children\": 2, \"children_ages\": \"4,19\", \"promo\": \"XMAS26\", \"split\": \"33.33,33.33,33.33\"}";
-> "[a]" (json select "rx_p4l_bk" "/arrival");
-> "[d]" (json select "rx_p4l_bk" "/departure");
-> "[errors]" "";
#2. Dates: departure after arrival, 1 to 30 nights, booking in the future;
if (not (date is_after [d] [a])) {-> "[errors]" (concat [errors] "departure must be after arrival|");};
-> "[nights]" (date nb_day [a] [d]);
if (or (< [nights] 1) (> [nights] 30)) {-> "[errors]" (concat [errors] "stay of " [nights] " nights, 1 to 30 expected|");};
if (date is_before [a] (json select "rx_p4l_bk" "/today")) {-> "[errors]" (concat [errors] "arrival in the past|");};
#3. Guests: ages must match the count, a child is under 18, capacity of the room;
-> "[ages]" (json select "rx_p4l_bk" "/children_ages");
if (!= (atom size [ages] ",") (json select "rx_p4l_bk" "/children")) {-> "[errors]" (concat [errors] "children count and ages differ|");};
for (-> "[i]" 1) (<= [i] (atom size [ages] ",")) (++ "[i]") {
	if (>= (atom get [ages] [i] ",") 18) {-> "[errors]" (concat [errors] "child " [i] " is " (atom get [ages] [i] ",") ", book as adult|");};
};
-> "[capacity]" (switch ((json select "rx_p4l_bk" "/room")) ("single") {1} ("double") {3} ("family") {5} {0});
-> "[guests]" (+ (json select "rx_p4l_bk" "/adults") (json select "rx_p4l_bk" "/children"));
if (> [guests] [capacity]) {-> "[errors]" (concat [errors] [guests] " guests for a room of " [capacity] "|");};
#4. Promo valid only for arrivals from Dec 1 to Dec 31, payment split must be exactly 100;
if (and (equal (json select "rx_p4l_bk" "/promo") "XMAS26") (not (date between [a] "2026-12-01" "2026-12-31"))) {-> "[errors]" (concat [errors] "promo XMAS26 not valid on these dates|");};
-> "[split]" (json select "rx_p4l_bk" "/split");
-> "[sum]" 0;
for (-> "[i]" 1) (<= [i] (atom size [split] ",")) (++ "[i]") {-> "[sum]" (bd+ [sum] (atom get [split] [i] ","));};
if (not (== [sum] 100)) {-> "[errors]" (concat [errors] "payment split is " [sum] "%, not 100%|");};
json unload "rx_p4l_bk";
#5. All the errors at once for the form;
-> "[list]" (string left [errors] (- (string length [errors]) 1));
if (is empty [errors]) {"booking valid";} {concat (atom size [list] "|") " errors:" [_n_] "- " (string replace [list] "|" (concat [_n_] "- "));};

Result

mentdb

5 errors:
- departure must be after arrival
- stay of -2 nights, 1 to 30 expected
- child 2 is 19, book as adult
- 4 guests for a room of 3
- payment split is 99.99%, not 100%

Go further

Sum percentages with bd+: three times 33.33 is 99.99, the form must give 33.34 to one payer.
Run the same function in the browser and on the server: the server check is the one that counts.