Financial Analytics - Incremental Improvement Roadmap (Q4 2025)

This document tracks prioritized gaps and the incremental plan to address them. Each task lists outcome, acceptance criteria, dependencies, and rollout strategy. Default to container-first, Go 1.24+, secure-by-default settings.

Progress to date (current iteration)

Phase 1 delivered:
- Durable ingestion: DB-backed document_jobs with worker (FOR UPDATE SKIP LOCKED, exponential backoff). Upload enqueues and returns 201. Feature flags: ENABLE_JOB_WORKER, JOB_WORKER_INTERVAL.
- DB dedupe: Unique index on transactions and repository ON CONFLICT DO NOTHING for single/batch inserts.
- Empty-state UX: All analytics/forecast/cashflow endpoints return 200 with []/zeroed objects; cashflow latest snapshot stable DTO.
- Default window: Analytics summary defaults to last 6 months.
- Post-upload refresh: Analytics refresh, forecast regeneration, and current-month cashflow predictions + snapshot after processing.
Phase 2 in progress:
- Multi-currency scaffolding: Added currency, amount_base, base_currency; parsers set currency (fallback BASE_CURRENCY); repo computes amount_base; analytics aggregates use COALESCE(amount_base, amount); SummaryResponse.baseCurrency exposed; BASE_CURRENCY wired in compose.
- Merchant normalization foundation: Added merchants, merchant_aliases, transactions.merchant_id; ingestion enriches merchant_id via repository; transactions API returns merchantId.
- Summary endpoints hardening: Top categories/merchants return [] (not null); default lookback aligned to 6 months for consistency.

Legend

P0: Critical user impact; address first
P1: High value; next
P2: Important but not urgent

Phase 1 — Reliability, Data Quality, and UX (P0)

Durable ingestion pipeline (replace goroutine with jobs)

Outcome: Document processing is queued, retriable, observable; API returns 201 immediately, worker processes.
Implementation:
- Create document_jobs table (job_id, document_id, user_id, status, attempts, next_run_at, error, created_at, updated_at).
- Add producer on upload; background worker (same service) polls and processes jobs with exponential backoff.
- Idempotency: job key (document_id); processing re-entrant; dedupe at DB level.
Acceptance: At-least-once processing, max_attempts respected, metrics for enqueue, start, success, fail.
Dependencies: migrations; small refactor of ProcessDocument entrypoint.
Rollout: feature flag ENABLE_JOB_WORKER.

Transaction dedupe at DB layer

Outcome: Hard guarantee against duplicate rows.
Implementation: Unique index on (user_id, document_id, transaction_date, description, amount, transaction_type); use ON CONFLICT DO NOTHING in batch insert.
Acceptance: Subsequent uploads of same statement do not increase row count.
Dependencies: migration + repository upsert.

Empty-state UX (already improved) — finalize contract

Outcome: All analytics/forecast/cashflow endpoints return 200 with empty lists/zeroed objects when no data.
Acceptance: Postman collections reflect zero failures in “no data” scenarios; docs updated.
Dependencies: none.

Default analytics window = last 6 months (done)

Outcome: Meaningful results without date params.
Acceptance: Summary endpoint returns non-zero totals given recent data; docs updated.

Post-upload refresh coverage (extended)

Outcome: After upload, system refreshes analytics, regenerates forecasts, generates current-month cashflow predictions and snapshot.
Acceptance: Within ~5s of upload completion, dashboards show current month predictions and snapshot.
Config: AUTO_REFRESH_ANALYTICS, AUTO_CASHFLOW_REFRESH.

Phase 2 — Data Normalization & Performance (P1)

Multi-currency support

Outcome: Store transaction currency + base currency conversions; analytics operate in base.
Implementation: Add currency to transactions if missing; introduce exchange_rates table; convert at query or ETL; expose currency in APIs.
Acceptance: Mixed-currency statements roll up consistently; API includes baseCurrency and per-item currency where relevant.
Dependencies: migration; summary/forecast adjustments.

Merchant normalization

Outcome: Canonical merchant table + alias patterns; fuzzy matching with confidence.
Implementation: merchants + merchant_aliases; text cleaning; n-gram similarity; record merchant_id + confidence.
Acceptance: Top merchants aggregation correctly groups variants (e.g., NETFLIX*NETFLIX.COM).
Exposure: return merchantId, merchantName, matchConfidence.

