> This page is for version Latest (default).
> For other versions, use one of these documentation indexes:
> - Latest (default): https://docs.coactive.ai/latest/llms.txt

> For clean Markdown of any page, append .md to the page URL.
> For a complete documentation index, see https://docs.coactive.ai/llms.txt.
> For AI client integration (Claude Code, Cursor, etc.), connect to the MCP server at https://docs.coactive.ai/_mcp/server.

# SQL Table Reference

Which tables appear depends on the dataset's media type and when it was ingested. Run `SHOW TABLES` to see the tables for your dataset.

This page lists the columns in each SQL table Coactive creates for a dataset, so you can write queries without running `DESCRIBE` first. To run queries, see [Getting Started with the Query Engine](/docs-guides/core-features/query-engine/getting-started).

Dynamic tag views (`dt_*`) are documented separately in [Dynamic Tag Tables](/docs-guides/core-features/query-engine/dynamic-tag-tables).

## Core tables

### coactive_table

| Column                | Definition                                                                                                              | Format                |
| --------------------- | ----------------------------------------------------------------------------------------------------------------------- | --------------------- |
| **coactive_image_id** | Coactive image identifier                                                                                               | string                |
| **coactive_shot_id**  | Legacy. Not populated on current datasets. For shot-level data, use `dt_[group]_shot` joined to `coactive_table_slice`. | string                |
| **coactive_video_id** | Coactive video identifier                                                                                               | string                |
| **created_dt**        | Timestamp of video ingestion (keyframe creation)                                                                        | timestamp             |
| **keyframe_index**    | Index of the keyframe within the asset                                                                                  | string                |
| **keyframe_time_ms**  | Start time of the keyframe                                                                                              | float                 |
| **path**              | Storage path of the keyframe                                                                                            | string                |
| **metadata**          | File-level metadata as key/value pairs (for example, `title` and `genre`)                                               | `map<string, string>` |

