Skip to content

LLD: NAV integrity (audit, de-duplication, unique key, one persistence path)

status: draft (MMDEP Design, 27 Oct). High-risk design written by Claude; the API agent checks it against the code and the read-only audit (Lane P step P2) and builds it. sprint: S2 (audit), S3 (rule, staging rehearsal), S4 (production, Atul approves) decisions: TD-050, TD-063, TD-064 overall: api/overall.md §5; phase: api/phase-1.md §3 stream B

Context

Duplicate NAV dates are visible from outside (collector, 25 Oct): - scheme 120245 has 3 duplicate dates on production and 1 on staging; - the NIFTY 50 index has 10 on production.

Nobody has measured the whole table (~37M rows), and no fix has been merged (HO-18 #5). The writers known from the code are listed below; which of them still run is open (KN; Lane S step S5 confirms):

Writer Path into the data Behaviour on an existing key
POST /save-nav (API) classifier, last row wins UNVERIFIED
lambda/navdata/lib/navdata.py (MM-Cron) direct SQL: tmp_funds_nav → funds_nav inserts only if the key is absent, so a wrong same-date value is never corrected (HO-18 #5)
missing.py, nav-history.py direct SQL UNVERIFIED
nav-bulk.py direct SQL into funds_nav2 a second NAV table: purpose and readers unknown
nifty_history_loader.py nifty_index UNVERIFIED

Atul's rule (HO-18 #5, KN) is that nothing changes until a read-only audit has been done and a persistence contract approved: no constraints, upserts or migrations before that.

Decision

Stage A: read-only audit (S2)

A script in the api repo, tools/nav_audit/audit.py, is run by a person on the server with the read-only database user (relay 18 Oct C3). It writes only to a local output folder. Outputs are public NAV data, aggregated, so they may be committed to MMDEP relay.

Output Content
summary.json rows, series and date range per table (funds_nav, funds_nav2, nifty_index); duplicate groups by type; gap counts
duplicates.csv one row per duplicate group: table, series (scheme_code or symbol), date, count, distinct values, row ids, type
gaps.csv series, from, to, business days missing (weekdays minus NSE holidays; gaps > 3 business days only)
writers.csv per series, the latest insert time and, where a column records it, the writer

Each duplicate group is classified:

Type Meaning Rule in stage B
D1 Same date, same value (to 4 decimals) Keep the row with the lowest id; delete the others
D2 Same date, different values Ask the source: the AMFI NAV history (S4) for schemes, NSE (S12) for indices. The source value is kept, every other row is deleted, and a correction is recorded. If the source has no value: keep the value of the latest write from the authoritative writer, and flag it for review
D3 Same date, one row is zero, negative or null Delete the invalid row(s); if nothing valid remains, treat it as D2

Gaps are reported, never filled here. Filling happens only from the source (AMFI history through MM-Cron's NAV history job and the ingest route) with the source recorded. No interpolation, ever (TD-050).

Stage B: the persistence contract (S3)

  1. One function writes NAV: persist_nav(records, source_id, run_id) in src/data/nav_store.py. /save-nav, /api/ingest/nav and any loader call it. Nothing else in the API writes funds_nav or nifty_index.
  2. Key: (scheme_code, nav_date) for funds_nav; (symbol, date) for nifty_index.
  3. On a write of an existing key:
  4. same value (to 4 decimals) → unchanged;
  5. different value from the authoritative source for that series (AMFI for schemes, NSE for indices) → update the row and insert a row into nav_corrections;
  6. different value from any other writer → reject that record with reason: conflict.
  7. nav_corrections (new table in mm_data): id, table, series, date, old_value, new_value, source_id, run_id, corrected_at. Corrections are never silent (TD-050).
  8. funds_nav2: no new writes. Its readers come from the inventory (S5); if there are none it is renamed _retired_funds_nav2 in stage C and dropped a month later.

Stage C: production (S4, Atul approves)

The same steps are rehearsed first, timed, on a full restored copy of production on the server (the restore drill, stgapi SG-4), and then run on production off-hours:

  1. Full mysqldump of funds_nav and nifty_index (person), with its checksum recorded.
  2. CREATE TABLE nav_dedup_backup_<date> holding every row that stage B's rules will delete or change.
  3. Apply the D1/D2/D3 rules (script tools/nav_audit/dedup.py --apply, dry run first; the dry-run output is part of the approval).
  4. Add the unique keys: ALTER TABLE funds_nav ADD UNIQUE KEY uq_scheme_date (scheme_code, nav_date), ALGORITHM=INPLACE, LOCK=NONE (MariaDB online DDL), and the same for nifty_index. The rehearsal measures the time; if it is over 30 minutes, or locks, use a copy-and-swap instead (build funds_nav_new with the key, copy, RENAME TABLE both in one statement).
  5. Deploy persist_nav (release).
  6. Re-run the audit: zero duplicates. Collector: duplicate column empty.

Rollback: - drop the unique keys; - re-insert the rows from nav_dedup_backup_<date>; - revert the release.

The rollback script ships in the same MR and is run once in the rehearsal.

C4 element

  • apiDataAccess
  • apiIngest
  • apiMfData
  • mmData

Evaluation

Assessment
V value High. Correct NAV history is the API's priority 1 (relay 17 Oct) and PE's first gate. Fund pages (R1) show it to clients
S simplicity One write function, one key, three duplicate types, source-wins for conflicts; no new service
C cost About 3 agent sessions; two person-runs on the server (audit, then production); one off-hours window
D data NAV and index values are public. The audit uses a read-only user; outputs are aggregates; no client data
L legal None. Values come from AMFI and NSE, as published

Test plan

  • Classification: fixture rows for each of D1, D2 (source has a value), D2 (source has none), and D3 → the expected keep/delete/correct decisions (tests/test_nav_dedup.py).
  • persist_nav: a new key → inserted; same value → unchanged; a different value from AMFI → updated, with one nav_corrections row; a different value from another source → rejected conflict; a batch with an internal duplicate → rejected before any write. Run against SQLite or a MariaDB container in CI on the laptop runner.
  • All writes go through it: a test greps src/ for INSERT INTO funds_nav / nifty_index outside nav_store.py: none.
  • Audit script: runs on a small fixture database; output files match the expected CSVs; it opens the connection read-only (SET SESSION TRANSACTION READ ONLY).
  • Rehearsal (evidence, not CI): on the restored copy: dry-run counts = applied counts; post-apply audit shows zero duplicates; unique key added; rollback restores the original row count and checksums.
  • After production: collector rows for 120245 and NIFTY 50 show zero duplicate dates for 7 days.

Risk

risk: high

This change deletes and changes financial history on production. Mitigations: - read-only audit first; - rules per duplicate type, with the source as referee; - a backup table plus a full dump; - a timed rehearsal on a full copy; - a dry run attached to the approval; - the rollback is tested in the rehearsal; - Atul approves stage C.

Work items

# Title Risk Model Scope paths Depends on Cx Tests
1 Read-only audit script and runbook for the person medium chatgpt-api-mmcron api: tools/nav_audit/audit.py, docs/runbooks/nav-audit.md, tests/test_nav_audit.py 3 tests/test_nav_audit.py
2 Run the audit on production (person), commit outputs to MMDEP relay medium chatgpt-api-mmcron MMDEP: docs/relay/data/nav-audit-/ 1 1
3 Classifier and dedup.py (dry run and apply) with source lookups high chatgpt-api-mmcron api: tools/nav_audit/dedup.py, tests/test_nav_dedup.py 2 3 tests/test_nav_dedup.py
4 persist_nav and nav_corrections; /save-nav uses it high chatgpt-api-mmcron api: src/data/nav_store.py, src/routes.py, migrations/, tests/test_nav_store.py 2 3 tests/test_nav_store.py
5 Grep guard: no NAV inserts outside nav_store.py low gemini-flash-lite api: tests/test_nav_single_writer.py 4 1 tests/test_nav_single_writer.py
6 Rehearsal on a restored full copy: timings, counts, rollback high chatgpt-api-mmcron api: docs/runbooks/nav-dedup-production.md; evidence in MMDEP relay 3,4 2 rehearsal evidence
7 Production run (person, off-hours) and release, with Atul's approval high chatgpt-api-mmcron api: release MR; production (person) 6 2 post-run audit