SQL and BI cookbook ​

Use these read-only queries to investigate one observed base, verify data freshness, or seed a customer-owned BI model. They intentionally use only the current Data reference, not processing tables or JSON payload shapes.

Before running a query ​

Run queries with a dedicated reader that has USAGE on orloi and SELECT only on the tables it needs. The results can contain Airtable identifiers, names, operational text, and sensitive findings. Do not share them across observed bases or load them into a less restricted BI destination.

Every query below expects a single sync_id: the UUID that identifies an observed base/engine. Replace psql variables such as :'sync_id' with bound parameters in your BI tool; do not concatenate user input into SQL. Keep the session read-only when possible:

sql
BEGIN READ ONLY;
SET LOCAL statement_timeout = '15s';
SET LOCAL lock_timeout = '3s';

The current implementation baseline is schema version 42. It is a current interface, not a versioned API. Start with the first query; if the version or required columns differ, stop and adapt a customer-owned view after inspecting that database. All timestamps are timestamptz; timestamps passed below are explicit UTC instants unless stated otherwise. A half-open range (>= start, < end) avoids duplicates at a reporting boundary.

1. Confirm the installed interface ​

Compatibility: schema version 42 current interface. Bounds: metadata-only; one schema_meta row and a fixed table list. Timezone: none. Expected output: the installed data-changelog version and the available documented columns. Access: reader needs SELECT on orloi.schema_meta and access to information_schema.columns.

sql
SELECT component, version, installed_at, updated_at
FROM orloi.schema_meta
WHERE component = 'data_changelog';

SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'orloi'
  AND table_name IN (
    'data_changelog_raw_events',
    'data_changelog_compacted_events',
    'data_changelog_metric_definitions',
    'data_changelog_metric_values',
    'data_changelog_metric_value_events',
    'data_changelog_reports',
    'data_changelog_alerts',
    'data_changelog_signals'
  )
ORDER BY table_name, ordinal_position;

If the first result is absent or not version 42, do not assume the remaining examples are executable unchanged. The schema may be older, upgrading, or include rows from older payload formats.

2. Recent readable activity ​

Use compacted events for the first human-readable account of activity. One compacted event can represent several raw events.

Compatibility: schema version 42 current interface; data_changelog_compacted_events. Bounds: one engine, an explicit 7-day UTC interval, newest 200 rows. Timezone: event_timestamp is rendered in UTC; convert only in presentation. Expected output: the newest readable activity items, including their source category and number of source events. Access: SELECT on compacted events; text, record names, and IDs can be sensitive.

sql
WITH params AS (
  SELECT
    :'sync_id'::uuid AS sync_id,
    :'start_utc'::timestamptz AS start_at,
    :'end_utc'::timestamptz AS end_at
)
SELECT
  event.id,
  event.event_timestamp AT TIME ZONE 'UTC' AS event_time_utc,
  event.event_type,
  event.event_scope,
  event.table_name,
  event.record_name,
  event.source_category,
  event.raw_event_count,
  event.text
FROM orloi.data_changelog_compacted_events AS event
CROSS JOIN params
WHERE event.sync_id = params.sync_id
  AND event.event_timestamp >= params.start_at
  AND event.event_timestamp < params.end_at
ORDER BY event.event_timestamp DESC, event.id DESC
LIMIT 200;

Trace compacted-event lineage ​

Use this when the Activity view shows a compacted event but you need to inspect the captured changes that support its summary. Replace the placeholders with the compacted event ID and the observed-base sync_id.

Compatibility: schema version 42 current interface; data_changelog_compacted_events and data_changelog_raw_events. Bounds: one engine and one compacted event; returns only the raw events named by that event's lineage. Timezone: event and receipt timestamps are rendered as stored. Expected output: the source-level changes behind one compacted event, ordered from oldest to newest. Access: SELECT on compacted and raw events; source metadata and before/after values can be sensitive.

