# FiniDB agent guide

This text is also the MCP resource `finidb://guide/full`, `finidb guide --full` and `GUIDE.md` in the npm package; the short version is the package README (`finidb guide`, `/agent`).

## 1. What FiniDB is

FiniDB is a modeling database for agents: named tables, pivot tables (a grid of line items × periods with formulas placed by condition), and *methods*, formulas attached to a condition instead of to a cell.
A changed input recomputes only its dependents, in milliseconds, even with 100,000-row fact tables; every cell can explain which method and inputs produced it.
It runs embedded (`npx finidb …` against a directory), as a daemon (`finidb serve`), or hosted at finicast.com, with the same commands.
The model is one reviewable file: `finidb apply model.json` builds it, `export script` writes it back.

Why not a spreadsheet: `=SUMIFS($C:$C,$A:$A,$A2,$B:$B,B$1)` copied over 720 cells is one method here, `frame = hist: SUM(SELECT("amount","ledger","account","=",THIS("account"),"period","=",THIS("period")))`; no coordinates, no copy-down, no ranges that break when a row is added.

## 2. Workflow and concepts

Write the whole model as one script, apply it, look, verify, deliver. `apply` is idempotent: edit the file and apply again after every change; it updates what differs (structure, methods, rows) and reports the diff.

```
npx finidb apply model.json --data ./model      # build or update the model from one file (--dry-run previews)
npx finidb query ./model pl                      # render a pivot as markdown (--cdim <id> for another value dim)
npx finidb errors ./model                        # must print "no errors" before you report done
npx finidb formula explain ./model pl value 8    # which method and inputs made cell 8 (row * columns + column)
npx finidb export ./model xlsx --out model.xlsx  # the deliverable; export ./model script --model-script writes the file back
```

MCP: `finidb_apply {script}` or `{path}`, `finidb_render`, `finidb_list_errors`, `finidb_explain`, `finidb_query` (ad-hoc formulas), `finidb_export`. `finidb_commands` (a `POST /commands` batch, §3) is the incremental path for one-off edits and for adding rows to a live database; `finidb_describe` first on a database you did not build.

```
Database › Model m1
  ledger   (tabular) id, account→accounts, period→periods, amount   fact rows
  accounts (tabular) id, Name          periods (tabular) id, Name, Frame (hist|fcst)
  pl       (pivot)   vdimIds [account]  hdimIds [period]  cdimIds [value]
                     linked pdim frame ← periods.Frame   periodsDimId period
                     methods, last match wins: frame = hist → SUM(SELECT(…)); account = gp → 'rev' - 'cogs'
```

- **Model**: a namespace with an ordered list of tables; one per database is normal.
- **Table**: `id, name, dimIds, isPivot, methods[], condObjs[], views[]`. Ids: `[0-9A-Za-z_ ,.()$&%#]`, 2–64 chars.
- **Dim**: a column; `dimIds[0]` is always `id`. Kinds: plain, **reference** (`refModelId, refTableId, refDimId`: values are ids of another table), **linked pdim**, **cdim** (pivot value dim). Types: `id, string, number, decimal, date, boolean, formula, reference`.
- **Record**: a row of a tabular table addressed by `id` (auto-generated when omitted). Rows align with `dimIds` unless a `dimIds` header names the columns (§7).
- **Pivot**: no stored records. `vdimIds` (rows, outer→inner) and `hdimIds` (columns) are reference dims whose members come from the reference table in record order; `cdimIds` are value dims; frames (`vfdimIds/hfdimIds`) show one value per row/column.
- **Linked pdim** `{dimId, linkedToPdimId, fetchDimId}`: fetches a column of a pdim's reference table for the current member, auto-placed in the frames. `Frame` from `periods` is the hist/fcst switch; a `parent` column gives levels.
- **Method** `{name, dimId, condition, formula}`: applies to every cell of `dimId` whose record/member tuple matches `condition` (empty = all). Ordered; **last matching wins**. A condition may not test the method's own dim.
- **Cond_obj** `{type, name, dimId, condition, data}`, `type ∈ style, format, validation, task, comment, censor`. **View**: saved render settings `{id, name, isDefault, spec}`. **Level**: a child pdim plus a linked pdim fetching its parent column; collapsing hides descendants, parents are ordinary members with a method.

