+ /3âi žãó„€Rt^RIt^RIHtHt^RIHt^RIt^RIt^RIH t H t ]PPRR4t ] !]4tRtRRR lltR R ltR tR t]P+R4R4t]P+R4R4t]P+R4R4t]R8Xd]P3RRRR7R#R#)u¾ pg_trickle demo — real-time fraud detection dashboard. Serves a single-page web app at http://localhost:8080. JavaScript polls /api/data every 2 seconds and updates all panels in-place. N)ÚdatetimeÚtimezone)ÚDecimal)ÚFlaskÚjsonifyÚ DATABASE_URLz*postgresql://demo:demo@postgres/fraud_democój€\P!\\PPR7#))Úcursor_factory)Úpsycopg2ÚconnectrÚextrasÚRealDictCursor©óÚ=/Users/geir.gronmo/projects/pg-trickle2/demo/dashboard/app.pyÚget_connrs$€Ü × Ò Ü¤X§_¡_×%CÑ%Cô ðrcó$€V^8„dQhR\/#)éÚsql)Ústr)Úformats"rÚ __annotate__rs€÷ ñ œ#ñ rcób€VP4;_uu_4pVPV4VP4uuRRR4# +'giR#;i \dJpTP 4M \dMi;i\ RT 2RR7T;'g.uRp?#Rp?ii;i)Nz[DASHBOARD] Query error: T)Úflush)ÚcursorÚexecuteÚfetchallÚ ExceptionÚrollbackÚprint)ÚconnrÚdefaultÚcurÚexcs&&& rÚ safe_queryr$s„€ð Ø [‰[]Œ]˜cØ K‰K˜Ô Ø—<‘<“>÷]]‹]ûô ôð Ø M‰MOøÜô Ù ð úä Ð)¨#¨Ð/°tÕ<Ø}ˆ}˜"Õûð ús]‚Aš!A» AÁ A ÁAÁAÁ B.Á&A7Á6B)Á7 BÂB)ÂBÂB)Â!B)Â#B.Â)B.có:€V^8„dQhR\\,/#)rÚreturn)ÚlistÚdict)rs"rrr+s€÷ ñ ”tœD•zñ rcó€.pVF‚p/pVP4FXwrE\V\4'd\V4W4&K*\V\4'dVP 4W4&KTWSV&KZ VP V4K„ V#)zAConvert RealDictRows with Decimal/datetime values to plain dicts.)ÚitemsÚ isinstancerÚfloatrÚ isoformatÚappend)ÚrowsÚoutÚrowÚdÚkÚvs& rÚ serializer5+su€à €CÛˆØ ˆØ—I‘I–K‰DˆAܘ!œW×%Ò%ܘQ“x“ܘAœx×(Ò(Ø—{‘{“}“à!“ñ  ð ‰ 1Ž ñð €Jru]i pg_trickle — Real-time Fraud Detection
pg_trickle Real-time Fraud Detection Pipeline
connecting…
—
Total Transactions
—
LOW Risk
—
MEDIUM Risk
—
HIGH Risk
LOW MEDIUM HIGH
âš ï¸ Recent HIGH / MEDIUM Risk Alerts
#UserMerchant CategoryAmountRisk
🪠Merchant Risk Leaderboard
MerchantCat.HighMedRisk%
👤 User Velocity (top 10)
UserðŸŒTxnsAvg $
🌠Country Overview
CountryUsersTxnsVolume $
📦 Category Volume
CategoryTxnsAvg $Users
🔬 Merchant Tier Stats DIFFERENTIAL showcase — change ratio ~0.07
MerchantTierTxnsAvg $Users
Only 1 of 15 rows changes per cycle (1 transaction hits 1 merchant). Tier rotates ~every 30 cycles. The Mode Advisor recommends KEEP DIFFERENTIAL here.
ðŸ·ï¸ Live Merchant Risk Tiers rotates ~every 30 cycles
MerchantCategoryTier
🎯 Alert Summary
Risk LevelTransactionsAvg AmountTotal Volume
🔠Risk Distribution by Category
CategoryLowMediumHighHigh%
âš¡ Stream Table Status
NameModeScheduleStatus
📊 DAG Topology — click to expand 
{{ dag }}
🥠Stream Table Health — pgtrickle.pgt_status()
NameModeSchedulePopulated Last RefreshDurationRows ΔStatus
🌳 Dependency Tree — pgtrickle.dependency_tree()
loading…
📜 Refresh History — pgtrickle.pgt_refresh_history
TableActionStartedmsRows ΔStatus
⚡ Refresh Efficiency — pgtrickle.refresh_efficiency()
TableTotalDIFFFULL Avg DIFF msAvg FULL msSpeedupAvg Change Ratio
🤖 Refresh Mode Advisor — pgtrickle.recommend_refresh_mode()
TableCurrentEffectiveRecommendation ConfidenceReason
uª Base tables Layer 1 — Silver Layer 2 — Gold Layer 3 — Platinum ──────────── ────────────────────── ───────────────────── ────────────────────── ┌────────────┠┌──────────────────┠│ users │────────►│ user_velocity │─────────────────────────►┌──────────────────┠└────────────┘ │ (DIFFERENTIAL) │ │ country_risk │ └──────┬───────────┘ │ (DIFFERENTIAL) │ │ └──────────────────┘ ┌────────────┠│ ┌──────────────────┠│transactions│────────────────┼─►│ merchant_stats │ │ (stream) │ │ │ (DIFFERENTIAL) │ └────────────┘ │ └──────┬───────────┘ │ │ │ │ ┌─────────────┼─────────┘ ↠DIAMOND DEPENDENCY │ │ │ │ â–¼ â–¼ ┌───────────────────────┠│ ┌────────────────────────┠│ alert_summary │ │ │ risk_scores │────────────────────────────────►│ (DIFFERENTIAL) │ │ │ (FULL, calculated) │ └───────────────────────┘ │ └────────────────────────┘ │ ┌───────────────────────┠│ │ top_risky_merchants │ └──────────────────────────────────────────────────────────────►│ (DIFFERENTIAL) │ └───────────────────────┘ ┌────────────┠┌──────────────────┠│ merchants │────────►│ category_volume │ └────────────┘ │ (DIFFERENTIAL) │ └──────────────────┘ ┌───────────────────┠│ merchant_risk_tier│──┠│ (slowly-changing) │ │ ┌─────────────────────┠└───────────────────┘ └─►│ merchant_tier_stats │ ↠DIFFERENTIAL SHOWCASE transactions ─────────────►│ (DIFFERENTIAL 5s) │ change ratio ~0.07 └─────────────────────┘ transactions feeds user_velocity AND merchant_stats — a genuine diamond. risk_scores is the convergence node that joins both Layer 1 outputs. merchant_tier_stats demonstrates low change ratio: only 1/15 rows changes per cycle (1 transaction touches 1 merchant; tier rotates ~every 30 cycles). Ú/có6€\PR\4#)z {{ dag }})ÚHTMLÚreplaceÚ DAG_DIAGRAMrrrÚindexr;ùs€ä <‰<˜ ¤[Ó 1Ð1rz /api/datacóþ€Rp\4p\VR4p\VR4p\VR4p\VR4p\VR4p\VR4p\VR4p\VR4p\VR 4p \VR 4p \R \V4R \V4R \V4R\V4R\V4R\V4R\V4R\V4R\V 4R\V 4/ 4V'dVP 4## \ d<p \R\ T 4/4R3uRp ? T'dTP 4##Rp ? ii;i T'dTP 4ii;i)Na SELECT txn_id, user_name, merchant_name, merchant_category, amount, risk_level FROM risk_scores WHERE risk_level IN ('HIGH', 'MEDIUM') ORDER BY txn_id DESC LIMIT 15 a SELECT risk_level, txn_count, total_amount, avg_amount FROM alert_summary ORDER BY CASE risk_level WHEN 'HIGH' THEN 0 WHEN 'MEDIUM' THEN 1 ELSE 2 END z¶ SELECT merchant_name, merchant_category, total_txns, high_risk_count, medium_risk_count, risk_rate_pct FROM top_risky_merchants z™ SELECT user_name, country, txn_count, total_spent, avg_txn_amount FROM user_velocity WHERE txn_count > 0 zj SELECT country, user_count, total_txns, total_volume FROM country_risk z  SELECT category, txn_count, total_volume, avg_txn_amount, unique_users FROM category_volume WHERE txn_count > 0 ú” SELECT name, status, refresh_mode, is_populated, schedule FROM pgtrickle.pgt_status() ORDER BY name a0 SELECT merchant_category, COUNT(*) FILTER (WHERE risk_level = 'LOW') AS low_count, COUNT(*) FILTER (WHERE risk_level = 'MEDIUM') AS medium_count, COUNT(*) FILTER (WHERE risk_level = 'HIGH') AS high_count, ROUND(100.0 * COUNT(*) FILTER (WHERE risk_level = 'HIGH') / NULLIF(COUNT(*), 0), 1) AS high_pct FROM risk_scores GROUP BY merchant_category ORDER BY high_pct DESC NULLS LAST aW SELECT mts.merchant_id, m.name AS merchant_name, m.category, mts.merchant_tier, mts.txn_count, mts.total_amount, mts.avg_amount, mts.unique_users FROM merchant_tier_stats mts JOIN merchants m ON m.id = mts.merchant_id ORDER BY mts.merchant_id zè SELECT mrt.merchant_id, m.name AS merchant_name, mrt.tier, mrt.updated_at FROM merchant_risk_tier mrt JOIN merchants m ON m.id = mrt.merchant_id ORDER BY mrt.merchant_id Ú recent_alertsÚ alert_summaryÚtop_risky_merchantsÚ user_velocityÚ country_riskÚcategory_volumeÚ st_statusÚrisk_by_categoryÚmerchant_tier_statsÚmerchant_tiersÚerroréô)rr$rr5Úcloserr) r r>r?r@rArBrCrDrErFrGr#s rÚapi_datarKþs¬€à €Dð`Ü‹zˆä" 4ð* ó ˆ ô# 4ð* ó ˆ ô)¨ð0 ó Ðô # 4ð* ó ˆ ô " $ð) ó ˆ ô % Tð, ó ˆô ˜tð& ó ˆ ô & dð - ó  Ðô)¨ð0 ó Ðô$ Dð+ ó ˆôà¤y°Ó'?ؤy°Ó'?Ø%¤yÐ1DÓ'Eؤy°Ó'?ؤy°Ó'>Ø!¤y°Ó'Aؤy°Ó';Ø"¤yÐ1AÓ'BØ%¤yÐ1DÓ'EØ ¤y°Ó'@ð ó  ÷" Ø J‰JLð øô ô1ܘ¤ S£Ð*Ó+¨SÐ0Ô0ç Ø J‰JLð ûð1û÷ Ø J‰JLð ús0„C:DÄ EÄ#EÄ:EÄ;E!ÅEÅE!Å!E<z/api/internalscó €Rp\4p\VR4p\VR4p\VR4p\VR4p\VR4p\VR4p\V4Uu/uF qwR,VbK pp.p \V4Fxp \V 4p V R,V9dLWŠR,,p V P R4V R&V P R 4V R &V P R 4V R &V P V 4Kz \ R T R VUu.uF qwR ,NK upR\V4R\V4R\V4/4V'dVP4##uupiuupi \d<p \ R\T 4/4R3uRp ? T'dTP4##Rp ? ii;i T'dTP4ii;i)Nr=a` SELECT DISTINCT ON (st.pgt_schema, st.pgt_name) st.pgt_schema || '.' || st.pgt_name AS name, h.start_time AS last_refresh, ROUND(EXTRACT(EPOCH FROM (h.end_time - h.start_time)) * 1000)::bigint AS duration_ms, (h.rows_inserted + h.rows_deleted) AS rows_affected FROM pgtrickle.pgt_refresh_history h JOIN pgtrickle.pgt_stream_tables st ON st.pgt_id = h.pgt_id WHERE h.status = 'COMPLETED' ORDER BY st.pgt_schema, st.pgt_name, h.start_time DESC zG SELECT tree_line FROM pgtrickle.dependency_tree() a§ SELECT st.pgt_schema || '.' || st.pgt_name AS name, h.action AS refresh_mode, h.start_time, ROUND(EXTRACT(EPOCH FROM (h.end_time - h.start_time)) * 1000)::bigint AS duration_ms, (h.rows_inserted + h.rows_deleted) AS rows_affected, h.status, h.was_full_fallback FROM pgtrickle.pgt_refresh_history h JOIN pgtrickle.pgt_stream_tables st ON st.pgt_id = h.pgt_id WHERE h.status IN ('COMPLETED', 'FAILED') ORDER BY h.start_time DESC LIMIT 40 zñ SELECT pgt_name AS name, total_refreshes, diff_count, full_count, avg_diff_ms, avg_full_ms, avg_change_ratio, diff_speedup FROM pgtrickle.refresh_efficiency() ORDER BY pgt_name zÖ SELECT pgt_name AS name, current_mode, effective_mode, recommended_mode, confidence, reason FROM pgtrickle.recommend_refresh_mode() ORDER BY pgt_name ÚnameÚ last_refreshÚ duration_msÚ rows_affectedÚ st_healthÚdep_treeÚ tree_lineÚrefresh_historyÚ efficiencyÚ opt_hintsrHrI) rr$r5r(Úgetr.rrJrr)r rQÚ latest_refrRÚ refresh_histrUrVÚrÚlatest_by_nameÚ health_outr1ÚenrichedÚlrr#s rÚ api_internalsr_dsÜ€à €DðOÜ‹zˆä˜tð& ó ˆ ô   ð ' ó  ˆ ô˜dð% ó ˆô" $ð) ó ˆ ô   ð' ó ˆ ô˜tð& ó ˆ ô1:¸*Ô0EÓFÑ0E¨1˜F) Qš,Ñ0EˆÐF؈ ܘYÖ'ˆCܘC“yˆHØ6{˜nÔ,Ø#¨¥KÕ0Ø,.¯F©F°>Ó,B˜Ñ(Ø,.¯F©F°=Ó,A˜Ñ'Ø,.¯F©F°?Ó,C˜Ñ)Ø × Ñ ˜hÖ 'ñ(ôØ ˜zØ ¹ÓA¹°1 +§ ¹ÑAØ œy¨Ó6Ø œy¨Ó4Ø œy¨Ó3ð  ó ÷ Ø J‰JLð ùò+Gùò Bøô ô1ܘ¤ S£Ð*Ó+¨SÐ0Ô0ç Ø J‰JLð ûð1û÷ Ø J‰JLð úsO„AE)Á#EÁ5BE)Ä E$Ä(E)Å E)Å) F/Å4F*Æ F/Æ F2Æ*F/Æ/F2Æ2G Ú__main__z0.0.0.0iF)ÚhostÚportÚdebug)N)Ú__doc__ÚosrrÚdecimalrr Úpsycopg2.extrasÚflaskrrÚenvironrWrÚ__name__Úapprr$r5r8r:Úrouter;rKr_ÚrunrrrÚrnsßðñó ß'ÝãÛß àz‰z~‰~ØÐ@ó€ ñ ˆHƒo€ò÷ õ ð$L €ð\)€ ð\‡ˆ3ƒñ2óð2ð‡ˆ;ÓñbóðbðJ‡Ð ÓñQóðQðh ˆzÔ؇GG ¨U€GÖ3ñr