Custom export and deletion with SQL

Use this optional procedure when the Raw event retention page does not provide the export or deletion control you need. Most users should use the Retention page in Orloi.

This workflow is for the current external Postgres layout: orloi.data_changelog_raw_events has an immutable bigserial id; orloi.data_changelog_state records the compaction watermark as state.last_raw_event_id; and orloi.data_changelog_metric_value_events links metric values to raw events. Compacted events retain raw-event IDs as provenance, but do not provide a foreign-key cascade to raw events.

WARNING

Do not export old rows and then run the same date-based delete query. A new event can arrive after the export but still look old because its event time is earlier than your cutoff. If you run the query again, you could delete that new event without exporting it first. First save the exact rows you plan to delete, export and verify that list, then delete only those saved rows by ID.

Before you start

• Run this procedure with a role that can read and delete the orloi schema. Perform it in a controlled session, using UTC timestamps.
• Set the cutoff in UTC, for example 2026-05-01T00:00:00Z. Do not use an unqualified local timestamp.
• Pause and resolve any legal hold, investigation, or contractual retention requirement before choosing a cutoff. Orloi does not model legal holds or prevent a database administrator from deleting held data.
• Store exports in an access-controlled, encrypted location. The database connection or export command alone does not encrypt the resulting file at rest.
• Do not begin a purge until you have a tested provider backup or point-in-time recovery plan. An exported JSONL file is not a supported raw-event restore mechanism.

Schema and version check

Verify that your installation has the expected tables and compaction watermark before continuing. Older Orloi installations, manually modified schemas, or a failed upgrade can differ. If this query does not return all three tables and a numeric watermark, stop and upgrade or investigate the database before writing a custom deletion script.

SELECT
  to_regclass('orloi.data_changelog_raw_events') AS raw_events_table,
  to_regclass('orloi.data_changelog_state') AS state_table,
  to_regclass('orloi.data_changelog_metric_value_events') AS metric_provenance_table,
  (
    SELECT nullif(state->>'last_raw_event_id', '')::bigint
    FROM orloi.data_changelog_state
    WHERE sync_id = '<engine_sync_id>'
      AND component = 'compaction'
    LIMIT 1
  ) AS compaction_last_raw_event_id;

The compaction watermark is a safety boundary: it excludes raw events that compaction has not yet processed. Do not delete beyond it. This procedure removes metric-to-raw-event links before raw rows; it intentionally leaves compacted events, metric values, reports, and saved summaries in place. Their historical provenance may still name deleted raw IDs, but the raw detail is no longer available.

Pin the row set

Work with three values and record them with the archive: the engine sync_id, a UTC cutoff, and the resulting max_id. id is the pinned upper boundary. Later inserts have higher IDs, even if their event timestamps are older than the cutoff, so they cannot enter this retention run.

First obtain the compaction watermark from the preceding check. Then run this snapshot query once and save every returned value. Replace the placeholders with literal values; do not reuse a relative expression such as now() - interval '90 days' in later queries.

WITH candidate_rows AS (
  SELECT id, event_timestamp
  FROM orloi.data_changelog_raw_events
  WHERE sync_id = '<engine_sync_id>'
    AND event_timestamp < '<cutoff_utc>'::timestamptz
    AND id <= <compaction_last_raw_event_id>
)
SELECT
  count(*) AS row_count,
  min(id) AS min_id,
  max(id) AS max_id,
  min(event_timestamp) AS oldest_event_timestamp,
  max(event_timestamp) AS newest_event_timestamp
FROM candidate_rows;

If row_count is zero, there is nothing to archive. Otherwise, replace <snapshot_max_id> in every remaining query with the returned max_id. The immutable selection is:

sync_id = '<engine_sync_id>'
AND event_timestamp < '<cutoff_utc>'::timestamptz
AND id <= <snapshot_max_id>
AND id <= <compaction_last_raw_event_id>

Keep both ID bounds. The second makes the safety boundary explicit; the first makes the set stable if more compaction happens while you export and delete.

Export and verify

Export with the exact pinned selection and a deterministic order. This example produces one JSON object per line through psql; use a controlled machine and an encrypted destination.

