Audit Log + Compliance Reporting

Category: Security & Compliance · 📦 1 install

Get this pack →

This page is generated from the Air Pipe marketplace. Browse it live to install into your organization.

Append-only audit log and compliance reporting API. Record every significant action in your system — logins, data access, mutations, exports, config changes — search them flexibly, generate compliance summaries, and manage retention. No extra services needed: it's all Postgres.

Pairs naturally with the GDPR Export + Deletion pack.

---

What's included

File	Purpose
config.yml	AirPipe config — all endpoints
schema.sql	Append-only audit_events table + indexes

---

Endpoints

Method	Path	Description
POST	/audit/seed	Create table + load 15 sample events
POST	/audit/log	Append a single audit event
POST	/audit/events/search	Search events by any combination of filters
GET	/audit/events/recent	100 most recent events
POST	/audit/report	Compliance summary for a date range
POST	/audit/retention/preview	Preview how many events a purge would delete
POST	/audit/retention/purge	Delete events older than N days

---

Setup

1. Run the schema

psql $DATABASE_URL -f schema.sql

2. Set managed variables

Name	Value
DATABASE_URL	Postgres connection string

3. Deploy the config

Upload config.yml via the AirPipe dashboard.

4. Seed sample data

curl -X POST https://your-airpipe-host/audit/seed

---

Usage

Log an event

curl -X POST https://your-airpipe-host/audit/log \
  -H 'Content-Type: application/json' \
  -d '{
    "event_type": "record.access",
    "actor_id": "user_abc123",
    "actor_type": "user",
    "resource_type": "invoice",
    "resource_id": "inv_9876",
    "action": "read",
    "outcome": "success",
    "ip_address": "203.0.113.42",
    "metadata": { "reason": "support_lookup", "ticket_id": "TKT-001" }
  }'

Only event_type, actor_id, and action are required. Everything else is optional context.

Search events

# All events for a specific actor
curl -X POST https://your-airpipe-host/audit/events/search \
  -H 'Content-Type: application/json' \
  -d '{ "actor_id": "alice" }'

# All failures in January
curl -X POST https://your-airpipe-host/audit/events/search \
  -H 'Content-Type: application/json' \
  -d '{ "outcome": "failure", "from": "2024-01-01", "to": "2024-01-31" }'

# All access to a specific resource
curl -X POST https://your-airpipe-host/audit/events/search \
  -H 'Content-Type: application/json' \
  -d '{ "resource_type": "invoice", "resource_id": "1001" }'

All filter fields are optional. Combine any of: actor_id, resource_type, resource_id, action, outcome, from, to. Returns up to 500 results.

Generate a compliance report

curl -X POST https://your-airpipe-host/audit/report \
  -H 'Content-Type: application/json' \
  -d '{ "from": "2024-01-01", "to": "2024-01-31" }'

Response:

{
  "summary": {
    "total_events": 342,
    "successful": 329,
    "failed": 8,
    "denied": 5,
    "unique_actors": 12,
    "period_start": "2024-01-02T09:14:00Z",
    "period_end": "2024-01-31T17:52:00Z"
  },
  "top_actors": [
    { "actor_id": "alice", "actor_type": "user", "event_count": 98 }
  ],
  "top_resources": [
    { "resource_type": "invoice", "resource_id": "1001", "access_count": 14 }
  ],
  "failures": [
    { "id": 42, "event_type": "user.login", "actor_id": "mallory", "outcome": "denied", ... }
  ]
}

Omit from and to to report on all events.

---

Common audit questions

"Who accessed this record in the last 30 days?"

curl -X POST https://your-airpipe-host/audit/events/search \
  -d '{ "resource_type": "invoice", "resource_id": "inv_9876", "from": "2024-01-01" }'

"Show me all failed logins this week"

curl -X POST https://your-airpipe-host/audit/events/search \
  -d '{ "event_type": "user.login", "outcome": "failure", "from": "2024-01-08" }'

"What did this user do last month?"

curl -X POST https://your-airpipe-host/audit/events/search \
  -d '{ "actor_id": "alice", "from": "2024-01-01", "to": "2024-01-31" }'

"What data exports happened this quarter?"

curl -X POST https://your-airpipe-host/audit/events/search \
  -d '{ "action": "export", "from": "2024-01-01", "to": "2024-03-31" }'

---

Retention management

Check what would be deleted (safe — no changes)

