# FiniDB guide for agents

FiniDB is a planning-model database. Everything is name-based; there are no cell addresses.

## Objects
- **Calendar**: periods at month/quarter/year granularity, e.g. `2026-01`, `2026-Q1`, `2026`. A module's `time` dimension comes from a calendar. `rollups: ["year"]` (or `["quarter", "year"]` on months) adds roll-up periods that aggregate their children per line item (`time_aggregation`: `sum` default for numbers, `average`, `last` for balances and YTD items, `formula` for ratios/margins/growth so the default rule is re-evaluated at the year, `none`); set it with `update_line_item`. Quarter and year ids are **fiscal**: with `fiscal_year_start_month: 2` (NVIDIA), `2027-Q1` = Feb–Apr 2026 and `2027` = the fiscal year ending Jan 2027. Rules apply to base periods; a rule scoped `{time: "level = year"}` targets the year level. PREV/LAG_YEAR/YTD stay within a level. Calendars can carry **period properties** such as `frame` (hist | fcst): `create_calendar {..., properties: {frame: {type: "string", default: "hist"}}, period_properties: {"2026-04..2026-12": {frame: "fcst"}}}`; change them later with `set_property_values {calendar, property, values: {"2026-07..2026-12": "fcst"}}`.
- **List**: a dimension with members and optional properties (e.g. `account` with `sign`; `reps` with `territory`). Lists replace reference tables. Every list has a built-in `parent` property: `{id: "t1", parent: "north"}` makes a hierarchy (any depth, must stay a tree). Parent members automatically aggregate their children in every module that uses the list (`list_aggregation` per line item: `sum` default for numbers, `average`, `min`, `max`, `first`, `last`, `none`, or `formula` to evaluate the item's default rule at the parent — use that for ratios). A rule scoped to a parent member still wins.
- **Table**: fact rows (ledgers, transactions, CSV imports). Columns: number, string, boolean, date, `ref` (member of a list), `period` (calendar member), or a `lookup` (virtual: a property of the referenced member). `time: {calendar, from: date_column}` derives a `period` column.
- **Module**: a cube of dims × line items. Dims are lists (or `list.subset`) plus at most one `time`. Line items hold values and formulas.
- **Scoped formulas**: a line item has an ordered list of rules `{scope, formula}`; the first matching scope wins; the unscoped rule is the default. Scopes test only dimensions, time and member properties: `{scenario: forecast, time: "> @last_actual"}`, `{time: "frame = fcst"}` (a calendar property), `{account: "type = revenue"}` (a list property), `{account: [revenue, cogs]}`, `{time: "in 2026-01..2026-06"}`. Data-dependent logic goes inside the formula with IF. Preferred hist/fcst pattern: a `frame` property on the calendar, rules scoped `{frame: hist}` / `{frame: fcst}` (a bare property key resolves to the dimension that owns it; `{time: "frame = fcst"}` is the explicit form); moving the boundary is one set_property_values call.
- **Constants**: `@last_actual`, `@tax_rate` — usable in scopes and formulas.
- **Queries** (`finidb_query`): rows/columns are dimensions, every other dimension needs a `pages` member. `filters` keeps only matching members of a rows/columns dimension with scope syntax: `{time: ["level = year"]}` (years only), `{time: ["frame = fcst"]}`, `{time: ["fiscal_year = 2027", "level = quarter"]}` (predicates AND), `{territories: ["parent = west"]}`, `{territories: ["children_of(all)"]}`, member ids (OR), `"in 2026-Q1..2026-Q4"`. Views (`create_view`) store the same `filters`.
- Cells not covered by a rule are inputs (`set_values`). A rule cell can be overridden only if the line item has `allow_override: true`.
- Alternatives are **scenario members** inside the model (a `scenario` list), not copies of the model.

## Formulas
```
revenue = PREV(revenue) * (1 + growth)            # same module, previous period
amount[account = revenue] + amount[account = cogs] # pin a dimension member
drivers.rev_growth                                 # another module; shared dims align by name; pin the rest: assumptions.rate[dept = rd]
SUM(gl.amount WHERE gl.account = account, gl.period = time)   # aggregate a table; = <dim> groups by the current member
SUM(gl.amount MATCHING *)                          # match every table column that refs one of this module's dims (and period ↔ time)
SUM(gl.amount MATCHING * EXCEPT dept)              # ...but sum across dept
SUM(amount OVER dept)                              # collapse a dimension
SUM(rep_score.score OVER reps WHERE reps.territory = territories)   # collapse members whose property equals the current member
YTD(x)  ROLLING(x, 4)  CUMULATIVE(x)  NEXT(x)  LAG_YEAR(x)  x[time = 2026-03]  x[time = FIRST]
IF x = 0 THEN BLANK ELSE amount / x                # BLANK counts as 0 in arithmetic; ISBLANK(x) tests it
IFERROR(a / b, 0)   ROUND(x, 2)   MIN/MAX/ABS/MOD/POWER/SQRT   CONCAT/LEFT/RIGHT/LEN/UPPER   NPV(rate, cf[time = FIRST : LAST])  IRR(...)
```
Operators: `+ - * / ^`, `&` (concat), `= != < <= > >=`, `AND OR NOT`, `IN`. Percent literal `12.5%` = 0.125.

## Idioms
- Actuals from a ledger, forecast by growth: rule 1 `{scenario: actual}` = `SUM(gl.amount MATCHING *)`; rule 2 `{scenario: forecast, time: "> @last_actual"}` = `PREV(amount) * (1 + drivers.growth)`; default = `amount[scenario = actual]`.
- Subtotals: give the list a `computed: true` member (e.g. `gross_profit`) and a scoped rule for it.
- Roll-forward: `{time: "= 2026-01"}` → opening; default → `PREV(balance) + flow`. A lag makes the self-reference legal; without one you get E130.
- Margin: `IF amount[account = revenue] = 0 THEN BLANK ELSE amount / amount[account = revenue]`.
- Allocation by property: `SUM(x OVER reps WHERE reps.territory = territories)`.
- Company financial model: quarterly calendar `{granularity: quarter, fiscal_year_start_month: 2, rollups: [year], properties: {frame: {type: string, default: hist}}, period_properties: {"2027-Q3..2029-Q4": {frame: fcst}}}`; module `is` on `[time:fy]` with actuals typed into hist quarters (inputs) and `{frame: fcst}` rules like `PREV(revenue) * (1 + drivers.growth)`; `gross_profit = revenue - cogs` as the default; margins with `time_aggregation: formula`; years roll up automatically.
- Regions over territories: `add_members {list: territories, members: [{id: west}, {id: east}]}` then `set_property_values {list: territories, property: parent, values: {t1: west, t2: east}}`; region rows of every module now sum their territories, no formula needed.
- Variance: `amount[scenario = actual] - amount[scenario = budget]`.  YoY: `amount / LAG_YEAR(amount) - 1`.

## Workflow
1. `finidb_describe` to see what exists. 2. `finidb_import_csv` lands facts (it can infer the table and create list members). 3. `finidb_apply` with a batch of commands (or `finidb_apply_spec` with a whole model spec) — a batch is atomic and a failed compile rolls back. 4. `finidb_query` to read numbers; `finidb_explain` for any cell (rule, reads, dependents). 5. `finidb_list_errors` must be empty before you hand back. 6. `finidb_export` for a spec or CSV.
Prefer one line item with scoped rules over IF ladders. Put fact data in tables and drivers in modules. Pin or aggregate every dimension the target has that you do not.

## Diagnostics
E100 unknown name (a suggestion is included) · E110 unaligned dimension: pin `[dim = m]` or aggregate `SUM(... OVER dim)` · E120 type/validation · E130 cycle without a lag: use PREV · E140 scope reads data · E150 not supported yet · W200 overlapping scopes · W210 scope matches no cells.