# Crypto Tax Lot Estimator — crypto-tax-lot-estimator

Upload an exchange CSV and instantly estimate FIFO/LIFO tax lots, realized PnL, and open positions. Optionally fill missing prices from CoinGecko to convert into your base currency.

## Features
- CSV ingestion from file (max 10 MB), URL fetch, or paste; auto-parse headers: date, asset, side, qty, price, fee, fee_asset, quote
- Cost methods: FIFO or LIFO; date filters; timezone-aware; auto or custom date formats
- Base currencies: USD, EUR, GBP, AUD, CAD
- Price source: CoinGecko (daily or hourly granularity) or none; cached for speed and rate-limit safety
- Outputs: Summary, PnL by asset, realized trades with short/long-term tags, open lots, and errors
- History of runs stored server-side for easy re-download and audit
- Exports: per-view CSV and full JSON payload
- Security: strict CSP, CSRF tokens, prepared statements, output escaping, rate limiting (15/min)
- UI: light theme, green accent, card layout
- Optional cron to backfill/refresh price cache
- Freemium gating: free quota 10 runs/day, pro quota 100 (configurable)
- Estimator only; does not file or reconcile taxes for you

## Requirements
- PHP 8.2+ with extensions: pdo_mysql, curl, json, mbstring, fileinfo (intl recommended)
- MySQL 8.x
- Outbound HTTPS to api.coingecko.com if using price fills

## Quick Start
1) Database
- Create DB and user in MySQL 8, then apply the schema (tables: runs, trades, lots, price_cache, rate_limits). Import the provided schema.sql if present.

2) Configure environment
- Create a .env file (or server environment variables). See the configuration table below. Minimum required: APP_DB_HOST/NAME/USER/PASS and APP_BASE_URL.
- Set APP_ENV=production and a strong APP_SECRET in production.

3) Deploy
- Place index.php in your web root (or public/) behind PHP-FPM.
- Enable HTTPS. Set secure headers (see Security).
- Ensure the upload directory is writable by PHP and not web-browsable if outside the document root.

4) First run
- Visit the app, choose Cost Method, Base Currency, Timezone, and Price Source.
- Upload a CSV or provide a CSV URL; submit to generate results.
- Download CSV/JSON exports from the results page.

5) Optional cron (price cache refresh)
- Schedule a job to backfill prices, e.g. curl "https://yourdomain/cron/price-cache?token=YOUR_TOKEN" nightly. Set APP_CRON_TOKEN and restrict access.

