# Fraud Detection and Data Lake Roadmap

## Assumption

This roadmap assumes you meant:

- `Fraud detection`
- `Data lake`

If you actually meant `data leak prevention (DLP)`, that should be treated as a separate security roadmap.

## Why this fits this repo

You do not have a real fraud engine yet, but you already have strong building blocks:

- Multi-tenant isolation by `app_id`
- ClickHouse for high-volume analytical/event reads
- PostgreSQL for transactional state
- Redis + Horizon for async processing
- GCS-backed storage already in use
- GCP logging integration
- Existing risk/churn/AI pipelines and admin dashboards

Good reuse points:

- [app/Services/Churn/ChurnClickhouseService.php](/var/www/php84/sms_admin_es/app/Services/Churn/ChurnClickhouseService.php)
- [app/Services/AiChurnDataService.php](/var/www/php84/sms_admin_es/app/Services/AiChurnDataService.php)
- [app/Services/Billing/GatewayHealthMonitorService.php](/var/www/php84/sms_admin_es/app/Services/Billing/GatewayHealthMonitorService.php)
- [app/Services/Subscriber360/SubscriberJourneyClickhouseService.php](/var/www/php84/sms_admin_es/app/Services/Subscriber360/SubscriberJourneyClickhouseService.php)
- [app/Services/Monitoring/DataFreshnessMonitorService.php](/var/www/php84/sms_admin_es/app/Services/Monitoring/DataFreshnessMonitorService.php)
- [app/Services/Logs/GcpLoggingService.php](/var/www/php84/sms_admin_es/app/Services/Logs/GcpLoggingService.php)
- [app/Services/ActivationCode/ActivationCodeClickhouseService.php](/var/www/php84/sms_admin_es/app/Services/ActivationCode/ActivationCodeClickhouseService.php)
- [app/Filament/Pages/RiskManagement/RiskManagement.php](/var/www/php84/sms_admin_es/app/Filament/Pages/RiskManagement/RiskManagement.php)
- [app/Filament/Pages/AnalyticsAndAdmin/SystemAdmin.php](/var/www/php84/sms_admin_es/app/Filament/Pages/AnalyticsAndAdmin/SystemAdmin.php)

---

## Part 1: Fraud Detection Roadmap

## Target architecture

Build fraud detection in 3 layers:

1. `Signal collection`
2. `Decision engine`
3. `Case management + analytics`

### A. Signal collection

Create one normalized event stream for risky activity from:

- registration / login
- OTP verification
- checkout start and payment handoff
- payment success / failure / refund
- recurring cancel / refund abuse
- coupon / voucher abuse
- device activation / activation code abuse
- push/email/SMS abuse patterns
- support ticket bursts

### B. Decision engine

Use two scoring modes:

- `Real-time rule score`
- `Offline model score`

Start with rules first. Add ML only after the event schema is stable.

### C. Case management

Expose:

- fraud alerts
- blocked / reviewed / allowed decisions
- analyst notes
- false-positive feedback
- tenant-level thresholds

---

## Fraud phases

### Phase 0: Define scope and ownership

Decide which fraud types you want first:

1. `Payment fraud`
2. `Promo abuse`
3. `Account takeover`
4. `Multi-account abuse`
5. `Refund abuse`
6. `Device / activation abuse`

Recommended first release:

- payment fraud
- promo abuse
- refund abuse
- account takeover

---

### Phase 1: Add core fraud event model

Create these tables first.

#### PostgreSQL

Use PostgreSQL for operational state:

- `fraud_rules`
- `fraud_rule_versions`
- `fraud_decisions`
- `fraud_cases`
- `fraud_case_notes`
- `fraud_allowlist`
- `fraud_blocklist`
- `fraud_review_feedback`

#### ClickHouse

Use ClickHouse for high-volume events and feature aggregation:

- `fraud_events`
- `fraud_signals`
- `fraud_feature_store`
- `fraud_model_scores`

Suggested `fraud_events` fields:

- `event_id`
- `event_time`
- `app_id`
- `event_type`
- `customer_id`
- `sms_cust_id`
- `order_id`
- `subscription_id`
- `transaction_id`
- `payment_gateway`
- `device_id`
- `ip_address`
- `country`
- `platform`
- `amount`
- `currency`
- `coupon_code`
- `voucher_code`
- `status`
- `raw_payload`
- `source_module`