curl -X POST https://your-airpipe-host/audit/retention/preview \
  -H 'Content-Type: application/json' \
  -d '{ "older_than_days": 365 }'

Response:

{
  "would_delete": 4821,
  "oldest_event": "2020-01-15T08:12:00Z",
  "cutoff_date": "2023-01-15T00:00:00Z"
}

Purge old events (irreversible)

curl -X POST https://your-airpipe-host/audit/retention/purge \
  -H 'Content-Type: application/json' \
  -d '{ "older_than_days": 365 }'

Run the preview first. Common retention windows by regulation:

Regulation	Minimum retention
GDPR (EU)	No fixed minimum — align with data minimisation principle
SOC 2	1 year
PCI DSS	1 year (online) + 3 years (archive)
HIPAA	6 years

Schedule the purge via the AirPipe scheduler or an external cron to automate retention enforcement.

---

Enforcing append-only at the database level

By default the table is append-only by convention — the AirPipe config never issues UPDATE or DELETE on audit_events. For stricter compliance postures, add Postgres rules to make it physically impossible:

CREATE RULE no_update_audit AS ON UPDATE TO audit_events DO INSTEAD NOTHING;
CREATE RULE no_delete_audit AS ON DELETE TO audit_events DO INSTEAD NOTHING;

If you add these rules, the retention/purge endpoint will silently delete nothing. Remove the rules first, run the purge, then re-add them — or use a separate privileged DB user for retention operations.

---

Integrating from your application

Call POST /audit/log from wherever a significant action occurs. Common integration points:

// After a successful login
await fetch('https://your-airpipe-host/audit/log', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    event_type: 'user.login',
    actor_id: userId,
    action: 'login',
    outcome: 'success',
    ip_address: req.ip,
    user_agent: req.headers['user-agent'],
  }),
})

// After accessing sensitive data
await fetch('https://your-airpipe-host/audit/log', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    event_type: 'record.access',
    actor_id: userId,
    resource_type: 'medical_record',
    resource_id: recordId,
    action: 'read',
    outcome: 'success',
    metadata: { accessed_from: 'patient_portal' },
  }),
})

---

Notes

• The metadata JSONB column accepts any structured context — use it for things that don't fit the standard fields (ticket IDs, feature flags, request correlation IDs).
• The outcome field has a CHECK constraint: success, failure, or denied. Log both successes and failures — only logging failures makes it impossible to establish a normal baseline.
• Indexes are optimised for the most common query patterns: by actor, by resource, by event type, and by time. The partial index on outcome keeps failure queries fast without inflating the main index.
• actor_type distinguishes human users from API keys and automated systems, which is important for access reviews.

Configuration

config.yml

name: AuditLog
description: >
  Append-only audit log and compliance reporting API. Record every significant
  action in your system, search by actor or resource, generate compliance
  summaries, and manage retention — all in Postgres with no extra services.

docs: true

# Required managed variables:
#   DATABASE_URL — Postgres connection string

global:
  databases:
    main:
      driver: postgres
      conn_string: "a|ap_var::DATABASE_URL|"

