Quickstart: Query event data with ScopeQL

Load a small order-processing dataset and find where orders stop progressing. The same events support the filtering, search, payload, aggregation, and ownership queries in Query events.

You need a ScopeDB Cloud account with account verification complete and a workspace ready to use. Your browser sign-in is enough for this walkthrough.

Start with a new events table so the expected counts are easy to verify. If events already exists, use a fresh name throughout this walkthrough and the query guide.

1. Create an event table

Event data usually has a few fields you query often and a payload that varies by event type. Use typed columns for the timestamp, service, event name, and message; keep order details in var.

In Console, open Data, select Create table in scopedb.public, switch to JSON, paste this table definition, and select Review changes → Create table (or add the same five columns in Form view):

{
  "name": "events",
  "columns": [
    { "name": "time", "data_type": "timestamp" },
    { "name": "service", "data_type": "string" },
    { "name": "name", "data_type": "string" },
    { "name": "message", "data_type": "string" },
    { "name": "var", "data_type": "object" }
  ]
}

This schema is an example, not a requirement: ScopeDB tables do not need a time column or an object column. Once the table exists, open Query and run each ScopeQL block below separately using Run.

2. Insert sample events

This fictional dataset follows 12 orders through checkout, inventory, and notifications over a 12-minute window ending just before NOW(). It includes payment failures, stock shortages, and an email timeout.

Run this statement once to insert 40 sample events.

VALUES
    ('0s', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1001', 'region': 'apac'}),
    ('3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1001', 'region': 'apac',
         'gateway': 'credit_card', 'outcome': 'success'}),
    ('5s', 'inventory', 'stock_reserved',
        'Items reserved for order',
        {'order_id': 'order-1001', 'region': 'apac',
         'sku': 'travel-mug', 'outcome': 'success'}),
    ('7s', 'notifications', 'confirmation_sent',
        'Order confirmation email sent',
        {'order_id': 'order-1001', 'region': 'apac',
         'channel': 'email', 'outcome': 'success'}),
    ('1m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1002', 'region': 'eu'}),
    ('1m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1002', 'region': 'eu',
         'gateway': 'credit_card', 'outcome': 'success'}),
    ('1m5s', 'inventory', 'stock_reserved',
        'Items reserved for order',
        {'order_id': 'order-1002', 'region': 'eu',
         'sku': 'travel-mug', 'outcome': 'success'}),
    ('1m7s', 'notifications', 'confirmation_sent',
        'Order confirmation email sent',
        {'order_id': 'order-1002', 'region': 'eu',
         'channel': 'email', 'outcome': 'success'}),
    ('2m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1003', 'region': 'us'}),
    ('2m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1003', 'region': 'us',
         'gateway': 'bank_transfer', 'outcome': 'success'}),
    ('2m5s', 'inventory', 'stock_reserved',
        'Items reserved for order',
        {'order_id': 'order-1003', 'region': 'us',
         'sku': 'travel-mug', 'outcome': 'success'}),
    ('2m7s', 'notifications', 'confirmation_sent',
        'Order confirmation email sent',
        {'order_id': 'order-1003', 'region': 'us',
         'channel': 'email', 'outcome': 'success'}),
    ('3m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1004', 'region': 'apac'}),
    ('3m3s', 'checkout', 'payment_failed',
        'Credit card gateway timed out',
        {'order_id': 'order-1004', 'region': 'apac',
         'gateway': 'credit_card', 'outcome': 'failed'}),
    ('4m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1005', 'region': 'apac'}),
    ('4m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1005', 'region': 'apac',
         'gateway': 'bank_transfer', 'outcome': 'success'}),
    ('4m5s', 'inventory', 'stock_reserved',
        'Items reserved for order',
        {'order_id': 'order-1005', 'region': 'apac',
         'sku': 'travel-mug', 'outcome': 'success'}),
    ('4m7s', 'notifications', 'confirmation_sent',
        'Order confirmation email sent',
        {'order_id': 'order-1005', 'region': 'apac',
         'channel': 'email', 'outcome': 'success'}),
    ('5m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1006', 'region': 'eu'}),
    ('5m3s', 'checkout', 'payment_failed',
        'Card declined by issuer',
        {'order_id': 'order-1006', 'region': 'eu',
         'gateway': 'credit_card', 'outcome': 'failed'}),
    ('6m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1007', 'region': 'us'}),
    ('6m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1007', 'region': 'us',
         'gateway': 'credit_card', 'outcome': 'success'}),
    ('6m5s', 'inventory', 'stock_reserved',
        'Items reserved for order',
        {'order_id': 'order-1007', 'region': 'us',
         'sku': 'travel-mug', 'outcome': 'success'}),
    ('6m7s', 'notifications', 'notification_failed',
        'Email provider timed out',
        {'order_id': 'order-1007', 'region': 'us',
         'channel': 'email', 'outcome': 'failed'}),
    ('7m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1008', 'region': 'apac'}),
    ('7m3s', 'checkout', 'payment_failed',
        'Credit card gateway timed out',
        {'order_id': 'order-1008', 'region': 'apac',
         'gateway': 'credit_card', 'outcome': 'failed'}),
    ('8m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1009', 'region': 'eu'}),
    ('8m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1009', 'region': 'eu',
         'gateway': 'bank_transfer', 'outcome': 'success'}),
    ('8m5s', 'inventory', 'stock_reserved',
        'Items reserved for order',
        {'order_id': 'order-1009', 'region': 'eu',
         'sku': 'travel-mug', 'outcome': 'success'}),
    ('8m7s', 'notifications', 'confirmation_sent',
        'Order confirmation email sent',
        {'order_id': 'order-1009', 'region': 'eu',
         'channel': 'email', 'outcome': 'success'}),
    ('9m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1010', 'region': 'us'}),
    ('9m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1010', 'region': 'us',
         'gateway': 'credit_card', 'outcome': 'success'}),
    ('9m5s', 'inventory', 'reservation_failed',
        'Requested item is out of stock',
        {'order_id': 'order-1010', 'region': 'us',
         'sku': 'canvas-tote', 'outcome': 'failed'}),
    ('10m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1011', 'region': 'apac'}),
    ('10m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1011', 'region': 'apac',
         'gateway': 'credit_card', 'outcome': 'success'}),
    ('10m5s', 'inventory', 'stock_reserved',
        'Items reserved for order',
        {'order_id': 'order-1011', 'region': 'apac',
         'sku': 'travel-mug', 'outcome': 'success'}),
    ('10m7s', 'notifications', 'confirmation_sent',
        'Order confirmation email sent',
        {'order_id': 'order-1011', 'region': 'apac',
         'channel': 'email', 'outcome': 'success'}),
    ('11m', 'checkout', 'checkout_started',
        'User opened checkout',
        {'order_id': 'order-1012', 'region': 'eu'}),
    ('11m3s', 'checkout', 'payment_succeeded',
        'Payment completed',
        {'order_id': 'order-1012', 'region': 'eu',
         'gateway': 'bank_transfer', 'outcome': 'success'}),
    ('11m5s', 'inventory', 'reservation_failed',
        'Requested item is out of stock',
        {'order_id': 'order-1012', 'region': 'eu',
         'sku': 'canvas-tote', 'outcome': 'failed'})
