Ignition Database Table Reference

Ignition has a lot of systems built-in that will query the database automatically without requiring you to build a query. These systems automatically create the necessary tables in the database, insert the relevant data, and even query it back out for you. However, because this data is all stored in simple database tables, it can be manually accessed using queries to customize how you see the data.

caution

These tables are configured in very specific ways. Altering them may cause unforeseen issues, and is not recommended.

While it can be useful to manually query out data from these tables, we recommend taking a backup of the database tables before making changes, with the understanding that altering the data or tables is done at your own risk.

Tag History

The Tag History system utilizes at least six different tables in the database:

Table Name	Table Description	Column References
sqlt_data_X_X	This table stores the raw tag data. There will be multiple tables that fit this format depending on the name of the Gateway and the date. (For example, a table named "sqlt_data_1_2018_01" would store data from the driver with an id of 1, for the year 2018, for the month of January)	sqlt_data_x_x_x.tagid = sqlth_te.id
sqlth_1_data	This table stores raw tag data, and is only used when the provider is configured to use a single partition (The provider's "Enable Partitioning" setting is unchecked).	sqlt_1_data.tagid = sqlth_te.id
sqlth_te	This table stores the non-data details of each tag.	sqlth_te.scid = sqlth_scinfo.id
sqlth_scinfo	This table stores tag group information.	sqlth_scinfo.drvid = sqlth_drv.id
sqlth_sce	This table stores start and end times for tag groups.	sqlth_sce.scid = sqlth_scinfo.id
sqlth_partitions	This table stores start and end times for each sqlt_data table.	sqlth_partitions.drvid = sqlth_drv.id
sqlth_drv	This table stores information about the drivers of the historical data.	none
sqlth_annotations	This table stores annotations for the Tag History system, such as those created by the Power Chart.	none

sqlt_data_X_X

This is the central table that stores the core tag values. The system stores data in tables based on the history provider's partition length and units. For example, a monthly partition would use a table named like sqlt_data_{driverId}_{year}_{month}, whereas a daily partition would use sqlt_data_{driverId}_{yearMonthDay}. The duration of each partition is also tracked on the sqlth_partitions table.

When pre-processed partitions are enabled, an additional sql_data table will be created for each partition. The system tracks which partitions are pre-processed by the "blocksize" column on the sqlth_partitions table.

Column Name	Data Type	Notes
tagid	int	Unique id of the tag. References the sqlth_te table.
intvalue	int	Holds the value of the tag if it is data type 0, NULL otherwise.
floatvalue	double	Holds the value of the tag if it is data type 1, NULL otherwise.
stringvalue	string	Holds the value of the tag if it is data type 2, NULL otherwise.
datevalue	date	Holds the value of the tag if it is data type 3, NULL otherwise.
dataintegrity	int	Quality of the tag for this timestamp. 192 is Good Quality, anything else is bad. See Tag Quality Overlays.
t_stamp	long	Unix Timestamp (milliseconds since epoch) for this value.
vtype	int	Represents metadata about the record, used for pre-processed partitions. For details, see Pre-Processed Partitions.

note

Default indexing includes:

• tagid
• t_stamp

sqlth_1_data

This is the data table for historian providers that have disabled multiple partitions (by unchecking the "Enable Partitions" setting).

Column Name	Data Type	Notes
tagid	int	Unique id of the tag. References the sqlth_te table.
intvalue	int	Holds the value of the tag if it is data type 0, NULL otherwise.
floatvalue	double	Holds the value of the tag if it is data type 1, NULL otherwise.
stringvalue	string	Holds the value of the tag if it is data type 2, NULL otherwise.
datevalue	date	Holds the value of the tag if it is data type 3, NULL otherwise.
dataintegrity	int	Quality of the tag for this timestamp. 192 is Good Quality, anything else is bad. See Tag Quality Overlays.
t_stamp	long	Unix Timestamp (milliseconds since epoch) for this value.
vtype	int	Represents metadata about the record, used for pre-processed partitions.

note

Default indexing includes:

• tagid
• t_stamp

sqlth_te

This table stores metadata for each historical tag. A row in this table represents a unique version of a tag's historical configuration. When a tag is modified in certain ways (renamed, data type changed, etc.), a new row is inserted and the previous entry is "retired."

Column Name	Data Type	Notes
id	int	Unique ID of the tag. Primary key for this table.
tagpath	string	Full path of the tag within the Tag Provider, e.g., Folder1/tag1.
scid	int	Scan class (Tag Group) ID. References the sqlth_scinfo table, indicating the scan class this tag uses.
datatype	int	Determines which column in the partition tables stores the tag’s historical value. 0: Stored in intvalue (used for integers and booleans) 1: Stored in floatvalue (used for floats, doubles, longs, and date/time values) 2: Stored in stringvalue (used for strings)
querymode	int	Reflects the Deadband Style setting selected when tag history was enabled. 0: Discrete, which stores values when the difference from the last stored value exceeds the deadband. 3: Analog, which uses compression algorithms to evaluate when to store values.
created	long	Unix timestamp (milliseconds since epoch) for when this tag entry was created.
retired	long	Unix timestamp (milliseconds since epoch) for when this tag entry was retired. NULL means the entry is currently active. A tag is marked as retired when any of the following occur: The tag is deleted The tag is renamed The tag’s scan class (Tag Group) is changed The tag’s data type is changed If a tag is simply disabled and then deleted, this value will remain NULL.

note

Default indexing includes:

• id
• scid

sqlth_scinfo

This table stores information about Tag Groups, including their execution rates and associated drivers.

Column Name	Data Type	Notes
id	int	Unique id of the Tag Group.
scname	string	Name of the Tag Group. A value of "exempt" is used when the execution rate is not recorded.
drvid	int	The driver this Tag Group uses. References the sqlth_drv table.

note

Default indexing includes:

• id
• drvid

sqlth_sce

This table stores the start and end times for Tag Group execution periods. Each entry tracks execution periods for a specific Tag Group.

Column Name	Data Type	Notes
scid	int	References the sqlth_scinfo table.
start_time	long	Unix Timestamp (milliseconds since epoch) for the first execution of this Tag Group.
end_time	long	Unix Timestamp (milliseconds since epoch) for the latest execution of this Tag Group.
rate	int	The execution rate (in milliseconds) of the Tag Group.

note

Default indexing includes:

• scid
• start_time

sqlth_partitions

This table defines the partitions used by the history system to segment data across multiple tables. Partitioning allows efficient storage and retrieval of data.

Column Name	Data Type	Notes
pname	string	The name of the table that contains this partition's data.
drvid	int	The id of the driver that owns this data table. References the sqlth_drv table.
start_time	long	Unix Timestamp (milliseconds since epoch) for the earliest time covered by this partition.
end_time	long	Unix Timestamp (milliseconds since epoch) for the latest time covered by this partition.
blocksize	int	The size (in milliseconds) of time covered by each entry, used for pre-processed partitions.
flags	int	Additional flags affecting partition usage. For example, a flag of 1 disables seed queries.

note

Default indexing includes:

• pname
• start_time

sqlth_drv

This table stores information about the drivers used in the history system. Each driver corresponds to a source of historical data.

Column Name	Data Type	Notes
id	int	Unique id of the driver.
name	string	Name of the driver. Typically, this is the system name of the Ignition Gateway storing records.
provider	string	The name of the Tag provider associated with the driver.

note

Default indexing includes:

• id

sqlth_annotations

This table stores annotations related to Tag History. These are often user-created notes associated with specific data points.

Column Name	Data Type	Notes
id	int	Unique id of the annotation.
tagid	int	The tag id the annotation pertains to. References the sqlth_te table.
start_time	long	Unix Timestamp (milliseconds since epoch) for the start time of the annotation.
end_time	long	Unix Timestamp (milliseconds since epoch) for the end time of the annotation.
type	string	The type of annotation. Currently, the only defined type is "note."
datavalue	string	The content of the annotation, when type is "note."
annotationid	string	A UUID used to track the annotation across multiple Gateways.

note

Default indexing includes:

• tagid
• start_time

Tag History - Internal History Provider

Internal History Providers use a different table scheme compared to external providers. These tables are stored in an IDB file located at IgnitionInstallationDirectory/data/local/tag-historian.

Note that these tables exclusively live in a SQLite database, so a database viewer is required to access them.

Table Name	Table Description
annotations	This table keeps track of annotations for specific tags.
schema_info	Provides information on when the internal provider was created. Usually contains only one row.
tagdata	Stores the actual historical records, along with a sync id for synchronizing data between Gateways.
tagdetails	Provides information about tags storing data. Does not track scan class/tag group information.
tagproperties	Tracks properties of tags, such as datatype and interpolation mode.
tags	Associates each tag’s id with a path for query purposes.

annotations

Column Name	Data Type	Notes
id	int	Unique id of the annotation.
tagid	int	The tag id the annotation pertains to. Maps to the id column on the tags table.
type	text	Type of the annotation.
rangestart	int	Timestamp representing the start of the annotation.
rangeend	int	Timestamp representing the end of the annotation.
data	text	Data associated with the annotation, such as user-entered content.
syncid	int	Sync id for synchronizing annotations with other Gateways.
annotationid	int	A UUID used to uniquely identify the annotation.
deleted	boolean	Indicates whether the annotation is marked as deleted.

note

Default indexing includes:

• tagid
• rangestart

schema_info

Column Name	Data Type	Notes
version	int	Version of the schema. Typically set to "4."
created	int	Unix Timestamp (milliseconds since epoch) for when the schema was created.

note

Default indexing includes:

• version

tagdata

Column Name	Data Type	Notes
tagid	int	References the tag id on the tags table.
numvalue	numeric	Numeric value of the tag, if applicable.
strvalue	text	String value of the tag, if applicable.
quality	int	Quality code for the value.
t_stamp	int	Unix Timestamp (milliseconds since epoch) for the stored value.
syncid	int	Sync id for synchronizing records across Gateways.

note

Default indexing includes:

• tagid
• t_stamp

tagdetails

Column Name	Data Type	Notes
id	int	Unique id of the detail.
tagid	int	References the tag id on the tags table.
created	int	Unix Timestamp (milliseconds since epoch) for when this detail entry was created.
retired	int	Unix Timestamp (milliseconds since epoch) for when this detail entry was retired.
datatype	int	The datatype of the detail.
ttl	int	Time-to-live.
syncid	int	Sync id for synchronizing records across Gateways.

note

Default indexing includes:

• tagid
• created

tagproperties

Column Name	Data Type	Notes
tagid	int	References the tag id on the tags table.
name	text	Name of the property.
value	text	Value of the property.
datatype	int	The datatype of the property.

note

Default indexing includes:

• tagid
• name

tags

Column Name	Data Type	Notes
id	int	Unique identifier for the tag.
tagpath	text	Full path of the tag, including the tag provider.

note

Default indexing includes:

• id
• tagpath

Alarm Journal

The Alarm Journal system utilizes two different tables in the database:

Table Name	Table Description
alarm_events	This table stores every event (active, cleared, acknowledged) that happened to any alarms that fit within the Journal filter parameters. Each row is a new event	alarm_events.id = alarm_event_data.id
alarm_events_data	This table stores unique information pertaining to each event. Each row is a specific property of a specific event, so alarm events with multiple properties will have multiple rows in the table.	none

The names of the tables are completely configurable in the Journal settings in the Gateway. The default table names are used in the table. See the Journal Properties and Tables page for more information regarding all of the columns in the tables.

alarm_events

This table captures the main record of each alarm event that occurred. One row is created for each change in state (active, cleared, acknowledged).

Column Name	Data Type	Notes
id	int	Unique ID for this event. Primary key.
eventid	int	Identifier that groups related event records together. All active, cleared, and acknowledged states for a single alarm occurrence share the same eventid.
source	string	The source path of the alarm, such as a tag path or expression item.
displayPath	string	Human-readable display path of the alarm (can be overridden in alarm configuration).
priority	int	Alarm priority level: 0 = Diagnostic 1 = Low 2 = Medium 3 = High 4 = Critical
eventtype	int	Alarm state change: 0 = Active 1 = Cleared 2 = Acknowledged
eventflags	int	Bitmask representing metadata about the event (e.g., whether it was system-generated).
eventtime	datetime	Timestamp for when the event occurred.

note

Default indexing includes:

• id

alarm_events_data

This table stores event-specific properties, such as custom alarm properties or tag values at the time of the event. Each row represents one property for one event.

Column Name	Data Type	Notes
id	int	References the id from the alarm_events table.
propname	string	Name of the property (e.g., "AckUser", "Notes", or "IsShelved").
dtype	int	Represents the type of value stored in this row: 0 = Integer 1 = Float 2 = String
intvalue	int	Value of the property if dtype = 0. Otherwise NULL.
floatvalue	float	Value of the property if dtype = 1. Otherwise NULL.
strvalue	string	Value of the property if dtype = 2. Otherwise NULL.

note

Default indexing includes:

• id

Authentication

The Database Authentication system utilizes six different tables in the database:

Table Name	Table Description	Column References
scada_users	This table stores each user contained within the user source, along with basic user information. Each row is a new user.	none
scada_roles	This table stores all of the possible roles within the user source. Each row is a new role.	none
scada_user_rl	This table stores a mapping of users to roles. Each row is a user and a paired role, so users with multiple roles will have multiple rows in the table.	scada_users_rl.user_id = scada_users.id<br/>scada_users_rl.role_id = scada_roles.id
scada_user_sa	This table stores a list of all upcoming schedule adjustments for each user. Each row is a new schedule adjustment, so users with multiple schedule adjustments will have multiple rows in the table.	scada_user_sa.user_id = scada_users.id
scada_user_ci	This table stores a list of all contact information items for each user. Each row is a new contact information item, so users with multiple contact information items will have multiple rows in the table.	scada_user_ci.user_id = scada_users.id
scada_user_ex	This table stores a list of all extra properties for each user, with properties and values stored 1 for 1. Each row is a new property and value pair, so users with multiple extra properties will have multiple rows in the table. Extra properties are added for modules that want to associate data with a user, such as the Voice Notification Module, which adds a Security PIN setting.	scada_user_ex.user_id = scada_users.id

The prefix of the tables are configurable in the User Source settings in the Gateway. The default prefix of "scada_" is used in the table.

scada_users

This is the core user table. It contains authentication credentials and general profile information for each user.

Column Name	Data Type	Notes
id	int	Unique ID for the user.
username	string	The user's login name.
password	string	Encrypted password hash.
firstname	string	User’s first name.
lastname	string	User’s last name.
schedule	string	Assigned schedule name (for On-Call Roster or Scheduled Roster logic).
notes	string	Optional user notes.

note

Default indexing includes:

• id

scada_roles

Stores all defined roles that can be assigned to users.

Column Name	Data Type	Notes
id	int	Unique ID for the role.
name	string	Name of the role (e.g., "Administrator", "Operator").

note

Default indexing includes:

• id

scada_user_rl

Links users to their assigned roles. One row per user-role pair.

Column Name	Data Type	Notes
user_id	int	References scada_users.id.
role_id	int	References scada_roles.id.

note

Default indexing includes:

• user_id
• role_id

scada_user_sa

Stores schedule overrides or adjustments for individual users.

Column Name	Data Type	Notes
id	int	Unique ID for the schedule adjustment.
user_id	int	References scada_users.id.
starttime	datetime	When the schedule adjustment starts.
endtime	datetime	When the schedule adjustment ends.
type	string	Type of adjustment (e.g., "Available", "Unavailable").

note

Default indexing includes:

• user_id

scada_user_ci

Stores pieces of contact info for users, such as emails or phone numbers.

Column Name	Data Type	Notes
id	int	Unique ID for the contact info entry.
user_id	int	References scada_users.id.
type	string	Type of contact (e.g., "Email", "Phone").
value	string	Contact information value.

note

Default indexing includes:

• user_id

scada_user_ex

Stores custom or extra properties for a user. Often used by modules (e.g., Voice Notification) to store settings like PIN codes.

Column Name	Data Type	Notes
id	int	Unique ID for the property.
user_id	int	References scada_users.id.
name	string	Name of the property (e.g., "VoicePIN").
value	string	Value of the property.

note

Default indexing includes:

• user_id

Audit Log

The Audit system utilizes one table in the database:

Table Name	Table Description	Column Reference
AUDIT_EVENTS	This table stores each auditable event (save, publish, edits, etc.) that has happened for each project or system that has auditing enabled. Each row is a new event.	none

The names of the tables are completely configurable in the Audit settings in the Gateway. The default table names are used in the table.

AUDIT_EVENTS

Column Name	Data Type	Notes
AUDIT_EVENTS_ID	int	Unique identifier for the event. Primary key.
EVENT_TIMESTAMP	datetime	Timestamp for when the action occurred.
ACTOR	string	The user or system that performed the action.
ACTOR_HOST	string	Hostname or IP of the system where the action originated.
ACTION	string	Brief description of the action (e.g., "Tag Write", "Script Run").
ACTION_TARGET	string	The target affected by the action (e.g., tag path, script name, user).
ACTION_VALUE	string	The value involved in the action (e.g., written value, changed property).
STATUS_CODE	int	A 32-bit integer bitmask representing the result of the action (e.g., good, bad, uncertain). Refer to AuditStatus.SubCode for decoding specific status codes.
ORIGINATING_SYSTEM	string	Name of the project or system that performed the action.
ORIGINATING_CONTEXT	int	Numeric bitmask representing the context of origin: 1 = Gateway 2 = Designer 4 = Client

note

Default indexing includes:

• audit_events_id
• event_timestamp