To read a `metadata` value, see [Read metadata values](/docs-guides/core-features/query-engine/getting-started#read-metadata-values).

### coactive_table_adv

An advanced version of `coactive_table`. It includes every column in `coactive_table`, plus additional metadata and a pair of columns for each concept:

| Additional column         | Definition                                                                                                                 | Format           |
| ------------------------- | -------------------------------------------------------------------------------------------------------------------------- | ---------------- |
| **\[concept_name]\_prob** | Score representing the likelihood that the concept applies to the asset                                                    | float (0.0–1.0)  |
| **\[concept_name]**       | Binary flag indicating whether the concept applies to the asset, using a default threshold of 0.7 on `[concept_name]_prob` | integer (0 or 1) |

For example queries, see [Analyzing Concepts with SQL](/docs-guides/deep-dives/analyzing-concepts-with-sql).

### coactive_table_video

| Column                | Definition                                                                          | Format                |
| --------------------- | ----------------------------------------------------------------------------------- | --------------------- |
| **coactive_video_id** | Coactive video identifier                                                           | string                |
| **path**              | Storage path of the video asset                                                     | string                |
| **created_dt**        | Timestamp of video ingestion                                                        | timestamp             |
| **metadata**          | File-level metadata as key/value pairs (for example, `series_name` and `tv_rating`) | `map<string, string>` |

To read a `metadata` value, see [Read metadata values](/docs-guides/core-features/query-engine/getting-started#read-metadata-values).

### coactive_table_keyframe

| Column            | Definition                                                                  | Format  |
| ----------------- | --------------------------------------------------------------------------- | ------- |
| **id**            | Coactive keyframe identifier                                                | string  |
| **org_id**        | Organization identifier                                                     | string  |
| **dataset_id**    | Dataset identifier                                                          | string  |
| **video_id**      | Coactive video identifier                                                   | string  |
| **status**        | Processing status of this record (for example, `new`, `processed`, `error`) | string  |
| **path**          | Storage path of the keyframe                                                | string  |
| **frame_time_ms** | Timestamp of the frame within the interval, in milliseconds                 | integer |
| **start_time_ms** | Start time of the interval the keyframe was extracted from, in milliseconds | integer |
| **end_time_ms**   | End time of the interval the keyframe was extracted from, in milliseconds   | integer |

### coactive_table_transcription

| Column                    | Definition                                                     | Format                  |
| ------------------------- | -------------------------------------------------------------- | ----------------------- |
| **org_id**                | Organization identifier                                        | string                  |
| **dataset_id**            | Dataset identifier                                             | string                  |
| **id**                    | Unique identifier of the transcript segment                    | string                  |
| **video_id**              | Associated video identifier                                    | string                  |
| **audio_id**              | Associated audio track identifier                              | string                  |
| **language**              | Language of the transcript segment                             | string                  |
| **granularity**           | Level of segmentation (for example, `sentence`)                | string                  |
| **text**                  | Transcript text                                                | string                  |
| **start_time**            | Start time of the segment in seconds (relative to audio start) | float                   |
| **end_time**              | End time of the segment in seconds                             | float                   |
| **confidence_score**      | Confidence score of the ASR output for the segment             | float (0.0–1.0)         |
| **created_dt**            | When the transcript record was created                         | timestamp               |
| **audio_file_format**     | Format of the audio file (for example, `wav`)                  | string                  |
| **audio_file_duration_s** | Duration of the audio file in seconds                          | float                   |
| **channel_index**         | Channel index within multi-channel audio                       | integer (starts from 0) |

## Concept tables

### concept\_\[concept name]

Each concept gets its own table, for example `concept_beach_scenes`.

| Column                | Definition                                              | Format          |
| --------------------- | ------------------------------------------------------- | --------------- |
| **coactive_image_id** | Coactive identifier of the image or keyframe            | string          |
| **concept_name**      | Name of the concept                                     | string          |
| **score**             | Likelihood the concept applies to the image or keyframe | float (0.0–1.0) |

## Video structure tables

Video structure tables describe how a video is divided into time ranges. They support native video understanding.

### coactive_table_slice

A slice is a time range in a video with a specific property.

| Column              | Definition                                                          | Format                  |
| ------------------- | ------------------------------------------------------------------- | ----------------------- |
| **id**              | Unique identifier of the slice                                      | string                  |
| **composite_id**    | Identifier of the composite (group of slices) this slice belongs to | string                  |
| **index**           | Index of the slice within the composite                             | integer (starts from 0) |
| **start_frame_num** | Frame index at which the slice starts                               | integer (starts from 0) |
| **end_frame_num**   | Frame index at which the slice ends                                 | integer                 |
| **start_time_ms**   | Start time of the slice, in milliseconds                            | integer                 |
| **end_time_ms**     | End time of the slice, in milliseconds                              | integer                 |
| **video_id**        | Coactive video identifier the slice is associated with              | string                  |

### coactive_table_composite

A composite, such as a shot or a scene, is made of slices.

| Column         | Definition                                                 | Format    |
| -------------- | ---------------------------------------------------------- | --------- |
| **id**         | Unique identifier of the composite                         | string    |
| **video_id**   | Coactive video identifier the composite is associated with | string    |
| **type**       | Composite type (for example, `intelligent sampling`)       | string    |
| **status**     | Processing status (for example, `new`)                     | string    |
| **created_dt** | When the composite was created                             | timestamp |
| **updated_dt** | When the composite was last updated                        | timestamp |

Ad segments are stored in `coactive_table_composite` as rows where `type = 'ad-segment'`. For a query that uses them, see [Find ad segments](/docs-guides/deep-dives/query-your-dataset-with-sql#find-ad-segments).

### coactive_table_shot

Legacy. Appears only on older datasets. Use [`dt_[group]_shot`](/docs-guides/core-features/query-engine/dynamic-tag-tables) joined to `coactive_table_slice` instead.