Data reference

Orloi stores its working data in the customer-owned orloi Postgres schema. This page is the documented boundary for reading that data as it exists in the current release. It is useful for investigations, customer-owned reporting, and building your own read-only views.

It is not a versioned public analytical API. Orloi currently creates and upgrades tables in place, does not publish compatibility views, and does not promise that every table, column, JSON shape, status value, or derived interpretation is stable across future schema versions. Do not make a production integration depend directly on undocumented tables or on jsonb payload shapes.

What is supported

The supported interface is deliberately small:

1. Read the current schema after checking its installed version.
2. Scope every query to one sync_id (the observed-base / engine identifier).
3. Use documented scalar columns from the core and analytical tables below for bounded, read-only queries.
4. Treat payload, evidence, artifact, config, and other JSON columns as implementation data unless a feature-specific guide describes the value you need.
5. Own the compatibility boundary yourself: expose a view, extract, or warehouse model under your control and version that output for downstream consumers.

Orloi itself owns schema installation and upgrades. Do not add foreign keys, triggers, constraints, or writes to orloi tables. It revokes PUBLIC privileges on the schema, tables, and sequences; grant a dedicated reader only the access it needs. See privacy and data boundaries and the AI-agent data guide for safe access patterns.

Check the installed baseline first

The current server writes data-changelog schema version 42. A database can be older, mid-upgrade, or retain rows created under older payload formats, so this number identifies an installed baseline rather than a compatibility promise.

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

Before using a documented object, also verify that it and its expected columns exist in that database:

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_reports',
    'data_changelog_alerts',
    'data_changelog_signals'
  )
ORDER BY table_name, ordinal_position;

If schema_meta is missing, version 42 is not installed, or a column is absent, stop treating this page as an executable query contract. Adapt a customer-owned view after inspecting that database, or upgrade through the Orloi runtime.

Data dictionary and stability boundary

All rows below are tenant-scoped by sync_id; never combine or expose rows across engines without an explicit customer-side policy. base_id, table_id, record_id, field IDs, names, source metadata, and value/payload columns can identify operational data and may be sensitive.

Object	Purpose and useful scalar fields	Key and relationship	Time fields	Sensitivity	Stability / version introduced
schema_meta	Installed component version. component, version.	component is the key; use the data_changelog row.	installed_at, updated_at.	Low operational metadata.	Current implementation metadata. Present in the version-42 baseline; historical introduction is not published.
data_changelog_raw_events	Close-to-source captured activity. Use id, event_key, sync_id, base_id, event_type, event_scope, table/record/field IDs, event_timestamp, received_at, source.	id primary key; event_key unique. sync_id is the tenant boundary.	event_timestamp is source event time; received_at is when Orloi received it.	High: can contain names, source metadata, and before/after values.	Documented read surface at baseline 42. payload_format_version and all value/payload JSON are explicitly version-sensitive.
data_changelog_compacted_events	Readable, derived activity history. Use id, compacted_event_key, sync_id, event_type, event_scope, table/record IDs, event_timestamp, raw_event_count, source_category, text.	id primary key; compacted_event_key unique. raw_event_ids is provenance, not a foreign key.	event_timestamp, raw_event_started_at, raw_event_ended_at, created_at.	High: text and payload can include business values and names.	Documented read surface at baseline 42. Compaction and payload format may change; do not parse payload or assume one row per raw event.
data_changelog_metric_definitions	Customer-configured metric rules. Use id, sync_id, metric_key, name, metric_type, table/field IDs, status.	id primary key; unique (sync_id, metric_key). Referenced by metric values.	created_at, updated_at.	Medium to high: configuration reveals operational measures.	Documented read surface at baseline 42. config and metric-type semantics are feature implementation details.
data_changelog_metric_values	Computed metric values for a bounded window. Use id, sync_id, metric_definition_id, metric_key, period_type, period_start, period_end, numeric_value, source_event_count, computation_version.	id primary key; FK metric_definition_id → metric definitions. Unique by definition and window.	period_start, period_end, created_at, updated_at.	Medium: aggregates can still disclose business activity.	Documented read surface at baseline 42. Values can be recomputed; computation_version is not a cross-release semantic guarantee.
data_changelog_metric_value_events	Provenance links from a metric value to captured raw events. metric_value_id, raw_event_id.	FK metric_value_id → metric values. raw_event_id has no foreign key to raw events, so retention can leave no matching raw row.	None.	High through linkage to raw activity.	Current implementation relationship, documented for provenance only; not a retention guarantee.
data_changelog_reports	Generated daily/weekly reports. Use id, report_key, sync_id, cadence, timezone, window, status, compacted-event range, error_message.	id primary key; report_key unique. first_compacted_event_id / last_compacted_event_id are range markers, not foreign keys.	window_start, window_end, created_at, updated_at, pipeline timestamps.	High: report text and structured JSON may contain business information.	Documented read surface at baseline 42. Report content, status transitions, and delivery JSON are not stable API fields.
data_changelog_alerts	Persisted findings. Use id, sync_id, alert_key, alert_type, severity, status, title, summary, window.	id primary key; unique (sync_id, alert_key). metric_definition_id is an un-enforced logical reference.	window, first/last seen, resolved, created, updated.	High: findings can describe sensitive operations.	Current analysis output. Do not automate decisions from alerts or parse evidence / payload as a stable contract.
data_changelog_signals	Repeated or ongoing detected patterns. Use id, sync_id, signal_key, occurrence_key, type/window fields, ongoing, counts, scores.	id primary key; unique (sync_id, occurrence_key). metric_definition_id is a logical reference.	window start, latest observed end, stopped, created, updated.	Medium to high.	Current analysis output; scores and payload semantics are implementation details.

