Sequin allows you to annotate database changes with additional context using annotations. This is useful for:

  • Audit trails: Track who made changes and from where
  • Debugging: Add request IDs and other debugging context
  • Business context: Include business-specific metadata about changes
  • Change grouping: Group related changes across tables

​
How it works

Transaction annotations are JSON objects that you attach to a transaction. Any changes that occur within that transaction following the annotation statement will include the annotations in their metadata.

To set annotations:

select pg_logical_emit_message(true, 'sequin:transaction_annotations.set', '{
  "username": "paul.atreides",
  "source": "web_ui",
  "request_id": "req_123"
}');

The annotations will be included in the metadata.transaction_annotations field of all messages in that transaction:

{
  "record": { /* ... */ },
  "changes": null,
  "action": "insert",
  "metadata": {
    "table_schema": "public",
    "table_name": "orders",
    "commit_timestamp": "2024-10-28T21:37:53Z",
    "commit_lsn": 123456789,
    "commit_idx": 1,
    "database_name": "myapp-prod",
    "consumer": {
      "id": "f47ac10b-58cc-4372-a567-0e02b2c3d479",
      "name": "orders_kafka",
      "annotations": {"my-custom-key": "my-custom-value"}
    },
    "database": {
      "id": "12345678-9abc-def0-1234-56789abcdef0",
      "name": "myapp-prod",
      "annotations": {"my-custom-key": "my-custom-value"},
      "database": "myapp-prod",
      "hostname": "db.example.com"
    },
    "transaction_annotations": {
      "username": "paul.atreides",
      "source": "web_ui",
      "request_id": "req_123"
    }
  }
}

​
Usage

​
Setting annotations

Annotations must be set within the transaction they’re annotating, before changes are made:

begin;
  -- Set annotations for this transaction
  select pg_logical_emit_message(true, 'sequin:transaction_annotations.set', '{
    "username": "paul.atreides",
    "source": "web_ui",
    "request_id": "req_123"
  }');

  -- All changes in this transaction will include the annotations
  insert into orders (customer_id, product, quantity)
  values (789, 'Wireless Headphones', 1);

  update customers
  set last_order_at = now()
  where id = 789;
commit;

​
Clearing annotations

Annotations are automatically cleared at the end of each transaction. You can also explicitly clear them, ensuring they will not be included in any changes following the clear statement:

select pg_logical_emit_message(true, 'sequin:transaction_annotations.clear', '');

​
Common use cases

​
Audit logs

Transaction annotations are particularly useful for audit logs. They allow you to capture who made changes and why:

begin;
  select pg_logical_emit_message(true, 'sequin:transaction_annotations.set', '{
    "username": "paul.atreides",
    "action_type": "refund_order",
    "reason": "customer_request",
    "ticket_id": "TICKET-123"
  }');

  update orders set status = 'refunded' where id = 456;
  insert into refunds (order_id, amount) values (456, 99.99);
commit;

See Create audit logs for a complete guide.

​
Request tracing

Add request context to help with debugging:

begin;
  select pg_logical_emit_message(true, 'sequin:transaction_annotations.set', '{
    "request_id": "req_123",
    "trace_id": "trace_456",
    "user_agent": "Mozilla/5.0...",
    "ip_address": "1.2.3.4"
  }');

  -- Make changes
commit;

​
Business workflows

Group related changes with workflow context:

begin;
  select pg_logical_emit_message(true, 'sequin:transaction_annotations.set', '{
    "workflow": "new_subscription",
    "plan_id": "pro_monthly",
    "payment_id": "py_789",
    "coupon_code": "WELCOME"
  }');

  insert into subscriptions (...);
  insert into subscription_items (...);
  update customer_metrics (...);
commit;

​
Next steps

See the reference for “annotations” for more details on how to use annotations to add context to your database changes.