Cell precedence: per-cell formula (`=…`) > entered value > last matching method > blank.

### 2.1 The model script

One JSON (or YAML) file `{models, tables, methods, data}` (§5.1 is a complete one). `apply` diffs it against the database: create-or-update, `--dry-run` validates and plans exactly like the real run, `--prune` also removes what the file omits. Unknown keys are rejected with their path (`tables[0].periodsDimId (did you mean pivot.periodsDimId?)`).

- A table is `{modelId, id, name?, isPivot?, dims[], pivot?, methods?, views?}`; a dim is `{id, type}`, `{id, refModelId, refTableId, refDimId}` or a linked pdim `{id, linkedToPdimId, fetchDimId}`; `pivot` is `{vdimIds, hdimIds, cdimIds, periodsDimId}` (`vdimIds` must not be empty).
- `methods` is a list of `{modelId, tableId, name, dimId, condition, formula}` at the root, or inside a table without `modelId`/`tableId`.
- `data` keys are `model:table`; a value is `{dimIds, records}` naming each value's column (preferred), or bare rows aligned with `[id, …dims]` in the table's order. Rows whose id exists are updated, new ids are created. Pivot cells (`{"dimIds":["dept","month","headcount"],"records":[["eng","2026-01",10]]}`) may sit in the same file that creates the pivot: they are applied after the structure and the reference rows.
- Bulk facts load faster with `finidb import csv <dir> facts.csv --table ledger` (first column = id, unique; types inferred, `--types date:date`).
- Every id and method name is 2–64 characters; ids are case-sensitive. `finidb export <dir> script --model-script` writes any database back in this form: the quickest way to see the shape.

## 3. The five commands you need

Send commands as a batch to `POST /commands` (MCP `finidb_commands`; atomic: a bad step rolls back the batch and names the step). Paths are relative to the database base URL (`https://finicast.com/db/<project>` hosted, `http://localhost:7407/v1/db/<db>` local daemon).

```json
[
{"type":"CREATE_TABLE","data":{"modelId":"m1","id":"periods","name":"Periods"}},
{"type":"CREATE_DIM","data":{"modelId":"m1","tableId":"periods","id":"Frame","type":"string"}},
{"type":"CREATE_MANY","data":{"modelId":"m1","tableId":"periods","records":[["2026-01","hist"],["2026-02","fcst"]]}},
{"type":"SET_PIVOT","data":{"modelId":"m1","tableId":"pl","vdimIds":["account"],"hdimIds":["period"],"cdimIds":["value"]}},
{"type":"CREATE_METHOD","data":{"modelId":"m1","tableId":"pl","name":"fcst","dimId":"value","condition":[{"left":"frame","comparison":"=","right":"fcst"}],"formula":"PREV(\"value\") * 1.05"}}
]
```

- `CREATE_MODEL {id, name}` first; `CREATE_TABLE {modelId, id, name?, isPivot?}`; `CREATE_DIM {modelId, tableId, id, type?, refModelId?, refTableId?, refDimId?}`.
- `CREATE_MANY {modelId, tableId, dimIds?, records, dupBehavior?}`: prefer the `dimIds` header, naming the column of each row value in any order (`{"dimIds":["id","rate"],"records":[["ops",0.3]]}`; omitted dims stay blank, `id` is generated). `dupBehavior` `cancel` (default) | `skip` | `update`.
- `SET_PIVOT {modelId, tableId, vdimIds, hdimIds, cdimIds}`; `SET_PERIODS_DIM_ID {modelId, tableId, periodsDimId}`; `CREATE_DIM_LINKED_TO_PDIM {modelId, tableId, dimId, linkedToPdimId, fetchDimId}`.
- `CREATE_METHOD {modelId, tableId, name, dimId, condition, formula}`; `UPDATE_METHOD {methodIdx, …}`, `MOVE_METHOD {fromIdx, destIdx}`.
- `SET_VALUE {modelId, tableId, dimId, recordId|recordIdx|coords, value}`, `UNSET_VALUE` and the batch `SET_VALUES {values:[{dimId, …, value}]}` (MCP `finidb_set_values`): address a row by `recordId` or `recordIdx`, a pivot cell by `coords: {pdimId: memberId, …}` naming every pdim (§5.3) or by `recordIdx = row * columns + column` over the members (0-based; `columns` = column-axis members) — exactly one of the three; `null` clears. `REMOVE_TABLE {…, confirm:true}`. The REST resources (§8) take the same `data` objects one at a time.

