events

8,577,493 rows

✎ View and edit SQL

This data as json, CSV (advanced)

Next page

CREATE TABLE events (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    occurred_at     TEXT NOT NULL,            -- ISO 8601 UTC, source-of-truth time
    received_at     TEXT NOT NULL,            -- when WE saw it (for lag analysis)
    store           TEXT,                     -- 'centralvapors' | 'wholesale' | NULL for cvreports/system events
    source          TEXT NOT NULL,            -- 'bc:webhook' | 'bc:systemlog' | 'bc:account' | 'bc:state_diff' | 'app:bundle' | 'app:audit'
    category        TEXT NOT NULL,            -- 'order' | 'product' | 'inventory' | 'customer' | 'cart' | 'auth' | 'permission' | 'app' | ...
    action          TEXT NOT NULL,            -- 'created' | 'updated' | 'deleted' | 'login' | 'permission_added' | ...
    scope           TEXT,                     -- raw BC webhook scope when source=bc:webhook
    actor           TEXT,                     -- BC user email if known, 'system', 'webhook', 'cv-bundle-app'
    target_type     TEXT,                     -- 'order' | 'product' | 'variant' | 'sku' | ...
    target_id       TEXT,                     -- BC entity id
    bc_log_id       INTEGER,                  -- BC System Log id (for dedup)
    bc_hash         TEXT,                     -- BC webhook payload hash (for dedup)
    severity        TEXT,                     -- 'success' | 'notice' | 'warning' | 'error'
    payload_json    TEXT NOT NULL,            -- full raw event body
    enriched_json   TEXT,                     -- enriched entity (e.g., full order body fetched after webhook)
    correlation_id  TEXT,                     -- ties intent to confirming webhook (cv-bundle-app pairs)
    prev_hash       TEXT,                     -- previous row's chain hash
    row_hash        TEXT NOT NULL             -- sha256(prev_hash || canonical_json(this_row_minus_chain))
, target_id_derived TEXT GENERATED ALWAYS AS (
  COALESCE(
    NULLIF(target_id, ''),
    json_extract(payload_json, '$.data.cart_id'),
    json_extract(payload_json, '$.data.order_id'),
    json_extract(payload_json, '$.data.product_id'),
    json_extract(payload_json, '$.data.customer_id'),
    json_extract(payload_json, '$.data.sku_id'),
    json_extract(payload_json, '$.data.id')
  )
) VIRTUAL);
CREATE INDEX idx_events_occurred       ON events(occurred_at DESC);
CREATE INDEX idx_events_store_action   ON events(store, category, action);
CREATE INDEX idx_events_target         ON events(target_type, target_id);
CREATE INDEX idx_events_actor          ON events(actor);
CREATE INDEX idx_events_correlation    ON events(correlation_id) WHERE correlation_id IS NOT NULL;
CREATE INDEX idx_events_source         ON events(source);
CREATE UNIQUE INDEX idx_events_bc_log_id ON events(store, bc_log_id) WHERE bc_log_id IS NOT NULL;
CREATE UNIQUE INDEX idx_events_bc_hash   ON events(bc_hash) WHERE bc_hash IS NOT NULL;
CREATE INDEX idx_events_received ON events(received_at);
CREATE INDEX idx_events_source_received ON events(source, received_at);
CREATE INDEX idx_events_source_occurred ON events(source, occurred_at);
CREATE INDEX idx_events_source_cat_perf ON events(source, category, occurred_at, received_at);
CREATE INDEX idx_events_severity ON events(severity);
CREATE INDEX idx_events_category ON events(category);
CREATE INDEX idx_events_action ON events(action);
CREATE INDEX idx_events_actor_id
    ON events(actor, id)
    WHERE actor IS NOT NULL AND actor != '';
CREATE INDEX idx_events_target_id_derived
  ON events(target_id_derived)
  WHERE source = 'bc:webhook' AND target_id_derived IS NOT NULL;
CREATE INDEX idx_events_hook_delivery_url
  ON events(json_extract(payload_json, '$.data.url'))
  WHERE source = 'bc:webhook'
    AND scope = 'store/hook/deliveryException';
CREATE INDEX idx_events_warn_sev_occ
    ON events(severity, occurred_at DESC, source);
CREATE INDEX idx_events_src_sev_occ
    ON events(source, severity, occurred_at);