# Migrate usage monitor to Postgres

**Linear:** MAN-85 — https://linear.app/manglasabang/issue/MAN-85/migrate-usage-monitor-to-postgres
**Status:** Ace to report progress
**Project:** palacering

## Description
## Goal

Migrate usage logging from `apps/palacemonitor/usage.jsonl` (flat file, Mac-only) to Postgres on the VM. Required for MAN-86 (multi-user): each palace's usage must be isolated and queryable. The monitor is user-facing — each palace user sees only their own usage.

## Approach

Add HTTP API endpoints to palacering for usage ingestion and retrieval. SDK POSTs to the API instead of appending to file. Monitor fetches from the API filtered by the logged-in user's palace. Postgres is the single source of truth.

## Tasks

### 1\. Create `usage` table in Postgres on prepalacering VM

```sql
CREATE TABLE usage (
  id SERIAL PRIMARY KEY,
  ts TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  date VARCHAR(10),
  time VARCHAR(5),
  user_id INTEGER REFERENCES users(id),
  palace VARCHAR(100),
  "user" VARCHAR(100),
  butler VARCHAR(100),
  channel VARCHAR(50),
  model VARCHAR(50),
  domain VARCHAR(50),
  duration_ms INTEGER,
  duration_min INTEGER,
  tokens_in INTEGER,
  tokens_out INTEGER,
  cost_usd DECIMAL(10,6),
  num_turns INTEGER
);
CREATE INDEX usage_ts_idx ON usage(ts);
CREATE INDEX usage_date_idx ON usage(date);
CREATE INDEX usage_palace_idx ON usage(palace);
```

### 2\. Add Postgres dependency to palacering

* Add `postgres` (postgres.js) to `code/palacering/package.json`
* Create `code/palacering/src/lib/db.ts` — singleton Postgres client via `DATABASE_URL` env var
* Add `DATABASE_URL=postgres://palacering@127.0.0.1:5432/palacering` to `/opt/palacering/.env` on the VM

### 3\. Add API endpoints to palacering

* `POST /api/usage/log` — accepts usage entry JSON body, inserts into Postgres. No auth required (internal SDK calls only).
* `GET /api/usage` — accepts `?period=1h|3h|6h|12h|24h|3d|7d|1m`. Filters by the palace of the logged-in user (from session). Until MAN-86 adds auth, defaults to `palace=manglasabang`. No client-side palace override accepted.

### 4\. Update `sdk/src/usage.ts`

* Change `logUsage()` to POST to `${PALACERING_URL}/api/usage/log`
* Include in body: `palace`, `user`, `butler`, `user_id` from env vars
* `PALACERING_URL` defaults to `http://localhost:6572`
* Fallback: if POST fails, append to JSONL as before

### 5\. Add env vars to Mac channels/heartbeats env

```
PALACE_USER_ID=1
PALACE_NAME=manglasabang
PALACE_USER=junwon
PALACE_BUTLER=ace
PALACERING_URL=http://localhost:6572
```

### 6\. Update monitor page (`apps/palacemonitor/src/pages/index.astro`)

* Change `loadUsage()` to `fetch('/api/usage?period=' + curTab + '&_=' + Date.now())`
* Response is a JSON array — same shape as JSONL objects, all rendering JS unchanged

### 7\. Migrate existing JSONL data

* Script: `code/palaceplatform/tools/migrate-usage-to-postgres.ts`
* Reads `apps/palacemonitor/usage.jsonl`, POSTs each entry with `palace=manglasabang`, `user=junwon`, `butler=ace`, `user_id=1`
* Reports count inserted vs total

## Tests

1. POST a usage entry → GET `/api/usage?period=24h` → entry appears
2. Run migration → count matches JSONL line count (1,302)
3. Monitor renders correctly (all 8 time tabs, charts, drill-down)
4. SDK fallback: unreachable `PALACERING_URL` → falls back to JSONL
5. GET endpoint only returns rows for `palace=manglasabang` (cannot be overridden by client)

## Files Changed

* `code/palacering/package.json` — add `postgres` dep
* `code/palacering/src/lib/db.ts` — new
* `code/palacering/src/pages/api/usage/log.ts` — new
* `code/palacering/src/pages/api/usage/index.ts` — new
* `code/palaceplatform/sdk/src/usage.ts` — change write path, add palace/user/butler/user_id
* `code/apps/palacemonitor/src/pages/index.astro` — change fetch URL
* `code/palaceplatform/tools/migrate-usage-to-postgres.ts` — new migration script