Analytics

  Table of Contents

• Asking in English
  • Choosing a key and model
  • LLM keys
• Views
• Limits and results
• API
• Deployment

Analytics runs read-only PostgreSQL queries over a stable set of views. Open Data → Analytics, enter SQL, and select Run. Results appear in a table. Download CSV downloads those same rows without running the query again.

The Analytics permission grants access to the entire exposed analytics dataset. Administrators receive it automatically; other users and organization API keys need it explicitly. Queries cannot change data. Pages use the viewer's Analytics permission when calling the API.

Asking in English

Type a question above the SQL, such as "How many work orders were completed each week this quarter?", and select Ask. An LLM writes a query that answers it and puts the query in the SQL box. Nothing runs until you read the query and select Run. The query has the same limits as one you write yourself.

Asking uses an API key brought from Anthropic, OpenAI, or Gemini. Factorial sends the provider the list of views and columns under Schema, plus the question. It never sends rows or results, and no Factorial feature sends data to an LLM on its own. The provider bills the key for each question.

Choosing a key and model

Ask with, beside the question, lists every key and model the question may use: your own keys first, then the organization's. A key the question can't use stays listed but disabled, with the reason, such as your own keys while the organization allows only its keys. The first of your keys is chosen by default, with its provider's balanced model. The choice stays selected for the next question. A failed question says why, including when a provider refuses the key or doesn't offer the model to it.

Each provider offers three models, from fastest and cheapest to most capable:

Provider	Fastest, cheapest	Balanced (default)	Most capable
Anthropic	Claude Haiku 5.5	Claude Sonnet 5.5	Claude Opus 5.5
OpenAI	GPT-6 Luna	GPT-6.1 Sol	GPT-6 Astra
Gemini	Gemini 3.5 Flash-Lite	Gemini 3.8 Flash	Gemini 4 Argon

Writing one query rarely needs the most capable model. Start with the balanced model, and switch when a query comes back wrong.

LLM keys

A key belongs to one person or to the organization.

• Your own keys are saved under LLM keys on the User API keys page. They may ask any of their provider's models. Nobody else can see or use them.
• Organization keys are saved by administrators in organization settings. Anyone with the Analytics permission can ask with them, using any of their provider's models, and the organization is billed.

To save a key, give it a name, choose a provider, paste the key, and select Save key. Without a name, the key is named after its provider. Each key needs its own name, so a second key from the same provider needs one. Factorial doesn't try the key when it is saved: if the provider refuses it, the first question says why.

To rename a key or paste a new one, select Edit under it. Leave the key blank to keep the saved one. A key's provider can't change: save a new key for another provider. You can always remove your own keys, even after losing the Analytics permission.

Keys are encrypted before they are stored, and Factorial never shows them again. Pages show only each key's name, provider, and last four characters.

An organization can turn LLM features off, or allow only its own keys. See LLM features.

Views

Expand Schema below the query input to explore view and column names, PostgreSQL types, descriptions, and join hints. These views show current state; they do not reconstruct historical inventory or execution state.

View	One row represents	Context
analytics.inventory	Inventory line	Part revision, base unit, quantity, trace number, status
analytics.work_orders	Work order	Procedure revision, output part, owner, schedule, parent
analytics.tasks	Instantiated execution task	Effective step title, parent work order or project, assignee, queue
analytics.parts	Part revision	Part number, revision, description, preferred and base units

Views include completed and canceled work, and archived, consumed, or scrapped inventory. Filter explicitly for the state needed. work_orders excludes projects. tasks.kind distinguishes PROJECT from WORK_ORDER according to the task's immediate parent. A task's title reflects any applied approved redline.

Part revisions have distinct IDs. Join part_id to analytics.parts.id. For tasks with kind = 'WORK_ORDER', join parent_id to analytics.work_orders.id. Inventory quantities use base_uom, which may differ from the part's preferred display unit. Do not add quantities with different units.

select part_number, part_revision, base_uom, sum(quantity) as available_quantity
from analytics.inventory
where status = 'available'
group by part_number, part_revision, base_uom
order by part_number, part_revision;

