Auditing Logs

Foundation emits audit logs for every incoming event and also derives detailed logs for user API interactions and entity-level actions. These logs help with troubleshooting, governance, and analytics.

Approach

Foundation treats Audit Logs as data products. These data products have key characteristics:

  • Data Product Type: System as they are automatically generated by Foundation

  • Organization: Root

  • Sensitivity: These data products contain personal data and confidential information, but cannot be classified before the Classification and Sensitivity levels are defined, as part of the Data Governance set up.

  • Access: Only Admin roles can access them (as they are the only roles with access to the Root Organization)

  • Consumption: in order to check the logs, users can query them following the same processes as set in Querying Data inside Foundation

  • Storage: These data products are not stored in the Object Storage or using Iceberg (as the rest). They are stored in a database.

What gets logged

  • Raw event audit log

    • Data Product Name: _foundation_events_audit_log

    • Table for queries: 

      processor.public."foundation_events_audit_log"

    • One row per event (upserted by span_id)

    • Contains full serialized event, message metadata, timestamps

  • User API interaction log

    • Data Product Name: _user_api_interaction_log

    • Table for queries: 

      processor.public."user_api_interaction_log"

    • One row per API event

    • Contains HTTP method/path, query/body, response status, user/session context (IP, agent, org, roles)

  • Entity events log

    • Data Product Name: _entity_events_logs

    • Table for queries: 

      processor.public."entity_events_logs"

    • One row per entity action event

    • Contains actor user_id, event_timestamp, message_type (action), entity_id, and entity_details

Update cadence

  • Raw audit: upserted on every event by span_id

  • Detailed logs: appended on every event; created_at is set server-side

  • End-to-end latency is the event processing time (near real-time)

Common use cases

  • Troubleshooting and replay: join detailed logs to raw event for exact payloads

  • Security and compliance: who did what to which entity and when

  • API analytics: path/method usage, response codes, error rates, session/role insights

  • Change timelines: per entity_id across time

How to query

  • Recent API calls by a user (last 7 days)

SELECT
  event_timestamp,
  request_http_method,
  request_path,
  response_status,
  user_ip_address,
  x_session_id
FROM processor.public."user_api_interaction_log"
WHERE user_id = $1
  AND event_timestamp >= NOW() - INTERVAL '7 days'
ORDER BY event_timestamp DESC
LIMIT 200;

  • Entity changes over a period

SELECT
  event_timestamp,
  user_id AS actor,
  message_type AS action,
  entity_id,
  entity_details
FROM processor.public."entity_events_logs"
WHERE entity_id = $1
  AND event_timestamp BETWEEN $2 AND $3
ORDER BY event_timestamp DESC;

  • Join detailed logs with the raw event by span_id

SELECT
  feal.event_timestamp,
  feal.message_type,
  feal.message_scope,
  eelog.user_id,
  eelog.entity_id,
  feal.event  -- full JSON
FROM processor.public."foundation_events_audit_log" feal
JOIN processor.public."entity_events_logs" eelog
  ON eelog.span_id = feal.span_id
WHERE eelog.entity_id = $1
ORDER BY feal.event_timestamp DESC
LIMIT 100;

  • API error-rate per path (last 24h)

SELECT
  request_path,
  COUNT(*) FILTER (WHERE response_status >= 500) AS server_errors,
  COUNT(*) FILTER (WHERE response_status BETWEEN 400 AND 499) AS client_errors,
  COUNT(*) AS total,
  ROUND(100.0 * COUNT(*) FILTER (WHERE response_status >= 400) / NULLIF(COUNT(*),0), 2) AS error_rate_pct
FROM processor.public."user_api_interaction_log"
WHERE event_timestamp >= NOW() - INTERVAL '24 hours'
GROUP BY request_path
ORDER BY error_rate_pct DESC NULLS LAST
LIMIT 50;

  • API usage by role (if roles provided)

SELECT
  (user_roles->>'primary') AS primary_role,
  COUNT(*) AS calls
FROM processor.public."user_api_interaction_log"
WHERE event_timestamp >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY calls DESC;

  • Find slow or failing endpoints by session

SELECT
  x_session_id,
  COUNT(*) FILTER (WHERE response_status >= 500) AS server_errors,
  COUNT(*) FILTER (WHERE response_status BETWEEN 400 AND 499) AS client_errors,
  COUNT(*) AS total
FROM processor.public."user_api_interaction_log"
WHERE x_session_id IS NOT NULL
  AND event_timestamp >= NOW() - INTERVAL '7 days'
GROUP BY x_session_id
HAVING COUNT(*) FILTER (WHERE response_status >= 400) > 0
ORDER BY server_errors DESC, client_errors DESC, total DESC;
PreviousManaging Master & Reference DataNextOverview

Last updated