SKILL.md

Use these instructions when analyzing Orloi data in a customer-connected Postgres database.

The live customer database is the source of truth. Installed schemas may differ by version, so discover the schema before writing analytical SQL.

Orloi stores compacted activity, raw provenance, metrics, alerts, reports, schema findings, and processing metadata. Use the read-only Postgres connection provided by the user. Do not attempt to create database users, grant permissions, install schemas, or modify Orloi-managed objects.

When to use this

Agent access is useful for:

• Recent activity and audit-style investigations.
• Questions about who changed what, when, and where.
• Table, field, record, collaborator, actor, automation, or source activity.
• Metric and metric-driver investigation.
• Alert, signal, report, and process review.
• Schema validation and schema intelligence review.
• LLM usage metadata review.
• Forensic questions about captured changes.

For most analysis, start with compacted events, metrics, reports, alerts, and signals. Use raw events only when the agent needs audit-level provenance.

Safety rules

• Use the read-only Postgres connection string provided for this Orloi workspace. A sync_id in a prompt is a query filter, not database authorization; do not assume it prevents access to another base. Follow the connection's enforced scope.
• Run SELECT-only analysis by default.
• Begin an explicit read-only transaction for each analysis session. This prevents writes in that transaction; it does not make an over-privileged database role safe. The connection role still needs an enforced read boundary.
• Set a statement_timeout.
• Use bounded date windows for event, metric, report, alert, signal, validation, and LLM-call queries.
• Start with aggregate queries before row-level inspection.
• Use small LIMITs for row samples.
• Avoid unbounded historical scans on event tables.
• Avoid raw JSON payload dumps unless explicitly necessary.
• Summarize and redact sensitive data in answers.

Do not run INSERT, UPDATE, DELETE, TRUNCATE, DROP, ALTER, CREATE, GRANT, REVOKE, migrations, schema installers, or maintenance commands against Orloi-managed objects.

Session-local SET, SHOW, and EXPLAIN are acceptable. Do not change role, default privileges, or any database-wide setting.

EXPLAIN plans a statement without executing it. EXPLAIN ANALYZE executes the statement to collect actual timings and row counts. Use plain EXPLAIN first. Use EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) only for a bounded SELECT inside the read-only transaction when the cost of executing that exact query is acceptable. Never use EXPLAIN ANALYZE on a write statement: rollback does not undo its load, locks, or WAL.

Session setup

Orloi tables are usually installed under the orloi schema. Verify the live schema before analysis. Do not put public in the search path: a writable or user-controlled public schema can shadow unqualified names. The starter queries schema-qualify every Orloi table, so no search path is required for them.

begin read only;
set local statement_timeout = '15s';
set local lock_timeout = '2s';
set local idle_in_transaction_session_timeout = '30s';
set local search_path = pg_catalog, orloi;

show transaction_read_only; -- expected: on
show search_path;           -- expected: pg_catalog, orloi

Use commit or rollback immediately after the analysis. SET LOCAL is transaction-scoped, so it cannot leak settings into a reused connection. If the client cannot run an explicit transaction, do not rely on session SET values or an agent-provided sync_id for safety; use a purpose-built read-only connection with its own timeout and access policy.

If queries time out, reduce the date window and lower row limits before increasing timeouts.

Schema discovery

Before writing non-trivial analytical SQL, introspect the installed schema.

The agent should discover:

• Installed Orloi schema version, if present.
• Table list.
• Columns and data types.
• Primary keys, foreign keys, unique constraints, and check constraints.
• Indexes.
• Timestamp columns.
• JSON and JSONB columns.
• Row counts and relevant min/max timestamps.
• Sample JSONB top-level keys, not full values.

If the agent stores a local schema snapshot, it should not include secrets or raw customer payloads.

Schema version and compatibility

The current server writes data-changelog schema version 42, but this guide is intentionally not a versioned SQL API. A customer database can be older, mid-upgrade, or carry rows from older payload formats. Run the discovery queries first and use a starter query only when its referenced table and columns exist. In particular, inspect payload_format_version before interpreting raw-event value columns. If schema_meta is absent, has no data_changelog row, or differs from the current server version, report that fact and do not infer missing fields from this guide.

select component, version, installed_at, updated_at
from orloi.schema_meta
where component = 'data_changelog';

Expected output: zero or one row. A row identifies the installed data-changelog schema version; it does not prove every historical row has the newest payload format.

Tables

select table_schema, table_name
from information_schema.tables
where table_schema = 'orloi'
  and table_type = 'BASE TABLE'
order by table_name;

Columns

