# Database

Postgres on palacestableserver VM. Shared across all palaces — one database, all masters.

## Tables

### masters

One row per person who has signed up.

> **Why "masters" not "users"**: `user` is a reserved word in PostgreSQL (and many SQL systems). It causes conflicts and failures without careful quoting — as it did during MAN-85 when the `user` column broke schema creation. "Master" better reflects the role (sovereign of their palace) and avoids reserved word collisions.

| Column | Type | Notes |
|--------|------|-------|
| id | serial | primary key |
| mastername | varchar | unique, login name |
| password_hash | varchar | bcrypt via pgcrypto |
| created_at | timestamptz | auto |

### palaces

One row per palace. Currently one palace per master.

| Column | Type | Notes |
|--------|------|-------|
| id | serial | primary key |
| master_id | integer | → masters.id, cascade delete |
| name | varchar | the palace name (e.g. whitehall) |
| butler_name | varchar | the butler's name (e.g. william) |
| created_at | timestamptz | auto |

### usage

One row per completed Claude session. All palaces share this table; rows are scoped by `palace`.

| Column | Type | Notes |
|--------|------|-------|
| id | serial | primary key |
| ts | timestamptz | auto, when session completed |
| date | varchar(10) | YYYY-MM-DD in PST |
| time | varchar(5) | HH:MM in PST |
| master_id | integer | → masters.id, nullable |
| palace | varchar(100) | palace name (e.g. manglasabang) |
| master | varchar(100) | mastername (e.g. junwon) |
| butler | varchar(100) | butler name (e.g. ace) |
| channel | varchar(50) | what triggered it (email, slack, heartbeat, etc.) |
| model | varchar(50) | claude model (opus, sonnet, haiku) |
| domain | varchar(50) | work domain (junwonhome, palacefund, etc.) |
| duration_ms | integer | session duration |
| duration_min | integer | duration rounded to minutes |
| tokens_in | integer | input tokens consumed |
| tokens_out | integer | output tokens produced |
| cost_usd | decimal(10,6) | total session cost |
| num_turns | integer | number of turns in the session |

## Notes

- Passwords are bcrypt-hashed using pgcrypto `crypt()` with `gen_salt('bf', 10)`
- Master data (diary, health, etc.) lives in the per-palace filesystem, not in this database
- Identity/auth and usage are the only things in SQL — everything else is files