Objects such as state, task bundles, LLM-call records, schema intelligence, automation mappings, process variants, detected process maps, open loops, company profiles, and database telemetry are processing or feature internals. They may be useful for support or an individual feature, but they are not part of this documented analytical read surface.

Relationships

The database enforces only the metric-definition/value and metric-value/provenance relationships shown with solid lines. Other arrows are semantic lineage recorded in IDs or arrays, not foreign keys. Rows can outlive related source data after retention cleanup.

erDiagram
  SCHEMA_META {
    text component PK
    text version
  }
  RAW_EVENTS {
    bigint id PK
    uuid sync_id
    text event_key UK
    timestamptz event_timestamp
    timestamptz received_at
  }
  COMPACTED_EVENTS {
    bigint id PK
    uuid sync_id
    bigint_array raw_event_ids "logical provenance"
    timestamptz event_timestamp
  }
  METRIC_DEFINITIONS {
    bigint id PK
    uuid sync_id
    text metric_key
  }
  METRIC_VALUES {
    bigint id PK
    uuid sync_id
    bigint metric_definition_id FK
    timestamptz period_start
    timestamptz period_end
  }
  METRIC_VALUE_EVENTS {
    bigint metric_value_id FK
    bigint raw_event_id "logical raw-event reference"
  }
  REPORTS {
    bigint id PK
    uuid sync_id
    bigint first_compacted_event_id "range marker"
    bigint last_compacted_event_id "range marker"
  }
  ALERTS {
    bigint id PK
    uuid sync_id
    bigint metric_definition_id "logical reference"
  }
  SIGNALS {
    bigint id PK
    uuid sync_id
    bigint metric_definition_id "logical reference"
  }

  METRIC_DEFINITIONS ||--o{ METRIC_VALUES : "enforced FK"
  METRIC_VALUES ||--o{ METRIC_VALUE_EVENTS : "enforced FK"
  RAW_EVENTS o|--o{ METRIC_VALUE_EVENTS : "provenance; no FK"
  RAW_EVENTS o|--o{ COMPACTED_EVENTS : "raw_event_ids; no FK"
  COMPACTED_EVENTS o|--o{ REPORTS : "ID range; no FK"
  METRIC_DEFINITIONS o|--o{ ALERTS : "logical ID; no FK"
  METRIC_DEFINITIONS o|--o{ SIGNALS : "logical ID; no FK"

Build a durable integration outside orloi

Use a dedicated reader and publish the fields you need through a customer-owned schema or warehouse model. A simple example deliberately avoids JSON and unbounded scans:

CREATE SCHEMA IF NOT EXISTS analytics;

CREATE OR REPLACE VIEW analytics.orloi_activity_v1 AS
SELECT
  sync_id,
  id AS compacted_event_id,
  event_timestamp,
  event_type,
  event_scope,
  table_id,
  record_id,
  source_category,
  text,
  raw_event_count
FROM orloi.data_changelog_compacted_events;

Version and test analytics.orloi_activity_v1 as your own contract, including filters for the engines a consumer may access. Use a physical, incrementally loaded table rather than a live view when a downstream system needs a frozen history, controlled refresh schedule, or protection from Orloi retention changes.

For an individual investigation, keep queries bounded and ordered, for example:

SELECT id, event_timestamp, event_type, event_scope, table_id, record_id, text
FROM orloi.data_changelog_compacted_events
WHERE sync_id = '<engine_sync_id>'
  AND event_timestamp >= now() - interval '7 days'
ORDER BY event_timestamp DESC, id DESC
LIMIT 200;

Consequences for consumers

• A sync_id is the sole common tenant key; it is not a public account or Airtable-base identifier. Preserve it in your model and map it to your own tenant metadata.
• Raw event and compacted event IDs are useful within the installed database, but raw retention can remove rows referenced by compacted events or metric provenance.
• event_timestamp describes source activity; received_at describes capture arrival. Do not substitute one for the other in latency or reporting logic.
• Derived tables are eventually produced from captured history. Missing, stale, replaced, or recomputed values are possible during processing, scope changes, and feature evolution.
• Values in JSON, text, names, identifiers, evidence, and report output may be sensitive. Apply the same access controls and retention policy that you use for the underlying Airtable data.

For the meaning and limits of raw versus compacted history, see investigate activity and changelog events. For metrics, reports, and data retention, use their feature guides rather than inferring semantics from the database layout.