interfaces:

  # POST /audit/seed
  # Creates the audit_events table (idempotent) and loads sample events.
  # Safe to re-run — truncates before inserting.
  audit/seed:
    output: http
    method: POST

    actions:
      - name: CreateTable
        database: main
        hide_data_on_success: true
        query: |
          CREATE TABLE IF NOT EXISTS audit_events (
            id            BIGSERIAL    PRIMARY KEY,
            event_type    TEXT         NOT NULL,
            actor_id      TEXT         NOT NULL,
            actor_type    TEXT         NOT NULL DEFAULT 'user',
            resource_type TEXT,
            resource_id   TEXT,
            action        TEXT         NOT NULL,
            outcome       TEXT         NOT NULL DEFAULT 'success'
                            CHECK (outcome IN ('success', 'failure', 'denied')),
            ip_address    TEXT,
            user_agent    TEXT,
            metadata      JSONB,
            occurred_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
          );

          CREATE INDEX IF NOT EXISTS idx_audit_actor
            ON audit_events (actor_id, occurred_at DESC);
          CREATE INDEX IF NOT EXISTS idx_audit_resource
            ON audit_events (resource_type, resource_id, occurred_at DESC);
          CREATE INDEX IF NOT EXISTS idx_audit_event_type
            ON audit_events (event_type, occurred_at DESC);
          CREATE INDEX IF NOT EXISTS idx_audit_occurred_at
            ON audit_events (occurred_at DESC);
          CREATE INDEX IF NOT EXISTS idx_audit_outcome
            ON audit_events (outcome) WHERE outcome != 'success';

      - name: TruncateEvents
        run_when_succeeded: [CreateTable]
        database: main
        hide_data_on_success: true
        query: |
          TRUNCATE audit_events RESTART IDENTITY;

      - name: InsertSampleEvents
        run_when_succeeded: [TruncateEvents]
        database: main
        hide_data_on_success: true
        query: |
          INSERT INTO audit_events (event_type, actor_id, actor_type, resource_type, resource_id, action, outcome, ip_address, occurred_at) VALUES
            ('user.login',      'alice',     'user',    NULL,       NULL,   'login',   'success', '203.0.113.1',  NOW() - INTERVAL '30 days'),
            ('user.login',      'bob',       'user',    NULL,       NULL,   'login',   'success', '203.0.113.2',  NOW() - INTERVAL '28 days'),
            ('record.access',   'alice',     'user',    'invoice',  '1001', 'read',    'success', '203.0.113.1',  NOW() - INTERVAL '25 days'),
            ('record.access',   'alice',     'user',    'invoice',  '1002', 'read',    'success', '203.0.113.1',  NOW() - INTERVAL '20 days'),
            ('record.update',   'alice',     'user',    'invoice',  '1001', 'update',  'success', '203.0.113.1',  NOW() - INTERVAL '18 days'),
            ('user.login',      'mallory',   'user',    NULL,       NULL,   'login',   'failure', '198.51.100.9', NOW() - INTERVAL '15 days'),
            ('user.login',      'mallory',   'user',    NULL,       NULL,   'login',   'failure', '198.51.100.9', NOW() - INTERVAL '15 days'),
            ('user.login',      'mallory',   'user',    NULL,       NULL,   'login',   'denied',  '198.51.100.9', NOW() - INTERVAL '15 days'),
            ('data.export',     'alice',     'user',    'report',   'q4',   'export',  'success', '203.0.113.1',  NOW() - INTERVAL '10 days'),
            ('record.delete',   'bob',       'user',    'invoice',  '1003', 'delete',  'success', '203.0.113.2',  NOW() - INTERVAL '7 days'),
            ('api_key.create',  'carol',     'user',    'api_key',  'k001', 'create',  'success', '203.0.113.3',  NOW() - INTERVAL '5 days'),
            ('record.access',   'k001',      'api_key', 'invoice',  '1004', 'read',    'success', '203.0.113.10', NOW() - INTERVAL '3 days'),
            ('record.access',   'k001',      'api_key', 'invoice',  '1005', 'read',    'success', '203.0.113.10', NOW() - INTERVAL '2 days'),
            ('user.login',      'carol',     'user',    NULL,       NULL,   'login',   'success', '203.0.113.3',  NOW() - INTERVAL '1 day'),
            ('data.export',     'carol',     'user',    'report',   'q1',   'export',  'success', '203.0.113.3',  NOW() - INTERVAL '2 hours');

      - name: SeedSummary
        run_when_succeeded: [InsertSampleEvents]
        database: main
        query: |
          SELECT COUNT(*) AS events_seeded FROM audit_events;
        post_transforms:
          - extract_value: "[0]"

  # POST /audit/log
  # Record a single audit event. Call this from your application on any
  # significant action: logins, data access, mutations, exports, config changes.
  # Body: { "event_type": "...", "actor_id": "...", "action": "...", ... }
  audit/log:
    output: http
    method: POST
    summary: Log event
    description: >
      Append a single audit event. Only event_type, actor_id, and action are
      required; all other fields are optional context.
    tags: [audit]
    request_example:
      event_type: record.access
      actor_id: user_abc123
      actor_type: user
      resource_type: invoice
      resource_id: "inv_9876"
      action: read
      outcome: success
      ip_address: "203.0.113.42"
      metadata:
        reason: support_ticket_lookup
        ticket_id: "TKT-001"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: event_type
              is_not_null: true
              is_not_empty: true
              description: "Event type (e.g. 'user.login', 'record.access', 'data.export')"
            - value: actor_id
              is_not_null: true
              is_not_empty: true
              description: "ID of the actor performing the action"
            - value: action
              is_not_null: true
              is_not_empty: true
              description: "The action performed (e.g. 'read', 'update', 'delete', 'login')"

      - name: AppendEvent
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO audit_events (
            event_type, actor_id, actor_type,
            resource_type, resource_id, action, outcome,
            ip_address, user_agent, metadata
          )
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10::jsonb)
          RETURNING id, event_type, actor_id, action, outcome, occurred_at;
        params:
          - a|ValidateBody::event_type|
          - a|ValidateBody::actor_id|
          - a|ValidateBody::actor_type|
          - a|ValidateBody::resource_type|
          - a|ValidateBody::resource_id|
          - a|ValidateBody::action|
          - a|ValidateBody::outcome|
          - a|ValidateBody::ip_address|
          - a|ValidateBody::user_agent|
          - a|ValidateBody::metadata|
        post_transforms:
          - extract_value: "[0]"

      - name: TrackEvent
        run_when_succeeded: [AppendEvent]
        hide_data_on_success: true
        emit_metric:
          name: app_audit_events_total
          type: counter
          labels:
            event_type: a|ValidateBody::event_type|
            outcome: a|ValidateBody::outcome|

  # POST /audit/events/search
  # Search audit events with flexible filters. All filters are optional.
  # Body: { "actor_id": "...", "resource_type": "...", "action": "...",
  #         "outcome": "...", "from": "2024-01-01", "to": "2024-01-31" }
  audit/events/search:
    output: http
    method: POST
    summary: Search events
    description: >
      Search audit events by any combination of actor, resource type, action,
      outcome, and date range. All filters are optional — omit any to broaden
      the search. Returns up to 500 events, most recent first.
    tags: [audit]
    request_example:
      actor_id: alice
    response_example:
      - id: 3
        event_type: record.access
        actor_id: alice
        actor_type: user
        resource_type: invoice
        resource_id: "1001"
        action: read
        outcome: success
        ip_address: "203.0.113.1"
        occurred_at: "2024-01-20T10:00:00Z"

    actions:
      - name: ReadFilters
        input: a|body|
        hide_data_on_success: true

      # All filter fields are optional. Missing JSON keys return SQL NULL so the
      # IS NULL guard correctly skips the condition — unlike direct interpolation
      # which resolves to a literal string when a field is absent.
      - name: SearchEvents
        run_when_succeeded: [ReadFilters]
        database: main
        query: |
          SELECT
            id, event_type, actor_id, actor_type,
            resource_type, resource_id, action, outcome,
            ip_address, metadata, occurred_at
          FROM audit_events
          WHERE
            ($1::jsonb->>'actor_id'      IS NULL OR actor_id      = $1::jsonb->>'actor_id')
            AND ($1::jsonb->>'resource_type' IS NULL OR resource_type = $1::jsonb->>'resource_type')
            AND ($1::jsonb->>'resource_id'   IS NULL OR resource_id   = $1::jsonb->>'resource_id')
            AND ($1::jsonb->>'action'        IS NULL OR action        = $1::jsonb->>'action')
            AND ($1::jsonb->>'outcome'       IS NULL OR outcome       = $1::jsonb->>'outcome')
            AND ($1::jsonb->>'from' IS NULL OR ($1::jsonb->>'from') !~ '^\d{4}-\d{2}-\d{2}'
                 OR occurred_at >= ($1::jsonb->>'from')::timestamptz)
            AND ($1::jsonb->>'to'   IS NULL OR ($1::jsonb->>'to')   !~ '^\d{4}-\d{2}-\d{2}'
                 OR occurred_at <= ($1::jsonb->>'to')::timestamptz + INTERVAL '1 day')
          ORDER BY occurred_at DESC
          LIMIT 500;
        params:
          - a|ReadFilters|

  # GET /audit/events/recent
  # The 100 most recent audit events across all actors. Useful for a live feed.
  audit/events/recent:
    output: http
    summary: Recent events
    description: Returns the 100 most recent audit events across all actors.
    tags: [audit]
    response_example:
      - id: 15
        event_type: data.export
        actor_id: carol
        action: export
        outcome: success
        occurred_at: "2024-01-15T06:00:00Z"

    actions:
      - name: ListRecent
        database: main
        query: |
          SELECT
            id, event_type, actor_id, actor_type,
            resource_type, resource_id, action, outcome,
            ip_address, occurred_at
          FROM audit_events
          ORDER BY occurred_at DESC
          LIMIT 100;

  # POST /audit/report
  # Generate a compliance summary for a date range.
  # Answers common audit questions: who is most active, what failed,
  # which resources were accessed most.
  # Body: { "from": "2024-01-01", "to": "2024-01-31" }
  audit/report:
    output: http
    method: POST
    summary: Compliance report
    description: >
      Generate a compliance summary for a specified date range: total events,
      breakdown by outcome, most active actors, most accessed resources, and
      all failed or denied actions. Suitable for periodic compliance reviews.
    tags: [audit, compliance]
    request_example:
      from: "2024-01-01"
      to: "2024-01-31"
    notes: |
      Omit `from` and `to` to report on all events.

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true

      - name: Summary
        run_when_succeeded: [ValidateBody]
        database: main
        query: |
          SELECT
            COUNT(*) AS total_events,
            COUNT(*) FILTER (WHERE outcome = 'success') AS successful,
            COUNT(*) FILTER (WHERE outcome = 'failure') AS failed,
            COUNT(*) FILTER (WHERE outcome = 'denied')  AS denied,
            COUNT(DISTINCT actor_id)                    AS unique_actors,
            MIN(occurred_at)                            AS period_start,
            MAX(occurred_at)                            AS period_end
          FROM audit_events
          WHERE
            ($1::jsonb->>'from' IS NULL OR ($1::jsonb->>'from') !~ '^\d{4}-\d{2}-\d{2}'
             OR occurred_at >= ($1::jsonb->>'from')::timestamptz)
            AND ($1::jsonb->>'to' IS NULL OR ($1::jsonb->>'to') !~ '^\d{4}-\d{2}-\d{2}'
             OR occurred_at <= ($1::jsonb->>'to')::timestamptz + INTERVAL '1 day');
        params:
          - a|ValidateBody|
        post_transforms:
          - extract_value: "[0]"

      - name: TopActors
        run_when_succeeded: [ValidateBody]
        database: main
        query: |
          SELECT actor_id, actor_type, COUNT(*) AS event_count
          FROM audit_events
          WHERE
            ($1::jsonb->>'from' IS NULL OR ($1::jsonb->>'from') !~ '^\d{4}-\d{2}-\d{2}'
             OR occurred_at >= ($1::jsonb->>'from')::timestamptz)
            AND ($1::jsonb->>'to' IS NULL OR ($1::jsonb->>'to') !~ '^\d{4}-\d{2}-\d{2}'
             OR occurred_at <= ($1::jsonb->>'to')::timestamptz + INTERVAL '1 day')
          GROUP BY actor_id, actor_type
          ORDER BY event_count DESC
          LIMIT 10;
        params:
          - a|ValidateBody|

      - name: TopResources
        run_when_succeeded: [ValidateBody]
        database: main
        query: |
          SELECT resource_type, resource_id, COUNT(*) AS access_count
          FROM audit_events
          WHERE
            resource_type IS NOT NULL
            AND ($1::jsonb->>'from' IS NULL OR ($1::jsonb->>'from') !~ '^\d{4}-\d{2}-\d{2}'
                 OR occurred_at >= ($1::jsonb->>'from')::timestamptz)
            AND ($1::jsonb->>'to' IS NULL OR ($1::jsonb->>'to') !~ '^\d{4}-\d{2}-\d{2}'
                 OR occurred_at <= ($1::jsonb->>'to')::timestamptz + INTERVAL '1 day')
          GROUP BY resource_type, resource_id
          ORDER BY access_count DESC
          LIMIT 10;
        params:
          - a|ValidateBody|

      - name: Failures
        run_when_succeeded: [ValidateBody]
        database: main
        query: |
          SELECT
            id, event_type, actor_id, resource_type, resource_id,
            action, outcome, ip_address, occurred_at
          FROM audit_events
          WHERE
            outcome IN ('failure', 'denied')
            AND ($1::jsonb->>'from' IS NULL OR ($1::jsonb->>'from') !~ '^\d{4}-\d{2}-\d{2}'
                 OR occurred_at >= ($1::jsonb->>'from')::timestamptz)
            AND ($1::jsonb->>'to' IS NULL OR ($1::jsonb->>'to') !~ '^\d{4}-\d{2}-\d{2}'
                 OR occurred_at <= ($1::jsonb->>'to')::timestamptz + INTERVAL '1 day')
          ORDER BY occurred_at DESC
          LIMIT 200;
        params:
          - a|ValidateBody|

      - name: BuildReport
        run_when_succeeded: [Summary, TopActors, TopResources, Failures]
        database: main
        query: |
          SELECT
            $1::jsonb AS summary,
            $2::jsonb AS top_actors,
            $3::jsonb AS top_resources,
            $4::jsonb AS failures;
        params:
          - a|Summary|
          - a|TopActors|
          - a|TopResources|
          - a|Failures|
        post_transforms:
          - extract_value: "[0]"

  # POST /audit/retention/purge
  # Permanently delete events older than the specified number of days.
  # This is the only destructive operation in the pack. Call it on a schedule
  # to enforce your data retention policy.
  # Body: { "older_than_days": 365 }
  audit/retention/purge:
    output: http
    method: POST
    summary: Purge old events
    description: >
      Permanently delete audit events older than the specified number of days.
      Use this to enforce your data retention policy. Default is 365 days if
      not specified.
    tags: [audit, compliance]
    request_example:
      older_than_days: 365
    notes: |
      This is irreversible. Run `POST /audit/retention/preview` first to see
      how many events would be deleted.

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: older_than_days
              is_not_null: true
              is_greater_than: 0
              description: "Retention window in days (must be positive)"

      - name: PurgeEvents
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          WITH deleted AS (
            DELETE FROM audit_events
            WHERE occurred_at < NOW() - ($1 || ' days')::interval
            RETURNING id
          )
          SELECT COUNT(*) AS events_deleted FROM deleted;
        params:
          - a|ValidateBody::older_than_days|
        post_transforms:
          - extract_value: "[0]"

      - name: PurgeSummary
        run_when_succeeded: [PurgeEvents]
        database: main
        query: |
          SELECT
            $1::int                             AS events_deleted,
            (SELECT COUNT(*) FROM audit_events) AS events_remaining;
        params:
          - a|PurgeEvents::events_deleted|
        post_transforms:
          - extract_value: "[0]"

  # POST /audit/retention/preview
  # Preview how many events would be deleted by a purge without deleting anything.
  # Body: { "older_than_days": 365 }
  audit/retention/preview:
    output: http
    method: POST
    summary: Preview purge
    description: >
      Returns a count of how many events would be deleted by a retention purge,
      without actually deleting anything. Run this before purging.
    tags: [audit, compliance]
    request_example:
      older_than_days: 365
    response_example:
      would_delete: 4821
      oldest_event: "2020-01-15T08:00:00Z"
      cutoff_date: "2023-01-15T00:00:00Z"

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: older_than_days
              is_not_null: true
              is_greater_than: 0
              description: "Retention window in days"

      - name: PreviewPurge
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT
            COUNT(*)    AS would_delete,
            MIN(occurred_at) AS oldest_event,
            NOW() - ($1 || ' days')::interval AS cutoff_date
          FROM audit_events
          WHERE occurred_at < NOW() - ($1 || ' days')::interval;
        params:
          - a|ValidateBody::older_than_days|
        post_transforms:
          - extract_value: "[0]"

