How Analytics works

Analytics runs read-only SQL over the tables in one profile. It is intended for totals, grouped reports, trends, comparisons, exports, and other questions that need several rows or tables at once.

Unlike Search and Search2, Analytics does not return business rows through a fixed filter model. The caller writes one SELECT query against a catalog assembled from the profile’s current table definitions.

What Analytics reads

Analytics reads live PostgreSQL table data through a DataFusion query engine. It does not use a separate search index or analytical copy, so committed inserts and updates do not wait for an indexing process.

Every exposed table contains:

• id;
• the table’s user columns under their current public names;
• created_at.

The deleted and row_revision columns are not exposed. Rows whose deletion flag is set are filtered out before the caller’s SQL runs, including inside joins and aggregates.

Physical ordinal column names are hidden. If a table column is publicly named invoice_total, analytical SQL uses invoice_total, not the database column that stores it.

Discover the catalog first

The catalog is the authoritative description of what a caller can query. For each visible table it provides:

• the public table name and table-definition ID;
• public columns, their field types, and whether they are system columns;
• money currency and rounding metadata where applicable;
• link columns and the table each link targets.

Clients should build table pickers, column pickers, joins, and query validation from this catalog instead of assuming a profile has a fixed schema. Table definitions can differ between profiles and can evolve over time.

The catalog contains current, non-deleted table definitions, including managed tables when the caller may read them. An unknown profile is reported as missing. A profile with no readable tables returns an empty catalog.

Permissions define the SQL namespace

Analytics has no separate data permission. It reuses table read grants.

Before a query is planned, the server constructs a catalog containing only tables the caller may read. An unreadable table is absent from the SQL namespace entirely, so it cannot be accessed through a direct selection, join, subquery, or aggregate.

If the caller cannot read any table in the profile, catalog discovery succeeds with an empty list, but query execution is denied.

Table read grants are data-plane grants on the table’s permission object. See Roles and permissions for how grants work.

Do not cache one user's catalog for another

Two roles can receive different catalogs for the same profile. Cache catalog data only with the profile, role, and permission lifecycle in mind.

Links describe possible joins

A link entry says that a public source column holds the ID of a row in another table. Analytics does not join linked tables automatically; the query chooses the join type and columns.

The analytical view is columnar rather than hydrated row data. A link remains an ID, and presentation-only expansion of related records is not performed. Join the linked table when the report needs its fields, and use only columns actually listed in the catalog.

For example, if invoice.customer_id links to customer, the ordinary join is:

SELECT
  invoice.number,
  customer.name AS customer_name
FROM invoice
JOIN customer
  ON customer.id = invoice.customer_id

Use a left join when rows without a linked record should remain in the report:

SELECT
  invoice.number,
  customer.name AS customer_name
FROM invoice
LEFT JOIN customer
  ON customer.id = invoice.customer_id

The catalog currently reports link metadata as optional for analytical purposes. Whether application writes require a link value is enforced by table-data validation, not by the Analytics link description.

Good uses for Analytics

• sum sales or costs by customer, product, account, or period;
• count records by status;
• combine header and line tables;
• calculate averages, minima, maxima, and ratios;
• create chart-ready time series;
• produce bounded tabular exports.

Analytics is not for modifying rows, opening an editable row picker, fuzzy discovery, or replacing accounting approval and balance workflows.

Continue with Writing analytical queries for selection, filtering, joins, grouping, null handling, and worked examples. See Results and limits before building exports or long-running reports.