# FiniDB for agents

FiniDB is a modeling database: named tables, pivot tables (a grid of line items × periods with formulas placed by condition), and conditional formulas that recalculate incrementally in milliseconds.
No cell addresses, no copy-down, no ranges that break when rows are added.
A P&L with 200 accounts × 60 periods is about 20 formulas, each attached to a condition such as `account = rev AND frame = fcst`.
Facts stay in tables (100,000 ledger rows is fine); time is a dimension with `hist`/`fcst` frames; every cell can explain which formula and inputs produced it.
It runs embedded (`npx finidb`, no server), as a daemon, or hosted at finicast.com. The full guide is at `/guide`.

## Workflow: one file, then look

Write the whole model as one script (below), apply it, look, verify, deliver. Re-apply after every edit (idempotent).

```
npx finidb apply model.json --data ./model      # build or update the model from one file
npx finidb query ./model pl                      # render a pivot as markdown
npx finidb errors ./model                        # must print "no errors" before you report done
npx finidb formula explain ./model pl value 3    # which method and inputs made cell 3
npx finidb export ./model xlsx --out model.xlsx  # the deliverable for humans
npx finidb mcp --data ./model                    # MCP server (stdio) over the same directory
```

MCP: `finidb_apply {script}` (or `{path}`), `finidb_render`, `finidb_list_errors`, `finidb_explain`, `finidb_export`. `finidb_commands` (a `POST /commands` batch) is the incremental path for one-off edits; `finidb_describe` first on a database you did not build.