## 4. Formulas

- `THIS("dim")`: tabular → another column of the current record; pivot → a pdim's member id, a linked pdim's value (`THIS("frame")`), or another cdim of the same cell.
- `SELECT("dim","table", "d1","=",v1, "d2",">=",v2, …)`: values of `dim` for the rows of `table` matching every condition. As a scalar: one match → the value, none → blank, many → `#VALUE!`. Fused aggregates (one incremental operator): `SUM, COUNT, COUNTA, COUNTBLANK, AVERAGE, MIN, MAX, MEDIAN, FIRST, LAST, LISTAGG, RANK`.
- `LOOKUP("dim","table","key_dim", key)`: scalar `SELECT` with one equality, e.g. `LOOKUP("value","assumptions","id","growth")`.
- `PTHIS("dim","member", …)`: a cell of the same pivot by member ids; unspecified pdims stay current.
- `PREV("cdim"[, n])` / `NEXT`: same row/column, `n` periods earlier/later along `periodsDimId`; `PPRIOR("pdim", offset)` the same by explicit pdim. Out of range → blank, no `IFERROR` needed.
- `CUMULATIVE("member", offset)`, `YTD("member")`: running sums along the periods axis. `ALL("pdim")`: every member, for `SUM(ALL("account"))` totals.
- `PERIOD(date, "periods")`: the id of the period containing `date` (`start`/`end` columns, else ids `YYYY-MM`, `YYYY-Qn`, `YYYY`).
- `'term'` syntax: `'rev' - 'cogs'` reads members `rev` and `cogs` of the line-item pdim; `'prior rev'` steps one period back.
- Rounding: `INT(x)` floors toward −∞ (`INT(-3.7)` = −4); `ROUNDDOWN(x, 0)` and `TRUNC` truncate toward zero; also `ROUND`, `ROUNDUP`, `MROUND`.
- Conditions: `[{left, comparison, right, join}]`, `comparison ∈ = <> < <= > >=`, `join` `AND` (default) | `OR`; AND binds tighter than OR; `right` is a literal, compared case-sensitively.
- Blank is 0 in `+ - SUM`, skipped by `COUNT/COUNTA/AVERAGE/MIN/MAX/MEDIAN`; a lookup with no match is blank, never an error (`IFBLANK(v, alt)`).
- Errors are values that propagate: `#DIV/0!`, `#REF!`, `#NAME?`, `#VALUE!`, `#CIRC!`; `IFERROR(v, alt)`, `ISERROR(v)`.