sql
WITH compacted AS (
  SELECT raw_event_ids
  FROM orloi.data_changelog_compacted_events
  WHERE id = :'compacted_event_id'::bigint
    AND sync_id = :'sync_id'::uuid
)
SELECT
  raw.id,
  raw.event_timestamp,
  raw.received_at,
  raw.event_type,
  raw.source,
  raw.source_metadata,
  raw.table_id,
  raw.record_id,
  raw.record_name,
  raw.field_id,
  raw.previous_values,
  raw.current_values
FROM orloi.data_changelog_raw_events AS raw
JOIN compacted ON raw.id = ANY(compacted.raw_event_ids)
WHERE raw.sync_id = :'sync_id'::uuid
ORDER BY raw.event_timestamp ASC, raw.id ASC;

If the query returns no rows but the compacted event remains, first check whether raw-event retention cleanup removed that period. Do not reconstruct missing raw values from the compacted text alone.

3. History for one record ​

Use raw events when investigating a known record. This returns source-level capture, not a reconstructed current record state.

Compatibility: schema version 42 current interface; data_changelog_raw_events. Bounds: one engine, one table and record, an explicit UTC interval, newest 250 rows. Timezone: both source event time and capture arrival are shown in UTC. Expected output: ordered captured events and capture lag for one record; a missing row does not prove Airtable had no change outside scope or before activation. Access: SELECT on raw events; source metadata, names, and event text can expose sensitive data.

sql
WITH params AS (
  SELECT
    :'sync_id'::uuid AS sync_id,
    :'table_id'::text AS table_id,
    :'record_id'::text AS record_id,
    :'start_utc'::timestamptz AS start_at,
    :'end_utc'::timestamptz AS end_at
)
SELECT
  raw.id,
  raw.event_timestamp AT TIME ZONE 'UTC' AS event_time_utc,
  raw.received_at AT TIME ZONE 'UTC' AS received_time_utc,
  raw.received_at - raw.event_timestamp AS capture_delay,
  raw.event_type,
  raw.field_id,
  raw.source,
  raw.text
FROM orloi.data_changelog_raw_events AS raw
CROSS JOIN params
WHERE raw.sync_id = params.sync_id
  AND raw.table_id = params.table_id
  AND raw.record_id = params.record_id
  AND raw.event_timestamp >= params.start_at
  AND raw.event_timestamp < params.end_at
ORDER BY raw.event_timestamp DESC, raw.id DESC
LIMIT 250;

4. Activity by source and table ​

This is a bounded operational summary, not reliable actor attribution: a source category describes the captured source classification, not a verified human identity.

Compatibility: schema version 42 current interface; data_changelog_compacted_events. Bounds: one engine and a 31-day UTC interval; grouping can return at most the tables and source categories present in that interval. Timezone: events are bucketed by a UTC interval; use a customer-owned model for local-calendar reporting. Expected output: readable-event count and underlying raw-event count by table and source category. Access: SELECT on compacted events; table names can reveal business structure.

sql
WITH params AS (
  SELECT
    :'sync_id'::uuid AS sync_id,
    :'start_utc'::timestamptz AS start_at,
    :'end_utc'::timestamptz AS end_at
)
SELECT
  COALESCE(event.table_name, event.table_id, '(no table)') AS table_label,
  COALESCE(event.source_category, 'unknown') AS source_category,
  count(*) AS compacted_event_count,
  sum(event.raw_event_count) AS raw_event_count
FROM orloi.data_changelog_compacted_events AS event
CROSS JOIN params
WHERE event.sync_id = params.sync_id
  AND event.event_timestamp >= params.start_at
  AND event.event_timestamp < params.end_at
GROUP BY 1, 2
ORDER BY raw_event_count DESC, table_label
LIMIT 100;

5. Capture freshness and arrival delay ​

Compare the newest source event with its receipt time. This verifies stored raw capture only; it does not prove compaction, metrics, reports, or alerts are up to date.

Compatibility: schema version 42 current interface; data_changelog_raw_events. Bounds: one engine; index-backed newest 500 raw events only. Timezone: output is UTC; the delay is an interval. Expected output: latest source activity, latest receipt, and the worst/p95 capture delay among the recent sample. Access: SELECT on raw events; aggregate output avoids raw payloads but still reveals activity timing.