\pset tuples_only on
\pset format unaligned
\o orloi-raw-events-<engine_sync_id>-<cutoff_utc>.jsonl
SELECT row_to_json(raw_event)
FROM (
  SELECT *
  FROM orloi.data_changelog_raw_events
  WHERE sync_id = '<engine_sync_id>'
    AND event_timestamp < '<cutoff_utc>'::timestamptz
    AND id <= <snapshot_max_id>
    AND id <= <compaction_last_raw_event_id>
  ORDER BY id
) AS raw_event;
\o

Record the archive's SHA-256 hash with the five snapshot values. On macOS, for example:

shasum -a 256 orloi-raw-events-<engine_sync_id>-<cutoff_utc>.jsonl
wc -l orloi-raw-events-<engine_sync_id>-<cutoff_utc>.jsonl

The line count must equal row_count. Before deleting, repeat the count using the same pinned selection; it must still equal row_count. If it does not, stop. Do not broaden the selection or replace snapshot_max_id—investigate the discrepancy and create a new snapshot if needed.

SELECT count(*) AS pinned_row_count
FROM orloi.data_changelog_raw_events
WHERE sync_id = '<engine_sync_id>'
  AND event_timestamp < '<cutoff_utc>'::timestamptz
  AND id <= <snapshot_max_id>
  AND id <= <compaction_last_raw_event_id>;

Delete in bounded transactions

Delete a small, ordered batch at a time. The current Orloi cleanup worker uses batches of 5,000 rows; start at or below that number and reduce it if your provider reports lock, timeout, or load pressure. Run the transaction repeatedly, always with the same sync_id, cutoff, and two ID bounds. Do not replace the predicate with a fresh timestamp-only query.

BEGIN;

WITH raw_events_to_delete AS (
  SELECT id
  FROM orloi.data_changelog_raw_events
  WHERE sync_id = '<engine_sync_id>'
    AND event_timestamp < '<cutoff_utc>'::timestamptz
    AND id <= <snapshot_max_id>
    AND id <= <compaction_last_raw_event_id>
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
),
deleted_metric_provenance AS (
  DELETE FROM orloi.data_changelog_metric_value_events
  WHERE raw_event_id IN (SELECT id FROM raw_events_to_delete)
  RETURNING raw_event_id
),
deleted_raw_events AS (
  DELETE FROM orloi.data_changelog_raw_events
  WHERE id IN (SELECT id FROM raw_events_to_delete)
  RETURNING id
)
SELECT
  (SELECT count(*) FROM deleted_raw_events) AS deleted_raw_event_count,
  (SELECT count(*) FROM deleted_metric_provenance) AS deleted_metric_provenance_count;

COMMIT;

Run the pinned count query after every batch or a small group of batches. Continue until it returns zero. Because SKIP LOCKED can temporarily omit a row held by another transaction, a zero-row batch alone is not proof that the purge is complete.

After completion, save the final zero count, total deleted counts, archive hash, UTC cutoff, snapshot maximum ID, and operator/time in the retention record. The transaction's two returned counts are the exact evidence for raw-row and metric-provenance deletion; retain the pre-delete and per-batch counts for audit.

Recovery and maintenance

Do a restore exercise before relying on this process for an important retention boundary. Restore a provider backup or point-in-time snapshot into an isolated database, verify that the raw rows and dependent metric links are present there, and rehearse the investigation you would need to perform. Do not restore an archive into production by inserting the JSONL rows: Orloi has no documented raw-event import API, and uniqueness, schema, and payload-format assumptions are version-specific.

Normal Postgres autovacuum is usually sufficient. If your provider recommends it after a large purge, run ordinary VACUUM (ANALYZE) during a low-traffic period; it can run alongside normal reads and writes. Do not use VACUUM FULL as routine retention maintenance—it requires an exclusive lock and can interrupt the product. Provider-specific storage reclamation, backup retention, encryption, and recovery settings remain the database owner's responsibility.

What remains available

Compacted events, metric values and history charts, reports, and saved process/activity summaries remain available. They can still describe historical trends, but they are no longer enough to fully recompute or investigate deleted periods from raw source detail.