PushizoDocs
Users & segments

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

ViewColumns
usersuser_id, external_id, properties, tags, first_seen_at, last_active_at, session_count, usage_seconds, last_sentiment, created_at
subscriptionssubscription_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
eventsevent_id, user_id, name, properties, value, source, occurred_at
deliveriesdelivery_id, message_id, user_id, channel, status, sent_at, delivered_at, opened_at, clicked_at, failed_at, created_at
messagesmessage_id, campaign_id, journey_run_id, source, status, name, category, created_at
campaignscampaign_id, name, type, status, category, labels, send_at, started_at, completed_at, created_at
segment_membershipssegment_id, user_id, computed_at
companiescompany_id, external_id, name, domain, properties, created_at, updated_at
company_memberscompany_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, SELECT only (CTEs, subqueries, JOIN, UNION and window functions are allowed). INSERT, UPDATE, DELETE, DDL, data-modifying CTEs, SELECT INTO, FOR UPDATE and 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_setting and 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: true is returned.
  • A rejection responds with 400 HS_VALIDATION_ERROR, details.code = "sql_rejected" and details.reason (not_select, multiple_statements, forbidden_relation, forbidden_function, forbidden_type, forbidden_construct, syntax, empty, too_long). A timeout responds with 422, 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 desc

Saving 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_error field 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
EndpointDescription
GET /v1/apps/{app}/sql/schemaViews, columns and limits
POST /v1/apps/{app}/sql/queryRun a query (query, row_limit, timeout_ms); rows are arrays in column order
GET /v1/apps/{app}/sql/runsQuery log (segment_id, status, limit, cursor)
GET /v1/apps/{app}/sql/segmentsSQL segments
POST /v1/apps/{app}/sql/segmentsSegment 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}/refreshRefresh 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.

On this page