# EVE Market Calculator — Backend Spec Amendment 01

**Date:** 2026-04-11
**Amends:** eve-market-calc-spec.md
**Reason:** Additional Go API endpoints required by UI spec (eve-market-calc-ui-spec.md §10)

---

## New Endpoints

### GET `/dashboard`

Aggregated stats for the dashboard page. Authenticated.

**Query Params:** `?region=Forge` (optional, defaults to all regions)

**Response:**
```json
{
  "opportunity_count": 142,
  "opportunity_count_yesterday": 128,
  "avg_margin": 0.312,
  "top_region": "Forge",
  "top_region_count": 98,
  "user_assignment_count": 5,
  "top_opportunities": [
    {
      "item_id": 587,
      "item_name": "Rifter",
      "region": "Forge",
      "build_cost": 2450000,
      "sell_price": 3100000,
      "profit": 650000,
      "margin": 0.265
    }
  ]
}
```

**Implementation:**
1. `opportunity_count` — `SELECT COUNT(*) FROM pruned_list WHERE date = CURRENT_DATE`
2. `opportunity_count_yesterday` — same query with `date = CURRENT_DATE - 1`
3. `avg_margin` — `SELECT AVG(bc.margin) FROM pruned_list pl JOIN build_costs bc ON ... WHERE pl.date = CURRENT_DATE` (join on latest build_costs per item/region)
4. `top_region` / `top_region_count` — `GROUP BY region ORDER BY COUNT(*) DESC LIMIT 1`
5. `user_assignment_count` — `SELECT COUNT(*) FROM assignments WHERE corp_member_id = $1`
6. `top_opportunities` — `JOIN build_costs ... ORDER BY margin DESC LIMIT 10`

---

### GET `/market/:item_id/:region`

Market order price history for a specific item in a region. Used by Market Data charts.

**Query Params:**
- `from` (optional, ISO 8601 timestamp, default 24h ago)
- `to` (optional, ISO 8601 timestamp, default now)
- `interval` (optional: `5m`, `1h`, `1d`, default `5m`)

**Response:**
```json
{
  "item_id": 587,
  "item_name": "Rifter",
  "region": "Forge",
  "interval": "5m",
  "data": [
    {
      "time": "2026-04-11T12:00:00Z",
      "best_buy": 2400000,
      "best_sell": 3100000
    },
    {
      "time": "2026-04-11T12:05:00Z",
      "best_buy": 2410000,
      "best_sell": 3090000
    }
  ]
}
```

**Implementation:**
Uses TimescaleDB `time_bucket` on `market_orders`:
```sql
SELECT
  time_bucket($interval, time) AS bucket,
  MAX(buy_price) AS best_buy,
  MIN(sell_price) AS best_sell
FROM market_orders
WHERE item_id = $1 AND region = $2 AND time BETWEEN $3 AND $4
GROUP BY bucket
ORDER BY bucket ASC;
```

---

### GET `/history/:item_id/:region`

Build cost and sell price history for an item. Used by History page charts.

**Query Params:**
- `from` (optional, ISO 8601, default 7 days ago)
- `to` (optional, ISO 8601, default now)
- `interval` (optional: `1h`, `1d`, default `1h`)

**Response:**
```json
{
  "item_id": 587,
  "item_name": "Rifter",
  "region": "Forge",
  "interval": "1h",
  "data": [
    {
      "time": "2026-04-11T12:00:00Z",
      "build_cost": 2450000,
      "sell_price": 3100000,
      "margin": 0.265
    }
  ],
  "pruned_transitions": [
    { "time": "2026-04-10T00:00:00Z", "action": "entered" },
    { "time": "2026-04-10T18:00:00Z", "action": "exited" }
  ]
}
```

**Implementation:**
```sql
SELECT
  time_bucket($interval, time) AS bucket,
  AVG(total_build_cost) AS build_cost,
  AVG(sell_price) AS sell_price,
  AVG(margin) AS margin
FROM build_costs
WHERE item_id = $1 AND region = $2 AND time BETWEEN $3 AND $4
GROUP BY bucket
ORDER BY bucket ASC;
```

`pruned_transitions` derived by comparing consecutive days in `pruned_list` for the item/region — present one day, absent the next = `exited`, vice versa = `entered`.

---

### GET `/items/search`

Item name autocomplete for UI search fields.

**Query Params:**
- `q` (required, min 2 chars) — partial item name
- `limit` (optional, default 20, max 50)

**Response:**
```json
{
  "results": [
    { "item_id": 587, "item_name": "Rifter", "group": "Frigate" },
    { "item_id": 11379, "item_name": "Rifter Blueprint", "group": "Blueprint" }
  ]
}
```

**Implementation:**
Query the EVE item database (imported static data):
```sql
SELECT type_id AS item_id, type_name AS item_name, group_name AS "group"
FROM eve_types t JOIN eve_groups g ON t.group_id = g.group_id
WHERE type_name ILIKE '%' || $1 || '%'
ORDER BY type_name ASC
LIMIT $2;
```

**Performance note:** Add a trigram index (`pg_trgm`) on `type_name` if search is slow:
```sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX eve_types_name_trgm ON eve_types USING gin (type_name gin_trgm_ops);
```

---

## Summary

| Endpoint | Joins/Queries | Auth Required |
|----------|---------------|---------------|
| `GET /dashboard` | pruned_list + build_costs + assignments | Yes |
| `GET /market/:item_id/:region` | market_orders (time_bucket) | No |
| `GET /history/:item_id/:region` | build_costs (time_bucket) + pruned_list | No |
| `GET /items/search` | eve_types + eve_groups | No |

Market and history are public (no auth) since they're read-only aggregate data. Dashboard requires auth for the user's assignment count.