sql
WITH recent AS (
  SELECT event_timestamp, received_at
  FROM orloi.data_changelog_raw_events
  WHERE sync_id = :'sync_id'::uuid
  ORDER BY received_at DESC, id DESC
  LIMIT 500
)
SELECT
  max(event_timestamp) AT TIME ZONE 'UTC' AS newest_event_time_utc,
  max(received_at) AT TIME ZONE 'UTC' AS newest_received_time_utc,
  max(received_at - event_timestamp) AS maximum_capture_delay,
  percentile_cont(0.95) WITHIN GROUP (
    ORDER BY extract(epoch FROM received_at - event_timestamp)
  ) * interval '1 second' AS p95_capture_delay
FROM recent;

An empty result means no retained raw events were found for that engine; it does not distinguish an inactive scope, retention cleanup, or a capture issue.

6. Metric history with its definition ​

Metric values are computed results. A stored zero differs from a missing or not-yet-computed window, and values can be recomputed.

Compatibility: schema version 42 current interface; metric definitions and values. Bounds: one engine, one metric key, a 90-day UTC interval, latest 100 windows. Timezone: periods are stored as instants; interpret calendar-day boundaries with the metric/report configuration rather than the database session timezone. Expected output: a metric's active definition and its stored period values with source-event counts. Access: SELECT on both metric tables; definitions can reveal operational measures.

sql
WITH params AS (
  SELECT
    :'sync_id'::uuid AS sync_id,
    :'metric_key'::text AS metric_key,
    :'start_utc'::timestamptz AS start_at,
    :'end_utc'::timestamptz AS end_at
)
SELECT
  definition.name,
  definition.metric_type,
  definition.status,
  value.period_type,
  value.period_start AT TIME ZONE 'UTC' AS period_start_utc,
  value.period_end AT TIME ZONE 'UTC' AS period_end_utc,
  value.numeric_value,
  value.source_event_count,
  value.computation_version,
  value.updated_at AT TIME ZONE 'UTC' AS computed_at_utc
FROM orloi.data_changelog_metric_definitions AS definition
JOIN orloi.data_changelog_metric_values AS value
  ON value.metric_definition_id = definition.id
CROSS JOIN params
WHERE definition.sync_id = params.sync_id
  AND definition.metric_key = params.metric_key
  AND value.sync_id = params.sync_id
  AND value.period_start >= params.start_at
  AND value.period_start < params.end_at
ORDER BY value.period_start DESC, value.id DESC
LIMIT 100;

7. Metric provenance that remains in hot storage ​

This query links one known metric-value ID to retained raw event rows. Metric provenance can outlive raw-event retention, so a missing raw row is expected after cleanup.

Compatibility: schema version 42 current interface; metric values, provenance links, and raw events. Bounds: one metric value ID, one engine, newest 200 linked raw events. Timezone: raw event and receipt times are UTC. Expected output: the retained source events that contributed to the chosen metric value; NULL raw columns identify retained provenance links whose raw row is gone. Access: SELECT on all three tables; raw rows can be highly sensitive.

sql
SELECT
  link.metric_value_id,
  link.raw_event_id,
  raw.event_timestamp AT TIME ZONE 'UTC' AS event_time_utc,
  raw.received_at AT TIME ZONE 'UTC' AS received_time_utc,
  raw.event_type,
  raw.table_name,
  raw.record_name,
  raw.source
FROM orloi.data_changelog_metric_value_events AS link
JOIN orloi.data_changelog_metric_values AS value
  ON value.id = link.metric_value_id
LEFT JOIN orloi.data_changelog_raw_events AS raw
  ON raw.id = link.raw_event_id
  AND raw.sync_id = value.sync_id
WHERE value.sync_id = :'sync_id'::uuid
  AND link.metric_value_id = :'metric_value_id'::bigint
ORDER BY raw.event_timestamp DESC NULLS LAST, link.raw_event_id DESC
LIMIT 200;

8. Open or recently updated findings ​

Alerts are persisted findings, not automatic facts or workflow commands. Do not automate business decisions from their status, score, evidence, or payload.

