Backend Data Management¶

Operational runbook for the people who keep LEAF DSS data alive: updating the village base data, running the /update operations console, editing clusters without wiping cadre work, managing the Google Sheet overlay, adding interventions/variables, and restoring from backup.

This is a do-this, check-that document. Every command is meant to be copy-pasted. Where an action can destroy someone's field edits, the danger is called out explicitly — read those boxes before you press the button.

Environment at a glance

Live URL: https://leaf-asrlm.in (Render, plan standard, no spin-down).
Data store: Supabase Postgres, reached over DATABASE_URL (IPv6 direct string). No ORM — raw SQL in db.py.
Base geodata + village master: CSV/shapefiles committed in leaf_flask/data/.
Coverage: Assam = 35 districts, 220 blocks, 6 commodities.
Auth: there is no login. Admin actions are gated only by ?admin=1 (or an X-Admin: 1 header). Treat every admin URL as privileged.
Commands below use PowerShell. Run them from the leaf_flask/ directory unless a path says otherwise.

The one invariant that governs everything

Never wipe cadre edits. Once a block coordinator, pashu sakhi, or reviewer has touched a cluster — by finalizing it, uploading an edited CSV (which locks it), or attaching a production dashboard — that cluster is human-owned and must survive every refresh, rebuild, and rename. The system enforces this automatically, but several of the operations in this runbook can override it if used carelessly. Each such step flags the risk.

1. Updating village base data end-to-end¶

data/villages.csv is the cluster planner's single source of truth (~21,495 rows, all of Assam). The tool reads district → block → village hierarchy and the per-village commodity member counts straight from this file. If a village is not in this CSV with valid coordinates, it does not exist as far as clustering and the map are concerned.

The file is built by a script from the raw SHG survey workbook — you never hand-edit villages.csv.

1.1 The pipeline¶

source_data/SHG_Assam_Consolidated_final_Jun22.xlsx
        │   sheet: "Village Location Detail"
        │
        ▼   scripts/build_village_master.py
        │     • district/block/village taken VERBATIM from the survey
        │       (uppercased, whitespace collapsed) — NOT rewritten
        │       against the block shapefile
        │     • coordinates numeric + inside the Assam bounding box
        │     • commodities aggregated into the 6 clustering buckets
        │     • ROWS WITH BAD/MISSING COORDINATES ARE DROPPED
        ▼
data/villages.csv   ──►  re-cluster (Section 2)

The workbook lives in source_data/ and is gitignored — it carries enumerator PII, so only the derived data/villages.csv is committed.

1.2 Receiving a survey workbook (e.g. a single-district file)¶

villages.csv is ALL-ASSAM — do not rebuild the whole file from a one-district workbook

scripts/build_village_master.py reads one workbook and writes the entire villages.csv. If a district team sends you a workbook that only contains their district (a common case — e.g. an Udalguri refresh), running the script as-is would replace all 35 districts with just that one and silently delete every other district's villages.

A single-district update must be merged surgically, not used to rebuild the whole file:

