Tax Reporting & Cost-Basis Lot Tracking

[robertocarlous]

Problem Statement

A user closing out positions across multiple protocols over a tax year has no way to answer "what's my realized gain/loss this year?" without manually reconstructing it from raw transaction history. Every deposit establishes a cost basis; every withdrawal or agent-driven rebalance realizes a gain or loss against that basis. This is a per-user tax document, distinct in purpose from the compliance audit-log work in #271 (which is for regulators/admins, not individual tax reporting).

Current State

There is no cost-basis tracking anywhere in the codebase today — Transaction records deposits/withdrawals but does not track lot-level acquisition price or FIFO consumption. There is also no CSV or PDF export capability anywhere in the codebase currently (confirmed by search across src/ — no relevant library is even a dependency). Both need to be built from scratch as part of this issue; this should be scoped and estimated accordingly rather than treated as "add an export button to an existing exporter."

Proposed Solution

Data Model

model CostBasisLot {
  id               String   @id @default(uuid())
  userId           String
  transactionId    String   // the deposit Transaction that created this lot
  assetSymbol      String
  amount           Decimal
  remainingAmount  Decimal
  acquiredAt       DateTime
  acquiredPriceUsd Decimal
  createdAt        DateTime @default(now())
  user             User        @relation(fields: [userId], references: [id], onDelete: Cascade)
  transaction      Transaction @relation(fields: [transactionId], references: [id])
  disposals        LotDisposal[]
  @@index([userId, assetSymbol])
}
model LotDisposal {
  id              String   @id @default(uuid())
  lotId           String
  transactionId   String   // the withdrawal/rebalance Transaction that triggered this disposal
  amountDisposed  Decimal
  disposedPriceUsd Decimal
  realizedGainUsd Decimal
  disposedAt      DateTime @default(now())
  lot             CostBasisLot @relation(fields: [lotId], references: [id])
  transaction     Transaction  @relation(fields: [transactionId], references: [id])
}

API Surface

• GET /api/portfolio/tax-report?userId=&year=2026&format=json|csv — behind requireAuth + enforceUserAccess (matching the existing :userId-scoped pattern in src/routes/portfolio.ts), returns the disposal ledger and totals for the given tax year, or a CSV stream when format=csv.

Integration Points

• A lot is created whenever a deposit's Transaction is finalized — hook into the same point in src/routes/deposit.ts / underlying service where the Transaction and Position update already happen, so lot creation is transactionally consistent with the deposit itself (not a separate, potentially-missed step).
• On withdrawal/rebalance, consume lots FIFO: walk CostBasisLot rows for the asset ordered by acquiredAt, decrementing remainingAmount and writing LotDisposal rows with the realized gain/loss, until the withdrawn amount is fully accounted for. Structure this as a pure function taking lots + disposal amount + disposal price, returning the disposals to make, so it's unit-testable without a database.
• Historical USD price at acquisition/disposal time should reuse whatever price data the platform already has (ProtocolRate/YieldSnapshot context) where available. Where it's genuinely not available for a given point in time, the report must say so explicitly for that lot/disposal rather than fabricating a price — this is a real limitation of the current data model and should be documented, not hidden.
• CSV generation: since no export library exists yet, pick one (e.g. a minimal streaming CSV writer) and establish the pattern here — this will likely be the first export feature other future export needs (e.g. transaction history export) can follow.

Edge Cases & Failure Modes

• A withdrawal amount exceeds the sum of all remaining lots for that asset (shouldn't happen if Position accounting is correct, but the disposal function must fail loudly rather than silently under-disposing if it does).
• Assets with no available historical price at the required date — flagged per-lot in the report output, and excluded from the totals with a visible caveat rather than silently treated as zero gain.
• A tax year with zero activity — return an empty-but-valid report, not an error.
• Partial-lot disposal spanning the request's date boundary (a lot acquired in one year, partially disposed in the next) — each LotDisposal is dated by its own disposedAt, so year filtering is straightforward, but this should be covered explicitly in tests.

Security & Privacy Considerations

• Tax data is sensitive financial information; this endpoint must follow the exact same requireAuth + enforceUserAccess pattern as other user-scoped financial endpoints — no new/weaker auth path.
• CSV output must not be vulnerable to CSV injection (formula injection via leading =, +, -, @ in any user-influenced field) — sanitize/escape accordingly since this is a genuinely exportable file a user might open in Excel.

Out of Scope

• LIFO or specific-lot-identification accounting methods — FIFO only for v1 (the model should make LIFO addable later without a schema change, but only FIFO needs to be implemented now).
• Jurisdiction-specific tax form generation (e.g. a literal Form 8949) — this produces the underlying ledger and totals, not a filled tax form.

Suggested Implementation Plan

1. CostBasisLot/LotDisposal models and migration; lot creation wired into the deposit path.
2. Pure FIFO consumption function with thorough unit tests, then wired into the withdrawal/rebalance path.
3. Tax report endpoint (JSON) with year filtering and gap-flagging.
4. CSV export, including injection-safe encoding.

Acceptance Criteria

• CostBasisLot/LotDisposal models + migration; lots created transactionally alongside every successful deposit
• FIFO consumption implemented as a pure, unit-tested function covering single-lot, multi-lot, and partial-lot consumption
• Tax report endpoint returns a disposal ledger + totals for a given tax year, behind the standard requireAuth/enforceUserAccess pattern
• Missing historical price data is flagged per-lot in the report, never silently treated as zero
• CSV export implemented and verified safe against formula/CSV injection
• Known limitations (price-data gaps, FIFO-only) documented in docs/
• docs/openapi.yaml updated

[Lansa-18]

Hi @robertocarlous, I'd like to work on this.

I'll implement the CostBasisLot and LotDisposal models and migration, wire lot creation transactionally into the deposit path alongside the existing Transaction and Position update, and build FIFO consumption as a pure function (taking lots, disposal amount, and disposal price, returning the disposals to make) so it's fully unit-testable in isolation. The function will cover single-lot, multi-lot, and partial-lot cases, fail loudly if withdrawal amount exceeds remaining lots, and get wired into the withdrawal/rebalance path once unit-tested. I'll add the tax report endpoint behind requireAuth + enforceUserAccess, flag missing historical prices per-lot rather than treating them as zero, and implement CSV export with formula/injection-safe encoding since this is the first export feature in the codebase.

Known limitations (price-data gaps, FIFO-only) will be documented in docs/, and docs/openapi.yaml updated for the new endpoint.

[grantfox-oss]

🦊 GrantFox — @Lansa-18 has been assigned to this issue as part of the Official Campaign | FWC26 campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #284)
2. Your PR will be reviewed by the Neurowealth maintainers

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Official Campaign | FWC26 campaign!

@Lansa-18's PR #297 was approved and merged by @robertocarlous.

🏆 @Lansa-18: You earned 40 FoxPoints for this contribution! Your current tier: Builder (1,444 total points). Track your full progress on GrantFox.

👏 Great work, @Lansa-18! Keep contributing to Neurowealth.