# 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](/health-and-fitness-api/integration-setup/setting-up-data-destinations.md#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](/health-and-fitness-api/managing-user-health-data/requesting-historical-data.md) to request it again.
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.tryterra.co/health-and-fitness-api/integration-setup/setting-up-data-destinations/sql-database-postgres-mysql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.