**Hosted** (a human creates a project at https://finicast.com/app/new and gives you the connect block):

```
claude mcp add finicast -- npx finidb mcp --url https://finicast.com/db/<project> --token <token>
# or REST: https://finicast.com/db/<project>/…  Authorization: Bearer <token>
```

Local daemon: `finidb serve --data ~/finidb-data --port 7407`, base URL `http://localhost:7407/v1/db/<db>`. REST paths below are relative to the database base URL.

## Concepts

- **Database** → **model** (a namespace) → **tables**. Ids are strings you choose (`[0-9A-Za-z_ ,.()$&%#]`, 2–64 chars).
- **Table** (tabular): ordered **dims** (columns; `dim_ids[0]` is always `id`) and **records** (rows with stable ids). Dim types: `id, string, number, decimal, date, boolean, formula, reference`.
- **Reference dim**: `refModelId/refTableId/refDimId`; its values are ids of another table (`ledger.account → accounts.id`).
- **Pivot table**: no stored records. Axes are reference dims: `vdimIds` (rows, outer→inner), `hdimIds` (columns), members taken from the reference tables in record order. `cdimIds` are the value dims; a render shows one of them at a time (the first by default, `cdimId` in `finidb_render` picks another). `periodsDimId` names the time axis.
- **Linked pdim** (`{dimId, linkedToPdimId, fetchDimId}`): shows a column of a pdim's reference table beside the axis. The **Frame mechanism**: `periods` has `id, Name, Frame` (`hist`/`fcst`); a linked pdim `frame` fetching `Frame` lets conditions say `frame = fcst`.
- **Method** = conditional formula: `{name, dimId, condition, formula}`. Applies to every cell of `dimId` whose row/member tuple matches `condition`; empty condition = all cells. Methods are ordered and **the last matching method wins**. A condition may not test the method's own dim.
- **Condition**: `[{left, comparison, right, join}]`; `comparison ∈ = <> < <= > >=`; `join` is `AND` (default) or `OR`, AND binds tighter; `right` is a literal (member id, value). String compares are case-sensitive.
- **Cell precedence**: per-cell formula (`=…`) > entered value > last matching method > blank. Blank is 0 in arithmetic and skipped by `COUNT/AVERAGE`. Errors are values: `#DIV/0!`, `#REF!`, `#NAME?`, `#VALUE!`, `#CIRC!`.
- **Cond_obj**: conditional `style, format, validation, task, censor, comment`, same condition shape. **View**: saved render settings. **Level**: a child pdim plus a linked pdim fetching its parent column; collapsing hides, roll-ups are methods.

Formulas you will write:

```
SUM(SELECT("amount","ledger","account","=",THIS("account"),"period","=",THIS("period")))
PREV("value") * (1 + LOOKUP("value","assumptions","id","growth"))
PTHIS("account","rev") * LOOKUP("value","assumptions","id","cogs_pct")
'rev' - 'cogs'                          -- other members of the line-item pdim, same column
PERIOD(THIS("date"),"periods")          -- date → "2026-03"
INT(PREV("headcount") * 0.05)           -- floor (also ROUNDDOWN, TRUNC, ROUND)
```

Rule R1: table and dim names inside `SELECT`, `THIS`, `PTHIS`, `LOOKUP`, `PERIOD` are string literals. A scalar `SELECT(...)` yields its single match, blank for none, `#VALUE!` for many. Cells may recurse through time: dims of one pivot whose only back-edges are period shifts (`PREV`/`NEXT`/`PPRIOR`/`CUMULATIVE`) are settled period by period, so `headcount = PREV("headcount") + hires - attrition` with `attrition = INT(PREV("headcount") * rate)` on a second dim is fine; a same-period cycle, or shifts pointing both ways, is refused.

## The model script (`finidb apply model.json`)

One JSON (or YAML) file. `apply` diffs it against the database (create-or-update, idempotent; `--dry-run` previews with the real validation, `--prune` also removes what the file omits). Unknown keys are rejected with their path, so copy these names exactly.

```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":"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","dimId":"value","condition":[{"left":"frame","comparison":"=","right":"fcst"}],"formula":"PREV(\"value\") * 1.05"},
  {"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","fcst"]],
  "m1:accounts":[["rev","Revenue"],["cogs","COGS"],["gp","GP"]],
  "m1:ledger":{"dimIds":["id","account","period","amount"],"records":[["l1","rev","2026-01",1000],["l2","cogs","2026-01",400]]}}}
```

- A linked pdim is a dim with `linkedToPdimId` + `fetchDimId`; `periodsDimId` sits inside `pivot`; `vdimIds` must not be empty. `methods` (and `views`) may also sit inside a table, then without `modelId`/`tableId`.
- **Name the columns**: `{dimIds, records}` in `data` and in `CREATE_MANY` maps each value to a named dim (`id` may be omitted and is generated). Bare rows are **positional** over the table's dims, `id` first, *including dims a method computes*: 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.
- Bulk facts: `finidb import csv <dir> facts.csv --table ledger` (`finidb_import_csv`): the first column is the record id and must be unique, other types are inferred (`--types date:date`). Numeric-looking text becomes a number everywhere, ids included (`1` → 1, `2026-01` stays text; `'1` forces text), so compare with `1`, not `"1"`.
- Every id and method name is 2–64 characters (`m` is rejected, `m1` is fine). `finidb export <dir> script --model-script` writes any database back in this form. Pivot cells entered by hand (`"m1:plan":{"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 rows.

## The other calls

1. **Describe**: `GET /models/{m}/tables/{t}` → dims, pivot config, methods, row counts.
2. **Batch commands** (atomic): `POST /commands` `[{"type":"CREATE_TABLE","data":{"modelId":"m1","id":"periods"}}, …]`. Shapes: `CREATE_MODEL {id}`, `CREATE_TABLE {modelId,id,isPivot?}`, `CREATE_DIM {modelId,tableId,id,type?}` or `{…,refModelId,refTableId,refDimId}`, `CREATE_MANY {modelId,tableId,dimIds?,records}`, `SET_PIVOT {vdimIds,hdimIds,cdimIds}`, `SET_PERIODS_DIM_ID {periodsDimId}`, `CREATE_DIM_LINKED_TO_PDIM {dimId,linkedToPdimId,fetchDimId}`, `CREATE_METHOD {name,dimId,condition,formula}`, `UPDATE_METHOD {methodIdx,…}`, `SET_VALUE {dimId,recordId|recordIdx|coords,value}`, `SET_VALUES {values:[…]}` — all `data` objects carry `modelId`/`tableId`. MCP: `finidb_create_pivot` and `finidb_add_method` wrap the pivot and method steps.
3. **Render**: `POST /models/m1/tables/pl/render` `{"type":"pivot","startRow":0,"endRow":50,"startCol":0,"endCol":24}`. A pivot grid starts with 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 — find a row by its id, not its position. A page is capped at 200,000 cells (`CALCULATION_LIMIT`): window large tables. Tabular: `{"type":"tabular","startIdx":0,"pageSize":100}`.
4. **Cell addresses**: a row is `recordId` or `recordIdx`, a pivot cell `coords: {pdimId: memberId, …}` (every pdim) or `recordIdx = row * columns + column` over the members (0-based, `columns` = column-axis members); exactly one, in `SET_VALUE(S)`, `UNSET_VALUE`, `REMOVE_RECORD`, `MOVE_RECORD`, `explain`. A render shows the coordinates back.
5. **Parse**: `POST /formulas/parse` `{"modelId":"m1","tableId":"pl","formula":"PREV(\"value\")*1.05"}` → `{ok, errors:[{pos,message,hint}], reads}`. Evaluating an ad-hoc formula is MCP `finidb_query` (no REST route; over `--url` it runs as a throw-away method in one batch).
6. **Explain**: `POST /models/m1/tables/pl/cells/explain` `{"dimId":"value","recordIdx":8}` → method, formula, read set with values.
7. **Import / export**: `POST /import/csv?modelId=m1&tableId=ledger` (body = file); `GET /export/xlsx`, `GET /export/script`. **Change feed**: `GET /events?tables=m1:pl` (SSE).

A failing step rolls back the whole batch and names the step. Destructive commands (`REMOVE_TABLE`, `REMOVE_DIM`, drop database) need `confirm: true`.

## Delivery checklist

1. `GET /errors` (MCP `finidb_list_errors`, CLI `finidb errors`) is empty.
2. `explain` one cell you did not expect; its read set names the inputs you meant.
3. Reconcile: `finidb_query` with `SUM(SELECT("amount","ledger","period","=","2026-01"))` against the pivot's column total.
4. Workbook for humans: `finidb export <dir> xlsx`; keep `export script --model-script` in the repo.

## More

Full guide with five recipes: https://finicast.com/guide · REST: https://finicast.com/docs/api · functions: https://finicast.com/docs/functions · https://finicast.com/llms.txt