SQL query and SQL segment
Define your audience with SQL when the rule builder is not enough: run a read-only SELECT query on the permitted views, see the result in a table and save the people in the result as a segment. A SQL segment is…
Define your audience with SQL when the rule builder is not enough: run a read-only SELECT query on the permitted
views, see the result in a table and save the people in the result as a segment. A SQL segment is refreshed manually or
on a schedule and is used in campaign, journey and in-app audiences like any other segment. Available on Growth and
above (plan feature sql_segments, ADR-0087).
Views
Queries can read only the views below. The views always return the data of the selected app; rows of another app never
appear in them. You can write the names unqualified (users) or qualified (sql_api.users).
| View | Columns |
|---|---|
users | user_id, external_id, properties, tags, first_seen_at, last_active_at, session_count, usage_seconds, last_sentiment, created_at |
subscriptions | subscription_id, user_id, channel, status, os, os_version, app_version, sdk_platform, device_model, locale, timezone, test_device, last_active_at, last_delivered_at, opted_in_at, unsubscribed_at, created_at |
events | event_id, user_id, name, properties, value, source, occurred_at |
deliveries | delivery_id, message_id, user_id, channel, status, sent_at, delivered_at, opened_at, clicked_at, failed_at, created_at |
messages | message_id, campaign_id, journey_run_id, source, status, name, category, created_at |
campaigns | campaign_id, name, type, status, category, labels, send_at, started_at, completed_at, created_at |
segment_memberships | segment_id, user_id, computed_at |
companies | company_id, external_id, name, domain, properties, created_at, updated_at |
company_members | company_id, user_id, role, created_at |
Under the personal data policy, the subscription token (email address, phone number, push token), location, advertising ID, IP address and provider error text are not exposed in the views.
Rules and limits
- One statement,
SELECTonly (CTEs, subqueries,JOIN,UNIONand window functions are allowed).INSERT,UPDATE,DELETE, DDL, data-modifying CTEs,SELECT INTO,FOR UPDATEand multiple statements are rejected. - The query is checked with PostgreSQL's own parser. Only permitted functions (aggregate, window, text, number, date,
JSON and array helpers) and basic type casts can be used; functions such as
pg_sleep,pg_read_file,dblink,set_config,current_settingand catalog tables (pg_*,information_schema) are rejected. - The query runs in a read-only transaction. The timeout is 10 s by default and at most 30 s; the row limit is 500 by
default and at most 5,000. If the result table exceeds 2 MB it is cut off and
truncated: trueis returned. - A rejection responds with
400 HS_VALIDATION_ERROR,details.code = "sql_rejected"anddetails.reason(not_select,multiple_statements,forbidden_relation,forbidden_function,forbidden_type,forbidden_construct,syntax,empty,too_long). A timeout responds with422,details.code = "sql_timeout".
Example: gold members with at least three purchases in the last 30 days.
with buyers as (
select user_id, count(*) as orders, sum(value) as total
from events
where name = 'purchase' and occurred_at > now() - interval '30 days'
group by user_id
)
select u.user_id, u.external_id, b.orders, b.total
from users u
join buyers b on b.user_id = u.user_id
where u.properties->>'tier' = 'gold' and b.orders >= 3
order by b.total descSaving as a segment
Dashboard → Segments → SQL editor → run the query → Save as segment. The query result must contain a user_id
column; the unique IDs in this column become the members of the segment.
- IDs are matched only against the people of this app: even if another app's ID is written into the query it cannot become a member. The ID of a merged person is mapped to the target person.
- The segment is static: membership is computed when it is saved, when the query changes and on refresh. At most 1,000,000 people; if exceeded the refresh fails and the previous membership is kept.
- Refresh interval: manual, 1, 6, 12 or 24 hours. The worker scans scheduled refreshes every 5 minutes; on failure a
code is written to the segment's
sql.last_errorfield and the previous membership is kept. - The rule definition and the dynamic/static type of a SQL segment cannot be changed through the segment endpoints;
its name and description can be changed, and it is deleted as usual with
DELETE /segments/{id}.
API
Running a query requires the users:read scope, reading the schema and the log segments:read, and creating, updating
or refreshing a segment admin.
curl -X POST https://api.pushizo.com/v1/apps/$APP_ID/sql/query \
-H "Authorization: Bearer $PUSHIZO_API_KEY" -H "Content-Type: application/json" \
--data-binary @- <<'JSON'
{"query": "select user_id, external_id from users where properties->>'tier' = 'gold'", "row_limit": 100}
JSON
curl -X POST https://api.pushizo.com/v1/apps/$APP_ID/sql/segments \
-H "Authorization: Bearer $PUSHIZO_API_KEY" -H "Content-Type: application/json" \
--data-binary @- <<'JSON'
{"name": "Gold members", "query": "select user_id from users where properties->>'tier' = 'gold'", "refresh_interval_minutes": 1440}
JSON| Endpoint | Description |
|---|---|
GET /v1/apps/{app}/sql/schema | Views, columns and limits |
POST /v1/apps/{app}/sql/query | Run a query (query, row_limit, timeout_ms); rows are arrays in column order |
GET /v1/apps/{app}/sql/runs | Query log (segment_id, status, limit, cursor) |
GET /v1/apps/{app}/sql/segments | SQL segments |
POST /v1/apps/{app}/sql/segments | Segment from a query (name, description, query, refresh_interval_minutes) |
PATCH /v1/apps/{app}/sql/segments/{id} | Name, description, query or refresh interval |
POST /v1/apps/{app}/sql/segments/{id}/refresh | Refresh now |
Node SDK: pushizo.sqlSegments.query({ query }), createSegment(...), refreshSegment(id).
Query log and security
Every run from the dashboard, the API and scheduled refreshes — including rejected and timed-out ones — is written to
the query log: source, user/API key, query text and its hash, status, rejection reason, row count and duration. The
log is shown under the SQL editor in the dashboard and at the GET /sql/runs endpoint.
Layers of defense: parser check, a read-only transaction on a separate connection, statement_timeout and
lock_timeout, a search path that finds only the permitted views, app-filtered security_barrier views and row-level
security (RLS) on the underlying tables. The query and refresh endpoints are limited to 30 requests per minute per user
and 60 per app.
Segments
Dynamic and static segments, the condition AST (all/any/none), field and operator list, behavior and location conditions, preview/estimate endpoints and campaign usage.
Zero-copy warehouse segment
Define audiences with the tables in your BigQuery, Snowflake or Databricks warehouse without copying customer data into Pushizo. The SELECT query you write runs in your warehouse; only the ID column you choose…