# Churn Runtime Runbook

## 1) Current Official Flow

- Runtime root: `SMSLa/sms_admin`
- Timezone: `Asia/Kolkata`
- Scheduler entrypoint: Laravel scheduler via cron every minute

### Scheduled cadence

- Nightly inference: daily `02:00 IST`
  - `bash churn_nightly_update.sh`
  - Runs: `php artisan churn:pipeline:run --all --sync-first --sync-mode=delta`
  - Then: `php artisan churn:cache-refresh --app-id=0 --validate --alert`
- Weekly reconcile: Sunday `02:15 IST`
  - `bash churn_weekly_reconcile.sh`
  - Runs: `php artisan churn:pipeline:run --all --sync-first --sync-mode=full`
  - Then: `php artisan churn:cache-refresh --app-id=0 --validate --alert`
- Monthly retrain: day `15`, `02:30 IST`
  - `bash churn_monthly_retrain.sh`
  - Runs: `php artisan churn:pipeline:run --all --train --sync-first --sync-mode=full --sync-clear`
  - Then: `php artisan churn:cache-refresh --app-id=0 --validate --alert`

## 2) Data Policy

- Nightly: incremental refresh (delta lookback) + latest prediction refresh.
- Weekly: full non-destructive reconcile to correct drift while preserving churn history continuity.
- Monthly: destructive rebuild (`--sync-clear`) + retrain + full prediction refresh.

### 6-month cohort definition

For full sync (`churn:sync-all`), cohort is customers with subscription activity in the rolling window:

- Included when `start_date >= NOW() - INTERVAL '6 months'` OR `end_date >= NOW() - INTERVAL '6 months'`
- Feature rows are computed from subscription/order/device behavior and upserted to `churn_feature_store`.

## 3) Ops Commands

### A) Cutover to SMSLa runtime

```bash
# Keep only one scheduler cron entry and point to SMSLa
( crontab -l 2>/dev/null | sed '/php artisan schedule:run/d'
  echo '* * * * * cd /home/areeb/Downloads/SMSLa/sms_admin && php artisan schedule:run >> /dev/null 2>&1'
) | crontab -

# Verify cron
crontab -l
```

### B) Verify schedule wiring

```bash
cd /home/areeb/Downloads/SMSLa/sms_admin
php artisan schedule:list
```

Expected entries:

- `0 2 * * *` nightly
- `15 2 * * 0` weekly
- `30 2 15 * *` monthly

### C) One-time production bootstrap after cutover

```bash
cd /home/areeb/Downloads/SMSLa/sms_admin
php artisan churn:pipeline:run --all --sync-first --sync-mode=full --train --sync-clear
php artisan churn:cache-refresh --app-id=0 --validate --alert
```

Capture and store in deployment notes:

- Date/time run started and completed
- Pipeline run ID(s)
- Cache refresh run ID

### D) Recovery commands

- Nightly failure recovery:

```bash
php artisan churn:pipeline:run --all --sync-first --sync-mode=delta
php artisan churn:cache-refresh --app-id=0 --validate --alert
```

- Weekly reconcile recovery:

```bash
php artisan churn:pipeline:run --all --sync-first --sync-mode=full
php artisan churn:cache-refresh --app-id=0 --validate --alert
```

- Monthly retrain recovery:

```bash
php artisan churn:pipeline:run --all --train --sync-first --sync-mode=full --sync-clear
php artisan churn:cache-refresh --app-id=0 --validate --alert
```

## 4) DB Health Checklist (one-screen SQL)

```sql
-- Freshness + volume by app
WITH fs AS (
  SELECT app_id, COUNT(*) fs_rows, MAX(computed_at) fs_max
  FROM churn_feature_store GROUP BY app_id
),
cp AS (
  SELECT app_id, COUNT(*) pred_rows, MAX(predicted_at) pred_max
  FROM churn_predictions GROUP BY app_id
)
SELECT
  COALESCE(fs.app_id, cp.app_id) AS app_id,
  COALESCE(fs_rows,0) AS fs_rows,
  COALESCE(pred_rows,0) AS pred_rows,
  ROUND(EXTRACT(EPOCH FROM (NOW() - fs_max))/3600.0,2) AS fs_lag_h,
  ROUND(EXTRACT(EPOCH FROM (NOW() - pred_max))/3600.0,2) AS pred_lag_h
FROM fs FULL JOIN cp ON cp.app_id = fs.app_id
ORDER BY 1;

-- Missing from 6-month expected set
WITH expected AS (
  SELECT DISTINCT app_id, sms_cust_id
  FROM tbl_customer_subscriptions
  WHERE start_date >= NOW() - INTERVAL '6 months'
     OR end_date   >= NOW() - INTERVAL '6 months'
)
SELECT e.app_id, COUNT(*) AS missing_vs_6m
FROM expected e
LEFT JOIN churn_feature_store fs
  ON fs.app_id = e.app_id AND fs.sms_cust_id = e.sms_cust_id
WHERE fs.sms_cust_id IS NULL
GROUP BY e.app_id
ORDER BY missing_vs_6m DESC, e.app_id;

-- Extra rows outside 6-month expected set
WITH expected AS (
  SELECT DISTINCT app_id, sms_cust_id
  FROM tbl_customer_subscriptions
  WHERE start_date >= NOW() - INTERVAL '6 months'
     OR end_date   >= NOW() - INTERVAL '6 months'
)
SELECT fs.app_id, COUNT(*) AS extra_outside_6m
FROM churn_feature_store fs
LEFT JOIN expected e
  ON e.app_id = fs.app_id AND e.sms_cust_id = fs.sms_cust_id
WHERE e.sms_cust_id IS NULL
GROUP BY fs.app_id
ORDER BY extra_outside_6m DESC, fs.app_id;

-- Prediction coverage
SELECT
  fs.app_id,
  COUNT(*) AS fs_rows,
  COUNT(cp.sms_cust_id) AS pred_rows,
  COUNT(*) - COUNT(cp.sms_cust_id) AS missing_predictions
FROM churn_feature_store fs
LEFT JOIN churn_predictions cp
  ON cp.app_id = fs.app_id AND cp.sms_cust_id = fs.sms_cust_id
GROUP BY fs.app_id
ORDER BY missing_predictions DESC, fs.app_id;

-- Active models
SELECT app_id, model_type, model_version, trained_at, accuracy, precision_score, recall_score, f1_score, auc_roc
FROM churn_model_registry
WHERE is_active = TRUE
ORDER BY app_id, model_type, trained_at DESC;
```

## 5) Client Communication (Plain-language SLA)

- Data refreshes every night at `02:00 IST`.
- A deeper weekly reconcile runs every Sunday at `02:15 IST`.
- Full model retraining and clean rebuild runs monthly on day `15` at `02:30 IST`.
- Dashboard cache is republished after each run only when validation passes.