# SQL database (Postgres, MySQL)

SQL databases are easy to set up and often the go-to choices for less abstracted storage solutions. Terra currently supports **Postgres & MySQL**.

### Setup

You will need to ensure your SQL database is 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 [data destinations overview page](https://docs.tryterra.co/health-and-fitness-api/integration-setup/setting-up-data-destinations/..#ip-whitelisting), and use a strong, unique password for the database user.
{% endhint %}

Next, 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 %}

Enter your database host, port, database name, username, and password in the Terra Dashboard when adding the SQL destination.

### Data Structure

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 it works:**

* **`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/health-and-fitness-api/managing-user-health-data/requesting-historical-data) to request it again.
{% endhint %}