Submit query Skill | Agent Skills

DTS Raw PostgreSQL via Redash

Core Principle: Direct access to DTS PostgreSQL replica via Redash API at tools.improvado.io for agencies, connections, users, and extraction data.

Data Architecture (Continuant - TD):

graph TD
    Redash[Redash tools.improvado.io] --> DTS[(DTS Replica)]

    DTS --> DSAS[dsas_extraction_*]
    DTS --> Agencies[agencies_*]
    DTS --> Users[users_*]
    DTS --> MCDM[mcdm_*]
    DTS --> Orders[orders_*]

    DSAS --> Agency[agency]
    DSAS --> Connection[connection]
    DSAS --> Account[account]
    DSAS --> DataSource[datasource]

Query Flow (Occurrent - LR):

graph LR
    Q[SQL Query] --> API[POST /api/query_results]
    API --> Job[Job ID]
    Job --> Poll[GET /api/jobs/ID]
    Poll --> QR[query_result_id]
    QR --> Data[GET /api/query_results/ID]

Ontological Rule: TD for database structure, LR for async query execution

Environment

REDASH_API_URL=https://redash.tools.improvado.io
REDASH_API_TOKEN=<your_token>
REDASH_DTS_DATA_SOURCE_ID=<data_source_id>  # e.g. 851 for "dts - replica"

API Usage

Execute query:

import os, requests, time

def query_dts(sql: str) -> list:
    url = os.getenv("REDASH_API_URL")
    token = os.getenv("REDASH_API_TOKEN")
    ds_id = int(os.getenv("REDASH_DTS_DATA_SOURCE_ID"))
    headers = {"Authorization": f"Key {token}"}

    # Submit query
    resp = requests.post(f"{url}/api/query_results", headers=headers, json={
        "query": sql,
        "data_source_id": ds_id,
        "max_age": 0
    })
    job_id = resp.json()["job"]["id"]

    # Poll until done (status: 1=queued, 2=started, 3=done, 4=error)
    while True:
        time.sleep(2)
        job = requests.get(f"{url}/api/jobs/{job_id}", headers=headers).json()["job"]
        if job["status"] == 3:
            qr_id = job["query_result_id"]
            return requests.get(f"{url}/api/query_results/{qr_id}", headers=headers).json()["query_result"]["data"]["rows"]
        elif job["status"] == 4:
            raise Exception(job["error"])

curl example:

# Submit query
curl -X POST -H "Authorization: Key $REDASH_API_TOKEN" \
  -H "Content-Type: application/json" \
  "$REDASH_API_URL/api/query_results" \
  -d "{\"query\": \"SELECT * FROM dsas_extraction_agency LIMIT 5\", \"data_source_id\": $REDASH_DTS_DATA_SOURCE_ID, \"max_age\": 0}"

# Poll job (replace JOB_ID)
curl -H "Authorization: Key $REDASH_API_TOKEN" "$REDASH_API_URL/api/jobs/JOB_ID"

# Get result (replace QR_ID)
curl -H "Authorization: Key $REDASH_API_TOKEN" "$REDASH_API_URL/api/query_results/QR_ID"

Key Tables (125 total)

dsas_extraction_ (15 tables) - Core extraction:* | Table | Purpose | |-------|---------| | dsas_extraction_agency | Agency master (id, rtbm_agency_id, agency_name) | | dsas_extraction_connection | Data source connections (datasource_name, credentials_id) | | dsas_extraction_account | Extracted accounts per connection | | dsas_extraction_datasource | Available data sources catalog | | dsas_extraction_extracttemplate | Extraction templates |

agencies_ (12 tables) - Agency management:* | Table | Purpose | |-------|---------| | agencies_agency | Agency details | | agencies_workspacesusers | User-workspace assignments | | agencies_workspacerole | Role definitions | | agencies_contract | Agency contracts |

users_ (5 tables) - User management:* | Table | Purpose | |-------|---------| | users_user | User accounts | | users_usersession | Active sessions | | users_userloginattempt | Login audit |

mcdm_ (13 tables) - Dashboards & recipes:* | Table | Purpose | |-------|---------| | mcdm_dashboard | Dashboard definitions | | mcdm_mcdmrecipe | MCDM recipes | | mcdm_transformationorder | Transformation queue |

orders_ (6 tables) - Extraction orders:* | Table | Purpose | |-------|---------| | orders_order | Extraction orders | | orders_orderjob | Job execution status |

Schema: dsas_extraction_agency

id                             uuid PRIMARY KEY
rtbm_agency_id                 integer          -- Legacy RTBM ID
agency_name                    varchar NOT NULL -- Display name
agency_iname                   varchar NOT NULL -- Internal name
is_enabled                     boolean NOT NULL
is_test_agency                 boolean NOT NULL
created_at                     timestamptz NOT NULL
updated_at                     timestamptz NOT NULL

Schema: dsas_extraction_connection

id                             integer PRIMARY KEY
agency_id                      uuid             -- FK to agency
datasource_name                varchar          -- e.g. "facebook_ads"
credentials_id                 varchar          -- Vault reference
params                         jsonb            -- Connection params
workspace_id                   integer
created_at                     timestamptz
invalidated_at                 timestamptz      -- NULL if valid

Common Queries

-- List active agencies
SELECT id, rtbm_agency_id, agency_name, created_at
FROM dsas_extraction_agency
WHERE is_enabled = true AND is_test_agency = false
ORDER BY created_at DESC LIMIT 50;

-- Connections by agency
SELECT c.id, c.datasource_name, c.created_at, a.agency_name
FROM dsas_extraction_connection c
JOIN dsas_extraction_agency a ON c.agency_id = a.id
WHERE c.invalidated_at IS NULL
ORDER BY c.created_at DESC LIMIT 100;

-- Data sources with connection count
SELECT c.datasource_name, COUNT(*) as conn_count
FROM dsas_extraction_connection c
WHERE c.invalidated_at IS NULL
GROUP BY c.datasource_name
ORDER BY conn_count DESC;

-- Users by agency
SELECT u.email, u.first_name, u.last_name, u.is_active
FROM users_user u
WHERE u.is_active = true
ORDER BY u.date_joined DESC LIMIT 50;

Ground Truth

Redash URL: via $REDASH_API_URL (default: https://redash.tools.improvado.io)
Data Source ID: via $REDASH_DTS_DATA_SOURCE_ID (e.g. 851 = "dts - replica")
Tables: 125 in public schema
NOT for: Client data (use ClickHouse Storage shard), internal analytics (use Palantir)
Different from: dts-postgres skill which uses redash.improvado.io for marketing warehouse

Agent Skills: Submit query

Install this agent skill to your local

Skill Files