schema.sql

CREATE TABLE IF NOT EXISTS audit_events (
  id            BIGSERIAL    PRIMARY KEY,
  event_type    TEXT         NOT NULL,   -- e.g. 'user.login', 'record.access', 'data.export'
  actor_id      TEXT         NOT NULL,   -- who performed the action (user ID, API key name, service)
  actor_type    TEXT         NOT NULL DEFAULT 'user',  -- 'user', 'api_key', 'system'
  resource_type TEXT,                    -- what was acted on (e.g. 'invoice', 'user', 'report')
  resource_id   TEXT,                    -- ID of the affected resource
  action        TEXT         NOT NULL,   -- 'create', 'read', 'update', 'delete', 'export', 'login'
  outcome       TEXT         NOT NULL DEFAULT 'success' CHECK (outcome IN ('success', 'failure', 'denied')),
  ip_address    TEXT,
  user_agent    TEXT,
  metadata      JSONB,                   -- any extra structured context
  occurred_at   TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

-- Append-only: no UPDATE or DELETE on this table.
-- Enforce at the DB level with a rule if your compliance posture requires it:
--   CREATE RULE no_update_audit AS ON UPDATE TO audit_events DO INSTEAD NOTHING;
--   CREATE RULE no_delete_audit AS ON DELETE TO audit_events DO INSTEAD NOTHING;

CREATE INDEX IF NOT EXISTS idx_audit_actor       ON audit_events (actor_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_resource    ON audit_events (resource_type, resource_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_event_type  ON audit_events (event_type, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_occurred_at ON audit_events (occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_outcome     ON audit_events (outcome) WHERE outcome != 'success';