View column names, types, units, and row meanings form the public contract. Internal schema changes are handled by updating the views. Breaking changes require a new view; compatible columns may be added. Select named columns in long-lived integrations.

Limits and results

Each query has a 10-second deadline, including waiting for an analytics connection. Results contain at most 10,000 rows, 128 columns, and 8 MiB of cell data. Queries may contain at most 64 KiB of SQL. A result stopped by the row or byte limit is marked truncated; its download includes only the returned rows. Narrow the query or query a smaller range to retrieve additional data. Use ORDER BY when row order matters.

A single SELECT, VALUES, or read-only WITH query is supported. Multiple statements, writes, transaction commands, and COPY are rejected. SQL errors appear above the results. Data refreshes only when a query runs.

CSV uses a header row, commas, quoted non-null fields, and CRLF line endings. An unquoted empty field represents SQL null; "" represents an empty string. Embedded quotes and newlines are preserved. CSV contains the query's values unchanged; import text columns as text in spreadsheet software.

API

POST /api/v1/analytics/query accepts JSON:

{
  "sql": "select part_number, revision from analytics.parts where part_number = $1 order by revision",
  "parameters": ["BRKT-100"],
  "format": "json"
}

Parameters are PostgreSQL text representations or null, with at most 100 parameters. Use explicit casts when PostgreSQL cannot infer a parameter's type, such as $1::uuid. Bind values through parameters rather than interpolating them into SQL. The entire request may be at most 1 MiB.

JSON results include columns, rows, row_count, truncated, and elapsed_ms. Rows are arrays in column order, so duplicate column names remain distinct. Every non-null cell is a string in PostgreSQL text format; null remains JSON null. This preserves exact decimals and large integers. Timestamps with time zones use UTC. Column metadata supplies PostgreSQL type names; unknown result types use oid:<number>.

Set format to csv, or send Accept: text/csv with no explicit format, for a CSV response. Both formats include X-Analytics-Truncated, X-Analytics-Row-Count, and X-Analytics-Elapsed-Ms headers. Check truncation before treating a response as a complete export. Failures return a non-success status with a JSON error message, including when CSV was requested.

GET /api/v1/analytics/schema returns a views array with each view's name, description, and ordered columns. Column descriptions include units, status meanings, and join hints where applicable. This endpoint powers the UI's Schema section.

Both endpoints accept normal API authentication. A custom Page can query using its signed-in viewer:

const response = await fetch('/api/v1/analytics/query', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    sql: 'select status, count(*) as count from analytics.tasks group by status order by status'
  })
});
const result = await response.json();
if (!response.ok) throw new Error(result.error);
// result.columns describes each position in result.rows.

Deployment

Analytics uses the regular application database. Normal database migrations create a read-only PostgreSQL login, grant access to the analytics views, and generate its credential. No separate setup command or analytics connection URL is required.

At startup, Factorial reads the credential from a private table in the application schema and connects using the regular database's host, database name, and transport settings. The analytics pool is limited to two connections. Submitted SQL runs under the restricted login, which cannot read the credential table or operational tables.

Migrations require permission to create PostgreSQL roles and manage their grants. Each database receives its own analytics login and credential. Include the private credential table and PostgreSQL roles when backing up or restoring a deployment.

The server validates the analytics login, rejecting elevated roles, role memberships, access to operational schemas, write privileges, and executable functions outside PostgreSQL's system schemas. Each query runs in a read-only transaction, and its connection is closed afterward to discard session changes and locks. Default grants cover future analytics views created by the same migration owner; a different migration owner must arrange grants explicitly.

Asking in English needs SECRET_ENCRYPTION_KEY, a base64-encoded 32-byte key that encrypts saved LLM keys. Create one with openssl rand -base64 32. Without it, keys can't be saved and Ask is disabled. The server must also be able to reach the providers' APIs. Changing or losing the encryption key makes every saved LLM key unreadable, and people must save their keys again.

Saved queries, dashboards, and scheduled query alerts are not included in this release.