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.
• Run SELECT-only analysis by default.
• 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.

Session setup

Orloi tables are usually installed under the orloi schema. Verify the live schema before analysis.

set statement_timeout = '15s';
set lock_timeout = '2s';
set idle_in_transaction_session_timeout = '30s';
set search_path = orloi, public;

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

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

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.

select key, count(*) as row_count
from (
  select jsonb_object_keys(payload) as key
  from orloi.data_changelog_raw_events
  where sync_id = :sync_id
    and event_timestamp >= :window_start
    and event_timestamp < :window_end
    and payload is not null
  limit 1000
) sampled
group by key
order by row_count desc, key asc
limit 50;

Adapt this query if the discovered schema uses different table, timestamp, or payload columns.

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;

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;

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;

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;

Alert overview

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_start
  and window_end <= :window_end
order by
  case when status = 'open' then 0 else 1 end,
  last_seen_at desc
limit 100;

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;

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.