select
  table_name,
  ordinal_position,
  column_name,
  data_type,
  udt_name,
  is_nullable,
  column_default
from information_schema.columns
where table_schema = 'orloi'
order by table_name, ordinal_position;

Constraints

select
  tc.table_name,
  tc.constraint_name,
  tc.constraint_type,
  kcu.column_name,
  ccu.table_name as referenced_table,
  ccu.column_name as referenced_column
from information_schema.table_constraints tc
left join information_schema.key_column_usage kcu
  on kcu.constraint_schema = tc.constraint_schema
 and kcu.constraint_name = tc.constraint_name
left join information_schema.constraint_column_usage ccu
  on ccu.constraint_schema = tc.constraint_schema
 and ccu.constraint_name = tc.constraint_name
where tc.table_schema = 'orloi'
order by tc.table_name, tc.constraint_type, tc.constraint_name, kcu.ordinal_position;

Indexes

select tablename, indexname, indexdef
from pg_indexes
where schemaname = 'orloi'
order by tablename, indexname;

JSONB keys

Inspect JSONB keys before inspecting values. This samples at most 1,000 event rows from the requested time window, then expands keys; the result is not a complete key inventory and counts sampled events containing each key, not value occurrences. It deliberately returns keys only.

with sampled_events as (
  select id, payload
  from orloi.data_changelog_raw_events
  where sync_id = :sync_id
    and event_timestamp >= :window_start
    and event_timestamp < :window_end
    and jsonb_typeof(payload) = 'object'
  order by event_timestamp desc, id desc
  limit 1000
)
select key, count(*) as sampled_event_count
from sampled_events
cross join lateral jsonb_object_keys(sampled_events.payload) as payload_key(key)
group by payload_key.key
order by sampled_event_count desc, key asc
limit 50;

Expected output: up to 50 top-level key names and the number of sampled events that contained each. An empty result means the bounded sample had no object-shaped payload values; it does not prove the table or full history has no JSON data. Adapt this query only after confirming the table, timestamp, and JSONB column names in the live schema.

Table families

Use these groups as a conceptual map. Always verify actual columns from the live schema.

Raw events

Tables commonly named like data_changelog_raw_events.

Raw events are the lowest-level captured change stream. They usually include event ids, sync ids, base/table/field/record identifiers, source metadata, timestamps, readable text, and JSON payloads. In payload format 2, record snapshots are stored separately in previous_values, current_values, and unchanged_values; payload contains the remaining event metadata. Inspect payload_format_version before interpreting those columns.

Use raw events for provenance, detailed audit trails, metric support events, and actor/source analysis. Query cautiously because raw payloads can contain customer operational data, record values, actor names, emails, ids, and automation metadata.

Compacted events

Tables commonly named like data_changelog_compacted_events.

Compacted events group nearby related raw events into more readable activity narratives. They are usually better than raw events for timeline summaries and human-readable investigations.

Metrics

Metric definitions, values, value-event links, and subgroup values describe what metrics exist, how they changed, and which source events or groups contributed to them.

Start with metric definitions before investigating metric values. Use metric value event links only after narrowing to a specific metric value.

Alerts and signals

Alerts are persisted findings, anomalies, or warnings. Signals are detected patterns, often ongoing trends or recurring observations.

Use aggregate review first, then inspect evidence only when needed.

Reports

Reports store generated report metadata and may store report text, structured report JSON, delivery state, token counts, model/provider metadata, validation links, and errors.

Use report metadata first. Do not dump report bodies unless explicitly requested and necessary.

Schema validation and intelligence

Schema validation tables store validation runs, findings, field hints, evidence, suggested fixes, and status. Schema intelligence stores AI-derived schema or business context profiles.

Treat evidence, causal context, and profile JSON as sensitive derived business context.

LLM calls

LLM-call tables generally store metadata such as descriptor, provider, model, request id, token counts, duration, task id, and created time.

Use these tables for usage and cost-style analysis. If prompt or response columns exist, treat them as highly sensitive and summarize only.

Analysis workflow

1. Set safe session settings.
2. Discover schema version, tables, columns, constraints, indexes, timestamp columns, and JSONB columns.
3. Identify available sync ids, bases, workspaces, or equivalent tenant/context ids from discovered tables.
4. Check row counts and time ranges for relevant tables.
5. Start with aggregate queries over bounded windows.
6. Inspect row-level samples only after narrowing by sync id, date range, table, field, record, metric, alert, report, source, actor, or automation.
7. Avoid raw JSON payloads unless required.

Answers should include the tables queried, time window, filters used, caveats about schema version or missing columns, and a concise summary rather than raw dumps.