Suggested `fraud_signals` fields:

- `signal_id`
- `event_id`
- `signal_type`
- `signal_value`
- `signal_weight`
- `explanation`
- `app_id`
- `customer_id`
- `created_at`

---

### Phase 2: Add event publishers without changing business logic

Follow the same safe side-effect pattern already documented for journey events.

Add fraud-event publishing after success/failure in:

- [app/Http/Controllers/UserAdminActionController.php](/var/www/php84/sms_admin_es/app/Http/Controllers/UserAdminActionController.php)
  - refund
  - recurring cancel
  - manual assignment
- [app/Http/Controllers/Subscription/SubscriptionUpgradeController.php](/var/www/php84/sms_admin_es/app/Http/Controllers/Subscription/SubscriptionUpgradeController.php)
- [app/Services/Subscription/SubscriptionUpgradeService.php](/var/www/php84/sms_admin_es/app/Services/Subscription/SubscriptionUpgradeService.php)
- [app/Http/Controllers/Sms/SmsDeliveryCallbackController.php](/var/www/php84/sms_admin_es/app/Http/Controllers/Sms/SmsDeliveryCallbackController.php)
- [app/Jobs/SendBulkSmsJob.php](/var/www/php84/sms_admin_es/app/Jobs/SendBulkSmsJob.php)
- [app/Jobs/SendBulkEmailJob.php](/var/www/php84/sms_admin_es/app/Jobs/SendBulkEmailJob.php)
- [app/Jobs/SendPushNotificationJob.php](/var/www/php84/sms_admin_es/app/Jobs/SendPushNotificationJob.php)

Also add publishers around:

- login / OTP failures
- repeated device activation attempts
- coupon retries
- too many refunds from same payment instrument

Important rule:

- never block the original workflow if fraud-event logging fails

---

### Phase 3: Ship rule-based fraud scoring

Start with deterministic rules. Keep them tenant-aware.

Recommended first rules:

#### Account / auth abuse

- too many failed logins in 10 minutes
- too many OTP sends to one phone/email
- too many OTP verifies from same IP/device
- login country mismatch after recent successful login

#### Checkout / payment abuse

- too many failed payments per customer in rolling 1h / 24h
- too many cards / gateways used by same account in short window
- same payment token reused across multiple customers
- repeated payment attempts immediately after refund

#### Promo abuse

- same coupon used across too many new accounts
- many accounts created from same device/IP using same coupon
- voucher applied / removed repeatedly

#### Refund abuse

- multiple refunds from same customer within rolling 7/30 days
- refund rate above tenant threshold
- refund attempts with mismatched account / gateway / amount

#### Device / activation abuse

- many activation codes redeemed from same device/IP
- same device linked to too many accounts
- sudden spike in activations from unusual geography

Decision levels:

- `allow`
- `review`
- `step_up`
- `block`

`step_up` can mean:

- OTP challenge
- manual review
- temporary hold
- disable coupon usage

---

### Phase 4: Add fraud review console

Add a new Filament module:

- `Fraud Management`

Recommended pages:

1. `Fraud Alerts`
2. `Fraud Cases`
3. `Rules and Thresholds`
4. `Blocked Entities`
5. `False Positive Review`
6. `Fraud Analytics`

Suggested widgets:

- alerts by severity
- blocked vs allowed trend
- refund-abuse trend
- promo-abuse trend
- high-risk devices
- high-risk IPs
- gateway fraud hotspots

You can mirror patterns already used in:

- [app/Filament/Pages/RiskManagement/RiskManagement.php](/var/www/php84/sms_admin_es/app/Filament/Pages/RiskManagement/RiskManagement.php)
- [app/Filament/Resources/BillingAdminResource/Pages/GatewayLifecycle.php](/var/www/php84/sms_admin_es/app/Filament/Resources/BillingAdminResource/Pages/GatewayLifecycle.php)
- [app/Filament/Resources/TicketAdminResource/Pages/TicketAdmin.php](/var/www/php84/sms_admin_es/app/Filament/Resources/TicketAdminResource/Pages/TicketAdmin.php)

---

### Phase 5: Add ML-based fraud scoring

Only do this after 4-8 weeks of stable event collection.

