No history yet

Internal Database Schemas

The Core Architecture

When you design a process in Nutrient, you're creating a blueprint. When a user kicks one off, that blueprint becomes a running instance. The platform stores the blueprint and the instance data in different places within its SQL Server database. Understanding this separation is key to querying the system effectively.

Three tables form the heart of Nutrient's runtime data structure:

  • REQUESTS: This is the master table for every process instance. Each row represents a single, unique workflow that has been started. It contains metadata like the process name, start date, current status, and the user who initiated it.
  • TASKS: This table holds the individual steps or tasks within each request. A single request in the REQUESTS table can have many associated rows in the TASKS table, representing every approval, form, or notification in the workflow.
  • INSTANCE_DATA: This is where the actual data entered into your forms lives. Rather than creating a new table for every form, Nutrient often uses a single, wide table to store form field data, linking it back to a specific request.

The blueprint itself, which includes the process flow design, form layouts, and configuration rules, is stored separately. A key table for this is FLOW_NODES, which defines the structure of each process—what the steps are and how they connect. This separation means you can update a process design without altering the data from past, completed requests that used an older version.

How Form Fields Become Columns

A common challenge is figuring out where the data from a specific form field is stored. Nutrient manages this through a concept called a 'Data Profile'. When you create a form field, you're also defining a mapping in the Data Profile that tells the system which column in the table should hold that field's value.

This mapping is crucial for reporting. Because a process can change over time, a field named 'Employee ID' might exist in version 1 and version 2, but be removed in version 3. The Data Profile ensures that when you query for 'Employee ID', you can pull data from all relevant versions, even if they used slightly different forms. The system knows, via this mapping, that the 'Employee ID' concept corresponds to a specific column, regardless of the process version.

Essentially, the Data Profile acts as a translation layer between the user-facing form and the physical database schema.

This means that to get a complete picture for reporting, you can't just query one table. You typically need to join REQUESTS with INSTANCE_DATA to link process metadata (like the start date) with the actual user-submitted data.

-- Retrieve the 'Project Name' and 'Budget'
-- for a specific request ID.

SELECT
  r.REQUEST_ID,
  r.PROCESS_NAME,
  r.REQUEST_STATUS,
  id.PROJECT_NAME_COL, -- Mapped via Data Profile
  id.BUDGET_COL       -- Mapped via Data Profile
FROM
  REQUESTS r
JOIN
  INSTANCE_DATA id ON r.REQUEST_ID = id.REQUEST_ID
WHERE
  r.REQUEST_ID = 12345;

Linking Requests to Tasks

A single request flows through multiple steps, and each of those steps is a task. The REQUESTS table and TASKS table are linked by a REQUEST_ID column. The REQUEST_ID in the REQUESTS table is one of the that uniquely identifies the entire workflow instance.

This same REQUEST_ID appears in the TASKS table as a foreign key, creating a one-to-many relationship. One request can have dozens of associated tasks. This structure lets you answer questions like, "Show me all tasks currently assigned to the finance department for the Q3 budget approval process."

Finally, consider how the system handles data for performance. Nutrient partitions its data, often separating active, in-flight requests from archived or completed ones. Completed request data might be moved to different tables or even a separate database optimized for reporting rather than transactions. When writing direct queries, always check if you're querying against the transactional tables for live processes or the historical archive, as this will affect both the data you see and the performance of your query.

Let's test your understanding of how this all fits together.

Quiz Questions 1/5

What is the primary purpose of the REQUESTS table in the Nutrient platform?

Quiz Questions 2/5

To create a report showing the start date of a process and the value of a specific form field, which two tables would you most likely need to join?

By understanding the relationships between these core tables and the logic of the Data Profile, you can build powerful, efficient reports that pull exactly the information you need from the Nutrient platform.