Compatibility: schema version 42 current interface; data_changelog_alerts. Bounds: one engine, open alerts or alerts updated in the last 31 days, newest 100 rows. Timezone: windows and seen timestamps are shown in UTC. Expected output: current open findings plus recently updated resolved/dismissed findings, with their reported window. Access: SELECT on alerts; titles and summaries can be sensitive. JSON evidence and payload are deliberately excluded.

sql
WITH params AS (
  SELECT
    :'sync_id'::uuid AS sync_id,
    :'updated_since_utc'::timestamptz AS updated_since
)
SELECT
  id,
  alert_type,
  severity,
  status,
  title,
  summary,
  window_start AT TIME ZONE 'UTC' AS window_start_utc,
  window_end AT TIME ZONE 'UTC' AS window_end_utc,
  last_seen_at AT TIME ZONE 'UTC' AS last_seen_utc,
  resolved_at AT TIME ZONE 'UTC' AS resolved_utc
FROM orloi.data_changelog_alerts
CROSS JOIN params
WHERE sync_id = params.sync_id
  AND (status = 'open' OR updated_at >= params.updated_since)
ORDER BY (status = 'open') DESC, last_seen_at DESC, id DESC
LIMIT 100;

9. Recent report delivery and processing state ​

This lists report metadata without reading report text, structured content, or delivery JSON. A non-success status is a state to investigate, not a guarantee that no usable data exists.

Compatibility: schema version 42 current interface; data_changelog_reports. Bounds: one engine, one cadence, a 90-day UTC window, newest 30 reports. Timezone: report windows are rendered in UTC; timezone records the report's configured timezone. Expected output: report windows, configured timezone, status, event count, and timestamps useful for troubleshooting. Access: SELECT on reports; even metadata can reveal reporting cadence and activity volume.

sql
WITH params AS (
  SELECT
    :'sync_id'::uuid AS sync_id,
    :'cadence'::text AS cadence,
    :'start_utc'::timestamptz AS start_at,
    :'end_utc'::timestamptz AS end_at
)
SELECT
  id,
  cadence,
  timezone,
  window_start AT TIME ZONE 'UTC' AS window_start_utc,
  window_end AT TIME ZONE 'UTC' AS window_end_utc,
  status,
  compacted_event_count,
  compaction_ready_at AT TIME ZONE 'UTC' AS compaction_ready_utc,
  pipeline_started_at AT TIME ZONE 'UTC' AS pipeline_started_utc,
  created_at AT TIME ZONE 'UTC' AS created_utc,
  updated_at AT TIME ZONE 'UTC' AS updated_utc,
  error_message
FROM orloi.data_changelog_reports
CROSS JOIN params
WHERE sync_id = params.sync_id
  AND cadence = params.cadence
  AND window_start >= params.start_at
  AND window_start < params.end_at
ORDER BY window_start DESC, id DESC
LIMIT 30;

10. Active signals and their recency ​

Signals describe repeated or ongoing patterns. Scores and occurrence counts are analysis output, not stable thresholds or guaranteed predictions.

Compatibility: schema version 42 current interface; data_changelog_signals. Bounds: one engine and active signals only; newest 100 signal rows. Timezone: observed-window and update times are UTC. Expected output: currently ongoing signals ordered by the latest observed window and score. Access: SELECT on signals; it excludes the version-sensitive payload but still exposes sensitive pattern metadata.

sql
SELECT
  id,
  signal_type,
  source_type,
  window_type,
  window_start AT TIME ZONE 'UTC' AS window_start_utc,
  latest_observed_window_end AT TIME ZONE 'UTC' AS latest_window_end_utc,
  occurrence_count,
  missed_observation_count,
  latest_score,
  peak_score,
  updated_at AT TIME ZONE 'UTC' AS updated_utc
FROM orloi.data_changelog_signals
WHERE sync_id = :'sync_id'::uuid
  AND ongoing = true
ORDER BY latest_observed_window_end DESC, latest_score DESC, id DESC
LIMIT 100;

11. Retained raw-event footprint by day ​

