> For the complete documentation index, see [llms.txt](https://docs.tryterra.co/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.tryterra.co/destinations/sql-database-postgres-mysql.md).

# SQL database (Postgres, MySQL)

Create a database user so Terra can write events to your Postgres or MySQL database, and read the tables it creates.

SQL databases are easy to set up and often the go-to choices for less abstracted storage solutions. Terra supports Postgres and MySQL. This page covers creating a database user for Terra, adding the destination, and the tables Terra writes.

## Before you begin

* A Postgres or MySQL database reachable from Terra's servers.

{% hint style="warning" %}
**Security:** Do not open your database to the entire internet. Restrict inbound connections to Terra's IP addresses listed on the [Destinations page](https://docs.tryterra.co/destinations/setting-up-data-destinations.md#ip-allowlist), and use a strong, unique password for the database user.
{% endhint %}

## Create a database user

Create a user with enough permissions to create tables and have read & write access within those tables. You can execute the scripts below based on your database:

{% tabs %}
{% tab title="Postgres" %}
Terra creates tables under a dedicated `terra` schema. Your database user needs the following permissions:

```sql
CREATE USER terra_user WITH PASSWORD 'your_password';
GRANT CONNECT ON DATABASE your_database_name TO terra_user;
CREATE SCHEMA IF NOT EXISTS terra;
GRANT USAGE ON SCHEMA terra TO terra_user;
GRANT CREATE ON SCHEMA terra TO terra_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA terra
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO terra_user;
```

{% endtab %}

{% tab title="MySQL" %}

```sql
CREATE USER 'terra_user'@'%' IDENTIFIED BY 'your_password';
GRANT CREATE, SELECT, INSERT, UPDATE, DELETE ON your_database_name.* TO 'terra_user'@'%';
```

{% endtab %}
{% endtabs %}

## Add the destination in Terra

On the [Connections page](https://dashboard.tryterra.co/dashboard/connections) in your Terra dashboard, click **Add New** and add the SQL destination. Enter your database host, port, database name, username, and password, then click **Apply**.

## What Terra writes

Terra creates three tables in your database:

{% tabs %}
{% tab title="Postgres" %}
All tables are created under the `terra` schema (e.g. `terra.terra_users`).

```sql
CREATE TABLE terra.terra_users (
    user_id VARCHAR(36) PRIMARY KEY,
    reference_id TEXT DEFAULT NULL,
    created_at TEXT NOT NULL,
    granted_scopes TEXT DEFAULT NULL,
    provider TEXT NOT NULL
);

CREATE TABLE terra.terra_data_payloads (
    payload_id VARCHAR(255) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    data_type TEXT NOT NULL,
    created_at TEXT NOT NULL,
    payload_url TEXT NOT NULL,
    start_time TEXT DEFAULT NULL,
    end_time TEXT DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES terra.terra_users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE terra.terra_other_payloads (
    user_id VARCHAR(36) NOT NULL,
    payload_type TEXT DEFAULT NULL,
    created_at TEXT NOT NULL,
    payload_url TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES terra.terra_users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);
```

{% endtab %}

{% tab title="MySQL" %}
Tables are created in the database you specify (no schema prefix).

```sql
CREATE TABLE terra_users (
    user_id VARCHAR(36) PRIMARY KEY,
    reference_id TEXT DEFAULT NULL,
    created_at TEXT NOT NULL,
    granted_scopes TEXT DEFAULT NULL,
    provider TEXT NOT NULL
);

CREATE TABLE terra_data_payloads (
    payload_id VARCHAR(255) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    data_type TEXT NOT NULL,
    created_at TEXT NOT NULL,
    payload_url TEXT NOT NULL,
    start_time TEXT DEFAULT NULL,
    end_time TEXT DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES terra_users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE terra_other_payloads (
    user_id VARCHAR(36) NOT NULL,
    payload_type TEXT DEFAULT NULL,
    created_at TEXT NOT NULL,
    payload_url TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES terra_users(user_id)
        ON DELETE CASCADE ON UPDATE CASCADE
);
```

{% endtab %}
{% endtabs %}

**How Terra uses each table:**

* **`terra_users`**: a row is created (or updated) for each user on `auth_success` events.
* **`terra_data_payloads`**: each data event (activity, sleep, body, etc.) gets a row. The actual data payload is stored in a Terra-managed bucket, and the `payload_url` column contains a pre-signed download link.
* **`terra_other_payloads`**: all non-data events (deauth, access revoked, etc.) get a row, with the full event payload available via `payload_url`.

{% hint style="warning" %}
The `payload_url` links are **pre-signed URLs that expire after 10 minutes**. Download the payload promptly after it is inserted. If you need to re-fetch expired data, use the [REST API](https://docs.tryterra.co/unified-api/requesting-historical-data.md) to request it again.
{% endhint %}

## Next steps

* [User authentication](https://docs.tryterra.co/unified-api/authentication-flow.md): link a user's wearable so events start flowing.
* [Event delivery](https://docs.tryterra.co/destinations/events.md): see what each delivery status means and how retries work.
* [Health data events](https://docs.tryterra.co/unified-api/receiving-data-updates.md): process the data Terra delivers.
