Migrate from Ad Hoc Logs to Structured Events and SQL

Free-form logs are useful for local debugging, but recurring operational and product questions need stable fields, explicit units, and reviewable definitions. A migration does not need to replace every existing log or observability tool. Start with one production workflow, emit one bounded completion event beside the existing logs, and prove that its SQL answers the intended question before changing dashboards or alerts.

This guide uses an API request as the example, but the same sequence applies to jobs, webhooks, AI runs, billing workflows, and application-level database operations.

1. Choose one decision, not an entire log stream

Begin with a question that already consumes engineering time:

• Which API routes have a meaningful 5xx rate?
• Which rate-limited requests recover after a retry?
• Which job types are building queue age?
• Which prompt version produces fewer accepted outcomes?
• Which database operation fingerprint repeats inside one request?

Write down the decision the answer will support, the owner, the reporting window, and the minimum volume needed to interpret a rate. This prevents an event from becoming a copy of every value available in application memory.

Keep diagnostic logs for stack traces or local context when they remain useful. The structured event is the durable analytical contract for the selected question.

2. Inventory the current meaning

Before changing instrumentation, save the existing search or dashboard definition and inspect several real outcomes. Record:

1. Which messages or attributes identify the workflow.
2. How success, retry, cancellation, and terminal failure are distinguished.
3. Which timestamp marks the beginning or completion of the work.
4. Whether retries create extra records.
5. Which fields contain secrets, personal data, raw payloads, or unbounded text.
6. Which exclusions and minimum-volume rules exist only in an operator's memory.

This inventory is a semantic baseline, not a promise that the old result is correct. If the existing search mixes attempts with logical requests, document that limitation instead of reproducing it silently.

3. Define one bounded completion event

Prefer one event for a completed unit of work. Use explicit numbers, booleans, units, and controlled categories. Use a route template instead of a raw URL, a categorized error instead of unrestricted exception text, and an internal identifier only when it is required for correlation.

{
  "event_name": "api_request_completed",
  "request_id": "req_7d91",
  "route_template": "/api/projects/:id/sync",
  "method": "POST",
  "status_code": 503,
  "outcome": "dependency_failed",
  "latency_ms": 842,
  "attempt_number": 2,
  "release": "2026.07.2",
  "environment": "production",
  "schema_version": 1
}

Do not send authorization headers, cookies, request bodies, connection strings, raw prompts, webhook payloads, payment details, or unrestricted customer content. Review the field allowlist before deployment. See Redacting Sensitive Data and High-Cardinality Fields.

4. Emit beside the existing logs

Run a time-bounded dual-write period. The application continues to produce the diagnostic records your team relies on while also emitting the new event. Add instrumentation at a central boundary—middleware, a job wrapper, a webhook dispatcher, or a database client wrapper—so success and failure paths use the same clock and field names.

Instrumentation must not turn an application success into a failure. Treat analytics delivery as a separate, bounded operation with an explicit timeout and the retry behavior appropriate for your system. Verify that missing configuration falls back safely in every deployed environment before enforcing it.

During dual write, monitor event ingestion freshness, required-field completeness, schema versions, and duplicate identifiers. The event ingestion freshness, required-field null rate, and duplicate event ID recipes provide reusable checks.

5. Translate the question into reviewed SQL

Start from the event contract rather than transliterating a text-search expression. For API errors, retain both the count and denominator:

SELECT
  route_template,
  COUNT(*) AS requests,
  SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS errors,
  100.0 * SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS error_rate_pct
FROM api_request_completed
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
  AND environment = 'production'
GROUP BY route_template
HAVING COUNT(*) >= 20
ORDER BY error_rate_pct DESC;

Review the time boundary, status definition, retry grain, late-event treatment, and minimum volume. Test at least one success, expected failure, retry, duplicate, null, and delayed event. If the query becomes operationally important, store a deterministic fixture and expected result beside it.

The SQL recipe library includes typed schemas, read-only DataFusion SQL, synthetic output, visualizations, edge cases, dashboard plans, and alert guidance. The browser SQL playground runs supported fixtures locally without sending the sample rows to Telemetry.

6. Compare semantics, not just totals

Run the old and new answers over the same closed UTC window. Investigate differences instead of targeting an arbitrary exact match:

• A lower new count may mean retries are correctly collapsed.
• A higher count may expose failures omitted by a message-pattern search.
• Different route rankings may result from stable route templates replacing raw URLs.
• A small recent mismatch may be caused by delayed events or an incomplete time bucket.
• Historical data may not contain fields introduced by the new contract.

Create a short reconciliation record for each difference: cause, accepted behavior, owner, and whether the event or query needs a change. Do not tune the new SQL until it reproduces an old bug.

7. Promote in stages

Move one consumer at a time:

1. Use the new SQL for an exploratory report.
2. Save the reviewed query with its owner and definition.
3. Build a dashboard that keeps volume beside rates and uses complete buckets.
4. Run any proposed alert in a non-paging or shadow mode.
5. Add a sustained-duration rule, minimum volume, and response link.
6. Retire the old consumer only after the new one survives the agreed validation window.

A dashboard cutover is reversible. Deleting old data, removing diagnostic logs, or disabling an established alert may not be. Keep those as separate decisions with their own retention and rollback review.

8. Keep an explicit rollback path

Before cutover, record:

• The previous search, dashboard, and alert identifiers.
• The release that introduced the event.
• The event and schema version.
• The new saved-query identifiers.
• The criteria for returning a consumer to the previous definition.
• The date when dual writing and extra validation can end.

If the new event loses required fields, becomes delayed, or changes meaning, restore the affected consumer while continuing to diagnose the producer. A rollback should not require removing the new instrumentation during the same incident.

What this migration does not cover

This workflow does not claim to replace native host metrics, database server statistics, distributed traces, or unrestricted diagnostic logs. Telemetry's database patterns, for example, analyze application-emitted database telemetry such as safe query fingerprints, pool waits, transaction outcomes, lock observations, replication signals, and migration results. They are not a pg_stat_* collector.

Use each signal for the question it can answer, and connect systems with stable identifiers only where the operational value justifies the data and cardinality cost.

A practical first migration

For an API service, start with API request throughput, API error rate by route, and API 429 recovery. They share a small event contract while answering traffic, reliability, and retry questions at different grains. For a database-backed workflow, add N+1 query detection only after request and query fingerprints can be correlated safely.

Once one workflow is stable, reuse the migration checklist for the next decision rather than expanding the original event without a question.