This estimates table-row and payload-byte volume for a deliberately short range. It is not a Postgres disk-usage measurement: indexes, TOAST storage, dead tuples, and provider backups are outside the result.

Compatibility: schema version 42 current interface; raw-event byte columns. Bounds: one engine and a 31-day UTC interval; at most 31 daily groups. Timezone: day buckets are UTC; choose explicit UTC dates to avoid DST ambiguity. Expected output: raw-row counts and the sum of recorded payload-component bytes per UTC day. Access: SELECT on raw events; aggregates do not expose payload content but reveal activity and volume.

sql
WITH params AS (
  SELECT
    :'sync_id'::uuid AS sync_id,
    :'start_utc'::timestamptz AS start_at,
    :'end_utc'::timestamptz AS end_at
)
SELECT
  date_trunc('day', raw.received_at AT TIME ZONE 'UTC') AS received_day_utc,
  count(*) AS raw_event_count,
  sum(COALESCE(raw.payload_bytes, 0)) AS payload_bytes,
  sum(COALESCE(raw.compaction_payload_bytes, 0)) AS compaction_payload_bytes
FROM orloi.data_changelog_raw_events AS raw
CROSS JOIN params
WHERE raw.sync_id = params.sync_id
  AND raw.received_at >= params.start_at
  AND raw.received_at < params.end_at
GROUP BY 1
ORDER BY received_day_utc DESC
LIMIT 31;

12. Enumerate observed bases available to the reader ​

There is no documented customer-account table in the read interface. Distinct sync_id values below are only the engines that have retained compacted activity and that the database role can already read.

Compatibility: schema version 42 current interface; data_changelog_compacted_events. Bounds: a 31-day UTC interval and at most 100 engines. Timezone: latest event time is UTC. Expected output: recent engine IDs and their latest compacted-event timestamp; map IDs to customer-owned tenant metadata outside orloi. Access: cross-engine enumeration is sensitive and should be granted only to an administrator or a BI role already authorized for every returned engine.

sql
WITH params AS (
  SELECT
    :'start_utc'::timestamptz AS start_at,
    :'end_utc'::timestamptz AS end_at
)
SELECT
  event.sync_id,
  max(event.event_timestamp) AT TIME ZONE 'UTC' AS latest_event_time_utc,
  count(*) AS compacted_event_count
FROM orloi.data_changelog_compacted_events AS event
CROSS JOIN params
WHERE event.event_timestamp >= params.start_at
  AND event.event_timestamp < params.end_at
GROUP BY event.sync_id
ORDER BY latest_event_time_utc DESC
LIMIT 100;

Use a customer-owned BI boundary ​

For a recurring export, do not point an unconstrained dashboard at orloi tables. Put a versioned model under your own control, preserve the source sync_id, and refresh incrementally with an ordered cursor. For example, use the highest copied compacted-event id plus an explicit engine filter; this keeps one batch bounded and avoids rescanning history.

Compatibility: schema version 42 current interface; compacted-event scalar columns only. Bounds: one engine, IDs strictly after one saved customer-owned cursor, at most 1,000 rows. Timezone: event timestamp is supplied as UTC for display; incremental ordering is by database ID, not time. Expected output: one deterministic export page. Save the largest returned compacted_event_id only after the destination accepts the page. Access: SELECT on compacted events; the destination needs equivalent protection for text, IDs, and source classification.

sql
SELECT
  id AS compacted_event_id,
  sync_id,
  event_timestamp AT TIME ZONE 'UTC' AS event_time_utc,
  event_type,
  event_scope,
  table_id,
  record_id,
  source_category,
  raw_event_count,
  text
FROM orloi.data_changelog_compacted_events
WHERE sync_id = :'sync_id'::uuid
  AND id > :'last_compacted_event_id'::bigint
ORDER BY id ASC
LIMIT 1000;

This page is for investigations and a carefully owned read model. For raw-event retention, use the separate retention guide; for a custom export or deletion, use Custom export and deletion with SQL. For feature meaning and limits, use the activity, metrics, findings and processes, and reports guides.