Timezone normalization

Outcome: Bucketing respects user/account timezone.
Implementation: user/account TZ setting; truncate dates in that TZ; pass TZ to summary/trend queries.
Acceptance: Month boundaries match UI calendar.

Indexing & query optimization

Outcome: Predictable latency under load.
Implementation: Indexes on transaction_summaries(user_id, summary_type, period_start), transactions(user_id, transaction_date), spending_forecasts(user_id, period_start); EXPLAIN analyze hot queries; add partial indexes if helpful.
Acceptance: p95 < 200ms for summaries/trends on 50k tx/user.

Phase 3 — Forecasts & Cash Flow Depth (P1)

Forecast bootstrap & confidence surfacing

Outcome: Graceful predictions with sparse history; explicit confidence in APIs.
Implementation: Heuristics fallback (seasonal averages) when n < Nmin; return {confidence, horizon}; keep current exponential smoothing for sufficient data.
Acceptance: No 500s; UI shows low-confidence state.

Rolling forecast generation service

Outcome: Rolling next 3–6 months forecasts, refreshed nightly + on-upload.
Implementation: Cron worker; config FORECAST_HORIZON_MONTHS.
Acceptance: /forecasts/overall always returns horizon window.

Cashflow balance model & alerts

Outcome: Snapshots incorporate starting balance/source-of-truth; alerts calibrated.
Implementation: Add accounts with starting_balance, per-user thresholds; cooldown logic; recommendations payload.
Acceptance: Alerts fire with expected sensitivity; snapshot displays realistic balances.

Phase 4 — API Contract & Observability (P1/P2)

DTO casing and pagination

Outcome: Consistent camelCase across all endpoints; pagination with cursors.
Implementation: Response mappers; add pageSize, nextCursor; keep compatibility window.
Acceptance: Linter/test gate for casing; docs updated.

Warnings & meta

Outcome: When data is empty due to insufficient history, include meta.warnings or X-Warning.
Acceptance: Forecasts with sparse input still 200 + warning.

Domain metrics & tracing

Outcome: Metrics for parse/dedupe/categorize/store/summarize/forecast/snapshot; traces across pipeline.
Implementation: Datadog/OpenTelemetry counters, timers; add span boundaries.
Acceptance: Dashboards and SLOs: parse success rate, pipeline latency, refresh throughput.

Phase 5 — Security & Ops (P2)

Startup/migration hardening

Outcome: Remove auto “dirty fix” from startup; provide admin command and documented runbook.
Acceptance: Startup fails fast on migration errors; CI migration test added.

Input security & limits

Outcome: File size/type limits; antivirus scan; reject suspicious content.
Acceptance: E2E tests with malicious samples blocked.

Auth strictness & JWKS TLS

Outcome: AUTH_SKIP=false outside dev; JWKS over HTTPS only (unless explicit allow flag in non-prod).
Acceptance: CI check + runtime warning if misconfigured.

Milestones & Estimates (rough)

M1 (1–2 weeks): P0 items 1–5
M2 (2–3 weeks): Items 6–9
M3 (2–3 weeks): Items 10–12
M4 (1–2 weeks): Items 13–15
M5 (1–2 weeks): Items 16–18

Acceptance Test Checklist (per milestone)

E2E upload → analytics/forecasts/cashflow reflect updates within SLA
No 500s in empty/sparse states
Postman collections green; new tests for job worker retries, dedupe, pagination
Dashboards show pipeline and forecast metrics

Configuration Flags (proposed)

ENABLE_JOB_WORKER (bool)
AUTO_REFRESH_ANALYTICS (bool)
AUTO_CASHFLOW_REFRESH (bool)
FORECAST_HORIZON_MONTHS (int)
BASE_CURRENCY (string), FX_PROVIDER (enum)
USER_DEFAULT_TZ (string) fallback

Risks & Mitigations

Forecast quality with sparse history → heuristic fallback + confidence surfacing
Job queue complexity → start with DB-backed worker; clear retry/backoff; idempotency
Currency conversion correctness → store rate source & timestamp; audit fields

References

PRD.md, execution_plan.md, CI_CD_PIPELINE_GUIDE.md
docs/swagger/* for API contracts