Skip to main content

Common Columns

Many tables in the BI Connector share a set of recurring columns. Their meaning and behavior is consistent everywhere they appear, so they are documented once here. Individual table pages link back to this reference and only describe table-specific columns.

Ids​

Most primary and foreign key ids are UUIDs stored as strings (varchar(36)). In some cases ids will instead be keys from other systems and will have a different format. The docs for each table will provide details on ids if there is any specific usage.

id: the primary identifier for a record in the table. Used to join other tables that reference this row.

companyid: the id of the company that owns the record. Nearly every table includes companyid and is scoped to your company.

Timestamps​

All timestamps have the same format and structure unless stated otherwise.

  • Format is yyyy-mm-dd hh:mm:ss.dddddd. eg, 2024-10-15 12:06:32.523754
  • Some timestamp columns may use milliseconds (3 decimals) instead of microseconds (6 decimals)
  • Timestamps are not timezone aware and timezone is always UTC
  • Max datetime is 9999-12-01 00:00:00.000000
  • Values in the year of 9999 or greater will be pushed up or down to the max datetime as usage of the year 9999 is assumed that the user wants the max datetime. eg 300000-05-26 will become 9999-12-01, but 9999-02-15 will also become 9999-12-01

createdat and updatedat: timestamps that are populated for each record on most tables. Every table will have different methods and processes for updatedat but this will always represent the last time a record was changed. Both createdat and updatedat will be in the standard TI timestamp format above.

Status and lifecycle​

statusgroup and statusgroupupdatedat: a rollup column that provides a single source of truth for whether a record is active, inactive, or deleted, regardless of which source column (deleted, disabled, archived, active, status) the underlying table uses. See Status Group for full details, value definitions, and the table-to-column mapping.

Import and export​

Some tables carry a set of columns that track whether a row was created through an import or cross-instance content-copy flow rather than through the normal product UI. These columns have the same meaning everywhere they appear and are typically all null for records created through standard UI flows. Not every table exposes every column, see the individual table page for which ones apply.

imported: boolean. true if the row was created via an import process, false otherwise.

importid: identifier of the import job that created the row. Set by bulk import flows (admin CSV imports, content imports, API bulk imports) and used to correlate all rows that came in together from the same import.

tableimportid: set when the row was created via a bulk table import (admin-driven CSV import of records and related data). null for records created through the normal UI.

backupexportid: set when the row was copied from another TI instance via the sandbox-to-production content copy flow. References the source row's backup-export identifier. null for content authored directly in the current instance.