Build a village frame from the district workbook into a temporary CSV (point the script's SURVEY_XLSX/VILLAGES_CSV at temp paths, or adapt in a one-off notebook using the same survey_to_out() logic).
Drop that district's existing rows from villages.csv and concatenate the new district rows in.
Diff before committing — see the checks below.

Only rebuild the whole villages.csv from a consolidated all-Assam workbook.

1.3 Running the builder (full consolidated workbook)¶

Preview (safe, no write)Write villages.csv

$env:PYTHONUTF8 = "1"
python scripts/build_village_master.py --dry-run

--dry-run prints the full accounting — source rows, dropped rows, coverage, and a continuity diff against the current villages.csv (blocks gained / lost) — but writes nothing. Always run this first.

$env:PYTHONUTF8 = "1"
python scripts/build_village_master.py

Writes data/villages.csv and prints the final row / block / district counts.

Local Python

On the maintainer's Windows box, python on PATH is a broken Microsoft Store stub. Use the real interpreter's full path if python errors immediately (see the team's environment notes). $env:PYTHONUTF8 = "1" is required so the print report doesn't choke on district names.

1.4 What gets DROPPED, and why¶

The script drops a row for exactly one reason: bad coordinates. Everything else is kept verbatim so per-block counts match the survey sheet.

Drop reason	Condition
Missing / non-numeric coordinates	`Latitude` or `Longitude` blank or not a number
Out of range (Assam bbox)	lat outside `24.0–28.5` or long outside `89.5–96.5`

Every dropped row is printed individually under === DROPPED ROWS (coordinate errors — fix in source sheet) === with district / block / GP / village / lat / long, so the field team can correct them at source.

The real-world gotcha: 'Location Status = MISSING' villages disappear

Villages whose survey record has no GPS fix (the survey marks Location Status = MISSING) come through with blank lat/long. The coordinate filter drops them, which means they cannot be clustered and cannot be placed on the map — they vanish from the tool entirely.

This is not a bug to patch in code; a village with no coordinates genuinely can't be plotted or spatially clustered. The fix is at source: either the field team supplies coordinates for those villages in the survey sheet and you rebuild, or the villages are accepted as out-of-scope until surveyed. This exact situation occurred with Udalguri on this project. Whenever a district reports "some of my villages aren't showing up," the dropped-rows list in the build report is the first place to look.

1.5 Naming is source-authoritative (do not "fix" it against the shapefile)¶

district_name / block_name are taken verbatim from the survey's own District / Block columns (uppercased, whitespace collapsed). They are deliberately not rewritten against the block shapefile.

An earlier version of the script did override the district from a shapefile and route unmatched blocks through a point-in-polygon "recovery." Because that shapefile predates newer districts (e.g. BAJALI, carved out of BARPETA) it silently moved villages to the wrong district and renamed/merged blocks — a systematic error reported from a training. The shapefile is the correct key for the map / feasibility overlay, but the cluster planner must mirror the sheet. Leave the verbatim naming alone.

The script applies only a short, curated list of within-district typo corrections (BLOCK_TYPO_FIXES, e.g. BINAKANDI → BINNAKANDI) plus a generic trailing-" BLOCK"/punctuation cleanup. Genuine directional splits (DERGAON NORTH/SOUTH) are preserved.

1.6 Before / after checklist¶

Before you commit a rebuilt villages.csv

[ ] Ran --dry-run and read the ROW ACCOUNTING and DROPPED ROWS sections.
[ ] Dropped-row count is expected (not hundreds of surprise drops from a column rename).
[ ] CONTINUITY diff shows no block unexpectedly lost. A lost block usually means a spelling drift, not a real deletion.
[ ] District count is 35, block count is 220 (unless the survey genuinely added/removed coverage — investigate if not).
[ ] For a single-district update: confirmed you merged surgically and other districts' rows are untouched.

After writing villages.csv

[ ] Re-cluster the affected blocks (Section 2). New/edited villages do not appear in clusters until the scope is regenerated.
[ ] Spot-check an affected block in the UI and on the map.
[ ] Commit data/villages.csv (never the workbook — it's gitignored for a reason).

1.7 Re-clustering after a data change¶

Clusters are generated lazily per block on view, and each scope is only rebuilt when its data fingerprint changes — unless it is locked (see Section 2). To materialise everything after a base-data change:

Whole state (recommended)One block (admin, wipes edits in scope)

Trigger the background sweep (same routine the nightly scheduler runs). Locked/finalized/dashboard scopes are skipped, so cadre edits survive.

curl -X POST "https://leaf-asrlm.in/api/clusters/refresh-all?admin=1"

Then poll coverage until the numbers stop climbing:

curl "https://leaf-asrlm.in/api/clusters/coverage"

curl -X POST "https://leaf-asrlm.in/api/clusters/regenerate?admin=1" `
  -H "Content-Type: application/json" `
  -d '{"block":"KHOWANG"}'

regenerate vs refresh-all

POST /api/clusters/regenerate force-rebuilds the scope and wipes any user edits/CSV uploads in it — even locked ones. Use it only for a block you know is unedited. For routine post-data-load rebuilds use refresh-all, which protects cadre edits. Both require ?admin=1.

2. The `/update` operations console¶

https://leaf-asrlm.in/update (update.html) is the ops console for the state team. It surfaces coverage, data-checks, cluster reconciliation, and the download/upload cluster-editing cycle. Everything on it maps to an API you can also call directly.

2.1 What the page shows and the calls behind it¶

Panel / action	Endpoint	Notes
Coverage table (assigned vs unassigned per commodity)	`GET /api/clusters/coverage`	`assigned + unassigned = raw total`; also shows blocks-with-clusters out of 220
Pending renames	`GET /api/clusters/pending-renames`	blocks with clusters under an old/renamed name
Refresh all clusters button	`POST /api/clusters/refresh-all?admin=1`	background whole-state rebuild; protects cadre edits
Data-checks panel	`GET /api/config/validate`	Google Sheet guardrails (Section 3)
Sheets sync status	`GET /api/config/sheets-status`	cache freshness per sheet
Refresh sheet data	`POST /api/config/refresh`	force re-fetch from Google Sheets
Upload AI doc	`POST /api/config/upload-ai-doc?admin=1`	adds a PDF to the RAG pool
Download cluster CSV	`GET /api/clusters/export.csv`	row-per-village edit file
Download unassigned villages	`GET /api/clusters/unassigned.csv`	reconciliation companion
(from clustering UI) upload edited CSV	`POST /api/clusters/import?block=…`	commodity-safe replace

2.2 Coverage refresh¶

Because clusters materialise lazily, a whole-state export only contains blocks someone actually opened — historically undercounting members by ~60%. The Refresh all clusters button closes that gap.

# Trigger (returns 202 immediately with current pre-refresh coverage)
curl -X POST "https://leaf-asrlm.in/api/clusters/refresh-all?admin=1"

# Watch it fill in
curl "https://leaf-asrlm.in/api/clusters/coverage"

Safe to re-run

The sweep skips locked/finalized/dashboard scopes (cadre edits), skips fresh scopes by fingerprint (no-op when nothing changed), and holds a Postgres advisory lock so overlapping triggers — or the nightly job — never run twice at once.

2.3 Editing, locking, and finalizing clusters¶

There are three levels of "human owns this cluster," each set by a different action:

State	How it gets set	Effect
locked	Uploading an edited CSV via `POST /api/clusters/import` sets `locked=true` on the imported clusters	Skipped by all refresh/rebuild sweeps
finalized	`POST /api/clusters/<cluster_id>/finalize` (body `{"finalized": true}`)	Skipped by sweeps and included in the outbound production-tool feed
dashboard	Production tool POSTs to `/api/production-tool/dashboard/<cluster_id>` — stores JSON and sets `locked=true`	Skipped by sweeps; report card shows the aggregates

Un-finalize with {"finalized": false}:

curl -X POST "https://leaf-asrlm.in/api/clusters/<cluster_id>/finalize" `
  -H "Content-Type: application/json" -d '{"finalized": false}'

2.4 The "never wipe cadre edits" invariant — how it's enforced¶

There is no origin column. Protection is driven entirely by the booleans locked, finalized, provisional and by dashboard IS NOT NULL. Two mechanisms:

Smart lazy refresh (get_or_regenerate, per block+commodity on view): skips entirely if the scope is locked (any cluster finalized or locked). Otherwise it regenerates only if the stored fingerprint differs from the current scope_fingerprint() — a SHA-256 of ALGO_VERSION (currently 5) + params + the village data blob. Unchanged scopes are served as-is, so reloading the map never rebuilds or wipes anything.
Whole-state sweep (refresh_all_coverage, the Refresh all button and nightly job): iterates every block × 6 commodities and skips any scope where finalized OR locked OR dashboard IS NOT NULL. Fresh scopes (fingerprint match) are skipped too.

What can still wipe an edit

POST /api/clusters/regenerate (Section 1.7) — force rebuild, ignores locks.
Bumping ALGO_VERSION in clustering.py — changes every fingerprint, forcing unlocked scopes to regenerate on next view (locked scopes are still protected).
A raw SQL DELETE on clusters — bypasses everything. cluster_villages cascades on delete.

Before any push to the app repo, confirm your change cannot trigger a full regeneration or clear the protection flags.

2.5 Downloading / uploading the cluster-mapping CSV¶

The edit cycle is: download → edit in Excel → upload.

DownloadUpload

# Whole block (all commodities) — the edit file
curl "https://leaf-asrlm.in/api/clusters/export.csv?block=KHOWANG" -o clusters_KHOWANG.csv

# Single commodity view
curl "https://leaf-asrlm.in/api/clusters/export.csv?block=KHOWANG&commodity=Dairy" -o clusters_KHOWANG_Dairy.csv

One row per (cluster, village). Editors can move a village between clusters (change its cluster_num), merge (same number), split (new number), fill in cluster_name / pashu_sakhi / block_coordinator / district_coordinator, or append a brand-new village by supplying its lat/long inline.

curl -X POST "https://leaf-asrlm.in/api/clusters/import?block=KHOWANG" `
  -F "file=@clusters_KHOWANG.csv"

block is required. Returns {"imported": <n>}. The uploaded clusters are set locked=true.

Commodity-safe replace

Import replaces only the commodities the uploaded file actually contains (or the single pinned ?commodity=). Re-uploading a one-commodity "view" export therefore never deletes the block's other five commodities. A file with no usable rows for the block is a no-op. cluster_code is display-only and ignored on import; cluster_id is the join key. CSVs exported before district_coordinator existed still import (that column treated as empty).

3. The block-values Google Sheet overlay¶

The map / feasibility layer does not read block variable values from a CSV in the repo at runtime — it reads them from published Google Sheets, with a local CSV as a fallback. This lets the state team update block values without a code deploy. (Note: the cluster planner still reads villages.csv — the Google Sheet overlay is for the block-level map/feasibility variables, a separate concern.)

3.1 The three sheets¶

Sheet key	Purpose	Local CSV fallback
`dss_input`	Intervention & variable configuration (which variables exist, ranges, convergence-card tags) — see Section 4	`data/DSS_input2.csv`
`block_values`	Per-block variable values = the actual map/feasibility overlay	`data/block_values.csv`
`user_update`	LEAF-friendly end-user sheet that overlays friendly columns onto `block_values` by `Block_name` (user wins)	— (URL currently empty → no-op)

3.2 How they're published and read¶

Each sheet is published as CSV (File → Share → Publish to web → CSV). The published URLs are configured in google_sheets.py (SHEET_URLS).
get_sheet(key) uses a 5-minute TTL cache. On a miss it fetches the published CSV; if the fetch fails it falls back to the stale cache, then to the local CSV in data/. So a Google outage never takes the map down — it just serves the last-known values.
get_block_values_overlaid() layers user_update's friendly columns on top of the coded block_values by Block_name; it is a no-op while user_update is unset.

3.3 Forcing a refresh¶

The 5-minute cache means edits appear within 5 minutes on their own. To see them immediately, use the Refresh sheet data button on /update or:

curl -X POST "https://leaf-asrlm.in/api/config/refresh"

Returns which sheets refreshed, the new cache status, and a validation result in one call.

# Check freshness without forcing a fetch
curl "https://leaf-asrlm.in/api/config/sheets-status"

3.4 Validating the sheets (do this after every edit)¶

GET /api/config/validate runs the guardrail checks surfaced in the /update Data-checks panel. It is read-only.

curl "https://leaf-asrlm.in/api/config/validate"

{
  "ok": true,
  "issues": [
    { "sheet": "block_values", "severity": "warning",
      "message": "…" }
  ]
}

ok is false if any error-severity issue is found. Checks include: required ID columns present, no duplicate BLOCK_IDs, no stray non-numeric values in numeric columns, range_min not greater than range_max, every I_variable referenced in dss_input actually exists in block_values, and no conflicting convergence-card tags.

Edit → validate → refresh, in that order

After editing a sheet: run validate first and clear every error issue, then refresh to push it live. An error means the map/feasibility layer may render wrong or blank for affected blocks.

4. Adding interventions / variables¶

Interventions and variables are defined in the dss_input Google Sheet (fallback data/DSS_input2.csv) — not in code. To add one:

Edit dss_input. Add the row(s) for the new variable/intervention: its ID/code, display metadata, range_min / range_max, and any convergence-card tags. Follow the column shape of the existing rows.
If the variable carries per-block values, add a matching column in block_values for every block (all 220), keyed by BLOCK_ID. Every I_variable referenced in dss_input must exist in block_values or validation fails.
Validate: GET /api/config/validate → confirm ok: true. Fix any error (missing column, non-numeric value, range_min > range_max, dangling I_variable).
Refresh: POST /api/config/refresh (or the /update button) to push it live inside the 5-minute window.
Keep the docs in sync — the Interventions / Variables API pages under docs/api/ document the available codes.

No deploy needed

Because both sheets are read at runtime with a CSV fallback, adding a variable is a sheet edit + validate + refresh — no code change and no Render deploy. Only update data/DSS_input2.csv / data/block_values.csv in the repo if you want the committed fallback to match (recommended for durable additions).

5. Backups & restore¶

5.1 What runs, and where it lands¶

A nightly logical backup dumps the cluster tables to Supabase Storage (not to disk — Render's filesystem is ephemeral and wiped on every restart/redeploy).

Driven by scheduler.py (in-process daemon thread; the same scheduler that runs the coverage sweep). It calls db_backup.backup_to_storage().
Tables backed up: clusters, cluster_villages, cluster_generation.
Destination: bucket db-backups, objects at <YYYY-MM-DD>/<table>.csv.gz (gzipped CSV).
Path: IPv4 HTTPS via Supabase REST/Storage using SUPABASE_URL + SUPABASE_SECRET_KEY — deliberately independent of the IPv6 Postgres path, so a backup can run even when IPv6 is flaky.
Retention: snapshots older than 14 days are pruned automatically.
No-op if SUPABASE_URL / SUPABASE_SECRET_KEY are absent (e.g. local dev) — it returns {"skipped": …}.

Backup credentials are separate from the app DB connection

The app talks to Postgres over DATABASE_URL (IPv6). Backups use SUPABASE_URL + SUPABASE_SECRET_KEY (IPv4 REST/Storage). Both must be set in the Render dashboard or backups silently skip. This is easy to miss on a fresh deploy.

5.2 Verifying backups are happening¶

In the Supabase dashboard → Storage → db-backups, confirm a folder exists for last night's date (YYYY-MM-DD) with three .csv.gz objects.
The scheduler records each run's summary in the maintenance_run table (last_run, last_summary JSON with per-table row counts and any pruned folders).

5.3 Restoring¶

There is no one-click restore endpoint; restore is a manual load.

Download the snapshot for the date you want from bucket db-backups, e.g. 2026-07-22/clusters.csv.gz, .../cluster_villages.csv.gz, .../cluster_generation.csv.gz.
Decompress each: gzip -d clusters.csv.gz (or unzip in any tool).

Load into Postgres. Restore in FK order — clusters before cluster_villages (the child cascades on delete and references cluster_id). For a full replace of a table:

-- connect with psql using DATABASE_URL, then per table:
TRUNCATE cluster_villages, clusters CASCADE;
\copy clusters FROM 'clusters.csv' WITH (FORMAT csv, HEADER true);
\copy cluster_villages FROM 'cluster_villages.csv' WITH (FORMAT csv, HEADER true);
\copy cluster_generation FROM 'cluster_generation.csv' WITH (FORMAT csv, HEADER true);

For a partial restore (one block that got clobbered), load into a staging table and INSERT … ON CONFLICT only the affected cluster_ids rather than truncating.

Restore is destructive — protect current cadre edits first

A full TRUNCATE + reload replaces all current clusters, including any cadre edits made since the backup was taken. Before restoring, export the current live state (GET /api/clusters/export.csv per affected block, and check finalized/locked flags) so you can reconcile, and prefer a scoped restore over a full truncate whenever the damage is localised.

Restore checklist

[ ] Picked the right snapshot date (Storage folder).
[ ] Exported current live clusters for the affected scope first.
[ ] Loaded clusters before cluster_villages.
[ ] Verified counts against the backup's maintenance_run summary.
[ ] Confirmed finalized / locked flags survived (cadre edits intact).

Clustering Workflow — how the algorithm turns villages into clusters.
Architecture — system overview.
Deployment / Hosting — Render, env vars, custom domain.
API reference: Clusters, Production Tool, Config & Health.