# Stitch v2.0.0

Load survey responses into your data warehouse through Stitch, with the v2.0.0 table schema.

Screeb sends survey displays and answers to Stitch, which loads them into your data warehouse. This page describes the tables of version 2.0.0. To set up the integration in Screeb, see [the help center](https://help.screeb.app/integrations/stitch/).

## Entities

- Organization: A Screeb account, shared by many Screeb users of your company
- Channel: A distribution channel for your survey (JavaScript tag, Android SDK, iOS SDK...)
- Survey
- Survey scenario: A versioned survey: on survey update, your flow is saved in a new scenario. New users will see the last scenario.
- User: Information about the visitor
- Response: A response is a chain of question+answers. A user sometimes has many responses to a single survey.
- Question
- Answer: An answer is part of a response. Each answer is associated with a question.
- Field: An answer may have many fields (for example, a multiple choice question)

## Hook types

You can send 3 different event types into your datawarehouse:

- On survey display: a survey is shown to visitors, but no question is replied. It fires even if the user gives no answer.
- On question answered: a question has been replied. It fires once per answered question.
- On response end: a survey has been closed or fully replied to by the user.

## Migrate from v1.0.0

- The `respondent__*` columns become `user__*`: `respondent__id` becomes `user__anonymous_id` and `respondent__alias` becomes `user__user_id`.
- Answer values move to their own table, one row per value (see the tables below).
- Context data and the user properties snapshot get their own tables, next to the hidden fields.

## Warehouse schema

Stitch delivers the responses to your surveys using the following database schema. In case of schema change, Stitch is in charge of updating columns in your datawarehouse.

Stitch requires an immutable primary key for identifying rows. Screeb uses `event_id` as primary key.

### Tables

| Table name                                                                                           | Description                                                        |
| ---------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------ |
| `<integration>.data`                                                                                 | A table with response metadata (organization, channel, survey...). |
| `<integration>.data__response__items`                                                                | A table with pairs of questions and answers.                       |
| `<integration>.data__response__answer_fields` or `<integration>.data__response__items_answer_fields` | A table with question answer.                                      |
| `<integration>.data__response__hidden_fields`                                                        | A table with hidden fields linked to the response.                 |
| `<integration>.data__response__context`                                                              | A table with context data linked to the response.                  |
| `<integration>.data__user__properties`                                                               | A table user properties snapshot linked to the response.           |

### `data` table

| Column                              | Type                     | Description                                                                           |
| ----------------------------------- | ------------------------ | ------------------------------------------------------------------------------------- |
| `_sdc_batched_at`                   | timestamp with time zone | Stitch internal                                                                       |
| `_sdc_received_at`                  | timestamp with time zone | Stitch internal                                                                       |
| `_sdc_sequence`                     | bigint                   | Stitch internal                                                                       |
| `_sdc_table_version`                | bigint                   | Stitch internal                                                                       |
| `event_id`                          | text                     | Unique id for this event (same id in retry request)                                   |
| `event_type`                        | text                     | Type of event sent over webhook                                                       |
| `event_time`                        | text                     | Date of event                                                                         |
| `event_time_ms`                     | bigint                   | Date of event in millisecond                                                          |
| `version`                           | text                     | Semver version of the webhook payload                                                 |
| `organization__id`                  | text                     | Unique identifier for the organization                                                |
| `organization__name`                | text                     | Name of the Screeb account                                                            |
| `channel__id`                       | text                     | Unique identifier for the channel                                                     |
| `channel__type`                     | text                     | Channel type (widget, android, ios, hosted-page...)                                   |
| `user__anonymous_id`                | text                     | Screeb identifier for the user                                                        |
| `user__user_id`                     | text                     | Main user identifier                                                                  |
| `user__name`                        | text                     | User name (when available in identity properties)                                     |
| `user__email`                       | text                     | User email (when available in identity properties)                                    |
| `user__group_names`                 | \[]text                  | User groups                                                                           |
| `survey__id`                        | text                     | Unique identifier for the survey                                                      |
| `survey__name`                      | text                     | Name of the survey                                                                    |
| `survey__scenario_id`               | text                     | Unique identifier for the survey scenario                                             |
| `survey__scenario_time`             | text                     | Date of the scenario edition                                                          |
| `survey__scenario_time_ms`          | bigint                   | Timestamp of the scenario edition                                                     |
| `survey__scenario_version`          | bigint                   | Version number of the survey scenario                                                 |
| `response__id`                      | text                     | Unique identifier for the response                                                    |
| `response__time`                    | text                     | Date of response start                                                                |
| `response__time_ms`                 | bigint                   | Timestamp of response start in millisecond                                            |
| `response__time_to_complete_second` | bigint                   | Seconds between survey display and response end (when event_type == `response.ended`) |
| `response__locale`                  | text                     | Locale of the user                                                                    |
| `response__tags`                    | \[]text                  | Tags of the response                                                                  |
| `response__question__id`            | text                     | Unique identifier for the question                                                    |
| `response__question__type`          | text                     | Type of question                                                                      |
| `response__question__title`         | text                     | Label of the question                                                                 |
| `response__answer__replied_at`      | text                     | Date of the answer                                                                    |
| `response__answer__replied_at_ms`   | bigint                   | Timestamp of the answer (millisecond)                                                 |
| `response__answer__tags`            | \[]text                  | Tags of the answer                                                                    |

### `data__response__items` table

| Column                     | Type                     | Description                           |
| -------------------------- | ------------------------ | ------------------------------------- |
| `_sdc_batched_at`          | timestamp with time zone | Stitch internal                       |
| `_sdc_level_0_id`          | bigint                   | Stitch internal                       |
| `_sdc_received_at`         | timestamp with time zone | Stitch internal                       |
| `_sdc_sequence`            | bigint                   | Stitch internal                       |
| `_sdc_source_key_event_id` | text                     | Stitch internal                       |
| `_sdc_table_version`       | bigint                   | Stitch internal                       |
| `question__id`             | text                     | Unique identifier for the question    |
| `question__type`           | text                     | Type of question                      |
| `question__title`          | text                     | Label of the question                 |
| `answer__replied_at`       | text                     | Date of the answer                    |
| `answer__replied_at_ms`    | bigint                   | Timestamp of the answer (millisecond) |
| `answer__tags`             | \[]text                  | Tags of the answer                    |

### `data__response__answer_fields` table

| Column                     | Type                     | Description                                                      |
| -------------------------- | ------------------------ | ---------------------------------------------------------------- |
| `_sdc_batched_at`          | timestamp with time zone | Stitch internal                                                  |
| `_sdc_level_0_id`          | bigint                   | Stitch internal                                                  |
| `_sdc_received_at`         | timestamp with time zone | Stitch internal                                                  |
| `_sdc_sequence`            | bigint                   | Stitch internal                                                  |
| `_sdc_source_key_event_id` | text                     | Stitch internal                                                  |
| `_sdc_table_version`       | bigint                   | Stitch internal                                                  |
| `id`                       | text                     | Unique identifier for the field (a button, a score, an input...) |
| `type`                     | text                     | Value type: "string", "number", "time", "boolean"...             |
| `number`                   | bigint                   | Value of the field (numeric)                                     |
| `bool`                     | boolean                  | Value of the field (boolean)                                     |
| `string`                   | text                     | Value of the field (string)                                      |
| `time`                     | text                     | Value of the field (string)                                      |

### `data__response__hidden_fields` + `data__response__context` + `data__user__properties` table

| Column                     | Type                     | Description                                                    |
| -------------------------- | ------------------------ | -------------------------------------------------------------- |
| `_sdc_batched_at`          | timestamp with time zone | Stitch internal                                                |
| `_sdc_level_0_id`          | bigint                   | Stitch internal                                                |
| `_sdc_received_at`         | timestamp with time zone | Stitch internal                                                |
| `_sdc_sequence`            | bigint                   | Stitch internal                                                |
| `_sdc_source_key_event_id` | text                     | Stitch internal                                                |
| `_sdc_table_version`       | bigint                   | Stitch internal                                                |
| `name`                     | text                     | Hidden field key                                               |
| `type`                     | text                     | Hidden field value type: "string", "number", "time", "boolean" |
| `value__number`            | bigint                   | Value of the hidden field (numeric)                            |
| `value__bo`                | boolean                  | Value of the hidden field (boolean)                            |
| `value__st`                | text                     | Value of the hidden field (string)                             |
