refactor: Change amount column from String to NUMERIC/DECIMAL in Position model

[YaronZaki]

Problem Statement

The amount column in Position model (quantara/web_app/db/models.py:80) is stored as String but represents a financial quantity. All mathematical operations require runtime cast(Numeric) — e.g., in get_total_amounts_for_open_positions() at position.py:220: func.sum(cast(Position.amount, Numeric)).

Evidence

# quantara/web_app/db/models.py:80
class Position(Base):
    amount = Column(String, nullable=False)  # Stored as string!
    multiplier = Column(NUMERIC, nullable=False)  # But multiplier is NUMERIC

# quantara/web_app/db/crud/position.py:220
func.sum(cast(Position.amount, Numeric))  # Runtime cast needed

The ExtraDeposit model (line 199) and Vault model (line 163) also store amount as String.

Impact

Medium — type mismatch bugs. Sorting by amount gives lexical order ("9" > "10"). Aggregation queries require explicit casting. Decimal arithmetic requires converting strings at runtime — error-prone and may lose precision. Inconsistent with multiplier which is already NUMERIC.

Proposed Solution

Change amount columns in Position, ExtraDeposit, and Vault from String to Numeric(38, 18) via a new Alembic migration. Update all code that relies on string conversion to work with Decimal directly. 38,18 precision supports Stellar's 7 decimal places with ample headroom.

Technical Requirements

• Must create a database migration that alters the column type with proper casting
• Must handle existing data: CAST(amount AS NUMERIC(38,18)) in migration
• Must update all code that does Decimal(amount) — can be simplified
• Must not break financial calculations

Acceptance Criteria

• amount column changed to Numeric(38, 18) in Position, ExtraDeposit, Vault models
• Alembic migration created and tested for upgrade and downgrade paths
• Runtime cast(Numeric) calls removed (no longer needed)
• All financial calculations verified correct after migration
• All existing tests pass

File Map

• quantara/web_app/db/models.py:80,163,199 — change amount column types
• quantara/web_app/alembic/versions/ — New: migration for column type change
• quantara/web_app/db/crud/position.py:220 — remove cast(Numeric) after migration
• quantara/web_app/tests/db/ — update tests for Decimal amounts

Dependencies

• Related: REPO-023 (migration round-trip tests would catch downgrade issues)

Testing Strategy

• Unit: Test financial calculations with Decimal amounts directly (no string conversion)
• Integration: Run migration upgrade and downgrade on test database with existing data, verify no data loss
• Manual: Verify position values, health ratios, and dashboard calculations after migration

Security Considerations

Financial calculation correctness is critical for a DeFi protocol. Test edge cases: very large amounts, very small amounts, zero, negative amounts. Verify Decimal precision doesn't introduce rounding errors.

Definition of Done

• Code implemented and peer-reviewed
• Migration created and tested (upgrade + downgrade)
• Tests written and passing
• PR linked and merged

---

Labels: refactoring
Priority: Medium
Difficulty: Advanced
Estimated Effort: 4h

[chuks68]

good day, i would love to work on this issue, having worked on previous issues, i am confident i can resolve this issue
My ETA is 4-5 hours

[grantfox-oss]

🦊 GrantFox — @chuks68 has been assigned to this issue as part of the Official Campaign campaign!

Next steps:

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

Good luck! Track your progress on GrantFox.