Event Tracking

Category: Data & Analytics · 📦 1 install

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

Lightweight product analytics backend. Track any named event with arbitrary properties, then query counts and user histories. No third-party analytics service required — your data stays in your database.

What's included

File	Purpose
`config.yml`	AirPipe config with `docs: true`
`schema.sql`	Events table with indexes

Endpoints

Method	Path	Description
`POST`	`/events/track`	Record an event
`GET`	`/events/summary`	Count by event type
`POST`	`/events/query`	Filter by type and date range
`GET`	`/events/recent`	Last 100 events
`POST`	`/events/user`	All events for a user

Setup

1. Run the schema

psql $DATABASE_URL -f schema.sql

2. Set managed variable

Name	Value
`DATABASE_URL`	your Postgres connection string

Testing

BASE=https://your-airpipe-host

# Track events
curl -X POST $BASE/events/track \
  -H "Content-Type: application/json" \
  -d '{"event_type": "signup", "user_id": "u_123", "properties": {"plan": "pro"}}'

curl -X POST $BASE/events/track \
  -H "Content-Type: application/json" \
  -d '{"event_type": "page_view", "user_id": "u_123", "properties": {"page": "/pricing"}}'

# See counts
curl $BASE/events/summary

# Get user history
curl -X POST $BASE/events/user \
  -H "Content-Type: application/json" \
  -d '{"user_id": "u_123"}'

Event schema

Field	Required	Description
`event_type`	Yes	Name of the event (`signup`, `purchase`, `page_view`, etc.)
`user_id`	No	Your user identifier — any string
`session_id`	No	Session identifier for funnel analysis
`properties`	No	Any JSON object — store whatever context is relevant

Connecting a front-end

Call /events/track directly from your front-end or server. For browser tracking, wrap it in a small fetch:

function track(event_type, properties = {}) {
  fetch('https://your-airpipe-host/events/track', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ event_type, user_id: getCurrentUserId(), properties })
  });
}

track('button_click', { button: 'upgrade', page: '/dashboard' });

Notes

The properties column is JSONB — any valid JSON object is accepted with no schema migration needed when you add new event types or properties.
emit_metric increments app_events_tracked_total{event_type=...} on every successful track call, giving you a Prometheus counter per event type for free.
For high-volume ingestion, add a LIMIT to the events/recent query or replace it with a time-windowed query.

Configuration

config.yml

name: EventTracking
description: Lightweight product analytics — track any event with arbitrary properties, then query counts and recent activity.

docs: true

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

interfaces:

  # POST /events/track
  # Record an event. user_id and session_id are optional but recommended for
  # any per-user analysis.
  # Body: { "event_type": "signup", "user_id": "u_123", "properties": {...} }
  events/track:
    output: http
    method: POST
    summary: Track event
    description: Record a named event with optional user context and arbitrary properties.
    tags: [events]
    request_example:
      event_type: page_view
      user_id: u_123
      session_id: s_abc
      properties:
        page: /pricing
        referrer: google

    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 name (e.g. page_view, signup, purchase)"

      - name: StoreEvent
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          INSERT INTO events (event_type, user_id, session_id, properties)
          VALUES ($1, $2, $3, $4::jsonb)
          RETURNING id, event_type, received_at;
        params:
          - a|ValidateBody::event_type|
          - a|ValidateBody::user_id|
          - a|ValidateBody::session_id|
          - a|ValidateBody::properties|
        post_transforms:
          - extract_value: "[0]"

      - name: TrackEvent
        run_when_succeeded: [StoreEvent]
        hide_data_on_success: true
        emit_metric:
          name: app_events_tracked_total
          type: counter
          labels:
            event_type: a|ValidateBody::event_type|

  # GET /events/summary
  # Event counts grouped by type, ordered by frequency.
  events/summary:
    output: http
    summary: Event summary
    description: Count of each event type across all time, ordered by frequency.
    tags: [events]
    response_example:
      - event_type: page_view
        count: 1420
      - event_type: signup
        count: 83

    actions:
      - name: Summarise
        database: main
        query: |
          SELECT event_type, COUNT(*) AS count
          FROM events
          GROUP BY event_type
          ORDER BY count DESC;

  # POST /events/query
  # Filter events by type and/or date range.
  # Body: { "event_type": "signup", "from": "2024-01-01", "to": "2024-12-31", "limit": 100 }
  events/query:
    output: http
    method: POST
    summary: Query events
    description: Filter events by type and date range.
    tags: [events]
    request_example:
      event_type: signup
      from: "2024-01-01"
      to: "2024-12-31"
      limit: 50

    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
              description: "Event type to filter by"

      - name: QueryEvents
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT id, event_type, user_id, session_id, properties, received_at
          FROM events
          WHERE event_type = $1
            AND ($2::timestamptz IS NULL OR received_at >= $2::timestamptz)
            AND ($3::timestamptz IS NULL OR received_at <= $3::timestamptz)
          ORDER BY received_at DESC
          LIMIT COALESCE($4, 100);
        params:
          - a|ValidateBody::event_type|
          - a|ValidateBody::from|
          - a|ValidateBody::to|
          - a|ValidateBody::limit|

  # GET /events/recent
  # Last 100 events across all types, most recent first.
  events/recent:
    output: http
    summary: Recent events
    description: Last 100 events across all types.
    tags: [events]

    actions:
      - name: RecentEvents
        database: main
        query: |
          SELECT id, event_type, user_id, session_id, properties, received_at
          FROM events
          ORDER BY received_at DESC
          LIMIT 100;

  # POST /events/user
  # All events for a specific user, most recent first.
  # Body: { "user_id": "u_123" }
  events/user:
    output: http
    method: POST
    summary: User event history
    description: All events recorded for a specific user ID.
    tags: [events]
    request_example:
      user_id: u_123

    actions:
      - name: ValidateBody
        input: a|body|
        hide_data_on_success: true
        assert:
          http_code_on_error: 400
          tests:
            - value: user_id
              is_not_null: true
              description: "User identifier"

      - name: UserEvents
        run_when_succeeded:
          actions: [ValidateBody]
          http_code_on_error: 400
        database: main
        query: |
          SELECT id, event_type, session_id, properties, received_at
          FROM events
          WHERE user_id = $1
          ORDER BY received_at DESC
          LIMIT 500;
        params:
          - a|ValidateBody::user_id|

schema.sql

CREATE TABLE IF NOT EXISTS events (
  id          BIGSERIAL    PRIMARY KEY,
  event_type  TEXT         NOT NULL,
  user_id     TEXT,
  session_id  TEXT,
  properties  JSONB,
  received_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_events_type        ON events (event_type);
CREATE INDEX IF NOT EXISTS idx_events_user_id     ON events (user_id);
CREATE INDEX IF NOT EXISTS idx_events_received_at ON events (received_at DESC);

Event Tracking

What's included​

Endpoints​

Setup​

1. Run the schema​

2. Set managed variable​

Testing​

Event schema​

Connecting a front-end​

Notes​

Configuration​

config.yml​

schema.sql​