# Reporting tables

> Every table and column in the Parquet files Roiva builds for Power BI and data warehouses: what a row is, and what each column means.
>
> Source: https://roiva.ai/docs/reference/reporting-tables
> Generated from what Roiva ships, on every deploy.

Roiva's figures as flat, typed tables, for Power BI or a data warehouse to read without an API client: initiatives, approved value with its formula version and inputs, costs, approvals, budgets against spend, portfolio decisions, locked months and AI spend. [Roiva's figures in Power BI and your data warehouse](https://roiva.ai/docs/reporting-tables-power-bi-and-warehouses) covers connecting.

#### How they're built

Once an Owner or Admin turns them on under **Organization Settings → Reporting Tables**, Roiva builds a **snapshot** every night: every table below, read at one moment, as a Parquet file each. Every row carries the snapshot's `snapshot_id`. A table is always whole, so a row deleted in Roiva is simply absent from the next snapshot; there's nothing to reconcile incrementally.

Before a snapshot can be read, Roiva rebuilds every figure in `reported_totals` from the `reported_value` and `costs` rows it's about to publish and compares them, to the cent, with what its own reports compute. If any differs, the snapshot isn't published and Roiva is alerted.

#### What the figures follow

- **Approved value only.** `reported_value` holds entries approved by a person or by the account's auto-approve setting, which is what every Roiva report counts. Drafts, entries awaiting review and disputed entries aren't in it; `approval_decisions` records what was decided about them.
- **Every cost counts.** A cost has no approval step, so `costs` holds every cost entry.
- **An entry belongs to the period its `period_start` falls in**, in whole UTC days, as in every Roiva report. A twelve-month entry starting in January counts in Q1 and in no other quarter. An entry with no period counts in all-time figures only.
- **No sample data.** Sample initiatives, and everything recorded against them, are left out of every table.
- **Money is integer cents** beside a currency. People are an id and a name, never an email address. Times are UTC.
- **Value adds up in dollars.** An entry keeps the currency its source gave it, and `reported_value.usd_amount_cents` is its value in US dollars, at the European Central Bank's average rate over its period. That is the column the totals add. It is empty, and the entry in no total, while it waits on its rate.
- **So does cost.** `costs.usd_amount_cents` is each cost in US dollars, added the same way. A cost whose period's rates aren't all published yet counts at the latest ECB rate, marked `usd_rate_provisional`, so a known cost is never left out; a later snapshot carries the period's average, and the month can't be locked until it does. A cost in a currency the ECB doesn't publish has no dollar figure and is in no total.

#### Reading them

The newest snapshot's manifest is at `https://roiva.ai/api/v1/reporting/latest`, and each table at `https://roiva.ai/api/v1/reporting/latest/<table>.parquet`, with a reporting token as a Bearer token or as the password in Basic authentication. The schema itself is public, at [`/api/v1/reporting/schema.json`](https://roiva.ai/api/v1/reporting/schema.json). Snapshots are kept for 14 days, and the newest until a newer one replaces it; the [Read API](https://roiva.ai/docs/reference/api) reference lists the endpoints.

Roiva can also deliver each build to an Amazon S3 bucket you own, writing through a role you create that can only add files to one folder. Each build gets its own folder, `roiva/v1/snapshots/<snapshot_id>/`, holding one Parquet file per table and a `manifest.json` written after them, and nothing already in the bucket is replaced.

#### What changes, and what doesn't

The files follow version v1 of this schema. Within a version, tables and columns are only added, and an added column may be empty. Removing, renaming or retyping a column would be a new version, published beside this one.

Columns marked **free text** hold what a person wrote in their own words, such as a note on a figure or the reason for a decision, and can name people or customers.

## snapshot
One row: the snapshot itself. When the tables were built, for which account, and what they leave out. Join any table to it on snapshot_id.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| schema_version | string; always present | The version of this schema the files follow. |
| generated_at | timestamp (UTC); always present | The moment every table in the snapshot describes, in UTC. |
| as_of | date; always present | The day the snapshot describes, in UTC. "To date" figures run through it. |
| account_id | int64; always present | The account's id in Roiva. |
| account_name | string; always present | The account's name. |
| fiscal_year_start_month | int64; always present | The month the account's fiscal year starts, 1 to 12. Fiscal quarters and years in reported_totals follow it. |
| sample_initiatives_excluded | int64; always present | How many sample initiatives the account holds. Their figures are invented for a demonstration, so no table includes them. |

## initiatives
One row per initiative, sample initiatives excluded. The account's AI initiatives: where each stands, who owns it, and its business case. The planned figures are the business case, which can change. What was signed off is in budgets.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| initiative_id | int64; always present | The initiative's id in Roiva. Every other table joins on it. |
| title | string; always present | The initiative's name. |
| stage | string; always present | Where it is in its lifecycle: identified, reviewing, implementing, live, paused, completed, rejected, dismissed. |
| previous_stage | string; may be empty | The stage it was in before it was paused, for a paused initiative. |
| category | string; always present | The kind of work it does. |
| module_kind | string; may be empty | The Roiva module it belongs to. |
| department | string; may be empty | The department it serves. |
| business_unit | string; may be empty | The business unit it serves. |
| priority | int64; may be empty | Its priority, 1 highest. |
| owner_id | int64; may be empty | The person who owns it. |
| owner_name | string; may be empty | The owner's name. |
| started_at | timestamp (UTC); may be empty | When work on it began. |
| went_live_at | timestamp (UTC); may be empty | When it went live. |
| closed_at | timestamp (UTC); may be empty | When it was completed or retired. |
| archived_at | timestamp (UTC); may be empty | When it was dismissed. |
| planned_capex_cents | int64 (cents); may be empty | The business case's one-off cost, in cents. |
| planned_opex_cents | int64 (cents); may be empty | The business case's running cost per value period, in cents. |
| planned_cost_savings_cents | int64 (cents); may be empty | The business case's cost savings per value period, in cents. |
| planned_revenue_uplift_cents | int64 (cents); may be empty | The business case's revenue uplift per value period, in cents. |
| planned_capacity_cents | int64 (cents); may be empty | The business case's freed capacity per value period, in cents: hours priced at what they cost. Not cash, and not in planned_value_cents. |
| planned_value_period | string; may be empty | The period the planned per-period figures are stated for. |
| planned_value_cents | int64 (cents); always present | Planned cash value over the whole ROI horizon, in cents: the per-period cost savings and revenue at its margin, annualized, times the horizon. |
| roi_horizon_years | int64; may be empty | The years the business case is measured over. |
| time_to_value_months | int64; may be empty | The months the business case expected before value arrives. |
| description | string; may be empty · free text | What the initiative is, as its team wrote it. |
| hypothesis | string; may be empty · free text | What its team expects it to change, in their words. |
| created_at | timestamp (UTC); always present | When it was added to Roiva. |
| roiva_url | string; always present | Its page in Roiva. |

## reported_value
One row per approved value entry, sample initiatives excluded. The value the account's initiatives have returned, approved by a person or by the account's auto-approve setting. An entry belongs to the period its period_start falls in, as in every Roiva report, so a twelve-month entry starting in January counts in Q1 and in no other quarter. Cash value is the sum of usd_amount_cents over the cash types (revenue_gain, cost_savings, cost_avoidance, capacity_realized). Freed capacity is never added to it: for each initiative and period_month it is capacity less capacity_realized, floored at zero.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| value_entry_id | int64; always present | The entry's id in Roiva. |
| initiative_id | int64; always present | The initiative it belongs to. |
| amount_cents | int64 (cents); always present | The approved figure, in cents of currency. |
| currency | string; always present | The currency of amount_cents. |
| usd_amount_cents | int64 (cents); may be empty | The approved figure in US dollars, in cents: amount_cents itself for a dollar entry, converted at usd_rate for another currency, and empty while the entry waits on the ECB's rate for its period. reported_totals add this column. |
| usd_rate | double; may be empty | US dollars per unit of currency: the average of the European Central Bank's daily euro reference rates over usd_rate_from to usd_rate_through, crossed through the euro. Empty for a dollar entry. |
| usd_rate_from | date; may be empty | The first ECB day usd_rate averages. |
| usd_rate_through | date; may be empty | The last ECB day usd_rate averages. |
| entry_type | string; always present | What kind of value it is: revenue_gain, cost_savings, cost_avoidance, capacity, capacity_realized. |
| period_start | timestamp (UTC); may be empty | When the period it covers begins. It decides which period the entry counts in. |
| period_end | timestamp (UTC); may be empty | When the period it covers ends. |
| period_month | date; may be empty | The first day of the month period_start falls in, in UTC. |
| is_estimated | boolean; always present | Whether whoever recorded it marked it an estimate. |
| confidence_score | double; may be empty | The confidence recorded with it, 0 to 100. |
| source_type | string; always present | How it reached Roiva: manual (typed in), assistant (drafted by the Roiva Assistant for the person in recorded_by_id), csv (imported), calculated (by a formula) or integration (synced from a connection). |
| provenance | string; always present | Where the number came from, in the phrase an approver is shown. |
| connection_id | int64; may be empty | The connection it was synced from. |
| platform | string; may be empty | The platform that connection reads. |
| source_ref | string; may be empty | The id the originating system knows it by. |
| formula_id | int64; may be empty | The formula that calculated it. |
| formula_key | string; may be empty | That formula's key, stable across its versions. |
| formula_name | string; may be empty | That formula's name. |
| formula_version | int64; may be empty | The formula version it was computed with, recorded when it was evaluated. A formula revised later keeps this row at the version that produced it. |
| formula_expression | string; may be empty | The expression it was computed with, as recorded when it was evaluated. |
| input_snapshot | string (JSON); always present | The inputs frozen with the figure, as JSON with sorted keys. A formula entry holds one object per input, flattened in value_inputs; a synced entry holds what the sync counted. |
| input_snapshot_sha256 | string; always present | SHA-256 of input_snapshot. Roiva computes the same digest for the same inputs, so a figure can be checked against the entry in Roiva. |
| vendor_price_effect_cents | int64 (cents); may be empty | Of a saving in AI spend, the part the provider's own price changes gave, in cents. Empty where the entry carries no such split. |
| own_changes_effect_cents | int64 (cents); may be empty | Of the same saving, the part the account's own choices earned, in cents. |
| notes | string; may be empty · free text | What a person wrote about the figure, in their own words. |
| recorded_by_id | int64; may be empty | The person who recorded it, or who had the Roiva Assistant draft it (source_type assistant). Empty when Roiva calculated or synced it. |
| recorded_by_name | string; may be empty | Their name. |
| approved_at | timestamp (UTC); may be empty | When it was approved. |
| approved_by_id | int64; may be empty | The person who approved it. Empty when the account's auto-approve did. |
| approved_by_name | string; may be empty | Their name. |
| approval_source | string; always present | auto when the account's auto-approve setting approved it, direct when a person did. |
| created_at | timestamp (UTC); always present | When it was recorded. |
| updated_at | timestamp (UTC); always present | When it last changed, or its month was locked or reopened. |
| roiva_url | string; always present | Its page in Roiva, with its inputs and approval history. |
| basis | string; may be empty | What it was compared with: measured (a baseline measured before go-live), reconstructed (one measured after go-live on work done without the AI), estimated (a benchmark or a judgment, or a figure its recorder marked an estimate) or none (no baseline: a count priced per unit, or a figure a person entered). |

## value_inputs
One row per input of each approved formula entry. What each calculated figure was computed from, as frozen when it was evaluated. Join to reported_value on value_entry_id, and to metric_readings on observation_id.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| value_entry_id | int64; always present | The approved entry these are the inputs of. |
| initiative_id | int64; always present | The initiative the entry belongs to. |
| variable | string; always present | The input's name in the formula's expression. |
| label | string; may be empty | What the input is, in words. |
| value | double; may be empty | The value the formula used. |
| source_type | string; may be empty | Where the value came from: observation (a metric reading for the period), baseline (the reading before the initiative), assumption, period, or derived (computed from other inputs). |
| metric_key | string; may be empty | The metric the value was read from. |
| observation_id | int64; may be empty | The metric reading used. Its row is in metric_readings. |
| assumption_id | int64; may be empty | The assumption used, for an assumption input. |
| assumption_key | string; may be empty | That assumption's key. |
| assumption_scope | string; may be empty | system for one of Roiva's defaults, account for the account's own figure. |
| requires_confirmation | boolean; may be empty | Whether the input was one of Roiva's defaults awaiting the account's confirmation when it was used. |
| assumption_effective_from | string; may be empty | The date the assumption's figure took effect, as recorded. |
| observed_at | string; may be empty | When the reading was taken, as recorded (a date or an ISO 8601 time). |
| input_period_start | string; may be empty | The start of the period the value covers, as recorded. |
| input_period_end | string; may be empty | The end of the period the value covers, as recorded. |
| expression | string; may be empty | For a derived input, the expression that computed it. |

## value_supporting_documents
One row per document an approved value entry cites. The evidence each approved figure cites: business cases, review decks, statements of work and contracts held on the initiative in Roiva. Join to reported_value on value_entry_id.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| value_entry_id | int64; always present | The approved entry that cites it. |
| document_id | int64; always present | The document's id in Roiva. |
| initiative_id | int64; always present | The initiative the document is held on. |
| kind | string; always present | What the document is: business_case, review_deck, sow, contract, other. |
| restricted | boolean; always present | Whether it is an SOW or a contract, which Roiva shows only to owners, admins and the initiative's approvers. |
| title | string; may be empty | The document's title. Empty for a restricted document. |
| format | string; always present | file for an uploaded file, link for a link to one held elsewhere. |
| cited_at | timestamp (UTC); always present | When the entry was made to cite it. |
| roiva_url | string; always present | The citing entry's page in Roiva, where the document opens for those allowed to see it. |

## metric_readings
One row per metric reading an approved formula entry used. The readings behind the reported figures. Join to value_inputs on observation_id.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| observation_id | int64; always present | The reading's id in Roiva. |
| metric_key | string; always present | The metric it is a reading of. |
| metric_name | string; may be empty | The metric's name. |
| initiative_id | int64; may be empty | The initiative it was recorded for. Empty for an account-wide reading. |
| role | string; always present | reading for a period's figure, baseline for the figure before the initiative. |
| value | double; always present | The reading. |
| currency | string; may be empty | The currency of value for a money reading (a CRM's deals all in euros read in EUR); empty for a reading that isn't money. |
| period_start | timestamp (UTC); may be empty | When the period it covers begins. |
| period_end | timestamp (UTC); may be empty | When the period it covers ends. |
| observed_at | timestamp (UTC); may be empty | When it was observed. |
| sample_size | int64; may be empty | How many records an average was taken over, where recorded. |
| is_estimated | boolean; always present | Whether it was marked an estimate. |
| source_type | string; always present | How it reached Roiva: integration (synced from a connection), manual (typed in), assistant (recorded by the Roiva Assistant for the person in recorded_by_id), csv (imported), calculated (by a formula), system (a baseline Roiva estimated or calculated) or time_study (a time study's published mean). |
| connection_id | int64; may be empty | The connection it was synced from. |
| dimensions | string (JSON); always present | The breakdown it belongs to, such as a line of business, as JSON with sorted keys. |
| notes | string; may be empty · free text | What a person wrote about the reading, in their own words. |
| recorded_by_id | int64; may be empty | The person who recorded it by hand, or who had the Roiva Assistant record it (source_type assistant). |
| recorded_by_name | string; may be empty | Their name. |
| baseline_method | string; may be empty | For a baseline, how the before-figure was measured: history, time_study, without_ai_sample, benchmark, estimate. Empty on a reading. |
| time_study_id | int64; may be empty | For source_type time_study, the time study it is the published mean of. |
| valid_until | date; may be empty | For a time study's figure, the last day it stands: the study's last day plus its re-study interval. Months after it hold value calculated from it as due for re-study. |

## costs
One row per cost entry, sample initiatives excluded. What the account's initiatives cost to build and run. An entry belongs to the period its period_start falls in, as value does. capex and opex split the same rows, so they add up to the total.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| cost_entry_id | int64; always present | The entry's id in Roiva. |
| initiative_id | int64; always present | The initiative it is booked to. |
| amount_cents | int64 (cents); always present | What it cost, in cents of currency. |
| currency | string; always present | The currency of amount_cents. |
| usd_amount_cents | int64 (cents); may be empty | What it cost in US dollars, in cents: amount_cents itself for a dollar cost, converted at usd_rate for another currency. Empty, and in no total, when there is no rate: a currency the ECB doesn't publish, or a cost whose month was locked before it was converted. An undated cost takes the rate for the day it was booked. reported_totals add this column. |
| usd_rate | double; may be empty | US dollars per unit of currency: the average of the European Central Bank's daily euro reference rates over usd_rate_from to usd_rate_through, crossed through the euro. Empty for a dollar cost. |
| usd_rate_from | date; may be empty | The first ECB day usd_rate averages. |
| usd_rate_through | date; may be empty | The last ECB day usd_rate averages. |
| usd_rate_provisional | boolean; may be empty | Whether usd_rate is the latest ECB rate rather than its period's average, because the period's rates weren't all published yet. A later snapshot carries the average, unless the month was locked before then. |
| cost_category | string; always present | capex for a one-off cost to build, opex for a cost to run. |
| cost_type | string; always present | What kind of cost it is, such as licenses, infrastructure or labor. |
| period_start | timestamp (UTC); may be empty | When the period it covers begins. It decides which period the entry counts in. |
| period_end | timestamp (UTC); may be empty | When the period it covers ends. |
| period_month | date; may be empty | The first day of the month period_start falls in, in UTC. |
| is_estimated | boolean; always present | Whether it was marked an estimate. |
| locked | boolean; always present | Whether a closed accounting period holds it, so it can no longer be changed. |
| vendor_id | int64; may be empty | The vendor it was paid to, in Roiva's vendor list. |
| vendor_name | string; may be empty | The vendor's name as recorded. |
| description | string; may be empty · free text | What the cost was for, as recorded or synced. |
| notes | string; may be empty · free text | What a person wrote about it, in their own words. |
| books_account_name | string; may be empty | The general-ledger account it was booked to, for a cost from the books. |
| source_type | string; always present | How it reached Roiva, such as manual, assistant (recorded by the Roiva Assistant), csv or integration. |
| connection_id | int64; may be empty | The connection it was synced from. |
| platform | string; may be empty | The platform that connection reads. |
| source_ref | string; may be empty | The id the originating system knows it by. |
| implementation_id | int64; may be empty | The implementation project it is attributed to, when it is one. |
| recurring_cost_id | int64; may be empty | The recurring cost that wrote it, when it is one. |
| allocation_line_id | int64; may be empty | The allocation line that split it from a shared bill, when it is one. |
| recorded_by_id | int64; may be empty | The person who recorded it by hand, or who had the Roiva Assistant record it (source_type assistant). |
| recorded_by_name | string; may be empty | Their name. |
| created_at | timestamp (UTC); always present | When it was recorded. |
| updated_at | timestamp (UTC); always present | When it last changed. |
| roiva_url | string; always present | Its page in Roiva. |

## approval_decisions
One row per approval decision, sample initiatives excluded. Who approved, rejected, disputed or sent back what, and when. A value entry's rows show how it came to be approved; join on subject_id where subject_kind is value_entry.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| approval_decision_id | int64; always present | The decision's id in Roiva. |
| subject_kind | string; always present | What was decided on: initiative, value_entry, budget, charter. |
| subject_id | int64; always present | Its id: a value_entry_id, an initiative_id or a budget_id. |
| initiative_id | int64; may be empty | The initiative it concerns. Empty once that initiative has been deleted. |
| subject_title | string; always present | What it was called when the decision was taken. |
| item_label | string; may be empty | The line decided on, for a value entry or a budget. |
| decision | string; always present | The decision: approved, step_approved, rejected, disputed, sent_back. step_approved is one approver's sign-off in a chain that needs more. |
| resulting_state | string; may be empty | The state it left the subject in. |
| step | int64; may be empty | Which step of the approval chain this was. |
| total_steps | int64; may be empty | How many steps the chain had. |
| source | string; always present | How it was taken: direct, bulk, auto, backfill, assistant, import. auto is the account's auto-approve setting. |
| amount_cents | int64 (cents); may be empty | The amount decided on, in cents, for a value entry or a budget. |
| currency | string; may be empty | The currency of amount_cents. |
| reason | string; may be empty · free text | The reason the approver gave, in their own words. |
| decided_by_id | int64; may be empty | The person who decided. Empty for the account's auto-approve. |
| decided_by_name | string; may be empty | Their name. |
| decided_at | timestamp (UTC); always present | When the decision was taken. |

## budgets
One row per budget version, sample initiatives excluded. What was signed off for each initiative, version by version: its one-off cost and its running cost. This is the approved budget, not the business case. How spend compares with it is in budget_vs_actual.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| budget_id | int64; always present | The version's id in Roiva. |
| initiative_id | int64; always present | The initiative it budgets. |
| status | string; always present | pending, approved, rejected, withdrawn. Only approved versions are budgets. |
| source | string; always present | How it arose: approval (set when the initiative was approved), revision or manual. |
| capex_cents | int64 (cents); always present | The one-off cost signed off, in cents: a total, not a rate. |
| opex_cents | int64 (cents); always present | The running cost signed off, in cents per opex_cadence. |
| opex_cadence | string; always present | The period opex_cents is stated for: monthly, quarterly or annually. |
| currency | string; always present | The currency of the amounts. |
| effective_from | date; always present | The version applies from the start of this date's month. |
| ends_on | date; may be empty | The last day it applies, when it has an end. |
| reason | string; may be empty · free text | Why the version was set, in the words of whoever set it. |
| set_by_id | int64; may be empty | The person who set it. |
| set_by_name | string; may be empty | Their name. |
| decided_at | timestamp (UTC); may be empty | When it was approved, rejected or withdrawn. |
| decided_by_id | int64; may be empty | The person who decided it. |
| decided_by_name | string; may be empty | Their name. |
| decision_reason | string; may be empty · free text | The reason given with the decision, in the decider's words. |
| approval_decision_id | int64; may be empty | The approval_decisions row that decided it, when one did. |
| created_at | timestamp (UTC); always present | When the version was written. |
| roiva_url | string; always present | The initiative's budget page in Roiva. |

## budget_vs_actual
One row per budgeted initiative per as_of day: the snapshot's day, and the end of each of the last 8 complete fiscal quarters. How spend compares with the approved budget, computed as Roiva's budget pages and the Board Brief compute it. CapEx is a total against a total; OpEx is spend against the budget for the months elapsed. Only initiatives with an approved budget in effect on as_of have a row.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| as_of | date; always present | The day the comparison runs through. |
| initiative_id | int64; always present | The initiative. |
| status | string; always present | within or over, as Roiva shows it. Over when either the CapEx or the OpEx half is over. |
| budget_id | int64; always present | The budget version in effect on as_of. |
| revised | boolean; always present | Whether the budget has been revised since it was first approved. |
| capex_budget_cents | int64 (cents); always present | CapEx signed off, in cents. |
| capex_actual_cents | int64 (cents); always present | CapEx spent since the budget began, in cents. |
| opex_budget_to_date_cents | int64 (cents); always present | OpEx budget for the months from the budget's start through as_of, in cents. |
| opex_actual_cents | int64 (cents); always present | OpEx spent over those months, in cents. |
| budget_to_date_cents | int64 (cents); always present | CapEx budget plus OpEx budget to date, in cents. |
| actual_to_date_cents | int64 (cents); always present | CapEx plus OpEx spent, in cents. |
| variance_cents | int64 (cents); always present | Budget to date less actual, in cents. Positive is under budget. |
| opex_budget_fiscal_year_cents | int64 (cents); always present | The OpEx budget for the whole fiscal year as_of falls in, in cents. |
| opex_budget_fiscal_ytd_cents | int64 (cents); always present | The part of it through as_of, in cents. |
| opex_actual_fiscal_ytd_cents | int64 (cents); always present | OpEx spent in the fiscal year through as_of, in cents. |
| spent_before_budget_cents | int64 (cents); always present | Spend dated before the budget began, reported apart rather than charged to it, in cents. |
| budget_monthly_run_rate_cents | int64 (cents); always present | The version's OpEx per month, in cents. |
| actual_monthly_run_rate_cents | int64 (cents); may be empty | Average OpEx per month over the recent complete months the budget covered, in cents. Empty before one has passed. |

## portfolio_decisions
One row per portfolio decision, sample initiatives excluded. The account's decisions about its initiatives, with the figures each was taken on. The figures are all-time as of the decision, not the snapshot.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| portfolio_decision_id | int64; always present | The decision's id in Roiva. |
| initiative_id | int64; may be empty | The initiative decided on. Empty once that initiative has been deleted; the decision is kept. |
| subject_title | string; always present | The initiative's name when it was decided. |
| outcome | string; always present | continue, scale, fix, pause, retire. |
| stage_before | string; always present | The initiative's stage before the decision. |
| stage_after | string; always present | Its stage after. |
| rationale | string; always present · free text | Why, in the words of whoever decided. |
| next_review_on | date; may be empty | When it is to be reviewed again. |
| cash_value_cents | int64 (cents); always present | Approved cash value to the decision, in cents. |
| freed_capacity_cents | int64 (cents); always present | Approved freed capacity to the decision, in cents. Not cash, and not in roi. |
| cost_to_date_cents | int64 (cents); always present | Cost to the decision, in cents. |
| planned_value_cents | int64 (cents); may be empty | The business case's value over its whole horizon, in cents, at the time. |
| planned_value_to_date_cents | int64 (cents); may be empty | The business case's value to the decision, in cents. |
| roi | double; may be empty | Cash ROI at the decision: (cash value − cost) ÷ cost. Empty without both. |
| currency | string; always present | The currency of the amounts. |
| decided_by_id | int64; may be empty | The person who decided. |
| decided_by_name | string; may be empty | Their name. |
| decided_at | timestamp (UTC); always present | When. |
| roiva_url | string; may be empty | The initiative's decisions in Roiva. Empty once it has been deleted. |

## accounting_periods
One row per month finance has opened for close. The account's accounting periods and whether each is locked. A month with no row has never been through close in Roiva.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| month | date; always present | The first day of the month. |
| status | string; always present | open, in_review, locked, reopened. |
| locked | boolean; always present | Whether the month is locked now. |
| locked_at | timestamp (UTC); may be empty | When it was last locked. |
| locked_by_id | int64; may be empty | Who locked it. |
| locked_by_name | string; may be empty | Their name. |
| reopened_at | timestamp (UTC); may be empty | When it was last reopened. |
| reopened_by_id | int64; may be empty | Who reopened it. |
| reopened_by_name | string; may be empty | Their name. |
| reopen_reason | string; may be empty · free text | Why it was reopened, in their words. |

## ai_spend
One row per month per provider connection with a bill, one per month per AI vendor on the books and card feeds with a bill, plus one row per month for the card feeds together. AI spend as the providers billed it and as the books and cards paid AI vendors, before any initiative books it, and how much of it an initiative does. A provider row whose attribution is unlinked or unmapped, and an ai_vendor row whose attribution is unbooked, is spend no initiative carries. ai_billed_cents and ai_booked_cents on provider_bill and ai_vendor rows add up to the figures Finance > AI Spend shows, except that a sample initiative's booking is never counted here: the page counts one while the account has only sample data. Card rows are what corporate cards were charged, stated beside the other rows and never added to them: an AI vendor's charges are in its ai_vendor row too. Every amount is US dollars: a bill in another currency counts at its month's ECB rate (a bill on the books or cards at its day's), or the latest one while that month's rates are out, and one with no ECB rate is in no row. Card rows count charges made in US dollars.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| month | date; always present | The first day of the month. |
| source_kind | string; always present | provider_bill for a provider's own bill, ai_vendor for an AI vendor's bills on the books and card feeds, card for the card feeds. |
| connection_id | int64; may be empty | The provider connection. Empty on ai_vendor and card rows. |
| platform | string; may be empty | The provider. Empty on ai_vendor and card rows. |
| connection_name | string; may be empty | The connection's name in Roiva. Empty on ai_vendor and card rows. |
| vendor_name | string; may be empty | The AI vendor, as Roiva names it: on its list of AI vendors, or as an Owner or Admin counted it. Empty on provider_bill and card rows. |
| billed_cents | int64 (cents); always present | What was billed, or charged to the cards, that month, in cents. The whole bill: for a cloud, data platform or GitHub connection, ai_billed_cents is the AI part of it. On an ai_vendor row, the vendor's bills on the books and cards, less any part its own connection bills. |
| currency | string; always present | The currency of the amounts. |
| attribution | string; may be empty | attributed when an initiative books the connection's bill or the vendor's bills; unlinked when no initiative links the connection; unmapped when it is linked but nothing maps its costs; unbooked when no initiative books the vendor's bills. Empty on card rows. |
| set_aside | boolean; may be empty | Whether an admin marked the connection, or the vendor on every connection carrying it, as belonging to no initiative. Its spend still counts as unattributed. Empty on card rows. |
| initiative_count | int64; may be empty | How many initiatives take cost from the connection or the vendor's bills. Empty on card rows. |
| booked_cents | int64 (cents); may be empty | On a card row, the part of the charges already booked to an initiative, and on an ai_vendor row, of the vendor's bills, in cents. Empty on provider rows; ai_booked_cents is theirs. |
| ai_billed_cents | int64 (cents); may be empty | The AI part of billed_cents, in cents: all of a model provider's bill; a cloud, data platform or GitHub bill's AI services and the lines an initiative claims; all of an ai_vendor row's billed_cents. What Finance > AI Spend counts as billed. Empty on card rows. |
| ai_booked_cents | int64 (cents); may be empty | What of ai_billed_cents an initiative's links book, in cents: 0 when unlinked, unmapped or unbooked. ai_billed_cents less this is what Finance > AI Spend calls attached to nothing. Empty on card rows. |

## reported_totals
One row per window, for the portfolio and for each initiative: all time, each fiscal quarter and each fiscal year from the first with value or cost. Approved cash value and freed capacity, kept apart, with cost, net cash value and cash ROI, exactly as Roiva's reports compute them, sample initiatives excluded. Freed capacity is in neither the net value nor the ROI. A figure built from reported_value and costs must equal the row for its window; Roiva checks that before publishing a snapshot.
| Column | Type | What it means |
| --- | --- | --- |
| snapshot_id | string; always present | The snapshot this row belongs to. Every table in one snapshot describes the same moment. |
| scope | string; always present | portfolio for the whole account, initiative for one initiative. |
| initiative_id | int64; may be empty | The initiative, for an initiative row. |
| window_kind | string; always present | all_time, fiscal_quarter or fiscal_year. |
| window_key | string; always present | The window's key: all, fy2026-q1, fy2026. |
| window_label | string; always present | The window as Roiva names it. |
| from_date | date; may be empty | The window's first day. Empty for all time. |
| through_date | date; may be empty | The window's last day. Empty for all time. |
| window_complete | boolean; always present | Whether the window had ended when the snapshot was built. |
| cash_value_cents | int64 (cents); always present | Approved cash value in the window, in cents: the margin on revenue earned, costs that fell, losses and spend that didn't happen, and freed capacity shown to have become cash. |
| freed_capacity_cents | int64 (cents); always present | Approved freed capacity in the window, in cents: hours and output freed, priced at what they cost. Not cash, and in no other figure here; never add it to cash_value_cents. |
| cost_cents | int64 (cents); always present | Cost in the window, in cents. |
| net_value_cents | int64 (cents); always present | Cash value less cost, in cents. Freed capacity is not in it. |
| roi | double; may be empty | Cash ROI: net value ÷ cost. Empty unless both cash value and cost are above zero, as in Roiva. |
| capex_cents | int64 (cents); may be empty | The CapEx part of cost_cents, in cents. Portfolio rows only. |
| opex_cents | int64 (cents); may be empty | The OpEx part of cost_cents, in cents. Portfolio rows only. |
| planned_value_to_date_cents | int64 (cents); may be empty | The business cases' value for the months of the window each initiative was live, in cents: the plan the approved value is set against. Portfolio rows only; leaves out a plan Roiva filled in that no one has confirmed. |
| planned_capacity_to_date_cents | int64 (cents); may be empty | The business cases' freed capacity for the months of the window each initiative was live, in cents. Portfolio rows only; leaves out a plan Roiva filled in that no one has confirmed. |
| planned_opex_to_date_cents | int64 (cents); may be empty | The business cases' running cost for the months of the window each initiative was under way, in cents. Portfolio rows only; leaves out a plan Roiva filled in that no one has confirmed. |
| planned_capex_cents | int64 (cents); may be empty | The business cases' one-off cost, in cents. Portfolio rows only; leaves out a plan Roiva filled in that no one has confirmed. |
| vendor_price_effect_cents | int64 (cents); may be empty | Of the approved value, the part AI providers' own price changes gave, in cents. Portfolio rows only; empty where no entry carries that split. |