Restrictions: **R1** table/model/dim names in `SELECT, THIS, PTHIS, PPRIOR, PREF, ALL, CUMULATIVE, LOOKUP, PERIOD` are string literals. **R2** a nested `SELECT` in a condition value is allowed; scalar equality forms stay incremental, other forms make the rule coarse. **R3** no `EVAL`. **R4** a cycle is an error, except time recursion: a dim reading itself through `PREV`/`NEXT`/`PPRIOR`/`CUMULATIVE`, and cdims of **one pivot** whose only back-edges are such shifts of each other (a *period group*, settled period by period, §5.3). Same-period cycles and shifts pointing both ways (a self `PREV` fed by a sibling's `NEXT`) are refused. **R5** `RAND`, `RANDBETWEEN`, `TODAY` recompute only on explicit `recompute`.

## 5. Recipes

Each recipe is one model script for an empty directory: save it as `model.json`, `npx finidb apply model.json --data ./model`, then `npx finidb query ./model <pivot>`. 5.2–5.4 omit `"models":[{"id":"m1"}]` for brevity; add it.

### 5.1 Income statement with hist/fcst frames and growth assumptions

```json
{"models":[{"id":"m1"}],
 "tables":[
  {"modelId":"m1","id":"periods","dims":[{"id":"Frame","type":"string"}]},
  {"modelId":"m1","id":"accounts","dims":[{"id":"Name","type":"string"}]},
  {"modelId":"m1","id":"assumptions","dims":[{"id":"value","type":"number"}]},
  {"modelId":"m1","id":"ledger","dims":[
    {"id":"account","refModelId":"m1","refTableId":"accounts","refDimId":"id"},
    {"id":"period","refModelId":"m1","refTableId":"periods","refDimId":"id"},
    {"id":"amount","type":"number"}]},
  {"modelId":"m1","id":"pl","isPivot":true,"dims":[
    {"id":"account","refModelId":"m1","refTableId":"accounts","refDimId":"id"},
    {"id":"period","refModelId":"m1","refTableId":"periods","refDimId":"id"},
    {"id":"value","type":"number"},
    {"id":"frame","linkedToPdimId":"period","fetchDimId":"Frame"}],
   "pivot":{"vdimIds":["account"],"hdimIds":["period"],"cdimIds":["value"],"periodsDimId":"period"}}],
 "methods":[
  {"modelId":"m1","tableId":"pl","name":"hist","dimId":"value","condition":[{"left":"frame","comparison":"=","right":"hist"}],
   "formula":"SUM(SELECT(\"amount\",\"ledger\",\"account\",\"=\",THIS(\"account\"),\"period\",\"=\",THIS(\"period\")))"},
  {"modelId":"m1","tableId":"pl","name":"fcst rev","dimId":"value","condition":[{"left":"frame","comparison":"=","right":"fcst"},{"left":"account","comparison":"=","right":"rev"}],
   "formula":"PREV(\"value\") * (1 + LOOKUP(\"value\",\"assumptions\",\"id\",\"growth\"))"},
  {"modelId":"m1","tableId":"pl","name":"fcst cogs","dimId":"value","condition":[{"left":"frame","comparison":"=","right":"fcst"},{"left":"account","comparison":"=","right":"cogs"}],
   "formula":"PTHIS(\"account\",\"rev\") * LOOKUP(\"value\",\"assumptions\",\"id\",\"cogs_pct\")"},
  {"modelId":"m1","tableId":"pl","name":"gp","dimId":"value","condition":[{"left":"account","comparison":"=","right":"gp"}],"formula":"'rev' - 'cogs'"}],
 "data":{
  "m1:periods":[["2026-01","hist"],["2026-02","hist"],["2026-03","fcst"],["2026-04","fcst"]],
  "m1:accounts":[["rev","Revenue"],["cogs","COGS"],["gp","Gross profit"]],
  "m1:assumptions":[["growth",0.05],["cogs_pct",0.4]],
  "m1:ledger":{"dimIds":["id","account","period","amount"],"records":[["l1","rev","2026-01",1000],["l2","cogs","2026-01",400],["l3","rev","2026-02",1100],["l4","cogs","2026-02",430]]}}}
```

Verify: `query ./model pl` → `rev` = 1000, 1100, 1155, 1212.75; `gp / 2026-03` = 693. Reconcile `rev / 2026-01` with `finidb_query` `SUM(SELECT("amount","ledger","account","=","rev","period","=","2026-01"))` = 1000. What-if: change `growth` to `0.1` in the file and re-apply (or `SET_VALUES {"values":[{"dimId":"value","recordId":"growth","value":0.1}]}`); only fcst cells change.

### 5.2 Sales-ops activity scoring (CSV → territory pivot)

Benchmark shape: 12 periods, 200 reps, 100,000 activities. Computed columns are methods on the fact table; the rows come from `finidb import csv ./model activities.csv --table activities` (`id, rep_id, type, date`; the `id` column must be unique) or, as here, from `data`.

```json
{"tables":[
  {"modelId":"m1","id":"periods","dims":[{"id":"Frame","type":"string"}]},
  {"modelId":"m1","id":"reps","dims":[]},
  {"modelId":"m1","id":"territory_list","dims":[]},
  {"modelId":"m1","id":"territories","dims":[{"id":"rep_id","refModelId":"m1","refTableId":"reps","refDimId":"id"},{"id":"territory_id","type":"string"}]},
  {"modelId":"m1","id":"scoring","dims":[{"id":"type","type":"string"},{"id":"score","type":"number"}]},
  {"modelId":"m1","id":"activities","dims":[
    {"id":"rep_id","refModelId":"m1","refTableId":"reps","refDimId":"id"},{"id":"type","type":"string"},{"id":"date","type":"date"},
    {"id":"period","type":"string"},{"id":"score","type":"number"},{"id":"territory","type":"string"}],
   "methods":[
    {"name":"period","dimId":"period","condition":[],"formula":"PERIOD(THIS(\"date\"),\"periods\")"},
    {"name":"score","dimId":"score","condition":[],"formula":"SELECT(\"score\",\"scoring\",\"type\",\"=\",THIS(\"type\"))"},
    {"name":"territory","dimId":"territory","condition":[],"formula":"SELECT(\"territory_id\",\"territories\",\"rep_id\",\"=\",THIS(\"rep_id\"))"}]},
  {"modelId":"m1","id":"territory_scores","isPivot":true,"dims":[
    {"id":"territory_id","refModelId":"m1","refTableId":"territory_list","refDimId":"id"},
    {"id":"period","refModelId":"m1","refTableId":"periods","refDimId":"id"},
    {"id":"score","type":"number"},{"id":"score_fcst","type":"number"},
    {"id":"frame","linkedToPdimId":"period","fetchDimId":"Frame"}],
   "pivot":{"vdimIds":["territory_id"],"hdimIds":["period"],"cdimIds":["score","score_fcst"],"periodsDimId":"period"},
   "methods":[
    {"name":"score","dimId":"score","condition":[],"formula":"SUM(SELECT(\"score\",\"activities\",\"territory\",\"=\",THIS(\"territory_id\"),\"period\",\"=\",THIS(\"period\")))"},
    {"name":"hist","dimId":"score_fcst","condition":[{"left":"frame","comparison":"=","right":"hist"}],"formula":"THIS(\"score\")"},
    {"name":"fcst","dimId":"score_fcst","condition":[{"left":"frame","comparison":"=","right":"fcst"}],"formula":"PREV(\"score_fcst\") * 1.05"}]}],
 "data":{
  "m1:periods":[["2026-01","hist"],["2026-02","hist"],["2026-03","fcst"],["2026-04","fcst"]],
  "m1:reps":[["r1"],["r2"]],
  "m1:territory_list":[["west"],["east"]],
  "m1:territories":[["t1","r1","west"],["t2","r2","east"]],
  "m1:scoring":[["s1","call",1],["s2","email",0.5],["s3","demo",5]],
  "m1:activities":{"dimIds":["id","rep_id","type","date"],"records":[["a1","r1","call","2026-01-05"],["a2","r1","demo","2026-01-20"],["a3","r2","email","2026-02-02"]]}}}
```

The activity rows name their columns with `dimIds` because the table's last three dims are computed: a positional row `["a1","r1","call","2026-01-05",…]` would be fine too, but one with a fifth value would override the `period` method for that row. Verify: `west / 2026-01` = 6 (call 1 + demo 5); the pivot has two value dims, `query --cdim score_fcst` shows the other. Reconcile `SUM(SELECT("score","activities","period","=","2026-01"))` with the column total.

### 5.3 Headcount and payroll plan by department × month

```json
{"tables":[
  {"modelId":"m1","id":"months","dims":[{"id":"Frame","type":"string"}]},
  {"modelId":"m1","id":"departments","dims":[{"id":"salary","type":"number"},{"id":"start","type":"number"},{"id":"rate","type":"number"}]},
  {"modelId":"m1","id":"hiring","dims":[{"id":"dept","refModelId":"m1","refTableId":"departments","refDimId":"id"},{"id":"month","refModelId":"m1","refTableId":"months","refDimId":"id"},{"id":"hires","type":"number"}]},
  {"modelId":"m1","id":"plan","isPivot":true,"dims":[
    {"id":"dept","refModelId":"m1","refTableId":"departments","refDimId":"id"},
    {"id":"month","refModelId":"m1","refTableId":"months","refDimId":"id"},
    {"id":"headcount","type":"number"},{"id":"hires","type":"number"},{"id":"attrition","type":"number"},{"id":"payroll","type":"number"},
    {"id":"frame","linkedToPdimId":"month","fetchDimId":"Frame"}],
   "pivot":{"vdimIds":["dept"],"hdimIds":["month"],"cdimIds":["headcount","hires","attrition","payroll"],"periodsDimId":"month"},
   "methods":[
    {"name":"hires","dimId":"hires","condition":[],"formula":"SUM(SELECT(\"hires\",\"hiring\",\"dept\",\"=\",THIS(\"dept\"),\"month\",\"=\",THIS(\"month\")))"},
    {"name":"opening","dimId":"headcount","condition":[{"left":"frame","comparison":"=","right":"hist"}],"formula":"LOOKUP(\"start\",\"departments\",\"id\",THIS(\"dept\"))"},
    {"name":"roll forward","dimId":"headcount","condition":[{"left":"frame","comparison":"=","right":"fcst"}],
     "formula":"PREV(\"headcount\") + THIS(\"hires\") - THIS(\"attrition\")"},
    {"name":"attrition","dimId":"attrition","condition":[{"left":"frame","comparison":"=","right":"fcst"}],"formula":"INT(PREV(\"headcount\") * LOOKUP(\"rate\",\"departments\",\"id\",THIS(\"dept\")))"},
    {"name":"payroll","dimId":"payroll","condition":[],"formula":"THIS(\"headcount\") * LOOKUP(\"salary\",\"departments\",\"id\",THIS(\"dept\"))"}]}],
 "data":{
  "m1:months":[["2026-01","hist"],["2026-02","fcst"],["2026-03","fcst"]],
  "m1:departments":[["eng",9000,10,0.1],["sales",6000,4,0.2]],
  "m1:hiring":[["h1","eng","2026-02",2]]}}
```

`headcount` and `attrition` read each other, but only backwards in time, so they form a period group settled month by month (R4); no need to fold attrition into the `headcount` rule. Verify: `eng` headcount is 10, 11, 10; `attrition` (`--cdim attrition`) 1 for eng in both fcst months; payroll `eng / 2026-02` = 99000. Reconcile `SUM(ALL("dept"))` on `payroll / 2026-01` = 114000. What-if without touching the file: `SET_VALUES {"values":[{"dimId":"hires","coords":{"dept":"sales","month":"2026-02"},"value":1}]}` enters one hire for `sales / 2026-02`; an entered value beats the `hires` method for that cell.

### 5.4 Cohort retention

```json
{"tables":[
  {"modelId":"m1","id":"periods","dims":[]},
  {"modelId":"m1","id":"cohorts","dims":[{"id":"start","refModelId":"m1","refTableId":"periods","refDimId":"id"},{"id":"size","type":"number"}]},
  {"modelId":"m1","id":"rates","dims":[{"id":"name","type":"string"},{"id":"value","type":"number"}]},
  {"modelId":"m1","id":"retention","isPivot":true,"dims":[
    {"id":"cohort","refModelId":"m1","refTableId":"cohorts","refDimId":"id"},
    {"id":"period","refModelId":"m1","refTableId":"periods","refDimId":"id"},
    {"id":"users","type":"number"},
    {"id":"start","linkedToPdimId":"cohort","fetchDimId":"start"}],
   "pivot":{"vdimIds":["cohort"],"hdimIds":["period"],"cdimIds":["users"],"periodsDimId":"period"},
   "methods":[{"name":"decay","dimId":"users","condition":[],
    "formula":"IF(THIS(\"period\") < THIS(\"start\"), \"\", IF(THIS(\"period\") = THIS(\"start\"), LOOKUP(\"size\",\"cohorts\",\"id\",THIS(\"cohort\")), PPRIOR(\"period\", -1) * LOOKUP(\"value\",\"rates\",\"name\",\"retention\")))"}]}],
 "data":{
  "m1:periods":[["2026-01"],["2026-02"],["2026-03"],["2026-04"]],
  "m1:cohorts":[["c1","2026-01",1000],["c2","2026-02",800]],
  "m1:rates":[["r1","retention",0.8]]}}
```

`PPRIOR("period", -1)` reads this cdim one period back; before the first period it is blank, not an error. Verify: `c1` = 1000, 800, 640, 512; `c2` = blank, 800, 640, 512; `SUM(ALL("cohort"))` at `2026-03` = 1280. Age-dependent rates: an `ages` table with ids `1, 2, …` and `LOOKUP("rate","ages","id",MIN(age, 4))`; numeric-looking ids are numbers (§7).

### 5.5 Financial statements from Financial Modeling Prep (hosted only)

Hosted only. `finicast_import_fmp {ticker, statements:["income","balance","cashflow"], period:"annual"|"quarter", forecastPeriods?}` creates `periods` (`Frame = hist`, plus `fcst` periods on request), `accounts`, the fact table `financials` (`account, period, amount`) and a pivot `statement` with the `frame = hist` method `SUM(SELECT("amount","financials","account","=",THIS("account"),"period","=",THIS("period")))`. Add `frame = fcst` methods as in 5.1; verify one rendered total against the filing.

## 6. Verification

- `GET /errors` (`finidb_list_errors`, `finidb errors <dir>`): every cell or rule in error, with hint. Must be empty before you report done.
- `POST /models/m1/tables/pl/cells/explain {"dimId":"value","recordIdx":11}` (`finidb_explain`, `finidb formula explain <dir> pl value 11`) → coordinates, owning method, formula, read set with values, `incremental` or the coarse reason.
- Reconcile with an ad-hoc formula (`finidb_query`): `SUM(SELECT("amount","ledger","period","=","2026-01"))` against the pivot column total; `SUM(ALL("account"))` on a total row against its children. `formula parse` only parses; `finidb_query` over `--url` runs the formula as a throw-away method in one batch.
- `GET /stats` (`finidb_stats`): timings, sizes and the coarse rules to rewrite.
- Reading a render back: a pivot grid has one header row per linked pdim on the column axis, then the member-id row; each body row starts with one column per linked pdim on the row axis, then the member id. Locate a row by its id, not by position.

## 7. Pitfalls

- **Method order**: the last matching method wins; general rule first, overrides after (`MOVE_METHOD` fixes precedence).
- **Ids vs names**: conditions and `PTHIS` use member **ids** (`rev`), never display names; `THIS("account")` on a pivot is the id.
- **Bare rows are positional**: without a `dimIds` header, `data` rows and `CREATE_MANY` records line up with the table's dims in order, `id` first, computed dims included — a value on a computed column is kept as an entered value that overrides the method for that row, and a short row leaves the last columns blank. Use `{dimIds, records}` in both; `import csv` aligns by header.
- **Numbers everywhere**: numeric-looking text becomes a number, ids included (`1` → 1, `2026-01` stays text; `'1` forces text), so compare CSV ids `1, 2, …` with `1`, not `"1"`.
- **CSV import**: the first column is the record id and must be unique (`record id "eng" appears twice in the batch`); add an `id` column to fact files that have none.
- **Blank vs 0**: blank is 0 in arithmetic but skipped by `AVERAGE`/`COUNT`. **Dates**: give date dims `type: "date"` or `PERIOD` fails.
- **Case-sensitive strings**: `hist` ≠ `Hist`; spell `Frame` values consistently.
- **Cycles**: same-period cycles are refused; dims of one pivot that read each other only through period shifts are settled period by period (§5.3). `INT(x)` floors toward −∞, `ROUNDDOWN`/`TRUNC` toward zero.
- **Render cap**: a page is at most 200,000 cells (`CALCULATION_LIMIT`); window large tables with `startRow/endRow/startCol/endCol` (`finidb_render` defaults to 50 rows × 24 columns).
- **Coarse rules**: a list-valued nested `SELECT` in a condition disables incremental recalculation for that rule; `stats` lists them.
- **Names**: database names `[a-z0-9_]{2,64}`; table/dim ids `[0-9A-Za-z_ ,.()$&%#]`, never `:`. **Ids are 2–64 characters**, method names too (`"id":"m"` fails with `MODEL_ID_INVALID`). A pivot needs at least one `vdimIds` entry.
- **Destructive commands** (`REMOVE_TABLE`, `REMOVE_DIM`, `CLEAR_RECORDS` on > 1,000 rows, drop database) require `confirm: true`.
- **Pivot cells in `data`** may come in the same `apply` that creates the pivot; a cell already holding that entered value is skipped, so re-applying is a no-op.

## 8. API cheat sheet

| MCP tool | REST (relative to the database base URL) |
|---|---|
| `finidb_apply` | `POST /apply` (the model script; `dryRun`, `prune`) |
| `finidb_describe` | `GET /models`, `GET /models/{m}/tables/{t}` |
| `finidb_commands` | `POST /commands` (batch, atomic) |
| `finidb_create_table` / `finidb_create_pivot` | `POST /models/{m}/tables`, `POST …/tables/{t}/dims`, `PUT …/tables/{t}/pivot`, `POST …/tables/{t}/linked-pdims` |
| `finidb_add_method` / `finidb_set_values` | `POST …/tables/{t}/methods` / `PUT …/tables/{t}/cells` |
| `finidb_import_csv` | `POST /import/csv?modelId=&tableId=` |
| `finidb_render` | `POST …/tables/{t}/render`; `POST /render` (multi-table) |
| `finidb_query` | no REST route: a scratch method in one `POST /commands` batch |
| `finidb_explain` | `POST …/tables/{t}/cells/explain` `{dimId, recordId|recordIdx|coords}` |
| `finidb_parse_formula` | `POST /formulas/parse` |
| `finidb_list_errors` | `GET /errors` |
| `finidb_export` | `GET /export/xlsx`, `GET /export/csv?tableId=`, `GET /export/script` |
| `finidb_stats` | `GET /stats` |
| `finidb_guide` / `finidb_functions` | `/guide` / `/docs/functions` on finicast.com (resources `finidb://guide`, `finidb://functions`) |
| change feed | `GET /events?tables=m1:pl` (SSE) |

Every command that names a row (`SET_VALUE(S)`, `UNSET_VALUE`, `UPDATE_RECORD`, `REMOVE_RECORD`, `MOVE_RECORD`, `explain`) takes exactly one of `recordId`, `recordIdx` or, for a pivot cell, `coords`; an unknown id names the table and the nearest existing id.

<a id="run-locally"></a>
### Run locally

```
npm install -g finidb
finidb serve --data ~/finidb-data --port 7407     # daemon; superuser password printed once
finidb createdb salesops --owner alice
finidb mcp --url http://localhost:7407 --db salesops --user alice
```

Embedded mode needs none of this: `npx finidb mcp --data ./model` or the CLI path in §2.

<a id="benchmarks"></a>
### Benchmarks

Engine-time targets from the benchmark suite (`finidb/bench`, B1: 100,000 activity rows, 20 × 12 territory pivot): one input change ≤ 5 ms (expected < 1 ms); a change in `scoring` touching ~16,700 rows ≤ 25 ms; editing a pivot formula ≤ 100 ms; initial load with the 100,000-row import ≤ 1,500 ms; pivot page render ≤ 3 ms. Every cell is checked against a brute-force recomputation after each step. Numbers on finicast.com are these results, nothing else.