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.

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, and use a strong, unique password for the database user.

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:

Postgres

Terra creates tables under a dedicated terra schema. Your database user needs the following permissions:

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;

MySQL

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:

Postgres

All tables are created under the terra schema (e.g. terra.terra_users).

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
);

MySQL

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.

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 to request it again.