Database
Every live table on the device: what it holds, who writes it, who reads it, and how it syncs.
Urvi keeps its data in six on-device stores. Two of them hold the same farmers, lands and crop plans in different shapes. Every table is described column by column in the table reference; this page explains how the pieces fit and what a rewrite should change.
Paths use app/... for app/src/main/java/org/platformcommons/urvi/ and data/... for data/src/main/java/org/mindtree/app/agricultor/data/.
The six on-device stores
| Store (file name) | Version | Tables | Purpose | Owner |
|---|---|---|---|---|
agri_db | 62 | 64 registered (55 live) | Main domain data: farmers, lands, crop plans, farming models, compact blocks, reference data, endorsements, sync errors. | :data module, Room (data/db/AppDatabase.kt) |
ie_register.db | 20 | 12 (11 live) | Legacy UI copy of farmers, assets, portfolios and inventory, plus dropdown data from bundled assets and FCM notifications. | :app module, Room (app/datasource/database/AppDatabase.kt) |
error_log_db | 2 | 1 | Raw HTTP failure log of sync calls. Only read when the file is exported. | :data module, Room (data/db/ErrorLogDatabase.kt) |
sync_ledger_db | 1 | 3 | App outbox: which local rows need POST, PATCH, PUT or DELETE, and in what order. | :data module, Room (data/db/SyncLedgerDB.kt), schema not exported |
SYNC_QUEUE_DB | n/a | 1 (OPERATION) | The HTTP request queue that actually sends data. | External library OfflineAndroid / CoreAPI (plain SQLite) |
| Assessment library DB | unknown | unknown | Assessments, activity tracker data and attachments. | External library commons-assessment-urvi-lib; source not in this repo |
Facts that apply to all of them:
- No store is encrypted. Aadhaar, ration card and phone numbers are in plain text.
agri_dbandsync_ledger_dballow queries on the main thread.ie_register.dbuses full destructive fallback: any missing migration wipes it.- Only
sync_dependencyhas real foreign keys. Every other link is by convention.
How data flows
- Setup and the "data down-sync" screens download data into
agri_db. - A user edit writes to
agri_dbwithsyncStatus = SYNC_NOT_INITIALIZEDand adds a row tosync_ledger. - Sync workers turn ledger rows into library operations in
SYNC_QUEUE_DB. - Response handlers (
data/sync/rh/*RH.kt) write server ids back and mark the rowSYNC_DONE. - "Bridge" mappers (
app/bridge/*) copy farmers, assets and portfolios intoie_register.db, which many older screens still read.
Three farmer representations
The same farmer exists in three tables, each with its own key.
| Table | Store | Shape | Primary key | Business key | Used for |
|---|---|---|---|---|---|
ie_flat_entity | agri_db | One wide row from the server report dataset | uniqueIdentifier (local autoincrement) | uuid (farmer alias), iEOLTPId (server id) | Farmer list, filters, endorsement, compact blocks |
ie + 6 ie_person_* / ie_ietagging child tables | agri_db | Platform API DTO | appId (local UUID) | aliasId (farmer alias) | What gets POSTed and PATCHed |
ie (legacy) | ie_register.db | Denormalised UI projection | uniqueIdentityKey (local autoincrement) | aliasId (farmer alias) | Farmer detail screen, Add farmer |
How the keys connect, in plain words:
- Farmer alias is the same value in all three:
ie_flat_entity.uuid=ie.aliasId= legacyie.aliasId. - Server farmer id is
ie_flat_entity.iEOLTPId. Other tables store it asowningEntityId(assets, portfolio, farming models, compact block members),entityId(endorsements),iEId(sync errors) andie_id(roles). - Local farmer id is
ie.appId. Rows created before the farmer reaches the server point to it throughappmill_metadata_ie_appId. - Farming models also use
aliasIdto find farmers that are not yet synced.
Because the three copies are filled by different code paths, they drift. If the bridge did not run, the farmer detail screen shows blanks.
Sync status lifecycle
Most agri_db tables carry three columns from BaseEntity: appId (local UUID primary key), opId and syncStatus.
| Step | Row syncStatus | sync_ledger.sync_status | What happens |
|---|---|---|---|
| Downloaded from server | SYNC_DONE (default) | no row | Nothing to send. |
| Created or edited on device | SYNC_NOT_INITIALIZED | SYNC_NOT_INITIALIZED | A ledger row is added. Dependencies are recorded in sync_dependency (for example, a crop plan waits for its farmer). |
| Worker picks it up | IN_SYNC_QUEUE | IN_SYNC_QUEUE | A library operation is created; its id goes into opId and sync_op_id. |
| Server accepts | SYNC_DONE | SYNC_DONE | The response handler writes the server id. Done ledger rows are deleted by collectGarbage. |
| Server rejects | unchanged | unchanged | Failure goes to error_logs, sync_error_logs and/or sync_errors; PLIs and farming models set hasSyncError. |
Rules worth keeping:
- A newer PUT replaces a PUT that has not started yet, so the latest edit wins.
- While an entity has a pending ledger row, the UI locks editing (
checkIfEntityHasPendingSync). sync_work_statusis a single-row flag that stops two sync runs at once.
Known gaps:
- The row status and the ledger status can disagree.
- ATM harvest skips the ledger and writes straight into the library queue.
- Server id is
nullor0until synced. Many queries useid IS NOT NULL AND id != 0to mean "on the server".
SharedPreferences and files
All main keys sit in the default, unencrypted SharedPreferences file, shared by the :data module (data/pref/SharedPrefSource.kt) and the :app module (PreferenceUtil, LocaleManager). Values are not listed here.
| Key group | Example keys | Meaning |
|---|---|---|
| Session and identity | X-SESSIONID, key_tenant_*, key_user_*, account, user_account, key_is_unit_login | Session header and logged-in user/tenant. The session token is stored in plain text. |
| Setup and language | setup_status, KEY_IS_SETUP_COMPLETED, is_synced, language, language_status, selectedLanguage, refresh_data | Onboarding state and chosen language. |
| Work node and tenant | worknode_detail, VOWorkNodeId | Selected VO/habitation (two Vrutti-only keys are unused, see Set-aside code). |
| Sync progress | SHARED_PREF_KEY_LAST_SYNC_TIME, PREF_KEY_FARMER_SYNC_*, PREF_KEY_ALL_SYNCS_COMPLETE, SHARED_PREF_KEY_LAST_FORCE_SYNC_LOG_TIME | Last sync time and down-sync paging. |
| Cached API data | key_product_data, key_product_json_data, key_facility_data, key_disabled_land_data*, cb_crops_data, pre_cb_crops_data, 365DKG, SyncErrorLogs | JSON copies of server data. |
| Screen state | key_habitat_vo_filter, key_selected_vo, key_selected_shg, key_selected_cadre, key_is_endorsement_cached | Filters and endorsement selections. |
| Content and analytics | key_program_configuration, key_program_instructions, key_aap_reject_reasons, key_youtube_video_links, key_analytics_*, key_login_history, key_current_season_value | Info screens, dashboard cache, history. |
| Clear-data audit | shared_pref_key_is_app_data_cleared, shared_pref_key_is_cleared_data_sent_to_firebase | Flags after a data wipe. |
Other key-value stores: device_registration_prefs (device session id), an osmdroid map cache file, and Android AccountManager.
| File location | Content |
|---|---|
filesDir/geofencing/ | Polygon files for land, crop plan and ATM geofences |
filesDir/images/ | Camera photos and downloaded attachment images |
cacheDir/ | Endorsement abstract PDFs, map exports |
getExternalFilesDir(null) | ATM harvest photos |
getExternalFilesDir("/")/<app_name>/ | Full copy of prefs, files and databases, zipped for "share logs" |
The "share logs" zip contains agri_db (with PII) and the prefs (with the session token). SYNC_QUEUE_DB is uploaded to Firestore. The FileProvider exposes very broad roots.
Migration situation
| Store | State |
|---|---|
agri_db | 62 versions. Manual migrations up to 52, auto-migrations 52-58 and 59-62. Schema JSONs 10-51 are missing, so old migrations cannot be tested. MIGRATION_1_2 is defined but not registered. Migrations 41-42 and 42-43 are empty. harvest_logs was dropped and recreated in 14-15 (data lost). MIGRATION_12_13 left an orphan table asset_quality_labels. One migration has a typo'd column type (INTERGER); another sets a preference. |
ie_register.db | 20 versions, manual up to 18, then auto. Destructive fallback wipes it on any gap. |
error_log_db | v2, one rebuild. |
sync_ledger_db | v1, no migrations, schema not exported. |
Advice for the rewrite: do not migrate in place. Start a fresh schema at v1. Before the old stores are discarded, upload every unsynced row (syncStatus != SYNC_DONE) and every pending sync_ledger / SYNC_QUEUE_DB operation, or force a full re-download once the queue is empty.
Data model problems
| Problem | Example | Effect |
|---|---|---|
| Two databases for one domain | ie_register.db copies farmers, assets, portfolios | Double writes, drift, blank screens |
| Three farmer tables | ie_flat_entity, ie, legacy ie | Four different join keys |
| Inconsistent identity | appId, uuid, autoincrement and server id all used as PK; land linked by asset (TEXT), assetId (INTEGER) and assetCheckSum | Hard joins, wrong matches |
Random appId on downloaded rows | BaseEntity.appId = UUID.randomUUID() with REPLACE | Re-downloads can insert duplicates |
| Child tables never cleared | asset_*_labels, ie_person_*, product_nameml, refdata_labels | Likely duplicate rows after each refresh (verify on a device) |
| Mixed conflict rules | insert(List) is IGNORE, insert(T) is REPLACE | Compact blocks never refresh; farmer REPLACE wipes device-only flags |
| No foreign keys, few indexes | owningEntityId, portfolioId, titleId, iEOLTPId, entityId unindexed | Slow lists, orphans after hard deletes |
| Wrong queries | appId LIKE, assetCode LIKE '%12%', ORDER BY a TEXT date, season update on all PLIs | Wrong rows, missed index |
| Over-flattened DTOs | worknodehierarchy 36 cols, inventoryproduct 44, units copied into every row | Wide tables, repeated data |
| Duplicate reference data | globalrefdata vs gref_data; refdata vs asset_master_data; five product stores; languages hardcoded (ENG/TEL, ENG/TEL/TAM) | Inconsistent labels |
| Three error stores, two notification stores | sync_errors, sync_error_logs, error_logs; notifications vs comment rows | Confusing error UI |
| JSON blobs and year columns | farmingModelLineItemDTOs, annualActionPlanData2526, NFCategory2526 | Not queryable; new column every year |
| Mixed time types | TEXT millis, INTEGER millis, Date | Wrong sorting |
| Privacy | Unencrypted PII, plain session token, full DB in share-logs zip | Data exposure risk |
| God repository | DataRepository.kt, 3,395 lines | Every feature coupled to one class |
Target data model
One encrypted database, local UUID primary keys, a unique nullable server id on every synced table, real foreign keys to local ids, and epoch-millis INTEGER times.
| Entity | Key columns | Relationships | Replaces |
|---|---|---|---|
farmer | local_id PK, server_id UNIQUE, alias_id UNIQUE | belongs to geo_node (habitation, VO, SHG) | ie_flat_entity, ie + children, legacy ie |
farmer_identity, farmer_contact, farmer_family | local_id, farmer_id FK | many per farmer | ie_person_* |
geo_node | local_id, server_id, type, name_key | tree via geo_edge(parent_id, child_id) | worknode*, worknodehierarchy |
farmer_role | farmer_id FK, role_code | many per farmer | ierolemap, legacy designation |
asset | local_id, server_id, farmer_id FK, category_code, area, uom_code | many per farmer; asset_lease, asset_spec children | farmerassets, ie_asset |
geofence | local_id, owner_type, owner_id, file_path, server_attachment_id | one per asset / plan line / farming model | geofence columns on 4 tables |
portfolio | local_id, server_id, farmer_id FK | one per farmer | portfolio, ie_portfolio |
plan_line | local_id, server_id, title_id, portfolio_id FK, asset_id FK, season | many per portfolio | portfolio_portfoliolineitem |
plan_crop | local_id, plan_line_id FK, product_code, dates, area_share | many per plan_line | PPLI, ie_portfolio_productsku |
plan_answer | plan_line_id FK, question_code, answer | many per plan_line | portfolio_question_response |
farming_model | local_id, server_id, farmer_id FK, asset_id FK, model_type | farming_model_crop children | farming_model and its JSON crops |
harvest | local_id, server_id, crop_type (plan or model), crop_id | one or more per crop | harvest_logs, farming_model_harvest, JSON harvests |
compact_block | local_id, server_id, title_id UNIQUE | compact_block_member(block_id FK, plan_line_id FK, farmer_id FK) | compact_block, compact_block_member |
endorsement | local_id, entity_type, entity_id, status | comment children | endorsement_entity, endorsement_comment_entity |
notification | local_id, source, read_at | none | notifications, NOTIFICATIONS comment rows |
farmer_plan | farmer_id FK, plan_year, data JSON | many per farmer | AAP and NF year columns |
product, product_sku | code PK | localized_label | product*, product_master, product_sku_master, cropdata |
practice, method, ref_data, uom | code PK | localized_label | masters, globalrefdata, gref_data, refdata, asset_master_data, legacy uom |
localized_label | (entity, code, lang) PK, text | any language, no hardcoding | all *_name, *_labels, *_nameml tables |
attachment | local_id, owner_type, owner_id, path, server_id | any owner | image columns on PLI, ATM, harvest |
outbox | local_id, entity, entity_local_id, op, payload, depends_on, attempts, last_error, status | written in the same transaction as the data | sync_ledger, sync_dependency, SYNC_QUEUE_DB, row syncStatus |
sync_checkpoint | key UNIQUE, cursor, status, updated_at | none | sync_status, worknode page counters, paging prefs |
error_log, analytics_event | local_id, created_at | none | error_logs, sync_errors, sync_error_logs, user_analytics |
Session tokens move to secure storage (Keystore / EncryptedSharedPreferences, or flutter_secure_storage). Support exports should be redacted.