Model semi-structured event data

ScopeDB is useful when event data has both stable structure and changing payloads. Model that directly: use typed columns for the fields you depend on and a semi-structured column for the rest.

The examples use events, but ScopeDB does not require an event schema or a time column. Apply the same typed-versus-flexible decision to your own data model.

Run the ScopeQL examples in Query, or save a block to a file and use scope query -f <file> after connecting your client.

Recommended shape

Define the table in Data (Tables in Console) or through the Catalog API:

{
  "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 table gives you:

a timestamp for filtering and ordering;
stable tags for common filters and groups;
a message field for human-readable evidence;
an object column for event-specific context.

Put fields in typed columns when they are stable

Good typed column candidates:

event time;
service, application, environment, region, account, or workspace identifiers;
event name or kind;
status, severity, or result;
fields whose meaning and type are shared across producers and consuming applications.

Typed columns make that contract visible and avoid repeatedly extracting the same path from a semi-structured object. A frequent filter alone does not require a dedicated column: you can index a nested path directly.

Keep fields in `var` when they are flexible

Good var candidates:

nested request or response details;
optional integration-specific fields;
integration-specific labels;
raw event payloads used during investigation;
fields that appear in only a small percentage of rows.

You can still query these fields:

FROM events
SELECT
    time,
    service,
    var['http']['url']::string AS http_url,
    var['user']['id']::string AS user_id
WHERE http_url IS NOT NULL
LIMIT 100

Evolve the model gradually

Start by preserving context in var. When a nested path becomes a frequent access pattern, consider an index. When its meaning and type have stabilized, expose it as a typed column.

Expose a field without changing producers

If producers already record a host name in var['machine']['host'], a computed column can expose it as a typed, read-only host column. Add this column entry to columns in the table's complete Catalog definition, then preview and apply the change:

{ "name": "host", "data_type": "string", "expression": "var['machine']['host']" }

The expression applies to historical and incoming rows. Queries can read host directly without repeating the nested path or a fallback expression. Missing values and evaluation or conversion errors produce NULL. Producers keep writing the original var payload and must omit the computed host field from appended rows.

Let producers write the field directly

Choose an ordinary column instead when producers should supply the value directly. This is an alternative to the computed-column approach above: an existing column cannot switch between ordinary and computed kinds in place.

For a table that does not already have host, first add an ordinary column to columns in Edit table on Tables in Console or through the Catalog API:

{ "name": "host", "data_type": "string" }

Adding this nullable column changes the table definition without rewriting existing rows. Historical rows read NULL in host; adding the column does not copy values out of var. Producers that omit the new column can continue writing rows, with NULL in that column.

Before changing producers, update queries that need both old and new records to prefer the column and fall back to the nested field:

FROM events
SELECT time, service,
    IF(host IS NOT NULL, host, TRY(var['machine']['host']::string)) AS host

Agree on the host name's format before rollout. The fallback casts legacy values to strings; it does not validate their meaning. Normalize inconsistent legacy formats in the query before using them as a shared identifier.

Then update producers to include a top-level host field in each appended row when a valid host name is available. Keep the original nested value in var while consumers still depend on it. For a Go map, add the "host" key only when a value is available; for a struct, add a string field tagged json:"host,omitempty" so an empty value is omitted. Create the column before deploying this change.

The SDK guides and Go SDK write guide cover append batching and commit confirmation. Check the committed rows when rolling out the new producer. Older producers can keep their existing shape, so they do not all need to upgrade together.

Keep the fallback query while retained rows may have a value only in var. Removing it requires either backfilling those rows or waiting until they are no longer retained. The schema change itself does not perform that backfill.

Model event data