# Database Tables

> The schemas and tables Collection stores Devices, Observations, Assets, and Connection Status in.

URL: https://www.trakhound.com/docs/mtconnect-server/tables

Tables Profiles store the data of their Devices in SQLite, PostgreSQL, or SQL Server. The tables are created automatically, organized in schemas, so reporting and analytics tools can query them directly. In SQLite, which has no schemas, each schema is its own database file next to the database.

## Schemas

| Schema | Contents |
| --- | --- |
| `mtconnect_devices` | The Device models: Devices, Components, DataItems, and their Compositions, Descriptions, Configurations (ex. Coordinate Systems, Motion, Sensor Configuration, Specifications), Definitions, Constraints, and Relationships |
| `mtconnect_observations` | Observations by type, plus the latest value of each DataItem in the `current_*` tables |
| `mtconnect_assets` | MTConnect Assets |
| `mtconnect_connections` | Each Device's connection `status` and `status_history` |
| `mtconnect_statistics` | Observation statistics by Device and DataItem, and Data Retention statistics |
| `mtconnect_definitions` | The MTConnect Standard's Component, Composition, and DataItem Types, with DataItem SubTypes and Values, to describe the stored data |

## Observations

Each Observation is stored in the table for its DataItem's Category and Representation. Observations are written in batches, and the `current_*` tables are kept up to date, so the latest value of every DataItem can be read with a single query.

| History Table | Current Table | DataItems |
| --- | --- | --- |
| `sample_value` | `current_sample_value` | Numeric SAMPLEs (ex. positions, temperatures, loads) |
| `sample_time_series` | `current_sample_time_series` | SAMPLEs with the `TIME_SERIES` Representation |
| `event_value` | `current_event_value` | EVENTs (ex. Execution, Program, Part Count) |
| `condition` | `current_condition` | CONDITIONs (alarms), with their `state` (level), native code, native severity, qualifier, and message (`result`) |

Every Observation has its Device UUID, DataItem ID, timestamp, and the Agent's instance ID and sequence. [Data Retention](https://www.trakhound.com/docs/mtconnect-server/data-retention) deletes old rows from the history tables, and never from the `current_*` tables.

## Assets

Tables Profiles store each Device's MTConnect Assets (ex. Cutting Tools, Files, Raw Materials) unless **Store Assets** is turned off for the Profile. The `mtconnect_assets.asset` table keeps the latest version of each Asset (by Device UUID and Asset ID), with its Type, Timestamp, Removed flag, and the full Asset as MTConnect XML, so every Asset Type is supported.

## Example Queries

Columns are named in snake case (ex. `device_uuid`, `data_item_id`). The latest Execution state of every Device, found by joining the DataItems of the Device models:

```sql
select o.device_uuid, o.data_item_id, o.result, o.timestamp
from mtconnect_observations.current_event_value o
join mtconnect_devices.data_item d on d.device_uuid = o.device_uuid and d.id = o.data_item_id
where d.type = 'EXECUTION';
```

The active alarms (Conditions whose `state` is `WARNING` or `FAULT`):

```sql
select device_uuid, data_item_id, state, native_code, result, timestamp
from mtconnect_observations.current_condition
where state in ('WARNING', 'FAULT');
```

> To read the data from an application instead of a database connection, use the [REST API](https://www.trakhound.com/docs/mtconnect-server/api), which reads the same tables.
