# KQL Reference for freispace Dashboards

Dashboard widgets use Kusto Query Language (KQL)-style queries over a curated freispace data catalogue. The catalogue exposes business entities, their documented fields and relations, and applies the current user's permissions automatically. Query the entity names and field names shown on this page; the underlying database table names are not queryable.

info

Already familiar with KQL? You can skip ahead to the [queryable entities](#queryable-entities) and start querying right away.

## Query basics[​](#query-basics "Direct link to Query basics")

A query starts from an entity and passes the result through a pipeline. The most common pipeline operations are `where`, `project`, `extend`, `summarize`, `sort`, `take` and `join`.

```
bookings

| where is_published == true

| summarize booking_count = count() by project_id

| sort by booking_count desc
```

Some of the operators you will use most often when building widgets:

| Operator    | Purpose                                                                          |
| ----------- | -------------------------------------------------------------------------------- |
| `where`     | Filter rows                                                                      |
| `project`   | Select or compute specific columns                                               |
| `summarize` | Aggregate data (e.g. `sum`, `count`, `avg`)                                      |
| `sort by`   | Sort results                                                                     |
| `top`       | Limit to the top N rows                                                          |
| `join`      | Combine rows from two tables                                                     |
| `bin`       | Group values (e.g. dates) into fixed-size buckets, useful for time-series charts |

Use the [Microsoft KQL documentation](https://learn.microsoft.com/en-us/kusto/query/?view=microsoft-fabric) for general operator and function syntax. The freispace catalogue and relation model described below determine which entities and fields are available to a dashboard query.

### Relations and joins[​](#relations-and-joins "Direct link to Relations and joins")

Use a documented relation with `join` instead of joining database tables by hand. For example, this query counts bookings per project and returns the project name:

```
projects

| join bookings on id == project_id

| summarize booking_count = count() by id, name

| sort by booking_count desc
```

Relation joins respect visibility on both sides. A left join keeps the parent row when no visible related rows exist; an inner join keeps only rows with a visible match. Link entities such as `booking_staff_links` are useful when the assignment itself has data, especially its own `starts_at` and `ends_at` window.

### Time, money and identifiers[​](#time-money-and-identifiers "Direct link to Time, money and identifiers")

* Datetimes are stored in UTC. Date grouping and date-part functions use the calling user's timezone. Dates such as `issued_on`, `start` and `end` have no time or timezone.
* Booking and assignment durations are calculated from `ends_at - starts_at`; there is no separate duration field. Assignment-level utilization must use the relevant link entity.
* Invoice and offer amounts are integer cents. `1250` means `12.50` in the document currency. Sum first and convert only for display; use `todouble` before division.
* Never add monetary values across currencies. Group by or filter on `currency` or `cost_currency` first.
* UUIDs are stable identifiers, but names and numbers are not guaranteed to be unique. Group by IDs as well as display names where duplicate names are possible.
* `NULL` values are meaningful. For example, a `NULL` project ID means that a booking is not attached to a project, and a `NULL` `locked_at` means that an invoice or offer is still a draft.

### Visibility and permissions[​](#visibility-and-permissions "Direct link to Visibility and permissions")

Every query is tenant-scoped and filtered by the caller's permissions. Soft-deleted records and fixed entity filters are applied automatically. Counts can therefore differ from totals shown to another user or elsewhere in the application. In particular, unpublished bookings, restricted projects, clients, staff, resources and suites may be absent.

The `staffs.number` field is additionally permission-gated. Without the relevant internal-staff permission, it is unavailable; with own-record access, other people's values may be returned as `NULL`. It cannot be filtered or sorted.

## Queryable entities[​](#queryable-entities "Direct link to Queryable entities")

Only entities marked as queryable in the schema can be used as a query root. Related entities can also be reached through documented relations.

### Core entities[​](#core-entities "Direct link to Core entities")

#### `bookings`[​](#bookings "Direct link to bookings")

Bookings reserve crew, suites or resources for a time range. Use `starts_at < window_end and ends_at > window_start` for overlap logic; filtering only `starts_at` finds bookings that start in the window, not every booking that overlaps it.

| Field                                 | Type               | Description                                                                                         |
| ------------------------------------- | ------------------ | --------------------------------------------------------------------------------------------------- |
| `id`                                  | UUID               | Stable booking identifier.                                                                          |
| `title`                               | string             | Booking title; not unique.                                                                          |
| `byline`                              | string, nullable   | Secondary text under the title.                                                                     |
| `description`                         | string, nullable   | Free-text description.                                                                              |
| `starts_at`, `ends_at`                | datetime           | Booking interval, stored in UTC.                                                                    |
| `is_all_day`                          | boolean            | Whether the booking is all day. For all-day bookings, compare calendar days rather than time parts. |
| `is_optional`                         | boolean            | Whether the booking is tentative and does not block availability.                                   |
| `is_published`                        | boolean            | Whether the booking is published. Unpublished rows may already be hidden by permissions.            |
| `workload_percent`                    | integer            | Whole percentage, such as `50` for 50%.                                                             |
| `project_id`, `order_id`, `status_id` | UUID, nullable     | Related project, order and team-defined booking status IDs.                                         |
| `created_at`                          | datetime, nullable | Creation timestamp in UTC.                                                                          |

Relations: `project`, `order`, `status`, `staffs`, `resources`, `suites`, `staff_links`, `resource_links`, `suite_links`, `offers` and `invoices`.

Named filters: `overlapping` accepts `from`, `to` and an optional `basis` (`effective` or `booking`); `overlapping_booking` accepts `from` and `to` and checks only the booking interval. Both require `from` to be earlier than `to` and use a half-open interval `[from, to)`.

#### `projects`[​](#projects "Direct link to projects")

Projects group bookings and orders. Project visibility restrictions apply to query results and to related bookings.

| Field          | Type             | Description                                            |
| -------------- | ---------------- | ------------------------------------------------------ |
| `id`           | UUID             | Stable project identifier.                             |
| `name`         | string           | Project name; not unique.                              |
| `number`       | string, nullable | Project number, treated as text.                       |
| `start`, `end` | date, nullable   | Planned calendar dates without timezone.               |
| `parent_id`    | UUID, nullable   | Parent project; only one hierarchy level is queryable. |
| `status`       | enum/integer     | `1` = active, `0` = inactive/archived.                 |
| `color`        | string, nullable | Display color as a hex code.                           |

Relations: `bookings`, `client_links` and `parent`.

#### `clients`[​](#clients "Direct link to clients")

Clients can be restricted to individual users. A user without access to a client may see only clients they created themselves.

| Field     | Type             | Description                                 |
| --------- | ---------------- | ------------------------------------------- |
| `id`      | UUID             | Stable client identifier.                   |
| `name`    | string           | Company or person name; not unique.         |
| `number`  | string, nullable | Client number, treated as text.             |
| `status`  | boolean          | `true` = active, `false` = deactivated.     |
| `starred` | boolean          | Whether the client is marked as a favorite. |

Relation: `project_links`.

#### `staffs`[​](#staffs "Direct link to staffs")

Staff members are available as people records. Staff cost, timezone and contract data are not exposed in this catalogue.

| Field          | Type             | Description                                                                                        |
| -------------- | ---------------- | -------------------------------------------------------------------------------------------------- |
| `id`           | UUID             | Stable staff identifier.                                                                           |
| `display_name` | string           | Calendar/list name; not guaranteed unique.                                                         |
| `title`        | string, nullable | Role or job title.                                                                                 |
| `internal`     | boolean          | `true` = internal staff, `false` = freelancer or external.                                         |
| `status`       | enum/integer     | `1` = active, `0` = inactive.                                                                      |
| `number`       | string, gated    | Internal staff number; only available with the required permission and not filterable or sortable. |

Relation: `booking_links` to `booking_staff_links`. Use that link entity for assignment windows and utilization.

#### `resources`[​](#resources "Direct link to resources")

Resources include equipment and other bookable items. A resource cost is a rate, not a spend total.

| Field           | Type             | Description                            |
| --------------- | ---------------- | -------------------------------------- |
| `id`            | UUID             | Stable resource identifier.            |
| `name`          | string           | Resource name; not unique.             |
| `number`        | string, nullable | Inventory number, treated as text.     |
| `status`        | enum/integer     | `1` = active, `0` = inactive/retired.  |
| `cost`          | number, nullable | Internal cost rate per `cost_unit`.    |
| `cost_unit`     | string, nullable | Unit such as `hour`, `day` or `piece`. |
| `cost_currency` | string           | ISO 4217 currency code.                |

Relation: `booking_links` to `booking_resource_links`.

#### `suites`[​](#suites "Direct link to suites")

Suites are rooms or studios that can be booked. Like resource cost, suite cost is a rate rather than a total.

| Field           | Type             | Description                                                                 |
| --------------- | ---------------- | --------------------------------------------------------------------------- |
| `id`            | UUID             | Stable suite identifier.                                                    |
| `name`          | string           | Suite name; not unique.                                                     |
| `room_number`   | string, nullable | Room number as text; use `isempty()` to cover both `NULL` and empty values. |
| `status`        | enum/integer     | `1` = active, `0` = inactive/out of service.                                |
| `cost`          | number, nullable | Internal cost rate per `cost_unit`.                                         |
| `cost_unit`     | string, nullable | Unit such as `hour` or `day`.                                               |
| `cost_currency` | string           | ISO 4217 currency code.                                                     |

Relation: `booking_links` to `booking_suite_links`.

### Financial and commercial entities[​](#financial-and-commercial-entities "Direct link to Financial and commercial entities")

#### `invoices`[​](#invoices "Direct link to invoices")

Invoices expose integer-cent amounts. Recurring invoice templates are excluded automatically. Revenue means invoiced net amount by `issued_on`; payment receipts and payment-level cash flow are not exposed.

| Field                                               | Type               | Description                                                                                                       |
| --------------------------------------------------- | ------------------ | ----------------------------------------------------------------------------------------------------------------- |
| `id`                                                | UUID               | Stable invoice identifier.                                                                                        |
| `number`                                            | string, nullable   | Invoice number; `NULL` while the invoice is a draft.                                                              |
| `subject`                                           | string             | Invoice subject.                                                                                                  |
| `client_id`, `offer_id`, `order_id`, `issuer_id`    | UUID, nullable     | Related record IDs.                                                                                               |
| `issued_on`, `due_on`, `payment_due_on`, `paid_on`  | date               | Invoice, service/due, payment deadline and payment dates. `due_on`, `payment_due_on` and `paid_on` may be `NULL`. |
| `currency`                                          | string             | ISO 4217 currency code.                                                                                           |
| `total_net_cents`, `total_gross_cents`              | integer            | Net and gross totals in cents.                                                                                    |
| `outstanding_cents`                                 | integer            | Remaining balance in cents. `0` does not necessarily mean paid.                                                   |
| `doc_type`                                          | enum               | `Invoice`, `Cancellation`, `Reminder`, `Partial` or `Final`. Recurring invoices are excluded.                     |
| `locked_at`, `sent_at`, `canceled_at`, `created_at` | datetime, nullable | Finalization, sending, cancellation and creation timestamps in UTC.                                               |

Calculated fields: `is_canceled`, `is_locked`, `is_paid`, `outstanding_percentage`, `overdue` and `status`. `status` is one of `canceled`, `draft`, `paid`, `partially-paid`, `overdue` or `open`. For paid business documents, also exclude cancellation documents; cancellation bookkeeping can set `paid_on`.

Relations: `client`, `offer`, `order`, `issuer`, `bookings` and `booking_links`.

Named filter: `status` accepts `draft`, `locked`, `open`, `overdue`, `paid`, `partially-paid` or `canceled`. The named filter's `locked` category means `locked_at` is set; it is not the same as the displayed `status` value.

#### `offers`[​](#offers "Direct link to offers")

Offers are quotes sent to clients. Amounts are integer cents and must be grouped by currency before they are summed.

| Field                                                  | Type               | Description                                                                       |
| ------------------------------------------------------ | ------------------ | --------------------------------------------------------------------------------- |
| `id`                                                   | UUID               | Stable offer identifier.                                                          |
| `number`                                               | string, nullable   | Offer number; `NULL` while the offer is a draft.                                  |
| `subject`                                              | string             | Offer subject.                                                                    |
| `client_id`, `order_id`, `issuer_id`                   | UUID, nullable     | Related record IDs.                                                               |
| `issued_on`, `valid_until`                             | date               | Offer date and last valid date.                                                   |
| `currency`                                             | string             | ISO 4217 currency code.                                                           |
| `total_net_cents`, `total_gross_cents`                 | integer            | Net and gross totals in cents.                                                    |
| `outcome`                                              | enum, nullable     | `accepted` or `declined`; `NULL` can mean open or draft depending on `locked_at`. |
| `outcome_set_at`, `locked_at`, `sent_at`, `created_at` | datetime, nullable | Outcome, finalization, sending and creation timestamps in UTC.                    |
| `order_number`                                         | string, nullable   | Client purchase-order number, not the same as `order_id`.                         |

Calculated fields: `is_locked`, `is_sent` and `status`. `status` is one of `draft`, `accepted`, `declined` or `open`.

Relations: `client`, `order`, `issuer`, `invoices`, `bookings` and `booking_links`. Named filter `status` accepts `draft`, `locked`, `open`, `accepted` or `declined`.

#### `orders`[​](#orders "Direct link to orders")

Orders group offers, invoices and bookings.

| Field    | Type             | Description                             |
| -------- | ---------------- | --------------------------------------- |
| `id`     | UUID             | Stable order identifier.                |
| `number` | string           | Order number, treated as text.          |
| `name`   | string, nullable | Order name.                             |
| `status` | enum/integer     | `1` = active, `0` = inactive/completed. |

Calculated fields: `is_open` and `status_label` (`active` or `inactive`). Relations: `offers`, `invoices` and `bookings`.

### Link entities[​](#link-entities "Direct link to Link entities")

Link entities represent an individual relationship and are queryable because the relationship itself has useful data. Their rows are visible only when both ends are visible.

#### `booking_staff_links`[​](#booking_staff_links "Direct link to booking_staff_links")

Fields: `id` (integer), `booking_id` (UUID), `staff_id` (UUID), `activity_id` (UUID, nullable), `starts_at` and `ends_at` (datetime, nullable), and `position` (integer, nullable). The assignment window is on this entity, not on `staffs`. If assignment times are `NULL`, use the related booking's times before measuring duration. Use `dcount(booking_id)` when counting bookings per staff member; `count()` counts assignments.

Relations: `booking`, `staff` and `activity`.

#### `booking_resource_links`[​](#booking_resource_links "Direct link to booking_resource_links")

Fields: `id` (integer), `booking_id` (UUID), `resource_id` (UUID), `activity_id` (UUID, nullable), `starts_at` and `ends_at` (datetime, nullable), and `position` (integer, nullable). The assignment window is on this entity, not on `resources`; combine it with the related resource's `cost` and `cost_unit` when analyzing rates.

Relations: `booking`, `resource` and `activity`.

#### `booking_suite_links`[​](#booking_suite_links "Direct link to booking_suite_links")

Fields: `id` (integer), `booking_id` (UUID), `suite_id` (UUID), `activity_id` (UUID, nullable), `starts_at` and `ends_at` (datetime, nullable), and `position` (integer, nullable). The assignment window is on this entity, not on `suites`.

Relations: `booking`, `suite` and `activity`.

#### `client_project_links`[​](#client_project_links "Direct link to client_project_links")

Fields: `id` (integer), `client_id` (UUID) and `project_id` (UUID). Use this entity to bridge clients and projects, especially when rolling project bookings up to clients. Relations: `client` and `project`.

#### `invoice_booking_links`[​](#invoice_booking_links "Direct link to invoice_booking_links")

Fields: `id` (UUID), `invoice_id` (UUID) and `booking_id` (UUID). Use it as a bridge between invoices and bookings. An invoice linked to several bookings appears several times, so do not sum invoice totals at link grain; aggregate invoices at invoice grain first. Relations: `invoice` and `booking`.

#### `offer_booking_links`[​](#offer_booking_links "Direct link to offer_booking_links")

Fields: `id` (UUID), `offer_id` (UUID) and `booking_id` (UUID). Use it as a bridge between offers and bookings. Relations: `offer` and `booking`.

## Examples[​](#examples "Direct link to Examples")

The following examples use the entity and field names from the schema. Choose the visualization after the query returns the desired result.

### Bookings per project[​](#bookings-per-project "Direct link to Bookings per project")

```
projects

| join bookings on id == project_id

| summarize booking_count = count() by id, name

| sort by booking_count desc
```

### Projects with no bookings[​](#projects-with-no-bookings "Direct link to Projects with no bookings")

```
projects

| join kind=leftouter bookings on id == project_id

| summarize booking_count = count() by id, name, number

| where booking_count == 0
```

### Monthly finalized net revenue per currency[​](#monthly-finalized-net-revenue-per-currency "Direct link to Monthly finalized net revenue per currency")

```
invoices

| where isnotnull(locked_at)

| summarize revenue_net_cents = sum(total_net_cents) by month = bin(issued_on, 1mo), currency

| sort by month asc
```

### Overdue invoices[​](#overdue-invoices "Direct link to Overdue invoices")

```
invoices

| where overdue == true

| project id, number, status, payment_due_on, currency, outstanding_cents, outstanding_percentage
```

### Offers per outcome[​](#offers-per-outcome "Direct link to Offers per outcome")

```


| summarize offer_count = count() by outcome
```

### Staff assignment count[​](#staff-assignment-count "Direct link to Staff assignment count")

```
booking_staff_links

| summarize assignment_count = count(), booking_count = dcount(booking_id) by staff_id

| sort by booking_count desc
```

### Resource rates by unit and currency[​](#resource-rates-by-unit-and-currency "Direct link to Resource rates by unit and currency")

```
resources

| where status == 1

| summarize resource_count = count(), rate_total = sum(cost), rate_average = avg(cost) by cost_unit, cost_currency
```

### Cross-entity calculations[​](#cross-entity-calculations "Direct link to Cross-entity calculations")

For multi-step calculations, use a named intermediate query (`let`) and join its result back to the entity you want to display. For example, calculate resource rates per booking first, then roll them up by project:

```
let resource_rates = booking_resource_links

| join kind=leftouter resources on resource_id == id

| summarize booking_rate = sum(cost) by booking_id;

bookings

| join kind=leftouter resource_rates on id == booking_id

| summarize project_rate = sum(booking_rate) by project_id

| join kind=leftouter projects on project_id == id

| project project_id, name, project_rate
```

The exact query editor representation may expose these operations as a structured pipeline. The entity names, field names, relation names and semantics remain those documented here.

## Widget-friendly results[​](#widget-friendly-results "Direct link to Widget-friendly results")

| Visualization    | Recommended result                                                              |
| ---------------- | ------------------------------------------------------------------------------- |
| **Table**        | Detail rows or several columns, such as bookings with titles and dates.         |
| **Number / KPI** | One row with one aggregated value, such as `count()` or `sum(total_net_cents)`. |
| **Bar chart**    | A category and a measure, such as `status` and `count()`.                       |
| **Line chart**   | A date bucket and a measure, such as `bin(issued_on, 1mo)` and revenue.         |
| **Donut chart**  | A small set of categories and their counts or totals.                           |

## From query result to widget[​](#from-query-result-to-widget "Direct link to From query result to widget")

Once your query returns a result, choose a visualization type for the widget:

| Visualization    | Best suited for                                                            |
| ---------------- | -------------------------------------------------------------------------- |
| **Table**        | Detailed, row-level results                                                |
| **Number / KPI** | A single aggregated value, e.g. `count()` or `sum()`                       |
| **Bar chart**    | Comparing values across categories                                         |
| **Line chart**   | Trends over time, typically combined with `summarize ... by bin(Date, 1d)` |
| **Donut chart**  | Showing proportions between a small number of categories                   |

## Example queries[​](#example-queries "Direct link to Example queries")

**Count of bookings per client in the last 30 days, as a bar chart:**

```
Bookings

| where StartDate >= ago(30d)

| summarize BookingCount = count() by Client

| order by BookingCount desc
```

**Outstanding invoice total, as a Number/KPI widget:**

```
Invoices

| where Status == "open"

| summarize TotalOpen = sum(Amount)
```

**Accepted quotes per month, as a line chart:**

```
Offers

| where Status == "accepted"

| summarize AcceptedCount = count() by bin(CreatedDate, 30d)

| order by CreatedDate asc
```