## Configuration and environment variables
| Variable | Required | Default | Description |
|---|---|---|---|
| APP_ENV | No | development | App environment: development or production. |
| APP_BASE_URL | Yes | — | Public base URL (e.g., https://example.com). |
| APP_SECRET | Yes (prod) | — | Secret used for CSRF/session HMAC. Keep private. |
| APP_RATE_LIMIT_PER_MINUTE | No | 15 | Requests per-IP per minute. |
| APP_MAX_UPLOAD_MB | No | 10 | Max CSV upload size (MB). |
| APP_UPLOAD_DIR | No | ./storage/uploads | Writable directory for uploads. |
| APP_TIMEZONE_DEFAULT | No | UTC | Default timezone for parsing if none provided. |
| APP_FREE_QUOTA | No | 10 | Daily free-run quota. |
| APP_PRO_QUOTA | No | 100 | Daily pro-run quota. |
| APP_CRON_TOKEN | No | — | Shared secret for cron endpoints. |
| COINGECKO_API_BASE | No | https://api.coingecko.com/api/v3 | CoinGecko base URL. |
| COINGECKO_API_KEY | No | — | CoinGecko Pro key (if applicable). |
| APP_AI_ENABLED | No | false | Toggle optional AI assist. |
| OPENAI_API_KEY | No | — | OpenAI API key (only if AI is enabled). |
| OPENAI_API_BASE | No | https://api.openai.com/v1 | OpenAI endpoint base. |
| OPENAI_MODEL | No | gpt-4o-mini | Model for optional AI assist. |
| APP_DB_HOST | Yes | 127.0.0.1 | MySQL host. |
| APP_DB_PORT | No | 3306 | MySQL port. |
| APP_DB_NAME | Yes | — | MySQL database name. |
| APP_DB_USER | Yes | — | MySQL username. |
| APP_DB_PASS | Yes | — | MySQL password. |

Notes:
- The app stores: runs, trades, lots, price_cache, and rate_limits tables.
- If using a proxy/CDN, configure forwarding and trust headers carefully before relying on IP-based rate limits.

## CSV format and parsing
- Expected headers: date, asset, side, qty, price, fee, fee_asset, quote
- date_format: auto (default) or any PHP date format (e.g., Y-m-d H:i:s)
- Timezone handling: user-selectable (UTC, America/New_York, Europe/London, Asia/Tokyo)
- side must be buy/sell; fees can be in base or a separate fee_asset
- Filters: from_date, to_date; fees_included toggles whether fees adjust proceeds/cost basis

## Price fills (CoinGecko)
- Select price_source=coingecko to convert quotes to your base currency with daily or hourly granularity.
- Historical prices are cached in price_cache. The app attempts symbol-to-ID mapping; ambiguous tickers may require manual correction or custom mapping.
- If price_source=none, the app only uses prices present in CSV.

## Outputs
- Summary: method, base_currency, trades, realized_pnl, short_term_pnl, long_term_pnl
- PnL by asset: asset, proceeds, cost_basis, pnl
- Realized trades: date, asset, side, qty, proceeds, cost_basis, pnl, term (short/long)
- Open lots: asset, qty, unit_cost, cost_basis
- Errors: list of parsing/validation issues

Short-term vs long-term uses a 365-day holding period.

## Exports
- JSON: full structured payload (summary, pnl_by_asset, realized_trades, open_lots, errors)
- CSV: per-view downloads (Realized trades, PnL by asset, Open lots)
- Content-Disposition forces file download; UTF-8 with headers included

## Security measures
- Content Security Policy (CSP): strict default (self-only), with connect permissions to CoinGecko if enabled
- CSRF protection: per-session token embedded in forms and verified on POST
- Prepared statements for all database access; no string concatenation with untrusted input
- Output escaping: escape all dynamic HTML; sanitize filenames; strict MIME checks (text/csv)
- Upload safety: extension + MIME validation, size capped to APP_MAX_UPLOAD_MB, stored outside web root or with deny rules
- URL fetch safety: only http/https, length-capped (max 300 chars), size-capped, limited redirects, no localhost/RFC1918 to mitigate SSRF
- Rate limiting: per-IP counters in rate_limits table; 429 on exceed; incremental backoff
- Secure headers: HSTS (HTTPS only), X-Frame-Options: DENY, X-Content-Type-Options: nosniff, Referrer-Policy: no-referrer, Permissions-Policy: minimal
- Session cookies: HttpOnly, Secure, SameSite=Lax
- Principle of least privilege: DB user with only required privileges for the target schema

## Optional AI behavior and fallback mode
- When APP_AI_ENABLED=true and OPENAI_API_KEY is set, AI can:
  - Suggest column mappings for messy CSVs
  - Infer date formats/timezones when ambiguous
  - Flag potentially misclassified assets or splits
- Temperature defaults to 0.0 for deterministic suggestions.
- Fallback: If AI is disabled, times out, or errors, the deterministic parser runs; the app never blocks on AI. AI output is advisory and never mutates trade math without explicit confirmation.

## Monetization (freemium)
- Enforced via simple counters per day: free quota 10 runs, pro quota 100
- Hook your auth/billing to elevate users to pro; defaults can be adjusted via APP_FREE_QUOTA and APP_PRO_QUOTA

## Acceptance checklist
- [ ] PHP 8.2+ and MySQL 8 are installed; DB schema applied
- [ ] APP_DB_* and APP_BASE_URL configured; app loads over HTTPS
- [ ] CSV upload (<=10 MB) and CSV URL ingest both work
- [ ] FIFO/LIFO computations produce realized PnL; short/long-term tagging correct
- [ ] CoinGecko price fill and caching work for daily/hourly modes
- [ ] Exports: JSON and per-view CSV download correctly
- [ ] History of runs is stored and retrievable
- [ ] Rate limit 15/min enforced; returns 429 when exceeded
- [ ] Security headers present; CSP applied; CSRF tokens verified; prepared statements in use
- [ ] Optional cron job fills price_cache without exposing secrets
- [ ] AI assist disabled by default; when enabled, app continues to function if AI fails

## License
MIT License (see LICENSE).