Starter queries

These are templates. Adapt them to the discovered schema.

Find recent syncs

select sync_id, count(*) as recent_event_count, max(event_timestamp) as last_event_at
from orloi.data_changelog_raw_events
where event_timestamp >= now() - interval '30 days'
group by sync_id
order by last_event_at desc
limit 20;

Expected output: up to 20 sync_id values that had raw events in the last 30 days, their event counts, and the newest captured event timestamp. This is a discovery query across every base visible to the connection, so do not expose its results to a caller who is authorized for only one base.

Recent compacted activity

select
  id,
  event_timestamp,
  event_type,
  event_scope,
  table_id,
  record_id,
  record_name,
  raw_event_count,
  event_class_id,
  left(coalesce(text, ''), 500) as event_summary
from orloi.data_changelog_compacted_events
where sync_id = :sync_id
  and event_timestamp >= :window_start
  and event_timestamp < :window_end
order by event_timestamp desc, id desc
limit 50;

Expected output: up to 50 newest compacted events in the requested half-open time range, ordered newest first. raw_event_count shows how many raw events contributed to the compacted event; event_summary is truncated to 500 characters and can still contain customer data.

Raw activity aggregates

select
  date_trunc('day', event_timestamp) as day,
  source,
  event_type,
  event_scope,
  count(*) as event_count,
  count(distinct table_id) as table_count,
  count(distinct record_id) as record_count
from orloi.data_changelog_raw_events
where sync_id = :sync_id
  and event_timestamp >= :window_start
  and event_timestamp < :window_end
group by 1, 2, 3, 4
order by day desc, event_count desc
limit 100;

Expected output: at most 100 grouped daily rows. event_count is raw-event volume; table_count and record_count are distinct non-null identifiers in that group, so they are not a count of all Airtable tables or records.

Metric overview

select
  d.id,
  d.metric_key,
  d.name,
  d.metric_type,
  d.status,
  d.period_granularity,
  count(v.id) as value_count,
  min(v.period_start) as first_period_start,
  max(v.period_end) as last_period_end
from orloi.data_changelog_metric_definitions d
left join orloi.data_changelog_metric_values v
  on v.metric_definition_id = d.id
where d.sync_id = :sync_id
group by
  d.id,
  d.metric_key,
  d.name,
  d.metric_type,
  d.status,
  d.period_granularity
order by d.status asc, last_period_end desc nulls last, d.name asc
limit 100;

Expected output: at most 100 metric definitions for the selected sync_id, with the count and first/last stored value window. A definition with value_count = 0 exists but has no persisted values in the visible data; it is not proof that the underlying Airtable measure is zero.

Alert overview

Alert windows use a half-open interval: [window_start, window_end). To find an alert that overlaps the requested observation window [ :window_start, :window_end ), compare both endpoints. The query below includes an alert that began before the requested period or ends after it, and excludes an alert ending exactly at the requested start or starting exactly at the requested end.

select
  id,
  alert_key,
  source,
  alert_type,
  severity,
  status,
  title,
  summary,
  confidence,
  window_start,
  window_end,
  first_seen_at,
  last_seen_at,
  resolved_at
from orloi.data_changelog_alerts
where sync_id = :sync_id
  and window_start < :window_end
  and window_end > :window_start
order by
  case when status = 'open' then 0 else 1 end,
  last_seen_at desc
limit 100;

Expected output: at most 100 alerts with their stored analysis window and current status. last_seen_at is the most recent observation, not necessarily the end of the analysis window.

Report metadata

select
  report_key,
  cadence,
  timezone,
  window_start,
  window_end,
  status,
  model,
  prompt_version,
  compacted_event_count,
  input_token_count,
  output_token_count,
  total_token_count,
  estimated_cost_usd,
  created_at,
  updated_at,
  error_message,
  report_text is not null as has_report_text,
  report_structured is not null as has_report_structured
from orloi.data_changelog_reports
where sync_id = :sync_id
  and window_end >= :window_start
  and window_end < :window_end
order by window_end desc, created_at desc
limit 50;

Expected output: at most 50 report metadata rows whose window_end falls in the requested half-open range. This is a report-completion lookup, not general interval-overlap logic; use the alert predicate above when the question is whether two time ranges overlap.

Avoid these patterns

Do not run unbounded scans or dumps:

select * from orloi.data_changelog_raw_events;
select payload from orloi.data_changelog_raw_events;
select report_text, report_structured from orloi.data_changelog_reports;
select profile_json from orloi.schema_intelligence_profiles;

Always introspect the live database first. This page is a guide for safe access, not a complete schema reference.