SELECT
    NOW() - '12m'::interval + $0::interval AS time,
    $1::string AS service,
    $2::string AS name,
    $3::string AS message,
    $4::object AS var
INSERT INTO events

Check the imported batch before continuing:

FROM events
AGGREGATE count() AS events,
    count_distinct(var['order_id']::string) AS orders,
    count_distinct(service) AS services

events	orders	services
40	12	3

If you see 80 events and 12 orders, the batch was inserted twice. Use a fresh table and run the insert once to reproduce the results below.

3. Query recent events

Read the latest five events from the last hour. ScopeQL is a pipeline: read a table, filter rows, select columns, and sort the result.

FROM events
WHERE time >= NOW() - '1h'::interval
SELECT time, service, name, var['order_id']::string AS order_id
ORDER BY time DESC
LIMIT 5

The latest event is reservation_failed for order-1012, after its successful payment. The next rows show the earlier steps for that order and the final steps for order-1011. Your time values reflect when you inserted the batch.

4. Aggregate events

Compare event volume and failed operations across services:

FROM events
GROUP BY service
AGGREGATE count() AS events,
    sum(if(var['outcome']::string = 'failed', 1, 0)) AS failures
ORDER BY failures DESC, service

service	events	failures
checkout	24	3
inventory	9	2
notifications	7	1

The failures occur at different stages: three orders fail to pay, two paid orders fail to reserve stock, and one confirmation attempt times out. Checkout records both starts and payment outcomes, so these event totals are not denominators for comparing failure rates. The query guide identifies the affected orders and payment methods, then adds a service directory to find the responsible teams.

Use a terminal instead

Connect a client, then apply the table definition with scope catalog and execute each ScopeQL block with scope query:

scope catalog apply --table scopedb.public.events -f events.json --yes
scope query -f insert-events.scopeql

Save the JSON definition from step 1 as events.json and the complete insert statement from step 2 as insert-events.scopeql, then run each command once. Keep each .scopeql file to one statement.

Next steps

Continue with Query events using this dataset. For a continuously running producer, follow Investigate application errors or Analyze Bluesky activity. For a specific task:

Use Explore in Console and Tables in Console to inspect the events table visually.
Model event data explains what belongs in typed columns and what belongs in var.
Ingest events shows how to load and reshape event batches with ScopeQL before connecting an application writer.
Add indexes for recurring filters and searches.