You already have a Python ML pattern in `ml/`, so reuse that structure:

- feature extraction job
- label generation job
- train job
- predict job
- registry table

Add:

- `ml/fraud_feature_engineering.py`
- `ml/generate_fraud_labels.py`
- `ml/train_fraud_model.py`
- `ml/predict_fraud.py`

Suggested fraud features:

- failed_login_count_1h
- otp_send_count_1h
- distinct_devices_24h
- distinct_ips_24h
- coupon_attempt_count_24h
- refund_count_30d
- payment_failure_rate_30d
- activation_count_24h
- country_change_score
- gateway_switch_count_7d
- average_amount_deviation

First model outputs:

- `fraud_probability`
- `recommended_action`
- `top_reasons`

---

### Phase 6: Enforcement rollout

Roll out in 3 steps:

1. `Observe only`
2. `Review + step-up`
3. `Auto-block high confidence`

Recommended rollout:

- Week 1-2: log only
- Week 3-4: review queue
- Week 5-6: block only the most obvious rules

Do not auto-block at launch.

---

## Fraud implementation order for your repo

Best order:

1. Add `fraud_events` and `fraud_signals`
2. Publish events from refund / cancel / login / OTP / activation / coupon flows
3. Add rule engine service
4. Add fraud dashboard and case queue
5. Add ML scoring
6. Add external fraud-provider connector if needed later

---

## Part 2: Data Lake Roadmap

## Goal

Your system already has transactional Postgres plus analytical ClickHouse and Elasticsearch. A proper data lake should make raw history durable, cheap, queryable, and reusable by:

- analytics
- BI
- ML
- fraud detection
- audit/compliance
- downstream data sharing

---

## Recommended target state

Use a 3-zone design:

1. `Raw zone`
2. `Curated zone`
3. `Serving zone`

### Raw zone

Store immutable event/data dumps in object storage.

Recommended contents:

- Postgres table snapshots / CDC files
- ClickHouse exports
- GCP log exports
- webhook payload archives
- payment journey event files
- support attachments metadata
- SMS/email/push delivery logs

### Curated zone

Cleaned, typed, partitioned datasets:

- customers
- subscriptions
- orders
- payments
- refunds
- support tickets
- journeys
- coupons / vouchers
- churn features
- fraud features

### Serving zone

Purpose-built marts for:

- dashboard
- business analytics
- lifecycle reports
- AI churn
- fraud analytics
- finance reporting

---

## Best fit for this system

Since this repo already uses GCS-style storage and GCP logging, the cleanest path is:

- `Cloud Storage` as the lake storage
- `BigQuery / BigLake` as lake query / warehouse layer where needed
- `ClickHouse` as real-time analytical serving layer
- `PostgreSQL` remains transactional source of truth

If you want the simplest first version:

- GCS raw zone
- ClickHouse curated + serving
- optional BigQuery later

---

## Data lake phases

### Phase 0: Define canonical domains

Create ownership by domain, not by page:

- customer
- subscription
- order
- payment
- refund
- messaging
- support
- tax
- audit
- fraud
- ML features

Each domain must define:

- source tables
- owner
- refresh frequency
- PII policy
- retention

---

### Phase 1: Create storage layout

Use bucket/folder conventions like:

```text
gs://sms-lake/raw/app_id=1004/domain=payments/year=2026/month=08/day=07/
gs://sms-lake/raw/app_id=1004/domain=auth/year=2026/month=08/day=07/
gs://sms-lake/curated/domain=customers/
gs://sms-lake/curated/domain=subscriptions/
gs://sms-lake/serving/domain=fraud_mart/
gs://sms-lake/serving/domain=churn_mart/
```

Recommended formats:

- raw: JSONL / CSV / parquet
- curated: parquet
- serving extracts: parquet or native engine tables

---

### Phase 2: Start with batch exports

Do not start with full streaming everywhere.

First exports should come from:

- `customers`
- `tbl_customer_subscriptions`
- `tbl_order`
- `support_tickets`
- `ticket_messages`
- `sms_logs`
- `push_campaigns`
- `dunning_campaigns`
- `payment_journey_events`
- `audit_logs`

Implement exporters as Artisan commands + scheduled jobs.

Suggested new commands:

- `lake:export-customers`
- `lake:export-subscriptions`
- `lake:export-orders`
- `lake:export-support`
- `lake:export-sms-logs`
- `lake:export-payment-journey`
- `lake:export-audit`

Store manifest rows in PostgreSQL:

- `lake_export_runs`
- `lake_export_files`

---

### Phase 3: Introduce CDC / event streaming for hot domains

After batch is stable, add near-real-time flow for:

- auth events
- payment events
- refund events
- coupon/voucher events
- support events
- fraud events

Use one event bus pattern for cross-service ingestion.

If you stay inside the current Laravel-first system initially:

- queue job writes event file batches to GCS
- ClickHouse ingests curated batches

If you add a true streaming layer later:

- Kafka or Pub/Sub
- materialized ingestion into ClickHouse
- raw archive to object storage

---

### Phase 4: Build curated lake datasets

Create normalized curated datasets:

- `customer_dim`
- `subscription_fact`
- `order_fact`
- `payment_fact`
- `refund_fact`
- `device_dim`
- `support_ticket_fact`
- `message_delivery_fact`
- `audit_fact`
- `fraud_event_fact`
- `fraud_signal_fact`
- `churn_feature_store`

Important:

- standardize IDs
- preserve `app_id`
- keep event timestamps in UTC
- define PII masking rules

---

### Phase 5: Build serving marts

Create marts for:

- dashboard KPIs
- lifecycle reporting
- finance reconciliation
- fraud monitoring
- churn monitoring
- executive reporting

Suggested marts:

- `mart_dashboard_daily`
- `mart_gateway_health_hourly`
- `mart_fraud_daily`
- `mart_churn_daily`
- `mart_support_daily`
- `mart_revenue_daily`

---

### Phase 6: Add governance and security

Before scaling usage, add:

- data catalog / dataset ownership
- PII tagging
- retention policies
- tenant-level isolation rules
- audit trail for exports and data access
- row-level or dataset-level access control

Sensitive fields to protect first:

- email
- phone
- IP
- payment references
- auth tokens
- support attachments

---

## Data lake implementation order for your repo

Recommended order:

1. Raw GCS exports
2. Export manifests and scheduler
3. Curated parquet datasets
4. Fraud and churn feature datasets
5. Serving marts
6. Streaming ingestion for hot domains

---

## Combined 90-day roadmap

## Days 1-15

- define fraud scope
- define data domains
- create base schemas
- create raw lake bucket layout
- add export manifest tables
- add `fraud_events` / `fraud_signals`

## Days 16-30

- publish fraud events from refund / cancel / login / OTP flows
- export customers / subscriptions / orders / sms_logs to raw lake
- create first ClickHouse fraud tables
- create first fraud rules

## Days 31-45

- add fraud review dashboard
- add curated datasets
- add fraud analyst queue
- add fraud alert jobs
- export audit / support / payment journey datasets

## Days 46-60

- add rule tuning and allow/block lists
- create serving marts
- connect fraud metrics into risk dashboard
- add data freshness checks for lake datasets

## Days 61-90

- build fraud feature store
- train first fraud model
- run in shadow mode
- add streaming ingestion for hot domains
- add analyst feedback loop

---

## What I would implement first in your codebase

If we start now, my recommended first 5 deliverables are:

1. `fraud_events` and `fraud_signals` schema
2. a `FraudEventPublisher` service
3. event hooks in refund / cancel / OTP / activation / coupon flows
4. `lake_export_runs` + first 3 export commands
5. a new `Fraud Management` Filament page with observe-only dashboards

---

## Recommended success metrics

### Fraud

- false-positive rate
- review-to-confirmed-fraud rate
- refund-abuse detection rate
- coupon-abuse detection rate
- fraud decision latency
- blocked-loss prevented

### Data lake

- export success rate
- freshness SLA by domain
- schema drift incidents
- cost per TB stored / queried
- percentage of reports reading curated/serving datasets
- ML feature reuse across churn and fraud

---

## Practical recommendation

Do not try to launch both as giant projects.

Best sequence:

1. Build the `data lake raw + curated foundation`
2. In parallel, build `fraud event collection`
3. Then launch `rules-based fraud detection`
4. Then add `fraud ML`

That order keeps the lake useful even before fraud is mature, and keeps fraud explainable before ML is added.